使用 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