【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

相关文章

Kotlin 作用域函数apply、let、run、with、also使用指南

《Kotlin作用域函数apply、let、run、with、also使用指南》在Kotlin开发中,作用域函数(ScopeFunctions)是一组能让代码更简洁、更函数式的高阶函数,本文将... 目录一、引言:为什么需要作用域函数?二、作用域函China编程数详解1. apply:对象配置的 “流式构建器”最

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

MyBatis 动态 SQL 优化之标签的实战与技巧(常见用法)

《MyBatis动态SQL优化之标签的实战与技巧(常见用法)》本文通过详细的示例和实际应用场景,介绍了如何有效利用这些标签来优化MyBatis配置,提升开发效率,确保SQL的高效执行和安全性,感... 目录动态SQL详解一、动态SQL的核心概念1.1 什么是动态SQL?1.2 动态SQL的优点1.3 动态S

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Linux卸载自带jdk并安装新jdk版本的图文教程

《Linux卸载自带jdk并安装新jdk版本的图文教程》在Linux系统中,有时需要卸载预装的OpenJDK并安装特定版本的JDK,例如JDK1.8,所以本文给大家详细介绍了Linux卸载自带jdk并... 目录Ⅰ、卸载自带jdkⅡ、安装新版jdkⅠ、卸载自带jdk1、输入命令查看旧jdkrpm -qa

Tomcat版本与Java版本的关系及说明

《Tomcat版本与Java版本的关系及说明》:本文主要介绍Tomcat版本与Java版本的关系及说明,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Tomcat版本与Java版本的关系Tomcat历史版本对应的Java版本Tomcat支持哪些版本的pythonJ

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T