hive中好用的函数

参考: Hive常用函数总结

1. 字符串相关

1.1 字符串替换

select regexp_replace('\n123\n','\n','');
select translate("MOBIN","BIN","M");
-- MOM

1.2 查找子串位置

1. 集合查找函数: find_in_set
   返回以逗号分隔的字符串中str第一次出现的位置，未找到则返回0，如果任一参数为NULL将返回NULL。
   注意：这里位置从1开始的！！！

select find_in_set('aa','aa,bb,cc');
-- 1
select find_in_set('aa','bb,cc');
-- 0
或 
select instr('aba', 'b');
-- 2

2. 查找字符串str中的pos位置后字符串substr第一次出现的位置

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

1.3 取子串

1. 返回从指定位置开始(从1开始编码)，到最后的子串

select substr('abcde',3);
-- cde
select substr('abcde',0);
-- abcde
select substr('abcde',1);
-- abcde
select substr('abcde',-1); 
-- e
select substr('abcde',-2); 
-- de

2. 返回从指定位置开始，长度为len的字符串

-- 截取除后两位之外的子串
select substr('abcde--',1,length('abcde--')-2);
-- abcde

3. 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取

substring_index(string A, string delim, int count)

2. 时间相关

2.1 计算两个日期相差多少天

select datediff('2021-06-04', '2021-06-01');
-- 3

2.2 日期增加函数

select date_add('2021-07-10',10);
-- 2021-07-20
select date_sub('2021-07-10',-10);

2.3 获取当前时间

select current_date;

3. 逻辑运算

3.1 返回a除b的余数的绝对值

pmod(int a, int b)
pmod(double a, double b)
-- 选取uid倒数第二位数字是偶数的数据
select *
from zz.table_name
where pmod(int(substr(uid,-2,1)),2)=0

3.2 判断Array类型字段，是否包含某值

array_contains(businesstype, 'SDK')
if(ARRAY_CONTAINS(split(event_type, ','), '2'),1,0)

3.3 取数组最大值

sort_array(array(a.cnt1,a.cnt2,a.cnt3))[2]
sort_array(array(3,10,null))[2]
-- 将数组里面的数组元素进行升序排序，下标为2表示取由小到大的第三个元素。
或
select greatest(10,null,1);
-- 最小值
select least(-1, 0, 5, 8) 
--  -1

3.3 数组长度

select size(array(3,10,null));
-- 3

4 其他

4.1 使用两个分隔符将文本拆分为键值对

str_to_map(字符串参数, 分隔符1, 分隔符2)
分隔符1将文本分成K-V对，分隔符2分割每个K-V对。对于分隔符1默认分隔符是 ‘,’，对于分隔符2默认分隔符是 ‘=’。

在table表中主键是dt+uid被储存的值是客户的状态。
现需要对客户状态进行统计。

select str_to_map(concat_ws(',',collect_set(concat(dt,':',label))),',',':') 
from table 
group by uid;

-- select str_to_map(concat_ws(',',collect_set(concat('khj',':','22'))),',',':')['khj'];

4.2 平均数

select avg(a.fuid_cnt) ;

4.3 中位数

select percentile(cast(a.chat_7d_cnt as int),0.5) as chat_7d_cnt_avg;
select percentile_approx(cast(a.total_7d_minutes as double),0.5) as total_7d_minutes_avg;