wordpress 网站备份,大连建设安全网站,湖南网站推广电话,做推广优化的网站有哪些内容文章目录 1. 窗口函数概述1.1 介绍1.2 作用 2. 场景说明2.1 准备工作2.2 场景说明2.3 分析2.4 实现2.4.1 非窗口函数方式实现2.4.2 窗口函数方式实现 3. 窗口函数分类4. 窗口函数基础用法#xff1a;OVER关键字4.1 语法4.2 场景一 :计算每个值和整体平均值的差值4.2.1 需求4.2… 文章目录 1. 窗口函数概述1.1 介绍1.2 作用 2. 场景说明2.1 准备工作2.2 场景说明2.3 分析2.4 实现2.4.1 非窗口函数方式实现2.4.2 窗口函数方式实现 3. 窗口函数分类4. 窗口函数基础用法OVER关键字4.1 语法4.2 场景一 :计算每个值和整体平均值的差值4.2.1 需求4.2.2 分析4.2.3 实现 4.3 场景二: 计算每个值占整体之和的占比4.3.1 需求4.3.2 分析4.3.3 非窗口函数实现4.3.4 窗口函数实现 5. PARTITION BY分区5.1 场景说明5.2 语法5.3 分析5.4 非窗口函数实现5.5 窗口函数实现5.6 GROUP BY 与 PARTITION BY的区别 6. 排名函数产生排名6.1 场景说明6.2 准备工作6.3 语法6.3 分析6.4 实现6.4.1 非窗口函数方式实现6.4.2 窗口函数方式实现6.4.2.1 rank6.4.2.2 dense_rank6.4.2.3 row_number6.4.2.4 总结 7. PARTITION BY和排名函数7.1 场景说明7.2 分析7.3 实现7.3.1 非窗口函数实现7.3.2 窗口函数实现 8. 排名 练习一8.1 准备工作8.2 需求: 去掉最高分和去掉最低分 求平均分8.3 分析8.4 实现 9. 排名 练习二9.1 需求:9.2 分析9.3 实现 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. 窗口函数分类 另外还有开窗聚合函数SUMAVGMINMAX。
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.gendera.gender),1) as avg_score,round(score - (select avg(b.score) from score b where b.gendera.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.scorea.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.subjecta.subject and b.scorea.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.rn11 and t1.rn219. 排名 练习二
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.rn11 and t1.rn21
group by region;感谢观看未完待续…
