Thread: Multiple aggs,sums in 1 query

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.