hive sql之将一行中的时间范围转成多行

 一、生成hive 临时表其中date_time 包含其实日期和结束日期。

with test1 as (
  select 'A班' as class,'2023-03-01,2023-03-14' as date_time,'100,99,89' as score
  union all 
  select 'B班' as class,'2023-02-01,2023-02-09' as date_time,'30,40' as acore
  union all 
  select 'C班' as class,'2023-01-01,2023-01-10' as date_time,'10,20' as acore
 ) 

二、hive sql 开发，其中主要应用hive 关键算子repeat 将特定开始时间结束时间生成重复的几个连续字符。lateral view posexplode（split(repeat_num,'&')）t as row_index,
new_date将特定字符打成多行，并且记录打成多行其对应的行号。

date_add 在开始时间加上对应行号生成日期。

具体逻辑为：

select 
class,
repeat_num,
start_dt,
end_dt,
date_add(start_dt,row_index) as new_dt from(
select class,
       start_dt,
       end_dt,
       repeat('&',datediff(end_dt,start_dt)) as repeat_num 
       from(
       select class,
       split(date_time,',')[0] as start_dt,
       split(date_time, ',')[1] as end_dt
       from test1
) t
) t lateral view posexplode(split(repeat_num,'&')) t as row_index,
new_date

结果为：

class	repeat_num	start_dt	end_dt	new_dt
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-01
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-02
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-03
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-04
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-05
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-06
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-07
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-08
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-09
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-10
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-11
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-12
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-13
A班	&&&&&&&&&&&&&	2023-03-01	2023-03-14	2023-03-14
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-01
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-02
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-03
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-04
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-05
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-06
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-07
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-08
B班	&&&&&&&&	2023-02-01	2023-02-09	2023-02-09
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-01
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-02
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-03
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-04
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-05
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-06
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-07
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-08
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-09
C班	&&&&&&&&&	2023-01-01	2023-01-10	2023-01-10

lateral view posexplode 参考：函数：explode 与posexplode - 简书