在 PostgreSQL 中计算百分比-未完纪

在现代 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;

除了 row_number() 这样的专用窗口函数，PostgreSQL 的聚合函数也可以按窗口模式运行。因此，可以将上面的查询改写为：

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 结果会发现，它仍然只扫描主数据表一次。这正是优化的重点：这类商业智能查询通常面对非常大的事实表，而扫描才是成本高昂的部分。

原文：Percentage Calculations。作者／来源：Crunchy Data。本文依据所列原文整理为中文，代码、命令与配置示例保留原文。

文章版权归作者所有，未经允许请勿转载。

THE END