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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
| trade_id,symbol,trade_type,quantity,price,commission,trade_date,trade_time,account_id,market,total_value
TRD00000001,NVDA,BUY,420,488.55,4.56,2023-11-30,15:19:03,ACC00043,AMEX,205191.00
```text
环境：DuckDB 1.5.5，腾讯云 Ubuntu 24.04，4 核 AMD EPYC，7.5GB 内存。
## 窗口函数的骨架
所有窗口函数都长一个样：
```sql
函数名(...) OVER (
PARTITION BY 分组字段
ORDER BY 排序字段
ROWS BETWEEN ... AND ... -- Frame 定义
)
```sql
三层结构，缺一不可。`PARTITION BY` 决定数据怎么分组，`ORDER BY` 决定组内怎么排序，Frame 决定每一行"看"多大范围的数据。
搞懂这三层，窗口函数就通了大半。
## 排名三兄弟：ROW_NUMBER、RANK、DENSE_RANK
这三个函数最容易混淆，放在一起对比最清楚。
```sql
SELECT
symbol,
trade_date,
total_value,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY total_value DESC) as row_num,
RANK() OVER (PARTITION BY symbol ORDER BY total_value DESC) as rank_val,
DENSE_RANK() OVER (PARTITION BY symbol ORDER BY total_value DESC) as dense_rank_val
FROM financial_trades
WHERE symbol = 'AAPL'
ORDER BY total_value DESC
LIMIT 10;
```sql
区别用一句话说：
- `ROW_NUMBER()`：1, 2, 3, 4... 永远唯一，不管有没有并列
- `RANK()`：1, 2, 2, 4... 并列跳号
- `DENSE_RANK()`：1, 2, 2, 3... 并列不跳号
查询耗时 8.10ms。20 万行数据，8 毫秒出结果，没什么好抱怨的。
### 实战：找出每只股票交易额 Top 3
```sql
WITH ranked_trades AS (
SELECT
symbol,
trade_date,
total_value,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY total_value DESC) as rn
FROM financial_trades
)
SELECT * FROM ranked_trades
WHERE rn <= 3
ORDER BY symbol, rn;
```sql
14.09ms，干净利落。
这个写法比"先 GROUP BY 再 JOIN 回去"的子查询方案简洁太多。CTE + 窗口函数 + WHERE 过滤，三步走，逻辑清晰。
## 前后行对比：LAG 和 LEAD
做时间序列分析，环比变化是刚需。`LAG` 取前一行，`LEAD` 取后一行。
```sql
WITH daily_trades AS (
SELECT
symbol,
trade_date,
SUM(total_value) as daily_total,
COUNT(*) as trade_count
FROM financial_trades
WHERE symbol IN ('AAPL', 'MSFT', 'GOOGL')
GROUP BY symbol, trade_date
)
SELECT
symbol,
trade_date,
daily_total,
LAG(daily_total, 1) OVER (PARTITION BY symbol ORDER BY trade_date) as prev_day_total,
ROUND(
(daily_total - LAG(daily_total, 1) OVER (PARTITION BY symbol ORDER BY trade_date))
/ LAG(daily_total, 1) OVER (PARTITION BY symbol ORDER BY trade_date) * 100,
2
) as day_over_day_pct
FROM daily_trades
ORDER BY symbol, trade_date
LIMIT 20;
```text
结果长这样：
```text
AAPL 2023-01-01 $6,655,718 NULL NULL
AAPL 2023-01-02 $5,588,070 $6,655,718 -16.04%
AAPL 2023-01-03 $5,247,924 $5,588,070 -6.09%
AAPL 2023-01-08 $7,427,184 $5,247,924 +67.94%
```sql
15.35ms。
注意第一行的 `prev_day_total` 是 NULL——因为它是分组内的第一行，没有"前一行"可以取。这个 NULL 在后续计算百分比时会自动传播，不需要额外处理。
## 累计求和与移动平均
这两个是窗口函数最经典的应用场景。
### 累计求和
```sql
SELECT
symbol,
trade_date,
total_value,
SUM(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date, trade_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as cumulative_sum
FROM financial_trades
WHERE symbol = 'TSLA'
ORDER BY trade_date, trade_id
LIMIT 10;
```sql
296.22ms。
这里有个细节：`ORDER BY trade_date, trade_id`。为什么要加 `trade_id`？因为同一天可能有多笔交易，如果只按 `trade_date` 排序，同一天的行顺序是不确定的，累计求和的结果就会飘。
### 移动平均
```sql
SELECT
symbol,
trade_date,
total_value,
AVG(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date, trade_id
ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
) as moving_avg_5
FROM financial_trades
WHERE symbol = 'AMZN'
ORDER BY trade_date, trade_id
LIMIT 15;
```sql
13.66ms。
`ROWS BETWEEN 4 PRECEDING AND CURRENT ROW` 是 5 行窗口（当前行 + 前 4 行），不是 4 行。这个数数方式新手容易搞混，记住"包含当前行"就行。
### 7 日移动平均线
股票分析里最常用的指标之一：
```sql
WITH daily_avg AS (
SELECT
symbol,
trade_date,
AVG(total_value) as avg_value
FROM financial_trades
WHERE symbol = 'TSLA'
GROUP BY symbol, trade_date
)
SELECT
trade_date,
avg_value,
AVG(avg_value) OVER (
ORDER BY trade_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as ma7
FROM daily_avg
ORDER BY trade_date
LIMIT 15;
```sql
4.54ms。先聚合到日级别，再算移动平均，两步走。
## 性能对比：窗口函数 vs 子查询
这是整篇文章最有说服力的部分。
同一个需求——按股票分组，按交易额排名——两种写法：
```sql
-- 方式一：窗口函数
SELECT
symbol,
trade_date,
total_value,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY total_value DESC) as rn
FROM financial_trades;
-- 方式二：关联子查询
SELECT
t1.symbol,
t1.trade_date,
t1.total_value,
(SELECT COUNT(*) + 1
FROM financial_trades t2
WHERE t2.symbol = t1.symbol
AND t2.total_value > t1.total_value) as rn
FROM financial_trades t1;
```text
结果：
| 方式 | 耗时 |
|------|------|
| 窗口函数 | 86.06ms |
| 关联子查询 | 74,502.94ms |
**865 倍的差距。**
原因很简单：窗口函数只需要扫描一次数据，排序一次就能出结果。关联子查询是 O(n²) 的嵌套循环——每一行都要去全表扫一遍计数。20 万行数据，400 亿次比较，不慢才怪。
## 全量性能测试
把几个常用窗口函数在 20 万行全量数据上跑一遍：
| 窗口函数 | 执行时间 |
|---------|---------|
| ROW_NUMBER 全量 | 348.5ms |
| RANK + DENSE_RANK 全量 | 354.2ms |
| LAG + LEAD 全量 | 339.9ms |
| Running SUM 全量 | 348.6ms |
| Moving AVG(7) 全量 | 303.9ms |
| 多窗口（共享分区） | 447.5ms |
所有窗口函数都在 450ms 内完成。DuckDB 的向量化执行引擎对窗口函数有专门优化，2025 年 2 月发布的 Segment Tree Vectorization 让累计类窗口函数可以流式计算，不用缓存全部数据。
Moving AVG 最快（303.9ms），因为它的 Frame 固定是 7 行，内存占用最小。多窗口共享分区反而稍慢（447.5ms），可能是多个窗口函数的 Frame 定义不同，无法合并计算。
## 踩坑记录
### 坑一：排序不稳定
这是新手最容易犯的错。
```sql
-- 错误写法
SUM(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date -- 同一天多笔交易，顺序不确定
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
```text
同一天有多笔交易时，`ORDER BY trade_date` 无法确定这些行的先后顺序。DuckDB 会按内部存储顺序排列，但这个顺序不保证稳定，累计求和的结果就会飘。
```sql
-- 正确写法
SUM(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date, trade_id -- 加 trade_id 保证唯一
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
```text
加一个次要排序字段，让排序结果唯一。这个习惯要养成。
### 坑二：LAST_VALUE 的默认 Frame
```sql
-- 错误写法
LAST_VALUE(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date
) as last_trade
```text
这个写法看起来没问题，但 `LAST_VALUE` 的默认 Frame 是 `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`——也就是说，"最后一行"其实是当前行本身。结果就是 `last_trade` 等于 `total_value`，完全没意义。
```sql
-- 正确写法
LAST_VALUE(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_trade
```sql
显式指定 Frame 到 `UNBOUNDED FOLLOWING`，才是真正的"最后一行"。
同理，`FIRST_VALUE` 的默认 Frame 是符合直觉的（从第一行到当前行），所以一般不会出问题。但 `LAST_VALUE` 和 `NTH_VALUE` 的默认 Frame 都需要注意。
### 坑三：移动平均的行数计算
`ROWS BETWEEN 4 PRECEDING AND CURRENT ROW` 是 5 行，不是 4 行。
- `4 PRECEDING` = 前 4 行
- `CURRENT ROW` = 当前行
- 合计 = 5 行
要算 7 日移动平均，写 `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`。要算 30 日移动平均，写 `ROWS BETWEEN 29 PRECEDING AND CURRENT ROW`。
这个数数方式确实反直觉，但记住"包含当前行"就不会搞错。
## 几个实用技巧
**1. 窗口函数可以叠加**
同一个查询里可以用多个窗口函数，只要 `PARTITION BY` 和 `ORDER BY` 相同，DuckDB 会尽量合并计算：
```sql
SELECT
symbol,
trade_date,
total_value,
ROW_NUMBER() OVER w as rn,
LAG(total_value, 1) OVER w as prev_value,
SUM(total_value) OVER w as running_total
FROM financial_trades
WINDOW w AS (PARTITION BY symbol ORDER BY trade_date, trade_id)
```sql
用 `WINDOW` 子句定义公共窗口，避免重复写 `OVER (...)`。
**2. PERCENT_RANK 做百分位分析**
```sql
SELECT
symbol,
trade_date,
total_value,
PERCENT_RANK() OVER (PARTITION BY symbol ORDER BY total_value) as pct_rank
FROM financial_trades
WHERE symbol = 'META'
ORDER BY total_value
LIMIT 10;
```sql
6.27ms。`PERCENT_RANK` 返回 0 到 1 之间的值，表示当前行在分组中的百分位。找"交易额在前 10% 的交易"，直接 `WHERE pct_rank >= 0.9` 就行。
**3. FIRST_VALUE 和 LAST_VALUE 配合使用**
```sql
SELECT
symbol,
trade_date,
total_value,
FIRST_VALUE(total_value) OVER (PARTITION BY symbol ORDER BY trade_date) as first_trade,
LAST_VALUE(total_value) OVER (
PARTITION BY symbol
ORDER BY trade_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_trade
FROM financial_trades
WHERE symbol = 'GOOGL'
ORDER BY trade_date
LIMIT 10;
```text
6.72ms。一眼看出分组内的首尾值。
## 总结
窗口函数不是花哨的语法糖，是实打实的性能利器。
20 万行数据，窗口函数 86ms 搞定的事，子查询要跑 74 秒。865 倍的差距，不是优化能弥补的，是算法层面的碾压。
常用场景记住这几个就够了：
- **排名**：`ROW_NUMBER` / `RANK` / `DENSE_RANK`
- **前后行对比**：`LAG` / `LEAD`
- **累计求和**：`SUM() OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`
- **移动平均**：`AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)`
- **首尾值**：`FIRST_VALUE` / `LAST_VALUE`（注意 Frame）
踩坑就两点：排序要稳定（加次要排序字段），Frame 要显式指定（别信默认值）。
DuckDB 对窗口函数的优化做得很到位，向量化执行 + 流式计算，20 万行数据全部在 450ms 内完成。如果是更大的数据集（百万级），性能优势会更明显。
## 参考资源
- [DuckDB 窗口函数官方文档](https://duckdb.org/docs/lts/sql/functions/window_functions.html)
- [DuckDB 窗口函数流式优化博客（2025.02）](https://duckdb.org/2025/02/14/window-flying.html)
- [Window Functions 101: Analytics Tricks in DuckDB SQL](https://medium.com/@hadiyolworld007/window-functions-101-analytics-tricks-in-duckdb-sql-6637294fae54)
- [MotherDuck: Window Functions in SQL](https://motherduck.com/blog/motherduck-window-functions-in-sql)
|
