数学函数
-
round(double a[,int b])
//对 a 四舍五入
round(6.4) = 6
round(6.5) = 7
round(-6.5) = 7
//对 a 保留 b 位小数,并四舍五入
round(3.1415926,3)=3.142 -
format_number(double a,int b)
//对 a 保留 b 位小数,并四舍五入(与round()效果相同)
format(3.1415926,3)=3.142 -
floor(double a)
//对 a 向下取整
floor(3.1) = 3
floor(3.9) = 3
floor(-3.5) = -4 -
ceil(double a) / ceiling(double a)
//对 a 向上取整
ceil(-3.5) = -3
ceil(3.1) = 4 -
rand([int seed])
//返回一个随机一个 0~1 的随机 double 型数(每次执行的随机数都不相同)
rand()
//返回一个以 seed 为种子的 0~1 的随机 double 型数(种子相同,生成的随机数也相同)
rand(1) = 0.7308781907032909 -
pow(double a,double b) / power(double a,double b)
//返回 a 的 b 次幂
power(2,3) = 8 -
exp(double a)
//返回 e 的 a 次幂(a 可为小数)
exp(0) = 1
exp(1) = 2.718281828459045 -
ln(double a)
//返回以 e 为底 a 的对数(a 可为小数)
ln(1) = 0
ln(e()) = 1 -
类似的函数还有:
log10(double a);
log2(double a);
log(double base, double a); -
sqrt(double a)
//返回 a 的平方根
sqrt(4) = 2
注意:sqrt(-1) = null -
pmod(double a,double b)
//令 a 对 b 取模(a%b)
pmod(10,3) = 1
pmod(-10,3) = 2
pmod(10,-3) = -2 -
abs(double a)
//对 a 求绝对值
abs(-3.1) = 3.1 -
negative(double a)
//返回 a 的相反数
negative(3) = -3 -
greatest(T v1, …)
//返回最大值
greatest(2,7,-5) = 7 -
least(T v1, …)
//返回最小值
least(2,7,-5) = -5
日期函数
-
current_date()
//返回当前时间的年-月-日
current_date() = 2020-06-16 -
current_timestamp()
//返回当前时间的时间戳(年-月-日 时:分:秒.毫秒)
current_timestamp() = 2020-06-16 23:42:47.572 -
from_unixtime(bigint unixtime[,string format])
//返回时间的年-月-日 时:分:秒
from_unixtime(1250111000) = 2009-08-13 05:03:20//将时间转换为 format 格式
//format:yyyy-MM-dd hh:mm:ss / yyyy-MM-dd …
from_unixtime(1250111000,“yyyy-MM-dd”) = 2009-08-13
from_unixtime(1250111000,“y”) = 2009
from_unixtime(1250111000,“d”) = 13 //当月第 13 天
from_unixtime(1250111000,“D”) = 225 //本年第 225 天 -
unix_timestamp([string date[,string pattern]])
//获取本地时区下的当前的时间戳
unix_timestamp() = 1592321617 (每秒增加 1 )
//获取 给定时间字符串的时间戳
unix_timestamp(‘2020-6-16 23:36:00’) = 1592321760
//获取 给定时间字符串(匹配格式)的时间戳
unix_timestamp(‘2020-6-16’,‘yyyy-MM-dd’) = 1592236800
unix_timestamp(‘2020-6-16’,‘yyyy-MM-dd hh’) = null(格式不匹配返回 null) -
to_date(string date)
//返回时间字符串的日期部分(年-月-日)
to_date(“2020-6-16 22:10:23”) = 2020-6-16 -
year(string date)
//返回时间字符串的年份部分
year(“2020-6-16 22:10:23”) = 2020 -
quarter(string date / timestamp)(注意:as of hive 1.3.0)
//返回给定时间所在的季度
quarter(“2020-6-16”) = 2 -
month(string date)
//返回时间字符串的月份部分
month(“2020-6-16 22:10:23”) = 6 -
类似函数还有:
day(string date) / dayofmonth(string date);
hour(string date);
minute(string date);
second(string date);
weekofyear(string date); -
datediff(string enddate,string startdate)
//计算从 startdate 到 enddate 之间相差的天数(enddate-startdate)
datediff(“2020-6-16”,“2020-6-10”) = 6
datediff(“2020-6-10”,“2020-6-16”) = -6 -
date_add(string startdate,int days)
//在 startdate 的基础上再加上 days 天
date_add(“2020-6-10”,6) = 2020-06-16 -
date_sub(string startdate,int days)
//在 startdate 的基础上再减去 days 天
date_sub(“2020-6-10”,6) = 2020-06-04 -
last_day(string date)
//返回给定日期所在月份的最后一天
last_day(“2020-06-17 00:42:47.572”) = 2020-06-30 -
next_day(string startdate,string dayofweek)
//返回当前时间的下一个星期 dayofweek 的日期
next_day(“2020-06-17 00:42:47.572”,“we”) = 2020-06-24(当前是周三) -
months_between(string date1,string date2)
//返回 date1 与 date2 之间相差的月份(date1 - date2 ;返回值可以是小数)
months_between(“2020-06-17”,“2020-03-11”) = 3.19354839
months_between(“2020-06-17”,“2020-08-17”) = -2 -
trunc(string date,string format)
//返回给定时间的最开始的 format 的日期
//format:MONTH / MON / MM / YEAR /
trunc(“2020-06-17”,“MON”) = 2020-06-01 -
date_format(string date,string format)
//按指定格式返回 给定时间字符串
date_format(“2020-06-17 1:07:50.123”,“MM-dd”) = 06-17
date_format(“2020-06-17 1:07:50.123”,“d”) = 17 //当月第 17 天
date_format(“2020-06-17 1:07:50.123”,“D”) = 169 //本年第 169 天
date_format(“2020-06-17 1:07:50.123”,“EEEE”) = Wednesday
字符函数
-
concat(string a,string b, …)
//按前后顺序将字符串拼接成一个字符串
concat(“ab”,“bcd”,“def”) = abbcddef -
concat_ws(string sep,string a,string b, …)
//按前后顺序将字符串拼接成一个字符串,但之间用 sep(分隔符)进行分隔
concat_ws("\",“ab”,“bcd”,“def”) = ab\bcd\def
concat_ws(",",“ab”,“bcd”,“def”) = ab,bcd,def
concat_ws(",",“ab”,“bcd”,“def”) = ab,bcd,def //双引号中的 \ 表示转义 -
encode(string a,string charset)
//使用指定的字符集(charset)将字符串编码成二进制值
//charset:‘utf8’ / ‘UTF-8’, ‘US_ASCII’, ‘UTF-16’
encode(‘你好’,‘utf8’) = 你好 -
decode(binary a,string charset)
//使用指定的字符集(charset)将二进制值 a 编码成字符串
decode(encode(‘你好’,‘utf8’),‘utf-8’) = 你好 -
find_in_set(string a,string alist)
//返回以逗号分隔的字符串 alist 中 a 的位置(不是下标,而是第几个)
find_in_set(“gc”,“ab,de,gc,ll”) = 3 -
get_json_object(string jsonstring,string path)
//从指定路径上的 json 字符串中抽取出 json 对象,返回对象的 json 格式
get_json_object(’{“name”:“zs”,“age”:“40”}’,’$.name’) = zs -
instr(string str,string substr)
//查找字符串 str 中子字符串 substr 第一次出现的位置,查找失败:0,注意位置从 1 开始
instr(“abcdefgcd”,“cd”) = 3
instr(“abcdefgcd”,“cs”) = 0 -
length(string str)
//返回该字符串 str 的长度
length(“abcde”) = 5 -
locate(string substr,string str[,int pos])
//查找字符串 str 中的 pos 位置后字符串 substr 第一次出现的位置
locate(“cd”,“abcdefgcd”) = 3
locate(“cd”,“abcdefgcd”,4) = 8 -
lower(string a) / lcase(string a)
//将 a 中的所有字母转换为小写字母
lcase(“aDFGd”) = adfgd
lower(“aDFGd”) = adfgd -
upper(string a) / ucase(string a)
//将 a 中的所有字母转换为小写字母
upper(“aDFGd”) = ADFGD -
lpad(string str,int len,string pad)
//从左边开始,将 pad(可重复)填充至 str 字符串,到最终长度 len 为止
//若字符串 str 的长度大于 len,则将 str 右边超出部分去除
lpad(“abcdefg”,11,“avg”) = avgaabcdefg
lpad(“abcdefg”,3,“avg”) = abc -
rpad(string str,int len,string pad)
//从右边开始,将 pad(可重复)填充至 str 字符串,到最终长度 len 为止
//若字符串 str 的长度大于 len,则将 str 右边超出部分去除
rpad(“abcdefg”,11,“avg”) = abcdefgavga
rpad(“abcdefg”,3,“avg”) = abc -
trim(string a)
//去除字符串 a 两端的空格
trim(" abcd “) = “abcd”
//去左(空格)
ltrim(” abcd ") = “abcd "
//去右(空格)
ltrim(” abcd “) = " abcd” -
parse_url(string url,string part[,string key])
//获取地址字符串 url 中相应 part 的部分,如果有 key,则取 key 对应的值
//part:HOST / PATH / PROTOCOL / REF / QUERY (可以通过 key 取得对应 value)
parse_url(“https://www.baidu.com/php?tn=78000241”,“QUERY”) = “tn=78000241”
parse_url(“https://www.baidu.com/php?tn=78000241”,“HOST”) = www.baidu.com
parse_url(“https://www.baidu.com/php?tn=78000241”,“PROTOCOL”) = https -
regexp_extract(string str,string rule,int index)
//通过 rule 正则在 str 字符串中匹配出相应部分,并返回 index 对应的字段
regexp_extract(“https://www.baidu.com/php?tn=78000241”,"([a-z]+)😕/([a-z0-9.]+)/",0) = https://www.baidu.com/
regexp_extract(“https://www.baidu.com/php?tn=78000241”,"([a-z]+)😕/([a-z0-9.]+)/",1) = https -
regexp_replace(string str,string rule,string replace)
//通过 rule 正则在 str 字符串中匹配出相应部分,并用 replace 替换
regexp_replace(“2020/06/17”,"/","-") = 2020-06-17 -
repeat(string str,int n)
//重复输出 n 次字符串 str
repeat(“abc”,3) = abcabcabc
repeat(“abc”,0) = “” -
reverse(string str)
//反转字符串 str
reverse(“abcde”) = edcba -
sentences(string str)
//将字符串 str 转换成 单词数组,注意 !和 ?会分段
sentences(“Hello there! How are you?”) = [[“Hello”,“there”],[“How”,“are”,“you”]]
sentences(“Hello there.How are you?”) = [[“Hello”,“there.How”,“are”,“you”]] -
split(string str,string pat)
//按照正则 pat 来分割字符串,返回分割后的数组字符串
split(“2020-06-17”,"-") = [“2020”,“06”,“17”]
split(“2020-06-17 12:00:00”," ")[0] = 2020-06-17 -
substr(string str, int start) / substring(string str,int start)
//截取 str 字符串,从 start 开始到结束,并返回
substr(“2020-06-17 12:00:00”,12) = 12:00:00 -
substring_index(string str,string rule,int count)
//分隔符 rule 将 str 字符串分成多段,返回 count 个分隔符左边的内容,count为负,则取从右边起 count 个分隔符右边的内容(as of hive 1.3.0)
substring_index(“2020-06-17”,"-",2) = 2020-06 -
translate(string str,string rule,string replace)
//将 str 字符串中 rule 字段替换成 replace (replace 不会填充超出 rule 长度的内容),并返回
translate(“2020/06/17”,"/","-") = 2020-06-17
translate(“ab cd ef cd ab”,“cd”,“ccdd”) = ab cc ef cc ab
translate(“ab cd ef cd ab”,“cd”,“d”) = ab d ef d ab -
initcap(string str)
//将字符串 str 中开头和每个空格后的第一个字母替换成大写
initcap(“how are you!”) = How Are You!
类型转换函数
-
cast(expr as )
//将 expr 类型的内容 转换为 type 类型的内容
cast(“123” as int)+10 = 133 -
binary(string a / binary a)
//将输入值 a 转换成二进制(求 hash 码)
条件函数
-
if(boolean testCondition,T trueValue,T falseValue)
//如果 testCondition 为 true,返回 trueValue,否则返回 falseValue
if(1<2,“真”,“假”) = 真 -
nvl(T value,T defaultValue)
//若 value 为 null,返回 defaultValue ,否则返回其本身 value(替换所有的 null) -
coalesce(T v1, …)
//返回数组中第一个非 null 的值
coalesce(null,null,12,13,null) = 12 -
case [a] when b then c [when b2 then c2]* [else d] end
//a存在:a=b ,return c;a=b2,return c2;…; 否则 return d;
//a不存在:b 为 true,return c;b2 为 true,return c2;…;否则 return d;
case 1 when 1 then 1 when 2 then 2 else 3 end = 1
case when 5<5 then “<5” when 5>5 then “>5” else “=5” end = “=5” -
isnull(a)
//判断 a 是否为 null
isnull(null) = true -
isnotnull(a)
//判断 a 是否不为 null
isnotnull(null) = false
聚合函数
注意:字段项前面加 DISTINCT,即对字段各行去重
-
count(expr)
//统计字段总行数,包括值为 null 的行 -
count(DISTINCT expr)
//统计字段非 null 且 去重后的总行数 -
sum(col)
//对指定列求和 -
avg(col)
//对指定列求平均值 -
min(col)
//对指定列求最小值 -
max(col)
//对指定列求最大值 -
variance(col) / var_pop(col)
//对指定列求方差 -
var_samp(col)
//对指定列求样本方差 -
stddev_pop(col)
//对指定列求标准偏差 -
stddev_samp(col)
//对指定列求样本标准偏差 -
covar_pop(col1,col2)
//对指定列求协方差 -
covar_samp(col1,col2)
//对指定列求样本协方差