From: | David Johnston <polobo(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Multiple aggs,sums in 1 que |
Date: | 2013-11-22 15:30:03 |
Message-ID: | 1385134201927-5779775.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Kienenberger | 2013-11-22 15:33:21 | Re: Browsing postgres database using the Eclipse QuantumDB plugin |
Previous Message | Mike Kienenberger | 2013-11-22 15:28:01 | Re: Browsing postgres database using the Eclipse QuantumDB plugin |