Search code examples
sqlnullgoogle-bigquerylag

LAG function and NULLS

So I'm trying to use a LAG function (Google BigQuery) and my first value is a NULL.

enter image description here

I'm using a lag function to get lag_Value_1,lag_Value_2... In here : 

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

Any help to fill out lag_Value_1,2,3,4,5....for ITEM 2?

Solution

  • I feel below is what you are looking for: 

      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