第 8 章聚合、窗口函数与数组

学习目标：掌握 ClickHouse「最值钱」的几类 SQL 能力 —— 丰富的聚合函数家族（精确 vs 近似、状态后缀宇宙）、标准 SQL 窗口函数、数组与 Lambda、以及漏斗 / 留存 / 路径分析这套**「OLAP 看家本领」**。学完之后你应该能用 30 行 SQL 写出竞品要花一周写代码才能算的指标。

0. 引子：为什么 OLAP 离不开这一章

OLTP 数据库（MySQL / PG）面对的查询是「给我用户 ID = 42 的订单」，只动几条。OLAP 数据库面对的查询是「过去 30 天每个城市每天的活跃用户数 + 留存率 + 漏斗转化率 + 用户行为路径前三跳的 Top 10」，一动就是几亿行。

这就要求：

聚合函数要丰富（光 count(distinct) 远远不够，需要近似算法、Top-K、分位数全家桶）；
窗口函数要好用（PV/UV、同环比、累计指标必须靠它）；
数组类型要能玩（用户行为序列就是一个数组）；
一些「半结构化」分析模型（漏斗、留存）要内建。

ClickHouse 在这四件事上都堆到了恐怖的高度。

8.1 GROUP BY 全家桶

8.1.1 基本语法

sql

SELECT toDate(ts) AS day, count() AS cnt
FROM events
GROUP BY day
ORDER BY day;

8.1.2 `WITH ROLLUP` / `WITH CUBE` / `WITH TOTALS`

sql

-- ROLLUP：从右到左逐步「上卷」，多一行汇总
SELECT country, city, count() AS cnt
FROM events
GROUP BY country, city
WITH ROLLUP;

-- 输出（示意）：
--  CN   北京   3000
--  CN   上海   2500
--  CN   ''     5500       ← country 维度小计
--  US   纽约   1000
--  US   ''     1000
--  ''   ''     6500       ← 全表合计

sql

-- CUBE：所有维度的笛卡尔组合都给我汇总一遍
SELECT country, city, count() FROM events GROUP BY country, city WITH CUBE;
-- 比 ROLLUP 多了 (city only) 和 () 的组合

-- TOTALS：单独多一行「全部合计」（最简单）
SELECT country, count() FROM events GROUP BY country WITH TOTALS;

📌 三者区别：TOTALS 只多 1 行（全表合计）；ROLLUP 多 N+1 行（按层级上卷）；CUBE 多 2^N 行（所有维度组合）。生成报表四象限指标时 CUBE 一句搞定，但维度多时要慎用，否则结果集爆炸。

8.1.3 `HAVING`

sql

SELECT uid, count() AS pv
FROM events
GROUP BY uid
HAVING pv >= 10
ORDER BY pv DESC
LIMIT 100;

8.1.4 `GROUP BY ALL`（22.8+）

sql

SELECT toDate(ts) AS day, country, count(), uniq(uid)
FROM events
GROUP BY ALL;   -- 等价于 GROUP BY day, country

省得手写 GROUP BY 列表，字段一改自动跟着改，写报表 SQL 时香得不行。

8.2 聚合函数大百科

ClickHouse 内置 150+ 聚合函数。下面只挑高频几类，按使用场景分组。

8.2.1 计数家族

函数	含义	精确？	内存
`count()`	行数	精确	O(1)
`count(col)`	非 NULL 行数	精确	O(1)
`countIf(cond)`	满足条件的行数	精确	O(1)
`countDistinct(col)`	去重计数（=`uniqExact` 的别名）	精确	O(N)
`uniq(col)`	近似去重，HLL++ 算法	误差 ~ 0.5%	O(K)，固定几十 KB
`uniqExact(col)`	精确去重，hash set	精确	O(N)
`uniqCombined(col)`	自适应：小集合精确，大集合 HLL	误差 ~ 0.1%	O(min(N,K))
`uniqHLL12(col)`	HyperLogLog，2^12 buckets	误差 ~ 1.5%	固定 ~3 KB

精确 vs 近似的取舍：

精确 (uniqExact / countDistinct):
  + 100% 准
  - 内存随基数线性增长，10 亿 UV 要几十 GB

近似 (uniq):
  + 几十 KB 解决 10 亿 UV
  + 速度快几个数量级
  - 误差 0.5% (一般报表完全可以接受)

📌 经验法则：日常 UV / DAU / 7 日活，用 uniq；需要审计 / 对账 / 准确反作弊，用 uniqExact。

8.2.2 分位数家族

sql

SELECT
    quantile(0.5)(duration)        AS p50,
    quantile(0.9)(duration)        AS p90,
    quantile(0.99)(duration)       AS p99,
    quantileExact(0.99)(duration)  AS p99_exact
FROM events;

函数	算法	适用
`quantile(level)(col)`	蓄水池抽样 + 插值	通用，最快
`quantileExact`	全排序	量小或要求精确
`quantileTDigest`	T-Digest 算法	近似但分布尾部更准
`quantileTiming(col)`	专用于毫秒级响应时间，最快	APM、日志
`quantileExactWeighted(level)(col, weight)`	带权重的精确分位	加权统计

也支持一次算多个：

sql

SELECT quantiles(0.5, 0.9, 0.99)(duration) FROM events;
-- 返回数组 [123.4, 456.7, 999.0]

8.2.3 Top-K 家族

sql

SELECT topK(5)(url) FROM events;
-- 返回数组：['/home','/p/1','/cart','/login','/p/2']

SELECT topKWeighted(5)(url, view_time) FROM events;
-- 按权重 (view_time) 加权 Top 5

topK 内部是 Filtered Space-Saving 算法，O(K) 内存近似 Top-K，不需要全排序。

8.2.4 极值家族：`argMin` / `argMax`

sql

-- 每个用户最近一次访问的 URL
SELECT
    uid,
    argMax(url, ts) AS last_url,
    max(ts)         AS last_ts
FROM events
GROUP BY uid;

-- 每个商品价格最低的下单时间
SELECT
    sku_id,
    argMin(ts, price) AS cheapest_ts,
    min(price)        AS min_price
FROM orders
GROUP BY sku_id;

⭐ argMin(a, b) = 「当 b 取最小值时 a 的取值」。这是 ClickHouse 「伪去重 + 取最新版本」的经典套路，比 ReplacingMergeTree FINAL 更便宜：

sql

SELECT
    uid,
    argMax(name, version)  AS name,
    argMax(email, version) AS email,
    max(version)           AS v
FROM user_profile
GROUP BY uid;

8.2.5 状态后缀宇宙：`-State / -Merge / -If / -Array / -OrNull / -Resample`

ClickHouse 把聚合函数做成了「算子组合」：在任何聚合函数后加后缀就能改变它的行为。

`-If` 后缀：行内过滤

sql

SELECT
    countIf(status = 200)              AS ok,
    countIf(status >= 500)             AS err5xx,
    avgIf(duration, status = 200)      AS avg_ok_dur,
    uniqIf(uid, country = 'CN')        AS cn_uv
FROM logs;

比 count(case when ...) 写法更短更快。

`-Array` 后缀：作用到数组列

sql

SELECT sumArray(scores) FROM tests;
-- 等价于 arraySum，一个 row 内对数组每个元素求和后再聚合

`-OrNull` / `-OrDefault` 后缀

聚合空集时返回 NULL 或类型默认值，避免 count(*) = 0 时拿到 0 的歧义。

sql

SELECT avgOrNull(price) FROM orders WHERE 1=0;  -- → NULL
SELECT avgOrDefault(price) FROM orders WHERE 1=0;  -- → 0

`-Resample` 后缀：分桶聚合

sql

-- 按 0~100 岁，每 10 岁一桶，统计每桶用户数
SELECT countResample(0, 100, 10)(uid, age) FROM users;
-- → [123, 456, ..., 78]   长度 10 的数组

⭐ `-State` / `-Merge` / `-MergeState`：聚合状态宇宙

这是 ClickHouse 物化视图、AggregatingMergeTree、增量聚合 的灵魂。

sql

-- 1. 用 -State 把聚合「中间状态」存起来（不是最终值！）
SELECT
    toDate(ts) AS day,
    uniqState(uid) AS uv_state,        -- 注意类型是 AggregateFunction(uniq, UInt64)
    sumState(amount) AS gmv_state
FROM events
GROUP BY day;

-- 2. 用 -Merge 把多个状态合并并算出最终值
SELECT
    day,
    uniqMerge(uv_state) AS uv,
    sumMerge(gmv_state) AS gmv
FROM events_daily       -- 该表存的是 -State 的二进制
GROUP BY day;

为什么需要状态？ 因为 uniq 这种聚合不能简单相加 —— 两个 100 万 UV 的并集不是 200 万。-State 把 HLL 中间结构（buckets）存下来，-Merge 把多个 HLL 按位 OR 合并后再估算。

直接看一眼状态长什么样：

sql

SELECT
    hex(uniqState(uid))  AS state_hex,
    length(uniqState(uid)) AS bytes
FROM events;
-- state_hex: 04...AB    bytes: 92

状态后缀的常用搭档：

后缀	作用
`xxxState`	输出聚合中间状态（二进制）
`xxxMerge`	把多个状态合并 → 最终值
`xxxMergeState`	把多个状态合并 → 仍然是状态（用于物化视图链）
`initializeAggregation`	把单值「升格」成 state，方便填表
`finalizeAggregation`	把 state 强行算成最终值（=`-Merge` 单 row）

📌 重要场景：AggregatingMergeTree + MaterializedView 做实时预聚合时，物化视图里写的就是 -State，下游 SELECT 时套 -Merge。这个组合会在第 10 章重点讲。

8.3 窗口函数：终于有了

ClickHouse 在 21.10 之前都不支持窗口函数（!）。21.10 之后补齐了 SQL 标准窗口函数：

sql

SELECT
    uid,
    ts,
    url,
    row_number() OVER (PARTITION BY uid ORDER BY ts)   AS rn,
    rank()       OVER (PARTITION BY uid ORDER BY ts)   AS rk,
    dense_rank() OVER (PARTITION BY uid ORDER BY ts)   AS drk,
    lag(url, 1)  OVER (PARTITION BY uid ORDER BY ts)   AS prev_url,
    lead(url, 1) OVER (PARTITION BY uid ORDER BY ts)   AS next_url,
    sum(duration) OVER (PARTITION BY uid ORDER BY ts
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_dur,
    avg(duration) OVER (PARTITION BY uid ORDER BY ts
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM events;

支持的函数：

函数	用途
`row_number()`	行号，1,2,3,4,5
`rank()`	名次（同分跳号），1,2,2,4
`dense_rank()`	名次（同分不跳），1,2,2,3
`ntile(N)`	把窗口切 N 等份，返回桶号
`lag(col, n, default)`	前 n 行
`lead(col, n, default)`	后 n 行
`first_value(col)` / `last_value(col)`	窗口首 / 末值
`sum / avg / min / max / count`	标准聚合作窗口

窗口范围语法：

sql

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   -- 累计
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW           -- 滚动 7 期
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- 全窗口
RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW   -- 按值范围

📌 与 PG/MySQL 8 的窗口函数对比：语法 100% 兼容，但 ClickHouse 的窗口函数性能不如 PG / Doris（实现较新，没做 segment tree 等深度优化）。能用 GROUP BY 解决的就别用窗口函数，能用 ClickHouse 特有 LIMIT N BY 的就别套 row_number()。

8.4 ClickHouse 特色查询子句

8.4.1 `LIMIT N BY col`：分组取 Top-N，比窗口函数快

sql

SELECT uid, ts, url
FROM events
ORDER BY uid, ts DESC
LIMIT 5 BY uid;     -- 每个 uid 取最新 5 条

等价于 PG 写法：

sql

SELECT uid, ts, url FROM (
    SELECT *, row_number() OVER (PARTITION BY uid ORDER BY ts DESC) AS rn
    FROM events
) t WHERE rn <= 5;

但 ClickHouse 的 LIMIT N BY 是单 pass 流式实现，不需要排序整张表，性能比窗口函数好得多。

8.4.2 `SAMPLE`：抽样查询

sql

SELECT uniq(uid) * 10 AS uv_est
FROM events SAMPLE 0.1;   -- 抽 10% 然后 ×10 估算

要求建表时声明 SAMPLE BY 列（必须是 ORDER BY 列的前缀且为整型 hash 列）。

8.4.3 `WITH FILL`：把缺日期补齐

sql

SELECT toDate(ts) AS day, count() AS cnt
FROM events
WHERE day BETWEEN '2026-04-01' AND '2026-04-07'
GROUP BY day
ORDER BY day WITH FILL FROM '2026-04-01' TO '2026-04-08' STEP 1;

如果 4 月 5 日完全没有事件，结果里也会自动补一行 2026-04-05 0，画图友好得不行。

8.5 数组与 Lambda：把 OLAP 玩出花

ClickHouse 的 Array(T) 是一等公民，配合一组 Lambda 高阶函数，能完成很多别的库要写存储过程的事。

8.5.1 `ARRAY JOIN` / `LEFT ARRAY JOIN`

类比：把购物清单展开成一个商品一行。

sql

WITH [
  ('a', [10, 20, 30]),
  ('b', [40])
] AS data
SELECT name, score
FROM
(
    SELECT data[1].1 AS name, data[1].2 AS scores
    FROM numbers(2)
)
ARRAY JOIN scores AS score;

更典型的用法：

sql

-- 表里 tags 是 Array(String)
SELECT tag, count() AS cnt
FROM events
ARRAY JOIN tags AS tag
GROUP BY tag
ORDER BY cnt DESC;

ARRAY JOIN 只展开有值的，LEFT ARRAY JOIN 在数组为空时也保留原行（值为 defaultValue）。

8.5.2 高阶数组函数（Lambda）

sql

SELECT arrayMap(x -> x * 2, [1, 2, 3]);              -- [2, 4, 6]
SELECT arrayFilter(x -> x > 5, [3, 7, 1, 9]);        -- [7, 9]
SELECT arraySum(x -> x * x, [1, 2, 3]);              -- 14
SELECT arrayCount(x -> x > 5, [3, 7, 1, 9]);         -- 2
SELECT arrayReduce('sum', [1, 2, 3]);                -- 6
SELECT arrayReduce('uniqExact', ['a','b','a']);      -- 2

-- 多数组并行 Lambda
SELECT arrayMap((x, y) -> x + y, [1, 2, 3], [10, 20, 30]);  -- [11, 22, 33]

-- 配合 ARRAY JOIN 做用户行为序列分析
SELECT
    uid,
    arraySort(groupArray(ts))                 AS ts_seq,
    arraySort(groupArray(url))                AS path,
    arraySum(x -> if(x > 0, 1, 0), arraySort(groupArray(duration))) AS engaged_steps
FROM events
GROUP BY uid;

8.5.3 `groupArray` 系列：把行折叠成数组

sql

SELECT
    uid,
    groupArray(url)                       AS pages,
    groupArrayArray(tags)                 AS all_tags,
    groupArrayMovingSum(2)(amount)        AS rolling2_sum,
    groupArraySample(3)(url)              AS sampled3,
    groupArrayLast(5)(url)                AS last5
FROM events
GROUP BY uid;

8.6 用户行为分析三件套（ClickHouse 内建）

ClickHouse 把电商 / 增长团队最常用的三类分析做成了内置函数：

8.6.1 漏斗：`windowFunnel`

sql

SELECT
    level,
    count()
FROM (
    SELECT
        uid,
        windowFunnel(1800)(            -- 30 分钟内
            ts,
            event_type = 'view',       -- 第 1 步
            event_type = 'click',      -- 第 2 步
            event_type = 'addcart',    -- 第 3 步
            event_type = 'pay'         -- 第 4 步
        ) AS level
    FROM events
    GROUP BY uid
)
GROUP BY level
ORDER BY level;

返回每个用户在窗口内最深完成到第几步，外层 GROUP BY 再画漏斗。

📌 算法本质：对每个用户的事件按 ts 排序后，从前往后扫一次，遇到第 1 步就开始 push，遇到第 N+1 步且离第 1 步 ≤ window 就 +1，最终 max。复杂度 O(events log events)。

8.6.2 留存：`retention`

sql

SELECT
    sum(ret[1]) AS d0,
    sum(ret[2]) AS d1,
    sum(ret[3]) AS d7,
    sum(ret[2]) / sum(ret[1]) AS d1_rate,
    sum(ret[3]) / sum(ret[1]) AS d7_rate
FROM (
    SELECT
        uid,
        retention(
            toDate(ts) = '2026-04-01',                    -- 起始条件
            toDate(ts) = '2026-04-02',                    -- D+1 留存
            toDate(ts) = '2026-04-08'                     -- D+7 留存
        ) AS ret
    FROM events
    GROUP BY uid
);

retention 返回 Array(UInt8)，第一位为 1 表示满足起始条件，后续位的 1 必须建立在前一位为 1 的基础上。

8.6.3 路径分析：`sequenceMatch` / `sequenceCount`

sql

-- 多少用户在 1 小时内严格按 view → click → pay 顺序完成？
SELECT
    countIf(matched) AS users_completed
FROM (
    SELECT
        uid,
        sequenceMatch('(?1).*(?2).*(?3)')(
            ts,
            event_type = 'view',
            event_type = 'click',
            event_type = 'pay'
        ) AS matched
    FROM events
    WHERE ts BETWEEN now() - 3600 AND now()
    GROUP BY uid
);

sequenceCount 同语法，但返回匹配次数而非布尔值。

8.7 真实案例：某电商日报 SQL

sql

WITH today AS (toDate(now()))
SELECT
    today                                                      AS day,
    count()                                                    AS pv,
    uniq(uid)                                                  AS uv,
    countIf(event_type = 'pay')                                AS pay_cnt,
    sumIf(amount, event_type = 'pay')                          AS gmv,
    quantile(0.5)(duration)                                    AS p50_dur,
    quantile(0.99)(duration)                                   AS p99_dur,
    topK(5)(url)                                               AS top5_pages,
    sumIf(amount, event_type = 'pay') /
        countIf(event_type = 'pay')                            AS aov
FROM events
WHERE toDate(ts) = today;

一句 SQL 就把 PV/UV/GMV/AOV/P99/Top5 全算出来了 —— 这就是为什么数据团队爱 ClickHouse。

8.8 📌 与 MySQL/PG 聚合 / 窗口函数对比

维度	MySQL 8	PostgreSQL 16	ClickHouse
聚合函数数量	~30	~50	150+
近似算法	无	`approx_count_distinct`（pg_hll 扩展）	uniq / uniqHLL12 / uniqCombined / topK / quantileTDigest 全内建
状态聚合	无	无	-State/-Merge
Top-K	自己写	自己写	`topK(N)(col)`
argMin/argMax	自己写	自己写	内建
漏斗 / 留存	自己写	自己写	windowFunnel / retention 内建
数组类型	`JSON_ARRAY`（弱）	`text[]`、`int[]` 等（强）	`Array(T)` + Lambda 一等公民
ARRAY JOIN	无	`unnest()`	`ARRAY JOIN`
窗口函数	✓	✓（成熟）	✓（21.10+，性能稍弱）
GROUP BY ALL	8.0.21+	不支持	✓
WITH FILL	不支持	不支持	✓（画图神器）

记忆点：MySQL 主打事务，ClickHouse 主打分析，所以 SQL 里凡是「分析味道」的能力（近似算法 / 状态 / 漏斗 / 数组 / FILL），ClickHouse 都给你内建好了。

8.9 本章小结

┌────────────────────────────────────────────────────────────┐
│                       关键拍板（背下来）                       │
├────────────────────────────────────────────────────────────┤
│ ① GROUP BY 全家桶：基础 + ROLLUP / CUBE / TOTALS / ALL       │
│                                                             │
│ ② 聚合函数四大金刚                                            │
│    · 计数：count*  /  uniq* (近似)  /  uniqExact (精确)       │
│    · 分位数：quantile*                                        │
│    · Top-K：topK / topKWeighted                              │
│    · 极值：argMin / argMax  ← 伪去重神器                      │
│                                                             │
│ ③ 后缀宇宙：-If / -Array / -OrNull / -Resample / -State      │
│    · -State + -Merge 是物化视图与增量聚合的灵魂                │
│                                                             │
│ ④ 标准窗口函数（21.10+）齐全，但能 GROUP BY 就别窗口          │
│                                                             │
│ ⑤ ClickHouse 特色：                                           │
│    · LIMIT N BY col  → 比窗口函数快的「分组 Top-N」            │
│    · WITH FILL       → 自动补齐缺日期                         │
│    · SAMPLE          → 抽样估算                               │
│                                                             │
│ ⑥ 数组 + Lambda 高阶函数 ≈ 内建 SQL 版的 Pandas              │
│                                                             │
│ ⑦ 用户行为三件套：windowFunnel / retention / sequence*       │
│                                                             │
└────────────────────────────────────────────────────────────┘

8.10 面试高频题

Q1：`uniq` / `uniqExact` / `uniqHLL12` / `uniqCombined` 有什么区别？怎么选？

考察点：是否真理解精确去重 vs 近似去重的代价权衡。

标准答案：

函数	算法	内存	误差	适用
`uniqExact`	全量 hash set	O(N)	0%	反作弊、对账、量小
`uniq`	HyperLogLog++ 自适应	O(K) ≈ 几十 KB	~0.5%	报表 UV / DAU 默认选择
`uniqHLL12`	标准 HyperLogLog（2^12 桶）	固定 ~3 KB	~1.5%	极致省内存
`uniqCombined`	小集合用 hash，大集合切到 HLL	自适应	~0.1%	中等数据量、比 uniq 准

怎么选：

默认上 uniq，0.5% 误差几乎所有报表都能接受。
内存敏感（大表 + 高并发）→ uniqHLL12。
必须精确（财务、风控）→ uniqExact。
想要近似但精度更高 → uniqCombined。

加分项：能解释 uniq 之所以能 O(K) 内存解决任意基数，是因为 HLL 利用了 hash 值的「前导零最大数」与基数的统计关系。能补一句「uniqExact 在分布式场景中是性能杀手，因为要把整个 hash set shuffle 到 coordinator」。

易错点：以为 countDistinct 是精确的另一个高效实现，实际它 = uniqExact 别名，仍然是 O(N)。

Q2：`-State` / `-Merge` 后缀是什么？什么时候用？

考察点：是否理解 ClickHouse 增量聚合体系。

标准答案：

ClickHouse 的聚合函数支持「返回中间状态」而非最终值。uniqState(uid) 返回的是 HLL 桶的二进制；avgState(x) 返回 (sum, count)；quantileTDigestState 返回 T-Digest 结构。
状态可以继续合并：uniqMerge(state) 把多个 state 按位 OR 后再估算，得到全局结果。
典型用途：
- AggregatingMergeTree：表里直接存 AggregateFunction(uniq, UInt64) 等状态列，由 ClickHouse 自动 Merge。
- 物化视图：在 MV 中用 xxxState，在查询时套 xxxMerge，实现「实时预聚合 + 后台合并」。
- 增量计算：每天落一份 -State 后，跨天求 30 日 UV 时直接 uniqMerge，不需要回溯原始事件表。
配套的 -MergeState 用于链式物化视图（合并多个状态、再产生一个状态给下游）。

加分项：能说出「uniqExactState 占用空间正比于基数，所以 AggregatingMergeTree 里别用 uniqExact」；能提到 initializeAggregation 用于把单值升格成 state。

易错点：把 xxxState 的输出直接 SELECT 出来当数据用 —— 它是二进制状态，需要 xxxMerge 才有意义。

Q3：`argMax` 怎么实现「按版本取最新」？为什么比 `FINAL` 好？

考察点：实战去重技巧。

标准答案：

sql

SELECT
    uid,
    argMax(name,    version) AS name,
    argMax(email,   version) AS email,
    argMax(country, version) AS country,
    max(version)             AS v
FROM user_profile
GROUP BY uid;

argMax(name, version) 等价于「按 version 排序，取最大那一行的 name」。比起 ReplacingMergeTree FINAL：

维度	`argMax`	`ReplacingMergeTree FINAL`
是否触发 Merge	否	是（查询时合并所有 Part）
性能	快，单 GROUP BY pass	慢，需要按主键合并
是否要求建表时声明	否	必须 `ENGINE = ReplacingMergeTree(version)`
灵活度	任意列、任意排序键	只能按建表时定的 version 列

结论：日常 ad-hoc 去重 → argMax；要长期稳定的「最新一行」语义 → ReplacingMergeTree，但只在底层数据上少用 FINAL，宁愿在物化视图里预聚合。

加分项：能补一句 argMax 在多列时其实有更优雅的写法 —— 用 Tuple 一次取：

sql

SELECT uid, argMax((name, email, country), version) AS row, max(version) AS v
FROM user_profile
GROUP BY uid;

易错点：argMax(col, ts) 当多个 row 的 ts 相同时返回任意一个，不是确定性的。需要用 argMax(col, (ts, id)) 加 tie-breaker。

Q4：ClickHouse 的窗口函数和 PG 的有什么差别？什么时候应该用 `LIMIT N BY` 而不是 `row_number`？

考察点：ClickHouse 性能优化的小心思。

标准答案：

ClickHouse 在 21.10 才支持 SQL 标准窗口函数，实现较新，没有 PG / Doris 那种成熟的优化器（segment tree / 增量更新等）。复杂窗口下性能不如 PG。
ClickHouse 提供了一个特色子句 LIMIT N BY col，专门解决「每个分组取前 N 条」的场景：
- 内部是单 pass 流式实现，不需要为了 row_number 给整张表排序；
- 配合 ORDER BY col, sort_key 后，扫描时一边输出一边裁剪；
- 对千万行级数据，性能比窗口写法快 5~10 倍。
对于累计求和 / 滚动均值 / 同环比 等真窗口需求，老老实实用窗口函数；对于「每个用户最近 5 条 / 每个商品 top 3」这种「分组截断」需求，首选 LIMIT N BY。

加分项：能补一句「窗口函数性能可以通过 WINDOW 子句复用同一个分区，避免重复排序」。

易错点：用 OFFSET N BY 误以为也是分组分页 —— LIMIT N OFFSET M BY col 的 OFFSET 才是分组生效。

Q5：`windowFunnel` 是怎么工作的？怎么算转化率？

考察点：增长 / 数据分析方向常考的内置函数。

标准答案：

windowFunnel(window_seconds)(timestamp, cond1, cond2, ..., condN) 的算法：

按 timestamp 升序遍历当前 GROUP（一般是一个用户）的所有事件；
维护一个状态 level（最深完成的步数）和一个数组 started_at[] 记录每一步达到的时间；
遇到 cond1 → 开新链，level 设为 1；
遇到 cond_{level+1} 且 now - started_at[1] ≤ window_seconds → level += 1；
全部扫完返回 level（0 表示没完成第 1 步）。
外层 GROUP BY level 即可得到漏斗各步的人数；除以第 1 步即转化率。

一段完整漏斗 + 转化率：

sql

SELECT
    sumIf(c, level >= 1) AS step1,
    sumIf(c, level >= 2) AS step2,
    sumIf(c, level >= 3) AS step3,
    sumIf(c, level >= 4) AS step4,
    step2 / step1 AS conv_1_2,
    step3 / step2 AS conv_2_3,
    step4 / step3 AS conv_3_4
FROM (
    SELECT
        windowFunnel(1800)(
            ts,
            event_type = 'view',
            event_type = 'click',
            event_type = 'addcart',
            event_type = 'pay'
        ) AS level,
        count()  AS c
    FROM events
    WHERE toDate(ts) = today()
    GROUP BY uid
)
GROUP BY level
WITH TOTALS;

加分项：

能区分 windowFunnel(window)('strict_order')（严格顺序，中间不能插）、'strict_deduplication'（去掉同一步的重复）、'strict_increase'（步骤间时间必须严格递增）。
能提到大数据量下用「漏斗物化视图」预聚合，避免每次扫源表。

易错点：把 window 单位看错（是秒），写成毫秒导致结果全 0。

Q6：`ARRAY JOIN` 和 `LEFT ARRAY JOIN` 区别是什么？跟 PG 的 `unnest()` 比呢？

考察点：数组展开操作的细节。

标准答案：

ARRAY JOIN 把行内的数组列「展开」成多行，每个数组元素一行。

sql

SELECT id, tag FROM t ARRAY JOIN tags AS tag;
-- 一行 (1, ['a','b','c']) → 三行 (1,'a') (1,'b') (1,'c')

ARRAY JOIN：数组为空的行会被丢弃。
LEFT ARRAY JOIN：数组为空也保留，展开列取 defaultValue（如 String 是 ''）。

跟 PG unnest() 的差别：

维度	ClickHouse `ARRAY JOIN`	PG `unnest()`
语法位置	FROM 后面，`ARRAY JOIN col AS x`	SELECT 列表里或 LATERAL JOIN
多数组并行展开	直接 `ARRAY JOIN a, b`（按位对齐）	`unnest(a, b)` 也支持
空数组处理	`ARRAY JOIN` 丢弃，`LEFT` 保留	`unnest` 默认丢弃，`LEFT JOIN LATERAL` 可保留
性能	极快（向量化展开）	较慢（一次一个 tuple）

加分项：能补一句 ARRAY JOIN 配合 groupArray 是 ClickHouse 「先聚合成数组，再按需展开」的经典套路；以及 arrayJoin(arr) 函数和 ARRAY JOIN 子句等价但用在表达式位置。

易错点：以为可以在 ARRAY JOIN 之前用 WHERE 过滤展开后的列 —— 必须用子查询或 HAVING。

📌 下一章预告：第 9 章我们要解决 ClickHouse 老大难 —— JOIN。讲清为什么大家说「CK 不擅长 JOIN」，怎么用 Dictionary 字典优雅地干掉 90% 的 JOIN，以及 ASOF JOIN 这把时序场景的瑞士军刀。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

agg_play.py

python

#!/usr/bin/env python3
"""
第 8 章 · 典型聚合 / 窗口 / 漏斗 / 留存 / 数组操作样板

跑之前请先：
    clickhouse-client < init.sql
    python3 ../seed.py

依赖：pip install clickhouse-connect
"""
from __future__ import annotations

import time

import clickhouse_connect

HOST = "127.0.0.1"
PORT = 8123


def show(client, title: str, sql: str) -> None:
    print(f"\n=== {title} ===")
    print(sql.strip())
    t0 = time.time()
    res = client.query(sql)
    cost = (time.time() - t0) * 1000
    print(f"--- {cost:.1f} ms, {len(res.result_rows)} rows")
    for row in res.result_rows[:10]:
        print(" ", row)
    if len(res.result_rows) > 10:
        print(f"  ... ({len(res.result_rows) - 10} more)")


def main() -> None:
    client = clickhouse_connect.get_client(host=HOST, port=PORT, username="default")

    show(client, "1. 基础聚合（PV/UV/GMV 一句出）", """
        SELECT
            count()                        AS pv,
            uniq(uid)                      AS uv,
            uniqExact(uid)                 AS uv_exact,
            countIf(event_type='pay')      AS pay_cnt,
            sumIf(amount, event_type='pay') AS gmv
        FROM learn_ck.user_actions
    """)

    show(client, "2. uniq vs uniqExact vs uniqHLL12 误差对比", """
        SELECT
            uniq(uid)        AS approx,
            uniqExact(uid)   AS exact,
            uniqHLL12(uid)   AS hll12,
            (uniq(uid) - uniqExact(uid)) / uniqExact(uid) AS err_uniq,
            (uniqHLL12(uid) - uniqExact(uid)) / uniqExact(uid) AS err_hll
        FROM learn_ck.user_actions
    """)

    show(client, "3. WITH ROLLUP 多维上卷", """
        SELECT country, city, count() AS cnt
        FROM learn_ck.user_actions
        GROUP BY country, city WITH ROLLUP
        ORDER BY country, city
        LIMIT 20
    """)

    show(client, "4. 分位数 + Top-K 一句搞定", """
        SELECT
            quantiles(0.5, 0.9, 0.99)(duration) AS [p50, p90, p99],
            topK(5)(url)  AS top5_pages,
            argMax(url, ts) AS last_page
        FROM learn_ck.user_actions
    """)

    show(client, "5. 窗口函数：每个用户访问的累计时长 + 上一页", """
        SELECT
            uid, ts, url,
            row_number() OVER (PARTITION BY uid ORDER BY ts) AS rn,
            lag(url, 1)  OVER (PARTITION BY uid ORDER BY ts) AS prev,
            sum(duration) OVER (PARTITION BY uid ORDER BY ts
                                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                               ) AS cum_dur
        FROM learn_ck.user_actions
        WHERE uid = (SELECT min(uid) FROM learn_ck.user_actions)
        ORDER BY ts
        LIMIT 10
    """)

    show(client, "6. LIMIT N BY：每个 uid 取最近 3 条，比窗口函数快", """
        SELECT uid, ts, url
        FROM learn_ck.user_actions
        ORDER BY uid, ts DESC
        LIMIT 3 BY uid
        LIMIT 12
    """)

    show(client, "7. ARRAY JOIN：把 tags 数组展开成行", """
        SELECT tag, count() AS cnt
        FROM learn_ck.user_actions
        ARRAY JOIN tags AS tag
        GROUP BY tag
        ORDER BY cnt DESC
        LIMIT 10
    """)

    show(client, "8. 高阶数组：Lambda 算用户活跃度", """
        SELECT
            uid,
            length(groupArray(url))                                  AS pv,
            arrayUniq(groupArray(url))                               AS distinct_url,
            arrayCount(x -> x > 60, groupArray(duration))            AS heavy_steps,
            arraySum(x -> x, groupArray(duration))                   AS total_dur
        FROM learn_ck.user_actions
        GROUP BY uid
        ORDER BY pv DESC
        LIMIT 5
    """)

    show(client, "9. windowFunnel：4 步漏斗", """
        SELECT level, count() AS users
        FROM (
            SELECT
                uid,
                windowFunnel(1800)(
                    ts,
                    event_type='view',
                    event_type='click',
                    event_type='addcart',
                    event_type='pay'
                ) AS level
            FROM learn_ck.user_actions
            GROUP BY uid
        )
        GROUP BY level
        ORDER BY level
    """)

    show(client, "10. retention：D0 / D1 / D7 留存", """
        SELECT
            sum(ret[1]) AS d0,
            sum(ret[2]) AS d1,
            sum(ret[3]) AS d7,
            round(sum(ret[2]) / sum(ret[1]), 4) AS d1_rate,
            round(sum(ret[3]) / sum(ret[1]), 4) AS d7_rate
        FROM (
            SELECT
                uid,
                retention(
                    toDate(ts) = today() - 7,
                    toDate(ts) = today() - 6,
                    toDate(ts) = today()
                ) AS ret
            FROM learn_ck.user_actions
            GROUP BY uid
        )
    """)

    show(client, "11. WITH FILL：把空日期补齐", """
        SELECT toDate(ts) AS day, count() AS pv
        FROM learn_ck.user_actions
        WHERE day BETWEEN today() - 14 AND today()
        GROUP BY day
        ORDER BY day WITH FILL FROM today() - 14 TO today() + 1 STEP 1
    """)

    show(client, "12. -State / -Merge：从聚合表取最终值", """
        SELECT
            day,
            countMerge(pv)               AS pv,
            uniqMerge(uv)                AS uv,
            sumMerge(gmv)                AS gmv,
            quantileMerge(0.99)(p99_dur) AS p99
        FROM learn_ck.user_actions_daily
        GROUP BY day
        ORDER BY day
        LIMIT 7
    """)


if __name__ == "__main__":
    main()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179

agg_play.py ↗

第 8 章 聚合、窗口函数与数组 ​

0. 引子：为什么 OLAP 离不开这一章 ​

8.1 GROUP BY 全家桶 ​

8.1.1 基本语法 ​

8.1.2 WITH ROLLUP / WITH CUBE / WITH TOTALS ​

8.1.3 HAVING ​

8.1.4 GROUP BY ALL（22.8+） ​

8.2 聚合函数大百科 ​

8.2.1 计数家族 ​

8.2.2 分位数家族 ​

8.2.3 Top-K 家族 ​

8.2.4 极值家族 ：argMin / argMax ​

8.2.5 状态后缀宇宙：-State / -Merge / -If / -Array / -OrNull / -Resample ​

-If 后缀：行内过滤 ​

-Array 后缀：作用到数组列 ​

-OrNull / -OrDefault 后缀 ​

-Resample 后缀：分桶聚合 ​

⭐ -State / -Merge / -MergeState：聚合状态宇宙 ​

8.3 窗口函数：终于有了 ​

8.4 ClickHouse 特色查询子句 ​

8.4.1 LIMIT N BY col：分组取 Top-N，比窗口函数快 ​

8.4.2 SAMPLE：抽样查询 ​

8.4.3 WITH FILL：把缺日期补齐 ​

8.5 数组与 Lambda：把 OLAP 玩出花 ​

8.5.1 ARRAY JOIN / LEFT ARRAY JOIN ​

8.5.2 高阶数组函数（Lambda） ​

8.5.3 groupArray 系列：把行折叠成数组 ​

8.6 用户行为分析三件套（ClickHouse 内建） ​

8.6.1 漏斗：windowFunnel ​

8.6.2 留存：retention ​

8.6.3 路径分析：sequenceMatch / sequenceCount ​

8.7 真实案例：某电商日报 SQL ​

8.8 📌 与 MySQL/PG 聚合 / 窗口函数对比 ​

8.9 本章小结 ​

8.10 面试高频题 ​

Q1：uniq / uniqExact / uniqHLL12 / uniqCombined 有什么区别？怎么选？ ​

Q2：-State / -Merge 后缀是什么？什么时候用？ ​

Q3：argMax 怎么实现「按版本取最新」？为什么比 FINAL 好？ ​

Q4：ClickHouse 的窗口函数和 PG 的有什么差别？什么时候应该用 LIMIT N BY 而不是 row_number？ ​

Q5：windowFunnel 是怎么工作的？怎么算转化率？ ​

Q6：ARRAY JOIN 和 LEFT ARRAY JOIN 区别是什么？跟 PG 的 unnest() 比呢？ ​

🎬 可视化演示 ​

💻 示例代码 ​

第 8 章聚合、窗口函数与数组

0. 引子：为什么 OLAP 离不开这一章

8.1 GROUP BY 全家桶

8.1.1 基本语法

8.1.2 `WITH ROLLUP` / `WITH CUBE` / `WITH TOTALS`

8.1.3 `HAVING`

8.1.4 `GROUP BY ALL`（22.8+）

8.2 聚合函数大百科

8.2.1 计数家族

8.2.2 分位数家族

8.2.3 Top-K 家族

8.2.4 极值家族：`argMin` / `argMax`

8.2.5 状态后缀宇宙：`-State / -Merge / -If / -Array / -OrNull / -Resample`

`-If` 后缀：行内过滤

`-Array` 后缀：作用到数组列

`-OrNull` / `-OrDefault` 后缀

`-Resample` 后缀：分桶聚合

⭐ `-State` / `-Merge` / `-MergeState`：聚合状态宇宙

8.3 窗口函数：终于有了

8.4 ClickHouse 特色查询子句

8.4.1 `LIMIT N BY col`：分组取 Top-N，比窗口函数快

8.4.2 `SAMPLE`：抽样查询

8.4.3 `WITH FILL`：把缺日期补齐

8.5 数组与 Lambda：把 OLAP 玩出花

8.5.1 `ARRAY JOIN` / `LEFT ARRAY JOIN`

8.5.2 高阶数组函数（Lambda）

8.5.3 `groupArray` 系列：把行折叠成数组

8.6 用户行为分析三件套（ClickHouse 内建）

8.6.1 漏斗：`windowFunnel`

8.6.2 留存：`retention`

8.6.3 路径分析：`sequenceMatch` / `sequenceCount`

8.7 真实案例：某电商日报 SQL

8.8 📌 与 MySQL/PG 聚合 / 窗口函数对比

8.9 本章小结

8.10 面试高频题

Q1：`uniq` / `uniqExact` / `uniqHLL12` / `uniqCombined` 有什么区别？怎么选？

Q2：`-State` / `-Merge` 后缀是什么？什么时候用？

Q3：`argMax` 怎么实现「按版本取最新」？为什么比 `FINAL` 好？

Q4：ClickHouse 的窗口函数和 PG 的有什么差别？什么时候应该用 `LIMIT N BY` 而不是 `row_number`？

Q5：`windowFunnel` 是怎么工作的？怎么算转化率？

Q6：`ARRAY JOIN` 和 `LEFT ARRAY JOIN` 区别是什么？跟 PG 的 `unnest()` 比呢？

🎬 可视化演示

💻 示例代码