Hive SQL间断日期补数

Hive SQL间断日期补数

0.业务场景

场景：用户间断消费，流水中无消费无记录，对用户每天余额进行补数。

1.补充数据代码

-- 补充间断日期中的数据
with data_test as (
    select stack(10,
		'Jack',double(5000),'2022-09-24',
		'Jack',double(5035),'2022-09-25',
		'Jack',double(5035),'2022-09-26',
		'Jack',double(5610),'2022-09-28',
		'Jack',double(5710),'2022-10-01',
		'Jack',double(5028),'2022-10-02',
		'Mary',double(5916),'2022-09-24',
		'Mary',double(5000),'2022-09-25',
		'Mary',double(5035),'2022-09-26',
		'Mary',double(5028),'2022-09-28'
    ) as (user_name,user_balance,inc_date)
)
select  
	user_name
    ,user_balance
    ,date_add(inc_date,d.idx) as inc_date
from( -- 获取间断时间差
	select b.*
		-- 连续日期差为1，间断日期差减1
		,datediff(next_date_new,inc_date)-1 as date_range 
		-- 根据间断日期长度用空格占位，切分成数组
		,split(space(datediff(next_date_new,inc_date) - 1),'') as to_explode 
	from (
		select a.*
			,case 
				when inc_date=next_date and last_day(next_date)<'2022-10-02' then last_day(next_date)
				else next_date
			end as next_date_new -- 补充至月底或当前日期
		from(
			select 
				user_name
				,user_balance
				,inc_date
				,lead(inc_date,1, inc_date) over (partition by user_name order by inc_date) as next_date    
			from data_test
		)a  
	)b
)c 
lateral view outer posexplode(to_explode) d as idx,val
order by user_name, inc_date asc
;

2.代码分析

1）使用lead函数获取下条数据日期;

2）进行计算两条日期之间的差值；

3）基于日期差使用space占位，使用split进行切分为array数组[“”,“”,“”]；

4）使用posexplode()函数炸裂数组、并获取索引，基于索引补充缺失日期。

3.结果

补数间隔数据：

补数至月底：