MYSQL强力排序新功能---窗口函数

MYSQL窗口函数

什么是窗口函数
含义：窗口函数也叫OLAP函数（Online Anallytical Processing,联机分析处理），可以对数据进行实时分析处理。

作用：

解决排名问题，e.g.每个班级按成绩排名
解决TOPN问题，e.g.每个班级前两名的学生
语法：

select 窗口函数 over (partition by 用于分组的列名， order by 用于排序的列名

分类：

专用窗口函数：rank()，dense_rank()，row_number()
汇总函数：max()，min()，count()，sum()，avg()
注意：窗口函数是对where后者group by子句处理后的结果进行操作，因此按照SQL语句的运行顺序，窗口函数一般放在select子句中。

窗口函数的用法
专用窗口函数
rank()函数

#按班级分类，将成绩降序排序
SELECT*,
rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
说明

rank()是排序函数，括号中不需要有参数；
通过partition by将班级分类，相当于之前用过的group by子句功能，但是group by子句分类汇总会改变原数据的行数，而用窗口函数自救保持原行数；
通过order by将成绩降序排列，与之前学的order by子句用法一样，后边可以升序asc或者降序desc；
总结：

窗口函数这里的“窗口”表示范围，可以理解为将原数据划分范围，即分组，然后用函数实现某些目的
窗口函数有分组和排序的功能
不减少原表的行数
2. 其他专用窗口函数：dense_rank/row_number

用法与rank（）函数相同
SELECT*,
dense_rank() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
SELECT*,
row_number() over ( PARTITION BY 班级 ORDER BY 成绩 DESC) AS ranking
FROM class;
当成绩相同时，会存在并列的情况，主要区别是三个函数如何处理并列情况：
在rank()函数，如果有并列情况，会占用下一个名次的位置，比如，成绩为100的学生有三个并列第一，那么99分的学生是第二名，通过rank()函数，名次是：1,1,1,4；

在dense()函数中，如果有并列的情况，不会占用下一个名词，同用上个例子，名次是：1,1,1,2；

在row_number()函数中，会忽略并列的情况，同用上述例子，名次是：1,2,3,4;

案例
#要求按成绩排名，如果出现并列，需要出现类似1,1,1,2的形式
SELECT *,
dense_rank() over (PARTITION BY 班级 ORDER BY 成绩) AS 排名
FROM class;

#在test1表中按成绩排名，如果有并列情况，则两个分数的排名相同，也就是平分后的下一个名词应该是下一个连续的整数值，名词之间不应该有间隔
dense_rank () over (ORDER BY score DESC) AS ranking
FROM test1;

经典面试问题-topN问题
相关业务问题：
每个类别下用户最喜欢的产品是哪个？
每个类别下用户点击最多的5个商品是什么？
这类问题就需要分组取最大值，最小值，每组最大的n条记录

2. 解决方法

分组取最大值(用关联子查询）
#查询每个学号成绩是最大的所有信息
SELECT* FROM score AS a
WHERE 成绩=( SELECT MAX(成绩)
FROM score AS b
WHERE a.学号=b.学号);

注意：因为0003号选择的三个课程成绩一样，所以最大成绩有三个

分组取最小值
#查询每个学号成绩是最大的所有信息
SELECT*
FROM score AS a
WHERE 成绩=( SELECT MIN(成绩)
FROM score AS b
WHERE a.学号=b.学号);

注意：因为0003号选择的三个课程成绩一样，所以最小成绩有三个

分组取最大N条记录
#查询每个学生成绩最高的两个科目
SELECT *
FROM (SELECT*,row_number() over (PARTITION BY 姓名 ORDER BY 成绩 DESC) AS ranking
FROM test1) AS newtest
WHERE ranking<=2;
说明

为了不受并列的影响，该题用row_number()
注意在子查询后边加别名
易错的写法：select*,row_number() over(partition by 姓名 order by 成绩 desc） as ranking from test where ranking<=2;按照sql运行顺序，where后边不能加别名，因为select子句在where子句之后运行
涉及到既要分组又要排序的情况，要想到用窗口函数
TOPN问题模板

SELECT *
FROM (SELECT*,row_number() over (PARTITION BY 姓名 ORDER BY 成绩 DESC) AS ranking
FROM test1) AS newtest
WHERE ranking<=N;
聚合函数作为窗口函数
作用：聚合函数作为窗口函数，是起到"累加/累计"的效果，比如，就是截止到本行，最大值？最小值是多少

与专用窗口函数的区别：括号中需要有指定列，不能为空

用法：与专用窗口函数相同

#查询成绩的累加
SELECT*, SUM(成绩) over (ORDER BY 成绩 DESC) AS ‘求和’
FROM class;
案例

#查找单科成绩高于该科目平均成绩的学生名单

法一解题思路：

单科成绩，表示需要按科目进行分组，分组有两种：group by子句和窗口函数的partition by；
要求平均值，需要通过avg()实现，并且找到大于平均值的学生，那么不能减少行数，则用partition by；
步骤：

聚合函数作为窗口函数

与平均值比较
注意这里不能用where 成绩>分组平均值，因为where子句在select子句之后执行

正确语句是套用子查询：

法二关联子查询

思路

单科成绩：需要对每门科目进行分组
平均成绩：avg()求每组的平均值
学生名单：输出信息中需要有学生姓名
步骤1：求分组平均值

SELECT AVG(成绩) AS 平均值,科目
FROM test3
GROUP BY 科目；
步骤2：比较

SELECT*
FROM test3 AS a
WHERE 成绩>(SELECT AVG(成绩) AS 平均值
FROM test3 AS b
WHERE a.科目=b.科目);
因为是按照科目分组，所以应该将科目进行关联

窗口函数的移动平均（以平均值为例）
作用：通过preceding,following,current row等调整作用范围，基本语法为

ROWS BETWEEN 一个时间点 AND 一个时间点

时间点可以表示为：

n PRECEDING : 前n行
n FOLLOWING：后n行
CURRENT ROW ： 当前行
UNBOUNDED PRECEDING：窗口第一行
UNBOUNDED FOLLOWING：窗口的最后一行
用法：

#查询前两行到当前行的平均成绩
SELECT *,
AVG(成绩) over (ORDER BY 姓名 ROWS 2 preceding) AS result
FROM test3;
解释：

rows N proceding—N表示在当前行的前N行，比如，N=2，当前行在第4行，那么该平均值是第2行，第3行，第4行，这三行数据的平均值

注意事项
窗口函数中的如果省略partition by，则结果不进行分组，则以整个表为范围，也就是窗口为整个表；
如果省略order by 则不进行排序；
总结
1.窗口函数的语法：

窗口函数 over (partition by 用于分列的列名 order by 用于排序的列名）；

2.功能：既能分组又可以排序，且不改变行数

3.分类：

专用窗口函数—rank(),dense_rank(),row_number()（括号中没有参数，注意区分三者区别）
聚合函数—max(),min(),sum(),avg(),count() (括号中有参数）
4.注意事项

原则上一般写在select子句中

5.应用场景

经典TOPN问题：找出每个部门工资排名前N的员工
模板：

select*
from（select*，row_number() over (partition by 部门 order by salary desc) as ranking
from 表名) as a
where ranking<=N;
注意：不要忘记起别名，子查询的别名以及排序结果的别名

经典排名问题
业务需求“在每个组内排名”，比如，每个部门按业绩来排名

在每个组内比较问题
可以用关联子查询，也可以用窗口函数实现