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

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

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

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

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

5、ment&39;設(shè)備imei&39;,7lat_lngvarchar(64)defaultnullcomment&39;緯經(jīng)度&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;表中記錄的數(shù)據(jù)大致如下：現(xiàn)在就對按獵取gps位置的時光capture_time在根據(jù)時光排序之后，舉行前后兩條記錄計算差值。為了計算兩者的差值，那么我們絕對是需要獵取到一前一后兩條記錄的，這里我們可以巧用一個變量來記錄當(dāng)前行的行數(shù)，然后隨著循環(huán)查詢每次將行數(shù)疊加，以達到行記錄的目的，這樣

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基于此，我們將目標(biāo)sql給寫出來，這里我按照我們的實際業(yè)務(wù)將語句略微做了收拾，腳本大致如下：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