窗口函数(Window function)之所以被称为“窗口函数”,是因为它在SQL查询中定义了一个可以在一组数据上进行计算的“窗口”或“范围”。这个窗口是由特定的排序和分区规则定义的数据集合。
通过使用窗口函数,我们可以执行许多常见的操作,例如:
- 计算每个组内的行计数、求和、平均值等聚合操作。
- 查询某个组内的最大值、最小值或其他排名相关的信息。
- 计算每个行与其它行之间的差异或百分比。
- 生成排序结果,并为每行分配一个排名或行号。
窗口函数的基本语法如下:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
<窗口函数>的位置,可以放以下两种函数:
1) 专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
2) 聚合函数,如sum. avg, count, max, min等
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
在有并列排名的情况下:
rank函数:如果有并列名次的行,会占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,4。
dense_rank函数:如果有并列名次的行,不占用下一名次的位置。比如正常排名是1,2,3,4,但是现在前3名是并列的名次,结果是:1,1,1,2。
row_number函数:不考虑并列名次的情况。比如前3名是并列的名次,排名是正常的1,2,3,4。
牛客:SQL33 找出每个学校GPA最低的同学
解法一:
select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university
解法二:
SELECT device_id,university,gpa FROM
(SELECT device_id,university,gpa,
RANK() over (PARTITION BY university ORDER BY gpa) rk FROM user_profile) a
WHERE a.rk=1;
解法三:
SELECT device_id,university,gpa FROM
(SELECT device_id,university,gpa,
RANK() over (PARTITION BY university ORDER BY gpa) rk FROM user_profile) a
WHERE a.rk=1;