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.
>
> 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 :)

>
> > 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.
>
>
it is looking well

I'll do review tomorrow

Regards

Pavel

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