MySQL SQL高级功能

窗口函数

7.2 rank

案例：

按班级内成绩分别排名:

SELECT * , RANK () OVER (partition by 班级

ORDER BY 成绩 DESC) AS ranking

FROM 班级表

注意：group by分组汇总后改变了表的行数，一行只有一个类别。

而partition by和rank函数不会减少原表中的行数

7.3 rank, dense_rank, row_number区别

SELECT * ,

RANK() OVER (ORDER BY 成绩 DESC ) AS ranking，

DENSE_RANK() OVER (ORDER BY 成绩 DESC ) AS dense_rank,

ROW_number() OVER (ORDER BY 成绩 DESC ) AS row_number

FROM 班级表

rank函数：如有并列名次的行，会占用下一名次的位置。

dense rank函数：如果有并列名次的行，不占用下一名次的位置。

row number函数：不考虑并列名次的情况

面试经典排名问题

分清rank，dense_rank，row_number的用法

面试经典topN问题

分组取每组最大值、最小值、每组最大的N条记录

查询每个课程最高分

查询每个课程最低分

topN问题

SELECT *

FROM （

SELECT * ,

row_number() over (partition by 要分组的列名 order by 要排序的列名 DESC) AS ranking

FROM 表名） AS A WHERE ranking <= N;

7.4 聚合函数作为窗口函数

案例：

SELECT *,

sum(成绩) over (order by 学号) as current_sum,

avg(成绩) over (order by 学号) as current_avg,

count(成绩) over (order by 学号) as current_count,

max(成绩) over (order by 学号) as current_max,

min(成绩) over (order by 学号) as current_min

FROM 班级表;

聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果，其他聚合函数同理。

聚合函数作为窗口函数，可以在每一行的数据里直观看到，截止到本行数据，统计数据是多少。同时可以看出每一行数据对整体统计数据的影响。

案例

如何在每个组里比较

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

思路：1、先分组求出平均数

2、组内进行比较

因为要求数据行数不能减少，所以使用聚合窗口函数

SELECT *

FROM（SELECT *,

avg(成绩) over (partition by 科目 ) AS avg_score FROM 成绩表） AS b

WHERE 成绩>avg_score;

也可使用关联自查询：

SELECT *

FROM score AS S1

WHERE 成绩 > (SELECT AVG(成绩)

FROM score AS S2

WHERE S1.课程号=S2.课程号);

7.5 窗口函数的移动平均

rows 2 preceding，是“之前2行”的意思。也就是得到的结果是自身记录及前2行的平均。

用处：在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

7.6 总结

注意：

1.窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中。

2.partition 子句可省略，省略就是不指定分组，只排序。