簡述

博主最近因工作任務纏身，都無暇顧及到我的這片自留地了。前段時間稍有空閑，花了較多的精力學習《啊哈演算法》，從中學習到很多之前沒有太註重的內容，收益頗豐。但是這些演算法題目還沒有看完，等後面有時間了，還需重新自我溫習一下前面所寫的內容，並且繼續耕耘後面的演算法知識。

今天稍微有點時間，總結一下博主近期工作中所遇到的一些難題，希望藉此機遇總結一下類似問題的解決方法，也算是一種積累吧。

背景

我們在司機的手機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一下，後面抽時間看看。），而對於計算前後兩條記錄怎麼計算差值，這好像確實沒有接觸過，那麼本篇文章就來解決這個問題。

傳送門：SQL函數TIMEDIFF在Java程式中使用報錯的問題分析

https://www.cnblogs.com/captainad/p/10855608.html

解決

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

 1 -- 司機GPS收集表
 2 CREATE TABLE captainad_driver_gps_position (
 3     id BIGINT NOT NULL auto_increment COMMENT '主鍵',
 4     business_id BIGINT DEFAULT NULL COMMENT '業務ID',
 5     device_mac VARCHAR (64) DEFAULT NULL COMMENT '設備MAC地址',
 6     device_imei VARCHAR (64) DEFAULT NULL COMMENT '設備IMEI',
 7     lat_lng VARCHAR (64) DEFAULT NULL COMMENT '緯經度',
 8     capture_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '捕獲時間',
 9     create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
10     update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
11     PRIMARY KEY (id),
12     KEY `idx_business_id` (`business_id`) USING BTREE
13 ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '司機GPS收集';

表中記錄的數據大致如下：

現在就對按獲取GPS位置的時間capture_time在按照時間排序之後，進行前後兩條記錄計算差值。為了計算兩者的差值，那麼我們肯定是需要獲取到一前一後兩條記錄的，這裡我們可以巧用一個變數來記錄當前行的行數，然後隨著迴圈查詢每次將行數疊加，以達到行記錄的目的，這樣一來，我們就能知道哪兩條記錄是一前一後的了。

列印行號的SQL語句：

 1 SELECT
 2     (@rownum := @rownum + 1) AS rownum,
 3     tab.business_id,
 4     tab.device_mac,
 5     tab.capture_time
 6 FROM
 7     captainad_driver_gps_position tab,
 8     (SELECT @rownum := 0) r  -- 聲明變數
 9 WHERE
10     1 = 1
11 AND DATE_FORMAT(
12     tab.capture_time,
13     '%Y-%m-%d'
14 ) = '2019-06-28'
15 ORDER BY
16     tab.capture_time

基於此，我們將目標SQL給寫出來，這裡我根據我們的實際業務將語句稍微做了整理，腳本大致如下：

 1 SELECT
 2     t.business_id,
 3     t.device_mac,
 4     t.capture_time,
 5     t.tdiff
 6 FROM
 7     (
 8         SELECT
 9             r1.business_id,
10             r1.device_mac,
11             r1.capture_time,
12             TIMEDIFF(
13                 r2.capture_time,
14                 r1.capture_time
15             ) AS 'tdiff'
16         FROM
17             (
18                 SELECT
19                     (@rownum := @rownum + 1) AS rownum,
20                     tab.business_id,
21                     tab.device_mac,
22                     tab.capture_time
23                 FROM
24                     captainad_driver_gps_position tab,
25                     (SELECT @rownum := 0) r
26                 WHERE
27                     1 = 1
28                 AND DATE_FORMAT(
29                     tab.capture_time,
30                     '%Y-%m-%d'
31                 ) = '2019-06-28'
32                 ORDER BY
33                     tab.capture_time
34             ) r1
35         LEFT JOIN (
36             SELECT
37                 (@INDEX := @INDEX + 1) AS rownum,
38                 tab.business_id,
39                 tab.device_mac,
40                 tab.capture_time
41             FROM
42                 captainad_driver_gps_position tab,
43                 (SELECT @INDEX := 0) r
44             WHERE
45                 1 = 1
46             AND DATE_FORMAT(
47                 tab.capture_time,
48                 '%Y-%m-%d'
49             ) = '2019-06-28'
50             ORDER BY
51                 tab.capture_time
52         ) r2 ON r1.business_id = r2.business_id
53         AND r1.device_mac = r2.device_mac
54         AND r1.rownum = r2.rownum - 1
55     ) t
56 WHERE
57     t.tdiff > '00:00:15'

在上面的代碼中，我們通過 r1.rownum = r2.rownum - 1來判斷兩條記錄是否是前後行，然後再使用TIMEDIFF函數來計算時間差，到此，我們的目標就實現了。