如何在PostgreSQL中用单查询实现每行除以子类别总计

假设我们有一张名为table0的PostgreSQL表，结构及数据如下：

id | value | category
----+-------+----------
  0 |    10 | a
  1 |    11 | b
  2 |    50 | b
  3 |    20 | a

我们需要为每一行计算weight列，值为当前行的value除以其对应category下所有value的总和，最终结果如下：

id | value | category | weight
----+-------+----------+--------
  0 |    10 | a        | 0.3333
  1 |    11 | b        | 0.1803
  2 |    50 | b        | 0.8197
  3 |    20 | a        | 0.6667

如果用两次查询，我们可以先聚合每个类别的总和再关联原表，但单查询可以通过窗口函数直接实现，无需临时表或关联操作：

SELECT
    id,
    value,
    category,
    value::numeric / SUM(value) OVER (PARTITION BY category) AS weight
FROM table0;

关键说明： #

• SUM(value) OVER (PARTITION BY category) 是窗口函数，它会为每一行计算其所属category分组的value总和，不会改变原表的行结构（区别于普通GROUP BY会合并行的特性）。
• 将value转为numeric类型是为了避免PostgreSQL的整数取整除法，确保得到精确的小数权重值。

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