Hive中替换空null，用上面非空的null替换

Hive中替换空null，用上面非空的null替换 一、问题

有两种情况，一种是中文字段的空null的替换，一种是int类型之间的替换。

（1）中文字段的空null替换，要补全一个表某列的空值，策略是按某个规则排序后，取上一个非空的值替代。

二、思路

根据上一个非空的值，进行到下一个非空的值，取出两个之间的数据，经过已经写好的函数，进行炸裂。取出对应的ecent值，用两个表left join一下，取两个表想要的数值

1、对数据进行分区排序，取rank值，获取“”值的长度，取cnt值，获取分区的总数，为后面的lead的最后一个空值做准备。创建临时表。  

2、根据上面创造的临时表，取出开始的长度和结束长度，结束的长度是取lead开始长度的值。判断出不为空值，之间的差距。用explode 取值。

3、两个表join，取出想要的两个值。

数据值：

1       2018-12-02 11:00:29     A
1       2018-12-02 11:00:30     ""
1       2018-12-02 11:00:31     B
1       2018-12-02 11:00:32     ""
1       2018-12-02 11:00:33     ""
2       2018-12-02 11:00:40     B
2       2018-12-02 11:00:41     ""
2       2018-12-02 11:00:42     C
2       2018-12-02 11:00:44     ""

建表语句：

--创建临时函数
add jar jar路径;
create temporary function 函数名 as 'com.hive.udf.Range';

drop table lianxi.text_uid;

create table lianxi.text_uid
(
     uid    int,
    `time` string,
    event  string
)row format delimited fields terminated by 't';

select * from lianxi.text_uid;

load data local inpath '文件路径' into table 表名;




--临时表名，对数据进行排序
with all_data as (
select a.*,
       row_number() over (partition by uid order by `time` asc) as rank,
       count(1) over(partition by uid) as cut
from text_uid as a
)
select a.uid,a.time,b.event
from all_data as a
left join (
select uid,event,rank
from (
         select uid,
                event,
                rank as start_rank,
           --clalesce 返回数值中的第一个非空值，如果所有值都为null，那么返回null
           --获取每个用户下一个不为空的event所在行数，最后一行获取到rank为null，故用cnt+1代替
                cast(coalesce(
                        lead(rank) over (partition by uid order by rank asc), cut + 1) as int) as next_rank
         from all_data
         -- 过滤掉不为空的数值
         where event <> '""'
     ) a
     lateral view explode(get_range(start_rank,next_rank)) b as rank) b
-- 取出不为空的数值是把next——rank的区间值取出来。然后用炸裂炸开。 这样就是AA   BBBB   CCC 这样的类似
-- 最后两个表join取出两个表想要的值。
on a.uid=b.uid and a.rank=b.rank;



输出结果为：

1,2018-12-02 11:00:29,A
1,2018-12-02 11:00:30,A
1,2018-12-02 11:00:31,B
1,2018-12-02 11:00:32,B
1,2018-12-02 11:00:33,B
2,2018-12-02 11:00:40,B
2,2018-12-02 11:00:41,B
2,2018-12-02 11:00:42,C
2,2018-12-02 11:00:44,C

二、int类型的数值进行替换

数据：

00:00,1,10.00
01:00,1,    
02:00,1,20.00
03:00,1,    
04:00,1,    
05:00,1,70.00

思路： 对于取值，我们需要对数据进行分区排序，然后取排序好的值。

drop table kong;

create table kong
(
    create_time string,
    user_id     int,
    money_total double
) row format delimited fields terminated by ',';

-- 清楚表中数据
truncate table kong;

select * from kong;

load data local inpath '/home/xiaofan/wenjian/intshuzhi.txt' into table kong;

--  因为在hsal中完成一些业务需求的时候，我们经常用窗口函数，某些场景需要控制窗口的函数。
-- row between可以根据自己的需求任意的控制窗口函数的范围

--over(): 指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随这行的变化而变化。
--CURRENT ROW:当前行
--n PRECEDING: 往前n行数据
--n FOLLOWING: 往后n行数据
--UNBOUNDED: 起点，UNBOUNDED PRECEDING  表示从前面的行，UNBOUNDED FOLLOWING 表示从后面的行。
-- LAG(col,n)：往前第n行数据
-- LEAD(col,n)：往后第n行数据
-- NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。


-- 取当前行的往前n行的最大值。
select create_time,user_id,
       nvl(max(money_total) over(partition by user_id order by create_time
           rows between unbounded preceding and current row),0)
       from kong;



输出结果为：

00:00,1,10
01:00,1,10
02:00,1,20
03:00,1,20
04:00,1,20
05:00,1,70