MySQL如何计算相邻两行某列差值

  • 作者:zccc
  • 来源:网络
  • 2020-09-03 15:03:54

MySQL计算相邻两行某列差值的方法:首先通过【r1.rownum = r2.rownum - 1】来判断两条记录是否是前后行;然后再使用TIMEDIFF函数来计算时间差即可。【相关学习推荐:mysql教程(视...

MySQL计算相邻两行某列差值的方法:首先通过【r1.rownum = r2.rownum - 1】来判断两条记录是否是前后行;然后再使用TIMEDIFF函数来计算时间差即可。

【相关学习推荐:mysql教程(视频)】

MySQL计算相邻两行某列差值的方法:

首先博主在服务端有一个表来记录司机上报上来的GPS点位信息,表结构如下:

-- 司机GPS收集表
CREATE TABLE captainad_driver_gps_position (
    id BIGINT NOT NULL auto_increment COMMENT '主键',
    business_id BIGINT DEFAULT NULL COMMENT '业务ID',
    device_mac VARCHAR (64) DEFAULT NULL COMMENT '设备MAC地址',
    device_imei VARCHAR (64) DEFAULT NULL COMMENT '设备IMEI',
    lat_lng VARCHAR (64) DEFAULT NULL COMMENT '纬经度',
    capture_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '捕获时间',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (id),
    KEY `idx_business_id` (`business_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '司机GPS收集';

表中记录的数据大致如下:

现在就对按获取GPS位置的时间capture_time在按照时间排序之后,进行前后两条记录计算差值。为了计算两者的差值,那么我们肯定是需要获取到一前一后两条记录的,这里我们可以巧用一个变量来记录当前行的行数,然后随着循环查询每次将行数叠加,以达到行记录的目的,这样一来,我们就能知道哪两条记录是一前一后的了。

打印行号的SQL语句:

SELECT
    (@rownum := @rownum + 1) AS rownum,
    tab.business_id,
    tab.device_mac,
    tab.capture_time
FROM
    captainad_driver_gps_position tab,
    (SELECT @rownum := 0) r  -- 声明变量
WHERE
    1 = 1
AND DATE_FORMAT(
    tab.capture_time,
    '%Y-%m-%d'
) = '2019-06-28'
ORDER BY
    tab.capture_time

基于此,我们将目标SQL给写出来,这里我根据我们的实际业务将语句稍微做了整理,脚本大致如下:

SELECT
    t.business_id,
    t.device_mac,
    t.capture_time,
    t.tdiff
FROM
    (
        SELECT
            r1.business_id,
            r1.device_mac,
            r1.capture_time,
            TIMEDIFF(
                r2.capture_time,
                r1.capture_time
            ) AS 'tdiff'
        FROM
            (
                SELECT
                    (@rownum := @rownum + 1) AS rownum,
                    tab.business_id,
                    tab.device_mac,
                    tab.capture_time
                FROM
                    captainad_driver_gps_position tab,
                    (SELECT @rownum := 0) r
                WHERE
                    1 = 1
                AND DATE_FORMAT(
                    tab.capture_time,
                    '%Y-%m-%d'
                ) = '2019-06-28'
                ORDER BY
                    tab.capture_time
            ) r1
        LEFT JOIN (
            SELECT
                (@INDEX := @INDEX + 1) AS rownum,
                tab.business_id,
                tab.device_mac,
                tab.capture_time
            FROM
                captainad_driver_gps_position tab,
                (SELECT @INDEX := 0) r
            WHERE
                1 = 1
            AND DATE_FORMAT(
                tab.capture_time,
                '%Y-%m-%d'
            ) = '2019-06-28'
            ORDER BY
                tab.capture_time
        ) r2 ON r1.business_id = r2.business_id
        AND r1.device_mac = r2.device_mac
        AND r1.rownum = r2.rownum - 1
    ) t
WHERE
    t.tdiff > '00:00:15'

在上面的代码中,我们通过r1.rownum = r2.rownum - 1来判断两条记录是否是前后行,然后再使用TIMEDIFF函数来计算时间差,到此,我们的目标就实现了。

想了解更多编程学习,敬请关注php培训栏目!

  • 相关专题

免责声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,也不承认相关法律责任。如果您发现本社区中有涉嫌抄袭的内容,请发送邮件至:operations@xinnet.com进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。

相关文章

免费咨询获取折扣