一、数学函数
1、取整函数: round
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | round(double a) | 返回double类型的整数值部分（遵循四舍五入） |
2.例程
hive> select round(10.235);
#----
10.0
#----
2、指定精度取整函数: round
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | round(double a, int d) | 返回指定精度d的double类型 |
2.例程
hive> select round(3.141592,4);
#-----
3.1416
#-----
3、向下取整函数: floor
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | floor(double a) | 返回等于或者小于该double变量的最大的整数 |
2.例程
hive> select floor(3.1415926);
#--
3
#--
hive> select floor(-10.2);
#--
-11
#--
4、向上取整函数: ceil
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | ceil(double a) | 返回等于或者大于该double变量的最小的整数 |
2.例程
hive> select ceil(10.1);
#--
11
#--
5、向上取整函数: ceiling
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | ceiling(double a) | 返回等于或者大于该double变量的最小的整数 |
2.例程
hive> select ceiling(3.1415926);
#--
4
#--
6、取随机数函数: rand
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | rand(),rand(int seed) | 返回一个double型0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 |
2.例程
hive> select rand();
#-----------------
0.5577432776034763
#-----------------
hive> select rand();
#-----------------
0.6638336467363424
#-----------------
#当传入种子后，数据就固定了
hive> select rand(100);
#-----------------
0.7220096548596434
#-----------------
hive> select rand(100);
#-----------------
0.7220096548596434
#-----------------
7、自然指数函数: exp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | exp(double a) | 返回自然对数e的a次方,a可为小数 |
2.例程
hive> select exp(3);
#-----------------
20.085536923187668
#-----------------
8、自然对数函数: ln
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | ln(double a) | 返回a的自然对数，a可为小数 |
2.例程
hive> select ln(7.38905609893065);
#--
2.0
#--
9、以10为底对数函数: log10
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | log10(double a) | 返回以10为底的a的对数，a可为小数 |
2.例程
hive> select log10(100);
#--
2.0
#--
10、以2为底对数函数: log2
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | log2(double a) | 返回以2为底的a的对数，a可为小数 |
2.例程
hive> select log2(8);
#--
3.0
#--
11、对数函数: log
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | log(double base, double a) | 返回以base为底的a的对数，base 与 a都是double类型 |
2.例程
hive> select log(4,256);
#--
4.0
#--
12、幂运算函数: pow
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | pow(double a, double p), power(double a, double p) | 返回a的p次幂 |
2.例程
hive> select pow(2,4);
#---
16.0
#---
13、开平方函数: sqrt
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | sqrt(double a) | 返回a的平方根 |
2.例程
hive> select sqrt(16);
#--
4.0
#--
14、 二进制函数: bin
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | bin(BIGINT a) | 返回a的二进制代码表示,，a为BIGINT类型 |
2.例程
hive> select bin(7);
#--
111
#--
15、十六进制函数: hex
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | hex(BIGINT a),hex(string a) | 如果变量是int类型，那么返回a的十六进制表示；如果变量是string类型，则返回该字符串的十六进制表示 |
2.例程
hive> select hex(17);
#--
11
#--
hive> select hex(‘abc’);
#-----
616263
#-----
16、反转十六进制函数: unhex
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | unhex(string a) | 返回该十六进制字符串所代码的字符串,hex的逆方法 |
2.例程
hive> select unhex(‘616263’);
#--
abc
#--
17、进制转换函数: conv
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | conv(BIGINT num, int from_base, int to_base),conv(STRING num, int from_base, int to_base) | 将bigint/string数值num从from_base进制转化到to_base进制 |
2.例程
hive> select conv(17,10,16);
#--
11
#--
hive> select conv(17,10,2);
#----
10001
#----
18、绝对值函数: abs
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double or int | abs(double a),abs(int a) | 返回数值a的绝对值 |
2.例程
hive> select abs(-3.9);
#--
3.9
#--
19、正取余函数: pmod
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| int or double | pmod(int a, int b),pmod(double a, double b) | 返回正的a除以b的余数 |
2.例程
hive> select pmod(9,4);
#--
1
#--
hive> select pmod(-9,4);
#--
3
#--
20、正弦函数: sin
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | sin(double a) | 返回a的正弦值 |
2.例程
hive> select sin(0.8);
#-----------------
0.7173560908995228
#-----------------
21、反正弦函数: asin
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | asin(double a) | 返回a的反正弦值 |
2.例程
hive> select asin(0.7173560908995228);
#--
0.8
#--
22、余弦函数: cos
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | cos(double a) | 返回a的余弦值 |
2.例程
hive> select cos(0.9);
#-----------------
0.6216099682706644
#-----------------
23、反余弦函数: acos
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | acos(double a) | 返回a的反余弦值 |
2.例程
hive> select acos(0.6216099682706644);
#--
0.9
#--
24、正切函数:tan
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | tan(double a) | 返回a的正切值 |
2.例程
hive> select tan(0.8);
#-----------------
1.0296385570503641
#-----------------
25、反正切函数:atan
1.函数描述
| 返回值 | 语法描述 | 功能描述 |
|---|
| double | atan(double a) | 返回a的反正切值 |
2.例程
hive> select atan(1.0296385570503641);
#--
0.8
#--
26、弧度值转换角度值:degrees
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | degrees(double a) | 返回a的角度值 |
2.例程
hive> select degrees(1);
#----------------
57.29577951308232
#----------------
27、角度值转换成弧度值:radians
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | radians(double a) | 返回a的弧度值 |
2.例程
hive> select radians(57.29577951308232);
#--
1.0
#--
28、positive函数: positive
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int or double | positive(int a), positive(double a) | 返回a的正数 |
2.例程
hive> select positive(-10);
#---
10
#---
29、negative函数: negative
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int or double | negative(int a), negative(double a) | 返回a的负数 |
2.例程
hive> select negative(5);
#---
-5
#---
30、判断正负函数:sign
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | sign(double a) | 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 |
2.例程
hive> select sign(-4);
#---
-1.0
#---
31、数学e函数:e
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | e() | 数学常数e |
2.例程
hive> select e();
#----------------
2.718281828459045
#----------------
32、数学pi函数:pi
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | pi() | 圆周率π |
2.例程
hive> select pi();
#----------------
3.141592653589793
#----------------
33、阶乘函数:factorial
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | factorial(int a) | 求a的阶乘 |
2.例程
hive> select factorial(5);
#----
120
#----
34、立方根函数:cbrt
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | cbrt(double a) | 求a的立方根 |
2.例程
hive> select cbrt(27);
#--
3
#--
35、左移函数:shiftleft
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int bigint | shiftleft(BIGINT a, int b) | 按位左移 |
2.例程
hive> select shiftleft(4,2);
#--
16
#--
36、右移函数:shiftright
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int bigint | shiftright(BIGINT a, int b) | 按位右移 |
2.例程
hive> select shiftright(16,1);
#--
8
#--
37、无符号按位右移函数:shiftrightunsigned
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int bigint | shiftrightunsigned(BIGINT a, int b) | 无符号按位右移（<<<） |
2.例程
hive> select shiftrightunsigned(32,2)
#--
8
#--
38、求最大值函数:greatest
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | greatest(T v1, T v2, …) | 求最大值 |
2.例程
hive> select greatest(1,2,3);
#--
3
#--
39、求最小值函数:least
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | least(T v1, T v2, …) | 求最小值 |
2.例程
hive> select least(1,2,3);
#--
1
#--
40、银行家舍入法函数:bround
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | bround(double a) | 银行家舍入法（1-4：舍，6-9：进，5->前位数是偶：舍，5->前位数是奇：进） |
2.例程
hive> select bround(3.5)
#--
3.0
#--
41、银行家精确舍入法函数:bround
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | bround(double a，int d) | 银行家舍入法,保留d位小数 |
2.例程
hive> select bround(3.15，1)
#---
3.1
#---
hive> select bround(3.25，1)
#---
3.3
#---
二、日期函数
1、UNIX时间戳转日期函数:from_unixtime
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | from_unixtime(bigint unixtime[, string format]) | 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式——format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等） |
2.例程
hive> select from_unixtime(1250111000,“yyyy-MM-dd”);
#---------
2022-01-06
#---------
2、获取当前UNIX时间戳函数:unix_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | unix_timestamp() | 获得当前时区的UNIX时间戳 |
2.例程
hive> select unix_timestamp();
#---------
1594648263
#---------
3、日期转UNIX时间戳函数:unix_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | unix_timestamp(string date) | 将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳。如果转化失败，则返回0。 |
2.例程
hive> select unix_timestamp(‘2000-1-1 8:8:8’);
#--------
946685288
#--------
4、指定格式日期转UNIX时间戳函数:unix_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | unix_timestamp(string date, string pattern) | 将指定时间字符串格式字符串转换成Unix时间戳。如果转化失败，则返回0。 |
2.例程
hive> select unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’);
#---------
1237478400
#---------
5、日期时间转日期函数:to_date
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | to_date(string timestamp) | 返回日期时间字段中的日期部分 |
2.例程
hive> select to_date(‘1970-01-01 23:00:00’);
#---------
1970-01-01
#---------
6、日期转年函数: year
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | year(string date) | 返回日期中的年 |
2.例程
hive> select year(“1970-01-01 00:00:00”);
#---
1970
#---
7、日期转月函数: month
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | month (string date) | 返回日期中的月份 |
2.例程
hive> select month(“1970-11-01”);
#--
11
#--
8、日期转天函数: day
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | day (string date) | 返回日期中的天 |
2.例程
hive> select day(“1970-11-01”);
#--
1
#--
9、日期转小时函数: hour
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | hour (string date) | 返回日期中的小时 |
2.例程
hive> select hour(‘2009-07-30 12:58:59’);
#--
12
#--
10、日期转分钟函数: minute
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | minute (string date) | 返回日期中的分钟 |
2.例程
hive> select minute(‘2011-12-08 10:03:01’);
#--
3
#--
11、日期转秒函数: second
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | second (string date) | 返回日期中的秒 |
2.例程
hive> select second(‘2011-12-08 10:03:01’);
#--
1
#--
12、日期转周函数:weekofyear
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | weekofyear (string date) | 返回时间字符串位于一年中的第几个周内 |
2.例程
hive> select weekofyear(‘2011-12-08 10:03:01’);
#---
49
#---
13、日期比较函数: datediff
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | datediff(string enddate, string startdate) | 返回结束日期减去开始日期的天数 |
2.例程
hive> select datediff(‘2012-12-08’,‘2012-05-09’);
#--
213
#--
14、日期增加函数: date_add
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | date_add(string startdate, int days) | 返回开始日期startdate增加days天后的日期 |
2.例程
hive> select date_add(‘2012-12-08’,10);
#---------
2012-12-18
#---------
15、日期减少函数: date_sub
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | date_sub (string startdate, int days) | 返回开始日期startdate减少days天后的日期 |
2.例程
hive> select date_sub(‘2012-12-08’,10);
#---------
2012-11-28
#---------
16、转化成指定的时区下时间戳函数: from_utc_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| timestamp | from_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳 |
2.例程
hive> select from_utc_timestamp(‘1970-01-01 08:00:00’,‘PST’);
#------------------
1970-01-01 00:00:00
#------------------
17、转化成UTC下的时间戳函数: to_utc_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| timestamp | to_utc_timestamp(timestamp, string timezone) | 如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳 |
2.例程
hive> select to_utc_timestamp(‘1970-01-01 00:00:00’,‘PST’);
#------------------
1970-01-01 08:00:00
#------------------
18、当前时间日期函数:current_date
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| date | current_date() | 返回当前时间日期 |
2.例程
hive> select current_date;
#---------
2022-01-06
#---------
19、当前时间日期函数:current_timestamp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| timestamp | current_timestamp() | 返回当前时间戳 |
2.例程
hive> select current_timestamp();
#----------------------
2022-01-06 22:52:11.309
#----------------------
20、月份增加函数:add_months
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | add_months(string start_date, int num_months) | 返回当前时间下再增加num_months个月的日期 |
2.例程
hive> select add_months(‘1996-10-21’,10);
#---------
1997-08-21
#---------
21、最后一天的日期函数:last_day
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | last_day(string date) | 返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss） |
2.例程
hive> select last_day(current_date());
#---------
2020-07-31
#---------
22、下一个星期X所对应的日期函数:next_day
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | next_day(string start_date, string day_of_week) | 返回当前时间的下一个星期X所对应的日期 如：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 以2015-01-14为开始时间，其下一个星期二所对应的日期为2015-01-20 |
2.例程
hive> select next_day(current_date(),‘su’);
#---------
2020-07-19
#---------
23、时间的最开始年份或月份函数:trunc
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | trunc(string date, string format) | 返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY |
2.例程
hive> select trunc(current_date(),‘MM’);
#---------
2020-07-01
#---------
24、相差的月份函数:months_between
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | months_between(date1, date2) | 返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1 |
2.例程
hive> select months_between(current_date(),‘2020-5-13’);
#--
2.0
#--
25、指定格式返回时间函数:date_format
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | date_format(date/timestamp/string ts, string fmt) | 按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22 |
2.例程
hive> select date_format(current_date(),‘MM.dd’);
#----
07.13
#----
26、当前星期函数:dayofweek
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | dayofweek(date) | 返回日期那天的周几 |
2.例程
hive> select dayofweek(current_date());
#--
2
#--
27、季节函数:quarter
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | quarter(date/timestamp/string) | 返回当前时间属性哪个季度 如quarter(‘2015-04-08’) = 2 |
三、条件函数
1、If函数: if
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | if(boolean testCondition, T valueTrue, T valueFalseOrNull) | 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull（valueTrue，valueFalseOrNull为泛型） |
2.例程
hive> select if(1=1,100,200);
#---
100
#---
2、空查找函数: nvl
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | nvl(T value, T default_value) | 如果value值为NULL就返回default_value,否则返回value |
2.例程
hive> select nvl(null,5);
#--
5
#--
3、非空查找函数: COALESCE
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | COALESCE(T v1, T v2,…) | 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL |
2.例程
hive> select COALESCE (NULL,44,55);
#--
44
#--
4、条件判断函数：CASE
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END | 如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f |
2.例程
hive> select CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END;
#--
4
#--
5、条件判断函数：CASE
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| T | CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END | 如果a为TRUE,则返回b；如果c为TRUE，则返回d；否则返回e |
2.例程
hive> select CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END;
#--
5
#--
6、空值判断函数：isnull
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| boolean | isnull( a ) | 如果a为null就返回true，否则返回false |
2.例程
hive> select isnull(5);
#----
false
#----
7、非空值判断函数：isnotnull
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| boolean | isnotnull ( a ) | 如果a为非null就返回true，否则返回false |
2.例程
hive> select isnotnull(5);
#---
true
#---
四、字符函数
1、首字符ascii函数：ascii
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | ascii(string str) | 返回字符串str第一个字符的ascii码 |
2.例程
hive> select ascii(‘abcde’);
#---
97
#---
2、转换成64位的字符串：base64
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | base64(binary bin) | 将二进制bin转换成64位的字符串 |
3、字符串连接函数：concat
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | concat(string A, string B…) | 返回输入字符串连接后的结果，支持任意个输入字符串 |
2.例程
hive> select concat(‘abc’,‘def’);
#-----
abcdef
#-----
4、字符串连接函数：context_ngrams
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array> | context_ngrams(array, array, int K, int pf) | 与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列，具体看StatisticsAndDataMining(这里的解释更易懂) |
5、带分隔符字符串连接函数：concat_ws
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | concat_ws(string SEP, string A, string B…) | concat_ws(string SEP, array) | 返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 |
2.例程
hive> select concat_ws(’,’,‘abc’,‘def’,‘gh’);
#---------
abc,def,gh
#---------
6、指定的字符集将二进制值bin解码成字符串：decode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | decode(binary bin, string charset) | 使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任意输入参数为NULL都将返回NULL |
7、指定的字符集charset将字符串编码成二进制值：encode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| binary | encode(string src, string charset) | 使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’，如果任一输入参数为NULL都将返回NULL |
8、集合查找函数:find_in_set
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | find_in_set(string str, string strList) | 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0，如果任一参数为NULL将返回NULL |
2.例程
hive> select find_in_set(‘ab’,‘ef,ab,de’);
#--
2
#--
9、将数值X转换成"#,###,###.##"格式字符串：format_number
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | format_number(number x, int d) | 将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数 |
2.例程
hive> select format_number(123345.65545,2);
#---------
123,345.66
#---------
10、json解析函数：get_json_object
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | get_json_object(string json_string, string path) | 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL。注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制 |
2.例程
hive> select get_json_object(’{“store”:
> {“fruit”:[{“weight”:8,“type”:“apple”},{“weight”:9,“type”:“pear”}],
> “bicycle”:{“price”:19.95,“color”:“red”}
> },
> “email”:“amy@only_for_json_udf_test.net”,
> “owner”:“amy”
> }
> ‘,’$.owner’);
#---
amy
#---
11、文件数据与字符串str匹配： in_file
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| boolean | in_file(string str, string filename) | 如果文件名为filename的文件中有一行数据与字符串str匹配成功就返回true |
12、查找字符串str中子字符串substr出现的位置：instr
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | instr(string str, string substr) | 查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的 |
2.例程
hive> select instr(‘dvfgefggdgaa’,‘aa’);
#--
11
#--
13、字符串长度函数：length
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | length(string A) | 返回字符串A的长度 |
2.例程
hive> select length(‘abcedfg’);
#--
7
#--
14、第一次出现的位置：locate
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 |
2.例程
hive> select locate(‘aa’,‘aabbedfaad’,2);
#--
8
#--
15、字符串转小写函数：lower,lcase
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | lower(string A) lcase(string A) | 返回字符串A的小写格式 |
2.例程
hive> select lower(‘abSEd’);
#----
absed
#----
16、左补足函数：lpad
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | lpad(string str, int len, string pad) | 将str进行用pad进行左补足到len位,从左边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
2.例程
hive> select lpad(‘abc’,10,‘td’);
#---------
tdtdtdtabc
#---------
注意：与GP，ORACLE不同，pad不能默认
17、左边去空格函数：ltrim
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | ltrim(string A) | 去掉字符串A前面的空格 |
2.例程
hive> select ltrim(’ abc fg ');
#-----
abc fg
#-----
18、返回出现次数TOP K的的子序列：ngrams
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array> | ngrams(array, int N, int K, int pf) | 返回出现次数TOP K的的子序列,n表示子序列的长度，具体看StatisticsAndDataMining (这里的解释更易懂) |
19、URL解析函数：parse_url
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | parse_url(string urlString, string partToExtract [, stringkeyToExtract]) | 返回从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’ |
2.例程
hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’);
#-----------
facebook.com
#-----------
hive> select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’,‘k1’);
#--
v1
#--
20、printf风格格式输出字符串：printf
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | printf(String format, Obj… args) | 按照printf风格格式输出字符串 |
2.例程
hive> select printf(‘abfhg’);
#----
abfhg
#----
21、正则表达式解析函数：regexp_extract
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | regexp_extract(string subject, string pattern, int index) | 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。注意些预定义字符的使用，如第二个参数如果使用’s’将被匹配到s,’s’才是匹配空格 |
2.例程
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2);
#---
bar
#---
hive> select regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 0);
#--------
foothebar
#--------
hive> select regexp_extract(‘hello,world’,’^((w+),(w+))$’,2);
#----
hello
#----
hive> select regexp_extract(‘hello,world’,’(w+),(w+)’,2);
#----
world
#----
注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则。
22、正则表达式替换函数：regexp_replace
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | regexp_replace(string A, string B, string C) | 按照Java正则表达式PATTERN将字符串INTIAL_STRING中符合条件的部分成REPLACEMENT所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace(“foobar”, “oo |
2.例程
hive> select regexp_replace(‘hello,world’,‘o|l’,‘e’);
#----------
heeee,wered
#----------
23、重复字符串函数：repeat
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | repeat(string str, int n) | 返回重复n次后的str字符串 |
2.例程
hive> select repeat(‘abc’,5);
#--------------
abcabcabcabcabc
#--------------
24、字符串反转函数：reverse
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | reverse(string A) | 返回字符串A的反转结果 |
2.例程
hive> select reverse(‘abc’);
#--
cba
#--
25、右补足函数：rpad
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | rpad(string str, int len, string pad) | 从右边开始对字符串str使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分 |
2.例程
hive> select rpad(‘abc’,10,‘td’);
#---------
abctdtdtdt
#---------
26、右边去空格函数：rtrim
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | rtrim(string A) | 去除字符串右边的空格 |
2.例程
hive> select rtrim(’ abc ');
#---
abc
#---
27、字符串str将被转换成单词数组：sentences
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | sentences(string str, string lang, string locale) | 字符串str将被转换成单词数组，如：sentences(‘Hello there! How are you?’) =( (“Hello”, “there”), (“How”, “are”, “you”) ) |
2.例程
hive> select sentences(‘Hello there! How are you?’);
#--------------------------------------
[[“Hello”,“there”],[“How”,“are”,“you”]]
#--------------------------------------
28、空格字符串函数：space
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | space(int n) | 返回n个空格 |
2.例程
hive> select length(space(10));
#--
10
#--
29、分割字符串函数: split
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | split(string str, string pat) | 按照pat字符串分割str，会返回分割后的字符串数组 |
2.例程
hive> select split(‘abc|cde,def kyy’,’[|, ]’);
#------------------------
[“abc”,“cde”,“def”,“kyy”]
#------------------------
hive> select split(‘abtcdtef’,‘t’);
#---------------
[“ab”,“cd”,“ef”]
#---------------
30、字符串str按照指定分隔符转换成Map: split
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| map | str_to_map(text[, delimiter1, delimiter2]) | 将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"=" |
31、字符串截取函数：substr,substring
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | substr(string A, int start),substring(string A, int start) | 返回字符串A从start位置到结尾的字符串 |
2.例程
hive> select substr(‘abcde’,3);
#---
cde
#---
hive> select substr(‘abcde’,-1); //(和ORACLE相同）
#--
e
#--
32、字符串截取函数：substr,substring
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | substr(string A, int start, int len),substring(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 |
2.例程
hive> select substr(‘abcde’,3,2);
#--
cd
#--
33、截取第count分隔符之前的字符串：substring_index
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | substring_index(string A, string delim, int count) | 截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取 |
34、字符串替换成to中的字符串：substring_index
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | translate(string|char|varchar input, string|char|varchar from, string|char|varchar to) | 将input出现在from中的字符串替换成to中的字符串 如：translate(“MOBIN”,“BIN”,“M”)=“MOM” |
2.例程
hive> select translate(“MOBIN”,“BIN”,“M”);
#---
MOM
#---
35、去空格函数：trim
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | trim(string A) | 去除字符串两边的空格 |
2.例程
hive> select trim(’ abc dce’);
#------
abc dce
#------
36、将64位的字符串转换二进制值：trim
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| binary | unbase64(string str) | 将64位的字符串转换二进制值 |
37、字符串转大写函数：upper,ucase
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | upper(string A) ucase(string A) | 将字符串A中的字母转换成大写字母 |
2.例程
hive> select upper(‘abSEd’);
#----
ABSED
#----
38、首字母大写函数：initcap
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | initcap(string A) | 将字符串A转换第一个字母大写其余字母的字符串 |
2.例程
hive> select initcap(‘abcd def’);
#-------
Abcd Def
#-------
39、两个字符串之间的差异大小： levenshtein
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | levenshtein(string A, string B) | 计算两个字符串之间的差异大小 如：levenshtein(‘kitten’, ‘sitting’) = 3 |
2.例程
hive> select levenshtein(‘kitten’, ‘sitting’);
#---
3
#---
40、字符串转换成soundex字符串：soundex
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| string | soundex(string A) | 将普通字符串转换成soundex字符串 |
五、聚合函数
1、个数统计函数: count
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| bigint | count(*), count(expr), count(DISTINCT expr[, expr…]) | count(*)统计检索出的行的个数，包括NULL值的行；count(expr)返回指定字段的非空值的个数；count(DISTINCTexpr[, expr_.])统计提供非NULL且去重后的expr表达式值的行数 |
2、总和统计函数: sum
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | sum(col), sum(DISTINCT col) | sum(col)统计结果集中col的相加的结果；sum(DISTINCT col)统计结果中col不同值相加的结果 |
3、平均值统计函数: avg
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | avg(col), avg(DISTINCT col) | avg(col)统计结果集中col的平均值；avg(DISTINCT col)统计结果中col不同值相加的平均值 |
4、最小值统计函数: min
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | min(col) | 统计结果集中col字段的最小值 |
5、最大值统计函数: max
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | max(col) | 统计结果集中col字段的最大值 |
6、非空集合总体变量函数:var_pop
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | variance(col), var_pop(col) | 统计结果集中col非空集合的总体变量（忽略null），（求指定列数值的方差） |
7、非空集合样本变量函数:var_samp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | var_samp (col) | 统计结果集中col非空集合的样本变量（忽略null）（求指定列数值的样本方差） |
8、总体标准偏离函数:stddev_pop
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | stddev_pop(col) | 该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同（求指定列数值的标准偏差） |
9、样本标准偏离函数:stddev_samp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | stddev_samp (col) | 该函数计算样本标准偏离，（求指定列数值的样本标准偏差） |
10、协方差函数:covar_pop
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | covar_pop(col1, col2) | 求指定列数值的协方差 |
11、样本协方差函数:covar_samp
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | covar_samp(col1, col2) | 求指定列数值的样本协方差 |
12、相关系数函数:corr
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | corr(col1, col2) | 返回两列数值的相关系数 |
13、中位数函数:percentile
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | percentile(BIGINT col, p) | 求准确的第pth个百分位数，p必须介于0和1之间，但是col字段目前只支持整数，不支持浮点数类型 |
14、中位数函数:percentile
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | percentile(BIGINT col, array(p1 [, p2]…)) | 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数 |
2.例程
select percentile(score,<0.2,0.4>) from lxw_dual；取0.2，0.4位置的数据
15、近似中位数函数:percentile_approx
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| double | percentile_approx(DOUBLE col, p [, B]) | 求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数 |
16、近似中位数函数:percentile_approx
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B]) | 功能和上述类似，之后后面可以输入多个百分位数，返回类型也为array，其中为对应的百分位数。 |
17、直方图:histogram_numeric
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | histogram_numeric(col, b) | 以b为基准计算col的直方图信息 |
hive> select histogram_numeric(100,5) 、
#--------------------
[{“x”:100.0,“y”:1.0}]
#--------------------
六、表生成函数
1、explode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| Array Type | explode(array a) | 对于a中的每个元素，将生成一行且包含该元素 |
2、explode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| N rows | explode(ARRAY) | 每行对应数组中的一个元素 |
3、explode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| N rows | explode(MAP) | 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值 |
4、posexplode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| N rows | posexplode(ARRAY) | 与explode类似，不同的是还返回各元素在数组中的位置 |
5、posexplode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| N rows | stack(INT n, v_1, v_2, …, v_k) | 把M列转换成N行，每行有M/N个字段，其中n必须是个常数 |
6、posexplode
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| tuple | json_tuple(jsonStr, k1, k2, …) | 从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值 |
7、parse_url_tuple
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| tuple | parse_url_tuple(url, p1, p2, …) | 返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,…是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY: |
8、parse_url_tuple
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| tuple | inline(ARRAY) | 将结构体数组提取出来并插入到表中 |
七、类型转换函数
1、转换成二进制: binary
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| binary | binary(string|binary) | 将输入的值转换成二进制 |
2、expr转换成type类型: cast
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| Expected “=” to follow “type” | cast(expr as ) | 将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL |
八、集合函数
1、Map类型长度函数: size(Map
)
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | size(Map) | 返回map类型的长度 |
2.例程
hive> select size(map(‘100’,‘tom’,‘101’,‘mary’));
#--
2
#--
2、array类型长度函数: size(Array)
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| int | size(Array) | 求数组的长度 |
2.例程
hive> select size(array(‘100’,‘101’,‘102’,‘103’));
#--
4
#--
3、key函数
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | map_keys(Map) | 返回map中的所有key |
4、value函数
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | map_keys(Map) | 返回map中的所有value |
5、包含value函数
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| boolean | array_contains(Array, value) | 如该数组Array包含value返回true。，否则返回false |
6、排序函数
1.函数描述
| 返回值 | 语法结构 | 功能描述 |
|---|
| array | sort_array(Array) | 按自然顺序对数组进行排序并返回 |
