hive 中常用的函数

一、hive 中常用的函数

1. 空字段赋值

NVL( value，default_value)：如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。

2. case when then else end

例如：case sex when ‘男’ then 1 else 0 end

3. 行转列

（1）函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数是剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

注意：CONCAT_WS must be “string or array”

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

COLLECT_LIST(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段。

（2）数据：constellation.txt

name	constellation	blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
胡老师	白羊座	B

（3）创建hive表并导入数据

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive/datas/constellation.txt" into table person_info;

（4）把星座和血型一样的人归类到一起

SELECT t1.c_b , CONCAT_WS("|",collect_set(t1.name))
FROM (
SELECT NAME ,CONCAT_WS(',',constellation,blood_type) c_b
FROM person_info
)t1 
GROUP BY t1.c_b

4. 列转行

Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array。

EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。

LATERAL VIEW

用法：lateral view udtf(expression) tableAlias AS columnAlias

解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

5. 窗口函数（开窗函数）

(1) 相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：无边界

​ UNBOUNDED PRECEDING 前无边界，表示从前面的起点，

​ UNBOUNDED FOLLOWING后无边界，表示到后面的终点

LAG(col,n,default_val)：往前第n行数据

L