北京网站建设要多少钱,电子商务网站的建设与流程,最新裁员公司名单,seo首页网站目录 一、窗口函数1. 窗口函数定义2. 窗口函数语法3. 演示表格一4. 窗口的确定4.1 例1#xff1a;查询各班级总分4.2 例2#xff1a;查询各班级累计总分4.3 分区子句#xff08;partition by#xff09;4.4 排序子句#xff08;order by#xff09;4.5 窗口子句#xff… 目录 一、窗口函数1. 窗口函数定义2. 窗口函数语法3. 演示表格一4. 窗口的确定4.1 例1查询各班级总分4.2 例2查询各班级累计总分4.3 分区子句partition by4.4 排序子句order by4.5 窗口子句rows4.6 总体执行流程 5. 函数分类5.1 排序类函数5.2 聚合类函数5.3 跨行类函数 二、相关题目练习6. 演示表格二7. 演示表格三 三、窗口函数问题与技巧总结 一、窗口函数
1. 窗口函数定义
窗口限定一个范围可以理解为满足某些条件的记录集合。窗口函数也就是在窗口范围内的执行的函数
2. 窗口函数语法
窗口函数有 over 关键字指定函数执行的范围。可分为三部分分组字句 (partition by)排序字句 (order by)滑动窗口字句 (rows)
函数名 over (partition by 分组的列名, order by 排序的列名 rows between 起始行 and 终止行)3. 演示表格一
create table gradeInfo1 (cid varchar(32),sname varchar(32),score int
);insert into gradeInfo1 (cid, sname, score)
values
(001, 张三, 78),
(001, 李四, 82),
(002, 小明, 90),
(001, 王五, 67),
(002, 小红, 85),
(002, 小刚, 90);cid班级idsname 学生姓名score分数001张三78001李四82002小明90001王五67002小红85002小刚90 4. 窗口的确定
4.1 例1查询各班级总分
# 方法一
select *,
sum(score) over(partition by cid) as 班级总分
from gradeinfo1# 方法二
select *,
sum(score) over(partition by cid order by score rows between unbounded preceding and unbounded following) as 班级总分
from gradeinfo14.2 例2查询各班级累计总分
# 方法一
select *,
sum(score) over(partition by cid order by score) as 班级总分
from gradeinfo1# 方法二
select *,
sum(score) over(partition by cid order by score rows between unbounded preceding and current row) as 班级总分
from gradeinfo14.3 分区子句partition by
① 不分区可以写成 partition by null或者直接不写 ② 后面可以跟多个列例如 partition by cid, sname partition by 和 group by的区别 partition by 不将行数去重group by 会将行数去重
4.4 排序子句order by
① 不排序可以写成 order by null或者直接不写 ② 后面可以跟多个列例如 order by cid, sname
4.5 窗口子句rows
语法概述 ① 起始行N preceding / unbounded preceding ② 当前行current row ③ 终止行N following / unbounded following 举例 ① 从分区内的前面所有行到当前行rows between unbounded preceding and current row ② 从分区内的前面 2 行 到当前行rows between 2 preceding and current row ③ 从分区内的当前行到后面所有行rows between current row and unbounded following ④ 从分区内的当前行到后面 3 行rows between current row and 3 following 注意 ① 若排序字句后缺少窗口字句窗口规范默认为rows between unbounded preceding and current row ② 若排序字句和窗口字句都缺少窗口规范默认为rows between unbounded preceding and unbounded following
4.6 总体执行流程
① 通过 partition by 个 order by 确定大窗口定义出上界和下界 ② 通过 rows 字句针对每一行数据确定小窗口即滑动窗口 ③ 针对每行的小窗口内的数据执行函数并生成新的列
5. 函数分类
5.1 排序类函数
① row_number序号不重复且序号连续即连续排名不考虑并列。例如123 … ② rank序号可重复且序号不连续即跳跃排名考虑并列。例如1224 … ③ dense_rank序号可重复且序号连续即连续排名考虑并列。例如1223 …
# 对每个班级的学生成绩进行排名
select *,
row_number() over(partition by cid order by score desc) as 不并列排名,
rank() over(partition by cid order by score desc) as 跳跃可并列排名,
dense_rank() over(partition by cid order by score desc) as 连续可并列排名
from gradeinfo15.2 聚合类函数
sumavgcountmaxmin
5.3 跨行类函数
① lead (比较的列N)返回当前行的后面 N 行 ② lag (比较的列N)返回当前行的前面 N 行
# 每个班级中成绩比自己高一名的成绩是多少
select *,
lag(score, 1) over(partition by cid order by score desc) as 高一名的分数
from gradeinfo1二、相关题目练习
6. 演示表格二
create table gradeInfo2 (cid varchar(32),sname varchar(32),course varchar(32),score int
);insert into gradeInfo2 (cid, sname, course, score)
values
(001, 张三, 语文, 78),
(002, 小刚, 语文, 71),
(001, 李四, 数学, 56),
(001, 王五, 数学, 97),
(002, 小明, 数学, 54),
(002, 小刚, 数学, 67),
(002, 小红, 数学, 82),
(001, 王五, 语文, 80),
(001, 张三, 数学, 77),
(002, 小明, 语文, 58),
(002, 小红, 语文, 87),
(001, 李四, 语文, 60),
(001, 张三, 英语, 66),
(002, 小刚, 英语, 50),
(001, 李四, 地理, 59),
(001, 王五, 地理, 88),
(002, 小明, 地理, 45),
(002, 小刚, 地理, 66),
(002, 小红, 地理, 82),
(001, 王五, 英语, 81),
(001, 张三, 地理, 77),
(002, 小明, 英语, 55),
(002, 小红, 英语, 87),
(001, 李四, 英语, 61)cid班级idsname 学生姓名course学科score分数001张三语文78002小刚语文71001李四数学56001王五数学97002小明数学54………… 题目一查询每个学生成绩最高的三个学科分组内 Top N 问题
select *
from(select *,row_number() over(partition by sname order by score desc) as rankingfrom gradeinfo2
) a
where ranking 3题目二查询每个学科都高于班级学科平均分的学生汇总分析问题
select sname
from(select *, score - course_avg_score as delfrom(select *,avg(score) over(partition by cid, course) as course_avg_scorefrom gradeinfo2) t1
) t2
group by sname
having min(del) 0 7. 演示表格三
create table employee(empno varchar(32),ename varchar(32),hire_date varchar(32),salary int,dept_no varchar(32)
);insert into employee(empno, ename, hire_date, salary, dept_no)
values
(001, Adam, 2018-03-01, 1000, A),
(002, Bill, 2021-03-01, 1200, A),
(003, Cindy, 2016-03-01, 1500, A),
(004, Danney, 2020-03-01, 5000, A),
(005, Eason, 2020-03-01, 4000, B),
(006, Fred, 2018-03-01, 3500, B),
(007, Gary, 2017-03-01, 1800, B),
(008, Hugo, 2020-03-01, 4500, B)empnoenamehire_datesalarydept_no001Adam2018-03-011000A002Bill2021-03-011200A003Cindy2016-03-011500A004Danney2020-03-015000A005Eason2020-03-014000B006Fred2018-03-013500B007Gary2017-03-011800B008Hugo2020-03-014500B
题目一查询每个部门工资最高的前三个员工信息分组内 Top N 问题
select *
from(select *,rank() over(partition by dept_no order by salary desc) as rankingfrom employee
) a
where ranking 3题目二查询员工工资占所属部门总工资的百分比汇总分析问题
select *,
concat(round(salary/total_salary*100, 3), %) as salary_percent
from(select *,sum(salary) over(partition by dept_no) as total_salaryfrom employee
) a 题目三对各部门员工的工资进行升序排列排名前 30% 为底层30% - 80% 为中层80% 以上为高层并打上标签
select *,
casewhen ranking_percent 0.3 then 底层when ranking_percent between 0.3 and 0.8 then 中层else 高层end as label
from(select *, round(ranking/total_employee, 2) as ranking_percentfrom(select *,rank() over(partition by dept_no order by salary) as ranking,count(empno) over(partition by dept_no) as total_employeefrom employee) a
) b题目四查询每年入职总数以及截至本年累计入职总人数本年总入职人数 本年之前所有年的入职人数之和
# 方法一
# 创建临时表
with
a as(select year(hire_date) as 入职年份, count(empno) as 入职员工数 from employeegroup by 入职年份order by 入职年份
)select *,
sum(入职员工数) over(rows between unbounded preceding and current row) as 累计入职总人数
from a# 方法二
select *,
sum(入职员工数) over(rows between unbounded preceding and current row) as 累计入职总人数
from(select year(hire_date) as 入职年份, count(empno) as 入职员工数 from employeegroup by 入职年份order by 入职年份
) a三、窗口函数问题与技巧总结
① 本文用窗口函数解决的 2 个主要问题分区内 Top N 问题汇总分析问题
② 分区内 Top N 公式
select *
from(select *,row_number() over(partition by 分区列 order by 比较列) as rankingfrom gradeinfo2
) a
where ranking N③ 窗口函数 → 生成辅助列用于计算百分比等
④ 重要with 子句 → 生成临时表把复杂的问题拆分成多个子问题再用临时表表达
