窗口函数可以进行排序,生成序号等操作,结合查询语句,可以进行一些较为复杂的查询。本文主要介绍窗口函数的用法,以及相关的查询示例。
示例
现在有成绩表grade
calss | name | grade |
高一一班 | 张三 | 79 |
高一一班 | 李四 | 86 |
高一一班 | 王武 | 87 |
高一二班 | 王六 | 90 |
高一二班 | 张望 | 76 |
高一二班 | 刘大 | 88 |
SELECT *,RANK() OVER (PARTITION BY class ORDER BY grade) AS ranking
FROM grade;
在SELECT 子句中,可对已有数据进行分组排序。在上例中,即按照班级(class)分组,再对每个班级的学生成绩(grade)进行排序。
查询结果中展示排名数字。
执行SQL语句后的结果:
calss | name | grade | ranking |
高一一班 | 王武 | 87 | 1 |
高一一班 | 李四 | 86 | 2 |
高一一班 | 张三 | 79 | 3 |
高一二班 | 王六 | 90 | 1 |
高一二班 | 刘大 | 88 | 2 |
高一二班 | 张望 | 76 | 3 |
语法解析
<窗口函数> OVER (PARTITON BY 分组列 ORDER BY 排序列)
之所以叫窗口函数,是因为通过PARTITION BY可将数据划分为不同的数据范围,这个数据范围即称为窗口。如上例中,将数据按照班级划分为不同的数据范围。(同GROUP BY的分组相似)
用于排序的窗口函数主要包括:
- RANK
计算排序时,如果存在相同位次的记录,则会跳过之后的位次。
例:有三条记录并列排第一,则:1,1,1,4
- DENSE_RANK
计算排序,存在相同位次的记录,不会跳过之后的位次。
例:有三条记录并列排第一,则1,1,1,2
- ROW_NUMBER
连续排位
例:有三条记录并列排第一,则1,2,3,4
语句示例:
RANK() OVER (PARTITON BY class ORDER BY grade)
语句解析:
RANK() :函数后面的括号为习惯用法。里面不需要放置参数。上述其他窗口函数均使用括号。
PARTITON BY :设定排序的对象范围,上例中,按照不同的班级进行排名,因此PARTITON BY class。
ORDER BY:排序标准。上例中,按照每个班级中的成绩排名,因此ORDER BY grade。
此语法仅用在SELECT子句之后,为查询结果增添排序列。在SQL语句执行时,执行FROM中的数据表,再执行WHERE条件,最后才会在执行SELECT子句时,按照函数语句,对查询结果进行分组排序。
参考题目
链接:在gameList表中(player_id, event_d__牛客网
来源:牛客网
在gameList表中(player_id, event_date)是主键,如何显示每个玩家(player_id)首次登录的设备号(device_id),并同时显示玩家ID(player_id)?
player_id | device_id | event_date | games_played |
111 | 21 | 2020-03-01 | 5 |
111 | 21 | 2020-01-02 | 6 |
212 | 33 | 2020-09-03 | 1 |
322 | 11 | 2020-01-21 | 0 |
322 | 44 | 2020-03-02 | 5 |
解题思路:
子查询排序:对每个玩家的信息,按照登陆日期进行排序。将子查询结果作为临时表。
对子查询的结果,保留每个玩家排名第一的数据。
SELECT player_id, device_id
FROM
(SELECT *,RANK() OVER (PARTITION BY player_id ORDER BY event_date) AS ranking FROM gamelist)
WHERE ranking=1;
在上述语句中,执行子查询产生的结果如下:
player_id | device_id | event_date | games_played | ranking |
111 | 21 | 2020-03-01 | 5 | 2 |
111 | 21 | 2020-01-02 | 6 | 1 |
212 | 33 | 2020-09-03 | 1 | 1 |
322 | 11 | 2020-01-21 | 0 | 1 |
322 | 44 | 2020-03-02 | 5 | 2 |
子查询语句中,根据每个玩家的登陆日期进行排序。
再找出排名为1的数据,即每个玩家最早登陆的数据,据此找出对应的玩家(player_id)和设备号(device_id)。