OVER 开窗函数BUG

Viewed 57

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
;

预期效果

image.png

BUG

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 中的 UNBOUNDED 不生效

f60c428c6bc94040c73cdb79d9ec02b.png

1 Answers

已提pr修复,下个版本合入。