Join and enrich data in one table by closest date BigQuery

userEuKl1 0
Viewed 39

I have a BigQuery table with data:

clientId revenue orderId order_date w_date w_source w_campaign
11111111 100 00000001 2022-08-02 null null null
11111111 1000 00000002 2022-08-07 null null null
11111111 2000 00000003 2022-08-07 null null null
11111111 null null null 2022-07-27 source_1 campaign_2
11111111 null null null 2022-06-30 source_2 campaign_4
22222222 250 00000011 2022-08-15 null null null
22222222 500 00000015 2022-08-22 null null null
22222222 100 00000087 2022-08-25 null null null
22222222 null null null 2022-08-02 source_4 campaign_6
22222222 null null null 2022-08-18 source_1 campaign_9

And I want to get the result:

clientId revenue orderId order_date w_date w_source w_campaign
11111111 100 00000001 2022-08-02 2022-07-27 source_1 campaign_2
11111111 1000 00000002 2022-08-07 2022-07-27 source_1 campaign_2
11111111 2000 00000003 2022-08-07 2022-07-27 source_1 campaign_2
22222222 250 00000011 2022-08-15 2022-08-02 source_4 campaign_6
22222222 500 00000015 2022-08-22 2022-08-18 source_1 campaign_9
22222222 100 00000087 2022-08-25 2022-08-18 source_1 campaign_9

And I don't understand how to properly merge such data:

I have only one value with clientId on which data can be joined

w_date must be less or equal order_date

w_source & w_campaign must be equal w_date as well

I tried to do it with JOIN or subquery with LAST_VALUE but it doesn't work but perhaps the desired query is too easy for that I hope.

google-bigquery
1 Answers

You need to use window function and the over statements. I defined a get_last window range.

The last_value returns the last entry in that column. The ignore nulls only takes filled values in account and ignores the all null entries. By limiting the range, only the values before that date are considered.

With tbl as 
(Select 11111111 clientId,100 revenue,1 orderId,date("2022-08-02") order_date,null w_date,null w_source,null w_campaign
union all Select 11111111,1000,2,date("2022-08-07"),null,null,null
union all Select 11111111,2000,3,date("2022-08-07"),null,null,null
union all Select 11111111,null,null,null,date("2022-07-27"),"source_1","campaign_2"
union all Select 11111111,null,null,null,date("2022-06-30"),"source_2","campaign_4"
union all Select 22222222,250,11,date("2022-08-15"),null,null,null
union all Select 22222222,500,15,date("2022-08-22"),null,null,null
union all Select 22222222,100,87,date("2022-08-25"),null,null,null
union all Select 22222222,null,null,null,date("2022-08-02"),"source_4","campaign_6"
union all Select 22222222,null,null,null,date("2022-08-18"),"source_1","campaign_9"
),
tmp as 
(Select * except(w_date,w_source,w_campaign) ,
last_value(w_date ignore nulls) over get_last as w_date_,
last_value(w_source ignore nulls) over get_last as w_source_,
last_value(w_campaign ignore nulls) over get_last as w_campaign_,
from tbl
window get_last as (partition by clientid order by ifnull(order_date,w_date) range between unbounded preceding and current row )
) 
Select * from  tmp
where orderId is not null
userG7m710
Related