MySQL高阶(九)——窗口函数

木鬼与槐

于 2024-08-01 20:30:12 发布

阅读量467

点赞数 5

分类专栏： MYSQL 文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/weixin_58305115/article/details/140856456

版权

MYSQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MySQL高阶(九)——窗口函数

特点

非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。
窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。最重要的是，窗口函数具有多种功能，比如：求差值、求占比、求排名、累计值计算等等。

在这里插入图片描述

语法结构

window_function ( expr ) OVER ( 
  PARTITION BY 分组的列... 
  ORDER BY 排序列... 
  [ rows between 起始行 and 结束行]
)

说明：

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：

分区（PARTITION BY）

PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算

排序（ORDER BY）

OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

以及窗口大小（frame_clause）。

frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

窗口函数分类

在这里插入图片描述

还有聚合函数：SUM,AVG,MIN,MAX,COUNT

准备数据

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);

序号函数

row_number()|rank()|dense_rank() over ( 
  partition by ... 
  order by ... 
)

例1：按每个部门的的员工求薪资排名

-- 对每个部门的员工按照薪资排序，并给出排名
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as `row_number`,
dense_rank() over(partition by dname order by salary desc) as `dense_rank`,
rank() over(partition by dname order by salary desc) as `rank`
from employee;

在这里插入图片描述

例2：求每个部分薪资的前三名的员工

# --求出每个部门薪资排在前三名的员工- 分组求TOPN
select
*
from
(
    select
     dname,
     ename,
     salary,
     row_number() over(partition by dname order by salary desc) as rn,
    from employee
)t
where t.rn <= 3;

在这里插入图片描述

聚合函数

例：求每个部门的薪水，并按照hiredate升序

select
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over(partition by dname order by hiredate) as pv1
-- 根据dname分组，按hiredate升序排列，求每个部门薪水
from employee;

在这里插入图片描述

分布函数

CUME_DIST（累计分布值）

•分组内小于、等于当前rank值的行数 / 分组内总行数

select
 dname,
 ename,
 salary,
 cume_dist() over(order by salary) as rn1, -- 没有partition语句 所有的数据位于一组
 cume_dist() over(partition by dname order by salary) as 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,
 salary,
 rank() over(partition by dname order by salary desc ) as rn,
 percent_rank() over(partition by dname order by salary desc ) as rn2
from employee;

在这里插入图片描述

说明：

rn2:

第一行: (1 - 1) / (6 - 1) = 0

第二行: (1 - 1) / (6 - 1) = 0

第三行: (3 - 1) / (6 - 1) = 0.4

前后函数

LAG函数

返回位于当前行的前n行（LAG(expr,n)）

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函数

返回当前行的后n行（LEAD(expr,n)）的expr的值

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;

在这里插入图片描述

头尾函数

FIRST_VALUE和LAST_VALUE

•返回第一个（FIRST_VALUE(expr)）的值或最后一个（LAST_VALUE(expr)）expr的值

-- 注意,  如果不指定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;

在这里插入图片描述

NTH_VALUE(expr, n）

•返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

-- 查询每个部门截止目前薪资排在第二和第三的员工信息
select
  dname,
  ename,
  hiredate,
  salary,
  nth_value(salary,2) over(partition by dname order by hiredate) as second_score,
  nth_value(salary,3) over(partition by dname order by hiredate) as third_score
from employee;

在这里插入图片描述

NTILE(n)

•将分区中的有序数据分为n个等级，记录等级数

-- 根据入职日期将每个部门的员工分成3组
select
  dname,
  ename,
  hiredate,
  salary,
ntile(3) over(partition by dname order by  hiredate  ) as rn
from employee;

在这里插入图片描述

窗口范围

window_function ( expr ) OVER ( 
  PARTITION BY 分组的列... 
  ORDER BY 排序列... 
  [ rows|range] between 起始行 and 结束行
) 
from 表名;

函数	说明
unbound preceding	向上无边界
n preceding	向上n行
current row	当前行
n following	向下n行
unbound following	向下无边界

窗口范围与聚合函数结合

例1：求第一行到当前行的薪水总和

select
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate  rows between unbounded preceding and current row) as c1
from employee;

在这里插入图片描述

例2：求当前行的前3行到当前行的薪水总和

select
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and current row) as c1
from employee;

在这里插入图片描述

例3：求当前行的前3行和后一行的薪水总和（即5行，包括当前行）

select
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between 3 preceding and 1 following) as c1
from employee;

在这里插入图片描述

例4：求当前行到最后一行的薪水总和

select
 dname,
 ename,
 salary,
 sum(salary) over(partition by dname order by hiredate   rows between current row and unbounded following) as c1
from employee;

在这里插入图片描述

rows和range的区别

ROWS是根据分区数据排序之后，每一行的 row_number 确定每行关联的 window frame 范围。
RANGE是根据分区数据排序之后，每一行的排序列的值确定每行关联的 window frame 范围。

准备数据

CREATE TABLE sales (
    month int,
    sales int
);
insert into sales values
(1,10),
(2,23),
(4,5),
(5,32),
(6,22);

例：计算最近3个月的累计销售

rows

select
    month,
    sales,
    sum(sales) over(
        order by month
        rows between 2 preceding and current row ) as sale
    from sales;

在这里插入图片描述

缺点:因为没有3月份，但是4月份把1月和2月销售一起计算。要求是连续3个月，因此这个需要用range。

range

select
    month,
    sales,
    sum(sales) over(
                    order by month
                    range between 2 preceding and current row
    ) as sum_sales
from sales;

在这里插入图片描述

说明：range是根据排序的那一列的值，所以是对month那一列的值进行向上几行或向下几行。

思考：如果求一个月中的近3天销售量

CREATE TABLE tb_sales (
    sale_date DATE,
    quantity INT
);

-- [0, 1) * 100 ==> [0, 100) + 1 ==> [1, 101)
INSERT INTO tb_sales (sale_date, quantity)
VALUES
('2024-07-01', FLOOR(RAND() * 100) + 1),
('2024-07-02', FLOOR(RAND() * 100) + 1),
('2024-07-03', FLOOR(RAND() * 100) + 1),
('2024-07-04', FLOOR(RAND() * 100) + 1),
('2024-07-05', FLOOR(RAND() * 100) + 1),
('2024-07-08', FLOOR(RAND() * 100) + 1),
('2024-07-09', FLOOR(RAND() * 100) + 1),
('2024-07-10', FLOOR(RAND() * 100) + 1),
('2024-07-11', FLOOR(RAND() * 100) + 1),
('2024-07-12', FLOOR(RAND() * 100) + 1),
('2024-07-13', FLOOR(RAND() * 100) + 1),
('2024-07-14', FLOOR(RAND() * 100) + 1),
('2024-07-15', FLOOR(RAND() * 100) + 1),
('2024-07-18', FLOOR(RAND() * 100) + 1),
('2024-07-19', FLOOR(RAND() * 100) + 1),
('2024-07-20', FLOOR(RAND() * 100) + 1),
('2024-07-21', FLOOR(RAND() * 100) + 1),
('2024-07-22', FLOOR(RAND() * 100) + 1),
('2024-07-23', FLOOR(RAND() * 100) + 1),
('2024-07-24', FLOOR(RAND() * 100) + 1),
('2024-07-25', FLOOR(RAND() * 100) + 1),
('2024-07-28', FLOOR(RAND() * 100) + 1),
('2024-07-29', FLOOR(RAND() * 100) + 1),
('2024-07-30', FLOOR(RAND() * 100) + 1),
('2024-07-31', FLOOR(RAND() * 100) + 1);
;

select
    sale_date, quantity,
    sum(quantity) over(
        order by sale_date
        range between interval 2 day preceding and current row
    ) total
from tb_sales;

注意：MySQL中计算间隔天数，需要加上interval n day。

木鬼与槐

关注

5
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
MySQL高阶(九)——窗口函数

- 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。- 窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。最重要的是，窗口函数具有多种功能，比如：求差值、求占比、求排名、累计值计算等等。
复制链接

扫一扫