美文网首页
查询相同device_id相邻记录某字段差值

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

作者: reco171 | 来源:发表于2019-01-03 10:42 被阅读0次

    需求:使用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 相邻两行记录某列的差值

    相关文章

      网友评论

          本文标题:查询相同device_id相邻记录某字段差值

          本文链接:https://www.haomeiwen.com/subject/lctfrqtx.html