hive函数大全+详情

hive函数分类

运算符

运算符	含义	实例	结果
!	非，类似not	select !true	FALSE
!=	不等于，类似<>	select 1!=2	TRUE
%	取余，返回a除以b的余数	select 40%3	1
&	位与操作，返回a和b按位进行与操作的结果	select 3&50	2
*	乘法	select 2*3	6
+	加法	select 2+4	6
-	减法	select 2-5	-3
/	除，返回a除以b的结果，最好通过round指定精度	select 2/6 select round(2/6,2)	0.3333333333 0.33
<	小于	select 1<2	TRUE
<=	小于等于	select 1<=2	TRUE
<=>	完全等于，严格比较两个null值是否相等	select 1<=>2	FALSE
<>	不等于	select 1<>2	TRUE
=	等于	select 1=2	FALSE
==	等于	select ‘a’==‘a’	TRUE
>	大于	select 1>2	FALSE
>=	大于等于	select 1>=2	FALSE
^	运算符查看两个参数的二进制表示法的值,并执行按位”异或”操作	select 5^12	9
I	运算符查看两个参数的二进制表示法的值，并执行按位”或”操作	select 5	9
~	对一个表达式执行按位”非”（取反）	~1	-2
not	逻辑否
or	逻辑或
and	逻辑与

数值计算函数

函数名	含义	实例	结果
round	取整函数，返回double类型的整数值部分 （遵循四舍五入）	select round(3.5);	4
round	指定精度取整函数，返回指定精度d的double类型	select round(3.1415926,4);	3.1416
floor	向下取整函数，返回等于或者小于该double变量的最大的整数	select floor(3.14) ;	3
ceil	向上取整函数，返回等于或者大于该double变量的最小的整数	select ceil(6.1);	7
ceiling	向上取整函数，与ceil功能相同	select ceil(3.1) from dual;	4
rand	取随机数函数，返回一个0到1范围内的随机数	select rand() from dual;	0.1828459
exp	自然指数函数，返回自然对数e的a次方	select exp(1) from dual;	2.718281828459
log10	返回以10为底的对数	select log10(100)	2.0
log2	返回以2为底的对数	select log2(16)	4
log	返回以具体数值为底的对数	select log(4,256)	4.0
pow	幂函数	select pow(2,3)	8
power	幂函数	select pow(2,5)	32
sqrt	开平方函数	select sqrt(9)	3
bin	二进制函数	select bin(4)	100
hex	十六进制函数	select hex(17)	11
unhex	反转十六进制函数	select unhex(‘616263’)	abc
conv	进制转换函数conv(BIGINT num, int from_base, int to_base)	select conv(17,10,2)	10001
abs	绝对值函数	select abs(-1.2)	1.2
pmod	正取余函数， pmod(int a, int b)，返回正的a除以b的余数	select pmod(9,4)	1
sin	正弦函数
asin	反正弦函数
cos	余弦函数
acos	反余弦函数
positive	返回数值本身	select positive(-9)	-9
negative	取相反数	select negative(-1)	1

汇总统计函数

函数名	含义	实例	结果
count	个数统计函数	select count(*)
sum	总和统计函数:	select sum(数值字段)
avg	平均值统计函数	select avg(数值字段)
min	最小值统计函数	select min(数值字段)
max	最大值统计函数	select max(数值字段)
var_pop	非空集合总体变量函数	统计结果集中col非空集合的总体变量
var_samp	非空集合样本变量函数	统计结果集中col非空集合的样本变量
stddev_pop	总体标准偏离函数
stddev_samp	样本标准偏离函数
percentile	中位数函数
percentile_approx	近似中位数函数percentile_approx(DOUBLE col, p [, B])	求近似的第pth个百分位数，p必须介于0和1之间
histogram_numeric	近似中位数函数percentile_approx(DOUBLE col, array(p1 [,p2]…) [,B])	可以输入多个百分位数，返回类型也为array
histogram_numeric	直方图	select histogram_numeric(100,5)	[{“x”:100.0,“y”:1.0}]
collect_set	集合去重数collect_set (rst)	rst字段进行合并成一个数组，去重
collect_list	集合不去重函数 collect_list (rst)	将 rst字段合并成一个数组,不去重

类型转换函数

函数名	含义	实例	结果
cast	指定类型转换	cast(“12345” as int)	string"12345"，转成int12345

日期函数

函数名	含义	实例	结果
current_date()	确认当前日期	select current_date()	2020-08-29
from_unixtime	UNIX时间戳转日期函数	select from_unixtime(1323308943,‘yyyyMMdd’)	20111208
unix_timestamp	获取当前UNIX时间戳函数	select unix_timestamp()	1323423425
unix_timestamp	日期转UNIX时间戳函数	select unix_timestamp(‘2020-8-29 13:01:03’)	1598720463
unix_timestamp	指定格式日期转UNIX时间戳函数	select unix_timestamp(‘20111207 13:01:03’,‘yyyyMMdd HH:mm:ss’)	1323280863
to_date	日期时间转日期函数: to_date	select to_date(‘2020-8-29 09:30:29’)	2020-8-29
year	日期转年函数	select year(‘2020-8-29 09:30:29’)	2020
month	日期转月函数	select month(‘2020-8-29 09:30:29’)	8
day	日期转天函数	select day(‘2020-8-29 09:30:29’)	29
hour	日期转小时函数	select hour(‘2020-8-29 09:30:29’)	9
minute	日期转分钟函数	select minute(‘2020-8-29 09:30:29’)	30
second	日期转秒函数	select second (‘2020-8-29 09:30:29’)	29
weekofyear	日期转周函数	select weekofyear(‘2020-8-29 09:30:29’)	35
datediff	日期比较函数	select datediff(‘2020-8-29’,‘2020-08-09’)	20
date_add	日期增加函数	select date_add(‘2020-8-29’,1)	2020-8-30
date_sub	日期减少函数	select date_sub(‘2020-8-29’,1)	2020-8-28
months_between	返回date1和date2之间的月数	select months_between(‘2020-01-01’,‘2020-02-01’)	1

条件函数

函数名	含义	实例	结果
if	if条件判断，类似于三元表达式	if( 1 is null,‘a’,‘b’ )	b
coalesce	非空查找函数,返回第一非null的值	coalesce(null,1,2)	1
case	条件判断函数	case a when b then c [when d then e] [else f] END	如果a=b就返回c,a=d就返回e，否则返回f
case	条件判断函数	case when a then b [when c then d] [else e] END	如果a=ture就返回b,c= ture就返回d，否则返回e
isnull	是否为空	isnull( a )	如果a为null就返回true，否则返回false
isnotnull	是否不为空	isnotnull ( a )	如果a为非null就返回true，否则返回false
nvl	nvl(T value, T default_value)	如果value值为NULL就返回default_value,否则返回value

字符串函数

函数名	含义	实例	结果
ascii	返回字符串第一个字符的ascii码	select ascii(‘abc’)	97
base64
concat	字符串连接函数	select concat(‘ab’,‘c’,‘d’)	abcd
concat_ws	带分隔符字符串连接函数	select concat(’.’,‘ab’,‘c’,‘d’)	ab.c.d
format_number	将数字X格式化为’＃，###，###。##'等格式	SELECT format_number（12332.123456,4）	‘12,332.1235’
substr	字符串截取函数	select substr(‘abcde’,3) :select substr(‘abcde’,3,2)	cde:cd
substring	字符串截取函数	select substring(‘abcde’,3)	cde
instr	返回str中第一次出现substr的索引	SELECT instr（‘Facebook’，‘boo’）	5
length	字符串长度函数	select length(‘abcedfg’)	7
reverse	字符串反转函数	select reverse(‘abc’)	cba
rlike	如果str匹配regexp则返回true，否则返回false
locate	返回位置pos后str中第一次出现substr的位置	SELECT locate（‘bar’，‘foobarbar’，5)	7
printf	可以根据printf样式格式字符串格式化字符串的函数	SELECT printf（“Hello World％d％s”，100，“days”）	“Hello World 100天”
str_to_map	通过解析文本使用两个分隔符将文本拆分为键值对来创建映射,第一个分隔符分隔对，第二个分隔符分配键和值。如果只给出一个参数，则使用默认分隔符：’,‘作为delimiter1，’:'作为delimiter2
upper	字符串转大写函数	select upper(‘ab’)	AB
ucase	字符串转大写函数	select ucase (‘ab’)	AB
lower	字符串转小写函数	select lower(‘AB’)	ab
lcase	字符串转小写函数	select lcase (‘AB’)	ab
trim	去空格函数
ltrim	左边去空格函数
rtrim	右边去空格函数
regexp_replace	正则表达式替换函数,将字符串A中的符合java正则表达式B的部分替换为C	regexp_replace(string A, string B, string C)：select regexp_replace(‘abc’, ‘a’, ‘v’)	vbc
regexp_extract	正则表达式解析函数,将字符串subject按照pattern正则表达式的规则拆分	regexp_extract(string subject, string pattern, int index)
parse_url	URL解析函数	parse_url(string urlString, string partToExtract [, stringkeyToExtract])	partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
get_json_object	json解析函数	get_json_object(string json_string, string path)	解析json的字符串json_string,返回path指定的内容
space	空格字符串函数	select space(10)	10个空格
repeat	重复字符串函数	select repeat(‘a’,2)	aa
lpad	左补足函数	select lpad(‘abc’,3,‘d’)	将’abc’进行用d进行左补足到4位：dabc
rpad	右补足函数	select lpad(‘abc’,3,‘d’)	将’abc’进行用d进行右补足到4位：abcd
split	分割字符串函数	select split(‘abcabcd’,‘c’)	[“ab”,“ab”,“d”]
find_in_set	集合查找函数	select find_in_set(‘a’,‘a,b,c’)	1
sentences	将字符串中内容按语句分组，每个单词间以逗号分隔，最后返回数组	sentences(‘Hello there! How are you?’)	( (“Hello”, “there”), (“How”, “are”,“you”) )

复合类型构造函数

函数名	含义	实例	结果
map	集合构造	select map(‘a’,‘1’,‘b’,‘2’)	{“a”:“2”,“b”:“1”}
struct	struct 构造函数	STRUCTprovince:STRING,city:STRING,district:STRING,detail:STRING
array	构造数组	select array(1,2,3)	[1,2,3]
create_union	create_union (tag, val1, val2, …) 使用给定的 tag 和表达式，构造一个 union类型数据结构。tag 表示使用第 tag 个	select create_union(0,‘ss’,array(1,2,3))	{0:“ss”}

集合操作函数

函数名	含义	实例	结果
size	集合大小	select size(array(‘a’,‘b’))	2
size	数组大小	select size(map(‘name’,‘chang’,‘age’,‘18’,‘hobbies’,‘swimming’))	3
array_contains	判断元素数组是否包含元素	select array_contains(array(“aa”,“bb”,“cc”),“aa”)	true
map_keys	获取map中所有key集合	select map_keys(map(‘name’,‘chang’,‘age’,‘18’,‘hobbies’,‘swimming’))	[“name”,“age”,“hobbies”]
map_values	获取map中所有value集合	select map_values(map(‘name’,‘chang’,‘age’,‘18’,‘hobbies’,‘swimming’))	[“chang”,“18”,“swimming”]
sort_array	数组排序	select sort_array(array(18,1,7,23,123,55))	[1,7,18,23,55,123]

表生成函数

函数名	含义	实例	结果
explode	字符串分割，或者将array或者map展开

XPath解析XML函数

函数名	含义	实例	结果
XPath	xpath（xml，xpath） - 返回xml节点中与xpath表达式匹配的值的字符串数组	SELECT xpath（’ b1 </ b> b2 </ b> b3 </ b> c1 </ c> c2 </ c> '，‘a / c / text（）’）FROM src LIMIT 1	[“c1”，“c2”]
xpath_boolean	xpath_boolean（xml，xpath） - 计算布尔xpath表达式	SELECT xpath_boolean（’ 1 </ b> '，‘a / b’）FROM src LIMIT 1;	true
xpath_double	xpath_double（xml，xpath） - 返回与xpath表达式匹配的double值	SELECT xpath_double（’ 1 </ b> 2 </ b> ’ ，‘sum（a / b）’） from src LIMIT 1;	3.0
xpath_float	xpath_float（xml，xpath） - 返回与xpath表达式匹配的浮点值	SELECT xpath_float（’ 1 </ b> 2 </ b> '，‘sum （a / b）’）FROM src LIMIT 1;	3.0
xpath_int	xpath_int（xml，xpath） - 返回与xpath表达式匹配的整数值	SELECT xpath_int（’ 1 </ b> 2 </ b> '，‘sum （a / b）’）FROM src LIMIT 1;	3
xpath_long	path_long（xml，xpath） - 返回与xpath表达式匹配的long值	SELECT xpath_long（’ 1 </ b> 2 </ b> '，‘sum （a / b）’）FROM src LIMIT 1;	3
xpath_number	xpath_number（xml，xpath） - 返回与xpath表达式匹配的double值	SELECT xpath_number（’ 1 </ b> 2 </ b> ’ ，‘sum（a / b）’）from src LIMIT 1;	3.0
xpath_short	xpath_short（xml，xpath） - 返回与xpath表达式匹配的short值	SELECT xpath_short（’ 1 </ b> 2 </ b> '，‘sum （a / b）’）FROM src LIMIT 1;	3
xpath_string	xpath（xml，xpath） - 返回xml节点中与xpath表达式匹配的值的字符串数组	SELECT xpath_string（’ b </ b> cc </ c> </ a >’，‘a / c’）FROM src LIMIT 1;	‘B1B2’