hive 函数总结--更新中

row_number() over

分组统计排序

语法：ROW_NUMBER() OVER(PARTITION BY COLUMNORDER BY COLUMN)

row_number() OVER (PARTITION BY COL1 ORDERBY COL2)表示根据COL1分组，在分组内部根据COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（该编号在组内是连续并且唯一的)。

分组统计排序：

ROW_NUMBER() OVER (PARTITION BY video_type_name ORDER BY count(course_name) DESC) AS rnk  sqlserver：rank() over()函数 - 正则吃饺子 - 博客园

select count(course_name) total,video_type_name,course_name,

ROW_NUMBER() OVER (PARTITION BY video_type_name ORDER BY count(course_name) DESC) AS rnk

from video_see_records

group by video_type_name,course_name

nvl

空值转换函数,必须是null，''不行

NVL(expr1, expr2)： 
1、空值转换函数； 
2、类似于mysql-nullif(expr1, expr2)，sqlserver-ifnull(expr1, expr2)。

备注： 
1、如果expr1为NULL，返回值为 expr2，否则返回expr1。 
2、适用于数字型、字符型和日期型，但是 expr1和expr2的数据类型必须为同类型。

from_unixtime

把时间戳格式的时间，转化为年月日时分秒格式的时间

from_unixtime的参数要求为整数，且单位为秒。

如果从业务系统拿到的时间戳格式的时间单位为毫秒，则需要先将它转化为秒，方法如下

from_unixtime( int (timestamp_in_millisecond / 1000))

datediff

两个时间参数的日期之差

parse_url

 函数parse_url,解析URL字符串

parse_url(url, partToExtract[, key]) - extracts a part from a URL  
解析URL字符串，partToExtract的选项包含[HOST,PATH,QUERY,REF,PROTOCOL,FILE,AUTHORITY,USERINFO]。  
  
举例：  
* parse_url('http://facebook.com/path/p1.php?query=1', 'HOST')返回'facebook.com'   
* parse_url('http://facebook.com/path/p1.php?query=1', 'PATH')返回'/path/p1.php'   
* parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY')返回'query=1'，  
可以指定key来返回特定参数，例如  
* parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY','query')返回'1'，  
  
* parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'REF')返回'Ref'   
* parse_url('http://facebook.com/path/p1.php?query=1#Ref', 'PROTOCOL')返回'http'  
 注意：HOST\PATH需要大写

parse_url_tuple

类似parse_url()，但它可以同时提取多个部分并返回

返回值: string
　　　　说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
　　举例：

hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1′, ‘HOST’) from dual;

facebook.com

hive> select parse_url_tuple(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY:k1’, ‘QUERY:k2’);

v1 v2

locate

非全量匹配

locate(string substr, string str[, int pos]) 

 join模糊匹配 
left join , right join , full join

hive> select * from a left join b on 1=1 where locate(a.col,b.col)>0

hive> select * from a   right join b on 1=1 where locate(a.col,b.col)>0

hive> select * from a full join b  where locate(a.col,b.col)>0

除了将locate()直接写在where条件里，也可以使用row_number()来搭配使用。

select col from(

select if(locate(a.col, b.col)>0, b.col, a.col) as col,

row_number() over(partition by a.col order by locate(a.col, b.col) desc) as rn

from a left join b on 1=1 ) as a where rn=1

split

 分割字符串函数

语法:  split(string str, string pat)
返回值:  array  
说明: 按照pat字符串分割str，会返回分割后的字符串数组  
举例：  
hive> select split(‘abtcdtef’,'t’) from dual;  
["ab","cd","ef"]  

特殊字符
如正则表达式中的特殊符号作为分隔符时，需做转义 (前缀加上\)

如果是在shell中运行,hive脚本执行，特别注意\\要写\\\\，则(前缀加上\\)

注：有些特殊字符转义只需\，而有些需\\，eg.?。可能在语句翻译过程中经历经历几次转义。

substr

字符串截取，下标

第一种用法：

substr（string A，int start）和 substring（string A，int start），用法一样

功效：返回字符串A从下标start位置到结尾的字符串

第二种用法：

substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

功效：返回字符串A从下标start位置开始，长度为len的字符串

注意：substr下标start记做0或1都是从第一个开始，记做2是从第二个开始

instr

模糊匹配

instr(sourceStr,destStr,start,appearPosition) 
instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）
1.destString 是匹配标准，sourceString 是被筛选的源数据集
2.start 代表查找的开始位置，这个参数可选的，默认为1；--从1开始计数
3.appearPosition代表想从源字符中查找出第几次出现的destStr，这个参数也是可选的， 默认为1
4.如果start的值为负数，则代表从右往左进行查找，但是位置数据 start 仍然从左向右计算。
5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。

最简单例子：
在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置
select instr(‘abcd’,’a’,1,1) from dual; —1
select instr(‘abcd’,’c’,1,1) from dual; —3
select instr(‘abcd’,’e’,1,1) from dual; —0

应用于模糊查询：instr(字段名/列名, ‘查找字段’)
select count(k.terminal) from cr_t_etl09 k  where k.partition='2020-12-01' and instr(k.prevurl,'https://baidu.com')=1;
等同于
select count(k.terminal) from cr_t_etl09 k  where k.partition='2020-12-01' and like 'https://baidu.com%' ;

应用于判断包含关系：
select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)>0;
等同于
select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);

trim

concat

用于将多个字符串连接成一个字符串

select concat(cookie,'-',rnk,'.0-',url)url_c from dwd_visit

正则表达式，匹配截取

hive官网函数地址

union