一月 8, 2025
摘要：在本教程中，您将学习如何在 PostgreSQL 中使用聚合，来加速 COUNT(DISTINCT)。
目录
介绍
只有在我们从数据库的角度理解了原始的估计值后，基于估计值的解决方案才有用。如果我们需要一个精确时间点的精确计数怎么办？如果预先知道时间（例如，每天上午 8 点），我们可以采用专用数据结构，预先计算出计数。
在读取密集型的环境中，只有很少的修改和写入的情况下，这种方法可以加快COUNT(DISTINCT)查询的速度，由于写入很少，更新聚合数据的开销将会最小。例如，如果我们按天对 order 表进行分区，我们可以对前一天的所有分区执行聚合计数，因为它们不会发生任何更新/删除。
在分析聚合之前，让我们使用下面的 SQL，给EXAMPLE_TBL表填充更多数据：
INSERT INTO EXAMPLE_TBL (Username, Purchase_Date, Product, Qty)
SELECT 'User' || floor(random() * 200 + 1)::int,
CURRENT_DATE - floor(random() * 5 + 1)::int,
CASE floor(random() * 3 + 1)::int
WHEN 1 then ''
WHEN 2 then ''
WHEN 3 then ''
END,
floor(random() * 10 + 1)::int
FROM generate_series(1,1000000);
运行完成后，上面的 SQL 会在EXAMPLE_TBL表中生成1,000,000行。
通过物化视图聚合
包括 PostgreSQL 在内的大多数数据库，都有物化视图的概念：这种方法，将 SELECT 语句的结果存储在一个专用表中，并让数据库自动使用该表进行匹配的查询。
例如，如果我们在表上创建一个物化视图，使用下面的方法计算每个产品的不同用户名：
CREATE MATERIALIZED VIEW EXAMPLE_MW AS
SELECT PRODUCT,
COUNT(DISTINCT USERNAME)
FROM EXAMPLE_TBL
GROUP BY PRODUCT;
通过查询pg_matviews系统视图，我们可以在数据库中看到一个名为example_mw的新物化视图：
SELECT * FROM pg_matviews WHERE matviewname = 'example_mw';
请记住，普通视图和物化视图之间的区别在于，后者的结果实际上存储在数据库中。如果现在我们运行如下的查询：
SELECT PRODUCT,
COUNT(DISTINCT USERNAME)
FROM EXAMPLE_TBL
GROUP BY PRODUCT;
上面的查询大约需要6346.786 ms，而如果我们对物化视图运行类似的查询：
SELECT * FROM example_mw;
它只用了16.683 ms！但是，我们需要记住，物化视图在创建时是物理化的，不会自动更新，因此为了获得准确的结果，我们需要使用下面的命令，刷新物化视图：
REFRESH MATERIALIZED VIEW example_mw;
在本例中，它用了6490.287 ms。如前所述，在底层表没有频繁写入的情况下，使用物化视图可能是一个很好的解决方案。计算一次物化视图，并持续查询它，可以提高性能。
通过专用数据结构聚合: hyperloglog
Citus 的 postgresql-hll 引入了一种称为hll的新数据类型，这是一种 HyperLogLog 数据结构，它是一种固定大小的集合结构，以用于不同值的计数，可调整精度。HyperLogLog 表的美妙之处在于，我们可以以详细的粒度（比如购买的日期）输入数据结构一次，并在更聚合的级别（比如购买的月份）来查询它，这依赖于hll结构估算聚合值的能力。
在我们的示例中，我们首先需要启用扩展：
CREATE EXTENSION hll;
然后，我们可以使用下面的 SQL，创建一个专用表：
CREATE TABLE daily_users (
Purchase_Date date UNIQUE,
users hll
);
并用下面的 SQL 来填充它：
INSERT INTO daily_users
SELECT Purchase_Date, hll_add_agg(hll_hash_text(Username))
FROM EXAMPLE_TBL
GROUP BY 1;
在加载完hll表后（用了614.442 ms），我们现在可以查询它，以检索特定日期的不同用户数：
SELECT Purchase_Date,
hll_cardinality(users)
FROM daily_users;
返回结果集用了18.357 ms。如上所述，hll的美妙之处在于它可以近似聚合，因此，例如，如果我们想了解过去两天的不同用户数量，我们可以查询该表，并使用下面命令让它聚合出结果：
SELECT hll_cardinality(hll_union_agg(users))
FROM daily_users
WHERE Purchase_Date >= CURRENT_DATE -2;
即使我们以每日级别来存储数据，hll数据结构也能够提供多天的聚合估计值。
hll更有趣的地方是，跨数据集执行集合计算的能力。例如，如果我们想了解哪些人昨天购买了，但今天没有，我们可以使用下面的查询来实现：
SELECT Purchase_Date, (#hll_union_agg(users) OVER two_days) - #users AS lost_uniques
FROM daily_users
WINDOW two_days AS (ORDER BY Purchase_Date ASC ROWS 1 PRECEDING);
上面的查询：
- 生成一个两天的窗口（
WINDOW two_days），包含了当前日期和上一个日期（ORDER BY Purchase_Date ASC ROWS 1 PRECEDING） - 计算两天的总用户数（
#hll_union_agg(users)），再减去第二天的用户数。
它只用了157.709 ms，就返回了结果。为了获得相同的结果，我们可以使用自连接和COUNT(DISTINCT)编写一个查询，只是性能要差得多，对数据库的影响也要大得多。