（12）Hive的空字段赋值,casewhen,⾏列转换窗⼝函数,rankwaiting ⽂章⽬录
空字段赋值
函数说明:
NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
查询：如果员⼯的comm为NULL，则⽤-1代替
hive (default)>select comm,nvl(comm, -1) from emp;
OK
comm _c1
NULL -1.0
300.0 300.0
500.0 500.0
NULL -1.0
1400.0 1400.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
0.0 0.0
NULL -1.0
NULL -1.0
NULL -1.0
NULL -1.0
查询：如果员⼯的comm为NULL，则⽤领导id代替
hive (default)>select comm, nvl(comm,mgr) from emp;
OK
comm _c1
NULL 7902.0
300.0 300.0
500.0 500.0
NULL 7839.0
1400.0 1400.0
NULL 7839.0
NULL 7839.0
NULL 7566.0
NULL NULL
0.0 0.0
NULL 7788.0
CASE WHEN
求出不同部门男⼥各多少⼈。结果如下：
A 2 1
B 1 2
创建本地⽂件
悟空 A 男
⼤海 A 男
宋宋 B 男
凤 A ⼥
婷 B ⼥
婷婷 B ⼥
创建hive表并导⼊数据
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/' into table emp_sex;
按需求查询数据
select
dept_id,
sum(case sex when '男'then 1 else 0 end ) mal_count,
sum(case sex when '⼥'then 1 else 0 end ) female_count
from emp_sex
group by
dept_id;
rank函数的用法⾏转列
相关函数说明
CONCAT(string A/col, string B/col…)：
返回输⼊字符串连接后的结果，⽀持任意个输⼊字符串;
CONCAT_WS(separator, str1, str2,…)：
它是⼀个特殊形式的 CONCAT()。第⼀个参数剩余参数间的分隔符。分隔符可以是与剩余参数⼀样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
COLLECT_SET(col)：
函数只接受基本数据类型，它的主要作⽤是将某字段的值进⾏去重汇总，产⽣array类型字段。
样例数据
孙悟空 ⽩⽺座 A
⼤海 射⼿座 A
宋宋 ⽩⽺座 B
猪⼋戒 ⽩⽺座 A
凤 射⼿座 A
需求：把星座和⾎型⼀样的⼈归类到⼀起。结果如下：
射⼿座,A ⼤海|凤
⽩⽺座,A 孙悟空|猪⼋戒
⽩⽺座,B 宋宋
导⼊数据
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/" into table person_info
1.查询名称，拼接星座和⾎型
select
name,
concat(constellation, ",", blood_type) base
from
person_info;
结果
⼤海射⼿座,A
宋宋⽩⽺座,B
猪⼋戒⽩⽺座,A
凤射⼿座,A
查询
select
t1.base,
concat_ws('|', collect_set(t1.name)) name
from
(select
name,
concat(constellation, ",", blood_type) base
from
person_info) t1
group by
t1.base;
列转⾏
函数说明:
EXPLODE(col)
将hive⼀列中复杂的array或者map结构拆分成多⾏。
LATERAL VIEW
⽤法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：
⽤于和split, explode等UDTF⼀起使⽤，它能够将⼀列数据拆成多⾏数据，在此基础上可以对拆分后的数据进⾏聚合。样例数据
《疑犯追踪》 悬疑，动作，科幻，剧情
《Lie to me》 悬疑，警匪，动作,⼼理，剧情
《战狼2》 战争，动作，灾难
需求
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 ⼼理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
create table movie_info(
movie string,
category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";
导⼊数据
load data local inpath "/" into table movie_info;
查询
hive (default)>select movie,category_name from movie_info lateral view explode(category) table_tmp as category_name;
结果
movie category_name
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 ⼼理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
窗⼝函数
OVER()：指定分析函数⼯作的数据窗⼝⼤⼩，这个数据窗⼝⼤⼩可能会随着⾏的变⽽变化。
CURRENT ROW：当前⾏
n PRECEDING：往前n⾏数据
n FOLLOWING：往后n⾏数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表⽰从前⾯的起点， UNBOUNDED FOLLOWING表⽰到后⾯的终点
LAG(col,n,default_val)：往前第n⾏数据
LEAD(col,n, default_val)：往后第n⾏数据
NTILE(n)：把有序分区中的⾏分发到指定数据的组中，各个组有编号，编号从1开始，对于每⼀⾏，NTILE返回此⾏所属的组的编号。注意：n必须为int类型。
样例数据
name , orderDate,cost
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
（1）查询在2017年4⽉份购买过的顾客及总⼈数
（2）查询顾客的购买明细及⽉购买总额
（3）上述的场景, 将每个顾客的cost按照⽇期进⾏累加
（4）查询每个顾客上次的购买时间
（5）查询前20%时间的订单信息
创建表
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
加载数据
hive (default)> load data local inpath '/' into table business;
（1）查询在2017年4⽉份购买过的顾客及总⼈数
select name,count(*) over ()
from business
where substring(orderdate,1,7)='2017-04' --截取⽇期为2017-04
group by name;
结果
name count_window_0
mart 2
jack 2
（2）查询顾客的购买明细及⽉购买总额
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;
结果
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论