由 John Doe 十一月 7, 2024
摘要：在本文中，您将学习如何在 PostgreSQL 中计算不同分组的百分比。
目录
介绍
传统上，在一组个体上计算其贡献百分比是一项笨拙的工作：
- 首先计算百分比的分母部分，
- 然后将该分母连接回到原始的表，以计算百分比。
这需要对表进行两次遍历：一次用于计算分母部分，一次用于计算百分比。对于大型表的报表查询（即大多数 BI 查询），表上的多次遍历会显著降低性能。而且，SQL 真的很丑！
使用较新版本的 PostgreSQL，您可以使用 “窗口函数” 在一次遍历中对不同的分组计算复杂的百分比。
示例数据
这是我们的测试数据，一个小表，其中包含了两个乐队中表演的 7 位乐手。
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;
在这里，我们通过使用带有OVER关键字的sum()函数，来指定一个窗口上下文，从而得到所有收入的总和。
由于我们没有在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来解释此查询，您会发现它仍然只在主数据表上进行了一次扫描，这主要是我们试图避免的，因为此类报表查询通常针对非常大的实际表运行，而扫描是高成本的部分。