MySQL計算相鄰兩行某列差值的方法

1、mysql計算相鄰兩行某列差值的方法簡述博主最近因工作任務纏身，都無暇顧及到我的這片自留地了。前段時光稍有空閑，花了較多的精力學習啊哈算法，從中學習到無數之前沒有太注意的內容，收益頗豐。但是這些算法題目還沒有看完，等后面有時光了，還需重新自我溫習一下前面所寫的內容，并且繼續(xù)耕耘后面的算法學問。今日略微有點時光，總結一下博主近期工作中所碰到的一些難題，希翼借此機遇總結一下類似問題的解決辦法，也算是一種堆積吧。背景我們在司機的手機app里預置了定時上報gps數據的功能，功能設置為了app每15秒收集一次gps定位地址，然后每收集到10次就上報到服務器端持久化。但由于app非安卓系統(tǒng)集成，而是由我方

2、自主提供并設計了此功能，那么gps數據的收集就有可能因司機人為操作或者其他網絡等的影響，產生定位偏差或者獵取定位失敗的狀況?，F(xiàn)在我們服務端就有這么一個需求，要求分析出安裝在司機手機中的app是否定期收集到了gps位置。為了保證gps定位數據能夠較為順當的上傳到服務器端，我們在app每次哀求完服務器端之后，惟獨當app接收到來自服務器的正確回應，才干表示這次上傳數據是正常且精確的，然后清空掉app端上一次記錄的gps定位數據；但是假如app由于各種緣由無法將上一次收集的數據上報的服務器端，再嘗試一番之后無果，將會在下一次與下一批收集到的數據一同上報，通過此機制來保證數據傳遞的穩(wěn)定性。分析假如司機

3、手機中的app是定時15秒就收集到gps地址的話，那么持久化到數據庫中按獵取時光挨次羅列的前后兩條記錄的時光差應當就是15秒了，假如大于了15秒，那么就解釋定時獵取浮現(xiàn)了問題。那么現(xiàn)在我們要做的，就是篩選出前后兩條記錄在獵取gps位置的時光差是否大于15秒，計算時光差的辦法在mysql中已經有了，那就是timediff（對于timediff在java中用法的小坑我前面的博文有所總結，有愛好的伴侶可以mark一下，后面抽時光看看。），而對于計算前后兩條記錄怎么計算差值，這好似的確沒有接觸過，那么本篇文章就來解決這個問題。傳送門：sql函數timediff在java程序中用法報錯的問題分析解決首先

4、博主在服務端有一個表來記錄司機上報上來的gps點位信息，表結構如下：1-司機gps收集表2createtablecaptainad_driver_gps_position(3idbigintnotnullauto_incrementcomment&39;主鍵&39;,4business_idbigintdefaultnullcomment&39;業(yè)務id&39;,5device_macvarchar(64)defaultnullcomment&39;設備mac地址&39;,6device_imeivarchar(64)defaultnullcom

5、ment&39;設備imei&39;,7lat_lngvarchar(64)defaultnullcomment&39;緯經度&39;,8capture_timetimestampdefaultcurrent_timestampcomment&39;捕捉時光&39;,9create_timetimestampdefaultcurrent_timestampcomment&39;創(chuàng)建時光&39;,10update_timetimestampdefaultcurrent_timestamponupdatecurrent_timesta

6、mpcomment&39;修改時光&39;,11primarykey(id),12keyidx_business_id(business_id)usingbtree13)engine=innodbdefaultcharset=utf8comment=&39;司機gps收集&39;表中記錄的數據大致如下：現(xiàn)在就對按獵取gps位置的時光capture_time在根據時光排序之后，舉行前后兩條記錄計算差值。為了計算兩者的差值，那么我們絕對是需要獵取到一前一后兩條記錄的，這里我們可以巧用一個變量來記錄當前行的行數，然后隨著循環(huán)查詢每次將行數疊加，以達到行記錄的目的，這樣

7、一來，我們就能知道哪兩條記錄是一前一后的了。打印行號的sql語句：1select2(rownum:=rownum+1)asrownum,3tab.business_id,4tab.device_mac,5tab.capture_time6from7captainad_driver_gps_positiontab,8(selectrownum:=0)r-聲明變量9where101=111anddate_format(12tab.capture_time,13&39;%y-%m-%d&39;14)=&39;2019-06-28&39;15orderby16tab.c

8、apture_time基于此，我們將目標sql給寫出來，這里我按照我們的實際業(yè)務將語句略微做了收拾，腳本大致如下：1select2t.business_id,3t.device_mac,4t.capture_time,5t.tdiff6from7(8select9r1.business_id,10r1.device_mac,11r1.capture_time,12timediff(13r2.capture_time,14r1.capture_time15)as&39;tdiff&39;16from17(18select19(rownum:=rownum+1)asrownum,2

9、0tab.business_id,21tab.device_mac,22tab.capture_time23from24captainad_driver_gps_positiontab,25(selectrownum:=0)r26where271=128anddate_format(29tab.capture_time,30&39;%y-%m-%d&39;31)=&39;2019-06-28&39;32orderby33tab.capture_time34)r135leftjoin(36select37(index:=index+1)asrownum,38tab

10、.business_id,39tab.device_mac,40tab.capture_time41from42captainad_driver_gps_positiontab,43(selectindex:=0)r44where451=146anddate_format(47tab.capture_time,48&39;%y-%m-%d&39;49)=&39;2019-06-28&39;50orderby51tab.capture_time52)r2onr1.business_id=r2.business_id53andr1.device_mac=r2.device_mac54andr1.ro