hive函数

1、日期函数

1.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

1.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

1.3 datediff 计算两个日期相差天数

用法：datediff(date('2018-09-19'),date('2018-09-09'))参数类型都为date类型，返回值int

select datediff(date('2018-09-19'),date('2018-09-09'))
-- 返回：10

1.4 dayofmonth 返回日期中的日

用法：dayofmonth(date('2018-09-09'))参数类型为date类型，返回值int

select dayofmonth(date('2018-09-09'))  
-- 返回：9

1.5 weekofyear 返回日期在一年中的周数

用法：weekofyear(date('2018-09-09'))参数类型为date类型，返回值int

select weekofyear(date('2018-09-09'))
-- 返回：36

1.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

1.7 unix_timestamp 将日期转为unix时间戳

用法：unix_timestamp(datedate('2018-09-19'))参数类型为date类型，返回值bigint

select unix_timestamp(date('2018-09-19'))
-- 返回：1537286400

1.8 year 返回日期或时间戳的年份

用法：year(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型

select year(date('2018-09-19'))
--返回：2018

1.9 month 返回日期或时间戳的月份

用法：month(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型

select month(date('2018-09-19'));
-- 返回 9

1.10 day 返回日期或时间戳的天

用法：day(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型

select day(date('2018-09-19'))
--返回：19

1.11 hour 返回时间戳中的小时

用法：hour(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型

select hour('2009-07-30 12:58:59');
select hour(timestamp('2009-07-30 12:58:59'));
--返回：12

1.12 minute 返回时间戳中的分钟

用法：minute(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型

select minute(timestamp('2009-07-30 12:58:59'));
select minute('2009-07-30 12:58:59');
--返回：58

1.13 second 返回时间戳中的秒数

用法：second(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型

select second(timestamp('2009-07-30 12:58:59'))
select second('2009-07-30 12:58:59');
--返回：59

1.14 date_sub 返回指定日期n天之前日期

用法：date_sub(date('2018-09-09'),5) 参数类型分别为date和int，返回date类型

select date_sub(date('2018-09-09'),5)
select date_sub('2018-09-09',5)
-- 返回：2018-09-04

1.15 current_date、current_timestamp 返回当前日期、当前时间戳

用法：select current_date,current_timestamp 返回当前日期、当前时间戳作为字段

select current_date,current_timestamp
--返回：2018-10-09  2018-10-09 17:14:25.868

2、集合函数

2.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))
--返回：3   6
--map中为奇数，报错Arguments must be in key/value pairs

2.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"]

2.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]

2.4 array_contains数组中是否包含某值，如果存在，返回true；反之，返回false

用法：array_contains(array(10,20,30),10)参数类型分别为数组类型及数组中的元素一致的数据类型

select array_contains(array(10,20,30),10)
--返回：true

2.5 sort_array数组排序

用法：sort_array(30,20,10)参数类型为数组类型，返回值为数组类型

select sort_array(array(30,20,10))
--返回：[10,20,30]

3、条件函数

3.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

3.2 isnull判断输入参数是否为空，为空返回ture；反之，返回false

用法：isnull(T a)参数类型为泛型T，返回值类型为boolean类型

select isnull(null)
--返回：true

3.3 isnotnull判断输入参数是否为空，为空返回ture；反之，返回false

select isnotnull('hello')
--返回：false

3.4 nvl返回如果当前值为空，返回默认值

用法：nvl(T value, T default_value)参数类型为泛型T，返回值类型为T

select nvl(null,'a')
--返回：a
select nvl('b','c')
--返回：b

3.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

4、字符串函数

4.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和数值类型使用此函数进行字符串截取

4.2 instr返回子字符串在字符串中位置

用法：instr(str, substr)参数类型都为string，返回值int

select instr('helloworld','wo')
-- 返回 ：6

4.3 ascii返回字符串第一字母的ascii码

用法：ascii('abcde')参数类型为string，返回值类型int

select ascii('abcde')
--返回：97

4.4 concat字符串连接

用法：concat('a','b','c',....)参数类型为string，返回值类型string

select concat('a','b','c','d','e')
--返回：abcde

4.5 length返回字符串长度

用法：length('abcde')参数类型string，返回类型int

select length('abcde')
--返回：5

4.6 lower把字符串转换为小写字符串

用法：lower('fOoBaR')参数类型为string，返回值string

select lower('fOoBaR')
--返回：foobar

4.7 upper把字符串转换为小写字符串

用法：upper('fOoBaR')参数类型为string，返回值string

select upper('fOoBaR')
--返回：FOOBAR

4.8 ltrim去除字符串左侧空格

用法：ltrim(' foobar ')参数类型string，返回值string

select ltrim(' foobar ')
--返回：'foobar '

4.9 rtrim去除字符串右侧空格

用法：rtrim(' foobar ')参数类型string，返回值string

select ltrim(' foobar ')
--返回：' foobar'

4.10 trim去除字符串左右两侧空格

用法：trim(' foobar ')参数类型string，返回值string

select trim(' foobar ')
--返回：'foobar'

4.11 initcap返回字符串首字母大写

用法：initcap('foobar')参数类型string，返回值string

select initcap('foobar')
--返回：Foobar

4.12 reverse返回字符串反序

用法：reverse('foobar')参数类型string，返回值string

select reverse('foobar')
--返回：raboof

4.13 space返回指定n个空格字符串

用法：space(10)参数类型int，返回值string

select space(10)
--返回：'         '

4.14 repeat返回字符串重复n次后的字符串

用法：repeat('a',10)

select repeat('a',10)
--返回：'aaaaaaaaaa'

4.15 split将字符串按指定分隔符，拆分为数组

select split('a,b,c,d',',')
--返回：["a","b","c","d"]

4.16 lpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从左边补全

select lpad('AAAAA',10,'d')
--返回：dddddAAAAA

4.17 rpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从右边补全

select rpad('AAAAA',10,'d')
--返回：AAAAAddddd

4.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)', 2)
--返回：bar

4.19 regexp_replace匹配正则表达式的字符串替换

用法：regexp_replace('foobar', 'oo|ar', 'A')

• 第一参数为目标字符串，
• 第二参数为正则表达式，
• 第三参数为替换字符串。

select regexp_replace('foobar', 'oo|ar', 'A')
--返回：fAbA

4.20 get_json_object依据json路径从json字符串提取json对象

用法：select get_json_object(json,'$.id')

4.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"

5、数值函数

5.1 negative返回数值相反数

用法：negative(value)返回值-value

select negative(3)
-- 返回 ：-3
select negative(-3)
-- 返回 ：3

5.2 round(double a)返回数值类型四舍五入的结果

用法：round(4.5)输入参数为double类型，返回值double类型

select round(4.5)
--返回：5.0

5.3 round(double a, INT d)返回数值类型四舍五入的结果，并指定保留小数点位数

用法：round(4.5555,3)第一参数double类型，第二参数int类型

select round(4.5555,3)
--返回：4.556

5.4 floor返回小于或等于该数值参数的最大整数

用法：floor(9.6)参数为double类型，返回值类型bigint

select floor(9.6)
--返回：9

5.5 ceil或ceiling返回大于或等于该数值参数的最小整数

用法：ceil(9.6)参数为double类型，返回值类型bigint

select ceil(9.6)
--返回：10

5.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) 两次执行返回结果相同

5.7 exp自然指数函数返回e的n次方

用法：exp(double n)或exp(decimal n)参数类型double或decimal类型，返回值double类型

select exp(1)
--返回：2.718281828459045
select exp(10)
--返回：22026.465794806718

5.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

5.9 log10返回以10为底log对数

用法：log10(double n)或log10(Decimal n)参数类型double或decimal类型，返回值double类型

select log10(10)
--返回：1.0
select log10(1000)
--返回：3.0

5.10 log2返回以2为底log对数

用法：log2(double n)或log2(Decimal n)参数类型double或decimal类型，返回值double类型

select log2(2)
--返回：1.0
select log2(8)
--返回：3.0

5.11 pow或power返回a的n次方

用法：pow(double a,double n)参数类型都为double类型，返回值double类型

select pow(2,3)
--返回：8.0
select pow(10,3)
--返回：1000.0

5.12 sqrt返回平方根

用法：sqrt(double a)参数类型double类型，返回值double类型

select sqrt(4)
--返回：2.0
select sqrt(100)
--返回：10.0

5.13 abs求绝对值

用法：abs(double a)参数类型double类型，返回值double类型

select abs(-100)
--返回：100
select abs(10)
--返回：10

5.14 sin正弦函数

用法：sin(double a)或sin(Decimal a)，返回值double类型

select sin(0.5235987755982989)
--返回：0.5

5.15 asin反正弦函数

用法：asin(double a)或asin(Decimal a)，返回值double类型

select asin(0.5)
--返回：0.5235987755982989

5.16 cos余弦函数

用法：cos(double a)或cos(Decimal a)，返回值double类型

select cos(1.0471975511965979)
--返回：0.4999999999999999

5.17 acos反余弦函数

用法：acos(double a)或acos(Decimal a)，返回值double类型

select acos(0.4999999999999999)
--返回：1.0471975511965979

5.18 tan正切函数

用法：tan(double a)或tan(Decimal a)，返回值double类型

select tan(0.5235987755982989)
--返回：0.5773502691896258

5.19 atan正切函数

用法：atan(double a)或atan(Decimal a)，返回值double类型

select atan(0.5773502691896258)
--返回：0.5235987755982989

5.20 degrees弧度值转换为度数值

用法：degrees(double a)或degrees(Decimal a)，返回值double类型

select degrees(0.5235987755982989)
--返回：30.000000000000004

5.21 radians度数值转换为弧度值

用法：radians(double a)或radians(Decimal a)，返回值double类型

select radians(30)
--返回：0.5235987755982989

5.22 pi返回派常量

select pi()
--返回：3.141592653589793

5.23 e返回Euler的e常量

用法：e()返回值double类型

select e()
--返回：2.718281828459045

5.24 greatest返回指定参数列表中最大值

用法：greatest(T v1, T v2, …)返回值类型T

select greatest(1,10,4,5,9)
--返回：10

5.25 least返回指定参数列表中最小值

用法：least(T v1, T v2, ...)返回值类型T

select least(1,10,4,5,9)
--返回：1

5.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

5.27 positive返回返回参数自身的函数

用法：positive(double a)或positive(int a)，返回值double类型或int类型

select sign(100)
--返回：100.0
select sign(-100)
--返回：-100

6、聚合函数

id    col1    col2
A      1       4
A      2       5
B      3       6

6.1 count返回总记录行数

用法：count(*) 或 count(distinct col1)返回值为bigint类型

select count(*) from t
--返回：3

6.2 sum返回分组组内元素汇总值

用法：sum(col1)或sum(distinct col1) 返回类型为double类型

select id,sum(col1) from t group by id
--返回:
A 3
B 3

6.3 avg返回分组组内元素平均值

用法：avg(col1)或avg(distinct col1) 返回类型为double类型

select id,avg(col1) from t group by id
--返回:
A 1.5
B 3

6.4 min返回分组组内元素最小值

用法：min(col1)返回类型为double类型

select id,min(col1) from t group by id
--返回:
A 1
B 3

6.5 max返回分组组内元素最小值

用法：max(col1)返回类型为double类型

select id,max(col1) from t group by id
--返回:
A 2
B 3

6.6 var_pop(col1)返回分组组内元素的方差

用法：var_pop(col1)返回类型为double类型

select id,var_pop(col1) from t group by id
--返回：
A0.25
B0

6.7 var_samp(col1)返回分组组内元素的无偏样本方差

用法：var_samp(col1)返回类型为double类型

select id,var_samp(col1) from t group by id
--返回：
A0.5
B0

6.8 stddev_pop(col1)返回分组组内元素的标准差

用法：stddev_pop(col1)返回类型为double类型

select id,stddev_pop(col1) from t group by id
--返回：
A0.5
B0

6.9 stddev_samp(col1)返回分组组内元素的无偏样本标准差

用法：stddev_samp(col1)返回类型为double类型

select id,stddev_samp(col1) from t group by id
--返回：
A0.7071067811865476
B0

6.10 covar_pop(col1, col2)返回分组组内一对元素的总体协方差

用法：covar_pop(col1, col2)返回类型为double类型

select id,covar_pop(col1, col2) from t group by id
--返回：
A0.25
B0

6.11 covar_samp(col1, col2)返回分组组内一对元素的样本协方差

用法：covar_samp(col1, col2)返回类型为double类型

select id,covar_samp(col1, col2) from t group by id
--返回：
A0.5
B0

6.12 corr返回分组组内一对元素的皮尔逊相关系数

用法：corr(col1, col2)返回类型为double类型

select id,corr(col1, col2) from t group by id
--返回：
A0.9999999999999999
BNULL


原文地址：https://mp.weixin.qq.com/s/pWf3XLtK5cxcGORaMHieJw