Hive常用函数大全说明及实例

Hive 函数数量很大，今天写一些常用的，其他的后面用到了会更新加上
Hive 函数大致分为：聚合函数、内置函数、表生成函数
其中内置函数占大部分，包括：数学函数、字符函数、集和函数、转换函数、日期函数、条件函数等
关于精度的问题，大家参考这篇文章：https://blog.csdn.net/qq_34566673/article/details/108299006

一、数学函数：

!
非，
举例：select !true
结果：false

!=
不等值比较，
举例：select 1!=1
结果：false

!=
不等值比较，
举例：select 1!=1
结果：false

%
取余，
举例：select 5%2
结果：1

&
与，
举例：select 1&1
结果：1

加减乘除就是常规的数学运算
select 2*3、select 2+3、select 2-3、select 2/3
结果： 6 、5、-1、0.666666
hive中最高精度的数据类型是double,只精确到小数点后16位，在做除法运算的时候要特别注意

等值比较: =/==
举例：
select 1=1
结果：true

不等值比较: <>
描述:如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE
举例：
select 1<>1
结果：false

小于比较: <
描述:如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE
举例：
select 2<3
结果：true

小于等于比较: <=
描述:如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE
举例：select 3<=3
结果：true

大于比较: >
描述:如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于表达式B，则为TRUE；否则为FALSE
举例：select 1>1
结果：false

大于等于比较: >=
描述:如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A大于或者等于表达式B，则为TRUE；否则为FALSE
举例：select 1>=1
结果：true

注意：String的比较要注意(常用的时间比较可以先to_date之后再比较)

空值判断: IS NUL
描述:如果表达式A的值为NULL，则为TRUE；否则为FALSE

非空判断: IS NOTNULL
描述:如果表达式A的值为NULL，则为FALSE；否则为TRUE

LIKE比较: LIKE
操作类型: strings
描述:如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。B中字符””表示任意单个字符，而字符”%”表示任意数量的字符。
举例：
select1 from test where ‘football’ like ‘foot%’;
select1 from test where ‘football’ like 'foot___’;
注意：否定比较时候用NOT ALIKE B
select1 from test where NOT ‘football’ like ‘fff%’;

JAVA的LIKE操作: RLIKE
操作类型: strings
描述:如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE。
举例： select1 from test where 'footbar’ rlike ‘^f.*rKaTeX parse error: Got function '\newline' with no arguments as superscript at position 1: \̲n̲e̲w̲l̲i̲n̲e̲’;
select1 from test where ‘123456aa’ rlike ‘^\d+$’;

regexp操作:
操作类型: strings
描述:功能与RLIKE相同
举例： select1 from test where ‘footbar’ regexp ‘^f.*r$’;

regexp_extract
描述:抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格。
举例：select regexp_extract(‘henry@qq.com’,’(\w{3,})@([0-9a-z]{2,}).(com|cn|org|edu)’,3)
结果：com

regexp_replace
描述:按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，
举例：select regexp_replace(‘2020/08/27’,’/’,’-’)
结果：2020/8/27

位或操作: |
说明：返回A和B按位进行或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例： select 4 | 8 from test;
12
select 6 | 8 from test;
14

位异或操作: ^
说明：返回A和B按位进行异或操作的结果。结果的数值类型等于A的类型和B的类型的最小父类型（详见数据类型的继承关系）。
举例：select 4 ^ 8 from test;
12
select 6 ^ 4 from test;
2

位取反操作: ~
说明：返回A按位取反操作的结果。结果的数值类型等于A的类型。
举例：select ~6 from test;
-7
select ~4 from test;
-5

取整函数: round
说明:返回double类型的整数值部分（遵循四舍五入）
举例：
#返回对a四舍五入的bigint值
select round(1.5) # 输出 2
select round(-1.5) # 输出 -2 该四舍五入计算方式为：绝对值四舍五入加负号

#返回double型d的保留n位小数的double型的近似值
select round(6.58354,3) #输出 6.584

floor()： 舍1
向下取整，返回<=该参数的最大整数
SELECT FLOOR(1.5); # 输出 1
SELECT FLOOR(-1.5); # 输出 -2

ceil()，ceiling()：进1
向上取整,返回>=该参数的最小整数
SELECT CEIL(1.5); # 输出 2
SELECT CEIL(-1.5); # 输出 -1

rand(), rand(INT seed)：每行返回一个DOUBLE型随机数，seed是随机因子
种子seed相同，会出现伪随机现象，即每次生成的随机数相同。
#select rand(1)
多次执行，结果相同，为0.72345873904579

自然指数函数: exp
返回值: double
说明:返回自然对数e的a次方

以10为底对数函数: log10
说明:返回以10为底的a的对数
举例：select log10(100) from test;
2.0

以2为底对数函数: log2
log2(double a)
说明:返回以2为底的a的对数
举例： select log2(8) from test;
3.0

对数函数: log
log(double base, double a)
说明:返回以base为底的a的对数
举例：select log(4,256) from test;
4.0

幂运算函数: pow(DOUBLE a, DOUBLE p)，power(DOUBLE a, DOUBLE p)：计算a的p次幂

幂运算函数: power
说明:返回a的p次幂,与pow功能相同
举例： select power(2,4) from test;
16.0

开平方函数: sqrt
说明:返回a的平方根
举例： select sqrt(16) from test;
4.0

二进制函数: bin(BIGINT a)
说明:计算二进制a的STRING类型，a为BIGINT类型
举例：select bin(54) #输出 110110

十六进制函数: hex(BIGINT a)，hex(STRING a)，hex(BINARY a)
说明:如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示
举例：select hex(54) #输出 36
select hex(‘bigdata’) #输出 62696764617461

反转十六进制函数: unhex
说明:hex的逆方法，返回该十六进制字符串所代码的字符串
举例：select unhex(62696764617461) #输出 bigdata

进制转换函数:conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)：
说明:将GIGINT/STRING类型的num从from_base进制转换成to_base进制
举例：
SELECT CONV(‘a’,16,2) 输出1010

绝对值函数: abs
说明:返回数值a的绝对值
举例：
select abs(-3.9) from test;
3.9
select abs(10.9) from test;
10.9

正取余函数: pmod(int a, int b),pmod(double a, double b)
说明:a对b取模，返回正的a除以b的余数
举例： select pmod(-9,4) from test;
3

sin(DOUBLE a), sin(DECIMAL a)：求a的正弦值

asin(DOUBLE a), asin(DECIMAL a)：求a的反正弦值

cos(DOUBLE a), cos(DECIMAL a)：求余弦值

acos(DOUBLE a), acos(DECIMAL a)：求反余弦值

tan(DOUBLE a), tan(DECIMAL a)：求正切值

atan(DOUBLE a), atan(DECIMAL a)：求反正切值

degrees(DOUBLE a), degrees(DECIMAL a)：将弧度值转换角度值

radians(DOUBLE a), radians(DOUBLE a)：将角度值转换成弧度值

positive(INT a), positive(DOUBLE a)：返回a

negative(INT a), negative(DOUBLE a)：返回a的相反数

sign(DOUBLE a), sign(DECIMAL a)：如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0；就是数学里的符号函数

e()：数学常数e

pi()：数学常数pi

factorial(INT a)：求a的阶乘

cbrt(DOUBLE a)：求a的立方根

greatest(T v1, T v2, …)：求最大值

least(T v1, T v2, …)：求最小值

二、字符函数

字符串长度函数：length
语法: length(string A)
返回值: int
说明：返回字符串A的长度
举例：select length(‘abcedfg’) from test;
7

字符串反转函数：reverse
语法: reverse(string A)
返回值: string
说明：返回字符串A的反转结果
举例：select reverse(abcedfg’) from test;
gfdecba

字符串连接函数：concat
语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串
举例：select concat(‘abc’,'def’,'gh’) from test;
abcdefgh

带分隔符字符串连接函数：concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符
举例：select concat_ws(’,’,‘abc’,‘def’,‘gh’) from test;
abc,def,gh

字符串截取函数：substr,substring
语法: substr(string A, int start),substring(string A, int start)
返回值: string
说明：返回字符串A从start位置到结尾的字符串
举例：
select substr(‘abcde’,3) from test;
cde
select substring(‘abcde’,3) from test;
cde
selectsubstr(‘abcde’,-1) from test; （和ORACLE相同）
e

字符串截取函数：substr,substring
语法: substr(string A, int start, int len),substring(string A, intstart, int len)
返回值: string
说明：返回字符串A从start位置开始，长度为len的字符串
举例：
select substr(‘abcde’,3,2) from test;
cd
select substring(‘abcde’,3,2) from test;
cd
select substring(‘abcde’,-2,2) from test;
de

字符串转大写函数：upper,ucase
语法: upper(string A) ucase(string A)
返回值: string
说明：返回字符串A的大写格式
举例：
select upper(‘abSEd’) from test;
select ucase(‘abSEd’) from test;

字符串转小写函数：lower,lcase
语法: lower(string A) lcase(string A)
返回值: string
说明：返回字符串A的小写格式
举例：
select lower(‘abSEd’) from test;
absed
select lcase(‘abSEd’) from test;
absed

去空格函数：trim
语法: trim(string A)
返回值: string
说明：去除字符串两边的空格
举例：
select trim(’ abc ') from test;
abc

左边去空格函数：ltrim
语法: ltrim(string A)
返回值: string
说明：去除字符串左边的空格
举例：
select ltrim(’ abc ') from test;
abc

右边去空格函数：rtrim
语法: rtrim(string A)
返回值: string
说明：去除字符串右边的空格
举例：
select rtrim(’ abc ') from test;
abc

正则表达式替换函数：regexp_replace
语法: regexp_replace(string A, string B, string C)
返回值: string
说明：将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数。
举例：
select regexp_replace(‘foobar’, ‘oo|ar’, ‘’) from test;
fb

正则表达式解析函数：regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明：将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
举例：
select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 1) fromtest;
the
select regexp_extract(‘foothebar’, 'foo(.?)(bar)’, 2) fromtest;
bar
select regexp_extract(‘foothebar’, ‘foo(.?)(bar)’, 0) fromtest;
foothebar
注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
select data_field,
regexp_extract(data_field,’.?bgStart=([^&]+)’,1) as aaa,
regexp_extract(data_field,’.?contentLoaded_headStart=([^&]+)’,1) as bbb,
regexp_extract(data_field,’.?AppLoad2Req=([^&]+)’,1) as ccc
from pt_nginx_loginlog_st
where pt = '2012-03-26’limit 2;

URL解析函数：parse_url
语法: parse_url(string urlString, string partToExtract [, stringkeyToExtract])
返回值: string
说明：返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.
举例：
selectparse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) fromtest;
facebook.com
selectparse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’,‘k1’) from test;
v15. json解析函数：get_json_object

语法: get_json_object(string json_string, string path)
返回值: string
说明：解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。
举例：
select id,
get_json_object(line,’$.nam{e}^{\prime })name,ge{t}_{j}so{n}_{o}bject(line{,}^{\prime }$.age’) age,
get_json_object(line,’$.gender’) gender,
from jsontest
结果：
1 zs 18 男
2 ls 19 女
3 ww 20 男

空格字符串函数：space
语法: space(int n)
返回值: string
说明：返回n个空格

重复字符串函数：repeat
语法: repeat(string str, int n)
返回值: string
说明：返回重复n次后的str字符串
举例：
select repeat(‘abc’,5) from test;
abcabcabcabcabc

首字符ascii函数：ascii
语法: ascii(string str)
返回值: int
说明：返回字符串str第一个字符的ascii码
举例：
select ascii(‘abcde’) from test;
97

左补足函数：lpad
语法: lpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行左补足到len位
举例：
select lpad(‘abc’,10,‘td’) from test;
tdtdtdtabc
注意：与GP，ORACLE不同，pad不能默认

右补足函数：rpad
语法: rpad(string str, int len, string pad)
返回值: string
说明：将str进行用pad进行右补足到len位
举例：
select rpad(‘abc’,10,‘td’) from test;
abctdtdtdt

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

集合查找函数:find_in_set
语法: find_in_set(string str, string strList)
返回值: int
说明:返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0
举例：
select find_in_set(‘ab’,‘ef,ab,de’) from test;
2
select find_in_set(‘at’,‘ef,ab,de’) from test;
0

trim(string A)：将字符串A前后出现的空格去掉

unbase64(string str)：将64位的字符串转换二进制值

upper(string A)，ucase(string A)：将字符串A中的字母转换成大写字母

initcap(string A)：将字符串A转换第一个字母大写其余字母小写的字符串

levenshtein(string A, string B)：计算两个字符串之间的差异大小
举例：select levenshtein(‘kitten’, ‘sitting’)
结果：3

三、集和函数

个数统计函数: count(Map<K.V>)、size(Array)
说明:返回map或者数组的长度
举例：# array 长度 3
select size(array(‘2018’,2348,‘2019’,948534,‘2020’,9483543))
#map 长度 3
select size(map(‘2018’,2348,‘2019’,948534,‘2020’,9483543))

map_keys(Map<K.V>)：返回map中的所有key
举例：select map_keys(map(‘2018’,2348,‘2019’,948534,‘2020’,943543))
结果：[“2018”,“2019”,“2020”]

map_values(Map<K.V>)：返回map中的所有value
举例：
select map_values(map(‘2018’,2348,‘2019’,948534,‘2020’,943543))
结果：[2348,948534,943543]

array_contains(Array, value)：如该数组Array包含value返回true，否则返回false
举例：select array_contains(array(‘aa’,‘bb’,‘cc’),‘cc’)
结果：true

sort_array(Array) ：按自然顺序对数组进行排序并返回（升序）
举例：select sort_array(array(4,46,15,6,84,64,89))
结果：[4,6,15,46,64,84,89]

四、转换函数

binary(string|binary)：将输入的值转换成二进制

cast(expr as type)：将expr转换成type类型。如果转换未成功，则返回null。如果转换(expr为布尔值)，Hive对非空字符串返回true。
举例：select cast(‘a’ as bigint) #输出 null
select cast(3>2 as boolean) #输出 true

五、日期函数

UNIX时间戳转日期函数:from_unixtime(bigint unixtime[, string format])
说明:将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）
举例：select from_unixtime(1250111000,“yyyy-MM-dd”) #输出 2009-08-12

获取当前UNIX时间戳函数:unix_timestamp()，unix_timestamp(string date)，unix_timestamp(string date, string pattern)
说明:获得当前时区的UNIX时间戳
举例：select unix_timestamp() #输出 1598527984

说明:将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
举例：select unix_timestamp(‘2009-03-20 11:30:01’) #输出 1237563001

说明:将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0
举例：select unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) #输出 1237521600

日期时间转日期函数:to_date(string timestamp)
说明:返回日期时间字段中的日期部分。
举例：select to_date(“1970-01-01 00:00:00”) #输出 1970-01-01

日期转年函数:year(string date)
说明：返回时间字符串的年份部分
举例：select year(“1970-01-01 00:00:00”) #输出 1970

日期转月函数: month
说明:返回日期中的月份。
举例：select year(“1970-01-01 00:00:00”) #输出 1

日期转天函数: day
说明:返回日期中的天。
举例：select year(“1970-01-01 00:00:00”) #输出 1

日期转小时函数: hour
说明:返回日期中的小时。
举例：select hour(‘2011-12-08 10:03:01’) #输出10

日期转分钟函数: minute
说明:返回日期中的分钟。
举例：select hour(‘2011-12-08 10:03:01’) #输出3

日期转秒函数: second
说明:返回日期中的秒。
举例：select second(‘2011-12-08 10:03:01’) #输出1

日期转周函数:weekofyear
说明：返回时间字符串位于一年中的第几个周内
举例：select weekofyear(“1970-11-01 00:07:09”) #输出 44

日期比较函数: datediff
语法: datediff(string enddate, string startdate)
说明:返回结束日期减去开始日期的天数。
举例：select datediff(‘2009-03-01’, ‘2009-02-27’) #输出 2

日期增加函数: date_add
语法: date_add(string startdate, int days)
说明:返回开始日期startdate增加days天后的日期。
举例：select date_add(‘2012-12-08’,10) #输出2012-12-18

日期减少函数: date_sub
语法: date_sub (string startdate, int days)
说明:返回开始日期startdate减少days天后的日期。
举例：
select date_sub(‘2012-12-08’,10) #输出2012-11-28

quarter(date/timestamp/string)：返回当前时间属性哪个季度
举例：select quarter(“1970-01-01 00:00:00”) #输出 1

months_between(date1, date2)
说明:返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0
举例：select months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) #输出 3.94959677

date_format(date/timestamp/string ts, string fmt)
说明:按指定格式返回时间date
举例：select date_format(“2016-06-22”,“MM-dd”) #输出 06-22

六、条件函数

If函数: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull
举例：select if(2>1,1,0) #输出 1

空查找函数：isnull( a )
说明: 如果a为null就返回true，否则返回false

非空查找函数: COALESCE
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
举例： select COALESCE(null,‘100’,'50′) from test;
100

条件判断函数：CASE
语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
举例：# 返回4
CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END

条件判断函数：CASE
语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
说明：如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e
举例： # 返回5
CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END
#返回0
CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END

七、聚合函数

count(), count(expr), count(DISTINCT expr[, expr…])
说明： count()
统计总行数，包括含有NULL值的行
count(expr)
统计提供非NULL的expr表达式值的行数
count(DISTINCT expr[, expr])
统计提供非NULL且去重后的expr表达式值的行数

sum(col), sum(DISTINCT col)
说明：sum(col),表示求指定列的和，sum(DISTINCT col)表示求去重后的列的和

min(col)
说明：求指定列的最小值

max(col)
说明：求指定列的最大值

variance(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%分位数