Mysql-窗口函数一

1. 窗口函数概述

1.1 介绍

Mysql8.0新增窗口函数，窗口函数又被称为开窗函数，与Oracle窗口函数类似，属于Mysql的一大特点。非聚合窗口函数是相对于聚合函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变函数。

1.2 作用

• 查询每一行数据时，使用指定的窗口函数对每行关联的一组数据进行处理。
  
• 简单
  • 窗口函数更易于使用。
  • 之前需要通过定义临时变量和大量的子查询或关联才能完成的工作，使用窗口函数实现起来更加简洁高效。窗口函数也是面试及实际工作的高频点。
• 快速
  • 使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。
• 多功能性
  • 最重要的是，窗口函数具有多种功能，比如：求差值、求占比、求排名、累计值计算等等。

2. 场景说明

2.1 准备工作

-- 创建表格
CREATE TABLE score (id INT PRIMARY KEY,name VARCHAR(50),gender CHAR(1),score INT
);-- 插入数据
INSERT INTO score (id, name, gender, score) VALUES
(1, '贾宝玉', '男', 85),
(2, '林黛玉', '女', 90),
(3, '薛宝钗', '女', 78),
(4, '王熙凤', '女', 92),
(5, '史湘云', '女', 88),
(6, '贾琏', '男', 86),
(7, '贾环', '男', 87);

2.2 场景说明

需求：计算每个学生的分数和整体平均值的差值。

2.3 分析

第一步：求出平均分
第二步：差值=成绩-平均分

2.4 实现

2.4.1 非窗口函数方式实现

select id, name, gender, score,round((select avg(score) from score),1) as `平均分`,score - (round((select avg(score) from score),1)) as `差值`
from score;

2.4.2 窗口函数方式实现

select id, name, gender, score,round(avg(score) over(),1) as `平均分`,round(score - (avg(score) over()),1) as `差值`
from score;

3. 窗口函数分类

另外还有开窗聚合函数：SUM，AVG，MIN，MAX。

4. 窗口函数基础用法：OVER关键字

4.1 语法

select字段,... ...,<window_function> over(... ...)
from 表;

• OVER(...)的作用就是设置每一行数据关联的一组数据范围，OVER()时，每行关联的数据范围都是整张表的数据。
• <window function>表示使用的窗口函数，窗口函数可以使用之前已经学过的聚合函数，比如COUNT、SUM、AVG等，也可以是其他函数，比如 ranking 排序函数等。

4.2 场景一 :计算每个值和整体平均值的差值

4.2.1 需求

4.2.2 分析

4.2.3 实现

select id, name, gender, score,round(avg(score) over(),1) as `平均分`,round(score - (avg(score) over()),1) as `差值`
from score;

4.3 场景二: 计算每个值占整体之和的占比

4.3.1 需求

4.3.2 分析

4.3.3 非窗口函数实现

selectid, name, gender, score,(select sum(score) from score) as sum_score,round(100 * score / (select sum(score) from score),1) as rate
from score;

4.3.4 窗口函数实现

selectid, name,gender, score,sum(score) over() as sum_score,round(100 * score / (sum(score) over()),1) as rate
from score;

5. PARTITION BY分区

5.1 场景说明

• 如何计算每个学生的Score 分数和同性别学生平均分的差值？
  

5.2 语法

• partition by 作用：用于对整张表的数据进行分区(分组)操作。

select字段,... ...,<window_function> over(partition by 字段 ...)
from 表;

• PARTITION BY 列名, ... 的作用是按照指定列的值对整张表的数据进行分区，OVER()中没有PARTITION BY时，整张表就是一个分区。
• 分区之后，在处理每行数据时，<window function>是作用在该行数据关联的分区上，不再是整张表。

5.3 分析

第一步：求出平均分（按性别分组求）

第二步：求出每个人的分数与平均分的差值

5.4 非窗口函数实现

selectid, name,gender, score,round((select avg(b.score) from score b where b.gender=a.gender),1) as avg_score,round(score - (select avg(b.score) from score b where b.gender=a.gender),1) as diff
from score a;

5.5 窗口函数实现

selectid, name,gender, score,round(avg(score) over(partition by gender),1) as avg_score,round(score-(avg(score) over(partition by gender)),1) as rate
from score ;

5.6 GROUP BY 与 PARTITION BY的区别

• 使用场景不同
  • GROUP BY分组是为了聚合，分组聚合属于：多进一出。
  • PARTITION BY分区是为了配合窗口函数做运算，窗口函数属于：一进一出。
    
    
    

6. 排名函数：产生排名

6.1 场景说明

6.2 准备工作

-- 创建学生成绩表
create table student_scores (studentname varchar(50) not null,subject varchar(50) not null,score int not null,primary key (studentname, subject)
);-- 插入张三的成绩数据
insert into student_scores (studentname, subject, score) values ('张三', '语文', 81);
insert into student_scores (studentname, subject, score) values ('张三', '数学', 75);-- 插入李四的成绩数据
insert into student_scores (studentname, subject, score) values ('李四', '语文', 76);
insert into student_scores (studentname, subject, score) values ('李四', '数学', 90);-- 插入王五的成绩数据
insert into student_scores (studentname, subject, score) values ('王五', '语文', 81);
insert into student_scores (studentname, subject, score) values ('王五', '数学', 100);

6.3 语法

• 排名函数作用：用于按照指定列对每一行产生一个所在分区内的排名序号。

select字段,... ...,<排名函数> over(order by 字段 ...)
from 表;

• OVER() 中可以指定 ORDER BY 按照指定列对每个分区内的数据进行排序。
• 排名函数用于对分区内的每行数据产生一个排名序号。
  • RANK、DENSE_RANK、ROW_NUMBER。

6.3 分析

6.4 实现

6.4.1 非窗口函数方式实现

-- 传统方式 排名 并列 且 排名连续
selectstudentname, subject, score,(select count(distinct score) from student_scores b where b.score>a.score) + 1 as `排名`
from student_scores a
order by score desc;

6.4.2 窗口函数方式实现

6.4.2.1 rank

-- 排名并列 但 排名不连续
select studentname,subject,score,rank() over (order by score desc) as `排名`
from student_scores;

6.4.2.2 dense_rank

-- 排名并列 且 排名连续
select studentname,subject,score,dense_rank() over (order by score desc) as `排名`
from student_scores;

6.4.2.3 row_number

-- 排名连续 但 不考虑排名并列
select studentname,subject,score,row_number() over (order by score desc) as `排名`
from student_scores;

6.4.2.4 总结

• RANK()：有并列的情况出现时序号会重复但不连续。
• DENSE_RANK()：有并列的情况时序号会重复但连续。
• ROW_NUMBER()：返回连续唯一的行号，序号不会重复且连续。
  

7. PARTITION BY和排名函数

• 规律: 只要碰到每个每种等类似词汇, 肯定分组
  • group by : 多进一出
  • partition by : 一进一出

  7.1 场景说明

  • 先分组, 再排名
    

7.2 分析

7.3 实现

7.3.1 非窗口函数实现

selectstudentname,subject, score,(select count(*) + 1 from student_scores b where b.subject=a.subject and b.score>a.score) as `排名`
from student_scores a
order by subject,`排名`;

7.3.2 窗口函数实现

selectstudentname, subject, score,rank() over (partition by subject order by score desc) as `排名`
from student_scores;

8. 排名 练习一

8.1 准备工作

-- 创建一个名为 Students 的表，增加区域列
create table Students (id int primary key auto_increment,  -- 学生ID，自动递增name varchar(50),                    -- 学生姓名score int,                           -- 学生成绩region varchar(10)                   -- 学生区域（魏、蜀、吴）
);-- 向 Students 表中插入数据，使用三国人物作为姓名及区域
insert into Students (name, score, region) values
-- 魏国人物
('曹操', 95, '魏'),
('司马懿', 89, '魏'),
('荀彧', 84, '魏'),
('甄氏', 91, '魏'),
('夏侯惇', 88, '魏'),
-- 蜀国人物
('刘备', 85, '蜀'),
('关羽', 92, '蜀'),
('张飞', 78, '蜀'),
('诸葛亮', 88, '蜀'),
('黄承儿', 80, '蜀'),
-- 吴国人物
('孙权', 76, '吴'),
('周瑜', 90, '吴'),
('鲁肃', 83, '吴'),
('陆逊', 86, '吴'),
('小乔', 87, '吴');

8.2 需求: 去掉最高分和去掉最低分 求平均分

8.3 分析

8.4 实现

with t1 as (select*,row_number() over (order by score asc) rn1,row_number() over (order by score desc) rn2from Students
)
selectround(avg(score), 2) as avg_score
from t1
where t1.rn1>1 and t1.rn2>1

9. 排名 练习二

9.1 需求:

求每个部门 去掉最高分和去掉最低分 求平均分

9.2 分析

9.3 实现

-- 需求2: 求每个部门 去掉最高分和去掉最低分 求平均分
with t1 as (select*,row_number() over (partition by region order by score asc) rn1,row_number() over (partition by region order by score desc) rn2from Students
)
selectround(avg(score), 2) as avg_score
from t1
where t1.rn1>1 and t1.rn2>1
group by region;

感谢观看，未完待续…