关注MySQL高级应用窗口函数之偏移分析函数

二、示例讲解

2.1 案例

查询出用户【yantian】和【lisi】的时间偏移(前N行)

分析：通过lag()窗口函数进行实现

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

复制代码

运行结果

2.2 案例

查询出用户【yantian】和【lisi】的时间偏移(后N行)

分析：通过lead()窗口函数进行实现

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

复制代码

运行结果

2.3 案例

查询出支付时间间隔超过10天的用户数

分析：

• 同一用户，相邻的订单进行下单时间比较，如果相邻订单下单时间间隔超过10天，那么这个用户就是需要统计的

• 在这个过程中，需要进行相邻订单支付时间相减

实现步骤

(1)通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中

(2)通过上一步将支付时间做差,然后对用户去重，再进行count操作

复制代码

实现

步骤一：通过lead窗口函数根据支付时间进行分组,将下一行支付时间移到上一行当中

SELECT

user_name,

pay_time,

lead( pay_time, 1 ) over ( PARTITION BY user_name ORDER BY pay_time ) lead_time

FROM

user_order;

复制代码

步骤二：通过上一步将支付时间做差,然后对用户去重，再进行count操作

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;

复制代码

2.4 案例

查询出每年支付时间间隔最长的用户

实现步骤

(1)根据用户和支付年份进行分组，通过lag()窗口函数将上一行的支付时间合并到下一行

(2)计算订单时间间隔,在年度内按照各自的订单时间间隔进行排名

(3)将上方查询结果作为表进行子查询,取得排名结果为1的值

复制代码

实现

步骤一：根据用户和支付年份进行分组，通过lag()窗口函数将上一行的支付时间合并到下一行

SELECT YEAR

( pay_time ) year_time,

user_name,

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

如果觉得本文对你有帮助的话，不妨给我点个赞，关注一下吧！

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门即可获取！
“img” style=“zoom: 33%;” />

最后

如果觉得本文对你有帮助的话，不妨给我点个赞，关注一下吧！

[外链图片转存中…(img-9ByKxXnT-1711890556218)]

[外链图片转存中…(img-cHzWjWVD-1711890556219)]

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门即可获取！