MySQL怎么計算相鄰兩行某列差值

本篇內(nèi)容主要講解“MySQL怎么計算相鄰兩行某列差值”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL怎么計算相鄰兩行某列差值”吧!

　　背景

　　我們在司機的手機APP里預置了定時上報GPS數(shù)據(jù)的功能，功能設(shè)置為了APP每15秒收集一次GPS定位地址，然后每收集到10次就上報到服務器端持久化。但因為APP非安卓系統(tǒng)集成，而是由我方自主提供并設(shè)計了此功能，那么GPS數(shù)據(jù)的收集就有可能因司機人為操作或者其他網(wǎng)絡(luò)等的影響，產(chǎn)生定位偏差或者獲取定位失敗的情況?，F(xiàn)在我們服務端就有這么一個需求，要求分析出安裝在司機手機中的APP是否定期收集到了GPS位置。

　　為了保證GPS定位數(shù)據(jù)能夠較為順利的上傳到服務器端，我們在APP每次請求完服務器端之后，只有當APP接收到來自服務器的正確回應，才能表示這次上傳數(shù)據(jù)是正常且準確的，然后清空掉APP端上一次記錄的GPS定位數(shù)據(jù)；但是如果APP因為各種原因無法將上一次收集的數(shù)據(jù)上報的服務器端，再嘗試一番之后無果，將會在下一次與下一批收集到的數(shù)據(jù)一同上報，通過此機制來保證數(shù)據(jù)傳遞的穩(wěn)定性。

　　分析

　　如果司機手機中的APP是定時15秒就收集到GPS地址的話，那么持久化到數(shù)據(jù)庫中按獲取時間順序排列的前后兩條記錄的時間差應該就是15秒了，如果大于了15秒，那么就說明定時獲取出現(xiàn)了問題。那么現(xiàn)在我們要做的，就是篩選出前后兩條記錄在獲取GPS位置的時間差是否大于15秒，計算時間差的方法在MySQL中已經(jīng)有了，那就是TimeDiff（對于TimeDiff在Java中使用的小坑我前面的博文有所總結(jié)，有興趣的朋友可以Mark一下，后面抽時間看看。），而對于計算前后兩條記錄怎么計算差值，這好像確實沒有接觸過，那么本篇文章就來解決這個問題。

　　解決

　　首先博主在服務端有一個表來記錄司機上報上來的GPS點位信息，表結(jié)構(gòu)如下：

　　1--司機GPS收集表

　　2CREATETABLEcaptainad_driver_gps_position(

　　3idBIGINTNOTNULLauto_incrementCOMMENT'主鍵',

　　4business_idBIGINTDEFAULTNULLCOMMENT'業(yè)務ID',

　　5device_macVARCHAR(64)DEFAULTNULLCOMMENT'設(shè)備MAC地址',

　　6device_imeiVARCHAR(64)DEFAULTNULLCOMMENT'設(shè)備IMEI',

　　7lat_lngVARCHAR(64)DEFAULTNULLCOMMENT'緯經(jīng)度',

　　8capture_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'捕獲時間',

　　9create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時間',

　　10update_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'修改時間',

　　11PRIMARYKEY(id),

　　12KEY`idx_business_id`(`business_id`)USINGBTREE

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

　　表中記錄的數(shù)據(jù)大致如下：

　　現(xiàn)在就對按獲取GPS位置的時間capture_time在按照時間排序之后，進行前后兩條記錄計算差值。為了計算兩者的差值，那么我們肯定是需要獲取到一前一后兩條記錄的，這里我們可以巧用一個變量來記錄當前行的行數(shù)，然后隨著循環(huán)查詢每次將行數(shù)疊加，以達到行記錄的目的，這樣一來，我們就能知道哪兩條記錄是一前一后的了。

　　打印行號的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給寫出來，這里我根據(jù)我們的實際業(yè)務將語句稍微做了整理，腳本大致如下：

　　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函數(shù)來計算時間差，到此，我們的目標就實現(xiàn)了。

到此，相信大家對“MySQL怎么計算相鄰兩行某列差值”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學習！