/**
语法结构：
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：
分区（PARTITION BY）
PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
排序（ORDER BY）
OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
以及窗口大小（frame_clause）。
frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。
*/
# --------------------------------1.序号函数----------------------------------------
/**
序号函数
序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。
打序号方式不一样
row_number()|rank()|dense_rank() over (
partition by ...
order by ...
)
*/
use mydb;
create table employee
(
dname varchar(20) comment '部门名',
eid varchar(20) comment '员工id',
ename varchar(20) comment '员工姓名',
hiredate date comment '入职日期',
salary double comment '薪资'
);
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);
select *
from employee;
-- 对每个部门的员工按照薪资降序排序，并给出排名
select dname,
ename,
salary,
# 窗口函数执行完，会多出一列
# row_number：同薪资按序号排名，序号连续
row_number() over (partition by dname order by salary desc) as rowNumber,
# rank：同薪资序号相同，下一薪资序号隔断，序号不连续
rank() over (partition by dname order by salary desc) as ranks,
# dense_rank：同薪资序号相同，序号连续
dense_rank() over (partition by dname order by salary desc) as denseRank
from employee;
# 求出每个部门薪资排在前三名的员工（分组求TopN问题）
select *
from (
select dname,
ename,
salary,
dense_rank() over (partition by dname order by salary desc ) as denseRank
from employee
) as dr
where dr.denseRank <= 3;
-- 对所有员工进行全局排序（不分组）
-- 不加partition by表示全局排序
select dname,
ename,
salary,
dense_rank() over (order by salary desc ) as dr
from employee;
# --------------------------------2.开窗聚合函数----------------------------------------
/**
开窗聚合函数-sum，avg，min，max，count
在窗口中每条记录动态地应用聚合函数（sum()、avg()、max()、min()、count()），
可以动态计算在指定的窗口内的各种聚合函数值。
*/
select dname,
ename,
hiredate,
salary,
# 从第一个值开始累加到当前行（包含当前行）
sum(salary) over (partition by dname order by hiredate) as sum
from employee;
select dname,
ename,
hiredate,
salary,
# 没有order by，默认把分组内所有的数据进行sum操作
sum(salary) over (partition by dname) as sum
from employee;
# 指定范围
select dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate
# 从第一行到当前行
# unbounded preceding：第一行
# current row：当前行
# 不写默认此操作
rows between unbounded preceding and current row) as sum
from employee;
select dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate
# 从当前行开始，累加向上3行到当前行
rows between 3 preceding and current row) as sum
from employee;
select dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate
# 从当前行开始，累加向上3行到向下1行
rows between 3 preceding and 1 following) as sum
from employee;
select dname,
ename,
hiredate,
salary,
sum(salary) over (partition by dname order by hiredate
# 从当前行开始，累加到最后1行
rows between current row and unbounded following) as sum
from employee;
# --------------------------------2.分布函数----------------------------------------
# 分布函数-cume_dist和percent_rank
# cume_dist用途：分组内小于、等于当前rank值的行数 / 分组内总行数
# 查询小于等于当前薪资（salary）的比例
select dname,
ename,
hiredate,
salary,
cume_dist() over (order by salary) rn1,
cume_dist() over (partition by dname order by salary) rn2
from employee;
/*
rn1: 没有partition,所有数据均为1组，总行数为12，
第一行：小于等于3000的行数为3，因此，3/12=0.25
第二行：小于等于4000的行数为5，因此，5/12=0.4166666666666667
rn2: 按照部门分组，dname='研发部'的行数为6,
第一行：研发部小于等于3000的行数为1，因此，1/6=0.16666666666666666
*/
-- percent_rank
/*
用途：每行按照公式(rank - 1) / (rows - 1)进行计算。
其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数
应用场景：不常用
*/
select dname,
ename,
hiredate,
salary,
rank() over (partition by dname order by salary desc) rn,
percent_rank() over (partition by dname order by salary desc) pr
from employee;
/*
rn2:
第一行: (1 - 1) / (6 - 1) = 0
第二行: (1 - 1) / (6 - 1) = 0
第三行: (3 - 1) / (6 - 1) = 0.4
*/
# --------------------------------3.前后函数----------------------------------------
# 前后函数-lag和lead
# 返回位于当前行的前n行（lag(expr, n)）或后n行（lead(expr, n)）的expr的值
# 应用场景：查询前1名同学的成绩和当前同学成绩的差值
-- lag
select dname,
ename,
hiredate,
salary,
lag(hiredate, 1, '2000-01-01') over (partition by dname order by hiredate) as last_1_time,
lag(hiredate, 2) over (partition by dname order by hiredate) as last_2_time
from employee;
/*
last_1_time: 指定了往上第1行的值，default为'2000-01-01'
第一行，往上1行为null,因此取默认值 '2000-01-01'
第二行，往上1行值为第一行值，2021-11-01
第三行，往上1行值为第二行值，2021-11-02
last_2_time: 指定了往上第2行的值，为指定默认值
第一行，往上2行为null
第二行，往上2行为null
第四行，往上2行为第二行值，2021-11-01
第七行，往上2行为第五行值，2021-11-02
*/
-- lead
select dname,
ename,
hiredate,
salary,
lead(hiredate, 1, '2000-01-01') over (partition by dname order by hiredate) as last_1_time,
lead(hiredate, 2) over (partition by dname order by hiredate) as last_2_time
from employee;
# --------------------------------4.头尾函数----------------------------------------
# 头尾函数-first_value和last_value
# 用途：截止到当前，返回第一个（first_value(expr)）或最后一个（last_value(expr)）expr的值
# 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
-- 注意，如果不指定order by，则进行排序混乱，会出现错误的结果
select dname,
ename,
hiredate,
salary,
first_value(salary) over (partition by dname order by hiredate) as first,
last_value(salary) over (partition by dname order by hiredate) as last
from employee;
# --------------------------------5.其他函数----------------------------------------
# nth_value(expr, n)
# 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
# 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select dname,
ename,
hiredate,
salary,
nth_value(salary, 2) over (partition by dname order by hiredate) as second,
nth_value(salary, 3) over (partition by dname order by hiredate) as third
from employee;
# ntile(n)
# 用途：将分区中的有序数据分为n个等级，记录等级数
# 应用场景：将每个部门员工按照入职日期分成3组
-- 根据入职日期将每个部门的员工分成3组
select dname,
ename,
hiredate,
salary,
ntile(3) over (partition by dname order by hiredate ) as nt
from employee;
-- 取出每个部门的第一组员工
select *
from (
select dname,
ename,
hiredate,
salary,
ntile(3) over (partition by dname order by hiredate ) as rn
from employee
) t
where t.rn = 1;
with t as
(
select dname,
ename,
hiredate,
salary,
ntile(3) over (partition by dname order by hiredate ) as rn
from employee
)
select *
from t
where t.rn = 1;
网友评论