目录
10, 计算每个员工的累积薪资分布（即薪资小于或等于当前员工薪资的员工比例）
一，概念之 窗口函数是个啥?
窗口函数是sql 8.0版本之后推出的一个新函数,又称作开窗函数;
它允许用户在不改变查询结果集的行数前提下,对每一行指定聚合计算或者更复杂的计算.这些计算是基于当前行和结果集中其他行之间的关系进行的,从而提供对数据的深入分析和处理能力;
说白话就是,
在原有的结果集上面单独开一列,这一列可以根据不同的窗口函数,执行聚合,排序,分布,抽样,对比等,来进一步对结果集运算.
更通俗的举例说,分组+聚合之后,查询结果集会变成单行单列.而使用开窗函数 分组+聚合之后结果集依旧可以呈现多行多列的效果.
普通分组聚合函数 和窗口函数对比示意图:
二，使用窗口函数和不使用窗口函数 做对比
初始数据.以下出去案例部分之后,都是这个数据作为初始数据;
use test;
create table employee(
dname varchar(20), -- 部门名
eid varchar(20),
ename varchar(20),
hiredate date, -- 入职日期
salary double -- 薪资
);
insert into employee values('研发部','1001','刘备','2021-11-01',3000);
insert into employee values('研发部','1002','关羽','2021-11-02',5000);
insert into employee values('研发部','1003','张飞','2021-11-03',7000);
insert into employee values('研发部','1004','赵云','2021-11-04',7000);
insert into employee values('研发部','1005','马超','2021-11-05',4000);
insert into employee values('研发部','1006','黄忠','2021-11-06',4000);
insert into employee values('销售部','1007','曹操','2021-11-01',2000);
insert into employee values('销售部','1008','许褚','2021-11-02',3000);
insert into employee values('销售部','1009','典韦','2021-11-03',5000);
insert into employee values('销售部','1010','张辽','2021-11-04',6000);
insert into employee values('销售部','1011','徐晃','2021-11-05',9000);
insert into employee values('销售部','1012','曹洪','2021-11-06',6000);1，排序对比:
普通排序
-- 对每个部门的员工按照薪资排序，并给出排名
select dname,employee.ename,salary
from employee order by dname ,salary desc ;结果
窗口函数排序
-- 对每个部门的员工按照薪资排序，并给出排名
-- 使用窗口函数 多增加一列 renk() 排序 有相同就并列 并且吃掉下列的序号
select dname,
ename,
salary,
rank() over (partition by dname order by salary desc ) as rn
from employee ;
2，分组+聚合对比
普通分组+聚合函数
-- 计算不同部门的总薪资 并且降序排序
select dname,
sum(salary)
from employee group by dname order by sum(salary) desc ;
窗口函数 分组+聚合样式
select dname,
sum(salary) over(partition by dname order by salary desc rows between unbounded preceding and unbounded following ) rn
from employee;
三，作用 之 我能用窗口函数干什么?
- 1,分组后聚合,开一个新列展示信息,而非原来直接合并成一行;
- 2,排序,按照不同格式为结果集排序,可以更高效的分组后求tonN;
- 3,定义窗口其实和结束范围,就可以自动执行滑动窗口的计算.可以用来递增求连续几天或者连续几月或者连续几年的产品销售累计额;
- 4,可以集中的对比上一行或者下一行,也可以指定区间行的值 进行比较分析;
- 5,可以快速获取每个分区中第一行和最后一行的数据信息;
四，特点
- 1,不会动用原始数据,窗口函数的特点就是单独开一列,对原始数据的查询结果集不做更改;
- 2,灵活性高,对于分组和聚合,灵活性更加好;
- 3,功能强大,排序,聚合,计算,分布,抽样等.可以综合处理复杂的业务逻辑和数据分析任务;
五，语法
窗口函数语法
分类函数名 ( 字段参数 ) OVER (
PARTITION BY 分组的列...
ORDER BY 排序列...
[ rows between 起始行 and 结束行
unbound preceding 表示第1行
n preceding n表示数字, 表示向上n行, 例如: 3 preceding 表示 向上 3行.
current row 表示 当前行
n following n表示数字, 表示向下n行, 例如: 3 following 表示 向下 3行.
unbound following 表示最后1行
]
)
六，窗口范围
关于 窗口范围值详解释：
1，分组后 窗口范围默认是 组内 最开始行 到最后行；
分组后 窗口范围默认 是最开始到最最后；
-- 聚合+开窗 +查询范围
-- 默认的查询 范围 最开始到最后
select dname,ename,salary,hiredate,sum(salary)over(partition by dname)
from employee; 
2，分组+排序之后 窗口范围 默认是 最开始到当前行；
分组+排序之后窗口范围默认是 开始到当前行；
-- 当前行
select dname,ename,salary,hiredate,
sum(salary)over (partition by dname order by salary ) so
from employee ; 
3，设置 窗口范围 最开始到最后
手动指定 从最开始到最后；
-- 向上无边界 向下无边界
select dname,ename,salary,hiredate,
sum(salary)over (partition by dname rows between unbounded preceding and unbounded following)
from employee; 
4，设置窗口范围 最开始到当前行
从最开始到当前行；
select dname,ename,salary,hiredate,
sum(salary)over (partition by dname rows between unbounded preceding and current row ) so
from employee;
5，自定义窗口范围
窗口范围指定从某某行开始 到 某某行结束
-- 指定范围
select dname,ename,salary,hiredate,
sum(salary) over(partition by dname rows between 1 preceding and 2 following) as rn
from employee;
七，分类
可以使用窗口函数的函数分类，排序，聚合，分布，抽样，首尾，前后
1，排序开窗函数
用于为结果集中的行分配序号或排名。
rank() over();
排序,有相同就合并,不保留序号;
案例
-- 查询员工表 信息 并求出各部门 不同员工的薪资 对齐排序
select dname,
ename,
salary,
rank() over (partition by dname order by salary desc ) as rn
from employee ;
row_number()over(),
排序,不合并序号,哪怕相同
select employee.dname,employee.ename,employee.salary,
row_number() over (partition by employee.dname order by salary desc ) as rn
from employee; 
dense_rank()over()
排序,有相同并列,保留序号
-- 使用 dense_rank() 有相同就并列 不吃掉后面的序号
select dname,
ename,
salary,
dense_rank() over (partition by dname order by salary) as rn
from employee; 
排序函数 rank,row_number,dense_rank 对比
2，聚合开窗函数
五大聚合函数 sum ,max,min,count,avg 后面都可以跟上over 执行开窗函数
avg
案例演示 求不同部门的最大薪资;
-- 求不同部门的最大薪资
select
dname,
ename,
salary,
max(salary) over(partition by dname) as pv1
from employee;
count
案例 求不同部门的人数 count
select
dname,
ename,
salary,
count(dname) over(partition by dname) as pv1
from employee;
avg
min
sum
平均值,最小值,总值 略.
3,分布开窗函数
cume_dist()over();
分布函数主要用于计算分组内数据的分布情况，例如计算累积分布、百分位排名等。这些函数可以帮助分析师了解数据的分布情况，从而进行更深入的数据分析和挖掘。
案例:我们需要查询每个部门内 薪资小于或等于当前员工薪资的比例
select dname,ename,salary,
cume_dist() over (order by salary)as rn1,
cume_dist() over (partition by dname order by salary) rn2
from employee;
rn列
第一行后面是0.25 是这么计算来的,刘备薪资3000 当前部门小于等于3000 有3位 3/12 得到0.25
第二行后面是0.41666 马超薪资是4000 当前部门小于等于4000 的有5位位 5/12 得到0.41666
……
rn2列
第一行后面时 0.16666 在本部门研发部 小于等于3000的只有1人 1/6 =0.166666
第二行后面时0.5 小于等于4000 的有3人 所以3/6 得到0.5
……
percent_rank()over()
用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
-- 计算薪资的分布情况 相对排名 0到1 之间
select
dname,
ename,
salary,
rank() over (partition by dname order by salary ) rn,
percent_rank() over (partition by dname order by salary ) rn2
from employee;
rn2列
第一行是0 当前行的值= 当前行的rank的值1- 1/ 部门总数是6 - 1 得到0；
第二行是0.2 当前行的值= 当前行rank的值2 -1 / 部门总是是6-1 得到1/5 值为0.5
……
4,前后开窗函数
前面函数都没有参数 。这2个函数里面允许有三个参数
分别是 字段，序号，默认值
lag()over()
-- lag() 参数 字段 和往前多少行数
select
dname,
ename,
salary,
hiredate,
lag(hiredate,1) over (partition by dname ) ,
lag(hiredate,2) over (partition by dname )
from employee;
以日期字段 往前1行 分组时部门；
rn2 是以日期字段 要当前2行的信息；
lead()over()
select
dname,
ename,
salary,
hiredate,
lead(hiredate,1,'我前面1行没有值') over (partition by dname order by salary),
lead(hiredate,2,'我前面2行没有值') over (partition by dname order by salary)
from employee;
没有值的时候 用第三个参数作为默认值代替；
5,首尾开窗函数
这两个函数 允许有一个参数 参数是字段名
first_value()over()
last_value()over()
这2个案例一起演示
#--截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
select
dname,
ename,
salary,
hiredate,
first_value(salary) over
(partition by dname order by hiredate
rows between unbounded preceding and unbounded following) rn1, -- 求到了最高薪资 开了新的一列
last_value(salary) over
(partition by dname order by hiredate
rows between unbounded preceding and unbounded following) rn2 -- 求到了最高薪资 开了新的一列
from employee; 
6,抽样开窗函数
ntile()over()
好比扑克牌的方式 指定分组， 可以用来抽样
发牌一样 分3组；
-- 抽样窗口函数
-- 像发扑克牌一样 给分组之后 在发牌的方式分组
select
dname,
ename,
salary,
hiredate,
ntile(3) over (partition by dname)
from employee;
发牌一样 分2组
select
dname,
ename,
salary,
hiredate,
ntile(4) over (partition by dname)
from employee;
7,分组求N开窗函数
nth()over()
用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
-- 截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资 分组求top 第二个和第三个
select
dname,
ename,
salary,
hiredate,
nth_value(salary,2) over (partition by dname order by salary) rn1,
nth_value(salary,3) over (partition by dname order by salary)
from employee;
八，案例
1，经典案例之 分组求topN
求出每个部门薪资排在前三名的员工- 分组求TOPN
-- 求出每个部门薪资排在前三名的员工- 分组求TOPN
select dname,ename,salary,
row_number() over (partition by dname order by salary) as rn
from employee;
-- 最终答案
select * from
(select dname,ename,salary,
row_number() over (partition by dname order by salary) as rn
from employee) as t1 where t1.rn <=3;
如果这道题目不用窗口函数 普通的sql还是比较难写的；
2，经典案例之 连续登陆天数
初始数据
# 网站点击量表
create database db1;
use db1;
create table website_pv_info(
cookieid varchar(20), # 用户id
createtime varchar(20), # 访问时间
pv int # 页面浏览量
);
insert into website_pv_info value ('cookie1', '2018-04-10', 1);
insert into website_pv_info value ('cookie1', '2018-04-11', 5);
insert into website_pv_info value ('cookie1', '2018-04-12', 7);
insert into website_pv_info value ('cookie1', '2018-04-13', 3);
insert into website_pv_info value ('cookie1', '2018-04-14', 2);
insert into website_pv_info value ('cookie1', '2018-04-15', 4);
insert into website_pv_info value ('cookie1', '2018-04-16', 4);
insert into website_pv_info value ('cookie2', '2018-04-10', 2);
insert into website_pv_info value ('cookie2', '2018-04-11', 3);
insert into website_pv_info value ('cookie2', '2018-04-12', 5);
insert into website_pv_info value ('cookie2', '2018-04-13', 6);
insert into website_pv_info value ('cookie2', '2018-04-14', 3);
insert into website_pv_info value ('cookie2', '2018-04-15', 9);
insert into website_pv_info value ('cookie2', '2018-04-16', 7);# 判断某个账号(用户cookieid)是否连续登陆 3天.
## 查询当前日期 到前3天的 日期
select cookieid,createtime,pv,
lag(createtime,3) over (partition by cookieid order by createtime) as rn
from website_pv_info;
#进一步筛选。 要求三天以内的有数值
select * from (select cookieid,createtime,pv,
lag(createtime,2) over (partition by cookieid order by createtime) as rn
from website_pv_info) t1 where datediff(createtime,t1.rn) =2; #判断三天以内的数值 第一个参数和第二个参数 时间差
## rn有值 即可代表 有连续登录第一个sql查询结果
第二个的sql查询的结果
根据上述表格的练习题
3，显示用户(cookieid)上一次的访问时间.
# 需求: 显示用户(cookieid)上一次的访问时间.
/*
每个用户
首次访问 lag上一条
*/
select cookieid,pv,createtime,
lag(createtime,1,'首次访问') over (partition by website_pv_info.cookieid order by createtime)
from website_pv_info;
4，根据cookieid进行分组, 获取每组点击量最高的前2名数据, 这个就是经典的案例: 分组求TopN
# 需求: 根据cookieid进行分组, 获取每组点击量最高的前2名数据, 这个就是经典的案例: 分组求TopN
select cookieid,pv,
row_number() over (partition by cookieid order by pv desc ) rn
from website_pv_info ;
select * from (select cookieid,pv,
row_number() over (partition by cookieid order by pv desc ) rn
from website_pv_info) t1 where t1.rn<=2;
5，根据点击量(pv)做排名, 组内排名.
# 需求: 根据点击量(pv)做排名, 组内排名.
#第一种
select cookieid,createtime,pv,
rank() over (partition by cookieid order by pv ) rn
from website_pv_info;
#第二种排名方式
select website_pv_info.cookieid,website_pv_info.createtime,website_pv_info.pv,
row_number() over (partition by cookieid order by pv) rn
from website_pv_info;
#第三种排名方式
select cookieid,createtime,pv,
dense_rank() over (partition by cookieid order by pv) rn
from website_pv_info;
6， 统计每个cookieid的pv(访问量), 只统计: 当前行及 向前3行 向后1行
# 需求: 统计每个cookieid的pv(访问量), 只统计: 当前行及 向前3行 向后1行
select
cookieid,
website_pv_info.createtime,
sum(pv) over (partition by cookieid rows between 3 preceding and 1 following)
from website_pv_info;
窗口函数综合练习题二
原始数据
CREATE TABLE employee_salaries (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10, 2)
);
INSERT INTO employee_salaries (EmployeeID, EmployeeName, Department, Salary)
VALUES
(1, 'Alice', 'Sales', 50000),
(2, 'Bob', 'Sales', 55000),
(3, 'Charlie', 'HR', 48000),
(4, 'David', 'HR', 45000),
(5, 'Eve', 'Engineering', 65000),
(6, 'Frank', 'Engineering', 60000),
(7, 'Grace', 'Sales', 52000),
(8, 'Helen', 'HR', 50000),
(9, 'Ian', 'Engineering', 70000),
(10, 'Jack', 'Sales', 58000);7,查询每个部门的平均薪资
# 查询每个部门的平均薪资：
select EmployeeName,avg(Salary) from employee_salaries group by EmployeeName
8,查询每个部门内员工的薪资排名：
使用RANK()窗口函数，按Department分区，并按Salary降序排序。
# 查询每个部门内员工的薪资排名：
# 使用RANK()窗口函数，按Department分区，并按Salary降序排序。
select EmployeeID,
EmployeeName,
Department,
Salary,
rank() over (partition by Department order by Salary desc) rn
from employee_salaries;
9,查询每个部门薪资高于部门平均薪资的员工：
使用子查询或窗口函数计算部门平均薪资，然后筛选出薪资高于该平均值的员工。
select * from
(select
EmployeeID,
EmployeeName,
Department,
Salary,
avg(Salary) over(partition by Department ) rn
from employee_salaries) t1
where t1.Salary< t1.rn;
解法二 使用子查询
select * from employee_salaries es ,
(select Department,avg(Salary) as av
from employee_salaries group by Department) t1
where es.Department=t1.Department and es.Salary<t1.av;
10, 计算每个员工的累积薪资分布（即薪资小于或等于当前员工薪资的员工比例）：
# 使用CUME_DIST()窗口函数，按Department分区，并按Salary降序排序。
# 计算每个员工的累积薪资分布（即薪资小于或等于当前员工薪资的员工比例）：
# 使用CUME_DIST()窗口函数，按Department分区，并按Salary降序排序。
select EmployeeID,
EmployeeName,
Salary,
Department,
cume_dist() over (partition by Department order by Salary desc ) co
from employee_salaries;
值解析
因为我们使用desc 所以是降序 也就是大于=值的 分布比率
co 第一行的值是 0.3333 来源自 :
本部门3个人 当前值70000 大于等于这值的之后只有1人 1/3=0.33
co 第2行的值是0.666 来源自 :
本部门2个人 当前值65000 大于等于这值的之后只有1人 2/3=0.0.666
依次类推……
11, 查询薪资排名前5的员工（不考虑部门）：
# 使用RANK()或DENSE_RANK()窗口函数，但不需要分区，直接按Salary降序排序，并通过LIMIT或FETCH FIRST等语句限制结果集大小。
# 查询薪资排名前5的员工（不考虑部门）：
# 使用RANK()或DENSE_RANK()窗口函数，但不需要分区，直接按Salary降序排序，并通过LIMIT或FETCH FIRST等语句限制结果集大小。
# 求top
select * from employee_salaries order by Salary desc limit 5;
用窗口函数
select
EmployeeName,
EmployeeID,
Department,
Salary,
rank() over (order by Salary desc ) ro
from employee_salaries limit 5;
九，总结：
6769
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
