SQL如何利用HyperLogLog算法在大数据中实现近似计数？
- 内容介绍
- 相关推荐
本文共计842个文字，预计阅读时间需要4分钟。
PostgreSQL 13原生支持HLL扩展，但默认不安装。必须手动加载扩展，否则会报错：
- 用超级用户执行：
CREATE EXTENSION hll; - 确认已加载：
SELECT * FROM pg_extension WHERE extname = 'hll'; - 建表时字段类型为
hll，不是TEXT或BYTEA—— 后者无法参与聚合运算 - 若需存储原始ID做回溯，
HLL字段应与id字段分开存，它只负责近似去重计数，不保存明细
INSERT时怎么把单个值转成HLL并合并
不能直接 INSERT INTO t(hll_col) VALUES (hll_hash_text('user_123')) 就完事——这只会存一个孤立的 sketch，后续 hll_union 会失效，因为没初始化空结构。
- 插入前先用
hll_empty()初始化，再用hll_add累加：hll_add(hll_empty(), hll_hash_text('user_123')) - 批量插入建议用
hll_add_agg聚合函数：SELECT hll_add_agg(hll_hash_text(user_id)) FROM events WHERE day = '2024-06-01' - 注意哈希函数要统一：
hll_hash_text（文本）、hll_hash_bigint（整数），混用会导致结果偏差放大 - 如果数据来自流式写入，建议每小时/每天归档一次
HLL值到汇总表，避免高频hll_union带来 CPU 峰值
查询近似唯一数时为什么COUNT(DISTINCT)突然变慢了
这不是 HLL 的问题，而是你还在用传统方式查——HLL 的价值在于预计算后秒级响应，不是替代实时 COUNT(DISTINCT)。
- 正确姿势是：提前把各维度的
HLL值存好（如按天、按地区），查时只调#hll_col（井号是取基数操作符） - 错误姿势：
SELECT #hll_union_agg(hll_col) FROM daily_hlls WHERE dt BETWEEN ...—— 这会触发大量 sketch 合并，比直接COUNT(DISTINCT)还慢 -
#操作返回的是DOUBLE PRECISION，不是整数；误差率约 ±0.81%，对亿级数据意味着几万误差，别拿它校验精确报表 - 如果需要“今天UV + 昨天UV − 重叠UV”，得用
hll_intersection，不是简单加减——重叠部分必须显式计算
MySQL或SQLite里没法用HLL怎么办
原生不支持，硬上会踩坑。MySQL 8.0+ 可用 GROUP_REPRESENTATIVE 类似思路但非标准，SQLite 连扩展机制都受限。
- 折中方案：用 Redis 的
PFADD/PFCOUNT做外部 sketch 存储，应用层控制写入节奏 - 更稳的路是降级为采样估算：
COUNT(*) * (COUNT(DISTINCT user_id) FILTER (WHERE random() ，适合临时分析 - 千万别在 MySQL 里用
BIT_COUNT(CONV(SUBSTR(MD5(user_id), 1, 16), 16, 10))自己模拟——哈希分布不均，误差可能超 30% - 如果业务允许延迟，用 ClickHouse 的
uniqCombined函数，它底层就是优化过的 HyperLogLog+Linear Counting 混合实现
实际用下来，最常被忽略的是哈希函数一致性——同一业务字段在不同时间、不同服务里用了 hll_hash_text 和 hll_hash_bytea 混搭，导致合并后基数虚高一倍。上线前务必用固定小样本跑通全链路哈希输出。
本文共计842个文字，预计阅读时间需要4分钟。
PostgreSQL 13原生支持HLL扩展，但默认不安装。必须手动加载扩展，否则会报错：
- 用超级用户执行：
CREATE EXTENSION hll; - 确认已加载：
SELECT * FROM pg_extension WHERE extname = 'hll'; - 建表时字段类型为
hll，不是TEXT或BYTEA—— 后者无法参与聚合运算 - 若需存储原始ID做回溯，
HLL字段应与id字段分开存，它只负责近似去重计数，不保存明细
INSERT时怎么把单个值转成HLL并合并
不能直接 INSERT INTO t(hll_col) VALUES (hll_hash_text('user_123')) 就完事——这只会存一个孤立的 sketch，后续 hll_union 会失效，因为没初始化空结构。
- 插入前先用
hll_empty()初始化，再用hll_add累加：hll_add(hll_empty(), hll_hash_text('user_123')) - 批量插入建议用
hll_add_agg聚合函数：SELECT hll_add_agg(hll_hash_text(user_id)) FROM events WHERE day = '2024-06-01' - 注意哈希函数要统一：
hll_hash_text（文本）、hll_hash_bigint（整数），混用会导致结果偏差放大 - 如果数据来自流式写入，建议每小时/每天归档一次
HLL值到汇总表，避免高频hll_union带来 CPU 峰值
查询近似唯一数时为什么COUNT(DISTINCT)突然变慢了
这不是 HLL 的问题，而是你还在用传统方式查——HLL 的价值在于预计算后秒级响应，不是替代实时 COUNT(DISTINCT)。
- 正确姿势是：提前把各维度的
HLL值存好（如按天、按地区），查时只调#hll_col（井号是取基数操作符） - 错误姿势：
SELECT #hll_union_agg(hll_col) FROM daily_hlls WHERE dt BETWEEN ...—— 这会触发大量 sketch 合并，比直接COUNT(DISTINCT)还慢 -
#操作返回的是DOUBLE PRECISION，不是整数；误差率约 ±0.81%，对亿级数据意味着几万误差，别拿它校验精确报表 - 如果需要“今天UV + 昨天UV − 重叠UV”，得用
hll_intersection，不是简单加减——重叠部分必须显式计算
MySQL或SQLite里没法用HLL怎么办
原生不支持，硬上会踩坑。MySQL 8.0+ 可用 GROUP_REPRESENTATIVE 类似思路但非标准，SQLite 连扩展机制都受限。
- 折中方案：用 Redis 的
PFADD/PFCOUNT做外部 sketch 存储，应用层控制写入节奏 - 更稳的路是降级为采样估算：
COUNT(*) * (COUNT(DISTINCT user_id) FILTER (WHERE random() ，适合临时分析 - 千万别在 MySQL 里用
BIT_COUNT(CONV(SUBSTR(MD5(user_id), 1, 16), 16, 10))自己模拟——哈希分布不均，误差可能超 30% - 如果业务允许延迟，用 ClickHouse 的
uniqCombined函数，它底层就是优化过的 HyperLogLog+Linear Counting 混合实现
实际用下来，最常被忽略的是哈希函数一致性——同一业务字段在不同时间、不同服务里用了 hll_hash_text 和 hll_hash_bytea 混搭，导致合并后基数虚高一倍。上线前务必用固定小样本跑通全链路哈希输出。