Hive常用函数

https://www.iteblog.com/archives/2258.html#3_lt

if

if判断

select if(1=1,“yes”,“no!”); //返回yes

case

语法 :
case [expression]
	when condition1   then   result1 
 	when condition2  then   result2
	else  result 
end 

例子 : 
case a 
	when 1 then "one"
    when 2 then "two"
    else "other"
end
    

concat , concat_ws

连接字符串

concat(string A, string B…) 连接多个字符串，合并为一个字符串，可以接受任意数量的输入字符串

concat_ws(string SEP, string A, string B…) 连接多个字符串，字符串之间以指定的分隔符分开。

collect_set , collect

返回无重复记录，返回的是一个数组 , 可以实现列转行(配合concat)

nvl

空值转换函数

nvl（expr1，expr2）将查询的null值转换为指定值

如果expr1为null , 则返回expr2 , 否则返回expr1

coalesce

和nvl类似(都是处理null值的方法) , 返回第一个非null的值

select coalesce(null,2,3,4); //返回2

explode

将数组/集合转换为单独的多行

select split("a,b,c,d",",");
OK
["a","b","c","d"]]

select explode(split("a,b,c,d",","));
OK
a
b
c
d

lateral view

概念

lateral view 其实就是用来实现类似explode这种UDTF函数联合使用的

lateral view会将UDTF生成的结果放到一个虚拟表中 , 然后这个虚拟表会和输入行的主键来进行join来达到UDTF外的select字段的目的

语法

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)*
fromClause: FROM baseTable (lateralView)*

可以在两个地方用lateral view : 在udtf前面使用 , 在from base table 后面使用

实例

select reneger,reason,test_id
from a lateral view  explode(split("1,2,3",","))  temp as test_id;

lateral view 与 explode等udtf就是天生好搭档 , explode将复杂结构的一行拆成多行 , 再用lateral view 做聚合

Lateral View通常和UDTF一起出现，为了解决UDTF不允许再select字段的问题。
Multiple Lateral View可以实现类似笛卡尔乘积。
Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

instr

instr(string str , string substr )

查找字符串str中子字符串substr出现的位置 , 如果查找失败返回0 , 如果任意一个参数为null , 将返回null , 位置从1开始

select instr("nihao","hao");
OK
3

select instr("nihao","hao22");
OK
0

row_number , rank , dense_rank

转自https://www.cnblogs.com/wujin/p/6051768.html

都是实现分组排序的方法

语法 : ROW_NUMBER() OVER (partition BY COLUMN_A ORDER BY COLUMN_B ASC/DESC) rn

row_number可以替换为rank , dense_rank

数据
id name sal
1   a   10
2   a   12
3   b   13
4   b   12
5   a   14
6   a   15
7   a   13
8   b   11
9   a   16
10  b   17
11  a   14

语句
select id,
name,
sal,
rank()over(partition by name order by sal desc ) rp,
dense_rank() over(partition by name order by sal desc ) drp,
row_number()over(partition by name order by sal desc) rmp
from f_test

结果
10   b    17    1    1    1
3    b    13    2    2    2
4    b    12    3    3    3
8    b    11    4    4    4
9    a    16    1    1    1
6    a    15    2    2    2
11   a    14    3    3    3
5    a    14    3    3    4
7    a    13    5    4    5
2    a    12    6    5    6
1    a    10    7    6    7

ank() 排序相同时会重复，总数不会变

dense_rank()排序相同时会重复，总数会减少

row_number() 会根据顺序计算

ntile

用于将分组数据按照顺序切分成n片，返回当前切片值

select * from (
    select id,
        name,
        sal,
        NTILE(2) over(partition by name order by sal desc ) rn
    from f_test
) t where t.rn=1

烦1!!!