码到成功

ClickHouse——分析函数

2021-05-14

很多 SQL 一旦碰到“分组后还想保留明细行”这个需求，写法就会开始打结。

比如你想做这些事：

每个人的销售额，同时对比所在部门平均值
每条订单旁边带上累计金额
每一行都知道自己在分组里的排名
看看当前值和上一行、下一行到底差了多少
做一个滚动平均，但又不想把明细抹平

这时候，窗口函数就不是“高级语法加分项”了，而是最顺手的工具。

ClickHouse 官方文档对它的定义很清楚：窗口函数允许你针对一组与当前行相关的记录做计算，但不会像普通聚合那样把多行折叠成一行，明细仍然保留。Window functions | ClickHouse Docs

这句话特别值钱，因为它正好点出了窗口函数的气质：

既要算聚合，又不想丢行。

窗口函数总览图

先把窗口函数的感觉抓住

如果你以前总把窗口函数看成“有点复杂的聚合”，那很容易越写越乱。

更稳的理解方式是：

GROUP BY 是把行收拢
窗口函数是把“同组上下文”临时贴回每一行

比如一张销售表：

department | employee | amount

你想知道“每个员工金额减去部门平均值”，如果用普通聚合，最后只能拿到部门级结果；如果用窗口函数，就能做到：

平均值按部门算
每个员工那一行还在
差值直接写在那一行旁边

这就是窗口函数最实用的地方。

ClickHouse 这页官方文档，最值得先记住什么

官方文档在窗口函数页一开始给了一张支持情况表，这张表很有含金量。Window functions | ClickHouse Docs

里面几个特别值得先记住的点：

ClickHouse 支持 ad hoc 窗口定义，也支持 WINDOW 子句复用窗口定义
ROWS 支持
RANGE 支持，而且它是默认 frame
GROUPS frame 不支持
对 DateTime 的 RANGE OFFSET，不能直接写 INTERVAL 语法；官方建议用秒数来指定
聚合函数可以直接跑在窗口 frame 上
row_number、rank、dense_rank、percent_rank、cume_dist、lag/lead、ntile 都支持

如果你只先记一句话，我会建议记这句：

ClickHouse 的窗口函数已经足够覆盖大部分分析 SQL，但 frame 细节一定要看清。

语法骨架其实不复杂

官方文档给的核心语法骨架大概就是下面这样：Window functions | ClickHouse Docs

aggregate_function(column_name)
OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS or RANGE ...]
)

或者先起一个窗口名：

SELECT ...
WINDOW w AS (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN ...
)

里面最核心的 4 个零件是：

PARTITION BY
ORDER BY
ROWS / RANGE
WINDOW

理解这 4 个以后，绝大多数窗口 SQL 都能顺下来。

PARTITION BY：先决定谁跟谁算一组

PARTITION BY 的作用，就是决定窗口函数的“分组范围”。

官方示例里用 part_key 做分组，然后对每组内的 value 计算 groupArray(value)，最终同一个分组里的每一行都能看到这组值的完整数组。Window functions | ClickHouse Docs

这个例子特别适合建立直觉：

PARTITION BY 决定“跟谁一起玩”
同一个 partition 内部，窗口函数结果会共享这段上下文

一个很实用的部门例子：

SELECT
    department,
    employee,
    amount,
    avg(amount) OVER (PARTITION BY department) AS dept_avg,
    amount - dept_avg AS diff_from_avg
FROM sales;

这个查询的味道就是：

平均值按部门算
但员工明细不消失

这也是窗口函数最经典的一类用法。

ORDER BY：同一组里谁先谁后，得讲秩序

只分组不排序时，你能做的事情有限。

一旦你想做下面这些操作：

排名
累计和
前一行 / 后一行对比
滚动平均

ORDER BY 就必须站出来。

因为窗口里的“前”“后”“当前”这些概念，全都依赖排序。

举个最经典的例子：

SELECT
    employee,
    amount,
    row_number() OVER (ORDER BY amount ASC) AS row_no,
    rank() OVER (ORDER BY amount ASC) AS rk,
    dense_rank() OVER (ORDER BY amount ASC) AS dense_rk
FROM sales;

官方文档也给了类似示例，展示 row_number、rank 和 dense_rank 在并列值出现时的区别。Window functions | ClickHouse Docs

简单说就是：

row_number()：机械编号，不管并列
rank()：并列同名次，但会跳号
dense_rank()：并列同名次，不跳号

这个差别在榜单、Top N、组内排序场景里非常常见。

ROWS 和 RANGE：窗口到底框住哪些行

这部分是很多人最容易半懂不懂的地方。

官方文档明确写了：

ROWS frame 支持
RANGE frame 支持，而且是默认值
Window functions | ClickHouse Docs

ROWS：按物理行数看窗口

如果你写：

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

意思更偏向：

“看当前行，再往前数两行。”

这很适合：

最近 3 条记录平均值
最近 N 行累计
固定行数对比

比如：

SELECT
    user_id,
    seq,
    amount,
    sum(amount) OVER (
        PARTITION BY user_id
        ORDER BY seq
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum_3_rows
FROM payments;

RANGE：按排序值的范围看窗口

RANGE 就不是简单数几行了，而是看排序列的值范围。

官方文档还专门给了移动平均示例，比如“按最近 10 个单位范围”的平均值。Window functions | ClickHouse Docs

这在按数值区间、按秒级范围做分析时会很顺手。

但这里有一个 ClickHouse 细节特别值得记住：

官方文档明确说，针对 DateTime 的 RANGE OFFSET，不能直接写 INTERVAL 语法；建议直接写秒数，因为 RANGE 本身是对数值类型工作的。Window functions | ClickHouse Docs

也就是说，别写成你在别的数据库里熟悉的那种：

-- 这个思路在 ClickHouse 这里不要想当然照搬
RANGE BETWEEN INTERVAL 10 SECOND PRECEDING AND CURRENT ROW

更稳的是显式把排序依据变成数值语义，或者直接按文档建议的方式用秒数范围理解它。

frame 边界怎么读，别靠猜

官方文档里那张 frame 示意图其实非常好，核心就是这几个边界词：Window functions | ClickHouse Docs

UNBOUNDED PRECEDING
N PRECEDING
CURRENT ROW
M FOLLOWING
UNBOUNDED FOLLOWING

一个很常见的累计和写法：

SELECT
    user_id,
    seq,
    amount,
    sum(amount) OVER (
        PARTITION BY user_id
        ORDER BY seq
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_amount
FROM payments;

这个语义很清楚：

从当前分组第一行
一直累到当前行

如果你看到 UNBOUNDED PRECEDING，脑子里就把它翻译成“从开头开始”，会舒服很多。

Frame 边界图

聚合函数放进窗口里，是最常用的一类玩法

官方文档明确说明，所有聚合函数都支持在窗口 frame 上计算。Window functions | ClickHouse Docs

这意味着：

sum
avg
max
min
count
groupArray

都能直接用在 OVER (...) 上。

这是 ClickHouse 窗口函数最香的一点之一，因为分析查询里最常见的很多动作，本质上就是：

先按某个维度划组
再把聚合结果贴回每一行

比如“每条订单旁边显示客户累计下单金额”：

SELECT
    customer_id,
    order_seq,
    amount,
    sum(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_seq
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS customer_running_total
FROM orders;

这类 SQL 在报表、用户行为分析、交易链路里都很常见。

percent_rank 和 cume_dist：做相对位置分析时很好用

官方文档把 percent_rank() 单独拎出来强调了一下，说它能高效计算分组内某个值的相对位置，而且能替代一段更啰嗦的手写 SQL。Window functions | ClickHouse Docs

这很适合：

百分位位置判断
成绩分位
指标相对排名

而 cume_dist() 表示的是“当前值以下（含当前值）累计占比”。官方文档也明确说明了这一点。Window functions | ClickHouse Docs

如果你经常做“这条记录已经超过了多少比例的数据”这种分析，这两个函数会比手搓公式优雅很多。

lag / lead 和 lagInFrame / leadInFrame：名字像，但别混了

这是 ClickHouse 窗口函数里一个非常值得单独讲的点。

官方文档写得很明确：

lag/lead(value, offset) 支持
lagInFrame/leadInFrame 也支持
但 lagInFrame/leadInFrame 会遵守当前 frame
如果你想要和普通 lag/lead 一样的行为，需要把 frame 显式写成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Window functions | ClickHouse Docs

这个区别非常关键。

简单记忆：

lag / lead：更像你熟悉的标准偏移
lagInFrame / leadInFrame：偏移要受 frame 约束

一个很适合看环比的写法：

SELECT
    user_id,
    seq,
    amount,
    lag(amount, 1) OVER (
        PARTITION BY user_id
        ORDER BY seq
    ) AS prev_amount,
    amount - prev_amount AS diff_from_prev
FROM payments;

如果你换成 lagInFrame，那就要特别留意你当前 frame 到底怎么写，不然结果可能和你预期不一样。

WINDOW 子句：当多个表达式共用同一窗口时，别重复写

官方文档确认 WINDOW 子句是支持的。Window functions | ClickHouse Docs

这在一条 SQL 里要重复用同一套窗口定义时，会非常省事。

比如：

SELECT
    department,
    employee,
    amount,
    avg(amount) OVER w AS dept_avg,
    max(amount) OVER w AS dept_max,
    rank() OVER w AS dept_rank
FROM sales
WINDOW w AS (
    PARTITION BY department
    ORDER BY amount DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

这比每个 OVER (...) 都重复写一遍窗口定义要干净很多，也更不容易在后期维护时改漏。

ClickHouse 还给了一个专属窗口函数：nonNegativeDerivative

官方文档列出的 ClickHouse 专属窗口函数是 nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])。Window functions | ClickHouse Docs

它的作用很适合计数器类指标，比如：

请求总量
字节累计值
监控计数器

官方描述里提到：

第一行结果为 0
后续行按时间差和指标差计算非负导数
Window functions | ClickHouse Docs

如果你做监控或时序分析，这个函数会比你自己一层层拼前后行差值舒服很多。

用 Python 跑窗口查询，会更容易有手感

ClickHouse 官方 Python 集成页给的核心驱动是 clickhouse-connect，并且安装方式很直接：pip install clickhouse-connect。Python integration with ClickHouse Connect

官方文档也提到：

主接口是 Client
它负责最核心的查询和写入
这个驱动走的是 HTTP 协议
Python integration with ClickHouse Connect

先连上 ClickHouse

import clickhouse_connect

client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,
    username='default',
    password=''
)

建一张适合演示窗口函数的表

client.command("""
CREATE TABLE IF NOT EXISTS sales_demo
(
    department String,
    employee String,
    seq UInt32,
    amount UInt32
)
ENGINE = MergeTree
ORDER BY (department, seq)
""")

插一点演示数据

rows = [
    ['ops', 'amy', 1, 120],
    ['ops', 'amy', 2, 180],
    ['ops', 'ben', 3, 160],
    ['sales', 'carl', 1, 200],
    ['sales', 'dora', 2, 260],
    ['sales', 'emma', 3, 260],
]

client.insert('sales_demo', rows)

查排名、平均值和累计和

result = client.query("""
SELECT
    department,
    employee,
    seq,
    amount,
    avg(amount) OVER (PARTITION BY department) AS dept_avg,
    rank() OVER (PARTITION BY department ORDER BY amount DESC) AS dept_rank,
    sum(amount) OVER (
        PARTITION BY department
        ORDER BY seq
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales_demo
ORDER BY department, seq
""")

for row in result.result_rows:
    print(row)

这段 Python 的好处在于，你可以很直观地看到：

窗口函数不会把明细吞掉
同一行里可以同时带多个窗口结果
同一个查询里，排名、平均值、累计值可以一起算

再看一眼 lag

result = client.query("""
SELECT
    department,
    employee,
    seq,
    amount,
    lag(amount, 1) OVER (
        PARTITION BY department
        ORDER BY seq
    ) AS prev_amount
FROM sales_demo
ORDER BY department, seq
""")

print(result.result_rows)