I have a table as below. How can I find the previous of each non null value in col5 looking from col4 in the most efficient manner using a SQL query in Snowflake?
col1 col2 col3 col4 col5
aaa bbb -1 null 2000-01-01 08:10:00.471
aaa bbb -1 null 2000-01-01 08:09:55.678
aaa bbb -1 null 2000-01-01 08:09:57.111
aaa bbb -1 null 2000-01-01 08:11:15.564
aaa bbb 0 2000-01-01 08:12:56.672 null
aaa bbb 1 2000-01-01 08:09:00.897 null
aaa bbb 2 2000-01-01 08:10:30.478 null
The result-set should be like below.
col1 col2 col3 col4 col5 col6
aaa bbb -1 null 2000-01-01 08:10:00.471 2000-01-01 08:09:00.897
aaa bbb -1 null 2000-01-01 08:09:55.678 2000-01-01 08:09:00.897
aaa bbb -1 null 2000-01-01 08:09:57.111 2000-01-01 08:09:00.897
aaa bbb -1 null 2000-01-01 08:11:15.564 2000-01-01 08:10:30.478
aaa bbb 0 2000-01-01 08:12:56.672 null null
aaa bbb 1 2000-01-01 08:09:00.897 null null
aaa bbb 2 2000-01-01 08:10:30.478 null null
I have tried to use the lag and last_value functions so far but lag returns value from same column and last_value also didnt work.
Any help would be appreciated here.
From your question, it does not seem necessary to use window functions. Your explanations do not make it possible to understand how to use them.
Look at a simpler example.
select col1,col2,col3,col4,col5
,(select max(col4) from test t2 where t2.col1=t.col1 and t2.col2=t.col2
and col4<t.col5
)new_col6
,test_col6
from test t
| col1 | col2 | col3 | col4 | col5 | new_col6 | test_col6 |
|---|---|---|---|---|---|---|
| aaa | bbb | -1 | null | 2000-01-01 08:10:00.470 | 2000-01-01 08:09:00.897 | 2000-01-01 08:09:00.897 |
| aaa | bbb | -1 | null | 2000-01-01 08:09:55.677 | 2000-01-01 08:09:00.897 | 2000-01-01 08:09:00.897 |
| aaa | bbb | -1 | null | 2000-01-01 08:09:57.110 | 2000-01-01 08:09:00.897 | 2000-01-01 08:09:00.897 |
| aaa | bbb | -1 | null | 2000-01-01 08:11:15.563 | 2000-01-01 08:10:30.477 | 2000-01-01 08:10:30.477 |
| aaa | bbb | 0 | 2000-01-01 08:12:56.673 | null | null | null |
| aaa | bbb | 1 | 2000-01-01 08:09:00.897 | null | null | null |
| aaa | bbb | 2 | 2000-01-01 08:10:30.477 | null | null | null |