hive中常用的重要函数NVL case when,行转列concat collect_set,列转行explode,时间函数date_format, date_add,next_day等等

介绍生产中经常使用的函数

1. NVL 函数: 给值为NULL的数据赋值
   格式: NVL( value，default_value)
   功能: 如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个 参数都为NULL ，则返回NULL。
   案例:

hive (default)> select comm from emp;
NULL   
300.0   
500.0   
NULL    
1400.0  
NULL    
NULL    
NULL    
NULL    
0.0     
NULL    
NULL    
NULL    
NULL  
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

2. case when 条件判断函数,case 开头end 结尾
   格式:
   CASE [ expression ]
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   …
   WHEN conditionn THEN resultn
   ELSE result
   END
   测试数据

0: jdbc:hive2://hadoop-03:10000> select * from emp_sex;
+---------------+------------------+--------------+--+
| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  |
+---------------+------------------+--------------+--+
| 张三            | A                | 男            |
| 李四            | A                | 男            |
| 王五            | B                | 男            |
| 赵六            | A                | 女            |
| 麻七            | B                | 女            |
| 王八            | B                | 女            |
+---------------+------------------+--------------+--+

如果是男生标记1,不是男生则标记0

0: jdbc:hive2://hadoop-03:10000> select *, case sex when '男'  then 1 else 0 end from emp_sex;
+---------------+------------------+--------------+------+--+
| emp_sex.name  | emp_sex.dept_id  | emp_sex.sex  | _c1  |
+---------------+------------------+--------------+------+--+
|  张三           | A                | 男            | 1    |
| 李四            | A                | 男            | 1    |
| 王五            | B                | 男            | 1    |
| 赵六            | A                | 女            | 0    |
| 麻七            | B                | 女            | 0    |
| 王八            | B                | 女            | 0    |
+---------------+------------------+--------------+------+--+

求出不同部门男女各多少人:

0: jdbc:hive2://hadoop-03:10000> select dept_id ,sum(case sex when '男' then 1 else 0 end ) as male ,sum(case sex when '女' then 1 else 0 end ) as female
from emp_sex
group by dept_id ;
+----------+-------+---------+--+
| dept_id  | male  | female  |
+----------+-------+---------+--+
| A        | 2     | 1       |
| B        | 1     | 2       |
+----------+-------+---------+--+

当然这里的case when 语句可以被if 语句代替,if 太简单,这里就不介绍了

 select dept_id, sum(if(sex='男','1','0')) from emp_sex group by dept_id;

3. 行转列函数
   3.1 函数concat:字符串拼接
   CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果，支持任意个输入字符串
   3.2 concat_ws: 字符串拼接符把字符串拼接
   concat_ws(separator, str1, str2,…)：**它是一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。**分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

写的太拗口了,直接看例子

0: jdbc:hive2://hadoop-03:10000> select concat_ws(".","192","168","33","44") ;
+----------------+--+
|      _c0       |
+----------------+--+
| 192.168.33.44  |
+----------------+--+
1 row selected (0.33 seconds)
0: jdbc:hive2://hadoop-03:10000> select concat(".","192","168","33","44") ;
+--------------+--+
|     _c0      |
+--------------+--+
| .1921683344  |
+--------------+--+

3.3 collect_set
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
把几行变为一个集合

综合案例

数据 vi constellation.txt

 孙悟空	白羊座	A
大海	     射手座	A
宋宋	     白羊座	B
猪八戒     白羊座	A
凤姐	     射手座	A

要求把星座和血型一样的人归类到一起。结果如下：

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋

按需求查询数据

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;

4. 列转行 函数
   EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
   EXPLODE 作用对象是数组/集合,作用是把数组集合炸开
   而且EXPLODE 是表生成函数,函数作用后生成的是表,不是字段

LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
综合案例

数据

select * from t_wc
tom jerry jim       
nancy baron nancy baron
ivy tom jerry allen

要求结果:hive 计算wordCount

+----------+-------+-
| t1.word  | cnts  |
+----------+-------+-
| allen    | 1     |
| baron    | 2     |
| ivy      | 1     |
| jerry    | 2     |
| jim      | 1     |
| nancy    | 2     |
| tom      | 2     |
+----------+-------+--

代码实现

select t1.word,count(word) cnts
from
(select explode(split(text,' ')) word from t_wc)
t1
group by t1.word;

数据
vi movie.txt

《疑犯追踪》	    悬疑,动作,科幻,剧情
《Lie to me》	   悬疑,警匪,动作,心理,剧情
《战狼2》	      战争,动作,灾难

要求

+--------------+----------------+--+
|    movie     | category_name  |
+--------------+----------------+--+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》     | 悬疑             |
| 《Lie to me》     | 警匪             |
| 《Lie to me》     | 动作             |
| 《Lie to me》     | 心理             |
| 《Lie to me》     | 剧情             |
| 《战狼2》          | 战争             |
| 《战狼2》          | 动作             |
| 《战狼2》          | 灾难             |
+--------------+----------------+--+

代码

select
    m.movie,
    t1.category_name
from 
    movie_info m lateral view explode(category) t1 as category_name;

这里注意区分表名和别名

5. 日期处理函数

5.1 date_format函数（根据格式整理日期）

hive (gmall)> select date_format('2019-10-18','yyyy-MM');
2019-10

5.2 date_add函数（加减日期）

hive (gmall)> select date_add('2019-10-18',-1);
2019-10-17
hive (gmall)> select date_add('2019-10-18',1);
2019-10-19 

5.3 next_day函数
（1）取当前天的下一周的周一

hive (gmall)> select next_day('2019-02-12','MO')
2019-02-18

（2）取当前周的周一

hive (gmall)> select date_add(next_day('2019-02-12','MO'),-7);
2019-02-11

5.4 last_day函数（求当月最后一天日期）

hive (gmall)> select last_day('2019-10-18');
2019-10-31  

如果有兴趣看hive 的开窗函数,链接
https://blog.csdn.net/weixin_43548518/article/details/104110733

总结: 所谓行转列 就是把多行数据变为一行数组的过程,例如concat
列转行就是把数组炸开为多行的过程,例如collect_set

补充: 如果想了解hive 开窗函数,以下连接
https://blog.csdn.net/weixin_43548518/article/details/104110733