Hive SQL 内置函数全解

Hive SQL 内置支持绝大多数 SQL 的内置函数，其中主要包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等。

1条件函数
2日期函数
3数学函数
4聚合函数
5字符串函数
6表生成函数

条件函数

Hive SQL 支持如下几种条件函数：

Hive SQL 内置条件函数
函数	返回值	描述
IF(boolean testCondition, T valueTrue, T valueFalseOrNull)	T	如果 testCondition 为 true，就返回 valueTrue，否则返回 valueFalseOrNull；其中 valueTrue 和 valueFalseOrNull 为泛型。
CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END*	T	如果 a=ture，就返回 b，c=ture 时，就返回 d，否则返回 e。
CASE a WHEN b THEN c [WHEN d THEN e] [ELSE f] END*	T	如果 a=b，就返回 c，若 a=d 就返回 e，否则返回 f。
NVL(T value, T default_value)	T	如果 value 值为 NULL，就返回 default_value，否则返回自身 value。
COALESCE(T v1, T v2, ...)	T	返回第一个非 NULL 的值，如果全部都为 NULL，就返回 NULL。
ISNULL(a)	boolean	如果 a 为 NULL，就返回 true，否则返回 false。
ISNOTNULL(a)	boolean	如果 a 为非 NULL，就返回 true，否则返回 false。

日期函数

Hive SQL 支持如下几种日期函数：

Hive SQL 内置日期函数
函数	返回值	描述
DATEDIFF(String enddate, String startdate)	int	返回两个日期的相差天数。

数学函数

Hive SQL 支持如下几种数学函数：

Hive SQL 内置数学函数
函数	返回值	描述
PMOD(INT a, INT b)	int 或 double	参数 a 对参数 b 取模。

聚合函数

Hive SQL 支持如下几种聚合函数：

Hive SQL 内置聚合函数
函数	返回值	描述
COUNT(*) COUNT(expr) COUNT(DISTINCT expr[, expr...])	`BIGINT`	`COUNT()`：统计总行数，包括含有 `NULL` 值的行； `COUNT(expr)`：统计提供非 `NULL` 的 expr* 表达式值的行数； `COUNT(DISTINCT expr[, expr ...])`：统计提供非 `NULL` 且去重后的 expr 表达式值的行数。
SUM(col) SUM(DISTINCT col)	Double	SUM(col)：统计指定列的总和； SUM(DISTINCT col)：统计去重后指定列的总和。
AVG(col) AVG(DISTINCT col)	Double	AVG(col)：统计指定列的平均值； AVG(DISTINCT col)：统计去重后指定列的平均值。
MIN(col)	Double	求指定列的最小值。
MAX(col)	Double	求指定列的最大值。
VARIANCE(col) VAR_POP(col)	Double	求指定列数值的方差。
VAR_SAMP(col)	Double	求指定列数值的样本方差。
STDDEV_POP(col)	Double	求指定列数值的标准偏差。
STDDEV_SAMP(col)	Double	求指定列数值的样本标准偏差。
COVAR_POP(col1, col2)	Double	求指定列数值的协方差。
COVAR_SAMP(col1, col2)	Double	求指定列数值的样本协方差。
CORR(col1, col2)	Double	返回两列数值的相关系数。
PERCENTILE(col, p)	Double	返回指定列（col）的 p% 分位数。
COLLECT_SET(col)	`Array`	聚合指定列，并去重。

字符串函数

Hive SQL 支持如下几种聚合函数：

Hive SQL 内置字符串函数
函数	返回值	描述
GET_JSON_OBJECT(string json_string, string path)	string	根据指定的 json 路径从 json 字符串中提取 json 对象，并返回提取的 json 对象的 json 字符串。如果输入的 json 字符串非法，它将返回 null。
ASCII(string str)	int	返回 str 的第一个字符的 ASCII 值。
BASE64(binary bin)	string	将二进制参数转换为 BASE64 的字符串，从 0.12.0 版本开始支持。
CHARACTER_LENGTH(string str)	int	返回 str 中包含的字符数，函数 char_length 是该函数的简写，2.2.0 版本开始支持。
CHR(bigint\|double A)	string	返回具有与参数 A 等效的二进制值的 ASCII 字符。
CONCAT(string A, string B, ...)	string	返回指定参数的连接字符串，此函数可以接受任意数量的输入字符串。
CONTEXT_NGRAMS(array<array<string>>, array<string>, int K, int pf)	array<struct<string,double>>	返回分词后统计与指定单词一起出现频次最高的 TOP-K。
CONCAT_WS(string SEP, string A, string B, ...)	string	类似 CONCAT() 函数，可以自定义分隔符。
CONCAT_WS(string SEP, array<string>)	string	等同于上面的 CONCAT_WS()，不一样的是参数是字符串数组，从 0.9.0 版本开始支持。
DECODE(binary bin, string charset)	string	针对指定的字符串进行解码，从 0.12.0 版本开始支持。
ELT(N int,str1 string,str2 string,str3 string,...)	string	返回指定索引的字符串，如果 N 小于 1 或者大于指定参数的个数，则返回 NULL。
ENCODE(string src, string charset)	binary	针对指定的字符串进行编码，从 0.12.0 版本开始支持。
FIELD(val T,val1 T,val2 T,val3 T,...)	int	返回 val1，val2，val3，... 列表中 val 的索引，如果未找到，则返回 0。
FIND_IN_SET(string str, string strList)	int	返回 str 在 strList 中第一次出现的地方，其中 strList 是逗号分隔的字符串。如果任一参数为 null，则返回 null。如果第一个参数包含逗号，则返回 0。
FORMAT_NUMBER(number x, int d)	string	将数字 X 格式化为 '＃，###，###。##' 之类的格式，四舍五入到小数点后 D 位，然后将结果作为字符串返回。如果 D 为 0，则结果没有小数点或小数部分。
IN_FILE(string str, string filename)	boolean	如果字符串 str 在文件名中显示为整行，则返回 true。
INSTR(string str, string substr)	int	返回 substr 中第一次出现 str 的位置。如果任一参数是 null，则返回 0。
LENGTH(string A)	int	返回字符串的长度。
LOCATE(string substr, string str[, int pos])	int	返回在位置 pos 之后的 str 中，第一次出现 substr 的位置。
LOWER(string A) lcase(string A)	string	返回指定字符串的小写形式。
LPAD(string str, int len, string pad)	string	左填充长度为 len 的 pad。
LTRIM(string A)	string	去除左边或者说起始处的空格字符，返回处理后的字符串。
NGRAMS(array<array<string>>, int N, int K, int pf)	array<struct<string,double>>	从一组标记化的句子中返回前 top-k N-gram。
OCTET_LENGTH(string str)	int	返回以 UTF-8 编码保存字符串 str 所需的八位字节数，从 2.2.0 版本开始支持。
PARSE_URL(string urlString, string partToExtract [, string keyToExtract])	string	从 URL 返回指定的部分。partToExtract 的可选值包括 HOST，PATH，QUERY，REF，PROTOCOL，AUTHORITY，FILE 和 USERINFO。
PRINTF(String format, Obj... args)	string	返回根据 printf 指定的格式输出字符串，从 0.9.0 版本开始支持。
QUOTE(String text)	string	返回带引号的字符串。
REGEXP_EXTRACT(string subject, string pattern, int index)	string	返回使用模式提取的字符串。
REGEXP_REPLACE(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	string	返回通过将 INITIAL_STRING 中所有与 PATTERN 中定义的 Java 正则表达式语法匹配的子字符串替换为 REPLACEMENT 的实例所产生的字符串。
REPEAT(string str, int n)	string	重复指定的字符串 n 次。
REPLACE(string A, string OLD, string NEW)	string	替换指定的老的字符串为新字符串。
REVERSE(string A)	string	返回反转的字符串。
RPAD(string str, int len, string pad)	string	右填充长度为 len 的 pad。
RTRIM(string A)	string	去除右边或者说末尾处的空格字符，返回处理后的字符串。
SENTENCES(string str, string lang, string locale)	array<array<string>>	针对文本进行分词或短语分开。
SPACE(int n)	string	返回 n 个空格的字符串。
SPLIT(string str, string pat)	array	根据指定分隔符对指定字符串分隔。
STR_TO_MAP(text[, delimiter1, delimiter2])	map<string,string>	使用两个定界符将文本分成键值对。
SUBSTR(string\|binary A, int start) SUBSTRING(string\|binary A, int start)	string	截断指定位置的字符串。
SUBSTR(string\|binary A, int start, int len) SUBSTRING(string\|binary A, int start, int len)	string	截断指定位置及指定长度的字符串。
SUBSTRING_INDEX(string A, string delim, int count)	string	在计数出现定界符 delim 之前，从字符串 A 返回子字符串。
TRANSLATE(string\|char\|varchar input, string\|char\|varchar from, string\|char\|varchar to)	string	通过将字符串中存在的字符替换为 from 字符串中的相应字符来翻译输入 to 字符串。
TRIM(string A)	string	去除指定字符串的两边空格。
UNBASE64(string str)	binary	将 base64 字符串转为 binary，从 0.12.0 版本开始支持。
UPPER(string A) UCASE(string A)	string	转换指定字符串为大写形式。
INITCAP(string A)	string	返回字符串，每个单词的首字母大写，所有其他字母小写。单词由空格分隔，从 1.1.0 版本开始支持。
LEVENSHTEIN(string A, string B)	int	返回两个字符串之间的 Levenshtein 距离，从 1.2.0 版本开始支持。
SOUNDEX(string A)	string	返回字符串的 soundex 代码，从 1.2.0 版本开始支持。

表生成函数

Hive SQL 内置表生成函数
函数	返回值	描述
EXPLODE(ARRAY<TYPE> a)	array	对于 a 中的每个元素，将生成一行且包含该元素。
EXPLODE(ARRAY)	N 行	将数组转换成多行，每行对应数组中的一个元素。
EXPLODE(MAP)	N 行	将 map 结构的字段转换成多行，每行对应 map 的一个元素。

Hive 基本教程

Hive SQL

Hive 笔记

Hive SQL 内置函数全解

条件函数

日期函数

数学函数

聚合函数

字符串函数

表生成函数