Hive常用函数大全
分类
- 数学函数
- 字符串函数
- 集合函数
- 日期函数
- 类型转换
- 条件函数
- 聚合函数
- 表生成函数
- 窗口函数
- 分析函数
函数列表
1.数学函数
pow(double base,double p) 底数为base的幂值double
pmod(int/double a,it/double b) 求a余b的结果int/double
-- 负数取余 select pmod(-3,5)
-- (-3+n)*5)%5 n为正整数取值为从1开始带入至表达式第一次为正值时的值
rand() 产生随机数
rand(seed) --伪随机：相同seed产生相同随机数
sign(double/decimal a) 返回a的符号
positive(int/double a) 返回a的
negative(int/double a) 返回a的相反数
bin(num10) 返回10进制参数的2进制数值
conv(bigint/string base,int from_base,int to_base) 将数值base从from_base进制转换为to_base进制
greatest(T...vs) 返回参数列表中的最大值
least(T...vs) 返回参数列表中的最小值
bround(decimal) 银行家舍入法
-- 四舍六入五考虑，五后非空就进一，五后为空看奇偶，五前为偶应舍去，五前为奇要进一
shiftleft(bigint|int a,int b) 位左移
shiftright(bigint|int a,int b) 位右移- 字符串函数
concat(int|string|...) 拼接字符串
concat_ws(string sep,string...||array<string>); 以特定分隔符拼接字符串
substr(string cnt,int pos[,int len]) 从pos开始截取指定长度的子字符串（pos起始为1）
substring_index(string src,string sep,int count) 按照指定分隔符截取指定数量的字符串
regexp_extract(str,group_regex,pos)
-- select regexp_extract('{"name":"henry","age":"18","gender":"male"}','\\{"name":"(.*?)","age":"(.*?)","gender":"(.*?)"\\}',1);
split(str,regex) 根据正则regex分割字符串，支持多字符分割
locate(substr,str[,pos]) 从pos开始找到substr首次出现首字母的位置，否则返回0
instr(str,substr) 找到substr在str中首次出现的首字母的位置，否则返回0
str rlike regex
-- select '{"name":"henry","age":"18","gender":"male"}' rlike '^.*?"age":"\\d+",.*?$'; => true
replace(str,substr,rep) 将str中的所有substr替换为rep
regexp_replace(str,regex,rep) 将str中的所有满足regex规则的内容替换为rep
translate(str,from,to) 按照from同位置将str中的内容替换为to的内容
-- select translate('abcd','bc','d '); => ad d
str to map(string cnt,string kvsSep,string kvSep)
-- 将字符串cnt使用kvsSept作为键值对之间的分隔符kvSep作为键和值的分隔符转化为map类型
sentences(str)
-- 将str以标点符号作为第一个维度的分割，再以空格作为第二个维度的分割，将str拆成一个二维数组
ngrams(array<array<string>> arr,int n,inttopk)
-- 针对arr中连连续n个单词做词频统计并倒序排列，将topk个结果返回
context_ngrams(array<array<string>> arr,array<string> cnt,int k)
-- 针对arr中连续size(cnt)个单词组合以cnt中非null内容匹配统计，按数量倒序排列，将topk个结果返回
encode(string cnt,string encode) 将cnt转换为encode编码内容
-- 主要编码有'US-ASCII','ISO-8859-1','UTF-8','UTF-16BE','UTF-16LE','UTF-16'
cast(val as type) 将exp转化为type类型的值，一次解析以像但可解析多层
get_json_object(string json,string path) 提取json格式字符串中指定key的值
json_tuple(string json,string...ps) 提取json字符串中指定Key列表的值，一次解析多个但限一层
inf_file(string cnt,string filepath) 返回内容cnt是否存在与文件filepath中
-- select in_file('KY08','/root/kb12/class.log');
parse_url(string url,string part[,string key]) 解析url根据part提取内容
-- 当part为Query时添加key进行单独键值的提取
-- part:PROTOCOL,HOST<QUERY<REF<PATH<USERINFO
MD5(concat('slat_prefix','field','salt suffix')) 将字符串转为MD5编码，非对称加密，不可逆
binary(str) 将字符串转换为二进制
base64(binary(str)) 将str字符串使用base64加密
unbase64(str) 将str字符串使用base64解密
base64(aes_encrypt(string cnt,string secretKey)) 对称加密
aes_decrypt(unbase64(string)) 解密
-- 密钥长度为16+n(0~正整数)*8- 集合函数
size(array/map) 返回集合元素的数量
array_contains(array,item) 返回数组中是否包含元素item
array(item1,...,itemn) 返回多个元素的数组
map(k1,v1,...kn,vn) 返回多个元素的键值对
struct(v1,...vn) 返回多个元素的结构体（自动追加列名col1,...coln）
map_keys(map) 返回多个键值对的键集合
map_values(map) 返回键值对的值集合
sort_array(array)- 日期函数
current_date() 返回系统当前日期
current_timestamp() 返回系统当前日期的完整格式
unix_timestamp([string|datetime|timestamp[,date_format]]) 默认返回系统当前时间戳（单位：秒）
-- 如果只有参数1，返回参数1的完整格式对应的时间戳
-- 如果有两个参数，返回参数1参数2格式的时间按戳
date_add(date start_date,int days) 返回start_date之后第days天的日期
add_months(string|datetime|timestamp start_date,int months) 返回start_date之后第months月的日期
datediff(big_date,small_date) 返回两个日期之间的天数差值
from_unixtime(bigint[,date_format]) 返回时间戳对应的date_format格式字符串信息
date_format(string|date|datetime|timestamp,date_format) 返回日期对应的date_format格式
to_date(string|datetime|timestamp) 返回日期的年月日
next_day(string|datetime|timestamp,weekDay) 返回距离当前日期最近的下一个weekDay
last_day(string|datetime|timestamp) 返回参数日期所属月份最后一天的日期
-- concat(year(current_date()),'-12-31') 年最后一天
-- date_sub(add_months(trunc(current_date(),'Q'),3),1) 季度最后一天
-- date_sub(next_day(current_date(),'mo'),1) 周最后一天
trunc(string|datetime|timestamp,part) 返回参数1对应参数2单位的第一天日期
-- part:YY => 年，Q => 季， MM => 月
-- date_add(next_day(date,'MO'),-7) 返回参数日期所属周的第一天日期
months_between(big_date,small_date) 返回两个日期之间的月数差- 条件函数
if(condition,if_true_val,if_false_val) 双重分支，相当于三元运算符
nvl(field,default_val) field==null ? default_vla : field
case [ field when CONST_V1 ] then V1 ... else VN end 等值判断多分支
case [ when field>=CONST_V1] then v1 ... else VN end 区间判断多分支
coalesce(T...vs) 返回参数列表中第一个非null,单行中多列操作
isnull(field) 返回field列值是否为空
isnotnull(field) 返回field列值是否为非空- 聚合函数
count(*) #all rows
count(expre) #rows where expr is not NULL
count(distinct expr[,expr,...]) #rows where all the exprs is unique and not NULL
sum(distinct expr)/avg(distinct expr)/min(col)/max(col)
var_pop(col) 方差（离散程度）
var_samp(col) 样本方差（变异程度）
stddev_pop(col) 标准偏差
stddev_samp(col) 样本标准偏差
covar_pop(col1,col2) 协方差
covar_samp(col1,col2) 样本协方差
corr(col1,col2) 两列数值的相关系数
percentile(bigint col,p) 分位数函数，返回col的p(0~1)%分位数，p为double或double array
collect_list(col) 列转列表
collect_set(col) 列传set- 表生成函数
-- 侧视图： select ... from TABLE_NAME lateral view func(field) LV_ALIAS as item_alias;
explode(array<T>/Map<K,V>)
posexplode(array<T>)
stack(rowNum,v1,...,vm)
json_tuple(string json,string...key)(key,value)...
parse_url_tuple(string url,string...parts)
inline(array<struct<...>>)- 窗口函数
row_number()
从1开始，按照顺序，生成分组内记录的序列，row_number()的值不会存在重复，当排序的值相同时，按照表中记录的顺序进行排列；通常用于获取分组内排序第一的记录；获取一个session中的第一条refer等
rank()
生成数据项在分组中的排名（并列出现空缺）1，2，2，4
dense_rank()
生成数据项在分组中的排名（并列不留空缺）1，2，2，3
sum、avg、min、max
ntile()
ntile(n)用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。ntile不支持ROWS BETWEEN
使用场景：1、统计出工资前1/5的人员的名单，使用NTILE分析函数，把所有工资分为5份，为的那一份就是所要结果
2.sale前20%或者50%的用户ID
lead()
lead(col,n,default)用户统计窗口内往下第n行值，第一个参数为列明，第二个参数为往n行（可选，默认为1），第三个参数为默认值（当往下n行为NULL时候，取默认值，如不指定，则为NULL）
使用场景：通常用于统计某用户在某个网页上的停留时间
lag()
lag(col,n,DEFAULT)用于统计窗口内往上第n行的值，第一个参数为列名，第二个参数为往上第n行（可选你，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL
first_value()
去分组内排序后，截止到当前行，第一个值
last_value()
取分组内排序后，截止到当前行，最后一个值
cume_dist()
-小于等于当前值的总行数/分组内总行数
-比如，统计小于等于当前薪水的人数，所占总人数的比例
percent_rank
分组内当前行的RANK值-1/分组内总行数-1
over从句（定义如何开窗口）
over( [partition by field [ order by field ] ] rows between … and …)
window子句（在窗口中移动）
rows between … and …
unbounded
n preceding
current_row
n following
unbounded_following
PRECEDING: 往前
FOLLOWING: 往后
CURRENT ROW: 起点
UNBOUNDED PRECEDING：表示从前面的起点
UNBOUNDED FOLLOWING: 表示到后面的终点
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
