ClickHouse聚合函数：uniqHLL12、quantileTDigest、topK

ClickHouse聚合函数完整概览，以真实博彩表为例。涵盖标准count/sum/avg、专门用于唯一计数的函数（uniqExact、uniqHLL12、uniqCombined）及按精度和速度的选择表、分位数（quantile、quantileTDigest、quantileExact）用于分布分析、groupArray和移动求和（groupArrayMovingSum）、通过topK获取顶部元素、通过-If组合器实现条件聚合（sumIf、countIf、avgIf）、AggregateFunction类型及-State/-Merge组合器用于物化视图、-OrDefault/-OrNull组合器、runningAccumulate用于累积指标。包含每日GGR（总博彩收入）、群组留存分析和移动7天留存率的现成查询。包含常见错误警告（count(DISTINCT)、大数据集上的quantileExact）。

By Sudonull Editorial Team • Published 2026-06-08 • Updated 2026-06-08

ClickHouse聚合：从count()到带-State/-Merge的AggregateFunction

ClickHouse聚合函数：我是如何不再害怕uniqHLL12和quantileTDigest的

在博彩分析中，我们需要统计每小时独立玩家数。在PostgreSQL中，我会写COUNT(DISTINCT user_id)然后去喝杯咖啡。在ClickHouse中，面对5亿行数据，同样的查询只需30秒。但业务需要一个每5秒刷新一次的数据面板。

那时我发现了uniqHLL12()——近似计数，误差1-2%，但只需0.2秒。我们切换后，数据面板飞速运行。主管没注意到数字差异，但注意到了速度。

ClickHouse不仅提供标准数学函数，还有数十种PostgreSQL从未想过的专用聚合函数。下面是我在实际项目中使用的所有内容。

1. 标准聚合：熟悉但更快

-- 所有投注的总体统计
SELECT 
    count() AS total_bets,                    -- 投注数
    sum(amount) AS total_staked,              -- 总投注金额
    avg(amount) AS avg_bet,                   -- 平均投注
    min(created_at) AS first_bet_time,        -- 首次投注
    max(created_at) AS last_bet_time,         -- 最后投注
    max(amount) - min(amount) AS range        -- 范围
FROM betting.bets
WHERE created_at >= today() - 7;

与PostgreSQL的区别： count(*)和count()工作方式相同。但count(DISTINCT user_id)很慢——请使用专用函数。

2. 独立用户：精度与速度的权衡

ClickHouse有三种计算独立值的方法：

-- 精确但慢（5亿行需30秒）
SELECT count(DISTINCT user_id) FROM betting.bets;

-- 精确但无语法糖
SELECT uniqExact(user_id) FROM betting.bets;

-- 近似，快速（0.2秒，误差1-2%）
SELECT uniq(user_id) FROM betting.bets;

-- HyperLogLog，可控误差（我们使用这个）
SELECT uniqHLL12(user_id) FROM betting.bets;

-- 更快，但误差更大
SELECT uniqCombined(user_id) FROM betting.bets;

何时使用什么：

函数	误差	速度	我的应用场景
`uniqExact()`	0%	慢	税务报告，精确支出
`uniqHLL12()`	1-2%	非常快	数据面板，趋势，KPI
`uniq()`	2-4%	快	实时分析
`uniqCombined()`	5-8%	即时	探索性分析，估算

实际生产示例： 在实时数据面板中统计每小时独立玩家数，我们使用uniqHLL12(user_id)。99%的精度可接受，数据面板每3秒刷新一次，而不是30秒。

3. 分位数：无需直方图的投注分布

问题“多少玩家投注少于100卢布，多少多于1000？”就是关于分位数。

-- 第50百分位数（中位数）
SELECT quantile(0.5)(amount) FROM betting.bets;

-- 第90百分位数（90%的投注低于此金额）
SELECT quantile(0.9)(amount) FROM betting.bets;

-- 同时计算多个分位数
SELECT quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(amount) FROM betting.bets;

-- 近似分位数（快10倍）
SELECT quantileTDigest(0.9)(amount) FROM betting.bets;

-- 精确但慢（内存排序）
SELECT quantileExact(0.9)(amount) FROM betting.bets;

我踩过的坑： quantileExact()在10亿行数据上会耗尽所有内存。我们切换到quantileTDigest()——误差0.5%，内存占用100 MB而不是8 GB。

实际用例： 确定欺诈检测阈值。如果99%的投注低于50,000卢布，而某玩家投注500,000卢布——送审。

4. groupArray：将值收集到数组中

有时你不需要聚合，而是保留所有值。

-- 用户每天的所有投注，放入数组
SELECT 
    user_id,
    toDate(created_at) AS day,
    groupArray(amount) AS amounts,
    groupArray(odds) AS odds_list,
    arrayMap(x -> x * 2, amounts) AS doubled  -- 处理数组
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id, day
LIMIT 10;

高级用法： 移动和与移动平均。

-- 每个用户最近3次投注的移动和
SELECT 
    user_id,
    created_at,
    amount,
    groupArrayMovingSum(3)(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS moving_sum
FROM betting.bets
WHERE user_id = 1001
ORDER BY created_at;

何时真正需要： 分析投注序列——机器人连续投注相同金额，真人玩家则变化。

5. topK：无需精确计数

问题：“最受欢迎的5种运动是什么？”GROUP BY sport ORDER BY count() DESC LIMIT 5可行，但在10亿行数据上，它会为百万个独立值构建哈希表。

-- 近似top（快速）
SELECT topK(5)(sport) FROM betting.bets;

-- 结果：['football', 'basketball', 'tennis', 'hockey', 'mma']

-- 精确但慢
SELECT sport, count() AS cnt 
FROM betting.bets 
GROUP BY sport 
ORDER BY cnt DESC 
LIMIT 5;

速度差异： 在100亿行数据上，topK执行0.5秒，精确GROUP BY需要15秒。对于自动刷新的数据面板，选择显而易见。

6. -If组合器：无需子查询的条件聚合

用sumIf()代替SUM(CASE WHEN ...)——可读性更好，运行更快。

-- 赢和输的投注在一行中
SELECT 
    user_id,
    countIf(outcome = 'win') AS wins,
    countIf(outcome = 'loss') AS losses,
    sumIf(amount, outcome = 'win') AS winning_stake,
    sumIf(amount, outcome = 'loss') AS losing_stake,
    avgIf(odds, outcome = 'win') AS avg_win_odds,
    -- 通过条件计算胜率
    round(wins / (wins + losses), 4) AS win_rate
FROM betting.bets
WHERE created_at >= today() - 7
GROUP BY user_id
HAVING wins + losses > 50
ORDER BY win_rate DESC
LIMIT 20;

其他-If函数： avgIf(), minIf(), maxIf(), anyIf(), uniqIf(), quantileIf()。

7. AggregateFunction类型和-State/-Merge组合器：用于物化视图

ClickHouse最强大的工具：你可以存储中间聚合状态，而不是数据。

-- 存储聚合状态的表
CREATE TABLE betting.daily_agg
(
    day Date,
    user_id UInt64,
    total_bets AggregateFunction(count, UInt64),
    total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_sports AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, user_id);

-- 使用-State插入
INSERT INTO betting.daily_agg
SELECT 
    toDate(created_at) AS day,
    user_id,
    countState() AS total_bets,
    sumState(amount) AS total_amount,
    uniqState(sport) AS unique_sports
FROM betting.bets
GROUP BY day, user_id;

-- 使用-Merge获取结果
SELECT 
    day,
    user_id,
    countMerge(total_bets) AS bets,
    sumMerge(total_amount) AS total_staked,
    uniqMerge(unique_sports) AS unique_sports_count
FROM betting.daily_agg
GROUP BY day, user_id;

我的使用场景： 用于小时级聚合的物化视图。无需每次重新计算20亿行数据，而是存储状态并简单合并。

8. 其他组合器：-OrDefault, -OrNull, -Array

-- -OrDefault：返回默认值而非NULL
SELECT avgOrDefault(amount, 0) FROM betting.bets WHERE 1=0;  -- 0，不是NULL

-- -OrNull：如果没有行则返回NULL
SELECT avgOrNull(amount) FROM betting.bets WHERE 1=0;  -- NULL

-- -Array：对数组元素进行聚合
SELECT groupArrayArray([[1,2], [3,4], [5,6]]) AS flattened;
-- 展平：[1,2,3,4,5,6]

9. runningAccumulate：累积和（窗口函数的增强版）

ClickHouse支持窗口函数，但runningAccumulate是更早（有时更快）的形式。

-- 按天累积投注和
SELECT 
    toDate(created_at) AS day,
    sum(amount) AS daily_amount,
    runningAccumulate(sum(amount)) OVER (ORDER BY day) AS cumulative_amount
FROM betting.bets
WHERE created_at >= today() - 30
GROUP BY day
ORDER BY day;

为什么我有时更喜欢窗口函数： runningAccumulate需要严格排序且不支持PARTITION BY。现在我写sum(amount) OVER (ORDER BY day)。

10. 实际示例：GGR、同期群、滚动留存

示例1：每日GGR（总博彩收入）

GGR = 总投注 - 总支出。

SELECT 
    toDate(created_at) AS day,
    sum(amount) AS total_staked,
    sumIf(amount * odds, outcome = 'win') AS total_paid,
    total_staked - total_paid AS ggr,
    round(ggr / total_staked, 4) AS hold_percentage
FROM betting.bets
GROUP BY day
ORDER BY day DESC
LIMIT 30;

示例2：同期群分析（按天玩家留存）

WITH cohorts AS (
    SELECT 
        user_id,
        toDate(min(created_at)) AS cohort_day
    FROM betting.bets
    GROUP BY user_id
),
user_activity AS (
    SELECT 
        b.user_id,
        c.cohort_day,
        toDate(b.created_at) AS activity_day,
        datediff('day', c.cohort_day, activity_day) AS day_number
    FROM betting.bets b
    JOIN cohorts c ON b.user_id = c.user_id
    WHERE day_number <= 30
)
SELECT 
    cohort_day,
    day_number,
    uniqHLL12(user_id) AS active_users
FROM user_activity
GROUP BY cohort_day, day_number
ORDER BY cohort_day DESC, day_number;

示例3：滚动7天留存

SELECT 
    toDate(created_at) AS day,
    uniqHLL12(user_id) AS dau,
    -- 7天前也活跃的用户
    uniqHLL12If(user_id, 
        created_at >= today() - 7 AND created_at < today() - 6
    ) AS retained_users,
    round(retained_users / uniqHLL12If(user_id, 
        created_at >= today() - 14 AND created_at < today() - 13
    ), 4) AS retention_7d
FROM betting.bets
GROUP BY day
ORDER BY day DESC;

聚合常见错误

错误1： 在数十亿行数据上使用COUNT(DISTINCT col)。解决方案：uniqHLL12(col)或需要精度时用uniqExact(col)。

错误2： 在高基数列（user_id）上使用GROUP BY而不加过滤。解决方案：始终添加WHERE或带限制的HAVING。

错误3： 在大数据上使用quantileExact()。解决方案：quantileTDigest()或quantile(0.9)（不带Exact）。

错误4： 在聚合中不加理解地使用arrayJoin。解决方案：记住arrayJoin会倍增行数。最好对数据进行反规范化。

下一步

聚合是分析的核心。下一篇文章——关于ClickHouse中的窗口函数和统计检验。

← 上一篇: ClickHouse中的SELECT查询：在PostgreSQL十年后如何重塑思维
→ 下一篇: ClickHouse 配置：我是如何搭建生产环境且不踩坑的

— Editorial Team

社区

暂无评论。

继续阅读

主题

数据库分析

常见问题

ClickHouse中uniqHLL12和count(DISTINCT)有什么区别？

count(DISTINCT)给出精确结果但速度慢——5亿行需要30秒。uniqHLL12()是近似的（误差1-2%），但只需0.2秒。对于仪表盘和趋势使用uniqHLL12，对于财务报告使用uniqExact。

ClickHouse中应该使用哪个分位数函数？

quantileExact是精确的，但在大数据集上消耗内存。quantileTDigest是近似的（误差0.5%），内存100 MB而非8 GB。生产环境使用quantileTDigest，精确计算则在聚合数据上使用quantileExact。

什么是AggregateFunction类型，为什么需要-State/-Merge？

AggregateFunction存储的不是数据，而是聚合的中间状态（例如uniq的哈希表）。-State在插入时创建状态，-Merge恢复聚合。用于物化视图的增量更新——而不是每分钟重新计算20亿行。

何时使用topK而不是GROUP BY配合ORDER BY LIMIT？

topK在100亿行上以几分之一秒给出近似顶部结果。GROUP BY是精确但慢（15秒）。对于自动刷新的仪表盘使用topK。对于需要排序的精确报告，使用GROUP BY。

ClickHouse聚合函数：uniqHLL12、quantileTDigest、topK

ClickHouse聚合函数：我是如何不再害怕uniqHLL12和quantileTDigest的

1. 标准聚合：熟悉但更快

2. 独立用户：精度与速度的权衡

3. 分位数：无需直方图的投注分布

4. groupArray：将值收集到数组中

5. topK：无需精确计数

6. -If组合器：无需子查询的条件聚合

7. AggregateFunction类型和-State/-Merge组合器：用于物化视图

8. 其他组合器：-OrDefault, -OrNull, -Array

9. runningAccumulate：累积和（窗口函数的增强版）

10. 实际示例：GGR、同期群、滚动留存

示例1：每日GGR（总博彩收入）

示例2：同期群分析（按天玩家留存）

示例3：滚动7天留存

聚合常见错误

下一步

社区

继续阅读

最近浏览

标签

主题

常见问题

ClickHouse聚合函数：我是如何不再害怕uniqHLL12和quantileTDigest的

1. 标准聚合：熟悉但更快

2. 独立用户：精度与速度的权衡

3. 分位数：无需直方图的投注分布

4. groupArray：将值收集到数组中

5. topK：无需精确计数

6. -If组合器：无需子查询的条件聚合

7. AggregateFunction类型和-State/-Merge组合器：用于物化视图

8. 其他组合器：-OrDefault, -OrNull, -Array

9. runningAccumulate：累积和（窗口函数的增强版）

10. 实际示例：GGR、同期群、滚动留存

示例1：每日GGR（总博彩收入）

示例2：同期群分析（按天玩家留存）

示例3：滚动7天留存

聚合常见错误

下一步

社区

继续阅读

ClickHouse中的SELECT查询：与PostgreSQL的差异，PREWHERE，SAMPLE

将数据加载到ClickHouse：批量、async_insert、监控

ClickHouse中的MergeTree：颗粒、部分和稀疏索引

ClickHouse 数据类型：分析完整参考

ClickHouse客户端和HTTP API：连接与首次查询

ClickHouse in Docker: 2分钟内启动分析

在Ubuntu 22.04和Debian 12上安装ClickHouse：指南

ClickHouse：为什么列式数据库管理系统能将分析速度提升100倍

自适应液态变压器：人工智能能效的突破

Alphabet筹集850亿美元：AI竞赛的求救信号还是企业自救？

最近浏览

标签

主题

常见问题

分享本文