MySQL如何计算相邻两行某列差值
MySQL计算相邻两行某列差值的方法:首先通过【r1.rownum = r2.rownum - 1】来判断两条记录是否是前后行;然后再使用TIMEDIFF函数来计算时间差即可。
创新互联公司长期为成百上千家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为平湖企业提供专业的成都网站建设、做网站,平湖网站改版等技术服务。拥有10余年丰富建站经验和众多成功案例,为您定制开发。
【相关学习推荐: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培训栏目!
分享标题:MySQL如何计算相邻两行某列差值
本文路径:http://myzitong.com/article/cjoosi.html