在实际业务中,有一个需求中有如下要求,对多个字段中出现的空值补上一次非空的值,主要用来默认市场数据平稳,其中可能出现连续多个空值或者多次间隔的空值,LAG函数只能解决一个null的补数问题,不能解决多个连续的null值补数,在网上有使用自定义udf的方法,但是使用并不方便,不利于日常的开发,现推荐一种使用hql代码的方式直接实现上述需求,简洁方便,高效代码如下:
测试数据:
测试代码如下:
select
t1.data
,t1.city
,t2.val_01
,t3.val_02
,t4.val_03
from (
select
data
,city
,val_01
,val_02
,val_03
,row_number() over(distribute by city,data_rank-val_01_rank sort by data) as rank_01
,row_number() over(distribute by city,data_rank-val_02_rank sort by data) as rank_02
,row_number() over(distribute by city,data_rank-val_03_rank sort by data) as rank_03
from (
select
data
,city
,val_01
,val_02
,val_03
,row_number() over(distribute by city sort by data) as data_rank
,row_number() over(distribute by city sort by if(val_01 is null,0,1),data) as val_01_rank
,row_number() over(distribute by city sort by if(val_02 is null,0,1),data) as val_02_rank
,row_number() over(distribute by city sort by if(val_03 is null,0,1),data) as val_03_rank
from app_test
) t
) t1
left join app_test t2 --关联第一次为val_01字段的null值补数
on t1.city=t2.city and if(t1.val_01 is null,t1.data-t1.rank_01,t1.data)=t2.data
left join app_test t3 --关联第二次为val_02字段的null值补数
on t1.city=t3.city and if(t1.val_02 is null,t1.data-t1.rank_02,t1.data)=t3.data
left join app_test t4 --关联第三次为val_03字段的null值补数
on t1.city=t4.city and if(t1.val_03 is null,t1.data-t1.rank_03,t1.data)=t4.data
;
测试结果如下:
可以看到按照时间排序的第一条为null时,是无法进行补数的,因为已经是第一条了,只要上次时间有值,那么后面的时间就一定能补上值!!!
注意:此方法一般适用于某些天没有数据,把没有数据的补上上一天的有的数据值,如果上述天不是连续的话,就会造成补数不正确,所以可以先把每一天的时间都补上然后再对其进行补上一天的值。