.jpg)
mysql計(jì)算相鄰兩行某列差值的方法：首先通過(guò)【r1.rownum = r2.rownum – 1】來(lái)判斷兩條記錄是否是前后行；然后再使用TIMEDIFF函數(shù)來(lái)計(jì)算時(shí)間差即可。
【相關(guān)學(xué)習(xí)推薦：mysql教程(視頻)】
MySQL計(jì)算相鄰兩行某列差值的方法：
首先博主在服務(wù)端有一個(gè)表來(lái)記錄司機(jī)上報(bào)上來(lái)的GPS點(diǎn)位信息，表結(jié)構(gòu)如下：
--?司機(jī)GPS收集表 CREATE?TABLE?captainad_driver_gps_position?( ????id?BIGINT?NOT?NULL?auto_increment?COMMENT?'主鍵', ????business_id?BIGINT?DEFAULT?NULL?COMMENT?'業(yè)務(wù)ID', ????device_mac?VARCHAR?(64)?DEFAULT?NULL?COMMENT?'設(shè)備MAC地址', ????device_imei?VARCHAR?(64)?DEFAULT?NULL?COMMENT?'設(shè)備IMEI', ????lat_lng?VARCHAR?(64)?DEFAULT?NULL?COMMENT?'緯經(jīng)度', ????capture_time?TIMESTAMP?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'捕獲時(shí)間', ????create_time?TIMESTAMP?DEFAULT?CURRENT_TIMESTAMP?COMMENT?'創(chuàng)建時(shí)間', ????update_time?TIMESTAMP?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'修改時(shí)間', ????PRIMARY?KEY?(id), ????KEY?`idx_business_id`?(`business_id`)?USING?BTREE )?ENGINE?=?INNODB?DEFAULT?CHARSET?=?utf8?COMMENT?=?'司機(jī)GPS收集';
表中記錄的數(shù)據(jù)大致如下：
現(xiàn)在就對(duì)按獲取GPS位置的時(shí)間capture_time在按照時(shí)間排序之后，進(jìn)行前后兩條記錄計(jì)算差值。為了計(jì)算兩者的差值，那么我們肯定是需要獲取到一前一后兩條記錄的，這里我們可以巧用一個(gè)變量來(lái)記錄當(dāng)前行的行數(shù)，然后隨著循環(huán)查詢每次將行數(shù)疊加，以達(dá)到行記錄的目的，這樣一來(lái)，我們就能知道哪兩條記錄是一前一后的了。
打印行號(hào)的SQL語(yǔ)句：
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
基于此，我們將目標(biāo)SQL給寫(xiě)出來(lái)，這里我根據(jù)我們的實(shí)際業(yè)務(wù)將語(yǔ)句稍微做了整理，腳本大致如下：
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'
在上面的代碼中，我們通過(guò)r1.rownum = r2.rownum – 1來(lái)判斷兩條記錄是否是前后行，然后再使用TIMEDIFF函數(shù)來(lái)計(jì)算時(shí)間差，到此，我們的目標(biāo)就實(shí)現(xiàn)了。
想了解更多編程學(xué)習(xí)，敬請(qǐng)關(guān)注mysql教程欄目！
.png)
