学习MySQL（五）：窗口函数

点击蓝字，关注我

Every Day

芙蓉楼送辛渐

[作者] 王昌龄 [朝代] 唐

寒雨连江夜入吴，平明送客楚山孤。

洛阳亲友如相问，一片冰心在玉壶。

窗口函数介绍

    窗口函数的引入是为了解决想要既显示聚集前的数据，又要显示聚集后的数据；窗口数对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

    强调：使用MySQL 8.0版本方可实现

基本语法

函数名（列） over（选项） 选项为partition by 列 order by 列

解释：

• over(partition by xxx) 按xxx分组的所有行进行分组

• over(partition by xxx order by aaa) 按列xxx分组，按列aaa排序

• over(order by aaa) 按aaa列排序

• over括号中的partition by和order by的使用根据具体情况选择

-- 需求：计算每个学生的及格科目数
-- 使用聚合函数，类似数据透视表，原有表结构已发生变化
SELECT student_id,count( sid ) FROM score WHERE num >= 60 GROUP BY student_id;


-- 使用窗口函数，不会更改原表结构
SELECT student_id,count( sid ) over ( PARTITION BY student_id ORDER BY student_id ) AS 及格数
FROM score WHERE num >= 60;

聚合窗口函数

语法：聚合函数（列） over（partition by 列 order by 列）

常见的聚合函数：sum() count() avg() max() min()

排序窗口函数

• row_number()：仅仅根据行号进行排序，相同结果则排序按照顺序依次排

• rank()：排名，与row_number函数不同的是，rank函数考虑到了over子句中排序字段值相同的情况，over子句中排序字段值相同的排序结果是一样的，后面字段值不相同的序号将跳过相同的排名号排下一个。如：11335

• dense_rank()：密集排序，用法跟rank类似，唯一不同是当排序结果相同时，排序不跳跃，而是紧跟排下一个。如：11223

• ntile()：桶排序，首先，ntile会先根据你的分组依据，然后把每个组的总记录数进行按照你给的ntile(n)里的数字n进行均分，这个数字就是桶数，例如一个组内总共12条记录，若n=6，则等划分成6桶，然后按照num的排序等级划分，12/6=2则每个桶两条记录，也就是112233445566的排序结果，常用于提取前百分之多少的应用场景。

都是排名函数，不同之处在对于名次相同的数据处理方式

-- 对每门课程进行排序
SELECT
s.sid,
s1.sname,
s1.gender,
c.cname,
s.num,
row_number() over ( PARTITION BY c.cname ORDER BY num DESC ) AS row_number排名,
rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS rank排名,
dense_rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名,
ntile( 6 ) over ( PARTITION BY c.cname ORDER BY num DESC ) AS ntile排名
FROM
score AS s
JOIN student AS s1 ON s.student_id = s1.sid
LEFT JOIN course AS c ON s.course_id = c.cid


-- 计算每门课程前三，考虑排名相同的情况
SELECT * FROM (
SELECT
s.sid,
s1.sname,
s1.gender,
c.cname,
s.num,
dense_rank() over ( PARTITION BY c.cname ORDER BY num DESC ) AS dense_rank排名
FROM
score AS s
JOIN student AS s1 ON s.student_id = s1.sid
LEFT JOIN course AS c ON s.course_id = c.cid
) AS a
WHERE
dense_rank排名