hive 分组后使用近期日期数据进行缺失值填充

1、 问题描述

有产品重量表,其数据如图所示,一共为四列,分别是日期inc_day,重量weights_value,产品类型product_name,与产品等级product_level.可以看到有些日期下的重量是缺失的,此时我想要拿近期同产品类型跟等级下的重量数据进行填充,也就是第二幅图这样

目标

2、 解决方法

首先对缺失与非缺失进行标记

select
    *
    ,case when weights_value = '' or weights_value is null then 0 else 1 end flag
from tmp_dm_predict.yang_product_weight

然后对标记分组求和得到👇结果,可以看到缺失的地方不会进行累加,无缺失的会累加.

sum(flag) over(partition by product_name,product_level order by inc_day) as cnt_sort

最后对求和,以及产品类型跟等级进行分组按日期排序取重量最大值,这样缺失的部分前面若有非缺失值,取最值,非缺失值则会被取到,否则继续为空,作为缺失处的值;而且不缺失的值因为会累积和,最大值只会取到自己.

max(weights_value) over(partition by cnt_sort,product_name,product_level order by inc_day) as weights_value_new

最终完整的代码如下:

select inc_day	
,weights_value	
,product_name	
,product_level
,max(weights_value) over(partition by cnt_sort,product_name,product_level order by inc_day) as weights_value_new
from
(
select
*

,sum(flag) over(partition by product_name,product_level order by inc_day) as cnt_sort
from
    (
    select
    *
    ,case when weights_value = '' or weights_value is null then 0 else 1 end flag
    from
         tmp_dm_predict.yang_product_weight
    )a2
)a3
order by product_name,product_level,inc_day ;

若要保持原表结构,可以用新重量列覆盖原重量列.