使用 Excel 统计分组占比太慢？快来试试 PostgreSQL-51CTO.COM

介绍

传统上，在一组个体上计算其贡献百分比是一项笨拙的工作：

• 首先计算百分比的分母部分，

• 然后将该分母连接回到原始的表，以计算百分比。

这需要对表进行两次遍历：一次用于计算分母部分，一次用于计算百分比。对于大型表的报表查询（即大多数 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来解释此查询，您会发现它仍然只在主数据表上进行了一次扫描，这主要是我们试图避免的，因为此类报表查询通常针对非常大的实际表运行，而扫描是高成本的部分。