我要评论mysql不同时间粒度下的分组统计
我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况，例如，每一天中每个小时网站的访问量，某路口每半个小时通过的车辆数量等。对于此类的问题，一个sql简单的查询就能实现，故特此记录下，方便以后使用。
在mysql中，我的表为：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即可。
按分钟统计
将按小时统计的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;
参考资料： https://blog.csdn.net/u010946448/article/details/83752984
到此这篇关于mysql按天/小时/半小时/n分钟/分钟进行数据分组统计的文章就介绍到这了,更多相关mysql数据分组统计内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论