一、问题
有两种情况,一种是中文字段的空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