这是一个快速查询,以说明其行为:
select
v,
first_value(v) over(order by v) f1,
first_value(v) over(order by v rows between unbounded preceding and current row) f2,
first_value(v) over(order by v rows between unbounded preceding and unbounded following) f3,
last_value (v) over(order by v) l1,
last_value (v) over(order by v rows between unbounded preceding and current row) l2,
last_value (v) over(order by v rows between unbounded preceding and unbounded following) l3,
max (v) over() m1,
max (v) over(order by v) m2,
max (v) over(order by v rows between unbounded preceding and current row) m3,
max (v) over(order by v rows between unbounded preceding and unbounded following) m4
from (values(1),(2),(3),(4)) t(v)
上述查询的输出结果可以在此处查看(
SQLFiddle 这里):
| V | F1 | F2 | F3 | L1 | L2 | L3 | M1 | M2 | M3 | M4 |
|---|----|----|----|----|----|----|----|----|----|----|
| 1 | 1 | 1 | 1 | 1 | 1 | 4 | 4 | 1 | 1 | 4 |
| 2 | 1 | 1 | 1 | 2 | 2 | 4 | 4 | 2 | 2 | 4 |
| 3 | 1 | 1 | 1 | 3 | 3 | 4 | 4 | 3 | 3 | 4 |
| 4 | 1 | 1 | 1 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
很少有人考虑到应用于带有
ORDER BY
子句的窗口函数的隐式框架。在这种情况下,窗口默认为框架
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。(RANGE不完全等同于ROWS,但这是另一回事)。可以这样想:
- 在
v = 1
的行上,有序窗口的框架跨越v IN (1)
- 在
v = 2
的行上,有序窗口的框架跨越v IN (1, 2)
- 在
v = 3
的行上,有序窗口的框架跨越v IN (1, 2, 3)
- 在
v = 4
的行上,有序窗口的框架跨越v IN (1, 2, 3, 4)
如果要防止这种行为,您有两个选择:
- 对于ordered窗口函数,请使用显式的
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
子句
- 在那些允许省略它们的窗口函数中不使用
ORDER BY
子句(如MAX(v) OVER()
)
更多细节在这篇关于LEAD()
,LAG()
,FIRST_VALUE()
和LAST_VALUE()
的文章中进行了解释