本文共 5666 字，大约阅读时间需要 18 分钟。

1、数学函数

1.1、四舍五入（保留n位小数）

round（a），round（a,b）

1.2、银行家舍入法：

broud（a），bround（a，b）

1.3、向下取整、向上取整

floor（a），ceil（a）

1.4、返回随机数（带有种子）

rand（），rand（seed）

1.5、对数函数

ln（a）、log10（a）、log2（a）、log（base，a）

1.6、幂函数

exp（a）、pow（a，p）

1.7、平方根函数

sqrt（a）

1.8、二进制、十六进制及其它进制函数

bin（12），hex（a），unhex（）、conv（num，from_base、to_base）

1.9、绝对值函数、取模函数

abs（a）、pmod（a，b）

1.10、余弦函数、反余弦函数、正弦函数、反正弦函数、正切函数、反正切函数

cos（a），acos（a），sin（a），asin（a），tan（a），atan（a）

1.11、弧度转角度，角度转弧度

degrees（a），radians（a）

1.12、取反函数、取原值函数、符号函数

negative（a）、positive（a），sign（a）

1.13、常数e、常数π pi

e（），pi（）

1.14、阶乘函数、立方根函数

factorial（a）、cbrt（a）

1.15、按位左移，按位右移，无符号按位右移

shiftleft（a，b）、shiftright（a，b），shiftrightunsigned（a，b）

1.16、求最大值、求最小值

greatest（T v1，T v2,...）,least（T v1，T v2，。。。）

2、集合函数

2.1、求map的长度、求数组的长度

size(Map<k.v>)，size（Array<T>）

2.2、获取map中的所有key，获取map中的所有value

map_keys(Map<k,v>)，map_values(Map<k,v>)

2.3、判断数组中是否包含某个值

array_contains(Array<T>,value)

2.4、对数组进行排序

sort_array(Array<T>)

3、类型转换函数

3.1、转换为二进制

binary（）

3.2、转换为任意类型

cast（expr as <type>）

4、日期函数

4.1、将秒值转换为指定格式日期、将指定格式日期转为时间戳

from_unixtime(1250111000,"yyyy-MM-dd")

from_unixtime(1250111000,“yyyy-MM-dd hh”)

from_unixtime(1250111000,"yyyy-MM-dd hh:mm")

from_unixtime(1250111000,"yyyy-MM-dd hh:mm:ss")

及

unix_timestamp('2009-03-20 11:30:01') = 1237573801

unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400

4.2、返回时间字符串的  日期、年份、季度、月份、周、天、小时、分钟、秒

to_date("1970-01-01 00:00:00") = "1970-01-01"

year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970

quarter('2015-04-08') = 2

 month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11

weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44

day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1

 hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12

minute('2009-07-30 12:58:59')=58

second（'2009-07-30 12:58:59'）=59

4.3、计算两个字符串日期的相隔天数、加上几天后的日期、减去几天后的日期

datediff('2009-03-01', '2009-02-27') = 2

date_add('2008-12-31', 1) = '2009-01-01'

date_sub('2008-12-31', 1) = '2008-12-30'

4.4、获取当前日期、获取当前日期之后几个月后的日期、获取当前时间戳

current_date、add_months(string start_date, int num_months)、current_timestamp

4.5、获取字符串日期月最后一天的日期、获取当前字符串日期的下一个星期几的日期、获取字符串日期的月初日期

last_day(string date)

next_day('2015-01-14', 'TU') = 2015-01-20

trunc("2016-06-26",“MM”)=2016-06-01

4.6、返回字符串日期相隔月数（可以是小数）

months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677

4.7、抽取时间字符串中的一部分

date_format("2016-06-22","MM-dd")=06-22

5、条件函数

5.1、if语句

hive (default)> select if(1==1,1,2) from t1 limit 2 ;OK_c011

嵌套if

hive (default)> select if(1==1,if(2==2,2,3),4) from t1 limit 2 ;OK_c02

5.2、如果值为空就返回默认值

nvl(T value, T default_value)

5.3、返回多个值中的第一个非空值

COALESCE (NULL,44,55)=44

5.4、case、when

CASE 4 WHEN 5  THEN 5 WHEN 4 THEN 4 ELSE 3 END   ### 将返回4

CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END  ####将返回5

5.5、判断是否为null

isnull( a )、isnotnull（a）

6、字符函数

6.1、返回字符串中第一个字符的ascii码

ascii('123')  = 49

6.2、base64加密解密：将二进制转换为64位的字符串，将64位的字符串转换为二进制

base64(binary('test')) = “bHh3MTIzNA==”

unbase64('bHh3MTIzNA==') = “test”

6.3、拼接字符串、以指定分隔符拼接字符串、以指定字符拼接数组元素

concat（a，b，c，。。。）

concat_ws(SEP,a,b,c,...)

concat_ws(SEP,array<string>)

6.4、encode字符串编码为二进制和decode二进制解码为字符串

decode(binary bin, string charset)  ## charset包括：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'

encode(string src, string charset)  ## charset包括：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'

6.5、从以逗号分隔的字符串中某个子串的位置

find_in_set(string str, string strList)

6.6、将数值转换为#，###，###.##格式的字符串，保留d位小数

format_number(number x, int d)

6.7、get_json_object

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') from test;

得到：amy

6.8、从文件中查找某个字符串、从字符串中查找子字符串，从字符串指定位置后查找子串出现的位置

in_file(string str, string filename)

instr(string str, string substr)

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

6.9、将字符串转为小写，将字符串小写转为大写

lower(string A)      ， lcase(string A)，upper(string A) ，ucase(string A)

6.10、对字符串左边使用指定字符进行填充直到指定长度，从右边填充，重复输出n次字符串，输出n个空格

lpad(string str, int len, string pad)，

rpad(string str, int len, string pad)，

repeat(string str, int n)，

space(int n)

6.11、去掉字符串前面的空格，去掉后面的空格，去掉字符串两边空格

ltrim(string A)，rtrim(string A)，trim(string A)

6.12、正则相关

regexp_extract(string subject, string pattern, int index)

regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)

split(string str, string pat)

6.13、反转字符串

reverse(string A)

6.14、将字符串转换成单词数组：

sentences('Hello there! How are you?') =( ("Hello", "there"), ("How", "are", "you") )

6.15、将字符串解析为map

str_to_map('k1:v1,k2:v2') = {"k2":"v2","k1":"v1"}

str_to_map('k1=v1,k2=v2',',','=') = {"k2":"v2","k1":"v1"}

6.16、从指定位置获取子字符串，从指定位置获取长度为n的字符串，获取第n个分隔符之前的字符串

substr(string|binary A, int start)

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

substring_index(string A, string delim, int count)

6.17、将字符串中的某个子串替换为其它字符串

translate("MOBIN","BIN","M")="MOM"

6.18、将字符串转为第一个字符大写

initcap(string A)

6.19、计算两个字符串之间的差异大小

levenshtein('kitten', 'sitting') = 3

6.20、将字符串转为soundex代码

soundex（'Miller'） = M460

7、聚合函数

7.1、count、sum、avg，其中count里可以有表达式

count(*), count(expr), count(DISTINCT expr[, expr...])

sum(col), sum(DISTINCT col)

avg(col), avg(DISTINCT col)

7.2、最小最大值

min(col),   max(col)

7.3、求指定列的方差、样本方差、标准差、样本标准差

variance(col)

var_samp(col)

stddev_pop(col)

stddev_samp(col)

7.4、求两列的协方差、样本协方差

covar_pop(col1, col2)

covar_samp(col1, col2)

7.5、求两列的相关系数

corr(col1, col2)

7.6、返回某列的p%百分位数

 percentile(BIGINT col, p)

8、表生成函数

8.1、explode的应用：对于array数组、map。及posexplode

explode(ARRAY)  # 得到n行，每一行包含array里的1个元素

explode(MAP)  # 得到n行，每一行添加key字段和value字段

posexplode(ARRAY)  # 得到n行，与explode类似，只不过还返回元素在数组中的位置

8.2、stack将M列转为N行

stack(INT n, v_1, v_2, ..., v_M)

8.3、解析json串，得到多个键值

json_tuple(jsonStr, k1, k2, ...)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

转载地址：https://blog.csdn.net/h_jlwg6688/article/details/108781396 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！