MySQL计算相邻两行某列差值的方法
这篇文章给大家分享的是有关MySQL计算相邻两行某列差值的方法的内容。小编觉得挺实用的,因此分享给大家做个参考。一起跟随小编过来看看吧。
MySQL计算相邻两行某列差值的方法:首先通过【r1.rownum = r2.rownum - 1】来判断两条记录是否是前后行;然后再使用TIMEDIFF函数来计算时间差即可。
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
函数来计算时间差,到此,我们的目标就实现了。
感谢各位的阅读!关于MySQL计算相邻两行某列差值的方法就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到吧!