mysql按天，小时，半小时，N分钟，分钟进行数据分组统计_尘光掠影的博客

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;

按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
			t_table
GROUP BY DATE_FORMAT(time, '%Y-%m-%d %H:%00:00')

结果如下：

按半小时统计

SELECT time, COUNT( * ) AS num 
    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 
GROUP BY DATE_FORMAT( time, '%Y-%m-%d %H:%i' ) 
ORDER BY time;

结果如下：

按N分钟统计

将上面的SQL语句稍微修改下，就可以实现按任意N分钟为时间片的分组统计，如按10分钟统计，先上代码：

SELECT time, COUNT( * ) AS num 
    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 
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
参考博客：

https://blog.csdn.net/kaka_buka/article/details/52614643
https://blog.csdn.net/Beingccccc/article/details/78685490
---------------------
原文网址： https://blog.csdn.net/u010946448/article/details/83752984

原文链接： https://blog.csdn.net/u010946448/article/details/83752984 访问量 Visits 访问量指的是某一段时间内网站被访问的总次数。这无疑是网站流量最重要的指标之一，它体现了网站推广手法的总体效果。流量分析软件都可以按时间，比如每天或每星期，显示出访问数。很多软件还可以以图形方式显示，就更加直观。在进行了某项特定营销活动后，检验效果如何的第一个指标当然就是看所带来的访问数。比如网站的文章被社会化网络大量转载，都经常会带来访问数的急剧提高，但通常在一两天 create table tb(时间 datetime , 金额 int) insert into tb values('2007-1-1 10:00:23' , 8 ) insert into tb values('2007-1-1 10:01:24' , 4 ) insert into tb val 一张电话报表（id,time,telephone） time:表示顾客打入时记录的时间实例如下：1 2006-06-11 05:52:12 13600654607582 2006-06-11 06:09:05 13801380172923 2006-06-11 06:25:13 973638493104 2006-06-11 06:26:08 617638493105 2006-06-11 06: 这个SQL语句的主要功能是从" count_driver_warn" 表中选出所有created_date在7天内的记录，并按照驾驶员名字分组计算每个驾驶员的警告次数数量和警告类型，然后按警告次数从高到低排序。如果有张表是GPS信息，log_bus_driver，其中有经度、纬度字段bus_longitute,bus_latitute，时间是created_date，想按照每5分钟进行分段取信息进行分析，如何做？子句来统计特定时间范围内的数据。：将所有驾驶员的记录进行分组，聚合统计每个驾驶员的总警告次数。 --这个按每五分钟的,你稍微改改就行了. [code=SQL]create table tb(时间 datetime , 金额 int) insert into tb values('2007-1-1 10:00:23' , 8 ) insert into tb values('2007-1-1 10:01:24' , 4 ) insert ... SELECT DATE_FORMAT(departure_time,'%Y-%m-%d') as demand_time , count(0) as count FROM haikou_1 where left(`geo`,6)='w7w3y2' AND DATE_FORMAT(departure_time,'%Y-%m-%d') BETWEEN '2017-06-01' AND '2017-08-31' GROUP BY demand_time ORDER BY demand_time 只需要关联开始时间的hour就可以，因为开始和结束时间都是同一个hour下，这样关联每一条记录会膨胀12条数据。判断游戏时长大于1h的为异常数据，已经过滤了游戏耗时大于1h的数据，所以目前数据的情况是。可以看到开始时间和结束时间判断对应的时间点是否在线的规则是不一样的。得到相对应的时间点，再判断，这个点是否在开始时间和结束时间的区间内。情况1：游戏开始时间和结束时间是在同一天，同一个小时内，则。1，游戏开始时间和结束时间是在同一天，同一个小时内；2，游戏开始时间和结束时间是同一天，不同小时内；按照秒统计数据并倒叙排列取前50条数据： SELECT DATE_FORMAT(insert_time, '%Y-%m-%d %H:%i:%S') AS time, COUNT(*) AS num FROM t_cust WHERE revisit_state in ('3','4') GROUP BY time ORDER BY num DESC limit 50; 按照分钟统计数据并倒叙排列取前50条数据： SELECT DATE_FORMAT(insert_time, '%Y-%m-%d %H:% 前些日子，在做项目的时候遇到一个比较奇葩（其实就是不会）的查询，就是根据传入的时间查询条件，对返回的数据做一次汇总，可能是按照n分钟、n 小时或者n天（具体怎么根据时间控制间隔，我这里就不说了）。下面进入真题。时间间隔类型可分为分钟、小时、天，根据不同的时间间隔类型，有如下规则(其中小时、天的原理与分钟基本一致）： 1）按分钟汇... 解决方法：第一个方法，是分时间段统计，用case解决每个时间段都精确的写出来。但是表中的数据已经跨年了，这种方法仅适合统计的时间跨度比较大的情况（比如统计每年的数据量）。第二个方法，把时间处理，思路是这样的。