如何使用SQL窗口函数计算合计百分比

小编典典

如何使用SQL窗口函数计算合计百分比

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

但是，我需要做的是计算出d1中d2之和的总份额。我正在寻找的输出是这样的：

"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

我假设这是因为它在抱怨在分组子句中没有考虑窗口函数，但是无论如何也不能将窗口函数放在分组子句中。

这使用的是Greenplum 4.1，它是Postgresql 8.4的一个分支，并具有相同的窗口功能。请注意，Greenplum无法执行相关子查询。

阅读 289

2021-04-15

共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 ...是一个窗口函数（附加的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版本相关的子查询。请参阅发行说明。

2021-04-15