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

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

按天统计

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

按小时统计

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

结果：

按半小时统计

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分钟统计

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即可。

按分钟统计

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