Hive常用函数_hive coalesce函数用法

一 常见函数

（1）基础函数

1.coalesce (expr1, expr2, …)：遇到非null值返回，如果没有非null值出现返回null

2.nvl(expr1, expr2):  如果expr1为null，则返回expr2，否则返回expr1
1
2
3

（2）字符串相关

1.concat(Str1,Str2,Str3,...) ：拼接字符串；Str1||Str2||...(效果一样)

2.concat_ws(B,'&&&')：表示连接时指定分隔符

3.regexp_replace(Array,'￥','$'):在集合或字符串中找符合条件的替代成新字符

特殊符合 加 \\  regexp_replace(str,'\\[|\\]','')

4.split(Str1,'%'):据指定字符切分字符串

5.instr(Str1,Str2,start,end): 查找Str2在Str1的位置，指定开始和结束位置,返回位置int

6.substr(Str,start,end)：截取字符串。指定开始位置和长度,索引从1开始

7. upper(Str)/ ucase(Str) : 转大写

8. lower(Str)/ lcase(Str) : 转小写

9.trim(Str)  ：去空格

10.regexp '条件1|条件2'：符合条件1或者条件2的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

（3）时间函数

1.datediff(day1,day2)：计算day1和day2间的日期间隔，返回间隔时间int

2.current_date()：返回当前日期

3.unix_timestamp(day,日期格式):据特定的日期格式转成时间戳

4.from_unixtime(timestamp,日期格式):将时间戳转成特定的日期格式

datediff(current_date(),from_unixtime(unix_timestamp(date,'yyyymmdd'),'yyyy-mm-dd'))

5.date_add(day,增加天数)：日期增加N天 date_add(current_date(),-3)
1
2
3
4
5
6
7
8
9
10
11

（4） json相关函数

1.get_json_object(json, '$.key')：解析json的字符串,返回path指定的内容get_json_object('{"name":"zhangsan","age":18}','name')

2. json_tuple(json,k1,k2...):解析json字符串,可指定多个json数据中的key，返回对应的value
1
2
3

select b.name,b.age from tableName a lateral view json_tuple(‘{“name”:“zhangsan”,“age”:18}’,‘name’,‘age’) b as name,age

二 窗口函数

分析函数（如:sum(),max(),row_number()…） + 窗口子句（over函数）

1.row_number() over():分区排序，同个字段值，随机顺序排序
2 rank() over():分区排序，同个字段值，排名相同，会在名次中留下空位
3 dense_rank()  over():分区排序，同个字段值，排名相同，会在名次中不留下空位
row_number() over(partition by ccid order by  dt desc )
4 sum() over(数据源):指定行范围分区排序
1
2
3
4
5

数据源 例如：

1）sum(pv) over(partition by 分区字段 order by 排序字段) as pv1, -- 默认为从起点到当前行
2）sum(pv) over(partition by 分区字段 order by 排序字段 rows between unbounded preceding and current row) as pv2, --从起点到当前行，结果同pv1 
3）sum(pv) over(partition by 分区字段 order by 排序字段 rows between 3 preceding and current row) as pv3,   --当前行+往前3行
4）sum(pv) over(partition by 分区字段 order by 排序字段 rows between 3 preceding and 1 following) as pv4,    --当前行+往前3行+往后1行
5）sum(pv) over(partition by 分区字段 order by 排序字段 rows between current row and unbounded following) as pv5   ---当前行+往后所有行  
1
2
3
4
5

关键是理解
rows between 含义,也叫做window子句：
preceding：往前
following：往后
current row：当前行
unbounded：无边界，unbounded preceding 表示从最前面的起点开始， unbounded following：表示到最后面的终点
–其他avg，min，max，和sum用法一样

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

ntile(2) over(partition by 分区字段 order by 排序字段) as ntile1,    --分组内将数据分成2片

6 lag(col,n,default) 用于统计窗口内往上第n行值 --数据源

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时候，取默认值，如不指定，则为null）

lag(createtime,1,'1970-01-01') over(partition by 分区字段order by 排序字段)

7 lead(col,n,default) ：与 lag 相反，用于统计窗口内往下第n行值
1
2
3
4
5
6
7
8
9
10
11

三 聚合函数（UDTF；UDAF等）

1.str_to_map(Str,'k-v间间隔符','k和v间间隔符'):将字符类型转成map类型

例如：
str_to_map(concat_ws('&&&',collect_set(concat(k,':',v))),'&&&',':')

concat(k,':',v)：拼接k-v字符串：如 k1:v1

2.collect_set(A)：列转行，将多个k-v 收集 k1:v1 k2:v2 k3:v3（多对一）

concat_ws(B,'&&&')：表示连接时指定分隔符 k1:v1&&&k2:v2&&&k3:v3

3.str_to_map(C,'&&&',':'): 将字符类型转成map类型{k1:v1,k2:v2,k3:v3}

4.explode：explode就是将hive一行中复杂的array或者map结构拆分成多行（一对多）
1
2
3
4
5
6
7
8
9
10
11
12
13
14

经常使用 例如：

explode(split(regexp_replace(Array,‘$$’),‘%’),‘&’))

explode(array(‘A’,‘B’,‘C’)) ; explode(map(‘A’,10,‘B’,20,‘C’,30))

regexp_replace:符合替代字符串

split(A,‘%’):据指定字符切分字符串

explode(A):拆分成多行

lateral view:用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
1

经常使用 例如：

lateral view explode(split(regexp_replace(Array,‘￥’,‘$’),‘&’)) 视图名 AS 列名

由此可见，lateral view与explode等udtf就是天生好搭档，explode将复杂结构一行拆成多行，然后再用lateral view做各种聚合。

四 map相关函数

map的具体k-v，例如：{k1:v1,k2:v2,k3:v3}

1、size(Map)函数：可得map的长度 

2. map_keys(Map)函数：可得map中所有的key;  返回值类型: array

3.map_values(Map)函数：可得map中所有的value; 返回值类型: array

4.array_contains(map_keys(Map),'k1')：判断map中是否包含某个key值

5.split(Map['k1'],',')[1]：

 在k-v对中，若value有多个值的情况，如 {'k1':'01,02,03'} ，要用 'k1' 中 '02'作为过滤条件

6.substr(Map['k2'],1,2) = '45'：k2的值必须为'45'开头
1
2
3
4
5
6
7
8
9
10
11
12
13

五 动态分区（随机分区，减少数据倾斜）

insert overwrite table xx partition (id_hash)

select id,int(rand()*100) asid_hash

from xx.xx

distribute by int(rand()*100);
1
2
3
4
5
6
7