postgreSQL之开窗函数用法 發布於 6 个月前 (10月18日) – 1438 次检阅
- 2020 年 4 月 9 日
- 筆記
开窗函数语法相关
<窗口函数> OVER (PARTITION BY <列清单> ORDER BY <排序用列清单>)
1.over 表示窗口函数关键字
2.partition by 对查询返回的结果集进行分组
3.order by 设定结果集的分组数据排序
可作为窗口函数的函数分类
1.聚合函数： SUM、AVG、COUNT、MAX、MIN
2.内置函数：RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数
avg() over()
演示avg()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的平均值。
先创建一张成绩表
CREATE TABLE score_demo ( id serial PRIMARY KEY, subject character varying(32), stu_name character varying(32), score numeric(3, 0) ); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Chinese', 'francs', 70); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Chinese', 'matiler', 70); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Chinese', 'tutu', 80); INSERT INTO score_demo (subject, stu_name, score) VALUES ('English', 'matiler', 75); INSERT INTO score_demo (subject, stu_name, score) VALUES ('English', 'francs', 90); INSERT INTO score_demo (subject, stu_name, score) VALUES ('English', 'tutu', 60); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Math', 'francs', 80); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Math', 'matiler', 99); INSERT INTO score_demo (subject, stu_name, score) VALUES ('Math', 'tutu', 65);
查询每名学生学习成绩并且显示课程的平均分。请问我自己会怎么做呢?如果是不用窗口函数先做的话，我能想到是这样： 1.先计算出课程的平均分。 2.然后用成绩表与平均分表关联 如下:
select d.subject,d.stu_name,d.score,"temp".avg_score from score_demo d LEFT JOIN ( select subject,avg(score) avg_score from score_demo group by subject ) temp on d.subject = temp.subject ----- subject stu_name score avg_score Chinese francs 70 73.3333333333333 Chinese matiler 70 73.3333333333333 Chinese tutu 80 73.3333333333333 English matiler 75 75 English francs 90 75 English tutu 60 75 Math francs 80 81.3333333333333 Math matiler 99 81.3333333333333 Math tutu 65 81.3333333333333
而使用窗口函数，基本上一句搞掂:
select subject,stu_name,score,avg(score)over(partition by subject) from score_demo --- subject stu_name score avg Chinese francs 70 73.3333333333333 Chinese matiler 70 73.3333333333333 Chinese tutu 80 73.3333333333333 English matiler 75 75 English francs 90 75 English tutu 60 75 Math francs 80 81.3333333333333 Math matiler 99 81.3333333333333 Math tutu 65 81.3333333333333
sum() over()
演示sum()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的总分值
例如：查询每名学生学习成绩并且显示课程的总分
select subject,stu_name,score,sum(score)over(partition by subject) from score_demo ---- subject stu_name score sum Chinese francs 70 220 Chinese matiler 70 220 Chinese tutu 80 220 English matiler 75 225 English francs 90 225 English tutu 60 225 Math francs 80 244 Math matiler 99 244 Math tutu 65 244
count() over()
演示count()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的总条数值
select subject,stu_name,score,COUNT(score)over(partition by subject) from score_demo --- subject stu_name score count Chinese francs 70 3 Chinese matiler 70 3 Chinese tutu 80 3 English matiler 75 3 English francs 90 3 English tutu 60 3 Math francs 80 3 Math matiler 99 3 Math tutu 65 3
max() over()
演示max()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的最大值
select subject,stu_name,score,MAX(score)over(partition by subject) from score_demo --- subject stu_name score max Chinese francs 70 80 Chinese matiler 70 80 Chinese tutu 80 80 English matiler 75 90 English francs 90 90 English tutu 60 90 Math francs 80 99 Math matiler 99 99 Math tutu 65 99
min() over()
演示min()聚合函数后接over属性的窗口函数，此窗口函数用来计算分组后数据的最小值
select subject,stu_name,score,min(score)over(partition by subject) from score_demo --- subject stu_name score min Chinese francs 70 70 Chinese matiler 70 70 Chinese tutu 80 70 English matiler 75 60 English francs 90 60 English tutu 60 60 Math francs 80 65 Math matiler 99 65 Math tutu 65 65
row_number()
row_number()窗口函数对结果集分组后的数据标注行号，从1开始的..
select row_number() over(partition by subject order by score desc),* from score_demo ---- row_number id subject stu_name score 1 3 Chinese tutu 80 2 1 Chinese francs 70 3 2 Chinese matiler 70 1 5 English francs 90 2 4 English matiler 75 3 6 English tutu 60 1 8 Math matiler 99 2 7 Math francs 80 3 9 Math tutu 65
如果不指定partition属性，row_number()就类似于oracle的rownum记录所有行号。如：
select row_number() over(order by id) rownum,* from score_demo ---- 1 1 Chinese francs 70 2 2 Chinese matiler 70 3 3 Chinese tutu 80 4 4 English matiler 75 5 5 English francs 90 6 6 English tutu 60 7 7 Math francs 80 8 8 Math matiler 99 9 9 Math tutu 65
rank()
rank()窗口函数和row_number()函数类似， 主要区分为当组内某行字段值相同，行号重复并且行号产生间隙，如下:
select rank() over(partition by subject order by score) ,* from score_demo ----- rank id subject stu_name score 1 2 Chinese matiler 70 1 1 Chinese francs 70 3 3 Chinese tutu 80 1 6 English tutu 60 2 4 English matiler 75 3 5 English francs 90 1 9 Math tutu 65 2 7 Math francs 80 3 8 Math matiler 99
从返回结果看到..第一二行都是1，第三行就3了，产生了间隙…
dense_rank()
dese_rank()函数和rank()函数类似，主要区别为当组内某个字段值相同时，虽然行号重复，但行号不产生间隙。
select dense_rank() over(partition by subject order by score),* from score_demo --- dense_rank id subject stu_name score 1 2 Chinese matiler 70 1 1 Chinese francs 70 2 3 Chinese tutu 80 1 6 English tutu 60 2 4 English matiler 75 3 5 English francs 90 1 9 Math tutu 65 2 7 Math francs 80 3 8 Math matiler 99
percent_rank()
percent_rank()函数：当前行的相对排名： (rank- 1) / (总行数 – 1)，得到的数在0-1之间[0,1)， 例如：限制序号在0~1之间(0作为第一个序)–>[0,1):
select percent_rank() over(partition by subject order by score),* from score_demo ---- percent_rank id subject stu_name score 0 2 Chinese matiler 70 0 1 Chinese francs 70 1 3 Chinese tutu 80 0 6 English tutu 60 0.5 4 English matiler 75 1 5 English francs 90 0 9 Math tutu 65 0.5 7 Math francs 80 1 8 Math matiler 99
cume_dist()
cume_dist()函数：当前行的相对排名： (当前行前面的行数 或 与当前行同等的行的行数)/(总行数)
注意:上面的percernt_rank()函数默认是从0开始排序的，如果需要使用相对0~1之间(0,1)的排名 例如，限制序号在0~1之间相对排名(0,1)
select cume_dist() over(partition by subject order by score),* from score_demo --- cume_dist id subject stu_name score 0.666666666666667 2 Chinese matiler 70 0.666666666666667 1 Chinese francs 70 1 3 Chinese tutu 80 0.333333333333333 6 English tutu 60 0.666666666666667 4 English matiler 75 1 5 English francs 90 0.333333333333333 9 Math tutu 65 0.666666666666667 7 Math francs 80 1 8 Math matiler 99
first_value()
first_value()函数用来取结果集每一个分组的第一行数的字段值。如：
select first_value(score) over(partition by subject ),* from score_demo --- first_value id subject stu_name score 70 1 Chinese francs 70 70 2 Chinese matiler 70 70 3 Chinese tutu 80 75 4 English matiler 75 75 5 English francs 90 75 6 English tutu 60 80 7 Math francs 80 80 8 Math matiler 99 80 9 Math tutu 65
所以通过first_value()函数很容易查询到分组数据的最大值和最小值。例如查询成绩表按课程分组同时取每门课程的最高分，如：
select first_value(score) over(partition by subject order by score desc) max_score,* from score_demo ---- max_score id subject stu_name score 80 3 Chinese tutu 80 80 1 Chinese francs 70 80 2 Chinese matiler 70 90 5 English francs 90 90 4 English matiler 75 90 6 English tutu 60 99 8 Math matiler 99 99 7 Math francs 80 99 9 Math tutu 65
last_value()
last_value函数用来取结果集每一个分组的最后一行数据的字段值。 如：
select last_value(score) over(partition by subject ),* from score_demo --- last_value id subject stu_name score 80 1 Chinese francs 70 80 2 Chinese matiler 70 80 3 Chinese tutu 80 60 4 English matiler 75 60 5 English francs 90 60 6 English tutu 60 65 7 Math francs 80 65 8 Math matiler 99 65 9 Math tutu 65
nth_value()
nth_value()函数用来取结果集每一个分组的指定行数据的字段值，语法：
nth_value(value any,nth integer) 其中： 1.value 指定表的字段 2.nth 指定结果集分组数据的第几行，如不存在则返回空..
例如成绩表按课程分组后去分组的第三行分数，如:
select nth_value(score,3) over(partition by subject),* from score_demo --- nth_value id subject stu_name score 80 1 Chinese francs 70 80 2 Chinese matiler 70 80 3 Chinese tutu 80 60 4 English matiler 75 60 5 English francs 90 60 6 English tutu 60 65 7 Math francs 80 65 8 Math matiler 99 65 9 Math tutu 65
窗口函数别名的使用
如果sql中需要多次使用到窗口函数，可以使用窗口函数别名，语法如：
select .. from .. WINDOW window_name as (window_definiton)
WINDOW 属性指定表的别名为window_name,可以给over属性引用，如下:
select avg(score) over(r),sum(score) over(r) ,* from score_demo WINDOW r as (PARTITION by subject) --- avg sum id subject stu_name score 73.3333333333333 220 1 Chinese francs 70 73.3333333333333 220 2 Chinese matiler 70 73.3333333333333 220 3 Chinese tutu 80 75 225 4 English matiler 75 75 225 5 English francs 90 75 225 6 English tutu 60 81.3333333333333 244 7 Math francs 80 81.3333333333333 244 8 Math matiler 99 81.3333333333333 244 9 Math tutu 65
