lag函数和null

tjrkku2a 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(517)

所以我尝试使用一个滞后函数（googlebigquery），我的第一个值是null。

我用一个lag函数来得到lag值1，lag值2。。。在这里：

SELECT ITEM,row_A,row_B,date,Value_1,Value_2,Value_3,Value_4,Value_5,
         LAG(Value_1,0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_1,
         LAG(Value_2,0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_2,
         LAG(Value_3,0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_3,
         LAG(Value_4,0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_4,
         LAG(Value_5,0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_5
FROM table

有没有帮助填写第2项的滞后值？

sql Null google-bigquery lag

来源：https://stackoverflow.com/questions/62051414/lag-function-and-nulls

2条答案

按热度按时间

kuarbcqp1#

我觉得下面就是你要找的：

LAST_VALUE(Value_1 IGNORE NULLS) OVER (PARTITION BY ITEM ORDER BY row_A ASC) AS lag_Value_1,
  LAST_VALUE(Value_2 IGNORE NULLS) OVER (PARTITION BY ITEM ORDER BY row_A ASC) AS lag_Value_2,
  LAST_VALUE(Value_3 IGNORE NULLS) OVER (PARTITION BY ITEM ORDER BY row_A ASC) AS lag_Value_3,
  LAST_VALUE(Value_4 IGNORE NULLS) OVER (PARTITION BY ITEM ORDER BY row_A ASC) AS lag_Value_4,
  LAST_VALUE(Value_5 IGNORE NULLS) OVER (PARTITION BY ITEM ORDER BY row_A ASC) AS lag_Value_5

赞(0）回复(0）举报 2021-08-13

du7egjpx2#

你想要滞后的三参数形式吗？

SELECT ITEM, row_A, row_B, date, Value_1, Value_2, Value_3, Value_4, Value_5,
       LAG(Value_1, 1, 0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_1,
       LAG(Value_2, 1, 0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_2,
       LAG(Value_3, 1, 0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_3,
       LAG(Value_4, 1, 0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_4,
       LAG(Value_5, 1, 0) OVER (PARTITION BY ITEM ORDER BY row_A asc) as lag_Value_5
FROM table

这三个论点是：
要滞后的值。
要返回的行数。
默认值。

赞(0）回复(0）举报 2021-08-13

我来回答

lag函数和null

2条答案

相关问题

热门标签

最新问答