学习群里的 hive sql 问题

上周末在学习群里有小伙伴发了一个sql 应用，原始记录是这样的

 

他 希望把数据生成下面这样的

 

 如果是你，你会怎么做呢？？？

  有同学说要用 笛卡尔积、也有说用 自关联，其实、这个sql可以简单点

   

with temp as (
select '2022-12-20' as date_time,'供应商A' as supper,'10' as value
union all
select '2022-12-25' as dete_time,'供应商A' as supper,'20' as value
union all
select '2022-12-29' as date_time,'供应商A' as supper,'30' as value
union all
select '2022-12-18' as date_time,'供应商B' as supper,'20' as value
union all
select '2023-01-03' as date_time,'供应商B' as supper,'30' as value
)

select
temp3.supper,temp3.date_time,date_add(date_time,ids2) full_time,temp3.sum_value
from
(select
temp2.supper,temp2.date_time,lead(date_time,1,date_add(date_time,1)) over(partition by supper order by date_time) next_date_time ,temp2.sum_value
from

(select date_time,supper, sum(value) sum_value from temp group by date_time,supper)

temp2
) temp3
lateral view outer posexplode (split(space(datediff(next_date_time,date_time)-1),'')) temp4 as ids2, ids