【HIVE】SQL实现统计每五分钟交易量

五分钟交易量"/>

【HIVE】SQL实现统计每五分钟交易量

最近单位组织大数据考试，有一道SQL题，题目如下

已知：交易表（trade）

交易表结构如下：

按照以下格式统计9：30以后每五分钟发生的交易量及交易占比：

当时直接蒙圈了

现在经过多方查证总结了以下两种实现方案

1，通过函数 floor()、ceil()、date_format()函数分别获取交易时间字段对应的归属五分钟时间段

具体实现sql如下：

select concat('[',a.minute_pgs,' ',a.minute_pge,')') minute_pg,count(1) cnt,concat(cast(cast(count(1)/18*100 as decimal(10,2)) as string),'%') as ratefrom (select t1.trade_no,t1.trade_time,concat_ws(':',cast(date_format(t1.trade_time,'HH') as string),case when cast((floor(date_format(t1.trade_time,'mm')/5 )*5) as string) = '0' then '00'when cast((floor(date_format(t1.trade_time,'mm')/5 )*5) as string) = '5' then '05'else cast((floor(date_format(t1.trade_time,'mm')/5 )*5) as string) end,'00') as minute_pgs,concat_ws(':',cast(date_format(t1.trade_time,'HH') as string),case when cast((CEIL(date_format(t1.trade_time,'mm')/5 )*5) as string) = '0' then '00'when cast((CEIL(date_format(t1.trade_time,'mm')/5 )*5) as string) = '5' then '05'else cast((CEIL(date_format(t1.trade_time,'mm')/5 )*5) as string) end,'00') as minute_pgefrom odss.trade t1where t1.trade_time >= '2022-08-25 09:30:00') agroup by a.minute_pgs,a.minute_pge

方法二 通过lateral view函数配合explode及split 实现时间列表初始化

具体实现代码如下：

with time_list as (
select 
idx,
from_unixtime(unix_timestamp(t.minNum)+300*idx) start_time,
from_unixtime(unix_timestamp(t.minNum)+300*(idx+1)) end_time,
SUBSTRING(from_unixtime(unix_timestamp(t.minNum)+300*idx),12,19) start_tm_str,
SUBSTRING(from_unixtime(unix_timestamp(t.minNum)+300*(idx+1)),12,19) end_tm_str
from(select DATE_FORMAT('2022-08-25 09:30:00','yyyy-MM-dd HH:mm:ss') as minNum,split(space(173-0),'') as x)t  --09-30 到凌晨还有 174个5分钟 idx起点=0
lateral view posexplode(x) pe as idx,se --类似Oracle 递归操作
), --初始化时间列表
tot_cnt as(
select count(1) tcnt    
from odss.trade t1
where t1.trade_time >= '2022-08-25 09:30:00'and t1.trade_time <= '2022-08-25 23:59:59'
)--计算09:30:00后交易总量select CONCAT('[',l.start_tm_str,' ',l.end_tm_str,')') time_pg,  --拼接时间段count(1) cnt,CONCAT(cast(cast(count(1)/c.tcnt * 100 as decimal(10,2)) as string),'%') rate   --计算利率  
from time_list l inner join odss.trade tinner join tot_cnt cwhere t.trade_time >= l.start_time --hive on 不支持不等式操作 这里使用where条件and t.trade_time < l.end_time
group by l.start_tm_str,l.end_tm_str,c.tcnt;