sqlsnowflake-cloud-data-platformwindow-functions

How to find previous of a value in one column looking from another column in Snowflake?


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.


Solution

  • 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

    fiddle