我想在 Hive 中将时差拆分为最接近的半小时
以下是我打算实现的示例
User Start_time End_time Duration
A 2020-05-05 06:45:00 2020-05-05 07:15:00 30
B 2020-05-05 10:15:00 2020-05-05 11:15:00 60
User Start_time End_time Duration
A 2020-05-05 06:45:00 2020-05-05 07:00:00 15
A 2020-05-05 07:00:00 2020-05-05 07:15:00 15
B 2020-05-05 10:15:00 2020-05-05 10:30:00 15
B 2020-05-05 10:30:00 2020-05-05 11:00:00 30
B 2020-05-05 11:00:00 2020-05-05 11:15:00 15
计算floor(duration/30)所需的间隔数，然后使用lateral view posexplode(split(space(s.num_intervals),' '))生成行 请参阅代码中的其他逻辑：
with mytable as (--test dataset
select stack(3,
'A', '2020-05-05 06:45:00', '2020-05-05 07:15:00', 30,
'B', '2020-05-05 10:15:00', '2020-05-05 11:15:00', 60,
'C', '2020-05-05 10:00:00', '2020-05-05 12:15:00', 135
) as (Usr, Start_time, End_time, Duration)
)
select Usr, start_time, end_time, (unix_timestamp(end_time)-unix_timestamp(start_time))/60 Duration
from
(
select Usr,
case when i.i=0 then start_time --first record
when i.i=1 then from_unixtime(unix_timestamp(start_time)+shift*60)
else from_unixtime(unix_timestamp(start_time)+shift*60+(i.i-1)*30*60)
end start_time,
case when i.i=num_intervals then end_time --end of range
when i.i=0 then from_unixtime(unix_timestamp(start_time)+shift*60) --first record
else from_unixtime(unix_timestamp(start_time)+shift*60+(i.i)*30*60)
end end_time
from
(--calculate required intervals and shift to 30 min
SELECT Usr,Start_time,End_time,
cast( floor(duration/30) as int) num_intervals,
case when (minute(Start_time) between 1 and 29) then 30-minute(Start_time)
when minute(Start_time) > 30 then 60-minute(Start_time)
else 30
end shift
FROM mytable
)s
lateral view posexplode(split(space(s.num_intervals),' ')) i as i,x
)s
;
结果：
usr start_time end_time duration
A 2020-05-05 06:45:00 2020-05-05 07:00:00 15
A 2020-05-05 07:00:00 2020-05-05 07:15:00 15
B 2020-05-05 10:15:00 2020-05-05 10:30:00 15
B 2020-05-05 10:30:00 2020-05-05 11:00:00 30
B 2020-05-05 11:00:00 2020-05-05 11:15:00 15
C 2020-05-05 10:00:00 2020-05-05 10:30:00 30
C 2020-05-05 10:30:00 2020-05-05 11:00:00 30
C 2020-05-05 11:00:00 2020-05-05 11:30:00 30
C 2020-05-05 11:30:00 2020-05-05 12:00:00 30
C 2020-05-05 12:00:00 2020-05-05 12:15:00 15
也许我测试了并非所有可能的情况。请自行测试和调试。它适用于您的数据示例。