SQL 窗口函数

背景

翻完《SQL必知必会》这本书之后, 去刷了一下SQL相关的题目. 发现其中一类题需要涉及到分组之后组内排序(如选出每组某个字段排序后前10的条目)这样的操作, 于是去查阅了相关的资料, 发现基本上使用窗口函数解决此类问题, 于是乎在此处总结一下窗口函数的用法.

窗口函数基本语法

<窗口函数> over ([partition by <用于分组的列名>]
                order by <用于排序的列名>)

窗口函数部分可以是专业的窗口函数rank() dense_rank() row_number() 等, 还可以是常见的聚合函数 avg sum count max min 等等操作. 此处并不是局限于专业窗口函数.

parition by: 指定分组列名, 此处如果不指定, 就是作用在全数据集上面, 而不是指定分组上面

order by: 指定排序的字段.

案例

创建一个成绩表, 里面含有学生id,班级id, 语文数学以及外语相关的分数.

CREATE TABLE IF NOT EXISTS `achievement` (
	`学号` char(5) NOT NULL,
    `班级` char(5) NOT NULL,
    `数学` int(3) NOT NULL,
    `语文` int(3) NOT NULL,
    `英语` int(3) NOT NULL,
    PRIMARY KEY (`学号`)
)ENGINE=InnoDB CHARSET=utf8mb4;

批量插入数据

INSERT INTO achievement VALUES('10001', '1',98, 96, 99);
INSERT INTO achievement VALUES('10002', '1',97, 96, 95);
INSERT INTO achievement VALUES('10003', '1',92, 95, 96);
INSERT INTO achievement VALUES('20001', '2',97, 96, 90);
INSERT INTO achievement VALUES('20002', '2',90, 96, 92);
INSERT INTO achievement VALUES('20003', '2',95, 96, 97);
INSERT INTO achievement VALUES('30001', '3',99, 96, 99);
INSERT INTO achievement VALUES('30002', '3',97, 99, 100);

SELECT * FROM achievement;

结果:

试验1: 查找出每一个班中数学成绩排名第一的学生的成绩信息.
首先是对班级进行分区 partition by 班级 然后以数学成绩进行降序排列 order by 数学 desc 构建一个含有每一个班级中数学排名的新字段排名

SELECT 学号, 班级, 数学, 语文, 英语, rank() over (partition by 班级 order by 数学 desc) AS 排名 FROM achievement 

在此基础上选择出 排名=1的行即可

SELECT 学号, 班级, 数学, 语文, 英语 FROM (SELECT 学号, 班级, 数学, 语文, 英语, rank() over (partition by 班级 order by 数学 desc) AS 排名 FROM achievement) As tmp WHERE 排名 = 1;

试验2: 选择全年级数学成绩排名前三的学生信息
可能有人直接就 ORDER BY 数学 DESC LIMIT 3 但是忽略了成绩相同的情况.

SELECT 学号, 班级, 数学, 语文, 英语, dense_rank() over (order by 数学 desc) as 排名 FROM achievement;

这里有两个点注意, 第一没有了partiton by 则是在全部数据集合上排序;第二此处窗口函数使用的是 dense_rank 上一个案例是 rank 看两者区别

SELECT 学号, 班级, 数学, 语文, 英语, rank() over (order by 数学 desc) as 排名 FROM achievement;

两者区别在于相同排名之后, dense_rank 会从下一个紧挨着的排名开始, 而 rank处理是相同的排名的行会”占用位置“, 下一个排名计数会不一样.

而row_number只是为每一行标个序号

SELECT 学号, 班级, 数学, 语文, 英语, row_number() over (order by 数学 desc) as 行号 FROM achievement;

完成这个题完全的sql语句如下

SELECT 学号, 班级, 数学, 语文, 英语 FROM (SELECT 学号, 班级, 数学, 语文, 英语, dense_rank() over (order by 数学 desc) as 排名 FROM achievement) AS tmp WHERE 排名 < 4;

试验3: 窗口函数用聚合函数替代

SELECT *, sum(数学) over (partition by 班级 order by 数学) as 数学分数 FROM achievement;

可以看到最后一列 结果很不一样 结果是按班级进行分组 然后组内按照数学分数升序排列累加计算数学分数之和. 这种操作会让人联想到group by 操作, 但是group by 操作是粗粒度的组内聚合计算, 最终结果是每组一行而不能像上面那样不改变行数, 而且group by 也没有组内排序功能.

SELECT 班级, SUM(数学) AS 数学分数 FROM achievement GROUP BY 班级;

相比之下窗口函数有更灵活的分区(partition by) 以及 组内排序功能(order by)