需求:使用sql语句,查询相同device_id相邻记录的date_time差值。具体需求为,按照device_id不同分组,然后分别查询出相同device_id相邻记录date_time的差值,且筛选出差值大于某个值。
查询SQL语句如下:
SELECT *,TIMESTAMPDIFF(MINUTE, r1.date_time, r2.date_time) AS diff FROM
(SELECT (@rownum := @rownum + 1) AS rownum,state.device_id,state.date_time
FROM device_state state , (SELECT @rownum := 0) r
ORDER BY state.device_id, state.date_time) r1
LEFT JOIN
(SELECT (@index := @index + 1) AS indexnum,state.device_id,state.date_time
FROM device_state state , (SELECT @index := 0) r
ORDER BY state.device_id, state.date_time) r2
ON r1.device_id = r2.device_id
AND r1.rownum = r2.indexnum - 1
WHERE TIMESTAMPDIFF(MINUTE, r1.date_time, r2.date_time)>30
其中sql函数的含义如下:
TIMESTAMPDIFF:时间差函数,参数可为DAY、HOUR、MINUTE,示例:
SELECT TIMESTAMPDIFF(MINUTE, '2015-04-20 00:00:00', '2015-04-22 23:00:00');
(SELECT @rownum := 0):初始化行记录号为0
SELECT (@rownum := @rownum + 1):行记录号递增
查询结果如下图所示:
参考:
Mysql 相邻两行记录某列的差值
网友评论