Re: [patch] Proposal for \rotate in psql

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

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