近期工作中发现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)