Aggregate window function output in postgresql (redshift)

Question

I really want to use the median window function as an aggregate function.

I currently am forced to use the window function in a sub-select, and then aggregate over it like this:

SELECT id, MIN(avg) AS mean, MIN(median) AS median, COUNT(*)
    FROM (
    SELECT id, AVG(metric) OVER(PARTITION BY id), MEDIAN(metric) OVER(PARTITION BY id)
    FROM data_table
    )
GROUP BY id;

Is there a way to aggregate over a window function result so there's only one SELECT statement?

Bacon Bits Bacon Bits · Accepted Answer · 2015-03-19T18:23:28

Strictly speaking, your example query could be rewritten:

SELECT id,
    AVG(metric),
    MEDIAN(metric),
    COUNT(*)
FROM data_table
GROUP BY id;

But I'm wondering if you just picked a poor example that happens to be mathematically capable of simplification. This is a special case because the subquery and the main query are aggregating on the same field, and the outer aggregates are picking a minimum from what would be a set of identical values.

If that's not the case and your actual query and subquery are not grouping by the same field, then the answer is no, you need a subquery for two reasons:

First, by ANSI definition, window functions are evaluated after the WHERE, GROUP BY, and HAVING clauses. There is no clause to specify your desired behavior of aggregating after a window function, so you must use a subquery or CTE.

Second, even if you eliminated the windowing from the OVER() clause you need to GROUP BY data you only know after the first round of aggregation has been completed.

Aggregate window function output in postgresql (redshift)

1 Answers