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