李林超博客
首页
归档
留言
友链
动态
关于
归档
留言
友链
动态
关于
首页
Java
正文
MySQL高级应用窗口函数(四)
Leefs
2021-11-21 PM
890℃
0条
[TOC] ### 前言 本篇将通过示例讲解:**偏移分析函数+over()** ### 一、偏移分析函数概念 + **lag(col,n,default):**用于统计分组内往上第n行值。 + 第一个参数为列名 + 第二个参数为往上第n行(可选,不填默认为1) + 第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL) + **lead(col,n,default):**与lag相反,统计分组内往下第n行值。 + 第一个参数为列名 + 第二个参数为往下第n行(可选,不填默认为1) + 第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL) + **first_value(col):**用于取分组内排序后,截止到当前行,第一个col的值。 + **last_value(col):**用于取分组内排序后,截止到当前行,最后一个col的值。 ### 二、示例讲解 #### 2.1 案例 > 查询出用户【yantian】和【lisi】的时间偏移(前N行) 分析:通过lag()窗口函数进行实现 ```mysql SELECT user_name, pay_time, lag( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag1, lag( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag2, lag( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lag3, lag( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lag4 FROM user_order WHERE user_name IN ( 'lisi', 'yantian' ) ``` **运行结果** ![05.MySQL高级应用窗口函数(四)01.jpg](https://lilinchao.com/usr/uploads/2021/11/2047273681.jpg) #### 2.2 案例 > 查询出用户【yantian】和【lisi】的时间偏移(后N行) 分析:通过lead()窗口函数进行实现 ```mysql SELECT user_name, pay_time, lead( pay_time, 1, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead1, lead( pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead2, lead( pay_time, 2, pay_time ) over ( PARTITION BY user_name ORDER BY pay_time ) lead3, lead( pay_time, 2 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead4 FROM user_order WHERE user_name IN ( 'lisi', 'yantian' ); ``` **运行结果** ![05.MySQL高级应用窗口函数(四)02.jpg](https://lilinchao.com/usr/uploads/2021/11/3782176268.jpg) #### 2.3 案例 > 查询出支付时间间隔超过10天的用户数 分析: + 同一用户,相邻的订单进行下单时间比较,如果相邻订单下单时间间隔超过10天,那么这个用户就是需要统计的 + 在这个过程中,需要进行相邻订单支付时间相减 **实现步骤** ``` (1)通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中 (2)通过上一步将支付时间做差,然后对用户去重,再进行count操作 ``` **实现** 步骤一:通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中 ```mysql SELECT user_name, pay_time, lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time FROM user_order; ``` 步骤二:通过上一步将支付时间做差,然后对用户去重,再进行count操作 ```mysql SELECT count( DISTINCT user_name ) FROM ( SELECT user_name, pay_time, lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time FROM user_order ) a WHERE datediff( a.lead_time, a.pay_time )> 10; ``` ![05.MySQL高级应用窗口函数(四)03.jpg](https://lilinchao.com/usr/uploads/2021/11/1540052768.jpg) #### 2.4 案例 > 查询出每年支付时间间隔最长的用户 **实现步骤** ``` (1)根据用户和支付年份进行分组,通过lag()窗口函数将上一行的支付时间合并到下一行 (2)计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名 (3)将上方查询结果作为表进行子查询,取得排名结果为1的值 ``` **实现** 步骤一:根据用户和支付年份进行分组,通过lag()窗口函数将上一行的支付时间合并到下一行 ```mysql SELECT YEAR ( pay_time ) year_time, user_name, pay_time, lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time FROM user_order; ``` 步骤二:计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名 ```mysql SELECT a.year_time, a.user_name, datediff( a.pay_time, a.lag_time ) interval_days, rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1 FROM ( SELECT YEAR ( pay_time ) year_time, user_name, pay_time, lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time FROM user_order ) a; ``` 步骤三:将上方查询结果作为表进行子查询,取得排名结果为1的值 ```mysql SELECT b.year_time, b.user_name, b.interval_days FROM ( SELECT a.year_time, a.user_name, datediff( a.pay_time, a.lag_time ) interval_days, rank() over ( PARTITION BY a.year_time ORDER BY datediff( a.pay_time, a.lag_time ) DESC ) rank1 FROM ( SELECT YEAR ( pay_time ) year_time, user_name, pay_time, lag( pay_time ) over ( PARTITION BY user_name, YEAR ( pay_time ) ORDER BY pay_time ASC ) lag_time FROM user_order ) a ) b where b.rank1 = 1; ``` **运行结果** ![05.MySQL高级应用窗口函数(四)04.jpg](https://lilinchao.com/usr/uploads/2021/11/1041988959.jpg)
标签:
MySQL
非特殊说明,本博所有文章均为博主原创。
如若转载,请注明出处:
https://www.lilinchao.com/archives/1651.html
上一篇
MySQL高级应用窗口函数(三)
下一篇
01.Hive基本概念
取消回复
评论啦~
提交评论
栏目分类
随笔
2
Java
326
大数据
229
工具
31
其它
25
GO
43
标签云
Spark RDD
Thymeleaf
锁
字符串
Beego
Java编程思想
散列
Java工具类
高并发
JVM
Redis
递归
微服务
GET和POST
JavaScript
算法
Map
Eclipse
并发线程
持有对象
MyBatis-Plus
JavaWEB项目搭建
Golang
队列
Quartz
机器学习
SpringCloud
Java
Shiro
人工智能
友情链接
申请
范明明
庄严博客
Mx
陶小桃Blog
虫洞