mysql按天，小时，半小时，N分钟，分钟进行数据分组统计

佚名 6年前 (2019-04-17) 随笔 775人围观抢沙发百度已收录

表为：track
数据结构如下所示：

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

按天统计

1 SELECT DATE(TimeStart) AS date, COUNT(*) AS num
2 FROM track
3 WHERE Flag = 0 AND Duration >= 300 
4 GROUP BY date
5 ORDER BY date;

按小时统计

1 SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:00:00') AS time, COUNT(*) AS num
2 FROM track
3 WHERE Flag = 0 AND Duration >= 300
4 GROUP BY time
5 ORDER BY time;

结果：

按半小时统计

 1 SELECT time, COUNT( * ) AS num 
 2 FROM
 3     (
 4     SELECT Duration,
 5         DATE_FORMAT(
 6             concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 30 ) * 30 ),
 7             '%Y-%m-%d %H:%i' 
 8         ) AS time 
 9     FROM tarck
10     WHERE Flag = 0  AND Duration >= 300 
11     ) a 
12 GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
13 ORDER BY time;

结果：

按N分钟统计

 1 SELECT time, COUNT( * ) AS num 
 2 FROM
 3     (
 4     SELECT Duration,
 5         DATE_FORMAT(
 6             concat( date( TimeStart ), ' ', HOUR ( TimeStart ), ':', floor( MINUTE ( TimeStart ) / 10 ) * 10 ),
 7             '%Y-%m-%d %H:%i' 
 8         ) AS time 
 9     FROM tarck
10     WHERE Flag = 0  AND Duration >= 300 
11     ) a 
12 GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
13 ORDER BY time;

思路：将datetime类型的时间转化为相应时间片的时间，例如将‘2017-03-01 01:08:19’ 转化为‘2017-03-01 01:00:00’，然后　group by即可。

按分钟统计

1 SELECT DATE_FORMAT(TimeStart, '%Y-%m-%d %H:%i:00') AS time, COUNT(*) AS num
2 FROM track 
3 WHERE Flag = 0 AND Duration >= 300
4 GROUP BY time
5 ORDER BY time;