logo

使用 PostgreSQL 窗口函数进行百分比计算

作者:makedata2023.07.23 18:26浏览量:3619

简介:使用 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
  ) bands;

请注意,我被迫在这里使用子查询,因为不允许在聚合中嵌入窗口查询。

但是,如果您检查EXPLAIN此查询,您会发现它仍然只对主数据表进行一次扫描,这主要是我们试图避免的,因为这些 BI 类型的查询通常针对非常大的事实表和扫描。

相关文章推荐

发表评论

  • avatar
    sded2023.09.11 10:03
    • 回复