Impala内置函数

[官网地址]

字符函数(字符串操作)

返回值类型	函数名称(参数)	函数说明
int	ascii(string str)	返回str中首个ASCII字符串的整数值
string	base64decode(string str)	将base64格式的字符串进行解码
string	base64encode(string str)	将字符串编码成base64格式的字符串。 函数BASE64ENCODE()和BASE64DECODE()通常组合使用，用于在Impala表中存储存储或传输有问题的字符串数据。例如，可以使用这些函数来存储使用非UTF-8编码的字符串数据，或者转换需要ASCII值的上下文中的值，例如分区键列。 base64编码的值对字符串函数(如LENGTH())产生不同的结果，MAX()和MIN()比使用未编码的字符串值调用这些函数时更有效。
string	btrim(STRING a [, STRING chars_to_trim])	从STRING值的开头和结尾删除一个或多个字符的所有实例。默认情况下，只删除空格。如果指定了非null可选的第二个参数，则该函数将从字符串的开头和结尾删除第二个参数中出现的所有字符。
int	bytes(str)	返回指定字节串中包含的字节数
int	char_length(string str) character_length(STRING a)	返回参数字符串的长度，以字符为单位，包括填充a的任何尾随空格字符值
string	chr(INT character_code)	返回由十进制码位值指定的字符。结果字符的解释和显示取决于您的系统语言环境。 由于Impala字符串值的一致性处理只保证ASCII范围内的值，所以只对ASCII字符对应的值使用此函数。特别是，参数值大于 255返回一个空字符串
string	concat(string|binary A, string|binary B...)	对二进制字节码或字符串按次序进行拼接。 如果有一个参数为null,则结果也为null
string	concat_ws(string SEP, string A, string B...)	用指定分隔符来拼接前后两个字符串。如果分割符为null则返回null，参数为null则忽略该参数
int	find_in_set(string str, string strList)	返回以逗号分隔的字符串中str出现的位置,如果参数str为逗号或查找失败将返回0,如果任一参数为NULL将返回NULL.
int	group_concat(STRING s [, STRING sep])	返回单个字符串，表示结果集每行连接在一起的参数值。如果指定了可选的分隔符字符串，则在每对连接的值之间添加分隔符。
string	initcap(string A)	将字符串A的首字母转换成大写其余字母不变
int	instr(string str, string substr)	查找字符串str中子字符串substr出现的位置,如果查找失败将返回0,如果参数为Null将返回null.注意位置为从1开始.
double	jaro_distance(string str1, string str2) jaro_dist(string str1, string str2)	返回两个输入字符串之间的Jaro距离。Jaro距离是两个字符串之间相似度的度量，是JARO_SIMILARITY()的互补，即(1 - JARO_SIMILARITY())。
double	jaro_similarity(string str1, string str2) jaro_sim(string str1, string str2)	返回两个字符串的Jaro相似性。两个字符串的Jaro相似度越高，表示字符串越相似。
double	jaro_winker_distance(STRING str1, STRING str2[, DOUBLE scaling_factor, DOUBLE boost_threshold]) jw_dst(STRING str1, STRING str2[, DOUBLE scaling_factor, DOUBLE boost_threshold])	返回两个输入字符串的Jaro-Winkler距离。它是JARO_WINKLER_SIMILARITY()的互补，即1 - JARO_WINKLER_SIMILARITY()。 使用Jaro或Jaro- winkler函数对相对较短的字符串执行模糊匹配，例如，根据数据库中的记录擦洗用户输入的名称。
double	jaro_winker_similarity(STRING str1, STRING str2[, DOUBLE scaling_factor, DOUBLE boost_threshold]) jw_sim(STRING str1, STRING str2[, DOUBLE scaling_factor, DOUBLE boost_threshold])	返回两个输入字符串之间的Jaro-Winkler相似性。Jaro-Winkler相似度使用由缩放因子指定的前缀权重，它为从一开始匹配一组前缀长度(最多四个字符)的字符串提供更有利的评级。
int	length(string A)	返回字符串的长度
int	levenshtein(string A,string B) le_dst(string A,string B)	计算两个字符串之间的差异大小  如：levenshtein('kitten', 'sitting') = 3
int	locate(string substr, string str[, int pos])	查找字符串str中的pos位置后字符串substr第一次出现的位置
string	lower(string A) lcase(string A)	将字符串A的所有字母转换成小写字母
string	lpad(string str, int len, string pad)	从左边开始对字段使用字符串pad填充，最终len长度为止，如果字符串str本身长度比len大的话，将去掉多余的部分
string	ltrim(string A)	去掉字符串A前面的空格
string	parse_url(string url, string partToExtract [, string keyToExtract])	返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,例如：parse_url('http://baidu.com/path1/p.php?k1=v1&k2=v2', 'HOST') ='baidu.com'，如果参数partToExtract值为QUERY则必须指定第三个参数key.  如：parse_url('http://baidu.com/path1/p.php?k1=v1&k2=v2','QUERY','k1') ='v1'
string	prettyprint_bytes(TINYINT|SMALLINT|INT|BIGINT num)	以人类可读的方式格式化数字字节
string	regexp_escape(string str)	返回RE2库中特殊字符的转义字符串，以便按字面意思解释特殊字符，而不是将其视为特殊字符。
string	regexp_extract(string subject, string pattern, int index)	抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意转义字符的使用,如第二个参数使用'\s'将被匹配到s,'\\s'才是匹配空格
boolean	regexp_like(string str, string pattern [, string options])	返回true或false以指示源字符串是否在其内部的任何位置包含模式给出的正则表达式。可选的第三个参数由改变匹配执行方式的字母标志组成，例如i表示不区分大小写的匹配
string	regexp_replace(string a, string b, string c)	按照Java正则表达式b将字符串a中符合条件的部分成c所指定的字符串，如果c为空，则将符合正则的部分将被去掉。 如：regexp_replace("foobar","oo|ar", "")='fb' .注意转义字符的使用.
string	repeat(string str, int n)	重复输出n次字符串str
string	replace(STRING initial, STRING target, STRING replacement)	返回初始参数，目标字符串的所有出现都由替换字符串替换。
string	reverse(string A)	反转字符串
string	rpad(string str, int len, string pad)	从右边开始对字符串str使用字符串pad填充到len长度为止,如果字符串str本身长度比len大的话,将去掉多余的部分
string	rtrim(string A)	去掉字符串后面出现的空格
string	space(int n)	返回n个空格
string	split_part(string str, string delimiter, bigint index)	返回按分隔符分割的输入源字符串的请求索引部分。   •如果index是正数，则返回源字符串中从左开始的第索引部分。   •如果index为负数，则返回源字符串中从右开始的第索引部分。   •如果index为0，则返回错误。 分隔符可以由多个字符组成，而不仅仅是单个字符。
string	strleft(STRING a, INT num_chars)	返回字符串最左边的字符。 对带有两个参数的SUBSTR()调用的简写
string	strright(STRING a, INT num_chars)	返回字符串最右边的字符。 对带有两个参数的SUBSTR()调用的简写
string	substr(string str, int start[, int length]) substring(string str, int start[, int length])	对于字符串str,从start位置开始截取,截取长度为length。当length省略时默认截取到字符串的末尾
string	substring_index(string A, string delim, int count)	截取第count分隔符之前的字符串，如count为正则从左边开始截取，如果为负则从右边开始截取
string	translate(string input, string from, string to)	将input出现在from中的字符串替换成to中的字符串 如：translate("MOBIN","BIN","M")="MOM"
string	trim(string A)	将字符串A前后出现的空格去掉
string	upper(string A) ucase(string A)	将字符串A中的字母转换成大写字母

数学函数

返回值类型	函数名称(参数)	函数说明
DOUBLE	abs(DOUBLE  a)	计算a的绝对值
DOUBLE	acos((DOUBLE  a)	求反余弦值
DOUBLE	asin((DOUBLE  a)	求d的反正弦值
DOUBLE	atan((DOUBLE  a)	求反正切值
DOUBLE	atan2(DOUBLE a, DOUBLE b)	返回两个参数的反正切值，参数的符号用于确定结果的象限
STRING	bin(BIGINT a)	计算STRING类型二进制a的STRING类型
DECIMAL	ceil((DOUBLE(p,s)  a) ceiling((DOUBLE(p,s)  a) dceil((DOUBLE(p,s)  a)	向上取整。 如：ceil(6.1) = ceil(6.9) = 7
STRING	conv(BIGINT|STRING  num, INT from_base, INT to_base)	将GIGINT/STRING类型的num从from_base进制转换成to_base进制
DOUBLE	cos((DOUBLE  a)	求余弦值
DOUBLE	cosh(DOUBLE a)	返回参数的双曲余弦值
DOUBLE	cot(DOUBLE a)	返回参数的余切值
DOUBLE	degrees((DOUBLE  a)	奖弧度值转换角度值
DOUBLE	e()	数学常数e
DOUBLE	exp((DOUBLE  a)	返回e的a幂次方， a可为小数
BIGINT	factorial(INT a)	求a的阶乘
DECIMAL	floor(DOUBLE a) dfloor(DOUBLE a)	向下取整，最数轴上最接近要求的值的左边的值  如：floor(6.10)=6   floor(-3.4)=-4
DOUBLE	fmod(DOUBLE a, DOUBLE b)	返回浮点数的模数
FLOAT	fmod(FLOAT a, FLOAT b)	返回浮点数的模数
(T v1, T v2, ...)	fnv_hash(T v)	返回从输入参数派生的一致的64位值，以便在应用程序中实现散列逻辑
T	greatest(T v1, T v2, ...)	求最大值
STRING	hex(BIGINT|STRING  a)	计算a的STRING类型，若a为STRING类型就转换成字符相对应的十六进制
BOOLEAN	is_inf(DOUBLE a)	测试一个值是否等于表示无穷大的特殊值inf
BOOLEAN	is_nan(DOUBLE a)	测试值是否等于表示非数字的特殊值NaN
T	least(T v1, T v2, ...)	求最小值
DOUBLE	ln((DOUBLE  a)	以自然数为底d的对数，a可为小数
DOUBLE	log10((DOUBLE  a)	以10为底d的对数，a可为小数
DOUBLE	log2((DOUBLE  a)	以2为底数d的对数，a可为小数
DOUBLE	log((DOUBLE  base, (DOUBLE  a)	以base为底的对数，base与a都是DOUBLE类型
T	max_int() max_tinyint() max_smallint() max_bigint()	返回关联整型的最大值
T	min_int() min_tinyint() min_smallint() min_bigint()	返回关联整型的最小值
T	mod(numeric_type a, same_type b)	返回一个数的模数。相当于%算术运算符。适用于任何大小的整数类型、任何大小的浮点类型以及具有任何精度和比例的DECIMAL
BIGINT	murmur_hash(T v)	为方便实现，返回从输入参数派生的一致的64位值。MurmurHash2非加密哈希函数
INT or DOUBLE	negative(INT|DOUBLE a)	返回a的相反数
DOUBLE	pi()	数学常数pi
INT or DOUBLE	pmod(INT|DOUBLE a, INT|DOUBLE b)	a对b取模
INT or DOUBLE	positive(INT|DOUBLE  a)	返回a
DOUBLE	pow(DOUBLE a, DOUBLE p) dpow(DOUBLE a, DOUBLE p) fpow(DOUBLE a, DOUBLE p) power(DOUBLE a, DOUBLE p)	计算a的p次幂
INT	precision(numeric_expression)	计算将参数表达式的类型表示为decimal值所需的精度(十进制位数)
BIGINT	QUOTIENT(BIGINT n1, BIGINT n2) QUOTIENT(DOUBLE n1, DOUBLE n2)	返回第一个参数除以第二个参数，丢弃任何小数部分。避免像/ SQL操作符那样将整型参数提升为DOUBLE。还包括一个接受DOUBLE参数的重载，在除法之前丢弃每个参数值的小数部分，并再次返回BIGINT。如果使用整数参数，该函数的工作方式与DIV操作符相同。
DOUBLE	radians(DOUBLE  a)	将角度值转换成弧度值
DOUBLE	rand() rand(BIGINT seed) random() random(BIGINT seed)	返回一个DOUBLE型随机数,seed是随机因子
DECIMAL	round(DOUBLE a) dround(DOUBLE a)	返回对a四舍五入的整数值
DECIMAL	round(DOUBLE a, INT d) dround(DOUBLE a, INT d)	返回对a保留d位小数的DOUBLE型的近似值
INT	scale(numeric_expression)	计算将参数表达式的类型表示为decimal值所需的比例(小数点右侧的十进制位数)计算将参数表达式的类型表示为decimal值所需的比例(小数点右侧的十进制位数)
INT	sign(DOUBLE  a)	返回-1、0或1以指示参数值的签名
DOUBLE	sin(DOUBLE  a)	求a的正弦值
DOUBLE	sinh(DOUBLE  a)	返回参数的双曲正弦值返回参数的双曲正弦值
DOUBLE	sqrt(DOUBLE  a)	计算a的平方根
DOUBLE	tan(DOUBLE  a)	求正切值
DOUBLE	tanh(DOUBLE  a)	返回参数的双曲正切
T	truncate(DOUBLE|DECIMAL a[,digits_to_leave]) dtrunc(DOUBLE|DECIMAL a[,digits_to_leave]) trunc(DOUBLE|DECIMAL a[,digits_to_leave])	从数值中删除部分或全部小数。使用单个浮点参数，删除所有小数位数，留下整数值。 可选的第二个参数指定返回值中包含的小数位数，仅在参数类型为DECIMAL时适用。第二个参数0将截断为一个整数值。第二个参数- N将小数点左侧的N位数字设置为0
STRING	unhex(STRING  a)	返回一个字符串，其ASCII值对应于参数中的十六进制数字对
BIGINT	width_bucket(DECIMAL expr, DECIMAL min_value, DECIMAL max_value, INT num_buckets)	返回expr值在直方图中所属的桶号，其中其min_value和max_value之间的范围被划分为大小相同的num_buckets桶

位操作函数

返回值类型	函数名称(参数)	函数说明
T	bitand(int a, same_type b)	返回一个整数值，表示两个参数中设置为1的位。如果参数大小不同，则较小的参数提升为较大参数的类型
T	bitnot(int a)	反转输入参数的所有位
T	bitor(int a, same_type b)	回一个整数值，表示在任意一个参数中设置为1的位。如果参数大小不同，则较小的参数提升为较大参数的类型
T	bitxor(int a, same_type b)	返回一个整数值，表示在一个参数中设置为1的位，而不是两个参数都设置为1的位。 如果参数大小不同，则较小的参数提升为较大参数的类型
T	countset(int a [,INT 0|1])	缺省情况下，返回指定整数值中1位的个数。如果第二个可选参数被设置为0，它将返回0位的个数
T	getbit(int a, INT position)	返回表示指定位置位的0或1。这些位置从右到左编号，从0开始。位置参数不能为负
T	rotateleft(int a, INT positions)	旋转指定位数左移的整数值。由于从原始值中取出了最高有效位，如果它是1位，则将其“旋转”回最低有效位。因此，最终值与原始值具有相同的1位数，只是位置不同。在计算机科学术语中，这种操作是“循环移位”。
T	rotateright(int a, INT positions)	旋转指定位数右移的整数值
T	setbit(int a, INT position [,INT 0|1])	默认情况下，将指定位置的一位更改为1，如果它还没有。如果可选的第三个参数被设置为0，则指定的位被设置为0
T	shiftleft(int a, INT positions)	将整数值向左移动指定位数。由于从原始值中去掉了最高有效位，因此该值被丢弃，最低有效位变为0。在计算机科学术语中，这种操作是一种“逻辑转换”
T	shiftright(int a, INT positions)	将整数值向右移动指定位数

日期函数

返回值类型	函数名称(参数)	函数说明
string	add_months(string dt, int n)	返回当前时间dt下再增加n个月的日期
	adddate(TIMESTAMP|DATE date, INT|BIGINT days)	向日期添加天数并返回新的日期值。 •如果date为TIMESTAMP，则返回TIMESTAMP。 •如果date为date，则返回date。
date	current_date()	返回当前时间日期
timestamp	current_timestamp()	返回当前时间戳
int	datediff(string enddt, string startdt)	计算时间startdt到时间enddt相差的天数
string	date_add(STRING|TIMESTAMP|DATE startdate, int days)	从开始时间startdate加上days
int	date_cmp(DATE dt1, DATE dt2)	比较date1和date2并返回。  •如果日期相同，则为0。  •如果date1 > date2则为1。  •如果date1 < date2，则-1。  •如果date1或date2为NULL，则为NULL。
bigint	date_part(STRING part, TIMESTAMP|DATE date)	类似于EXTRACT()，只是参数顺序相反。支持与EXTRACT()相同的日期和时间单位
string	date_sub(STRING|TIMESTAMP|DATE startdate, int days)	从开始时间startdate减去days
	date_trunc(STRING unit, TIMESTAMP|DATE ts)	返回截断为指定单位的ts值
string	date_format(date/timestamp/string ts, string fmt)	按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
int	day(string date)	返回时间字符串的天,例如day('2018-05-20')=20
string	dayname(date|timestamp ts)	返回date参数的日期名称。 返回值的范围是'Sunday'到 “星期六”。用于报表生成查询，作为调用DAYOFWEEK()并使用CASE表达式将该数值返回值转换为字符串的替代方法。
int	dayofweek(date|timestamp ts)	返回date参数的day字段，对应于星期几。返回值的范围是1(星期日)到7(星期六)
int	dayofyear(date|timestamp ts)	返回date参数中的日期字段，对应于一年中的日期。返回值范围为1(闰年的1月1日)到366(闰年的12月31日)
	days_add(STRING|TIMESTAMP|DATE startdate, int days)	返回将天数添加到date的值
	days_sub(STRING|TIMESTAMP|DATE startdate, int days)	返回从日期中减去天数的值
bigint	extract(TIMESTAMP|DATE ts, STRING unit) extract(unit FROM TIMESTAMP|DATE ts)	从ts中返回由单位指定的数字日期或时间字段之一
string	from_timestamp(TIMESTAMP|STRING ts, STRING format)	将TIMESTAMP值转换为表示相同值的字符串
string	from_unixtime(bigint unixtime[, string format])	将时间的秒值转换成format格式（format可为"yyyy-MM-dd hh:mm:ss","yyyy-MM-dd hh","yyyy-MM-dd hh:mm"等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
timestamp	from_utc_timestamp(timestamp ts, string timezone)	如果给定的时间戳并非UTC，则将其转化成指定的时区下时间戳
int	hour(string date)	返回时间字符串的小时
timestamp	hours_add(timestamp ts, int|bigint h)	返回指定的日期和时间加上一些小时数
timestamp	hours_sub(timestamp ts, int|bigint h)	返回指定的日期和时间减去某些小时数
int	int_months_between(TIMESTAMP|DATE enddate, TIMESTAMP|DATE startdate)	返回从起始日期到结束日期的月数，仅表示已经过的完整月份
string	last_day(string date)	返回这个月最后一天的日期，忽略时分秒部分
timestamp	microseconds_add(timestamp ts, int|bigint ms)	返回指定的日期和时间加上一些微秒数
timestamp	microseconds_sub(timestamp ts, int|bigint ms)	返回指定的日期和时间减去一些微秒数
Int	millisecond(timestamp ts)	返回a的毫秒部分
timestamp	milliseconds_add(timestamp ts, int|bigint ms)	返回指定的日期和时间加上一些毫秒数
timestamp	milliseconds_sub(timestamp ts, int|bigint ms)	返回指定的日期和时间减去一些毫秒数
int	minute(string date)	返回时间字符串的分钟
timestamp	minutes_add(timestamp ts, int|bigint m)	返回指定的日期和时间加上一些分钟数
timestamp	minutes_sub(timestamp ts, int|bigint m)	返回指定的日期和时间减去一些分钟数
int	month(timestamp|date dt)	返回时间字符串的月份部分
String	monthname(timestamp|date dt)	返回date参数的月份名称
timestamp|date	months_add(timestamp|date dt, int m)	返回将月数添加到date的值
double	months_between(date1, date2)	返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1<date2,则返回负，否则返回0.0  如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677  1997-02-28 10:30:00与1996-10-30相差3.94959677个月
timestamp|date	months_sub(timestamp|date dt, int m)	返回从日期中减去月数的值
timestamp	nanoseconds_add(timestamp ts, int|bigint ns)	返回指定的日期和时间加上一些纳秒数
timestamp	nanoseconds_sub(timestamp ts, int|bigint ns)	返回指定的日期和时间减去一些纳秒数
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
timestamp	now()	返回当前日期和时间(在本地时区)作为TIMESTAMP值
Int	quarter(date|timestamp|string a)	返回季度
int	second(string date)	返回时间字符串的秒
timestamp	seconds_add(timestamp ts, int|bigint s)	返回指定的日期和时间加上一些秒数
timestamp	seconds_sub(timestamp ts, int|bigint s)	返回指定的日期和时间减去一些秒数
timestamp|date	subdate(timestamp|date ts, int|bigint ds)	从date中减去天数并返回新的日期值
string	timeofday()	根据本地系统的时间(包括任何时区指定)返回当前日期和时间的字符串表示形式
Int	timestamp_cmp(timestamp t1, timestamp t2)	测试一个TIMESTAMP值是否比另一个TIMESTAMP值更新、旧或相同 •如果第一个参数表示比第二个参数晚的时间点，则结果为1。 •如果第一个参数表示比第二个参数更早的时间点，则结果为-1。 •如果第一个和第二个参数表示相同的时间点，结果为0。 •如果其中一个参数为NULL，则结果为NULL。
string	to_date(string|timestamp ts)	返回时间字符串的日期部分
timestamp	to_timestamp(BIGINT unixtime) to_timestamp(STRING date, STRING pattern)	将表示日期/时间值的整数或字符串转换为相应的TIMESTAMP值
timestamp	to_utc_timestamp(timestamp ts, string timezone)	如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
string	trunc(string dt, string format) trunc(n1, n2)	截断函数,可截断日期或数字。 当第一个参数为string类型的时间字段时返回时间的最开始年份或月份。此时第二个参数format所支持的格式(不分大小写)为: YEAR/YYYY/YY/Y(截取本年第一天); MONTH/MON/MM(截取本月第一天); DD(截取本周第一天);D(截取到今天); HH(截取到小时); MI(截取到分钟); SS(截取到秒);  如trunc("2016-06-26","MM")=2016-06-01, trunc("2016-06-26","YY")=2016-01-01, trunc("2016-06-26 12:30:45","DD")=2016-06-26 00:00:00。 当第一个参数是数字类型的数值时第二个参数取整数,省略时从小数点处开始截断。例如: trunc(12345.6789)=12345 trunc(12345.6789,2)=12345.67 trunc(12345.6789,-2)=12300
bigint	unix_timestamp()	获取本地时区下的时间戳(秒值)
bigint	unix_timestamp(string dt)	将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳(秒值)
bigint	unix_timestamp(string dt, string pattern)	将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 .如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400 unix_timestamp('20090320', 'yyyyMMdd') = 1237532400
timestamp	utc_timestamp()	返回一个TIMESTAMP，对应于UTC时区的当前日期和时间
int	week(timestamp|date dt) weekofyear(timestamp|date dt)	返回时间字符串位于一年中的第几个周内.  如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
timestamp|date	weeks_add(timestamp|date ts, int|bigint wk)	返回在date上添加周数的值
timestamp|date	weeks_sub(timestamp|date ts, int|bigint wk)	返回在date上减少周数的值
int	year(timestamp|date dt)	返回时间字符串的年份部分
timestamp|date	years_add(timestamp|date ts, int|bigint ys)	返回将年数添加到date的值
timestamp|date	years_sub(timestamp|date ts, int|bigint ys)	返回将年数减少到date的值

条件函数

返回值类型	函数名称(参数)	函数说明
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	如果a=b就返回c,a=d就返回e，否则返回f。 例如下面的将返回4： CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	如果a=ture就返回b,c= ture就返回d，否则返回e。如：CASE WHEN  5>0  THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN  5<0  THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
T	COALESCE(T v1, T v2, ...)	返回第一非null的值，如果全部都为NULL就返回NULL 。如：COALESCE(null,44,55)=44
	DECODE(type expression, type search1, type result1 [,type search2, type result2 ...] [,type default])	使用IS NOT DISTINCT操作符将第一个参数expression与search表达式进行比较，并返回: •找到匹配项后的对应结果。 •如果有多个匹配的搜索表达式，则第一个对应的结果。 •如果没有一个搜索表达式与第一个参数表达式匹配，则使用默认表达式。 •如果省略最后的默认表达式，并且没有一个搜索表达式与第一个参数匹配，则为NULL。
T	if(boolean a, T v1, T v1)	如果条件a为true就返回v1,否则返回v2（v1、v2为泛型）
boolean	isnull(a)	如果a为null就返回true，否则返回false
boolean	IFNULL(type a, type ifNull)	ISNULL()函数的别名，具有相同的行为
boolean	ISFALSE(BOOLEAN expression)	如果表达式为FALSE则返回TRUE。 如果表达式为TRUE或NULL，则返回FALSE。 与IS FALSE操作符相同。类似于ISNOTTRUE()，除了它对NULL参数返回相反的值。
boolean	ISNOTFALSE(BOOLEAN expression)	测试布尔表达式是否不为FALSE (即TRUE或NULL)。如果是，则返回TRUE。如果参数为NULL，则返回TRUE。 与IS NOT FALSE操作符相同。类似于ISTRUE()，除了它对NULL参数返回相反的值
boolean	ISNOTTRUE(BOOLEAN expression)	测试布尔表达式是否不为TRUE(即FALSE或NULL)。如果是，则返回TRUE。如果参数为NULL，则返回TRUE。 与IS NOT TRUE操作符相同。类似于ISFALSE()，除了它对NULL参数返回相反的值。
boolean	isnotnull (a)	如果a为非null就返回true，否则返回false
boolean	ISTRUE(BOOLEAN expression)	如果表达式为TRUE则返回TRUE。 如果表达式为FALSE或NULL，则返回FALSE。 与ISTRUE()运算符相同。类似于ISNOTFALSE()，除了它对NULL参数返回相反的值。
boolean	NONNULLVALUE(type expression)	如果表达式非空则返回TRUE，如果表达式为空则返回FALSE。 与IS NOT NULL操作符相同。 NULLVALUE()的逆函数。
T	NULLIF(type expr1, type expr2)	如果两个指定的参数相等，则返回NULL。如果指定的参数不相等，返回expr1的值。根据第102页数据类型的转换规则，表达式的数据类型必须是兼容的。不能对expr1使用计算结果为NULL的表达式;这样，您就可以区分返回值 从NULL的参数值中取出NULL，它永远不会匹配expr2。
T	NULLIFZERO(type numeric_expr)	如果数值表达式的计算结果为0，则返回NULL，否则返回表达式的结果
boolean	NULLVALUE	如果表达式为NULL则返回TRUE，否则返回FALSE。 与IS NULL操作符相同。 NONNULLVALUE()的逆函数。
T	NVL(T value, T default_value)	如果value值为NULL就返回default_value,否则返回value
T	NVL2(type a, type ifNotNull, type ifNull)	如果第一个参数不为NULL，则返回第二个参数ifNotNull。如果第一个参数为NULL，则返回第三个参数ifNull。 相当于Oracle数据库中的NVL2()函数
T	ZEROIFNULL(type expression)	如果数值表达式的计算结果为NULL，则返回0，否则返回表达式的结果。

类型转换函数

返回值类型	函数名称(参数)	函数说明
	cast(expr as <type>)	将expr转换成type类型. 如：cast("1" as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回NULL
STRING	typeof(expression)	返回与表达式对应的数据类型的名称。对于具有额外属性的类型，例如CHAR和VARCHAR的长度，或DECIMAL的精度和比例，包括类型的完整规范

聚合函数

返回值类型	函数名称(参数)	函数说明
T	appx_median([DISTINCT | ALL] expression)	一个聚合函数，它返回的值近似于输入值集合中值的中位数(中点)。 该函数适用于任何输入类型，因为唯一的要求是该类型支持小于和大于比较操作符。
DOUBLE	avg([DISTINCT | ALL] expression) [OVER (analytic_clause)]	一个聚合函数，从一组数字或TIMESTAMP值返回平均值。它的单个参数可以是数值column，也可以是应用于列值的函数或表达式的数值结果。指定列的值为NULL的行将被忽略。如果表为空，或者提供给AVG的所有值为NULL, AVG返回NULL。 avg(col),表示求指定列的平均值; avg(DISTINCT col)表示求去重后的列平均值。
BIGINT	count([DISTINCT | ALL] expression) [OVER (analytic_clause)]	返回行数或非null行数的聚合函数。 根据参数的不同，COUNT()会考虑满足特定条件的行: •计数(*)在总数中包含NULL值。 •计数(column_name)只考虑列包含非null值的行。 •您还可以将COUNT与DISTINCT操作符结合使用，以 在计数前消除重复项，并对跨多个列的值组合进行计数。 当查询包含GROUP BY子句时，为分组值的每个组合返回一个值。
DOUBLE	cume_dist (expr) OVER ([partition_by_clause] order_by_clause)	返回值的累积分布。结果集中每行的值大于0且小于或等于1。
STRING	group_concat([distinct | all] expr [, SEPARATOR str_val])	一个聚合函数，它返回一个字符串，表示结果集中每一行连接在一起的参数值。如果指定了可选的分隔符字符串，则在每对连接的值之间添加分隔符。默认分隔符是逗号后跟空格。
DOUBLE	max([DISTINCT | ALL] expression) [OVER (analytic_clause)]	求指定列的最大值
DOUBLE	min([DISTINCT | ALL] expression) [OVER (analytic_clause)]	求指定列的最小值
INT	ndv([DISTINCT | ALL] expression [,scale])	返回近似于COUNT(DISTINCT col)结果的聚合函数 “不同值的个数”。它比COUNT和DISTINCT的组合快得多，并且使用恒定的内存量，因此对于具有高基数的列来说内存密集型更少。
INT	ntile(INT x)OVER ([partition_by_clause] order_by_clause)	将一个有序分区划分为x组，称为bucket，并为分区中的每一行分配一个桶号。这可以方便地计算三元组、四分位数、十分位数、百分位数和其他常见的汇总统计数据。
DOUBLE	stddev(col)	一组数字的标准偏差的集合函数
DOUBLE	stddev_pop(col)	求指定列数值的标准偏差
DOUBLE	stddev_samp(col)	求指定列数值的样本标准偏差
DOUBLE	sum([DISTINCT | ALL] expression) [OVER (analytic_clause)]	sum(col)表示求指定列的和; sum(DISTINCT col)表示求去重后的列的和
DOUBLE	variance(col)	求指定列数值的方差
DOUBLE	var_pop(col) variance_pop(col)	求指定列数值的方差
DOUBLE	var_samp(col) variance_samp(col)	求指定列数值的样本方差

辅助功能类函数

返回值类型	函数名称(参数)	函数说明
string	current_database()	返回当前数据库名称
string	effective_user()	通常返回与USER()相同的值。 如果启用了委托，则返回被委托用户的ID。
string	get_json_object(STRING json_str, STRING selector)	根据选择器JSON路径从json_str中提取JSON对象，并返回提取的JSON对象的字符串。 如果输入json_str无效，或者基于选择器JSON路径没有选择任何内容，则该函数返回NULL。
string	logged_in_user()	通常返回与USER()相同的值。 如果启用了委托，则返回被委托用户的ID。 LOGGED_IN_USER()是EFFECTIVE_USER()的别名。
string	pid()	返回会话所连接的impalad守护进程的进程ID。您可以在低级调试期间使用它，发出跟踪、显示参数等impalad进程的Linux命令。
string	sleep(int ms)	将查询暂停指定毫秒数。使用足够小的结果集来减慢查询的速度，以监视运行时执行、内存使用或其他在查询执行的短暂间隔内难以捕获的因素。在SELECT列表中使用时，对结果集中的每一行调用一次;相应地调整毫秒数。 为了避免并发查询的数量过多，请将此函数用于测试和开发系统上的故障排除，而不是用于生产查询。
string	user()	返回连接到impalad守护进程的Linux用户的用户名。通常在没有任何FROM子句的查询中调用一次，以了解授权设置如何在安全上下文中应用;一旦知道了登录的用户名，就可以检查该用户属于哪些组，并且可以从组列表中检查通过授权策略文件对这些组可用的角色。
string	uuid()	返回通用唯一标识符，这是一个128位值，编码为字符串，其中包含以破折号分隔的十六进制数字组。 每次调用UUID()都会产生一个新的任意值。 如果结果集的每一行都有一个UUID，则可以将其用作表内的唯一标识符，甚至可以用作跨表的唯一ID。
string	version()	返回当前连接的impalad守护进程的精确版本号和构建日期等信息。通常用于确认您已连接到预期的Impala级别以使用特定功能，或连接到多个节点并确认它们都运行同一级别的impalad。
string	coordinator()	返回正在运行作为当前查询协调器的impalad守护进程的主机的名称。

运算符

类型	符号	说明
算术运算	+	加
	-	减
	*	乘
	/	除
	%	取余数
逻辑运算	&	位与
	|	位或
	^	位异或
	~	位取反
	and	逻辑与（并且）
	or	逻辑或
	not	逻辑非
关系运算	=	等于
	>=	大于等于
	<	小于
	<=	小于等于
	<>	不等于
	is null	为null
	is not null	不为null
	like	模糊匹配
	rlike	模糊匹配

窗口函数

开窗函数就是定义一个行为列，就是在你查询的结果上直接多出一列值（可以是聚合值或是排序号），特征就是带有over()。用法与Hive一致。

使用场景:开窗函数适用于在每一行的最后一列添加聚合函数的结果。

（1）用于分区排序;（2）动态分组;（3）Top N;（4）累加计算;（5）层次查询

注意:开窗函数只能出现在 SELECT 或 ORDER BY 子句中.

OVER从句

基本语法：函数 + over( [partition by ...] [order by ...] [窗口子句] )

over表示开窗，默认窗口大小会包含所有数据。

partition by表示根据字段再划分一个细窗口，相同字段进入同一个细窗口里面，每个窗口之间相互独立，窗口子句对于每个细窗口独立生效。

order by表示窗口内按什么排序，如果只有over表示直接最大窗口排序；如果有partition by则按每个细窗口单独排序。

窗口子句，可以进一步限定范围。窗口规范支持以下格式：

(rows | range) between (unbounded | [num]) preceding and ( [num] preceding | current row | (unbounded | [num]) following)

(rows | range) between current row and (current row | (unbounded | [num]) following)

(rows | range) between [num] following and (unbounded | [num]) following

注意: rank、dense_rank、ntile、row_number、lag、lead 这些函数不支持窗口子句。

order by子句可以对结果集按照指定的排序规则进行排序，并且在一个指定的范围内进行聚合运算。

order by子句(也叫做WINDOW子句)的语法为：

order by 字段名 range|rows between 边界规则1 and 边界规则2

range|rows between这个子句又被称为定位框架。

range是按照值的范围进行范围的定义，rows是按照行的范围进行范围的定义，边界范围可取值：

PRECEDING：往前(n preceding：前N行，比如 2 preceding)

FOLLOWING：往后(n following：后N行)

CURRENT ROW：当前行

UNBOUNDED：无界限（起点或终点）

UNBOUNDED PRECEDING：一直到第一条记录

UNBOUNDED FOLLOWING：一直到最后一条记录

当ORDER BY后面缺少窗口子句条件，窗口规范默认是:

range between unbounded preceding and current row.

当ORDER BY和窗口子句都缺失, 窗口规范默认是:

rows between unbounded preceding and unbounded following.

注意:结果和ORDER BY相关,默认为升序

如果不指定ROWS BETWEEN,默认为从起点到当前行;

如果不指定ORDER BY，则将分组内所有值累加; 

OVER从句支持以下函数，但是并不支持和窗口一起使用它们:

Ranking函数: NTile、Rank、Dense_Rank、Row_Number、Cume_Dist、Percent_Rank.

Lead和Lag函数.

聚合函数(sum、count、max、min、avg...)

--例如:

sum(sales)over(partition by user_type order by sales asc rows between unbounded preceding and current row) as sales_2;

over语句还可以独立出来，用window重写，但需要注意的是，如果sort by使用了多个字段，如果用range指定窗口的话会出错，需要用rows来指定窗口，因为range是对列的比较，不支持多列比较。

例如:

select shop_id, stat_date, ordamt, sum(ordamt) over win as t

from rt_data

where dt = '2015-01-11' and shop_id = 10026

window win as (distribute by shop_id sort by shop_id, ordamt desc rows between unbounded preceding and current row);

over(partition by ......)与group by 区别:

group by可以实现同样的分组聚合功能，但sql语句不能写与分组聚合无关的字段，否则会报错，即group by与over(partition by ......)主要区别为:

带上group by的hive sql语句只能显示与分组聚合相关的字段;

而带上over(partition by ......)的hql语句能显示所有字段.

窗口聚合函数

可以计算一定范围内、一定值域内或者一段时间内的累积和以及移动平均值等。

可以结合聚集函数SUM() 、AVG() 等使用。

SQL标准允许将所有聚合函数用做开窗函数，只需要在聚合函数后加over()即可。

count() over(partition by ... order by ...) : 行数统计

max() over(partition by ... order by ...) : 计算最大值

min() over(partition by ... order by ...) : 计算最小值

sum() over(partition by ... order by ...) : 求和

avg() over(partition by ... order by ...) : 求平均数

stddev_samp()over() : 计算样本标准差，只有一行数据时返回null

stddev_pop()over() : 计算总体标准差

variance()over() : 计算样本方差，只有一行数据时返回0

var_samp()over() : 计算样本方差，只有一行数据时返回null

var_pop()over() : 计算总体方差

covar_samp()over() : 计算样本协方差

covar_pop()over() : 计算总体协方差

在Hive 2.1.0及以后版本中(参见Hive -9534)提供了对Distinct的支持。

Distinct支持聚合函数，包括SUM、COUNT和AVG，这些函数通过每个分区内的不同值进行聚合。当前实现的限制是，出于性能考虑，分区子句中不支持ORDER BY或window规范。

示例:

本例的数据源： select * from wx_tmp1;

需求1：要在源表中，增加两列，全国总的gmv和各城市的gmv占比。

select *,
  sum(gmv) over() as all_gmv,
  gmv/sum(gmv) over() as gmv_pro
from wx_tmp1;

需求2：要在源表中，增加两列，各区域的gmv及各分组的gmv。

select *,
  sum(gmv) over(partition by area) as area_gmv,
  sum(gmv) over(partition by group) as group_gmv
from wx_tmp1;

窗口分析函数

FIRST_VALUE、LAST_VALUE

-- FIRST_VALUE()      获得组内当前行往前的首个值

-- LAST_VALUE()       获得组内当前行往前的最后一个值

-- FIRST_VALUE(DESC)  获得组内全局的最后一个值

最多接受两个参数。第一个参数是您想要第一个值的列，第二个(可选)参数必须是一个布尔值，默认情况下为false。如果设置为true，它跳过空值。

select dp_id, mt, payment,
   FIRST_VALUE(payment)over(partition by dp_id order by mt) payment_g_first,
   LAST_VALUE(payment) over(partition by dp_id order by mt) payment_g_last,
   FIRST_VALUE(payment)over(partition by dp_id order by mt desc) payment_g_last_global
from test2 
ORDER BY dp_id,mt;

注意:如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果。

last_value默认的窗口是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，表示当前行永远是最后一个值，需改成RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

LAG、LEAD

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值.(复制目标列, 并后移n行)

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为匹配空行对应的指定默认值（当往上第n行为NULL时取默认值，如不指定则为NULL）。

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反。

lag 和lead 可以获取结果集中按一定排序所排列的当前行的上下相邻若干offset 的某个行的某个列(不用结果集的自关联）.

select dp_id, mt, payment, 
  LAG(mt,2) over(partition by dp_id order by mt) mt_new 
from test2;

-- 组内排序后，向后或向前偏移

-- 如果省略掉第三个参数，默认为NULL，否则补上。

select dp_id, mt, payment, 
  LEAD(mt,2,'1111-11') over(partition by dp_id order by mt) mt_new 
from test2;

CUME_DIST

计算某个窗口或分区中某个值的累积分布(小于等于当前值的行数/分组内总行数)。

假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中x 等于 order by 子句中指定的列的当前行中的值。如果存在并列情况，则需加上并列的个数-1。

比如统计小于等于当前薪水的人数所占总人数的比例:

SELECT dept, userid, sal,
  CUME_DIST() OVER(ORDER BY sal) AS rn1,
  CUME_DIST() OVER(PARTITION BY dept ORDER BY sal) AS rn2
FROM tb;

窗口排序函数

用于等级、百分点、n分片等。

Ntile

NTILE 分析函数将分区中已排序的行划分为大小尽可能相等的指定数量的已排名组，并返回给定行所在的组。可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。如果切片不均匀，默认增加第一个切片的分布。NTILE不支持ROWS BETWEEN。语法是：

     ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num

 然后可以根据桶号，选取前或后 n分之几的数据。

例子：给了用户和每个用户对应的消费信息表， 计算花费前50%的用户的平均消费

-- 把用户和消费表，按消费下降顺序平均分成2份

drop table if exists test_by_payment_ntile;

create table test_by_payment_ntile as 
select nick, payment,
  NTILE(2) OVER(ORDER BY payment desc) as rn
from test_nick_payment;

-- 例2

select name,orderdate,cost,
  ntile(3) over() as sample1 ,  --全局数据切片
  ntile(3) over(partition by name),  -- 按照name进行分组,在分组内将数据切成3份
  ntile(3) over(order by cost),  -- 按照cost全局升序排列,数据切成3份
  ntile(3) over(partition by name order by cost )  -- 在分组内按照cost升序排列,数据切成3份
from t_window;

Rank，Dense_Rank, Row_Number

(hive0.11.0版本开始加入)3个组内排序函数,也叫做排序开窗函数。语法为：

R()over (partion by col1... order by col2... desc/asc)

rank  会对相同数值输出相同的序号，但是下一个序号会间断;

dense_rank  会对相同数值输出相同的序号，而且下一个序号不间断;

row_number 会对所有数值输出不同的序号，序号唯一连续。

示例:

select class1, score,
   rank() over(partition by class1 order by score desc) rk1,
   dense_rank() over(partition by class1 order by score desc) rk2,
   row_number() over(partition by class1 order by score desc) rk3
from zyy_test1;

如下图所示:

注意:当使用order by 排序，desc时NULL值排在首位，ASC时NULL值排在末尾.

可以通过NULLS LAST、NULLS FIRST 控制:

RANK() OVER (ORDER BY column_name DESC NULLS LAST) 

 PARTITION BY 分组排列顺序:

RANK() OVER(PARTITION BY month ORDER BY column_name DESC)  

 这样，就会按照month 来分，即所需要排列的信息先以month 的值来分组，在分组中排序，各个分组间不干涉。

percent_rank

计算给定行的百分比排名。排名计算公式为：(current rank - 1)/(total number of rows - 1)。

返回值范围介于 0 和 1（含 1）之间。任何集合中的第一行的 PERCENT_RANK 均为 0。

例如360小助手开机速度超过了百分之多少的人:

select studentid, departmentid, classid, math,
  row_number() over(partition by departmentid,classid order by math) as row_number,
  percent_rank() over(partition by departmentid,classid order by math) as percent_rank
from student_scores;

结果

studentid   departmentid    classid math    row_number  percent_rank

115         department1     class1  93      4           0.75

114         department1     class1  94      5           1.0

124         department1     class2  70      1           0.0

121         department1     class2  74      2           0.3333333333333333

123         department1     class2  78      3           0.6666666666666666

122         department1     class2  86      4           1.0

结果解释:

    studentid=115,percent_rank=(4-1)/(5-1)=0.75

    studentid=123,percent_rank=(3-1)/(4-1)=0.6666666666666666