Thread: Multiple aggs,sums in 1 query

Multiple aggs,sums in 1 query

From
Dorian Hoxha
Date:
I have: create table tbl (a,b,c,d,e,f,g,h);

And i need to select in 1 query ,or the most performant way:

top 5(a)
top 5(b)
top 5(c): for each top5(c): top 5(d)
count(f) GROUP BY f


I can make these in separate queries but that means that postgresql would read the table multiple-times?

Is it possible/doable/sane ?

Thanks

Re: Multiple aggs,sums in 1 que

From
David Johnston
Date:
Dorian Hoxha wrote
> I have: create table tbl (a,b,c,d,e,f,g,h);
>
> And i need to select in 1 query ,or the most performant way:
>
> top 5(a)
> top 5(b)
> top 5(c): for each top5(c): top 5(d)
> count(f) GROUP BY f
>
>
> I can make these in separate queries but that means that postgresql would
> read the table multiple-times?
>
> Is it possible/doable/sane ?
>
> Thanks

Yes, it is - sorta.

Create a custom array_agg like aggregate that only keeps the first 5
encountered values in the array and ignores all subsequent values.  Then
call that with order by.

This gets you a,b,c,f

You will need a separate query for the d output since that is dependent up
the result of the c query.

You should also try a window clause with appropriate partitions and compare
the performance of the two.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Multiple-aggs-sums-in-1-query-tp5779761p5779775.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.