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

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/u010946448/article/details/83752984

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

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

结果如下:
一小时结果

按半小时统计

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

结果如下:
半小时查询结果

按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
参考博客:

  1. https://blog.csdn.net/kaka_buka/article/details/52614643
  2. https://blog.csdn.net/Beingccccc/article/details/78685490
                                </div>
            <link href="https://csdnimg.cn/release/phoenix/mdeditor/markdown_views-e44c3c0e64.css" rel="stylesheet">
                </div>
posted @ 2019-08-21 17:14  奋斗的中年人哈哈哈  阅读(12618)  评论(0编辑  收藏  举报