Sql語句實現不同記錄同一屬性列的差值計算

風靈使發表於2018-11-08

所使用的表的具體結構如下圖所示
在這裡插入圖片描述

Table中主鍵是(plateNumber+currentTime

要實現的查詢是:

給定車牌號和查詢的時間區間,查詢給定的時間區間內所包含記錄的currentTime的差值,並計算AverageSpeed和該差值的乘積,求這段時間內的最高速度(HighestSpeed),並按照type值得不同進行劃分。–>(type值只有0和1兩個值)

主要思路是,首先能夠得出的是相同type型別下同一個車牌號(也即同一車輛)在給定的時間區間內的currentTime的差值,比如按照currentTime排序號,相鄰兩條記錄currentTime的差值,得出這個以後,其餘的都可以通過聚合函式得出。

我們以車牌號為京A111111為例,設計如下圖所示的測試用例。
在這裡插入圖片描述

可以看到車牌號為京A111111的車輛總共有6條記錄,其中type為0的有兩條,type為1的有4條,

我們首先計算時間的差值,sql語句書寫如下:

SELECT a.platenumber, 
       a.currenttime, 
       a.type, 
       a.averagespeed, 
       a.highestspeed, 
       currenttime - (SELECT currenttime 
                      FROM   carmultispeedinfo 
                      WHERE  platenumber = a.platenumber 
                             AND type = a.type 
                             AND currenttime < a.currenttime 
                      ORDER  BY currenttime DESC 
                      LIMIT  1)AS timediff 
FROM   carmultispeedinfo a 

通過navicat可以看到如下圖所示的查詢結果:

通過核查timediff的值是正確的,這樣之後就可以在這個基礎上新增內容了。

完整的sql語句如下:

SELECT Sum(aa.averagespeed * aa.timediff) AS milesdiff, 
       Max(aa.highestspeed)               AS HighestSpeed, 
       Sum(aa.timediff)                   AS timediff, 
       aa.type 
FROM   (SELECT a.platenumber, 
               a.currenttime, 
               a.type, 
               a.averagespeed, 
               a.highestspeed, 
               currenttime - (SELECT currenttime 
                              FROM   carmultispeedinfo 
                              WHERE  platenumber = a.platenumber 
                                     AND type = a.type 
                                     AND currenttime < a.currenttime 
                              ORDER  BY currenttime DESC 
                              LIMIT  1) AS timediff 
        FROM   carmultispeedinfo a)aa 
WHERE  aa.platenumber = '京A111111' 
       AND aa.currenttime >= 1521790124670 
       AND aa.currenttime <= 1521790125685 
GROUP  BY aa.type 

顯示結果如下:
在這裡插入圖片描述

經過核對,是我們想要得出的結果。之後將sql對應替換到mybatismapper檔案中就可以了。<記錄一下,備忘>將來有更深入的理解之後會繼續更新,謝謝大家。

相關文章