ClickHouse lagInFrame与标准SQL LAG的差异及查询结果分歧场景
什么是窗口框架？
窗口框架是窗口函数的核心组成部分之一，它在窗口分区（PARTITION BY指定的分组）内，基于ORDER BY的排序结果，定义了当前行执行窗口计算时能访问的行范围。通过ROWS/RANGE BETWEEN <起始范围> AND <结束范围>语法来声明，比如ROWS BETWEEN 1 PRECEDING AND CURRENT ROW就表示当前行和它的前一行构成计算范围。
窗口框架对lagInFrame的影响
标准SQL的LAG函数逻辑很直接：不管窗口框架如何，它只取当前行的前N行（默认前1行）的数据。但ClickHouse的lagInFrame是严格绑定窗口框架的——只有框架范围内包含的前序行，才会被它作为候选行来选取“前一行”的值。如果框架里没有符合偏移要求的行，lagInFrame就返回NULL。
两种窗口框架的输出差异场景
是的，存在明显的输出不同场景，主要集中在以下几种情况：
1. 使用大于1的偏移量时
当你给lagInFrame指定了大于1的偏移参数（比如lagInFrame(startdate, 2)，取前2行的值），两种框架的差异会立刻显现：
- 用
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING时，只要分区内存在前2行，就能正常取到对应的值； - 用
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW时，框架内最多只有当前行和前1行，无法找到前2行，所以lagInFrame(startdate,2)会返回NULL。
举个数据例子：
| startdate |
|---|
| 2024-01-01 00:00:00 |
| 2024-01-01 00:05:00 |
| 2024-01-01 00:10:00 |
| 2024-01-01 00:30:00 |
- 全量框架下，第四行的
lagInFrame(startdate,2)会取到2024-01-01 00:05:00； - 小范围框架下，第四行的
lagInFrame(startdate,2)返回NULL。
2. 存在重复排序键时
当ORDER BY的列有重复值时，ClickHouse对相同排序值的行的排列顺序是不确定的（除非额外增加更精细的排序字段）。如果小范围框架的范围刚好卡在重复行的边界，就可能导致lagInFrame的取值和全量框架不同。
比如有3个相同时间的行：
| startdate |
|---|
| 2024-01-01 00:00:00 |
| 2024-01-01 00:00:00 |
| 2024-01-01 00:00:00 |
| 2024-01-01 00:20:00 |
如果用lagInFrame(startdate,2)：
- 全量框架下，第四行能取到第二行的
00:00:00； - 小范围框架下，第四行的框架只有第三行和自己，找不到前2行，返回NULL。
3. 使用RANGE类型框架时
如果把ROWS换成RANGE（基于排序键的值范围而非行数量），两种框架的差异会更显著。比如RANGE BETWEEN INTERVAL 10 MINUTE PRECEDING AND CURRENT ROW，这种框架只包含当前时间前10分钟内的行，而全量框架会包含所有前序行。此时如果某行的前一行时间超出了10分钟范围，lagInFrame在小范围框架下会返回NULL，而全量框架下能正常取到值。
总结
你的示例中两种框架结果一致，是因为场景只用到了lagInFrame的默认偏移量（前1行），且排序键无重复。但一旦偏移量大于1、存在重复排序键，或者使用RANGE框架，两种写法的输出就会出现明显差异。同时，小范围框架的内存占用更低，在确定业务场景符合范围限制时，优先使用更紧凑的框架是更优选择。
内容的提问来源于stack exchange，提问作者Soenderby
