窗口函数（window function）在PG和Oracle中的区别

近期工作中发现PG和Oracle在窗口函数的使用上还是有不小的差异的，差异点在与PG可能有更多的使用方式，遂记录一下备忘。

PG官网对于窗口函数的语法描述如下图所示

语法差异-窗口定义位置

上图标注的1号位置处，PG支持先定义一个窗口名，然后在查询列表中可以引用该窗口名。

create table t(a int,b int);
insert into t values(1,1),(1,1),(1,2),(2,1),(2,2);

--定义一个别名为“w”的window，查询列表中可以引用该别名 
select sum(b) over(w),max(b) over(w) from t window w as(partition by a order by b);
 sum | max 
-----+-----
   2 |   1
   2 |   1
   4 |   2
   1 |   1
   3 |   2
(5 rows)

语法差异-filter

上图标注的2号位置处，PG支持使用filter子句，将待聚集的数据做一次过滤。被过滤的行不但不参与聚集，而且不为该行输出聚集结果，这点是我没想到的。

--b<=1的记录被过滤了，不参与聚集，同时也不会为这些记录生成最终的聚集结果
select sum(b) filter(where b>1) over(w),a,b from t window w as(partition by a order by b);
 sum | a | b 
-----+---+---
     | 1 | 1
     | 1 | 1
   2 | 1 | 2
     | 2 | 1
   2 | 2 | 2
(5 rows)

窗口模式差异

PG除了range和rows这两种窗口模式外，还支持groups模式。这里对PG各窗口模式做一个总结：

1、range和groups侧重以逻辑的方式来定义窗口的边界，而rows则侧重以物理的方式来定义窗口边界。

在range或groups模式下，若窗口起始边界设置为current row，它的含义是该起始边界是当前记录的第一条peer row; 类似的，当窗口的终止边界设置为current row时，该终止边界是当前记录的最后一条peer row。而在rows模式下，current row表示就是当前行本身。

peer row是指同一个分组(partition)内，聚集列相等的记录。参考下图

下面用几个例子来说明差异

drop table t;
insert into t values(1,1),(1,1),(1,2),(1,2),(1,2),(2,2),(2,2),(2,3),(2,4),(2,4);
select * from t;
 a | b 
---+---
 1 | 1
 1 | 1
 1 | 2
 1 | 2
 1 | 2
 2 | 2
 2 | 2
 2 | 3
 2 | 4
 2 | 4
(10 rows)

--以a=1，b=1这组数据为例，PG会将分组内所有b=1的记录累加后才输出
ludeng=# select sum(b) over(partition by a order by b groups between unbounded preceding and current row),a,b from t;
 sum | a | b 
-----+---+---
   2 | 1 | 1
   2 | 1 | 1    --将所有peer row累加后才输出
   8 | 1 | 2
   8 | 1 | 2
   8 | 1 | 2
   4 | 2 | 2
   4 | 2 | 2
   7 | 2 | 3
  15 | 2 | 4
  15 | 2 | 4
(10 rows)

--同上
ludeng=# select sum(b) over(partition by a order by b range between unbounded preceding and current row),a,b from t;
 sum | a | b 
-----+---+---
   2 | 1 | 1
   2 | 1 | 1
   8 | 1 | 2
   8 | 1 | 2
   8 | 1 | 2
   4 | 2 | 2
   4 | 2 | 2
   7 | 2 | 3
  15 | 2 | 4
  15 | 2 | 4
(10 rows)

--以a=1，b=1这组数据为例，PG会将分组内到当前b=1的记录累加后输出
ludeng=# select sum(b) over(partition by a order by b rows between unbounded preceding and current row),a,b from t;
 sum | a | b 
-----+---+---
   1 | 1 | 1    --累加到当前行即输出
   2 | 1 | 1
   4 | 1 | 2
   6 | 1 | 2
   8 | 1 | 2
   2 | 2 | 2
   4 | 2 | 2
   7 | 2 | 3
  11 | 2 | 4
  15 | 2 | 4
(10 rows)

PG独有的frame_exclusion

frame_exclusion子句允许将当前行周围的行排除掉，再执行聚集过程。

EXCLUDE CURRENT ROW将当前行从frame中排除掉。
EXCLUDE GROUP将当前行所在的peer group从frame中排除掉。
EXCLUDE TIES将当前行所在的peer group从frame中排除掉，但是当前行本身不排除。
EXCLUDE NO OTHERS是默认行为，也就是不排除的意思。

下面再用一些例子来展示一下

drop table t;
create table t(a int,b int);
insert into t values(1,1),(1,1),(1,2),(1,2),(2,11),(2,11),(2,22),(2,22);

--设定窗口(frame)为分区内的首行到当前行
select sum(b) over(partition by a order by b range between unbounded preceding and current row exclude current row),a,b from t;
 sum | a | b  
-----+---+----
   1 | 1 |  1	--排除本身后，只有第2行的b=1
   1 | 1 |  1	--排除本身后，只有第1行的b=1
   4 | 1 |  2	--排除本身后，两行b=1和第4行的b=2累加
   4 | 1 |  2	--排除本身后，两行b=1和第3行的b=2累加
  11 | 2 | 11
  11 | 2 | 11
  44 | 2 | 22
  44 | 2 | 22
(8 rows)

select sum(b) over(partition by a order by b range between unbounded preceding and current row exclude group),a,b from t;
 sum | a | b  
-----+---+----
     | 1 |  1	--排除当前行所在的peer group后，没有记录参与聚集
     | 1 |  1	--排除当前行所在的peer group后，没有记录参与聚集
   2 | 1 |  2	--排除当前行所在的peer group后，只有2行b=1参与聚集
   2 | 1 |  2	--排除当前行所在的peer group后，只有2行b=1参与聚集
     | 2 | 11
     | 2 | 11
  22 | 2 | 22
  22 | 2 | 22
(8 rows)

select sum(b) over(partition by a order by b range between unbounded preceding and current row exclude ties),a,b from t;
sum | a | b  
-----+---+----
   1 | 1 |  1	--除当前行本身外，排除当前行所在的peer group后，只有第2行b=1参与聚集
   1 | 1 |  1	--除当前行本身外，排除当前行所在的peer group后，只有第1行b=1参与聚集
   4 | 1 |  2	--除当前行本身外，排除当前行所在的peer group后，只有2行b=1和第4行b=2参与聚集
   4 | 1 |  2	--除当前行本身外，排除当前行所在的peer group后，只有2行b=1和第3行b=2参与聚集
  11 | 2 | 11
  11 | 2 | 11
  44 | 2 | 22
  44 | 2 | 22
(8 rows)