MySQL系列文章
什么是窗口函数
窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。
作用类似于在查询中对数据进行分组，不同的是，group分组操作会把分组的结果聚合成一条记录，而窗口函数是结果分组然后分别处理。
窗口函数是对 where 或者 group by 子句处理后的结果进行操作，所以窗口函数原则上只能写在 select 子句中
语法
select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名）
常见窗口函数
名称 |
描述 |
CUME_DIST() |
累积分配值 |
DENSE_RANK() |
当前行在其分区中的排名，稠密排序 |
FIRST_VALUE() |
指定区间范围内的第一行的值 |
LAG() |
取排在当前行之前的值 |
LAST_VALUE() |
指定区间范围内的最后一行的值 |
LEAD() |
取排在当前行之后的值 |
NTH_VALUE() |
指定区间范围内第N行的值 |
NTILE() |
将数据分到N个桶，当前行所在的桶号 |
PERCENT_RANK() |
排名值的百分比 |
RANK() |
当前行在其分区中的排名，稀疏排序 |
ROW_NUMBER() |
分区内当前行的行号 |
举例表
CREATETABLE 成绩单 ( 学号 VARCHAR(8), 姓名 VARCHAR(8), 科目 VARCHAR(8), 得分 INT) ENGINE = INNODB DEFAULT CHARSET = utf8;INSERTINTO 成绩单 VALUES('1000','小明','语文',112),('1000','小明','数学',120),('1000','小明','英语',92),('1001','云朵','语文',112),('1001','云朵','数学',118),('1001','云朵','英语',99),('1002','库里','语文',101),('1002','库里','数学',111),('1002','库里','英语',90),('1003','才子','语文',112),('1003','才子','数学',120),('1003','才子','英语',112),('1004','小华','语文',112),('1004','小华','数学',112),('1004','小华','英语',112),('1005','强森','语文',92),('1005','强森','数学',120),('1005','强森','英语',92);
排名函数
- RANK()，根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，但序号并列后中存在间隙。(1,1,1,4,5这种）
- DENSE_RANK()，根据排序字段为每个分组中的每一行分配一个序号。排名值相同时，序号相同，序号中没有间隙（1,1,1,2,3这种）
- ROW_NUMBER()，为表中的每一行分配一个序号，可以指定分组（也可以不指定）及排序字段(连续且不重复)
三个函数都是排名函数，按科目分组计算每科排名
SELECT*, RANK() OVER ( PARTITION BY 科目 ORDERBY 得分 DESC)AS RANK_排名 , DENSE_RANK() OVER ( PARTITION BY 科目 ORDERBY 得分 DESC)AS DENSE_RANK_排名 , ROW_NUMBER() OVER ( PARTITION BY 科目 ORDERBY 得分 DESC)AS ROW_NUMBER_排名 FROM 成绩单
TOPN问题
求各个科目排名第一的学生及得分
SELECT*FROM(SELECT*, row_number() OVER ( PARTITION BY 科目 ORDERBY 得分 DESC)AS rank_row FROM 成绩单 ) a WHERE rank_row =1;
通用公式:
select*from(select*, row_number() over (partition by 要分组的列名 orderby 要排序的列名 desc ）as ranking from 表明） as a where ranking<=N;
还有很多其他的用法，这里只是提一个最常见的场景
