Impala /Hive填充类似于locf的缺失值(上次观察结转)

ny6fqffe  于 2021-06-27  发布在  Hive
关注(0)|答案(1)|浏览(354)

我有一个时间序列数据在 Impala ,在这种格式。
只有在发生更改时才创建一条记录,更新的值表示新数据。

---------------------------------------
| Product | Year | Week | UpdatedValue |
---------------------------------------
|A        | 2017 | 1    | 5            |
|A        | 2017 | 5    | 10           |
|A        | 2017 | 20   | 80           |
|B        | 2017 | 8    | 90           |
|...      | ...  | ...  | ...          |
---------------------------------------

假设我们的时间窗口是2017年全年从第一周到第52周。上面的数据表明,产品a第一周的值变为5,第五周的值变为10,第二十周的值变为80。我想用上一次观察的结转逻辑来填充缺失值,如果数据不是从第一周开始的,那么也用下一次出现的情况来填充前导的缺失值。
这应该是理想的输出。

---------------------------------------
| Product | Year | Week | UpdatedValue |
---------------------------------------
|A        | 2017 | 1    | 5            |
|A        | 2017 | .    | 5            |
|A        | 2017 | 4    | 5            |
|A        | 2017 | 5    | 10           |
|A        | 2017 | 6    | 10           |
|A        | 2017 | .    | 10           |
|A        | 2017 | 20   | 80           |
|A        | 2017 | .    | 80           |
|A        | 2017 | 52   | 80           |
|B        | 2017 | 1    | 90           |
|B        | 2017 | .    | 90           |
|B        | 2017 | 8    | 90           |
|B        | 2017 | .    | 90           |
|B        | 2017 | 52   | 90           |
|...      | ...  | ...  | ...          |
---------------------------------------

有没有一个可行的解决方案Hive/ Impala 你可以确定使用他们的高级分析功能,如果这是比较容易的?但是,如果有一个通用的sql解决方案,那就太好了。

iqjalb3h

iqjalb3h1#

步骤1:创建一个带有数字的表。

create table if not exists tblNumbers
location 'hdfs_location' as 
select 1 as num
union all 
select 2 as num
....

第二步:交叉联接唯一的产品,用数字表示年份,然后左键联接原始表,为产品生成缺少的行。然后使用运行求和逻辑生成组,以对连续缺少值的行进行分组,然后可以使用 max 从最后找到的值生成缺少行的值。

select product,year,week,max(val) over(partition by product,year,grp) as new_val
from (select py.product,py.year,n.week,t.val
      ,sum(case when t.val is not null then 1 else 0 end) 
       over(partition by py.product,py.year order by n.week) as grp
      from tblNumbers n
      cross join (select distinct product,year from tbl) py
      left join tbl t on n.week = t.week and py.product = t.product and py.year = t.year
     ) t

相关问题