Hive SQL 查询以使用日期范围之间的最近值填充表中缺失的日期值

对于下面的示例，如果我使用下面的相同数据，并且我希望 Mary 和 Peter 帐户在同一日期范围内，我将如何修改 hive sql 查询来执行此操作？例如，将日期范围设置在“2021-05-24”和“2021-06-03”之间，并填充此期间的所有余额。如果我们以 Mary 为例，我还希望看到 Mary 可用余额 ‘53028.1’ 向前填充到 ‘2021-06-03’ 并且如果 Mary 在 ‘2021-05-24’ 上没有值’ 将其填充 ‘50000’ 的余额。

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

实现方式:

另外计算整个数据集的最小和最大日期以确定所需的日期范围，还计算每个帐户的最小日期以检查最小日期是否需要修复。然后为两个日期添加额外的计算步骤：检查它是否是边界日期，如果它们不符合要求，则相应地分配最小值和最大值。

在此示例中，Peter 的开始日期是 2021-05-24，而 Mary 的开始日期是 2021-05-23，因此，范围被扩展并为 Peter 生成了 2021-05-23 记录。对于 Mary 的最后日期是 2021-05-30，在范围末尾生成的缺失行。

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', -------------end date greater than Mary
'Mary',float(50000),'2021-05-23', ----------------start date Less than Peter
'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
(
	select account_name, available_balance, 
	        case when min_date < min_date_account and Date_of_balance = min_date_account then min_date 
	             else Date_of_balance 
	         end Date_of_balance,
	        
	        case when (next_date is null) and (Date_of_balance = max_date) then Date_of_balance 
	             when (Date_of_balance < max_date) then nvl(next_date,date_add(max_date,1)) 
	         end as next_date
	from
	( 	--Get next_date to generate date range
		select account_name,available_balance,Date_of_balance,
		       lead(Date_of_balance,1) over (partition by account_name order by Date_of_balance) next_date,
		       max(Date_of_balance) over() max_date, --total min and max dates all accounts should align
		       min(Date_of_balance) over() min_date, 
		       min(Date_of_balance) over(partition by account_name) min_date_account
		  from mytable d  --use your table
	) s 
) 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-23
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-23
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
Mary              53028.1            2021-05-31
Mary              53028.1            2021-06-01
Mary              53028.1            2021-06-02
Mary              53028.1            2021-06-03

注意 lead 函数的计算方式也不同，它没有默认值，NULL 表示可用的结束日期
space(int n) : 返回n个空格