MySQL查询相邻两条记录的时间间隔

MySQL查询相邻两条记录的时间间隔。最近需要统计相邻两条记录的时间间隔，筛选出时间间隔大于2min的数据记录。因为是同一张表，又需要查询出相邻的数据，所以最开始想到使用子表来做，分别用t1、t2表示，但是实践后发现查询时间太久了，因为这张表是一张数据量很大的表，用子查询去查就更慢了，所以需要换个思路去做，想到了LEAD开窗函数

ps：为什么要用开窗函数，因为这些函数是官方提供的，所以一般都是有做过优化的，性能一般是可以接受的

实验环境

• MySQL8.0.26
• Navicat15.0.28

ps：LEAD函数需要MySQL8.0+版本才支持

实验步骤

这里需要一些MySQL函数支持

• IMESTAMPDIFF函数：IMESTAMPDIFF函数用来计算两个DATE或DATETIME值之间的差值，语法TIMESTAMPDIFF(unit,begin,end);，其中unit参数可以是MICROSECOND、SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR
• LEAD函数：LEAD函数是一个窗口函数，LEAD函数需要MySQL8.0+版本才支持，语法是LEAD(expr [, N[, default]] over(partition by order by )，LEAD函数会返回分区内当前行后边N行字段的数值（如果N不设置，就默认为1），如果没有这样的行，会返回你设置的default （如果default省略了，则默认为 null)，与LEAD函数相反的函数是LAG函数，LAG函数是返回前面N行字段的数值

所以，查询打卡流水，前一条记录与后面一条记录的打卡间隔就可以这样查询：

SELECT
	device_id AS '设备号',
  create_time AS '上报时间',
	TIMESTAMPDIFF(
		MINUTE,
		create_time,
		LEAD (create_time) OVER (

			ORDER BY
				device_id,
				create_time
		)
	) AS '时间间隔'
FROM
	t_user_atten_record
WHERE
com_id = 111168
AND create_time >= '2024-05-13'
AND create_time <= '2024-05-20'
ORDER BY
	device_id,
	create_time;

查询效果

通过SQL，就可以查询出这个设备的打卡时间间隔，可以用来分析定位问题