How to set sliding windows frame to range BETWEEN 1 Following AND UNBOUNDED Following in snowflake

user_10585338 0
Viewed 1030

I need to convert oracle sql to snowflake sql.

In oracle, I have this :

last_value(my_table.my_col) 
Over(Partition BY 
my_table.my_col2,
my_table.my_col3
Order By my_table.my_col4 
range BETWEEN 1 Following AND UNBOUNDED Following)

In snowflake as written in the documentation https://docs.snowflake.com/en/user-guide/functions-window-using.html https://docs.snowflake.com/en/sql-reference/functions-analytic.html

For sliding frame, range is not supported and I have to use rows

So I have to use this :

slidingFrame ::=
{
   ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
 | ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
 | ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

So I should be using this

last_value(my_table.my_col) 
Over(Partition BY 
my_table.my_col2,
my_table.my_col3
Order By my_table.my_col4 
rows BETWEEN 1 Following AND UNBOUNDED Following)

But it throws the error : "Invalid Window Frame"

Note :

I can do 

last_value(my_table.my_col) 
Over(Partition BY 
my_table.my_col2,
my_table.my_col3
Order By my_table.my_col4 
rows BETWEEN CURRENT row AND UNBOUNDED Following)

and 

last_value(my_table.my_col) 
Over(Partition BY 
my_table.my_col2,
my_table.my_col3
Order By my_table.my_col4 
rows BETWEEN 1 PRECEDING AND UNBOUNDED Following)

without error

But I need the 1 following to get the same result.

Do you have any idea what's going on here ?

Thanks

snowflake-cloud-data-platform oracle sql
1 Answers

You are not ignoring NULL values so your original code looks like a lead() to me:

lead(my_table.my_col) over (partition by my_table.my_col2, my_table.my_col3
                            order by my_table.my_col4
                           )

No window frame is needed. This should work in both databases.

EDIT:

@dnoeth is correct. This is a complicated formulation. The logic appears to be to put in the last value of the column, for all rows that do not share the final value. That seems weird, but equivalent to this formulation:

first_value(my_table.my_col) over (partition by my_table.my_col2, my_table.my_col3
                                   order by my_table.my_col4 desc
                                  )

The difference is that this always picks up the last value in the column. The stated code returns NULL for the very last value. So, a case might be needed:

(case when rank() over (partition by my_table.my_col2, my_table.my_col3
                        order by my_table.my_col4 desc)  > 1 
      then first_value(my_table.my_col) over (partition by my_table.my_col2, my_table.my_col3
                                              order by my_table.my_col4 desc
                                  )
 end)

Here is a db<>fiddle, using Postgres which accepts both queries.

user_11440350
Related