[SQL]hive sql数据中间的缺少的值进行填充数据_hive缺失值填充

1.问题

2.建表、添加数据，原表和一张全部的date表

test0112是 1 中的第一张表

create table test0112 (
dateA string,
fromA string,
toA string,
rateA double
) row format delimited fields terminated by '\t';
1
2
3
4
5
6

test0112_all是 1 中的第二张表

create table test0112_all(
dateA string,
fromA string,
toA string,
rateA double
) row format delimited fields terminated by '\t';
1
2
3
4
5
6

注：建表导初始数的时候主义是低版本还是高版本
低版本是不能指定列名， 默认是全部列，直接 values()，指定列名会报错。
高版本可以指定列名，不会报错。

3.两张表 union all，得到一张全量表，最终处理逻辑只用这张表

创建结果表并插入数据

create table test0112_result(
dateA string,
fromA string,
toA string,
rateA double
) row format delimited fields terminated by '\t';

insert into test0112_result
select 
test0112_all.dateA as dateA,
test0112.fromA as fromA,
test0112.toA as toA,
test0112.rateA as rateA
from test0112_all 
left join   
test0112 
on test0112_all.dateA=test0112.dateA
order by dateA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4.主要逻辑

4.1 使用窗口函数rank进行排序；利用toA是否为 ‘’ 进行标记并复制0或1（flag），目的是逐行累加flag标记，和值sumValue会是1,2，3…

select 
row_number()over() as rn,
case when rateA =='' then 0 else 1 end as flag,
dateA,
fromA,
toA,
rateA
from test0112_result
1
2
3
4
5
6
7
8

select 
sum(flag)over(order by rn) as sumValue,
dateA,
fromA,
toA,
rateA
from 
(
select 
row_number()over() as rn,
case when toA is NULL then 0 else 1 end as flag,
dateA,
fromA,
toA,
rateA
from test0112_result
) t
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

ps：4.1中日期忘记排序了，但是不影响整体思路就是结合两个窗口函数 sum和 rownum，加上一个关于有值空/值的复制1/0

4.2 可见sumValue是顺序的1，2,3… 这样将最开始的test0112表中的数据提出来标记成sumFlag，排序成1……n

select 
row_number()over() as sumFlag,
dateA,
fromA,
toA,
rateA  
from test0112
1
2
3
4
5
6
7

4.3 其实就这一条sql是主要的：将 4.1 和 4.2的结果进行join

select
t1.dateA,
t2.fromA,
t2.toA,
t2.rateA
from
(
select 
sum(flag)over(order by rn) as sumValue,
dateA,
fromA,
toA,
rateA
from 
(
select 
row_number()over() as rn,
case when toA is NULL then 0 else 1 end as flag,
dateA,
fromA,
toA,
rateA
from test0112_result
)t
)t1
inner join
(
select 
row_number()over() as sumFlag,
dateA,
fromA,
toA,
rateA  
from test0112_result
where rateA is not null
) as t2
on t1.sumValue=t2.sumFlag
order by t1.dateA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

其余的Oder by 的优化需要的自己研究吧，大致思路是这样解决的。。。