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

短命女 2021-11-23 05:26 487阅读 0赞

[原文链接][Link 1]

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

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

## 按天统计 ##

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

## 按小时统计 ##

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;
    
     
      
     
      
       1
       2
       3
       4
       5

结果如下：  
![一小时结果][20181105164437450.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;
    
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13

结果如下：  
![半小时查询结果][20181105164102966.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;
    
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13

基本思路：  
将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;
    
     
      
     
      
       1
       2
       3
       4
       5

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

1.  [https://blog.csdn.net/kaka\_buka/article/details/52614643][https_blog.csdn.net_kaka_buka_article_details_52614643]
2.  [https://blog.csdn.net/Beingccccc/article/details/78685490][https_blog.csdn.net_Beingccccc_article_details_78685490]

[Link 1]: https://blog.csdn.net/u010946448/article/details/83752984
[track]: /images/20211122/a9ce1f43ebf44db58b84c8e366dd3b39.png
[20181105164437450.png]: /images/20211122/73502b2a946c438e8642de9abfc998c8.png
[20181105164102966.png]: /images/20211122/67356dcf0afc4cc3818bac0b7ca296cd.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