全网最全Hive近百个函数详解

背景

Apache Hive是一个建立在Apache Hadoop之上的数据仓库软件项目，用于提供数据查询和分析，现支持引擎有MapReduce、Tez、Spark等等。

Hive像传统的关系型数据库一样含有大量内置函数，但也可支持UDF、UDAF等用户自定义函数编写。Hive自身支持函数的隐式转换，方便用户使用。但是这些隐式转换出现问题可能不会报错，但是也会给用户带来非期望的结果。建议大家函数按照规范来使用。

内置函数

现在分门别类地整理了日期、数值、集合、条件、字符串、聚合等内置函数的详解与举例说明等

• 日期函数：

1. date_add 指定日期n天之后 用法：date_add(date('2018-09-09'),10) 参数类型分别为date和int，返回date类型 例如： select date_add(date('2018-09-09'),10) -- 返回：2018-09-19
2. add_months 指定日期n月之后 用法：add_months(date('2018-09-09'),2) 参数类型分别为date和int类型，返回date类型 例如： select add_months(date('2018-09-09'),2) -- 返回：2018-11-09
3. datediff 计算两个日期相差天数 用法：datediff(date('2018-09-19'),date('2018-09-09'))参数类型都为date类型，返回值int， 例如： select datediff(date('2018-09-19'),date('2018-09-09')) -- 返回：10
4. dayofmonth 返回日期中的日 用法：dayofmonth(date('2018-09-09'))参数类型为date类型，返回值int 例如： select dayofmonth(date('2018-09-09')) -- 返回：9
5. weekofyear 返回日期在一年中的周数 用法：weekofyear(date('2018-09-09'))参数类型为date类型，返回值int 例如： select weekofyear(date('2018-09-09')) -- 返回：36
6. to_date 返回时间戳中的日期部分 用法：to_date(cast('2018-09-09 12:00:00' as timestamp))或to_date(date('2018-09-09'))参数类型为timestamp时间戳或date类型，返回值为string 例如： select to_date(cast('2018-09-09 12:00:00' as timestamp)) select to_date(date('2018-09-09')) -- 返回 ：2018-09-09
7. unix_timestamp 将日期转为unix时间戳 用法：unix_timestamp(datedate('2018-09-19'))参数类型为date类型，返回值bigint 例如： select unix_timestamp(date('2018-09-19')) -- 返回：1537286400
8. year 返回日期或时间戳的年份 用法：year(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型 例如： select year(date('2018-09-19')) --返回：2018
9. quarter 返回日期或时间戳的季度 用法：quarter(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型 例如： select quarter(date('2018-09-19')) --返回：3
10. month 返回日期或时间戳的月份 用法：month(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型 例如： select quarter(date('2018-09-19')) --返回：3
11. day 返回日期或时间戳的天 用法：day(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型 例如： select day(date('2018-09-19')) --返回：19
12. hour 返回时间戳中的小时 用法：hour(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型 例如： select hour(timestamp('2009-07-30 12:58:59')) --返回：12
13. minute 返回时间戳中的分钟 用法：minute(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型 例如： select minute(timestamp('2009-07-30 12:58:59')) --返回：58
14. second 返回时间戳中的秒数 用法：second(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型 例如： select second(timestamp('2009-07-30 12:58:59')) --返回：59
15. date_sub 返回指定日期n天之前日期 用法：date_sub(date('2018-09-09'),5) 参数类型分别为date和int，返回date类型 例如： select date_sub(date('2018-09-09'),5) -- 返回：2018-09-04
16. current_date、current_timestamp 返回当前日期、当前时间戳 用法：select current_date,current_timestamp 返回当前日期、当前时间戳作为字段 select current_date,current_timestamp --返回：2018-10-092018-10-09 17:14:25.868

• 集合函数：

1. size 返回map或array中元素的大小 用法：size(map('A',10,'B',20,'C',30))或size(array('A',10,'B',20,'C',30))参数类型为map类型或array类型，返回值int类型 例如： select size(map('A',10,'B',20,'C',30)),size(array('A',10,'B',20,'C',30)) --返回：36
2. map_keys 返回map类型中的keys 用法：map_keys(map('A',10,'B',20,'C',30))参数类型为map类型，返回值array类型 例如： select map_keys(map('A',10,'B',20,'C',30)) --返回：["A","B","C"]
3. map_values 返回map类型中的values 用法：map_values(map('A',10,'B',20,'C',30))参数类型为map类型，返回值array类型 例如： select map_values(map('A',10,'B',20,'C',30)) --返回：[10,20,30]
4. array_contains数组中是否包含某值，如果存在，返回true；反之，返回false 用法：array_contains(array(10,20,30),10)参数类型分别为数组类型及数组中的元素一致的数据类型 例如： select array_contains(array(10,20,30),10) --返回：true
5. sort_array数组排序 用法：sort_array(30,20,10)参数类型为数组类型，返回值为数组类型 例如： select sort_array(array(30,20,10)) --返回：[10,20,30]

• 条件函数：

1. if判断函数，判断条件为true返回为true的条件值，否则返回为false的条件值 用法：if(boolean testCondition, T valueTrue, T valueFalseOrNull)有三个参数，第一个为布尔类型表达式，第二、三参数为泛型T，返回值类型T 例如： select if(2>8,'A','C') --返回：C select if(10>8,'A','C') --返回：A
2. isnull判断输入参数是否为空，为空返回ture；反之，返回false 用法：isnull(T a)参数类型为泛型T，返回值类型为boolean类型 例如： select isnull(null) --返回：true
3. isnotnull判断输入参数是否为空，为空返回ture；反之，返回false 用法：isnull(T a)参数类型为泛型T，返回值类型为boolean类型 例如： select isnotnull('hello') --返回：false
4. nvl返回如果当前值为空，返回默认值 用法：nvl(T value, T default_value)参数类型为泛型T，返回值类型为T 例如： select nvl(null,'a') --返回：a select nvl('b','c') --返回：b
5. coalesce返回第一个不为空的值 用法：coalesce(T v1, T v2, ...),参数类型为泛型T，返回值类型为T 例如： select coalesce('a','b','c') --返回：a select coalesce(null,'b','c') --返回：b select coalesce( null,null,'c') --返回：c

• 字符串函数：

1. substr或substring从字符串中截取子字符串 用法：substr(str1,start_index,end_index)或substring(str1,start_index,end_index)参数类型依次为string，int和int，返回值为string 例如： select substring('helloworld',1,5) -- 返回 ：hello select substring('helloworld',-3) -- 返回 ：rld 禁止使用date和数值类型使用此函数进行字符串截取
2. instr返回子字符串在字符串中位置 用法：instr(str, substr)参数类型都为string，返回值int 例如： select instr('helloworld','wo') -- 返回 ：6
3. ascii返回字符串第一字母的ascii码 用法：ascii('abcde')参数类型为string，返回值类型int 例如： select ascii('abcde') --返回：97
4. concat字符串连接 用法：concat('a','b','c',....)参数类型为string，返回值类型string 例如： select concat('a','b','c','d','e') --返回：abcde
5. length返回字符串长度 用法：length('abcde')参数类型string，返回类型int 例如： select length('abcde') --返回：5
6. lower把字符串转换为小写字符串 用法：lower('fOoBaR')参数类型为string，返回值string 例如： select lower('fOoBaR') --返回：foobar
7. upper把字符串转换为小写字符串 用法：upper('fOoBaR')参数类型为string，返回值string 例如： select upper('fOoBaR') --返回：FOOBAR
8. ltrim去除字符串左侧空格 用法：ltrim(' foobar ')参数类型string，返回值string 例如： select ltrim(' foobar ') --返回：'foobar '
9. rtrim去除字符串右侧空格 用法：rtrim(' foobar ')参数类型string，返回值string 例如： select ltrim(' foobar ') --返回：' foobar'
10. trim去除字符串左右两侧空格 用法：rtrim(' foobar ')参数类型string，返回值string 例如： select ltrim(' foobar ') --返回：'foobar'
11. initcap返回字符串首字母大写 用法：initcap('foobar')参数类型string，返回值string 例如： select initcap('foobar') --返回：Foobar
12. reverse返回字符串反序 用法：reverse('foobar')参数类型string，返回值string 例如： select reverse('foobar') --返回：raboof
13. space返回指定n个空格字符串 用法：space(10)参数类型int，返回值string 例如： select space(10) --返回：' '
14. repeat返回字符串重复n次后的字符串 用法：repeat('a',10) 例如： select repeat('a',10) --返回：'aaaaaaaaaa'
15. split将字符串按指定分隔符，拆分为数组 用法： select split('a,b,c,d',',') --返回：["a","b","c","d"]
16. lpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从左边补全 用法：lpad('AAAAA',10,'d') 例如： select lpad('AAAAA',10,'d') --返回：dddddAAAAA
17. rpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从右边补全 用法：rpad('AAAAA',10,'d') 例如： select rpad('AAAAA',10,'d') --返回：AAAAAddddd
18. regexp_extract返回被解析字符串，匹配到正则表达式中index指定组的结果 用法：regexp_extract('foothebar', 'foo(.*?)(bar)', 2)第一个参数为被解析字符串，第二参数为正则表达式，第三参数为指定组索引index。注意：第三参数index，0代表返回全部，默认为1，索引数不能大于正则表达中括号，大于会报错。 例如： select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) --返回：foothebar select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) --返回：the select regexp_extract('foothebar', 'foo(.*?)(bar)', 0) --返回：bar
19. regexp_replace匹配正则表达式的字符串替换 用法：regexp_replace('foobar', 'oo|ar', 'A')第一参数为目标字符串，第二参数为正则表达式，第三参数为替换字符串。 例如： select regexp_replace('foobar', 'oo|ar', 'A') --返回：fAbA
20. get_json_object依据json路径从json字符串提取json对象用法：select get_json_object(json,'.id')例如：select get_json_object(json,'.id')--返回：0001
21. str_to_map用两个分隔符将文本拆分为key-value键值对 用法：str_to_map(text, delimiter1, delimiter2)第一参数为拆分文本，第二参数为拆分key-values，第三参数把key-values拆分为key和value，返回值类型map 例如： select str_to_map('a:1,b:2,c:3,d:4',',',':') --返回：{"b":"2","a":"1","d":"4","c":"3"}

• 数值函数：

1. negative返回数值相反数 用法：negative(value)返回值-value 例如： select negative(3) -- 返回 ：-3 select negative(-3) -- 返回 ：3
2. round(double a)返回数值类型四舍五入的结果 用法：round(4.5)输入参数为double类型，返回值double类型 例如： select round(4.5) --返回：5.0
3. round(double a, INT d)返回数值类型四舍五入的结果，并指定保留小数点位数 用法：round(4.5555,3)第一参数double类型，第二参数int类型 例如： select round(4.5555,3) --返回：4.556
4. floor返回小于或等于该数值参数的最大整数 用法：floor(9.6)参数为double类型，返回值类型bigint 例如： select floor(9.6) --返回：9
5. ceil或ceiling返回大于或等于该数值参数的最小整数 用法：ceil(9.6)参数为double类型，返回值类型bigint 例如： select ceil(9.6) --返回：10
6. rand(), rand(INT seed)返回一个0到1范围内的随机数。如果指定种子seed，则得到一个稳定的随机数序列。 用法：rand()或rand(100)返回一个0到1范围内Doubel类型的随机数 例如： select rand() --返回：0.311864052663654 select rand() --返回：0.48138097605120345 select rand(100) --返回：0.7220096548596434 select rand(100) --返回：0.7220096548596434 --rand(100) 两次执行返回结果相同
7. exp自然指数函数返回e的n次方 用法：exp(double n)或exp(decimal n)参数类型double或decimal类型，返回值double类型 例如： select exp(1) --返回：2.718281828459045 select exp(10) --返回：22026.465794806718
8. ln返回以e为底log对数 用法：ln(double n)或ln(Decimal n)参数类型double或decimal类型，返回值double类型 例如： select ln(2.718281828459045) --返回：1.0 select ln(22026.465794806718) --返回：10.0
9. log10返回以10为底log对数 用法：log10(double n)或log10(Decimal n)参数类型double或decimal类型，返回值double类型 例如： select log10(10) --返回：1.0 select log10(1000) --返回：3.0
10. log2返回以2为底log对数 用法：og2(double n)或og2(Decimal n)参数类型double或decimal类型，返回值double类型 例如： select log2(2) --返回：1.0 select log2(8) --返回：3.0
11. pow或power返回a的n次方 用法：pow(double a,double n)参数类型都为double类型，返回值double类型 例如： select pow(2,3) --返回：8.0 select pow(10,3) --返回：1000.0
12. sqrt返回平方根 用法：sqrt(double a)参数类型double类型，返回值double类型 例如： select sqrt(4) --返回：2.0 select sqrt(100) --返回：10.0
13. abs求绝对值 用法：abs(double a)参数类型double类型，返回值double类型 例如： select abs(-100) --返回：100 select abs(10) --返回：10
14. sin正弦函数 用法：sin(double a)或sin(Decimal a)，返回值double类型 例如： select sin(0.5235987755982989) --返回：0.5
15. asin反正弦函数 用法：asin(double a)或asin(Decimal a)，返回值double类型 例如： select asin(0.5) --返回：0.5235987755982989
16. cos余弦函数 用法：cos(double a)或cos(Decimal a)，返回值double类型 例如： select cos(1.0471975511965979) --返回：0.4999999999999999
17. acos反余弦函数 用法：acos(double a)或acos(Decimal a)，返回值double类型 例如： select acos(0.4999999999999999) --返回：1.0471975511965979
18. tan正切函数 用法：tan(double a)或tan(Decimal a)，返回值double类型 例如： select tan(0.5235987755982989) --返回：0.5773502691896258
19. atan正切函数 用法：atan(double a)或atan(Decimal a)，返回值double类型 例如： select atan(0.5773502691896258) --返回：0.5235987755982989
20. degrees弧度值转换为度数值 用法：degrees(double a)或degrees(Decimal a)，返回值double类型 例如： select degrees(0.5235987755982989) --返回：30.000000000000004
21. radians度数值转换为弧度值 用法：radians(double a)或radians(Decimal a)，返回值double类型 例如： select radians(30) --返回：0.5235987755982989
22. pi返回派常量 用法：pi() 返回值double类型 例如： select pi() --返回：3.141592653589793
23. e返回Euler的e常量 用法：e()返回值double类型 例如： select e() --返回：2.718281828459045
24. greatest返回指定参数列表中最大值 用法：greatest(T v1, T v2, …)返回值类型T 例如： select greatest(1,10,4,5,9) --返回：10
25. least返回指定参数列表中最小值 用法：least(T v1, T v2, ...)返回值类型T 例如： select least(1,10,4,5,9) --返回：1
26. sign返回数值符号类型，如果参数大于0，返回1.0；小于0，返回-1.0；等于0，返回0.0 用法：sign(double a)或sign(Decimal a)，返回值double类型 例如： select sign(100.0) --返回：1.0 select sign(-100.0) --返回：-1.0 select sign(0.0) --返回：0.0
27. positive返回返回参数自身的函数 用法：positive(double a)或positive(int a)，返回值double类型或int类型 例如： select sign(100) --返回：100.0 select sign(-100) --返回：-100

• 聚合函数：

假设有t表，数据如下：

1. count返回总记录行数 用法：count(*) 或 count(distinct col1)返回值为bigint类型 例如： select count(*) from t --返回：3
2. sum返回分组组内元素汇总值 用法：sum(col1)或sum(distinct col1) 返回类型为double类型 例如： select id,sum(col1) from t group by id --返回: A 3 B 3
3. avg返回分组组内元素平均值 用法：avg(col1)或avg(distinct col1) 返回类型为double类型 例如： select id,avg(col1) from t group by id --返回: A 1.5 B 3
4. min返回分组组内元素最小值 用法：min(col1)返回类型为double类型 例如： select id,min(col1) from t group by id --返回: A 1 B 3
5. max返回分组组内元素最小值 用法：max(col1)返回类型为double类型 例如： select id,max(col1) from t group by id --返回: A 2 B 3
6. var_pop(col1)返回分组组内元素的方差 用法：var_pop(col1)返回类型为double类型 例如： select id,var_pop(col1) from t group by id --返回： A0.25 B0
7. var_samp(col1)返回分组组内元素的无偏样本方差 用法：var_samp(col1)返回类型为double类型 例如： select id,var_samp(col1) from t group by id --返回： A0.5 B0
8. stddev_pop(col1)返回分组组内元素的标准差 用法：stddev_pop(col1)返回类型为double类型 例如： select id,stddev_pop(col1) from t group by id --返回： A0.5 B0
9. stddev_samp(col1)返回分组组内元素的无偏样本标准差 用法：stddev_samp(col1)返回类型为double类型 例如： select id,stddev_samp(col1) from t group by id --返回： A0.7071067811865476 B0
10. covar_pop(col1, col2)返回分组组内一对元素的总体协方差 用法：covar_pop(col1, col2)返回类型为double类型 例如： select id,covar_pop(col1, col2) from t group by id --返回： A0.25 B0
11. covar_samp(col1, col2)返回分组组内一对元素的样本协方差 用法：covar_samp(col1, col2)返回类型为double类型 例如： select id,covar_samp(col1, col2) from t group by id --返回： A0.5 B0
12. corr返回分组组内一对元素的皮尔逊相关系数 用法：corr(col1, col2)返回类型为double类型 例如： select id,corr(col1, col2) from t group by id --返回： A0.9999999999999999 BNULL

总结

这里Hive整理的是日常常用的函数，同时这也是Presto有的函数，只是函数名称、字段类型、参数的顺序不同而已。但是Presto这些函数都要求规范的数据类型，不然会报错的。

由于笔者知识及水平有限,因此文中错漏之处在所难免,恳请各位老师、专家不吝赐教。