配置单元窗口函数-当前行和无界跟随

Question

无界前行和当前行与无界前行和无界跟随之间的区别是什么？

假设我正在编写一段代码：

from groceries
select id, revenue, day, sum(revenue)
over ( order by id rows between unbounded preceding and current row)

我正在写另一篇文章：

from groceries
select id, revenue, day, sum(revenue)
over ( order by id rows between unbounded preceding and unbounded following)

代码的执行会有什么不同？

Answer 1

第一次求和

sum(revenue)
over ( order by id rows between unbounded preceding and current row)

是一个连续的和:数据集是按id排序的，对于每一行，计算帧[first row...current row]的总和。因此，对于每一行，帧是不同的，并且随着下一行的增加而增加。对应于最大id的最后一行将具有总和(所有行的总和)。

Second sum ( order by id rows between unbounded preceding and unbounded following) -是所有行的总和-帧对于每行第一行都是相同的……最后一排。排序在这里没有任何意义，您的第二个表达式等同于sum(revenue) over ()，正如您所看到的，它产生相同的结果：

with mydata as (
select 1 id, 10 revenue union all
select 2 id, 10 revenue union all
select 3 id, 10 revenue union all
select 4 id, 10 revenue union all
select 5 id, 10 revenue
)

select id, revenue, 
       sum(revenue) over ( order by id rows between unbounded preceding and current row) sum1,
       sum(revenue) over ( order by id rows between unbounded preceding and unbounded following) sum2,
       sum(revenue) over() sum3 --this is the same as sum2
  from mydata
  order by id

结果：

id  revenue sum1    sum2    sum3
1   10      10      50      50
2   10      20      50      50
3   10      30      50      50
4   10      40      50      50
5   10      50      50      50