【hive】时间段为五分钟的统计

问题内容

• 今天遇到了一个需求,需求就是时间段为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;