2015-11-30 16:34 GMT+01:00 Daniel Verite <dan...@manitou-mail.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 >
