postgreSQL之开窗函数用法

本文最后更新于 1919 天前，其中的信息可能已经有所发展或是发生改变。

开窗函数语法相关

<窗口函数>
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

好了~~终于写完常用的窗口函数了~~啊呜…要不打个赏?

开窗函数语法相关

可作为窗口函数的函数分类

avg() over()

sum() over()

count() over()

max() over()

min() over()

row_number()

rank()

dense_rank()

percent_rank()

cume_dist()

first_value()

last_value()

nth_value()

窗口函数别名的使用

评论

发送评论编辑评论

开窗函数语法相关

可作为窗口函数的函数分类

avg() over()

sum() over()

count() over()

max() over()

min() over()

row_number()

rank()

dense_rank()

percent_rank()

cume_dist()

first_value()

last_value()

nth_value()

窗口函数别名的使用

评论

发送评论 编辑评论

发送评论编辑评论