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

本篇內容主要講解“MySQL怎么計算相鄰兩行某列差值”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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一下，后面抽時間看看。），而對于計算前后兩條記錄怎么計算差值，這好像確實沒有接觸過，那么本篇文章就來解決這個問題。

　　解決

　　首先博主在服務端有一個表來記錄司機上報上來的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函數來計算時間差，到此，我們的目標就實現了。

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