From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | avg,first,last,median in one query |
Date: | 2016-03-24 14:00:55 |
Message-ID: | 56F3F317.2080507@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi, hackers.
I need advice from SQL experts: is there any way in PostgreSQL to
calculate avg,first,last,median aggregates in one query?
Assume that we have the following table:
create table Securities ("Symbol" varchar, "Date" date, "Time" time,
"Price" real);
We can simulate median using percentile_disc:
select "Symbol","Date",
avg("Price"),
percentile_disc(0.5) within group (order by "Price")
from Securities
group by "Symbol","Date";
And all other aggregates can be calculated using windows functions:
select distinct "Symbol","Date",
first_value("Price") over (partition by "Symbol","Date" order by
"Time" rows between unbounded preceding and unbounded following),
last_value("Price") over (partition by "Symbol","Date" order by
"Time" rows between unbounded preceding and unbounded following),
avg("Price") over (partition by "Symbol","Date" rows between
unbounded preceding and unbounded following)
from Securities;
I wonder is there are any simpler/efficient alternative to the query above?
But unfortunately it is not possible to calculate median is such way
because percentile_disc is not compatible with OVER:
ERROR: OVER is not supported for ordered-set aggregate percentile_disc
So is there any chance to calculate all this four aggregates in one
query without writing some supplementary functions?
Additional question: what is the most efficient way of calculating
MEDIAN in PostgreSQL?
I found three different approaches:
1. Using CTE:
https://www.periscopedata.com/blog/medians-in-sql.html
2. Using user-defined aggregate function which uses array_appendand so
materialize all values in memory:
https://wiki.postgresql.org/wiki/Aggregate_Median
3. Using percentile aggregate:
Thanks in advance,
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2016-03-24 14:01:30 | Re: PostgreSQL 9.6 behavior change with set returning (funct).* |
Previous Message | Aleksander Alekseev | 2016-03-24 13:50:45 | Small patch: Change calling convention for ShmemInitHash (and fix possible bug) |