MYSQL按時間段分組查詢當天，每小時，15分鐘數據分組 · php開發筆記

查詢的需求比較麻煩，根據spreader_id和返回的狀態碼來判斷事件的回傳成功率，但是還需要新增時間段的查詢，比如產品要的是每15分鐘內數據的回傳成功率。這樣的話就需要對時間段進行分組查詢，我保存的數據是時間戳的格式。網上找資料看了下，抄了一些方法，總結以下sql 按照小時分組進行查詢 ``` select FROM_UNIXTIME(timestamp, '%Y-%m-%d %H') as dt_h, -- 這里根據分組的條件進行時間戳的格式化 spreader_id, status, count(*) as num FROM s2s_log.s2s_callback_log WHERE event_type_id = '8' AND timestamp BETWEEN '1599321600' AND '1599494400' group by dt_h, spreader_id, status order by dt_h -- 也可執行 SELECT COUNT(*), DATE_FORMAT(TIME,'%Y-%m-%d %H:00:00') AS TIME FROM tableName GROUP BY TIME /* 如果按小時分組則格式調整為 “%Y-%m-%d %H:00:00” 按分鐘分組則格式調整為 “%Y-%m-%d %H:%i:00” */ ``` 但是這個滿足不了產品的每15分鐘去查詢數據分組的數據拆解搜索網址之后，修改如下，后續修改按照15分鐘分組查詢 ``` SELECT TIME, spreader_id, status, COUNT(*) AS num FROM ( SELECT FROM_UNIXTIME(TIMESTAMP) AS data_dt, -- 需要將時間戳進行格式的轉換 DATE_FORMAT( concat(date(FROM_UNIXTIME(TIMESTAMP)), ' ', HOUR (FROM_UNIXTIME(TIMESTAMP)), ':', floor(MINUTE (FROM_UNIXTIME(TIMESTAMP)) / 15) * 15), '%Y-%m-%d %H:%i') AS TIME, spreader_id, status FROM s2s_log.s2s_callback_log WHERE event_type_id = '8' AND TIMESTAMP BETWEEN '1599321600' AND '1599580800' ) a -- 時間戳限制3日內的數據 GROUP BY DATE_FORMAT(TIME, '%Y-%m-%d %H:%i'), spreader_id, status ORDER BY TIME ``` 以上的sql根據日期的分鐘來進行篩選（15分鐘），后續分組的維度為spreader_id, status得出的數據便是不同的渠道在15分鐘的時間點內數據的狀態。 mysql相關函數 DATE_FORMAT() ''' DATE_FORMAT() 函數用于以不同的格式顯示日期/時間數據。語法： DATE_FORMAT(date,format) 參數： https://www.w3school.com.cn/sql/func_date_format.asp 總結 mysql不同時間粒度下的分組統計按天統計按小時統計按半小時統計按N分鐘統計按分鐘統計按天統計 ``` SELECT DATE(dt) AS date, COUNT(*) AS num FROM my_table WHERE Flag = 0 AND Duration >= 300 GROUP BY date ORDER BY date; ``` 按小時統計 ``` SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num -- SELECT DATE_FORMAT(dt, '%Y-%m-%d %H') AS time, COUNT(*) AS num FROM track WHERE Flag = 0 AND Duration >= 300 GROUP BY time ORDER BY time; ``` 按每分鐘統計 ``` SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num -- SELECT DATE_FORMAT(dt, '%Y-%m-%d %H:%i') AS time, COUNT(*) AS num FROM track WHERE Flag = 0 AND Duration >= 300 GROUP BY time ORDER BY time; ``` 按30分鐘統計 ``` SELECT time, COUNT( * ) AS num FROM ( SELECT Duration, DATE_FORMAT( concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 30 ) * 30 ), '%Y-%m-%d %H:%i' ) AS time FROM tarck WHERE Flag = 0 AND Duration >= 300 ) a GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) ORDER BY time; ``` 按N分鐘統計將上面的SQL語句稍微修改下，就可以實現按任意N分鐘為時間片的分組統計，如按10分鐘統計，先上代碼： ``` SELECT time, COUNT( * ) AS num FROM ( SELECT Duration, DATE_FORMAT( concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 10 ) * 10 ), '%Y-%m-%d %H:%i' ) AS time FROM tarck WHERE Flag = 0 AND Duration >= 300 ) a GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) ORDER BY time; ``` 基本思路：將datetime類型的時間轉化為相應時間片的時間，例如將‘2017-03-01 01:08:19’ 轉化為‘2017-03-01 01:00:00’，然后group by即可。參考博客 mysql按天，小時，半小時，N分鐘，分鐘進行數據分組統計 https://blog.csdn.net/qq_39268288/article/details/103735748?utm_medium=distribute.pc_relevant.none-task-blog-title-4&spm=1001.2101.3001.4242 sql中時間以5分鐘半個小時任意間隔分組的實現方法 http://www.45fan.com/article.php?aid=19062448408855841712601613 https://www.cnblogs.com/fashflying/p/6023036.html