查询相同device_id相邻记录某字段差值

需求：使用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)：行记录号递增

查询结果如下图所示：

date_time_diff.PNG
参考：
Mysql 相邻两行记录某列的差值