使用 PostgreSQL 窗口函数进行百分比计算

当我第一次学习 SQL 时，计算一组个人贡献的百分比是一件很笨拙的事情：

• 首先计算百分比的分母
• 然后将该分母连接回原始表以计算百分比

这需要两次遍历表：一次用于分母，一次用于百分比。对于针对大型表的 BI 查询（即：对于大多数 BI 查询），更多的表传递会显著降低性能。
另外，SQL 实在是太难看了！
使用现在的 PostgreSQL，您可以使用“窗口函数”一次计算不同组的复杂百分比。

示例数据

这是我们的测试数据，一个由七名音乐家组成的小表，他们在两个乐队中表演。

CREATE TABLE musicians (
  band text,
  name text,
  earnings numeric(10,2)
);

INSERT INTO musicians VALUES
  ('PPM',  'Paul',   2.2),
  ('PPM',  'Peter',  4.5),
  ('PPM',  'Mary',   1.1),
  ('CSNY', 'Crosby', 4.2),
  ('CSNY', 'Stills', 6.3),
  ('CSNY', 'Nash',   0.3),
  ('CSNY', 'Young',  2.2);

每个音乐家的总收入百分比

回到“过去”，在 WITH语句和 窗口函数之前，查询可能如下所示：

SELECT
  band, name,
  round(100 * earnings/sums.sum,1) AS percent
FROM musicians
CROSS JOIN (
   SELECT Sum(earnings)
   FROM musicians
  ) AS sums
ORDER BY percent;

借助现代 PostgreSQL，我们可以使用“窗口函数”来即时计算百分比的分母。如果您在文档中查找窗口函数，您会发现一些特定的窗口函数，例如 row_number()，但您还会发现旧的聚合函数，例如 sum()可以在窗口模式下使用。

SELECT
  band, name,
  round(100 * earnings /
      Sum(earnings) OVER (),
       1) AS percent
FROM musicians
ORDER BY percent;

sum()在这里，我们通过使用带有 OVER关键字的函数来指示窗口上下文，从而获得所有收入的总和。

由于我们没有提供任何限制，因此OVER效果是 结果关系中所有行的总和。这就是我们所需要的！

每个音乐家的乐队收入百分比

收入占总收入的百分比只是划分收入的一种方法：也许我们想知道相对于乐队收入，哪些音乐家赚的钱最多？
如果用老式的方式来做这件事，SQL 就会变得更加复杂！

WITH sums AS (
   SELECT Sum(earnings), band
   FROM musicians
   GROUP BY band
)
SELECT
  band, name,
  round(100 * earnings/sums.sum, 1) AS percent
FROM musicians
JOIN sums USING (band)
ORDER BY band, percent;

另一方面，对于窗口函数，我们只需要改变分母的特性。我们想要的不是所有收益的总和，而是每个波段计算的总和，这是通过在窗口函数的OVER子句中添加PARTITION来获得的。

SELECT
  band, name,
  round(100 * earnings /
      Sum(earnings) OVER (PARTITION BY band),
       1) AS percent
FROM musicians
ORDER BY band, percent;

每个乐队的总收入百分比

最后，为了完整起见，以下是获取每个乐队占总收入百分比的单次扫描方法：

SELECT
  band,
  round(100 * earnings /
      Sum(earnings) OVER (),
       1) AS percent
FROM (
   SELECT band,
      Sum(earnings) AS earnings
   FROM musicians
   GROUP BY band
  ) bands;

请注意，我被迫在这里使用子查询，因为不允许在聚合中嵌入窗口查询。

但是，如果您检查EXPLAIN此查询，您会发现它仍然只对主数据表进行一次扫描，这主要是我们试图避免的，因为这些 BI 类型的查询通常针对非常大的事实表和扫描。