Hive常用函数

1.nvl函数

hive中的nvl函数为判断是否为空值，和oracle判断空值使用的函数一致。nvl叫做空值转换函数。
NVL函数的格式如下：NVL(expr1,expr2)
在mysql和sqlsever中分别使用的是nullif和ifnull
mysql-nullif(expr1, expr2)，sqlserver-ifnull(expr1, expr2)。
备注：
1、如果expr1为NULL，返回值为 expr2，否则返回expr1。
2、适用于数字型、字符型和日期型，但是 expr1和expr2的数据类型必须为同类型。

 nvl(p1.index0,0)
,nvl(p2.index_min,0)

2.hive中case when的两种使用方法

如下两种的表达效果是一样

方法一：

select
tm ,
count(tm) as times
from
(
select
case
when single_times = '1' then 'one_time'
when single_times = '2' then 'two_time'
when single_times = '3' then 'three_time'
when single_times = '4' then 'four_time'
when single_times = '5' then 'five_time'
else 'more_five' end as tm
from
(select count(userid) single_times from test where dt = '2020-10-12' group by userid ) t
) t
group by tm ；

方法二：

select
tm ,
count(tm) as times
from
(
select
case single_times
when '1' then 'one_time'
when '2' then 'two_time'
when '3' then 'three_time'
when '4' then 'four_time'
when '5' then 'five_time'
else 'more_five' end as tm
from
(select count(userid) single_times from test where dt = '2020-10-12' group by userid ) t
) t
group by tm ；

3.concat_ws函数

4.row_number() over()

语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
注意： 在使用 row_number() over()函数的时候，over()里面的分组以及排序的执行晚于 where、group by、order by 的执行。

按照他的例子进行了实现： 新建Hive表tmp_learning_mary如图

drop table if exists tmp_learning_mary;
create table if not exists tmp_learning_mary(
id varchar(10),
name varchar(10) ,
age varchar(10),
salary int
);
insert into tmp_learning_mary(id,name,age,salary) values(1,’a’,10,8000);
insert into tmp_learning_mary(id,name,age,salary) values(1,’a2’,11,6500);
insert into tmp_learning_mary(id,name,age,salary) values(2,’b’,12,13000);
insert into tmp_learning_mary(id,name,age,salary) values(2,’b2’,13,4500);
insert into tmp_learning_mary(id,name,age,salary) values(3,’c’,14,3000);
insert into tmp_learning_mary(id,name,age,salary) values(3,’c2’,15,20000);
insert into tmp_learning_mary(id,name,age,salary) values(4,’d’,16,30000);
insert into tmp_learning_mary(id,name,age,salary) values(5,’d2’,17,1800);

select * from tmp_learning_mary;

1.按照id分组，不排序
select *, row_number()over(partition by id) from tmp_learning_mary;

结果

最后一列中，每个分组内的排序是随机的

2.按照salary进行降序排序，不分组（排序列命名为ranking）
select *, row_number()over(order by salary desc) ranking from tmp_learning_mary

结果为：

3.按照salary降序排序，同时根据id进行分组
select *, row_number()over(partition by id order by salary desc) ranking
from tmp_learning_mary;

结果为：

4.在2的基础上，找出每一组中序号为1的数据
select from(select , row_number()over(partition by id order by salary desc) ranking
from tmp_learning_mary) t where t.ranking < 2;

这里注意子查询语句中的表格一定要加上一个命名，我原来没有命名为t，导致报错：

错误信息为无法识别where那一块的语句，因此我把子查询语句的表命名为t，where语句写为where t.ranking < 2后可以正常运行。

还可以利用with table as的写法，具体可参考博文：Hive（二）：with as用法

with t as (select , row_number()over(partition by id order by salary desc) ranking
from tmp_learning_mary)
select from t where ranking = 1;

最后的结果为：

5.找出年龄在13岁到16岁数据，按salary排序
select *, row_number()over(order by salary desc) ranking
from tmp_learning_mary where age between ‘13’ and ‘16’;

结果：

从结果中的ranking可以看出，是先进行了where语句的筛选，再进行了排序，即over()里面的分组排序是在where之后的。

6.按id进行分组，每组随机排序，找到每组序号为1的数据
随机排序的可以用order by rand()表示随机排序，这种需求可以用在对各个分组的随机采样上

select from (select ,row_number()over(partition by id order by rand()) ranking
from tmp_learning_mary) t where t.ranking = 1;

结果：

5.hive的集合运算

1.表的加法–UNION

2.包含重复行的集合运算 UNION ALL

3.差集,补集与表的减法

set hive.mapred.mode=nonstrict;
set hive.strict.checks.cartesian.product = false; ###设置非严格模式
##求product_1中特有的
 SELECT * 
  FROM hive_4_product_1
 WHERE product_id NOT IN (SELECT product_id FROM hive_4_product_2);

hive不支持表的减法运算符 EXCEPT，但是使用NOT IN, 我们同样可以实现表的减法.

例子

使用NOT进行集合的减法运算, 求出product_1表中, 售价高于2000,但利润低于30%的商品

SELECT * 
  FROM hive_4_product_1
 WHERE sale_price > 2000 
   AND 
   product_id NOT IN 
   (SELECT product_id  FROM hive_4_product_1 WHERE sale_price<1.3*purchase_price)

4.对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合. 对称差也是个非常基础的运算.也就是求不是两个表各自独有的元素。可以看作两个差集的并集就是对称差。

求两张表中独有的物品,预测一下执行的时间

SELECT * 
  FROM hive_4_product_1
 WHERE product_id NOT IN (SELECT product_id FROM hive_4_product_2)
UNION
SELECT * 
  FROM hive_4_product_2
 WHERE product_id NOT IN (SELECT product_id FROM hive_4_product_1)

5.并集

俩张表所共有的，可以使用in关键字来实现，在第一张表选择的时候，判断是否在第二张表中出现就可以。

求product_1与product_2特有的产品

 SELECT * 
  FROM hive_4_product_1
 WHERE product_id IN (SELECT product_id FROM hive_4_product_2)