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

SELECT 
    sensor_id,
    location,
    timestamp,
    value,
    AVG(value) OVER w AS moving_avg_5,
    MIN(value) OVER w AS min_5,
    MAX(value) OVER w AS max_5
FROM iot_data
WHERE sensor_id = 'sensor_001'
WINDOW w AS (
    PARTITION BY location 
    ORDER BY timestamp 
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
)
ORDER BY timestamp
LIMIT 10;
```sql

三个函数共享同一个窗口定义 `w`，想改窗口大小只改一处就行。执行时间 269ms，和把窗口写三遍几乎没区别——DuckDB 的优化器会自动合并相同 `OVER` 子句的窗口函数。

## RANGE 帧 vs ROWS 帧：选错帧，结果差很多

这是窗口函数里最容易踩坑的地方。`ROWS BETWEEN` 按行数算窗口，`RANGE BETWEEN` 按值的范围算窗口。听起来差不多，但在时序数据里差别很大。

假设某个传感器每小时上报一次数据，但中间丢了几条。用 `ROWS BETWEEN 5 PRECEDING AND CURRENT ROW` 算的是前 5 条数据的平均值，但这 5 条可能跨越了 8 个小时（因为有数据丢失）。用 `RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW` 算的是过去 1 小时内所有数据的平均值，不管实际有多少条。

```sql
SELECT 
    sensor_id,
    timestamp,
    value,
    AVG(value) OVER (
        PARTITION BY sensor_id 
        ORDER BY timestamp 
        RANGE BETWEEN INTERVAL 1 HOUR PRECEDING AND CURRENT ROW
    ) AS range_1h_avg,
    AVG(value) OVER (
        PARTITION BY sensor_id 
        ORDER BY timestamp 
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS rows_6_avg
FROM iot_data
WHERE sensor_id = 'sensor_001'
ORDER BY timestamp
LIMIT 10;
```sql

执行时间 252ms。选择原则很简单：数据可能有缺失就用 RANGE 帧，数据密集且均匀就用 ROWS 帧。IoT 场景下数据丢失是常态，RANGE 帧更靠谱。

## LAG/LEAD：前后数据对比的利器

计算环比、同比、变化率，LAG 和 LEAD 是标配。LAG 拿前一行的值，LEAD 拿后一行的值，配合简单的算术运算就能算出各种对比指标。

```sql
SELECT 
    sensor_id,
    timestamp,
    value,
    LAG(value, 1) OVER w AS prev_value,
    LEAD(value, 1) OVER w AS next_value,
    value - LAG(value, 1) OVER w AS diff_from_prev,
    FIRST_VALUE(value) OVER w AS first_val,
    LAST_VALUE(value) OVER w AS last_val
FROM iot_data
WHERE sensor_id = 'sensor_001'
WINDOW w AS (PARTITION BY location ORDER BY timestamp)
ORDER BY timestamp
LIMIT 10;
```sql

执行时间 253ms。`FIRST_VALUE` 和 `LAST_VALUE` 也很有用，可以拿到分区内的首尾值，不用额外写子查询。

## Z-Score 异常检测：用窗口函数做实时监控

异常检测听起来很高大上，但用窗口函数实现起来其实很简单。核心思路是：算一个滚动窗口的均值和标准差，然后看当前值偏离均值多少个标准差（Z-Score）。Z-Score 超过 2 或低于 -2 就认为是异常。

```sql
WITH stats AS (
    SELECT 
        sensor_id,
        timestamp,
        value,
        AVG(value) OVER (
            PARTITION BY sensor_id 
            ORDER BY timestamp 
            ROWS BETWEEN 50 PRECEDING AND 50 FOLLOWING
        ) AS rolling_mean,
        STDDEV(value) OVER (
            PARTITION BY sensor_id 
            ORDER BY timestamp 
            ROWS BETWEEN 50 PRECEDING AND 50 FOLLOWING
        ) AS rolling_std
    FROM iot_data
)
SELECT 
    sensor_id,
    timestamp,
    value,
    rolling_mean,
    rolling_std,
    (value - rolling_mean) / rolling_std AS z_score,
    CASE 
        WHEN ABS((value - rolling_mean) / rolling_std) > 2 THEN true 
        ELSE false 
    END AS is_anomaly
FROM stats
WHERE sensor_id = 'sensor_001'
ORDER BY ABS((value - rolling_mean) / NULLIF(rolling_std, 0)) DESC
LIMIT 15;
```sql

执行时间 275ms。窗口大小选的是前后各 50 行（共 101 行），这样能捕捉到局部趋势的变化。测出来的最大 Z-Score 是 -4.16，对应的值是 -21.72，确实是个明显的异常点。

比全局统计（整个传感器的均值和标准差）更敏感，因为它考虑的是局部上下文。一个传感器平时读数在 20-30 之间，突然跳到 50，全局统计可能觉得"还行，在 3 个标准差内"，但局部窗口会发现"这完全偏离了最近的趋势"。

## QUALIFY 子句：告别嵌套子查询

想拿每个传感器的 Top 3 最高值，传统写法是套一层子查询：

```sql
SELECT * FROM (
    SELECT 
        sensor_id,
        value,
        ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY value DESC) AS r
    FROM iot_data
) WHERE r <= 3;
```sql

DuckDB（和 Snowflake、BigQuery）支持 `QUALIFY` 子句，直接过滤窗口函数的结果，不用嵌套：

```sql
SELECT 
    sensor_id,
    location,
    timestamp,
    value,
    ROW_NUMBER() OVER (
        PARTITION BY sensor_id ORDER BY value DESC
    ) AS rank_by_value
FROM iot_data
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY sensor_id ORDER BY value DESC
) <= 3
ORDER BY sensor_id, rank_by_value;
```sql

执行时间 301ms，每个传感器返回 3 行，总共 150 行。性能和子查询差不多（DuckDB 优化器会自动处理），但代码干净很多。

## 累计计算：销售报表的标配

`UNBOUNDED PRECEDING AND CURRENT ROW` 这个帧定义可以实现累计计算——从分区的第一行到当前行。算累计销售额、累计用户数、运行计数都靠它。

```sql
SELECT 
    location,
    sensor_id,
    timestamp,
    value,
    SUM(value) OVER (
        PARTITION BY location 
        ORDER BY timestamp 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum,
    AVG(value) OVER (
        PARTITION BY location 
        ORDER BY timestamp 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_avg,
    COUNT(*) OVER (
        PARTITION BY location 
        ORDER BY timestamp 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_count
FROM iot_data
WHERE location = 'Server_Room' AND sensor_id = 'sensor_001'
ORDER BY timestamp
LIMIT 10;
```sql

执行时间 258ms。`cumulative_sum` 是累计和，`cumulative_avg` 是到当前行为止的平均值，`running_count` 是到当前行为止的行数。

## Gap 检测：发现数据丢失

IoT 场景下数据丢失是家常便饭。用 LAG 函数拿到前一行的时间戳，和当前行的时间戳做差，就能发现数据间隔异常大的地方。

```sql
WITH gaps AS (
    SELECT 
        sensor_id,
        timestamp,
        LAG(timestamp) OVER (
            PARTITION BY sensor_id ORDER BY timestamp
        ) AS prev_ts,
        timestamp - LAG(timestamp) OVER (
            PARTITION BY sensor_id ORDER BY timestamp
        ) AS gap_duration
    FROM iot_data
)
SELECT 
    sensor_id,
    prev_ts AS gap_start,
    timestamp AS gap_end,
    gap_duration
FROM gaps
WHERE gap_duration > INTERVAL '30 minutes'
ORDER BY gap_duration DESC
LIMIT 10;
```sql

执行时间 384ms。测出来最大的数据间隔是 6 小时 27 分 52 秒（sensor_049），最小的是 5 小时 6 分 41 秒（sensor_009）。这种级别的数据丢失，不查一下传感器状态说不过去。

## 性能汇总：100 万行数据，平均 250-380ms

把 8 个场景的执行时间放在一起看：

| 场景 | 执行时间 | 说明 |
|------|---------|------|
| 命名窗口（5 行移动） | 269ms | 多函数共享窗口 |
| RANGE 帧 vs ROWS 帧 | 252ms | 时间范围 vs 行数 |
| LAG/LEAD 对比 | 253ms | 前后数据对比 |
| Z-Score 异常检测 | 275ms | 滚动均值 + 标准差 |
| QUALIFY 过滤 | 301ms | Top N 查询 |
| 累计计算 | 258ms | UNBOUNDED PRECEDING |
| 命名窗口 vs 独立窗口 | 1133ms vs 1085ms | 性能几乎一样 |
| Gap 检测 | 384ms | 数据缺失检测 |

100 万行数据，单表查询，没有索引，平均 250-380ms 出结果。命名窗口和独立窗口的性能差异只有 4.3%，优化器确实做了合并。

有个细节值得提一下：Gap 检测是最慢的（384ms），因为它要对整个传感器分区排序并计算时间差。但 384ms 处理 2 万行数据，换算下来每秒能处理 5 万多行，对于数据质量检查这种非实时场景完全够用。

## 踩坑记录

### 命名窗口的性能误解

一开始以为命名窗口会有额外的性能开销（毕竟要多一次"查找窗口定义"的操作），实测发现完全多虑了。DuckDB 的优化器会把相同 `OVER` 子句的窗口函数合并到一次数据布局中，命名窗口只是语法糖，不影响执行计划。

### RANGE 帧和 ROWS 帧的混用

写第一版代码的时候，把 RANGE 帧和 ROWS 帧混在一个窗口定义里，结果报错。后来才搞清楚，一个窗口函数只能有一种帧类型，不能混用。如果同一个 SELECT 里需要两种帧，就写两个窗口函数。

### QUALIFY 的兼容性问题

QUALIFY 子句是 DuckDB、Snowflake、BigQuery 都支持的特性，但 PostgreSQL 和 MySQL 还不支持。如果代码要跨数据库迁移，还是得用子查询的写法。不过如果只是用 DuckDB 做数据分析，QUALIFY 能省不少代码。

## 总结

窗口函数的核心价值是：在不改变原始数据行的前提下，给每一行加上上下文信息。命名窗口让代码更干净，RANGE 帧处理缺失数据更准确，LAG/LEAD 做前后对比，Z-Score 做异常检测，QUALIFY 过滤窗口结果，累计计算做报表，Gap 检测找数据丢失。

这些场景覆盖了时序分析、数据质量检查、业务报表的大部分需求。100 万行数据 250-380ms 出结果，DuckDB 的性能确实没话说。

## 参考资源

- [DuckDB 官方文档 - Window Functions](https://duckdb.org/docs/lts/sql/functions/window_functions.html)
- [DuckDB 官方博客 - Windowing in DuckDB](https://duckdb.org/2021/10/13/windowing.html)
- [DuckDB 2025 新特性 - Catching up with Windowing](https://duckdb.org/2025/02/10/window-catchup.html)
- [DuckDB 时序分析完全指南](https://duckdblab.org/en/post/duckdb-time-series-guide)
- [DuckDB 窗口函数实战指南](https://www.dench.com/blog/duckdb-window-functions)