sql - 如何将滑动窗口框架设置为雪花中 1 个关注和无界关注之间的范围

Question

我需要将oracle sql 转换为snowflake sql。

在oracle中，我有这个:

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)

在文档中写的雪花中
https://docs.snowflake.com/en/user-guide/functions-window-using.html
https://docs.snowflake.com/en/sql-reference/functions-analytic.html

对于滑动框架，不支持范围，我必须使用行

所以我必须使用这个:

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
}

所以我应该使用这个

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)

但它抛出错误:“无效的窗口框架”

笔记 :

我可以

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)

和

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)

没有错误

但我需要 1 following得到相同的结果。

你知道这里发生了什么吗？

谢谢

Answer 1

你没有忽视 NULL值，因此您的原始代码看起来像 lead()对我来说:

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

不需要窗框。这应该适用于两个数据库。

编辑:

@dnoeth 是正确的。这是一个复杂的公式。对于不共享最终值的所有行，逻辑似乎是放入列的最后一个值。这看起来很奇怪，但相当于这个公式:

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

不同之处在于这总是选取列中的最后一个值。所述代码返回 NULL对于最后一个值。所以，一个 case可能需要:

(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是一个 db<>fiddle，使用接受两个查询的 Postgres。