错误直觉与正确写法
错误直觉与正确写法
-- 直觉上想要“该用户最后一单时间”，实际拿到当前行
SELECT user_id, created_at,
LAST_VALUE(created_at) OVER (
PARTITION BY user_id ORDER BY created_at
) AS wrong_last
FROM orders;
-- 正确：帧扩展到分区结尾
SELECT user_id, created_at,
LAST_VALUE(created_at) OVER (
PARTITION BY user_id ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS real_last
FROM orders;| 默认帧 | 有 ORDER BY 时为 RANGE UNBOUNDED PRECEDING AND CURRENT ROW |
|---|---|
| 修正 | 显式写 ROWS BETWEEN … AND UNBOUNDED FOLLOWING |
| 相关 | FIRST_VALUE 默认行为符合直觉，LAST_VALUE 不符合 |