hive 常用函数汇总及详细解释

数学函数

• 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)
  //对指定列求样本协方差