PostgreSQL中窗口函数是否可以与Grouping Sets搭配使用？

引言 #

本次问答将探讨在PostgreSQL的同一条查询中同时使用窗口函数和分组集的方法。读者需提前熟悉这两个概念才能充分理解相关内容，可以配套示例环境跟随操作。

数据准备 #

如下表存储了若干交易记录，每条交易包含客户名称、交易发生城市、交付给客户的商品数量三个字段。

CREATE TABLE transactions (customer TEXT, city TEXT, units INT);
INSERT INTO transactions VALUES ('Bob', 'Manchester', 10),
                                ('Chuck', 'Manchester', 20),
                                ('Bob', 'Concord', 10),
                                ('Tim', 'Manchester', 15),
                                ('Jane', 'Derry', 10),
                                ('Tim', 'Derry', 15),
                                ('Tim', 'Concord', 20),
                                ('Bob', 'Manchester', 20),
                                ('Chuck', 'Concord', 10);

预期结果 #

需要生成一份报表，可回答类似“当前行数据占全量交易的比例是多少”、“当前行数据占对应客户全部交易的比例是多少”这类问题，同时覆盖所有同类维度的查询需求。借助这份报表，只需查看每行数据即可获取它与全量数据、特定数据切片的关联信息。

首次实现尝试 #

要生成这类报表，可以通过多条不同GROUP BY逻辑的查询配合UNION ALL合并结果，也可以尝试如下写法：

-- 结果错误示例
SELECT customer
     , city
     , SUM(units) AS "units"
     , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER () , 2) AS "to report"
     , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY customer) , 2) AS "to customer"
     , ROUND( 100 * SUM(units) / SUM(SUM(units)) OVER (PARTITION BY city) , 2) AS "to city"
  FROM transactions
GROUP BY CUBE(customer, city)

上述查询在GROUP BY子句中使用了CUBE，会生成customer和city两个字段所有可能组合的分组结果。比例的分子是对应当前行的商品数量聚合值，所有比例的分子都相同，也就是units列的计算表达式SUM(units)。分母的计算更复杂，需要用窗口函数计算目标切片的总商品量，比如特定客户、特定城市的总交易量，或是全量报表的总交易量。

结果错误说明 #

遗憾的是，上述查询返回的比例结果并不正确。比如第一行的units值为10，占全量总交易量130的7.7%，占客户Bob总交易量40的25%，占Concord城市总交易量40的25%，但查询返回的所有比例都低于正确值。再举个例子，customer和city均为NULL的行units值为130，但计算得到的“to report”比例仅为25%，显然比例列的分母计算逻辑有误。我们要如何得到预期的正确结果？

内容的提问来源于stack exchange，提问作者dicristina