hive的space和posexplode函数

小李投了A和B两种类型的股票，收益为天,需求：计算小李截止到每日的累计收益

原数据样式：

投资类型 开始时间 结束时间 每日收益

A 2023-01-01 2023-01-10 10

B 2023-01-04 2023-01-06 20

目标样式：

投资类型 日期 累计收益

space函数：例：产生长度为5的空字符串

将投资类型为A的日期炸裂：

将多个投资类型，即投资类型为A、B的日期炸裂开来 ：

with t1 as(
select 'A' as type,'2023-01-01' as start_date,'2023-01-10' as end_date,10 as money
union all 
select 'B','2023-01-04','2023-01-06',20
)
select type,start_date,date_add(start_date,pos),money
from t1
lateral view posexplode(split(space(datediff(cast(end_date as date),cast(start_date as date))),'')) tmp as pos,val

 然后按照日期groupbuy，金额累加：

with t1 as(
select 'A' as type,'2023-01-01' as start_date,'2023-01-10' as end_date,10 as money
union all 
select 'B','2023-01-04','2023-01-06',20
),
t2 as(
select type,start_date,date_add(start_date,pos) continue_date,money
from t1
lateral view posexplode(split(space(datediff(cast(end_date as date),cast(start_date as date))),'')) tmp as pos,val--不用cast as date也可以，不会报错
),
t3 as(
select concat_ws(',',sort_array(collect_set(type))) type,continue_date,sum(money) money --sort_array:让collect_set里的元素按照字典排序
from t2
group by continue_date
)
select type,continue_date,sum(t3.money)over(order by continue_date) money
from t3