SQL 语句
SELECT uid
,amt
,COALESCE(LAST_VALUE(amt, true) OVER(PARTITION BY uid ORDER BY time_s ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) amt1
,COALESCE(LAST_VALUE(amt, true) OVER(PARTITION BY uid ORDER BY time_s ASC ROWS BETWEEN 100 PRECEDING AND CURRENT ROW)) amt_not
,COALESCE(FIRST_VALUE(amt, true) OVER(PARTITION BY uid ORDER BY time_s DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) amt2
,time_s
FROM (
SELECT 'a' AS uid, 1 AS amt, 0 AS time_s UNION ALL
SELECT 'a' AS uid, null AS amt, 1 AS time_s UNION ALL
SELECT 'a' AS uid, null AS amt, 2 AS time_s UNION ALL
SELECT 'a' AS uid, 2 AS amt, 3 AS time_s UNION ALL
SELECT 'b' AS uid, null AS amt, 4 AS time_s UNION ALL
SELECT 'b' AS uid, 3 AS amt, 5 AS time_s UNION ALL
SELECT 'b' AS uid, null AS amt, 6 AS time_s UNION ALL
SELECT 'b' AS uid, 2 AS amt, 7 AS time_s
) t
ORDER BY uid, time_s
;
预期效果
BUG
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 中的 UNBOUNDED 不生效