码到成功
ClickHouse——分析函数
很多 SQL 一旦碰到“分组后还想保留明细行”这个需求，写法就会开始打结。
比如你想做这些事：
- 每个人的销售额，同时对比所在部门平均值
- 每条订单旁边带上累计金额
- 每一行都知道自己在分组里的排名
- 看看当前值和上一行、下一行到底差了多少
- 做一个滚动平均，但又不想把明细抹平
这时候，窗口函数就不是“高级语法加分项”了，而是最顺手的工具。
ClickHouse 官方文档对它的定义很清楚：窗口函数允许你针对一组与当前行相关的记录做计算，但不会像普通聚合那样把多行折叠成一行，明细仍然保留。[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
这句话特别值钱，因为它正好点出了窗口函数的气质：
**既要算聚合，又不想丢行。**
## 先把窗口函数的感觉抓住
如果你以前总把窗口函数看成“有点复杂的聚合”，那很容易越写越乱。
更稳的理解方式是：
- `GROUP BY` 是把行收拢
- 窗口函数是把“同组上下文”临时贴回每一行
比如一张销售表：
```sql
department | employee | amount
```
你想知道“每个员工金额减去部门平均值”，如果用普通聚合，最后只能拿到部门级结果；如果用窗口函数，就能做到：
- 平均值按部门算
- 每个员工那一行还在
- 差值直接写在那一行旁边
这就是窗口函数最实用的地方。
## ClickHouse 这页官方文档，最值得先记住什么
官方文档在窗口函数页一开始给了一张支持情况表，这张表很有含金量。[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
里面几个特别值得先记住的点：
- 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](https://clickhouse.com/docs/sql-reference/window-functions)
```sql
aggregate_function(column_name)
OVER (
[PARTITION BY ...]
[ORDER BY ...]
[ROWS or RANGE ...]
)
```
或者先起一个窗口名：
```sql
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](https://clickhouse.com/docs/sql-reference/window-functions)
这个例子特别适合建立直觉：
- `PARTITION BY` 决定“跟谁一起玩”
- 同一个 partition 内部，窗口函数结果会共享这段上下文
一个很实用的部门例子：
```sql
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` 就必须站出来。
因为窗口里的“前”“后”“当前”这些概念，全都依赖排序。
举个最经典的例子：
```sql
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](https://clickhouse.com/docs/sql-reference/window-functions)
简单说就是：
- `row_number()`：机械编号，不管并列
- `rank()`：并列同名次，但会跳号
- `dense_rank()`：并列同名次，不跳号
这个差别在榜单、Top N、组内排序场景里非常常见。
## ROWS 和 RANGE：窗口到底框住哪些行
这部分是很多人最容易半懂不懂的地方。
官方文档明确写了：
- `ROWS` frame 支持
- `RANGE` frame 支持，而且是默认值
[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
### ROWS：按物理行数看窗口
如果你写：
```sql
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
```
意思更偏向：
“看当前行，再往前数两行。”
这很适合：
- 最近 3 条记录平均值
- 最近 N 行累计
- 固定行数对比
比如：
```sql
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](https://clickhouse.com/docs/sql-reference/window-functions)
这在按数值区间、按秒级范围做分析时会很顺手。
但这里有一个 ClickHouse 细节特别值得记住：
官方文档明确说，针对 `DateTime` 的 `RANGE OFFSET`，不能直接写 `INTERVAL` 语法；建议直接写秒数，因为 `RANGE` 本身是对数值类型工作的。[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
也就是说，别写成你在别的数据库里熟悉的那种：
```sql
-- 这个思路在 ClickHouse 这里不要想当然照搬
RANGE BETWEEN INTERVAL 10 SECOND PRECEDING AND CURRENT ROW
```
更稳的是显式把排序依据变成数值语义，或者直接按文档建议的方式用秒数范围理解它。
## frame 边界怎么读，别靠猜
官方文档里那张 frame 示意图其实非常好，核心就是这几个边界词：[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
- `UNBOUNDED PRECEDING`
- `N PRECEDING`
- `CURRENT ROW`
- `M FOLLOWING`
- `UNBOUNDED FOLLOWING`
一个很常见的累计和写法：
```sql
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 上计算。[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
这意味着：
- `sum`
- `avg`
- `max`
- `min`
- `count`
- `groupArray`
都能直接用在 `OVER (...)` 上。
这是 ClickHouse 窗口函数最香的一点之一，因为分析查询里最常见的很多动作，本质上就是：
- 先按某个维度划组
- 再把聚合结果贴回每一行
比如“每条订单旁边显示客户累计下单金额”：
```sql
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](https://clickhouse.com/docs/sql-reference/window-functions)
这很适合：
- 百分位位置判断
- 成绩分位
- 指标相对排名
而 `cume_dist()` 表示的是“当前值以下（含当前值）累计占比”。官方文档也明确说明了这一点。[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
如果你经常做“这条记录已经超过了多少比例的数据”这种分析，这两个函数会比手搓公式优雅很多。
## 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](https://clickhouse.com/docs/sql-reference/window-functions)
这个区别非常关键。
简单记忆：
- `lag / lead`：更像你熟悉的标准偏移
- `lagInFrame / leadInFrame`：偏移要受 frame 约束
一个很适合看环比的写法：
```sql
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](https://clickhouse.com/docs/sql-reference/window-functions)
这在一条 SQL 里要重复用同一套窗口定义时，会非常省事。
比如：
```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](https://clickhouse.com/docs/sql-reference/window-functions)
它的作用很适合计数器类指标，比如：
- 请求总量
- 字节累计值
- 监控计数器
官方描述里提到：
- 第一行结果为 `0`
- 后续行按时间差和指标差计算非负导数
[Window functions | ClickHouse Docs](https://clickhouse.com/docs/sql-reference/window-functions)
如果你做监控或时序分析，这个函数会比你自己一层层拼前后行差值舒服很多。
## 用 Python 跑窗口查询，会更容易有手感
ClickHouse 官方 Python 集成页给的核心驱动是 `clickhouse-connect`，并且安装方式很直接：`pip install clickhouse-connect`。[Python integration with ClickHouse Connect](https://clickhouse.com/docs/integrations/python)
官方文档也提到：
- 主接口是 `Client`
- 它负责最核心的查询和写入
- 这个驱动走的是 HTTP 协议
[Python integration with ClickHouse Connect](https://clickhouse.com/docs/integrations/python)
### 先连上 ClickHouse
```python
import clickhouse_connect
client = clickhouse_connect.get_client(
host='localhost',
port=8123,
username='default',
password=''
)
```
### 建一张适合演示窗口函数的表
```python
client.command("""
CREATE TABLE IF NOT EXISTS sales_demo
(
department String,
employee String,
seq UInt32,
amount UInt32
)
ENGINE = MergeTree
ORDER BY (department, seq)
""")
```
### 插一点演示数据
```python
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)
```
### 查排名、平均值和累计和
```python
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
```python
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)
```