2015-12-14 23:09 GMT+01:00 Daniel Verite <dan...@manitou-mail.org>:

>         Pavel Stehule wrote:
> > postgres=# \crosstabview 4 +month label
> >
> > Maybe using optional int order column instead label is better - then you
> can
> > do sort on client side
> >
> > so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol]
> In the meantime I've followed a different idea: allowing the
> vertical header to be sorted too, still server-side.
> That's because to me, the first impulse for a user noticing that
> it's not sorted vertically would be to write
>  \crosstabview +customer month
> rather than figure out the
>  \crosstabview customer +month_number month_name
> invocation.
> But both ways aren't even mutually exclusive. We could support
>  \crosstabview [+|-]colV[:labelV] [+|-]colH[:labelH]
> it's more complicated to understand, but not  harder to implement.

yes, I was able to do what I would - although the query was little bit

 select amount, label, customer from (select sum(amount) as amount,
extract(month from closed)::int - 1 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)
union select sum(amount), extract(month from closed)::int - 1 as month,
to_char(date_trunc('month', closed), 'TMmon') as label, '**** TOTAL ****'
from data group by to_char(date_trunc('month', closed), 'TMmon'),
extract(month from closed)::int - 1 order by  month) x

 \crosstabview +3 2

> Also, a non-zero FETCH_COUNT is supported by this version of the patch,
> if the first internal FETCH retrieves less than FETCH_COUNT rows.
> Otherwise a specific error is emitted.

good idea

> Also there are minor changes in arguments and callers following
> recent code changes for \o
> Trying to crosstab with 10k+ distinct values vertically, I've noticed
> that the current code is too slow, spending too much time
> sorting.  I'm currently replacing its simple arrays of distinct values
> with AVL binary trees, which I expect to be much more efficient for
> this.



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

Reply via email to