问题内容
- 今天遇到了一个需求,需求就是时间段为5分钟的统计.有数据的时间戳.对成交单量进行统计.
想法思路
- 因为数据有时间戳,可以通过from_unixtime()来获取具体的时间.
- 有了具体的时间,就可以用minute()函数获取对应数据所在的分钟.(minute()获取到的分钟为字符串,需要进行类型转换cast())
- 那么怎么通过获取到的minute来进行分组呢?
- 想法 00 - 05 应该分到一组, 05 - 10 应该分到第二组,依次类推.
- 用minute 整除 5 的话, 00 - 05 整除 5 都为 0, 05 - 10 整除 5 都为1,依次类推
- 这样就可以把每5分钟的数据都分到了一组.
- 注意,hive中的 minute / 5 的话并能整除,所以需要函数向下取整,用floor()函数来完成.
- 虽然把分钟分开了,但是并不能仅仅按照分钟来分组,这样就把每个小时的相同分组分成一组,所以每个小时之间也是一个聚合维度.
具体步骤
- 先通过时间戳取到数据对应的小时和分钟.
- 小时 from_unixtime(time,'yyyyMMdd HH')
- 分钟 minute(from_unixtime(time))
- 对分钟进行整除5来完成分段.(同时需要对获取到的minute进行类型转换)
- floor(cast(minute(from_unixtime(time)) as int) / 5)
- 如果要是变时间段的话,分钟为单位,除以相应的时间段就行.10分钟就整除10,15分钟就整除15.
- 对小时和(分钟整除的结果)进行分组进行统计.
示例SQL
select
case
when sort in (0,1) then concat(substring(time,10,11),':0',sort*5)
else concat(substring(time,10,11),':',sort*5)
end, --对时间格式进程处理,把小时和分钟合并 例如,hour =2018111 00,sort = 1 -> 00:05
num --统计的字段
from(
select time,sort,count(distinct purchase_id) as num --统计
from
(
select purchase_id,--需要统计的字段
from_unixtime(add_time,'yyyyMMdd HH') as time, --取小时
floor(cast(minute(from_unixtime(add_time)) as int) / 5 ) as sort --按分钟归类
from kkgoo.kk_order_association
where from_unixtime(add_time,'yyyyMMdd') = 20181111 --筛选条件
and status not in ('unpayed', 'close', 'timeout_pay', 'timeout_lock')
) t
group by 1,2 --对小时和分钟整除结果进行分组
) t;