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",
    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:


2. Using user-defined aggregate function which uses array_appendand so materialize all values in memory:


3. Using percentile aggregate:


Thanks in advance,


Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to