PostgreSQL: Window Functions 初步使用

PostgreSQL 支持 Window Functions,可以对查询出的结果集进行分组处理,非常方便,接下来举个简单的例子演示下。

创建测试表

创建一张成绩表,取各科目成绩最高的同学。

1
2
3
4
5
6
7
8
9
10
11
12
francs=> create table score ( id serial primary key,subject varchar(32),stu_name varchar(64),score numeric(3,0) );  
NOTICE: CREATE TABLE will create implicit sequence "score_id_seq" for serial column "score.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "score_pkey" for table "score"
CREATE TABLE

francs=> \d score
Table "francs.score"
Column | Type | Modifiers
----------+-----------------------+-----------
subject | character varying(32) |
stu_name | character varying(64) |
score | numeric(3,0) |

插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
francs=> insert into score ( subject,stu_name,score ) values ('Chinese','francs',70);  
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','francs',90);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','francs',80);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Chinese','fpzhou',70);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','fpzhou',75);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','fpzhou',99);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Chinese','tutu',80);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('English','tutu',60);
INSERT 0 1
francs=> insert into score ( subject,stu_name,score ) values ('Math','tutu',65);
INSERT 0 1

francs=> select * from score;
subject | stu_name | score
---------+----------+-------
Chinese | francs | 70
English | francs | 90
Math | francs | 80
Chinese | fpzhou | 70
English | fpzhou | 75
Math | fpzhou | 99
Chinese | tutu | 80
English | tutu | 60
Math | tutu | 65
(9 rows)

方法一: 使用窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
select *  
from (select subject,
stu_name,
score,
row_number() over(partition by subject order by score desc ) as score_desc
from score) as order_score
where score_desc < 2 order by subject ;
subject | stu_name | score | score_desc
---------+----------+-------+------------
Chinese | tutu | 80 | 1
English | francs | 90 | 1
Math | fpzhou | 99 | 1
(3 rows)

备注:这里使用了 windows function ,其中 “over(partition by subject order by score desc )”是 windows function 核心,”partition by ..”表示将结果集根据指定字段进行分组,上例中是将结果集根据 subject 进行分组; “order by ..” 是指将每组的结果集根据指定字段排序。

方法二: 使用嵌套查询

1
2
3
4
5
6
7
8
9
10
11
12
13
select a.*  
from score a,
(select subject, max(score) as score from score group by subject) b
where a.subject = b.subject
and a.score = b.score
order by a.subject;

id | subject | stu_name | score
----+---------+----------+-------
7 | Chinese | tutu | 80
2 | English | francs | 90
6 | Math | fpzhou | 99
(3 rows)

备注:暂且不考虑语句效率,仅实现功能。

在结果集中增加各科目平均成绩信息

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> select subject,stu_name,score,avg(score) over (partition by subject) from score;  
subject | stu_name | score | avg
---------+----------+-------+---------------------
Chinese | fpzhou | 70 | 73.3333333333333333
Chinese | francs | 70 | 73.3333333333333333
Chinese | tutu | 80 | 73.3333333333333333
English | fpzhou | 75 | 75.0000000000000000
English | francs | 90 | 75.0000000000000000
English | tutu | 60 | 75.0000000000000000
Math | francs | 80 | 81.3333333333333333
Math | tutu | 65 | 81.3333333333333333
Math | fpzhou | 99 | 81.3333333333333333
(9 rows)

Row_number() 窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> select subject,stu_name,score,row_number() over (partition by subject) from score;  
subject | stu_name | score | row_number
---------+----------+-------+------------
Chinese | fpzhou | 70 | 1
Chinese | francs | 70 | 2
Chinese | tutu | 80 | 3
English | fpzhou | 75 | 1
English | francs | 90 | 2
English | tutu | 60 | 3
Math | francs | 80 | 1
Math | tutu | 65 | 2
Math | fpzhou | 99 | 3
(9 rows)

备注:使用 row_number() over … 函数可以对结果集按某字段分组后的记录进行标记,因此使用 row_number() 窗口函数很容易实现取分组后指定记录的功能。

Rank() over 窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> select subject,stu_name,score,rank() over (partition by subject order by score desc) from score;  
subject | stu_name | score | rank
---------+----------+-------+------
Chinese | tutu | 80 | 1
Chinese | francs | 70 | 2
Chinese | fpzhou | 70 | 2
English | francs | 90 | 1
English | fpzhou | 75 | 2
English | tutu | 60 | 3
Math | fpzhou | 99 | 1
Math | francs | 80 | 2
Math | tutu | 65 | 3
(9 rows)

备注: rank() 窗口函数和 ow_number() 窗口函数类似,但 rank() 窗口函数会将结果集分组后相同值的记录的标记相等,例如上例中红色的记录。

取行号

取结果集行号,相当于Oracle 里的 rownum

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> select row_number() OVER (ORDER BY id) AS rownum ,* from score;  
rownum | id | subject | stu_name | score
--------+----+---------+----------+-------
1 | 1 | Chinese | francs | 70
2 | 2 | English | francs | 90
3 | 3 | Math | francs | 80
4 | 4 | Chinese | fpzhou | 70
5 | 5 | English | fpzhou | 75
6 | 6 | Math | fpzhou | 99
7 | 7 | Chinese | tutu | 80
8 | 8 | English | tutu | 60
9 | 9 | Math | tutu | 65
(9 rows)

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%