sql時間段切分實現每隔x分鐘出一份高速門架車流量

業務場景:

我們需要查詢從表T裡查詢某一天按照5分鐘為一段間隔通過高速門架的車流量。部分脫敏數據如下

表T裡包含time,id,chepai,部分數據為(’2020-02-23 00:43:27','某高速門架0010','浙XXXXXX')

按照正常情況下我們的限定where條件是time >= '2020-02-23 0:00' and time < '2020-02-23 0:00'  或者 between等,但是這樣來寫一兩個還行,要每隔5分鐘切分一次,需要寫二百多,過於麻煩

解決思路:

  • 需要把時間進行下降到其5分鐘開始的最初時間處理,即00:12:30是在00:10:00-00:15:00之間,要轉換為00:10:00,同樣10:21:39在10:20:00-10:25:00,所以轉換為10:20:00,這樣一來五分鐘內的數據都下降統一成5分初的時間,也就是5分鐘這一段的數據就有瞭。當然也可以把數據進行上升處理,隻不過23:55:00-23:59:59這個時間段就上升到第二天的00:00瞭
  • 對時間段進行下降處理思想有瞭,問題是如何實現:可以把時間字段轉換為數字整形,即每一個時間都對應著一串數字,隻要把這個數字由年月日時分秒切割相加再除以5,然後向下取整再乘以5,最後再轉換為時間,這樣就能達到下降瞭,這裡的核心思想就是無法整除5的那部分餘數就是5分鐘這個時間段的,現在我們向下取整,比如2.75向下取整是2,那麼就能把時間下降瞭。同樣的如果是每隔10分鐘,那就是除以10,每隔1小時的,直接substr切分時間字段就可以瞭
  • 這裡使用強大的兩個函數FROM_UNIXTIME和UNIX_TIMESTAMP來完成,具體使用方法參加我的舊文https://www.jb51.net/article/239100.htm腳本實現:
SELECT t.time,
        t.id,
        COUNT(DISTINCT(t.chepai)) num
FROM 
    (SELECT time,
        id,
		-- FROM_UNIXTIME把整型數字轉為時間類型
        FROM_UNIXTIME(
			-- 一般年月日無需動,直接unix_timestamp先轉為整型再加上時分秒即可
			unix_timestamp(left(time,10))
			-- 分*60+秒然後除以5,floor向下取整後再*5*60,具體思路見上
			+5*60*floor((substr(PicTime,12,2)*60+substr(Pictime,15,2))/5)
			) time,
        chepai
    FROM T
    WHERE left(time,10) = '2020-02-23'
    ORDER BY  time asc)t
GROUP BY  t.time,t.id
ORDER BY  t.time,t.id

到此這篇關於sql時間段切分實現每隔x分鐘出一份高速門架車流量的文章就介紹到這瞭,更多相關sql時間段切分內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: