使用 PostgreSQL 窗口函数进行百分比计算
原创使用 PostgreSQL 窗口函数进行百分比计算
原创
当我第一次学习 SQL 时，计算一组个人贡献的百分比是一件很笨拙的事情：
- 首先计算百分比的分母
- 然后将该分母连接回原始表以计算百分比
这需要两次遍历表：一次用于分母，一次用于百分比。对于针对大型表的 BI 查询（即：对于大多数 BI 查询），更多的表传递会显著降低性能。
另外，SQL 实在是太难看了！
使用现在的 PostgreSQL，您可以使用“窗口函数”[1]一次计算不同组的复杂百分比。
示例数据
这是我们的测试数据，一个由七名音乐家组成的小表，他们在两个乐队中表演。
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语句[2]和 窗口函数之前，查询可能如下所示：
SELECT band, name, round(100 * earnings/sums.sum,1) AS percentFROM musiciansCROSS JOIN ( SELECT Sum(earnings) FROM musicians ) AS sumsORDER BY percent;借助现代 PostgreSQL，我们可以使用“窗口函数”来即时计算百分比的分母。如果您在文档中查找窗口函数，您会发现一些特定的窗口函数，例如 row_number()[3]，但您还会发现旧的聚合函数，例如 sum()可以在窗口模式下使用。
SELECT band, name, round(100 * earnings / Sum(earnings) OVER (), 1) AS percentFROM musiciansORDER 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 percentFROM musiciansJOIN sums USING (band)ORDER BY band, percent;另一方面，对于窗口函数，我们只需要改变分母的特性。我们想要的不是所有收益的总和，而是每个波段计算的总和，这是通过在窗口函数的OVER子句中添加PARTITION来获得的。
SELECT band, name, round(100 * earnings / Sum(earnings) OVER (PARTITION BY band), 1) AS percentFROM musiciansORDER BY band, percent;每个乐队的总收入百分比
最后，为了完整起见，以下是获取每个乐队占总收入百分比的单次扫描方法：
SELECT band, round(100 * earnings / Sum(earnings) OVER (), 1) AS percentFROM ( SELECT band, Sum(earnings) AS earnings FROM musicians GROUP BY band ) bands;请注意，我被迫在这里使用子查询，因为不允许在聚合中嵌入窗口查询。
但是，如果您检查EXPLAIN[4]此查询，您会发现它仍然只对主数据表进行一次扫描，这主要是我们试图避免的，因为这些 BI 类型的查询通常针对非常大的事实表和扫描。
参考
SN(序号) | 参考信息 |
|---|---|
1 | |
2 | |
3 | |
4 |
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
