|
最近单位组织大数据考试，有一道SQL题，题目如下
已知：交易表（trade）
交易表结构如下：
| trade_no | trade_time | | 10001 | 2022/8/29 09:30:37 | | 10002 | 2022/8/29 09:31:02 | | 10003 | 2022/8/29 09:32:51 | | … | … |
按照以下格式统计9：30以后每五分钟发生的交易量及交易占比：
| 时间段 | 交易量 | 占比 | | [09:30:00 09:35:00) | 30 | 2.00% | | [09:35:00 09:40:00) | 15 | 1.00% | | [09:40:00 09:45:00) | 5 | 0.33% | | … | … | … |
当时直接蒙圈了
现在经过多方查证总结了以下两种实现方案
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?rate
from?(
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_pge
from?odss.trade?t1
where?t1.trade_time?>=?'2022-08-25?09:30:00'
)?a
group?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 t
inner join tot_cnt c
where 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;
?
|