MySql时间粒度下分组统计

# 我们在做项目或者数据分析时，经常遇到这样的需求：统计不同时间粒度下的数据分布情况


-- 按照不同时间粒度进行分组统计


-- 按天
SELECT date(`下单日期时间`) as date1
       ,count(1) as num 
from orders
group by   date(`下单日期时间`)
order by date1 asc;

-- 按照每小时

SELECT  date_format(`下单日期时间`,'%Y-%m-%d %H:00:00') as date2
       ,count(1) as num 
from orders
group by date_format(`下单日期时间`,'%Y-%m-%d %H:00:00')
order by date2 asc;


-- 按照半小时
SELECT  date3
			 ,count(1) as num
from 
(SELECT    DATE_FORMAT( concat(date(`下单日期时间`)
															 ,' '
															 ,HOUR(`下单日期时间`)
															 ,':'
															 ,floor(MINUTE(`下单日期时间`)/30) * 30 
															  )
												,'%Y-%m-%d %H:%i')  as date3
-- 					,concat(date(`下单日期时间`),' '
-- 											  ,HOUR(`下单日期时间`)
-- 												,':'
-- 												,floor(MINUTE(`下单日期时间`)/30) * 30 
-- 								 ) as date3_1 /* 必须要规范格式的原因 */
			 ,`用户id` 
from orders) t -- 将维度进行加工转换成业务需求的维度
group by date3
order by date3 asc;


-- 按照N分钟进行统计



SET @terval := 20; -- 进阶：设定参数：


SELECT  date3
			 ,count(1) as num
from 
(SELECT    DATE_FORMAT( concat(date(`下单日期时间`)
															 ,' '
															 ,HOUR(`下单日期时间`)
															 ,':'
															 ,floor(MINUTE(`下单日期时间`)/@terval) * @terval 
															  )
												,'%Y-%m-%d %H:%i')  as date3
			 ,`用户id` 
from orders) t -- 将维度进行加工转换成业务需求的维度
group by date3
order by date3 asc;

-- 按照分钟统计：

SELECT  date_format(`下单日期时间`,'%Y-%m-%d %H:%i:00') as date4
       ,count(1) as num 
from orders
group by date_format(`下单日期时间`,'%Y-%m-%d %H:%i:00')
order by date4 asc;



-- 作业1：使用SQL、Python复盘一下上周时间格式转换问题,并结合Excel三者实现逻辑进行对比;

-- 作业2：实现每半个小时/每小时/每2小时获得一次均值数据

-- 按照每N小时

SET @terval := 2; -- 每2小时


SELECT  date5
			 ,avg(`订单金额` ) as avg_income
from 
(SELECT    DATE_FORMAT( concat(date(`下单日期时间`)
															 ,' '
															 ,floor(HOUR(`下单日期时间`)/@terval)*@terval
															 )
												,'%Y-%m-%d %H')  as date5
			 ,`订单金额` 
from orders) t -- 将维度进行加工转换成业务需求的维度
group by date5
order by date5 asc;

转载：https://blog.youkuaiyun.com/weixin_44976611/article/details/119306556