1. 云图网首页
  2. 技术专区
  3. 数据库

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)

参考

原创文章，作者：3628473679，如若转载，请注明出处：https://blog.ytso.com/237884.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论