mysql根据小时分组 mysql按分钟分组

按照季度统计

SELECT CONCAT(DATE_FORMAT(create_time, '%Y'),'-',FLOOR((DATE_FORMAT(create_time, '%m')+2)/3),'季度') AS QUARTER ,
COUNT(1) AS 总量 FROM 表 WHERE
 create_time>'2021-01-01' AND create_time<'2022-01-01'
GROUP BY QUARTER
ORDER BY QUARTER;

 

按月统计

SELECT DATE_FORMAT(create_time,'%Y-%m') months,COUNT(1) COUNT 
FROM 表 
WHERE create_time>'2021-01-01' AND create_time<'2021-06-01'
GROUP BY months
ORDER BY months;

 

按周统计

SELECT DATE_FORMAT(create_time,'%Y%u') weeks,COUNT(1)COUNT FROM t_product_spu 
WHERE create_time>'2021-01-01' AND create_time<'2021-03-26'
GROUP BY weeks
ORDER BY weeks;

 

按照天统计

SELECT DATE(create_time) AS DATE,COUNT(*)  FROM  表
WHERE create_time<'2021-01-10'
GROUP BY DATE
ORDER BY DATE;

按照小时统计

SELECT DATE_FORMAT(create_time,'%Y-%m-%d %H:00:00') AS TIME,COUNT(*) FROM 表
WHERE create_time>'2021-03-25' AND create_time<'2021-03-26'
GROUP BY TIME
ORDER BY TIME;

按照每半个小时统计分组

SELECT TIME, COUNT(*) AS num 
FROM
	(
	SELECT id,
		DATE_FORMAT(
			CONCAT( DATE( create_time ), ' ', HOUR ( create_time ), ':', FLOOR( MINUTE ( create_time ) / 30 ) * 30 ),
			'%Y-%m-%d %H:%i' 
		) AS TIME 
	FROM 表
	WHERE create_time>'2021-03-25' AND create_time<'2021-03-26'
	) a 
GROUP BY TIME 
ORDER BY TIME;

 

 

指定分钟内的统计分组

SET @min=20; -- 以每20分钟味分组的查询为例
SELECT TIME, COUNT( * ) AS num 
FROM
	(
	SELECT 
		DATE_FORMAT(
			CONCAT( DATE( create_time ), ' ', HOUR ( create_time ), ':', FLOOR( MINUTE ( create_time ) / @min ) * @min ),
			'%Y-%m-%d %H:%i' 
		) AS TIME 
	FROM 表
	WHERE create_time>'2021-05-12' AND create_time<'2021-05-13'
	) a 
GROUP BY TIME 
ORDER BY TIME;

按照分钟统计

SELECT DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:00') AS TIME, COUNT(*) AS num
FROM 表 
WHERE create_time>'2021-05-12 09:40' AND create_time<'2021-05-12 09:50'
GROUP BY TIME
ORDER BY TIME;