How to use a SQL window function to calculate a percentage of an aggregate
I think you are looking for this:
SELECT d1, d2, sum(v)/sum(sum(v)) OVER (PARTITION BY d1) AS share
FROM test
GROUP BY d1, d2;
Produces the requested result.
Window functions are applied after aggregate functions. The outer sum()
in sum(sum(v)) OVER ...
is a window function (attached OVER ...
clause) while the inner sum()
is an aggregate function.
Effectively the same as:
WITH x AS (
SELECT d1, d2, sum(v) AS sv
FROM test
GROUP BY d1, d2
)
SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS share
FROM x;
Or (without CTE):
SELECT d1, d2, sv/sum(sv) OVER (PARTITION BY d1) AS share
FROM (
SELECT d1, d2, sum(v) AS sv
FROM test
GROUP BY d1, d2
) x;
Or @Mu's variant.
Aside: Greenplum introduced correlated subqueries with version 4.2. See release notes.
Do you need to do it all with window functions? Sounds like you just need to group the result you have by d1
and d2
and then sum the sums:
select d1, d2, sum(p)
from (
select d1, d2, v/sum(v) over (partition by d1) as p
from test
) as dt
group by d1, d2
That gives me this:
d1 | d2 | sum
----+----+------------------------
a | x | 0.25000000000000000000
a | y | 0.75000000000000000000
b | x | 1.00000000000000000000