十月 23, 2024
摘要：在本教程中，您将学习一些在 PostgreSQL 中将聚合过滤器与窗口函数结合使用的实际示例。
目录
介绍
PostgreSQL 可以在数百万记录的表中进行高性能的汇总，并支持一些出色的 SQL 语法，以使查询简洁易读，特别是聚合过滤，这是 PostgreSQL 和 SQLite 独有的功能。
大量的报告都是关于生成百分比的：在一个特定条件下，相对于基线的值是什么。
创建示例数据
下面是一个快速的“销售表”，其中包含三个类别（“a” 和 “b” 和 “c”），和一千个介于 0 和 10 之间的随机值：
CREATE TABLE sales
AS
SELECT a, b,
CASE WHEN random() < 0.4 THEN 'bird' ELSE 'bee' END AS c,
10 * random() AS value
FROM generate_series(1,100) a,
generate_series(1,100) b;
以前的做法
在过去，要生成百分比，可能需要在计算百分比之前添加子查询以生成总数。要 “找到 c 为 ‘bee’ 的值的 %”：
SELECT
100.0 * sum(value) / (SELECT sum(value) AS total FROM sales) AS bee_pct
FROM sales
WHERE c = 'bee';
这一切都很好，但是如果我还想计算 “a” 值 > 90 的销售额百分比怎么办？
突然间，我需要运行两个查询，或者可以构建一个如下所示的 CTE：
WITH total AS (
SELECT sum(value) AS total
FROM sales
),
bee AS (
SELECT sum(value) AS bee
FROM sales
WHERE c = 'bee'
),
a90 AS (
SELECT sum(value) AS a90
FROM sales
WHERE a > 90
)
SELECT 100.0 * bee / total AS bee_pct,
100.0 * a90 / total AS a90_pct
FROM total, bee, a90;
这个查询太难看了！此外，它还会扫描表三次。有没有其他方法？当然，总有另一种方法，但并不一定更好。
SELECT
100.0 * sum(CASE WHEN c = 'bee' THEN value ELSE 0.0 END) /
sum(value) AS bee_pct,
100.0 * sum(CASE WHEN a > 90 THEN value ELSE 0.0 END) /
sum(value) AS a90_pct
FROM sales;
聚合过滤器
PostgreSQL 支持 “窗口函数”，但有时人们会忘记聚合函数也是窗口函数，因此，它们可以同更高级的窗口函数（如lag()或rank()）一样，进行同样的控制。
SELECT
100.0 * sum(value) FILTER (WHERE c = 'bee') / sum(value) AS bee_pct,
100.0 * sum(value) FILTER (WHERE a > 90) / sum(value) AS a90_pct
FROM sales;
这比其他方法清晰多了，而且运行速度也比它们快！
使用新版本的 PostgreSQL 时，该表的单次扫描将是并行化的。更好的是，您可以将任何聚合函数与过滤条件一起使用，这在CASE方法中是不可能的。
SELECT
stddev(value) FILTER (WHERE c = 'bee') AS bee_stddev,
stddev(value) FILTER (WHERE a > 90) AS a90_stddev
FROM sales;
大数据量分析
对于数据仓库中的简单报告和数据分析，最好的方法就是，创建一个宽的物化视图，将所有感兴趣的列聚集到一个平面表中，而且还可以自由地应用聚合过滤器。
聚合过滤器甚至可以与标准的GROUP子句结合使用，以快速细分组内的统计数据。
SELECT
b / 100 AS b_div_100,
stddev(value) FILTER (WHERE c = 'bee') AS bee_stddev,
stddev(value) FILTER (WHERE a > 90) AS a90_stddev
FROM sales
GROUP BY 1;
结论
-
在构建分析查询时，请考虑一下您可以在表中一次性提取哪些内容，再使用聚合过滤条件去除所需的信息。
-
在构建数仓分析时，请考虑将感兴趣的列物化到查询视图中，并使用聚合过滤器来浏览内容。