PG 窗口函数
窗口函数：基于结果集进行计算，将计算的结果合并到输出的结果集上，并返回多行。
1、语法
function_name ([expression[,expression ...]]) [filter (where filter_clause )] over (window_definition)
window_definition语法：
[ existing_window_name ] [ partition by expression [, ...]] [ order by expression [asc | desc | using operator] [nulls {first | last}] [, ...]] [ frame_clause ]
- over：窗口函数的关键字。决定查询中的哪些行被分离出来由窗口函数处理
- partition by：对查询返回的结果集进行分组，之后窗口函数处理分组的数据
- order by：设定结果集的分组数据排序
- 窗口帧：对于每一行，在它的分区中的行集被称为他的窗口帧
-- 创建测试函数
create table score(id serial primary key,subject character varying(32),stu_name character varying(32),score numeric(3,0));
insert into score(subject,stu_name,score) values('Chinese','aaa',70),('Chinese','bbb',70),('Chinese','ccc',80),('English','aaa',75),('English','bbb',90),('English','ccc',60),('Math','aaa',80),('Math','bbb',99),('Math','ccc',65);
postgres=# select * from score;
id | subject | stu_name | score
----+---------+----------+-------
1 | Chinese | aaa | 70
2 | Chinese | bbb | 70
3 | Chinese | ccc | 80
4 | English | aaa | 75
5 | English | bbb | 90
6 | English | ccc | 60
7 | Math | aaa | 80
8 | Math | bbb | 99
9 | Math | ccc | 65
(9 rows)
2、avg() over()
-- 查询每名学生成绩并且显示课程的平均分
postgres=# select a.*,b.avg_score from score as a left join (select subject,avg(score) as avg_score from score group by subject) as b on a.subject=b.subject; id | subject | stu_name | score | avg_score
----+---------+----------+-------+---------------------
1 | Chinese | aaa | 70 | 73.3333333333333333
2 | Chinese | bbb | 70 | 73.3333333333333333
3 | Chinese | ccc | 80 | 73.3333333333333333
4 | English | aaa | 75 | 75.0000000000000000
5 | English | bbb | 90 | 75.0000000000000000
6 | English | ccc | 60 | 75.0000000000000000
7 | Math | aaa | 80 | 81.3333333333333333
8 | Math | bbb | 99 | 81.3333333333333333
9 | Math | ccc | 65 | 81.3333333333333333
(9 rows)
-- 窗口函数
postgres=# select subject,stu_name,score,avg(score) over(partition by subject) from score;
subject | stu_name | score | avg
---------+----------+-------+---------------------
Chinese | aaa | 70 | 73.3333333333333333
Chinese | bbb | 70 | 73.3333333333333333
Chinese | ccc | 80 | 73.3333333333333333
English | aaa | 75 | 75.0000000000000000
English | bbb | 90 | 75.0000000000000000
English | ccc | 60 | 75.0000000000000000
Math | aaa | 80 | 81.3333333333333333
Math | bbb | 99 | 81.3333333333333333
Math | ccc | 65 | 81.3333333333333333
(9 rows)
3、row_number()
对结果集分组后的数据标注行号，从1开始
postgres=# select row_number() over (partition by subject order by score desc),* from score;
row_number | id | subject | stu_name | score
------------+----+---------+----------+-------
1 | 3 | Chinese | ccc | 80
2 | 1 | Chinese | aaa | 70
3 | 2 | Chinese | bbb | 70
1 | 5 | English | bbb | 90
2 | 4 | English | aaa | 75
3 | 6 | English | ccc | 60
1 | 8 | Math | bbb | 99
2 | 7 | Math | aaa | 80
3 | 9 | Math | ccc | 65
(9 rows)
-- 不指定 partition 属性
postgres=# select row_number() over (order by id),* from score;
row_number | id | subject | stu_name | score
------------+----+---------+----------+-------
1 | 1 | Chinese | aaa | 70
2 | 2 | Chinese | bbb | 70
3 | 3 | Chinese | ccc | 80
4 | 4 | English | aaa | 75
5 | 5 | English | bbb | 90
6 | 6 | English | ccc | 60
7 | 7 | Math | aaa | 80
8 | 8 | Math | bbb | 99
9 | 9 | Math | ccc | 65
(9 rows)
4、rank()
当组内某行字段值相同时，行号重复并且行号产生间隙
postgres=# select rank() over (partition by subject order by score),* from score;
rank | id | subject | stu_name | score
------+----+---------+----------+-------
1 | 2 | Chinese | bbb | 70
1 | 1 | Chinese | aaa | 70
3 | 3 | Chinese | ccc | 80
1 | 6 | English | ccc | 60
2 | 4 | English | aaa | 75
3 | 5 | English | bbb | 90
1 | 9 | Math | ccc | 65
2 | 7 | Math | aaa | 80
3 | 8 | Math | bbb | 99
(9 rows)
5、dense_rank()
和rank()函数相似，主要区别为当组内某行字段值相同时，虽然行号相同，但行号不产生嫌隙
postgres=# select dense_rank() over (partition by subject order by score),* from score;
dense_rank | id | subject | stu_name | score
------------+----+---------+----------+-------
1 | 2 | Chinese | bbb | 70
1 | 1 | Chinese | aaa | 70
2 | 3 | Chinese | ccc | 80
1 | 6 | English | ccc | 60
2 | 4 | English | aaa | 75
3 | 5 | English | bbb | 90
1 | 9 | Math | ccc | 65
2 | 7 | Math | aaa | 80
3 | 8 | Math | bbb | 99
(9 rows)
6、lag()
获取行偏移offset那行某个字段的数据
lag(value anycompatible[,offset integer[,default anycompatible ]])
-- value：要返回记录的字段
-- offset：偏移量，默认为1
-- default：如果不存在 offset 便宜的行时用默认值填充，default 默认值是 null
示例：
-- 查询 score 表并获取向上偏移一行记录的 id 值
postgres=# select lag(id,1) over(),* from score;
lag | id | subject | stu_name | score
-----+----+---------+----------+-------
| 1 | Chinese | aaa | 70
1 | 2 | Chinese | bbb | 70
2 | 3 | Chinese | ccc | 80
3 | 4 | English | aaa | 75
4 | 5 | English | bbb | 90
5 | 6 | English | ccc | 60
6 | 7 | Math | aaa | 80
7 | 8 | Math | bbb | 99
8 | 9 | Math | ccc | 65
(9 rows)
-- 查询 score 表并获取向上偏移两行行记录的 id 值，并指定默认值
postgres=# select lag(id,-2,9999) over(),* from score;
lag | id | subject | stu_name | score
------+----+---------+----------+-------
3 | 1 | Chinese | aaa | 70
4 | 2 | Chinese | bbb | 70
5 | 3 | Chinese | ccc | 80
6 | 4 | English | aaa | 75
7 | 5 | English | bbb | 90
8 | 6 | English | ccc | 60
9 | 7 | Math | aaa | 80
9999 | 8 | Math | bbb | 99
9999 | 9 | Math | ccc | 65
(9 rows)
7、first_value()
读取结果集每一个分组的第一行数据的字段值
postgres=# select first_value(score) over(partition by subject),* from score;
first_value | id | subject | stu_name | score
-------------+----+---------+----------+-------
70 | 1 | Chinese | aaa | 70
70 | 2 | Chinese | bbb | 70
70 | 3 | Chinese | ccc | 80
75 | 4 | English | aaa | 75
75 | 5 | English | bbb | 90
75 | 6 | English | ccc | 60
80 | 7 | Math | aaa | 80
80 | 8 | Math | bbb | 99
80 | 9 | Math | ccc | 65
(9 rows)
8、last_value()
读取结果集每一个分组的最后一行数据的字段值
postgres=# select last_value(score) over(partition by subject),* from score;
last_value | id | subject | stu_name | score
------------+----+---------+----------+-------
80 | 1 | Chinese | aaa | 70
80 | 2 | Chinese | bbb | 70
80 | 3 | Chinese | ccc | 80
60 | 4 | English | aaa | 75
60 | 5 | English | bbb | 90
60 | 6 | English | ccc | 60
65 | 7 | Math | aaa | 80
65 | 8 | Math | bbb | 99
65 | 9 | Math | ccc | 65
(9 rows)
9、nth_value()
获取结果集每一个分组的指定行数据的字段值
语法：nth_value(value any,nth integer)
- value：指定表的字段
- nth：指定结果集分组数据中的第几行，如果不存在则返回空
postgres=# select nth_value(score,2) over(partition by subject),* from score;
nth_value | id | subject | stu_name | score
-----------+----+---------+----------+-------
70 | 1 | Chinese | aaa | 70
70 | 2 | Chinese | bbb | 70
70 | 3 | Chinese | ccc | 80
90 | 4 | English | aaa | 75
90 | 5 | English | bbb | 90
90 | 6 | English | ccc | 60
99 | 7 | Math | aaa | 80
99 | 8 | Math | bbb | 99
99 | 9 | Math | ccc | 65
(9 rows)
10、窗口函数别名的使用
如需多次使用窗口函数，可以使用窗口函数别名
语法：select ... from ... window window_name as (window_defination)[,...]
示例：
postgres=# select avg(score) over(r),sum(score) over(r),* from score window r as (partition by subject);
avg | sum | id | subject | stu_name | score
---------------------+-----+----+---------+----------+-------
73.3333333333333333 | 220 | 1 | Chinese | aaa | 70
73.3333333333333333 | 220 | 2 | Chinese | bbb | 70
73.3333333333333333 | 220 | 3 | Chinese | ccc | 80
75.0000000000000000 | 225 | 4 | English | aaa | 75
75.0000000000000000 | 225 | 5 | English | bbb | 90
75.0000000000000000 | 225 | 6 | English | ccc | 60
81.3333333333333333 | 244 | 7 | Math | aaa | 80
81.3333333333333333 | 244 | 8 | Math | bbb | 99
81.3333333333333333 | 244 | 9 | Math | ccc | 65
(9 rows)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
