Re: [patch] Proposal for \rotate in psql

2015-12-05 8:59 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2015-11-30 16:34 GMT+01:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:
>
>> Pavel Stehule wrote:
>>
>> > [ \rotate being a wrong name ]
>>
>> Here's an updated patch.
>>
>
> Today I have a time to play with it. I am sorry for delay.
>
>
>>
>> First it renames the command to \crosstabview, which hopefully may
>> be more consensual, at least it's semantically much closer to crosstab .
>>
>
> Thank you very much - it is good name.
>
>
>>
>> > The important question is sorting output. The vertical header is
>> > sorted by first appearance in result. The horizontal header is
>> > sorted in ascending or descending order. This is unfriendly for
>> > often use case - month names. This can be solved by third parameter
>> > - sort function.
>>
>> I've thought that sorting with an external function would be too
>> complicated for this command, but sorting ascending by default
>> was not the right choice either.
>> So I've changed to sorting by first appearance in result (like the
>> vertical
>> header), and sorting ascending or descending only when specified
>> (with +colH or -colH syntax).
>>
>> So the synopsis becomes: \crosstabview [ colV [+ | -]colH ]
>>
>> Example with a time series (daily mean temperatures in Paris,2014),
>> month names across, day numbers down :
>>
>> select
>> to_char(w_date,'DD') as day ,
>> to_char(w_date,'Mon') as month,
>> w_temp from weather
>> where w_date between '2014-01-01' and '2014-12-31'
>> order by w_date
>> \crosstabview
>>
>> day | Jan | Feb | Mar | Apr | May | Jun | ...[cut]
>> -----+-----+-----+-----+-----+-----+-----+-
>> 01 | 8 | 8 | 6 | 16 | 12 | 15 |
>> 02 | 10 | 6 | 6 | 15 | 12 | 16 |
>> 03 | 11 | 5 | 7 | 14 | 11 | 17 |
>> 04 | 10 | 6 | 8 | 12 | 12 | 14 |
>> 05 | 6 | 7 | 8 | 14 | 16 | 14 |
>> 06 | 10 | 9 | 9 | 16 | 17 | 20 |
>> 07 | 11 | 10 | 10 | 18 | 14 | 24 |
>> 08 | 11 | 8 | 12 | 10 | 13 | 22 |
>> 09 | 10 | 6 | 14 | 12 | 16 | 22 |
>> 10 | 6 | 7 | 14 | 14 | 14 | 19 |
>> 11 | 7 | 6 | 12 | 14 | 12 | 21 |
>> ...cut..
>> 28 | 4 | 7 | 10 | 12 | 14 | 16 |
>> 29 | 4 | | 14 | 10 | 15 | 16 |
>> 30 | 5 | | 14 | 14 | 17 | 18 |
>> 31 | 5 | | 14 | | 16 | |
>>
>> The month names come out in the expected order here,
>> contrary to what happened with the previous iteration of
>> the patch which forced a sort in all cases.
>> Here it plays out well because the single "ORDER BY w_date" is
>> simultaneously OK for the vertical and horizontal headers,
>> a common case for time series.
>>
>> For more complicated cases, when the horizontal and vertical
>> headers should be ordered independantly, and
>> in addition the horizontal header should not be sorted
>> by its values, I've toyed with the idea of sorting by another
>> column which would supposedly be added in the query
>> just for sorting, but it loses much in simplicity. For the more
>> complex stuff, users can always turn to the server-side methods
>> if needed.
>>
>>
> .Usually you have not natural order for both dimensions - I miss a
> possibility to set [+/-] order for vertical dimension
>
> For my query
>
> select sum(amount) as amount, to_char(date_trunc('month', closed),'TMmon')
> as Month, customer
> from data group by customer, to_char(date_trunc('month', closed),
> 'TMmon'), extract(month from closed)
> order by extract(month from closed);
>
> I cannot to push order by customer - and I have to use
>
>
> select sum(amount) as amount, extract(month from closed) as Month,
> customer from data group by customer, extract(month from closed) order by
> customer;
>
> and \crosstabview 3 +2
>
> So possibility to enforce order for vertical dimension and use data order
> for horizontal dimension can be really useful. Other way using special
> column for sorting
>
> some like \crosstabview verticalcolumn horizontalcolumn
> sorthorizontalcolumn
>
>
> Next - I use "fetch_count" > 0. Your new version work only with
> "fetch_cunt <= 0". It is limit - but I am thinking it is acceptable.In this
> case some warning should be displayed - some like "crosstabview doesn't
> work with FETCH_COUNT > 0"
>
> I miss support for autocomplete and \?
>
>
> Regards
>
> Pavel
>
>
I did few minor changes in your patch

1. autocomplete + warning on active FETCH_COUNT (the worning should be
replaced by error, the statement show nothing)

2. support for labels

postgres=# \d data
Table "public.data"
┌──────────┬─────────┬───────────┐
│ Column │ Type │ Modifiers │
╞══════════╪═════════╪═══════════╡
│ id │ integer │ │
│ customer │ text │ │
│ name │ text │ │
│ amount │ integer │ │
│ expected │ text │ │
│ closed │ date │ │
└──────────┴─────────┴───────────┘

postgres=# select sum(amount) as amount, extract(month from closed) as
Month, to_char(date_trunc('month', closed), 'TMmon') as label, customer
from data group by customer, to_char(date_trunc('month', closed), 'TMmon'),
extract(month from closed) order by customer;

postgres=# \crosstabview 4 +month label
┌──────────────────────────┬───────┬───────┬────────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┐
│ customer │ led │ úno │ bře │ dub │ kvě │ čen
│ čec │ srp │ zář │ říj │ lis │
╞══════════════════════════╪═══════╪═══════╪════════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╪═══════╡
│ A********** │ │ │ │ │ │
│ │ │ │ 13000 │ │
│ A******** │ │ │ 8000 │ │ │
│ │ │ │ │ │
│ B***** │ │ │ │ │ │
│ │ │ │ │ 3200 │
│ B*********************** │ │ │ │ │ │
│ │ │ 26200 │ │ │
│ B********* │ │ │ │ │ │
│ 14000 │ │ │ │ │
│ C********** │ │ │ │ 7740 │ │
│ │ │ │ │ │
│ C*** │ │ │ │ │ │
│ │ │ 26000 │ │ │
│ C***** │ │ │ │ 12000 │ │
│ │ │ │ │ │
│ G******* │ 30200 │ 26880 │ 13536 │ 39360 │ 60480 │ 54240
│ 44160 │ 16320 │ 29760 │ 22560 │ 20160 │
│ G*************** │ │ │ │ │ │ 25500
│ │ │ │ │ │
│ G********** │ │ │ │ │ │ 16000
│ │ │ │ │ │
│ I************* │ │ │ │ │ │
│ │ 27920 │ │ │ │
│ i**** │ │ │ │ 13500 │ │
│ │ │ │ │ │
│ n********* │ │ │ │ │ │
│ 12600 │ │ │ │ │
│ Q** │ │ │ │ │ 16700 │
│ │ │ │ │ │
│ S******* │ │ │ │ │ │
│ 8000 │ │ │ │ │
│ S******* │ │ │ │ │ 5368 │
│ │ │ │ │ │
│ s******* │ │ │ 5000 │ 3200 │ │
│ │ │ │ │ │
└──────────────────────────┴───────┴───────┴────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘
(18 rows)

Regards

Pavel

>
>
>
>> Best regards,
>> --
>> Daniel Vérité
>> PostgreSQL-powered mailer: http://www.manitou-mail.org
>> Twitter: @DanielVerite
>>
>
>