CODEWITHSUNDEEP
sqlgoogle-bigquerynullsnapshotcoalesce
Elbert
Elbert

Reputation: 506

SQL fill NULL row with previous non NULL row

So I have a bq table that was created as several calender date snapshot, joined to trx data. Please find below query to populate the table

  SELECT
    GENERATE_DATE_ARRAY(date_add(DATE(CURRENT_TIMESTAMP), interval -20 day), DATE('2020-08-22')) AS date_array
  )
         
 ,dim_date AS (
  SELECT
    sn_date
  FROM
    date_array_table,
    UNNEST(date_array) AS sn_date
    )
  
 ,data_test as (
 select date('2020-08-20') as date, 1 as id, 1000 as num
 UNION ALL
 select date('2020-08-18') as date, 1 as id, 130 as num
 UNION ALL
 select date('2020-08-18') as date, 2 as id, 300 as num
 UNION ALL
 select date('2020-08-13') as date, 2 as id, 250 as num
 )

 ,jjoin as (
 select
 *
 from dim_date
 left join 
 data_test
 on 1=1 and sn_date = date
 )

 select *
 from jjoin
 order by 1 desc

The result is as following img enter image description here

next I would like to fill the snapshot row with the NULL values with previous non NULL row by date for each id. I have tried to use max or first_value but it is still NULL. example :

select sn_date
coalesce(num, max (num) over (partition by id order by date)
from jjoin

but it doesn't show previous non Null row. any advice? thanks

expected :

--------------------------
sn_date | date | id | num
--------------------------
08/22   | 08/20| 1  | 1000
08/21   | 08/20| 1  | 1000
08/20   | 08/20| 1  | 1000
08/19   | 08/18| 1  | 130
08/18   | 08/18| 1  | 130
08/18   | 08/18| 2  | 300
08/17   | 08/13| 1  | 250
08/16   | 08/13| 1  | 250
08/15   | 08/13| 1  | 250

Upvotes: 2

Views: 2204

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Below is for BigQuery Standard SQL

#standardSQL
SELECT sn_date, 
  FIRST_VALUE(date IGNORE NULLS) OVER (win) AS date,
  FIRST_VALUE(id IGNORE NULLS) OVER (win) AS id,
  FIRST_VALUE(num IGNORE NULLS) OVER (win) AS num
FROM your_current_result
WINDOW win AS (ORDER BY sn_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

If to apply to your current result shown in the question as in below example

#standardSQL
WITH your_current_result AS (
  SELECT DATE '2020-08-20' sn_date, DATE '2020-08-20' date, 1 id, 1000 num UNION ALL
  SELECT '2020-08-22', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-21', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-19', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-18', '2020-08-18', 1, 130 UNION ALL
  SELECT '2020-08-18', '2020-08-18', 2, 300 UNION ALL
  SELECT '2020-08-17', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-16', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-15', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-14', NULL, NULL, NULL UNION ALL
  SELECT '2020-08-13', '2020-08-13', 1, 250 
)
SELECT sn_date, 
  FIRST_VALUE(date IGNORE NULLS) OVER (win) AS date,
  FIRST_VALUE(id IGNORE NULLS) OVER (win) AS id,
  FIRST_VALUE(num IGNORE NULLS) OVER (win) AS num
FROM your_current_result
WINDOW win AS (ORDER BY sn_date DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)

result is

Row sn_date     date        id  num  
1   2020-08-22  2020-08-20  1   1000     
2   2020-08-21  2020-08-20  1   1000     
3   2020-08-20  2020-08-20  1   1000     
4   2020-08-19  2020-08-18  1   130  
5   2020-08-18  2020-08-18  1   130  
6   2020-08-18  2020-08-18  2   300  
7   2020-08-17  2020-08-13  1   250  
8   2020-08-16  2020-08-13  1   250  
9   2020-08-15  2020-08-13  1   250  
10  2020-08-14  2020-08-13  1   250  
11  2020-08-13  2020-08-13  1   250

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269653

You can use last_value():

select sn_date, date, id, num,
       last_value(date ignore nulls) over (order by date desc),
       last_value(id ignore nulls) over (order by date desc),
       last_value(num ignore nulls) over (order by date desc)

I should note that the SQL standard supports ignore nulls on lag() as well as first_value() and last_value(). When I think about solving this problem, I think in terms of lag(). I think BigQuery is the only database that supports ignore nulls but not on lag().

Upvotes: 5

Related Questions