【MySQL】窗口函数 Lead 和 Lag 的运用(MySQL版本8+)以及 时间差函数 TIMESTAMPDIFF 的运用

本文主要是介绍【MySQL】窗口函数 Lead 和 Lag 的运用(MySQL版本8+)以及 时间差函数 TIMESTAMPDIFF 的运用,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

力扣题

1、题目地址

1939. 主动请求确认消息的用户

2、模拟表

表:Signups

Column NameType
user_idint
time_stampdatetime
  • user_id is the primary key for this table.
  • 每行包含有关 ID 为 user_id 的用户的注册时间的信息。

表:Confirmations

Column NameType
user_idint
time_stampdatetime
actionENUM
  • (user_id, time_stamp) is the primary key for this table.
  • user_id is a foreign key with a reference to the Signups table.
  • action is an ENUM of the type (‘confirmed’, ‘timeout’)
  • 此表的每一行都表示 ID 为 user_id 的用户在 time_stamp 请求了确认消息,
  • 并且该确认消息已被确认(‘confirmed’)或已过期(‘timeout’)。

3、要求

编写 SQL 查询以查找在 24 小时窗口内两次请求确认消息的用户的 ID。
两个正好相隔 24 小时的消息被认为是在窗口内。 该操作不会影响答案,只会影响请求时间。

以任意顺序返回结果表。

查询结果格式如下例:

Signups 表:

user_idtime_stamp
32020-03-21 10:16:13
72020-01-04 13:57:59
22020-07-29 23:09:44
62020-12-09 10:39:37

Confirmations 表:

user_idtime_stampaction
32021-01-06 03:30:46timeout
32021-01-06 03:37:45timeout
72021-06-12 11:57:29confirmed
72021-06-13 11:57:30confirmed
22021-01-22 00:00:00confirmed
22021-01-23 00:00:00timeout
62021-10-23 14:14:14confirmed
62021-10-24 14:14:13timeout

输出:

user_id
2
3
6

解析:
用户 2 在彼此恰好 24 小时内请求了两条消息,因此我们将它们包括在内。
用户 3 在 6 分 59 秒内请求了两条消息,因此我们将它们包括在内。
用户 6 在 23 小时 59 分 59 秒内请求了两条消息,因此我们将它们包括在内。
用户 7 在 24 小时 1 秒内请求了两条消息,因此我们将它们从答案中排除。

4、代码编写

知识点(先看这个方便理解代码)

TIMESTAMPDIFF ⭐️

语法: TIMESTAMPDIFF (interval, datetime_expr1, datetime_expr2)

说明: 返回结果 datetime_expr2 - datetime_expr1

参数: interval 可以为 SECOND - 秒、MINUTE - 分、HOUR - 时、DAY - 日、MONTH - 月、YEAR - 年

mysql> SELECT TIMESTAMPDIFF(DAY,'2024-01-03','2024-01-01'); 
-> -2
mysql> SELECT TIMESTAMPDIFF(DAY,'2024-01-01','2024-01-03'); 
-> 2

参考:MySQL – 计算时间差

Lead 和 Lag⭐️

语法:
Lead (列名, 偏移量, 超出记录窗口时的默认值)
Lag (列名, 偏移量, 超出记录窗口时的默认值)
注:如果使用 Lead (列名) 或 Lag (列名) 语法,则偏移量默认1,默认值是 null(无显示)

说明:
Lead:形象的理解就是把数据从下向上推,下端出现空格
Lag:形象的理解就是把数据从上向下推,上端出现空格

原始数据 (items):

item_iditem_brand
1Samsung
2Lenovo
3LG
4HP

Lead代码效果

SELECT *, Lead(item_brand, 1, 0) OVER (ORDER BY item_id) AS lead_data
FROM items
item_iditem_brandlead_data
1SamsungLenovo
2LenovoLG
3LGHP
4HP0

Lag代码效果

SELECT *, Lag(item_brand, 1, 0) OVER (ORDER BY item_id) AS lag_data
FROM items
item_iditem_brandlag_data
1Samsung0
2LenovoSamsung
3LGLenovo
4HPLG

参考:sql中的窗口函数:lead,lag

1、TIMESTAMPDIFF 配合 Lead 写法

SELECT DISTINCT user_id
FROM (SELECT user_id, TIMESTAMPDIFF(SECOND, time_stamp, LEAD(time_stamp, 1, 0) OVER (PARTITION BY user_id ORDER BY time_stamp)) AS diffFROM Confirmations
) AS one
WHERE diff IS NOT NULL
AND diff <= 24*60*60

代码分析

SELECT user_id, TIMESTAMPDIFF(SECOND, time_stamp, LEAD(time_stamp, 1, 0) OVER (PARTITION BY user_id ORDER BY time_stamp)
) AS diff
FROM Confirmations

Lead的效果是下向上推,例如

| 2       | 2021-01-22 00:00:00 |
| 2       | 2021-01-23 00:00:00 |

变成

| 2       | 2021-01-23 00:00:00 |
| 2       | null |

利用向上推的数据减去原先的数据,
上面SQL语句 TIMESTAMPDIFF 的第二个参数是 2021-01-22 00:00:00,第三个参数是 2021-01-23 00:00:00
下面出现空格,默认值null(无显示)没错,

| user_id | diff  |
| ------- | ----- |
| 2       | 86400 |
| 2       | null  |
| 3       | 419   |
| 3       | null  |
| 6       | 86399 |
| 6       | null  |
| 7       | 86401 |
| 7       | null  |

要小于等于86400(一天)里面只有三个符合要求

| user_id | diff  |
| ------- | ----- |
| 2       | 86400 |
| 3       | 419   |
| 6       | 86399 |

2、TIMESTAMPDIFF 配合 Lag 写法

SELECT DISTINCT user_id
FROM (SELECT user_id, TIMESTAMPDIFF(SECOND, time_stamp, LAG(time_stamp, 1, 0) OVER (PARTITION BY user_id ORDER BY time_stamp)) AS diffFROM Confirmations
) AS one
WHERE diff IS NOT NULL
AND ABS(diff) <= 24*60*60

Lag的效果是上向下推,例如

| 2       | 2021-01-22 00:00:00 |
| 2       | 2021-01-23 00:00:00 |

变成

| 2       | null |
| 2       | 2021-01-22 00:00:00 |

利用向上推的数据减去原先的数据,
上面SQL语句 TIMESTAMPDIFF 的第二个参数是 2021-01-23 00:00:00,第三个参数是 2021-01-22 00:00:00
上面出现空格,默认值null(无显示)没错,

| user_id | diff   |
| ------- | ------ |
| 2       | null   |
| 2       | -86400 |
| 3       | null   |
| 3       | -419   |
| 6       | null   |
| 6       | -86399 |
| 7       | null   |
| 7       | -86401 |

要小于等于86400(一天)里面只有三个符合要求(需要取绝对值)

| user_id | diff  |
| ------- | ----- |
| 2       | -86400 |
| 3       | -419   |
| 6       | -86399 |

你要简单写就把 TIMESTAMPDIFF 参数二和参数三换个位置就不用取绝对值了,主要还是演示多种情况,方便更好理解

SELECT DISTINCT user_id
FROM (SELECT user_id, TIMESTAMPDIFF(SECOND, LAG(time_stamp, 1, 0) OVER (PARTITION BY user_id ORDER BY time_stamp), time_stamp) AS diffFROM Confirmations
) AS one
WHERE diff IS NOT NULL
AND diff <= 24*60*60

注:ABS() 函数的效果和数学中的绝对值效果一样

3、单 TIMESTAMPDIFF 写法

有限制正范围的情况就只保证两个时间不相同即可(a.time_stamp != b.time_stamp 是为了不和自己连接,因为没有唯一键做区分)

SELECT DISTINCT a.user_id
FROM Confirmations a, Confirmations b
WHERE a.user_id = b.user_id
AND TIMESTAMPDIFF(SECOND, a.time_stamp, b.time_stamp) BETWEEN 0 AND 24*60*60
AND a.time_stamp != b.time_stamp

包含负范围的情况就还得控制算出的时间差是正的(b.time_stamp - a.time_stamp > 0)

SELECT DISTINCT a.user_id
FROM Confirmations a, Confirmations b
WHERE a.user_id = b.user_id
AND TIMESTAMPDIFF(SECOND, a.time_stamp, b.time_stamp) <= 86400
AND a.time_stamp < b.time_stamp

这篇关于【MySQL】窗口函数 Lead 和 Lag 的运用(MySQL版本8+)以及 时间差函数 TIMESTAMPDIFF 的运用的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/569098

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Python的time模块一些常用功能(各种与时间相关的函数)

《Python的time模块一些常用功能(各种与时间相关的函数)》Python的time模块提供了各种与时间相关的函数,包括获取当前时间、处理时间间隔、执行时间测量等,:本文主要介绍Python的... 目录1. 获取当前时间2. 时间格式化3. 延时执行4. 时间戳运算5. 计算代码执行时间6. 转换为指

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

Python正则表达式语法及re模块中的常用函数详解

《Python正则表达式语法及re模块中的常用函数详解》这篇文章主要给大家介绍了关于Python正则表达式语法及re模块中常用函数的相关资料,正则表达式是一种强大的字符串处理工具,可以用于匹配、切分、... 目录概念、作用和步骤语法re模块中的常用函数总结 概念、作用和步骤概念: 本身也是一个字符串,其中

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用