Re: [patch] Proposal for \rotate in psql - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: [patch] Proposal for \rotate in psql |
| Date | |
| Msg-id | CAFj8pRDjcy4XV9GPo2=iYoarC1+kt=kJ6ZQCF5y+e2pmO27wFw@mail.gmail.com Whole thread Raw |
| In response to | Re: [patch] Proposal for \rotate in psql ("Daniel Verite" <daniel@manitou-mail.org>) |
| Responses |
Re: [patch] Proposal for \rotate in psql
|
| List | pgsql-hackers |
2015-11-30 16:34 GMT+01:00 Daniel Verite <daniel@manitou-mail.org>:
.Usually you have not natural order for both dimensions - I miss a possibility to set [+/-] order for vertical dimension
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);
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;
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
pgsql-hackers by date: