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

痛定思痛。 2022-02-24 05:20 342阅读 0赞

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

*  mysql不同时间粒度下的分组统计
    
     *  按天统计
     *  按小时统计
     *  按半小时统计
     *  按N分钟统计
     *  按分钟统计

### mysql不同时间粒度下的分组统计 ###

我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况，例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等。对于此类的问题，一个sql简单的查询就能实现，故特此记录下，方便以后使用。

在MySQL中，我的表为：track  
数据结构如下所示：

![20190403103257392.png][]

### 按天统计 ###

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;

### 按N小时统计 ###

SELECT time,COUNT(*) AS num FROM
    	(
    		SELECT
    			DATE_FORMAT(
    				concat(
    					date(TimeStart),
    					' ',
    					FLOOR(HOUR(TimeStart) / 2) * 2
    				),
    				'%Y-%m-%d %H:%00:00'
    			) AS time
    		FROM
    			t_table
    	) a
    GROUP BY DATE_FORMAT(time, '%Y-%m-%d %H:%00:00')

###    结果如下： ###

![20190403103422614.png][]

### 按半小时统计 ###

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;

结果如下：

![20190403103528446.png][]

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

### 按分钟统计 ###

将按小时统计的SQL语句稍微修改下，就可以实现按分钟统计

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;

DATE\_FORMAT功能强大，可以根据format字符串格式化date值，参考下面链接  
[http://www.w3school.com.cn/sql/func\_date\_format.asp][http_www.w3school.com.cn_sql_func_date_format.asp]  
参考博客：

[https://blog.csdn.net/kaka\_buka/article/details/52614643][https_blog.csdn.net_kaka_buka_article_details_52614643]  
[https://blog.csdn.net/Beingccccc/article/details/78685490][https_blog.csdn.net_Beingccccc_article_details_78685490]  
\---------------------   
**原文网址：**[https://blog.csdn.net/u010946448/article/details/83752984 ][https_blog.csdn.net_u010946448_article_details_83752984]

[20190403103257392.png]: /images/20220224/4a3078f5e9314393a89dce9542e39d6b.png
[20190403103422614.png]: /images/20220224/428e8b1237a24e5e8978e748b2bd0164.png
[20190403103528446.png]: /images/20220224/0c408598ffef4425b883b5ac6bcfb5af.png
[http_www.w3school.com.cn_sql_func_date_format.asp]: http://www.w3school.com.cn/sql/func_date_format.asp
[https_blog.csdn.net_kaka_buka_article_details_52614643]: https://blog.csdn.net/kaka_buka/article/details/52614643
[https_blog.csdn.net_Beingccccc_article_details_78685490]: https://blog.csdn.net/Beingccccc/article/details/78685490
[https_blog.csdn.net_u010946448_article_details_83752984]: https://blog.csdn.net/u010946448/article/details/83752984%C2%A0