如何在 Hive 中使用最近的值填补到缺失的日期中

我花了几天的时间试图弄清楚如何在 Hive 中使用最近的值填补到缺失的日期中，但没有成功。

原始表目前看起来像下表：

account name,available balance,Date of balance 

Peter,50000,2021-05-24
Peter,50035,2021-05-25
Peter,50035,2021-05-26
Peter,50610,2021-05-28
Peter,51710,2021-06-01
Peter,53028.1,2021-06-02
Peter,53916.1,2021-06-03
Mary,50000,2021-05-24
Mary,50035,2021-05-25
Mary,53028.1,2021-05-30

我需要的是将上面的表格转换为下面的表格：

account name,available balance,Date of balance 

Peter,50000,2021-05-24
Peter,50035,2021-05-25
Peter,50035,2021-05-26
Peter,50035,2021-05-27
Peter,50610,2021-05-28
Peter,50610,2021-05-29
Peter,50610,2021-05-30
Mary,50000,2021-05-24
Mary,50035,2021-05-25
Mary,50035,2021-05-26
Mary,50035,2021-05-27
Mary,50035,2021-05-28
Mary,50035,2021-05-29
Mary,53028.1,2021-05-30

想不出来解决办法，不可能！绝对不可能！！！ 直接上方案吧:

使用 Lead() 函数获取下一个日期，计算天数差异，获取长度为天数差异的空格字符串，拆分，使用posexplode 生成行，使用位置添加到日期以获取缺失日期

hive sql实现:

with mytable as (--Demo dataset, use your table instead of this
select stack(10, --number of tuples
Peter,float(50000),2021-05-24,
Peter,float(50035),2021-05-25,
Peter,float(50035),2021-05-26,
Peter,float(50610),2021-05-28,
Peter,float(51710),2021-06-01,
Peter,float(53028.1),2021-06-02,
Peter,float(53916.1),2021-06-03,
Mary,float(50000),2021-05-24,
Mary,float(50035),2021-05-25,
Mary,float(53028.1),2021-05-30
) as (account_name,available_balance,Date_of_balance)
) --use your table instead of this CTE

select  account_name, available_balance, date_add(Date_of_balance,e.i) as Date_of_balance
from
( --Get next_date to generate date range
select account_name,available_balance,Date_of_balance,
       lead(Date_of_balance,1, Date_of_balance) over (partition by account_name order by Date_of_balance) next_date    
  from mytable d  --use your table
) s lateral view outer posexplode(split(space(datediff(next_date,Date_of_balance)-1),)) e as i,x --generate rows
order by account_name desc, Date_of_balance --this is to have order of rows like in your Converted Table

结果：

account_name    available_balance   date_of_balance 
Peter           50000                2021-05-24
Peter           50035                2021-05-25
Peter           50035                2021-05-26
Peter           50035                2021-05-27
Peter           50610                2021-05-28
Peter           50610                2021-05-29
Peter           50610                2021-05-30
Peter           50610                2021-05-31
Peter           51710                2021-06-01
Peter           53028.1              2021-06-02
Peter           53916.1              2021-06-03
Mary            50000                2021-05-24
Mary            50035                2021-05-25
Mary            50035                2021-05-26
Mary            50035                2021-05-27
Mary            50035                2021-05-28
Mary            50035                2021-05-29
Mary            53028.1              2021-05-30