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

　　简述

 

　　博主最近因工作任务缠身，都无暇顾及到我的这片自留地了。前段时间稍有空闲，花了较多的精力学习《啊哈算法》，从中学习到很多之前没有太注重的内容，收益颇丰。但是这些算法题目还没有看完，等后面有时间了，还需重新自我温习一下前面所写的内容，并且继续耕耘后面的算法知识。

 

　　今天稍微有点时间，总结一下博主近期工作中所遇到的一些难题，希望借此机遇总结一下类似问题的解决方法，也算是一种积累吧。

 

　　背景

 

　　我们在司机的手机APP里预置了定时上报GPS数据的功能，功能设置为了APP每15秒收集一次GPS定位地址，然后每收集到10次就上报到服务器端持久化。但因为APP非安卓系统集成，而是由我方自主提供并设计了此功能，那么GPS数据的收集就有可能因司机人为操作或者其他网络等的影响，产生定位偏差或者获取定位失败的情况。现在我们服务端就有这么一个需求，要求分析出安装在司机手机中的APP是否定期收集到了GPS位置。

 

　　为了保证GPS定位数据能够较为顺利的上传到服务器端，我们在APP每次请求完服务器端之后，只有当APP接收到来自服务器的正确回应，才能表示这次上传数据是正常且准确的，然后清空掉APP端上一次记录的GPS定位数据；但是如果APP因为各种原因无法将上一次收集的数据上报的服务器端，再尝试一番之后无果，将会在下一次与下一批收集到的数据一同上报，通过此机制来保证数据传递的稳定性。

 

　　分析

 

　　如果司机手机中的APP是定时15秒就收集到GPS地址的话，那么持久化到数据库中按获取时间顺序排列的前后两条记录的时间差应该就是15秒了，如果大于了15秒，那么就说明定时获取出现了问题。那么现在我们要做的，就是筛选出前后两条记录在获取GPS位置的时间差是否大于15秒，计算时间差的方法在MySQL中已经有了，那就是TimeDiff（对于TimeDiff在Java中使用的小坑我前面的博文有所总结，有兴趣的朋友可以Mark一下，后面抽时间看看。），而对于计算前后两条记录怎么计算差值，这好像确实没有接触过，那么本篇文章就来解决这个问题。

 

　　传送门：SQL函数TIMEDIFF在Java程序中使用报错的问题分析

 

　　https://www.cnblogs.com/captainad/p/10855608.html

 

　　解决

 

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

 

　　1--司机GPS收集表

 

　　2CREATETABLEcaptainad_driver_gps_position(

 

　　3idBIGINTNOTNULLauto_incrementCOMMENT'主键',

 

　　4business_idBIGINTDEFAULTNULLCOMMENT'业务ID',

 

　　5device_macVARCHAR(64)DEFAULTNULLCOMMENT'设备MAC地址',

 

　　6device_imeiVARCHAR(64)DEFAULTNULLCOMMENT'设备IMEI',

 

　　7lat_lngVARCHAR(64)DEFAULTNULLCOMMENT'纬经度',

 

　　8capture_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'捕获时间',

 

　　9create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

 

　　10update_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改时间',

 

　　11PRIMARYKEY(id),

 

　　12KEY`idx_business_id`(`business_id`)USINGBTREE

 

　　13)ENGINE=INNODBDEFAULTCHARSET=utf8COMMENT='司机GPS收集';

 

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

 

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

 

　　打印行号的SQL语句：

 

　　1SELECT

 

　　2(@rownum:=@rownum+1)ASrownum,

 

　　3tab.business_id,

 

　　4tab.device_mac,

 

　　5tab.capture_time

 

　　6FROM

 

　　7captainad_driver_gps_positiontab,

 

　　8(SELECT@rownum:=0)r--声明变量

 

　　9WHERE

 

　　101=1

 

　　11ANDDATE_FORMAT(

 

　　12tab.capture_time,

 

　　13'%Y-%m-%d'

 

　　14)='2019-06-28'

 

　　15ORDERBY

 

　　16tab.capture_time

 

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

 

　　1SELECT

 

　　2t.business_id,

 

　　3t.device_mac,

 

　　4t.capture_time,

 

　　5t.tdiff

 

　　6FROM

 

　　7(

 

　　8SELECT

 

　　9r1.business_id,

 

　　10r1.device_mac,

 

　　11r1.capture_time,

 

　　12TIMEDIFF(

 

　　13r2.capture_time,

 

　　14r1.capture_time

 

　　15)AS'tdiff'

 

　　16FROM

 

　　17(

 

　　18SELECT

 

　　19(@rownum:=@rownum+1)ASrownum,

 

　　20tab.business_id,

 

　　21tab.device_mac,

 

　　22tab.capture_time

 

　　23FROM

 

　　24captainad_driver_gps_positiontab,

 

　　25(SELECT@rownum:=0)r

 

　　26WHERE

 

　　271=1

 

　　28ANDDATE_FORMAT(

 

　　29tab.capture_time,

 

　　30'%Y-%m-%d'

 

　　31)='2019-06-28'

 

　　32ORDERBY

 

　　33tab.capture_time

 

　　34)r1

 

　　35LEFTJOIN(

 

　　36SELECT

 

　　37(@INDEX:=@INDEX+1)ASrownum,

 

　　38tab.business_id,

 

　　39tab.device_mac,

 

　　40tab.capture_time

 

　　41FROM

 

　　42captainad_driver_gps_positiontab,

 

　　43(SELECT@INDEX:=0)r

 

　　44WHERE

 

　　451=1

 

　　46ANDDATE_FORMAT(

 

　　47tab.capture_time,

 

　　48'%Y-%m-%d'

 

　　49)='2019-06-28'

 

　　50ORDERBY

 

　　51tab.capture_time

 

　　52)r2ONr1.business_id=r2.business_id

 

　　53ANDr1.device_mac=r2.device_mac

 

　　54ANDr1.rownum=r2.rownum-1

 

　　55)t

 

　　56WHERE

 

　　57t.tdiff>'00:00:15'

 

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