Filling in missing data in Snowflake

user_16513269 0
Viewed 801

I have a table in Snowflake like this:

TIME   USER   ITEM
1      frank  1
2      frank  0
3      frank  0
4      frank  0
5      frank  2
6      alf    5
7      alf    0
8      alf    6
9      alf    0
10     alf    9

I want to be able to replace all the zeroes with the next non-zero value, so in the end I have a table like this:

TIME   USER   ITEM
1      frank  1
2      frank  2
3      frank  2
4      frank  2
5      frank  2
6      alf    5
7      alf    6
8      alf    6
9      alf    9
10     alf    9

How would I write a query that does that in Snowflake?

snowflake-cloud-data-platform window-functions sql
4 Answers

You can use conditional_change_event function for this - documented here:

with base_table as (
    select
        t1.*,
        conditional_change_event(item) over (order by time desc) event_num
    from test_table t1
    order by time desc
)
select
    t1.time,
    t1.user,
    t1.item                    old_item,
    coalesce(t2.item, t1.item) new_item
from base_table t1
   left join base_table t2 on t1.event_num = t2.event_num + 1 and t1.item = 0
order by t1.time asc

Above SQL Results:

+----+-----+--------+--------+
|TIME|USER |OLD_ITEM|NEW_ITEM|
+----+-----+--------+--------+
|1   |frank|1       |1       |
|2   |frank|0       |2       |
|3   |frank|0       |2       |
|4   |frank|0       |2       |
|5   |alf  |2       |2       |
|6   |alf  |5       |5       |
|7   |alf  |0       |6       |
|8   |alf  |6       |6       |
|9   |alf  |0       |9       |
|10  |alf  |9       |9       |
+----+-----+--------+--------+
user_32872500

You can use lead(ignore nulls):

select t.*,
       (case when item = 0
             then lead(nullif(item, 0) ignore nulls) over (partition by user order by time)
             else item
        end) as imputed_item
from t;

You can also phrase this using first_value():

select t.*,
       last_value(nullif(item, 0) ignore nulls) over (partition by user order by time desc)
from t;
user_11440350

If you want to use first_value() or last_value() in Snowflake, please keep in mind that Snowflake supports window frames differently from the ANSI standard as documented here. This means that if you want to use the default window frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW you have to include it explicitly in the statement, otherwise, the default would be ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING and that is why the LAST_VALUE example from the previous answer would not work correctly. Here is one example that would work:

select t.*,
       last_value(nullif(item, 0) ignore nulls) over (partition by user order by time desc rows between unbounded preceding and current row)
from t;
user_165222240

Nothing wrong with above solutions ... but here's a different approach ... I think it's simpler.

select * from good
union all
select 
     bad.time
    ,bad.user
    ,min(good.item) 
from  bad 
left outer join  
good on good.user=bad.user and good.time>bad.time 
group by
    1,2

enter image description here

Full COPY|PASTE|RUN SQL:

with cte as (
select * from (
select 1  time, 'frank' user , 1 item union
select 2  time, 'frank' user , 0 item union
select 3  time, 'frank' user , 0 item union
select 4  time, 'frank' user , 0 item union
select 5  time, 'frank' user , 2 item union
select 6  time, 'alf' user ,   5 item union
select 7  time, 'alf' user ,   0 item union
select 8  time, 'alf' user ,   6 item union
select 9  time, 'alf' user ,   0 item union
select 10 time, 'alf' user ,   9) )
, good as (select * from cte where item<> 0) 
, bad as (select * from cte where item= 0) 


select *  from  good
union all
select 
     bad.time
    ,bad.user
    ,min(good.item ) 
from  bad 
left outer join  
    good on good.user=bad.user and good.time>bad.time 
group by
    1,2
user_13524900
Related