hive函数分类，常用函数整理【案例说明】

一、hive函数分类

从输入输出角度分类

• 标准函数： 一行数据中的一列或多列为输入，结果为单一值
• 聚合函数： 多行的零列到多列为输入，结果为单一值
• 表生成函数： 零个或多个输入，结果为多列或多行。

从实现方式分类

• 内置函数
• 自定义函数
  • UDF：自定义标准函数
  • UDAF：自定义聚合函数
  • UDTF：自定义表生成函数

二、标准函数

数学函数

round

1.1 round(double a)

说明：返回对a四舍五入的bigint值
代码解释

select round(3.423);

结果为:3.0

应用场景：
一般用于取整，例如本月访问量等。

1.2 round(double a, INT d)

说明：返回double型d的保留n位小数的double型的近似值
代码解释：

select round(3.1415926,2);

结果为：3.14

应用场景：
一般用于某些数值需要特定的精确度。例如可以用于人命币的计算，因为人命币计算只需要精确到分。

floor(DOUBLE a)

说明：向下取整，最数轴上最接近要求的值的左边的值
代码解释：

select floor(3.59);

结果为：3

应用场景：
例如坐电梯，如果承载量是11个人左右，绝对到不了12个人，可能是11.5个人，那一次也只能装11人

ceil(DOUBLE a), ceiling(DOUBLE a)

说明：求其不小于小给定实数的最小整数
代码解释：

select ceil(3.12);

结果为：4

应用场景：
例如唱歌打分，可能评委打分取平均值，直接就所有选手都补齐多出的部分。

rand(), rand(INT seed)

说明：每行返回一个DOUBLE型随机数seed是随机因子。一般来说rand（），取的是0-1，但是取不到两端。
代码解释：

select rand();

结果为：0-1之间

应用场景：
随机抽奖，但是一般可能不会是0-1，但是可以在后面乘以想要的范围，或者加减，得到想要的范围的随机数。

exp(DOUBLE a), exp(DECIMAL a)

说明：返回e的a幂次方， a可为小数，也可以为负数。
代码解释：

select exp(1.2);

结果为：3.3201169227365472

应用场景：
自个看着用吧。

pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)

说明：计算a的p次幂
代码解释

select pow(2,3);

结果为：8

应用场景：
数学计算

sqrt(DOUBLE a), sqrt(DECIMAL a)

说明：计算a的平方根

 select sqrt(16);

结果为：4

应用场景：
数学计算

abs(DOUBLE a)

说明：计算a的绝对值
代码解释：

select abs(-10);

结果为：10

应用场景：
用于去整数运算

pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)

说明：a对b取模
代码解释：

select pmod(9,2);

结果为：1

应用场景：
用于取模运算。

positive(INT/double a)

说明：返回a
代码解释：

select positive(9);

结果为：9

应用场景： 看着用吧。

negative(INT/double a)

说明：输入的是a，则会返回-a
代码解释：

select negative(-9);

结果为：9

应用场景： 用于去相反数，注意，是相反数，不是绝对值。

greatest(T v1, T v2, …)

说明：在众多数字中，找出最大的返回。
代码解释：

select greatest(1,4,6,9,2,5,7,3);

结果为：9

应用场景： 取最大值。

least(T v1, T v2, …)

说明：least(T v1, T v2, …)
代码解释：

select least(1,3,2,6,9);

结果为:1;

应用场景： 取最小值

集合函数

size(Map<K.V>)

说明：求map的个数
代码解释：

select size(Map(1,2,3,4,5,6));

结果为：3

应用场景：
求键值对数量。

size(Array)

说明：求数组的长度
代码解释：

select size(Array(1,2,3,3,3,33,3,3));

结果为：8

应用场景：
数组过长，需要求数组的长度。

map_keys(Map<K.V>)

说明：返回map中的所有key
代码解释：

select map_keys(Map(1,2,4,5,6,3,9,8));

结果为：[1,4,6,9]

应用场景：
只需要所有中的键。

map_values(Map<K.V>)

说明：返回map中的所有value
代码解释：

select map_values(Map(1,2,4,5,6,3,9,8));

结果为：[2,5,3,8]

应用场景：
只需要得到所有得值。

array_contains(Array, value)

说明：如该数组Array包含value返回true。，否则返回false
代码解释：

select array_contains(Array(1,2,3,3,3,33,3,3),0);

结果为：false

应用场景：
如果字符串过长，可以用此函数判断某一内容，是否在字符串当中。

sort_array(Array)

说明：按自然顺序对数组进行排序并返回
代码解释：

select sort_array(Array(3,8,6,9,1,0,2));

结果为：[0,1,2,3,6,8,9]

应用场景：
将字符创按自然顺序排好，便于查找元素。

类型转换函数

cast(expr as )

说明：将expr转换成type类型
代码解释：

select cast("1" as int);

结果为：1

应用场景：
如果是字符型，讲不好进行比较，可以转成int进行比较，或者排序。

日期函数

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-13

应用场景：
用于将指定时间戳，转换成指定格式的时间。

unix_timestamp()

说明：获取本地时区下的时间戳
代码解释：

select unix_timestamp();

结果为：1595144869

应用场景：
获取本时区下的时间戳，时间戳是1970年一月一日 0时0分0秒到现在所过的秒数。

unix_timestamp(string date)

说明：将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳。
代码解释：

select unix_timestamp('2020-07-19 17:22:00');

结果为：1595150520

应用场景：
用于将时间格式转换为时间戳，方便时间的计算。

unix_timestamp(string date, string pattern)

说明：将指定时间字符串格式字符串转换成Unix时间戳
代码解释：

select unix_timestamp('2020-07-19','yyyy-MM-dd');

结果为：1595088000
后面没显示的部分，会自动补零。

应用场景：
基本与上面一个用法相同，就是不用再去精确到时分秒，比较方便。

to_date(string timestamp)

说明：返回时间字符串的日期部分
代码解释：

select to_date('2020-07-19 22:22:22');

结果为：2020-07-19

应用场景：
在只需要日期的时候直接返回日期，去掉后面的时间部分。

year(string date)

说明：返回时间字符的年部分。
代码解释：

select year("2020-07-19");

结果为：2020

应用场景：
只取相应的年份。

month(string date)

说明：返回时间字符串的月份部分
代码解释：

select month("2020-07-19");

结果为：7

应用场景：
去相应的月份，进行比较。

day(string date) dayofmonth(date)

说明：返回字符串的天。
代码解释：

select day("2020-07-19");

结果为：19

应用场景：
去具体的天数，进行比较计算。

hour(string date)

说明：返回时间字符串的小时
代码解释：

select hour("2020-07-19 22:23:24");

结果为：22

应用场景：
用于只取小时作为比较计算的对象。

minute(string date)

说明：返回时间字符串的分钟
代码解释：

select minute("2020-07-19 22:23:24");

结果为：23

应用场景：
取分钟进行计算。

second(string date)

说明：返回时间字符串的秒
代码解释：

select second("2020-07-19 22:23:24");

结果为：24

应用场景：
用于取得秒数，用的比较少。

weekofyear(string date)

说明：返回输入日期是一年中的第几个日期。
代码解释：

select weekofyear("2020-07-19 22:23:24");

结果为：29

应用场景：
用于特定需要的计算，一般来说用得少。

dayofweek(string date)

说明：返回字符串是星期几。
代码解释:

select dayofweek("2020-07-19 22:23:24");

结果为：1
其实是周日，但是在国外周日就是一周的第一天，而不是休息日，所以可以用减一得到0表示周日。

应用场景:
在不清楚年份月份的时候，可以不用查询，很快地获得对应的是周几。

datediff(string enddate, string startdate)

说明;计算开始时间startdate到结束时间enddate相差的天数
代码解释：

select datediff('2009-03-01', '2009-02-27');

结果为：2

应用场景：
如果两个日期天数相差过大，将会很难计算相差多少天，用这个函数会很快得到结果。

date_add(string startdate, int days)

说明：从开始时间startdate加上days
代码解释;

select date_add('2009-03-01',3);

结果为：2009-03-04

应用场景：
用于添加相应的天数

date_sub(string startdate, int days)

说明：从开始时间startdate减去days
代码解释：

select date_sub('2009-03-01',3);

结果为：2009-02-26 

应用场景：
用于减去相应的天数。

from_utc_timestamp(timestamp, string timezone)

说明：如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
代码解释：

select from_utc_timestamp('1970-01-01 08:00:00','PST');

结果为：1970-01-01 00:00:00.0

应用场景：
拿到数据的时候，数据的时间并非UTC的时候，可以将其转换成指定时区下的时间戳。
PST：是东八区的的时区。

to_utc_timestamp(timestamp, string timezone)

说明：如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
代码解释：

select to_utc_timestamp('1970-01-01 08:00:00','PST');

结果为：1970-01-01 16:00:00.0

current_date

说明：返回当前时间日期
代码解释：

select current_date;

结果为：当前时间

current_timestamp

说明：返回当前时间戳
代码解释：

select current_timestamp;

结果为当前时间戳
2020-07-19 22:37:55.667

add_months(string start_date, int num_months)

说明：返回当前时间下再增加num_months个月的日期
代码解释：

select add_months('2020-07-19',3);

结果为：2020-10-19

last_day(string date)

说明：返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
代码解释：

select last_day('2020-07-19');

结果为：2020-07-31

next_day(string start_date, string day_of_week)

说明：返回当前时间的下一个星期X所对应的日期
代码解释：

select next_day('2020-07-21','TU');

结果为：2020-07-28

trunc(string date, string format)

说明：返回时间的最开始年份或月份
代码解释：

select trunc("2020-06-26",'MM');

结果为：2020-06-01

months_between(date1, date2)

说明：返回date1与date2之间相差的月份
代码解释：

select months_between('1997-11-05 10:30:00', '2020-07-19');

结果为：-272.4375

date_format(date/timestamp/string ts, string fmt)

说明：按指定格式返回时间date
代码解释：

select date_format("2020-07-22","MM-dd");

结果为：07-22

条件函数

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

说明：如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull
代码解释：

select if(2<1,4,9);

结果为:9

nvl(T value, T default_value)

说明：如果value值为NULL就返回default_value,否则返回value。
代码解释：

select nvl(null,'enenen');

结果为：

COALESCE(T v1, T v2, …)

说明：返回第一非null的值，如果全部都为NULL就返回NULL
代码解释：

select COALESCE(null,null,null,1,2,3);

结果为：1

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

说明： 如果a=b就返回c,a=d就返回e，否则返回f
代码解释：

select CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END;

结果为：4

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

说明：如果a=ture就返回b,c= ture就返回d，否则返回e
代码解释：

select CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END;

结果为：5

isnull( a )

说明：如果a为null就返回true，否则返回false
代码解释：

select isnull(null);

结果为：true

isnotnull ( a )

说明：如果a为非null就返回true，否则返回false
代码解释：

select isnotnull(null);

结果为：false

字符函数

concat(string|binary A, string|binary B…)

说明：对二进制字节码或字符串按次序进行拼接。
代码解释：

select concat('hello','world');

结果为：helloworld

concat_ws(string SEP, string A, string B…)

说明：与concat()类似，但使用指定的分隔符喜进行连接
代码解释:

select concat_ws(' | ','hello','world');

结果为：hello | world

concat_ws(string SEP, array)

说明：拼接Array中的元素并用指定分隔符进行分隔
代码解释：

select concat_ws(' | ',Array('I','am','LiYuQi'));

结果为：I | am | LiYuQi

find_in_set(string str, string strList)

说明：返回以逗号分隔的字符串中str出现的位置，如果参数str为逗号或查找失败将返回0，如果任一参数为NULL将返回NULL回
代码解释：

select find_in_set('ab', 'abc,b,ab,c,def');

结果为：3

get_json_object(string json_string, string path)

说明：从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
代码解释：

userinfo ：{"user":{"name":"张三","age":"20"}}
select get_json_object(userinfo,'$.user.name')  as user_name from tableUser;

in_file(string str, string filename)

说明：如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true
代码解释：

instr(string str, string substr)

说明：查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
代码解释：

select instr('ab','nhjkabidl');

结果为：0

length(string A)

说明：返回字符串的长度
代码解释：

select length('askahsdjk');

结果为：9

locate(string substr, string str[, int pos])

说明：查找字符串str中的pos位置后字符串substr第一次出现的位置
代码解释:

select locate("strabjdk","jdk");

结果为：0

lower(string A)

说明：将字符串A的所有字母转换成小写字母
代码解释：

select lower("StrIng");

结果为：string

lpad(string str, int len, string pad)

说明：从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分。
代码解释：

select lpad("abc",20,"e");

结果为：eeeeeeeeeeeeeeeeeabc

parse_url(string urlString, string partToExtract [, string keyToExtract])

说明：返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) =‘facebook.com’，如果参数partToExtract值为QUERY则必须指定第三个参数key 如：parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) =‘v1’

printf(String format, Obj… args)

说明：按照printf风格格式输出字符串

regexp_extract(string subject, string pattern, int index)

说明：抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用’\s’将被匹配到s,’\s’才是匹配空格。

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

说明：按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉
代码解释：

select regexp_replace("foobar", "oo|ar", "");

结果为：fb

repeat(string str, int n)

说明：重复输出n次字符串str
代码解释：

select repeat("str",4);

结果为：strstrstrstr

sentences(string str, string lang, string locale)

说明：字符串str将被转换成单词数组
代码解释：

select sentences('Hello there! How are you?');

结果为：[["Hello","there"],["How","are","you"]]

split(string str, string pat)

说明：按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回
代码解释：

select split("adss,sdgah,dshaj,dash",",");

结果为：["adss","sdgah","dshaj","dash"]

substr(string|binary A, int start) substring(string|binary A, int start)

说明：对于字符串A,从start位置开始截取字符串并返回

trim(string A)

说明：将字符串A前后出现的空格去掉
代码解释：

select trim("    abc    ");

结果：abc

upper(string A)

说明：将字符串A中的字母转换成大写字母
代码解释：

select upper("string");

结果为：STRING

表生成函数

explode(array a)

说明：对于a中的每个元素，将生成一行且包含该元素

explode(ARRAY)

说明：每行对应数组中的一个元素

explode(MAP)

说明：每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值

posexplode(ARRAY)

说明：与explode类似，不同的是还返回各元素在数组中的位置

stack(INT n, v_1, v_2, …, v_k)

说明：把M列转换成N行，每行有M/N个字段，其中n必须是个常数

json_tuple(jsonStr, k1, k2, …)

说明：从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

parse_url_tuple(url, p1, p2, …)

返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY: