欢迎投稿

今日深度:

深入解析MySQL的窗口函数,

深入解析MySQL的窗口函数,


目录
  • 一、定义
  • 二、语法格式
  • 三、分类
    • 1、聚合类
    • 2、排序类
    • 3、偏移分析函数

对一个成熟的数据分析师来说,窗口函数可以大幅提高查询效率,且SQL代码优雅。

img

一、定义

窗口可以理解为记录集合,窗口函数就是在满足某种条件的记录集合上执行的特殊函数 即:应用在窗口内的函数。

静态窗口:每条记录都要在此窗口内执行函数,窗口大小都是固定的。

动态窗口:不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。

二、语法格式

函数名(字段名) over(子句) 

over()括号内若不写,则意味着窗口函数基于满足where条件的所有行进行计算。

若括号内不为空,则支持以下语法来设置窗口。

函数名(字段名) over(partition by <要分列的组> order by <要排序的列> rows between <数据范围>) 

数据范围:

rows between 2 preceding and current row # 取本行和前面两行
rows between unbounded preceding and current row # 取本行和之前所有的行 
rows between current row and unbounded following # 取本行和之后所有的行 
rows between 3 preceding and 1 following # 从前面三行和下面一行,总共五行 
# 当order by后面没有rows between时,窗口规范默认是取本行和之前所有的行
# 当order by和rows between都没有时,窗口规范默认是分组下所有行(rows between unbounded preceding and unbounded following) 

三、分类

1、聚合类

聚合窗口函数与普通聚合函数的区别

  • 普通场景下的聚合函数是将多条记录聚合为一条**(多到一);**窗口函数是每条记录都会执行,有几条记录执行完还是几条**(多到多)**。
  • 接下来通过解决具体需求来让大家更加了解窗口函数的用法,希望大家阅读完能动手练习。 先创建user_trade表:
-- 现有2018~2020某电商平台订单信息表user_trade
create table user_trade (
 user_name  varchar(20) COMMENT '用户名',
 piece  int COMMENT '购买数量',
 price  double COMMENT '价格',
 pay_amount double COMMENT '支付金额',
 goods_category varchar(20) COMMENT '商品品类',
 pay_time date COMMENT '支付日期'
);

从navicat中导入以下数据源:

user_trade数据源:https://gitee.com/hu-weiqing/datasource/blob/master/user_trade.xlsx

数据随机展示10条如下:

  • 累计求和:sum()over()
-- 需求1: 查询出2019年每月的支付总额和当年累积支付总额 
select a.mon,a.pay_amount,sum(a.pay_amount) over(order by a.mon) as sum_amount
from(
select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) = '2019'
group by month(a.pay_time)
) a ;
-- 需求2:查询出2018-2019年每月的支付总额和当年累积支付总额
select a.*,sum(a.pay_amount) over(partition by a.year order by a.mon) as sum_amount
from(
select year(a.pay_time) as year,month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) in('2018','2019')
group by year(a.pay_time),month(a.pay_time)
) a ;

img

需求1运行结果(部分)

img

需求2运行结果(部分)

  • 移动平均:avg() over()
-- 需求3: 查询出2019年每个月的近三月移动平均支付金额
select a.mon,a.pay_amount,
avg(a.pay_amount) over(order by a.mon rows between 2 preceding and current row) as avg_amount
from(
select month(a.pay_time) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
where year(a.pay_time) = '2019'
group by month(a.pay_time)
) a ;

img

需求3运行结果(部分)

  • 最大/最小值:max()/min() over()
-- 需求4: 查询出每四个月的最大月总支付金额
select 
a.mon,
a.pay_amount,
max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amount
from(
select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
group by SUBSTRING(a.pay_time,1,7)
)a ;

img

需求4运行结果(部分)

2、排序类

  • row_number()、rank() 和dense_rank()
-- 需求4: 查询出每四个月的最大月总支付金额
select 
a.mon,
a.pay_amount,
max(a.pay_amount) over(order by a.mon rows between 3 preceding and current row) as max_amount
from(
select SUBSTRING(a.pay_time,1,7) as mon,sum(a.pay_amount) as pay_amount
from user_trade a
group by SUBSTRING(a.pay_time,1,7)
)a ;

img

需求5运行结果(部分)

row_number()、rank() 和dense_rank() 三种排序函数的区别:

row_number:每一行记录生成一个序号,依次排序且不会重复。 12345…

rank:跳跃排序,生成的序号有可能不连续。11345…

dense_rank:在生成序号时是连续的。11234…

  • ntile(n)over()

ntile(n)用于将分组数据按照顺序切分成n片,返回当前切片值. n表示切片的数量; 不支持rows between

-- 需求6: 查询出将2020年2月的支付用户,按照支付金额分成5组后的结果
select 
a.user_name,
sum(a.pay_amount) as pay_amount,
ntile(5) over(order by sum(a.pay_amount) desc) as level
from user_trade a
where SUBSTRING(a.pay_time,1,7) = '2020-02'
group by a.user_name;
-- 需求7: 查询出2020年支付金额排名前30%的所有用户
select a.user_name,a.pay_amount
from (
select 
a.user_name,
sum(a.pay_amount) as pay_amount,
ntile(10) over(order by sum(a.pay_amount) desc) as level
from user_trade a
where year(a.pay_time) = '2020'
group by a.user_name
) a 
where a.level in(1,2,3);

img

需求6运行结果(部分)

img

需求7运行结果(部分)

3、偏移分析函数

  • lag() over()向上偏移

lag(exp_str,offset,defval) exp_str:字段名 offset:偏移量 defval:默认值。当向上偏移了offset行已经超出了表的范围时,lag()函数将defval这个参数值作为函数的返回值,若没有指定默认值,则返回NULL。

-- 需求8: 查询出King和West的时间偏移(前N行)
select a.user_name,a.pay_time,
lag(a.pay_time,1,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag1,
-- 没有传入偏移量,那么默认就是1,找不到的话,此处也没有给默认值,为null
lag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lag2,
lag(a.pay_time,2,a.pay_time) over(partition by a.user_name order by a.pay_time) as lag3,
lag(a.pay_time,2) over(partition by a.user_name order by a.pay_time) as lag4
from user_trade a 
where a.user_name in('King','West');

img

需求8运行结果

  • lead() over()向下偏移

用法同lag()over()函数。

补充练习:

-- 需求9: 查询出支付时间间隔超过100天的用户数
select count(distinct a.user_name)
from (
select a.user_name,a.pay_time,
lag(a.pay_time) over(partition by a.user_name order by a.pay_time) as lg
from user_trade a 
) a 
where DATEDIFF(a.pay_time,a.lg) >100;
# 需求9运行结果为180
-- 需求10: 查询出每年支付时间间隔最长的用户
select c.years,c.user_name,c.pay_days 
from(
select b.years,b.user_name,datediff(b.pay_time,b.lg) as pay_days,
rank() over(partition by b.years order by datediff(b.pay_time,b.lg) desc) as rk 
from (
select year(a.pay_time) as years,a.user_name,a.pay_time,
lag(a.pay_time) over(partition by a.user_name,year(a.pay_time) order by a.pay_time) as lg
from user_trade a 
) b 
where b.lg is not null
) c 
where c.rk = 1;

img

需求10运行结果

窗口函数在数据分析师的工作中应用非常广,如果不会窗口函数,很可能同样的需求用普通表关联写需要关联很多张表,导致性能不好,查询速度非常慢。

到此这篇关于深入解析MySQL的窗口函数的文章就介绍到这了,更多相关MySQL窗口函数内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友!

您可能感兴趣的文章:
  • mysql 窗口函数 ROW_NUMBER、NTILE详解
  • MySQL8.0实现窗口函数计算同比环比
  • MySQL窗口函数实现榜单排名
  • 使用Pandas实现MySQL窗口函数的解决方法
  • MySQL窗口函数OVER使用示例详细讲解

www.htsjk.Com true http://www.htsjk.com/Mysql/46821.html NewsArticle 深入解析MySQL的窗口函数, 目录 一、定义 二、语法格式 三、分类 1、聚合类 2、排序类 3、偏移分析函数 对一个成熟的数据分析师来说,窗口函数可以大幅提高查询效率,且SQL代码优雅...
评论暂时关闭