Как использовать функцию окна SQL для вычисления процента от совокупности

Мне нужно рассчитать проценты различных размеров в таблице. Я хотел бы упростить вещи, используя функции окна для вычисления знаменателя, однако у меня проблема, потому что числитель также должен быть агрегатом.

В качестве простого примера возьмите следующую таблицу:

create temp table test (d1 text, d2 text, v numeric);
insert into test values ('a','x',5), ('a','y',5), ('a','y',10), ('b','x',20);

Если я просто хочу рассчитать долю каждой отдельной строки из d1, то функции оконной обработки работают нормально:

select d1, d2, v/sum(v) over (partition by d1)
from test;

"b";"x";1.00
"a";"x";0.25
"a";"y";0.25
"a";"y";0.50

Однако мне нужно вычислить общую долю для суммы d2 из d1. Результат, который я ищу, следующий:

"b";"x";1.00
"a";"x";0.25
"a";"y";0.75

Итак, я пробую это:

select d1, d2, sum(v)/sum(v) over (partition by d1)
from test
group by d1, d2;

Однако теперь я получаю сообщение об ошибке:

ERROR:  column "test.v" must appear in the GROUP BY clause or be used in an aggregate function

Я предполагаю, что это связано с тем, что он жалуется, что функция window не учитывается в предложении grouping, однако в любом случае функции привязки не могут быть помещены в предложение группировки.

Это использует Greenplum 4.1, который является fork Postgresql 8.4 и имеет одни и те же функции окон. Обратите внимание, что Greenplum не может выполнять коррелированные подзапросы.

Ответ 1

Я думаю, что вы действительно ищете:

SELECT d1, d2, sum(v)/sum(sum(v)) OVER (PARTITION BY d1) AS share
FROM   test
GROUP  BY d1, d2;

Производит запрошенный результат.

Функции окна применяются после агрегатных функций. Внешний sum() в sum(sum(v)) является оконной функцией в этом примере и привязан к предложению OVER ..., тогда как внутренний sum() является агрегатом.

Эффективно то же самое, что:

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;

Или (без 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;

Или вариант @Mu.

Кроме того, Greenplum представил коррелированные подзапросы с версией 4.2. См. примечания к выпуску.

Ответ 2

Вам нужно сделать все это с помощью оконных функций? Похоже, вам просто нужно сгруппировать полученный результат с помощью d1 и d2, а затем суммировать суммы:

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

Это дает мне следующее:

 d1 | d2 |          sum           
----+----+------------------------
 a  | x  | 0.25000000000000000000
 a  | y  | 0.75000000000000000000
 b  | x  | 1.00000000000000000000