Hive SQL操作与函数自定义（二）

http://blog.csdn.net/u013980127/article/details/52606024

9 Operators and UDFs

9.1 内置运算符

9.1.1 关系运算符

操作符	运算对象的类型	描述
A <=> B	ALL	都是NULL时，返回TRUE，有一为NULL时，返回FALSE，都不为NULL时，与‘=’运算符一样。
A <> B	ALL	A或B为NULL时，返回NULL，否则A不等于B是返回TRUE，反之FALSE。
A RLIKE B	strings	A或B为NULL时，返回NULL，A的子串与正则表达式B匹配时，返回TRUE，反之FALSE。
A REGEXP B	strings	同A RLIKE B

其他不在此列举。

9.1.2 算数运算符

只要有一个操作数为NULL，结果为NULL。

Arithmetic Operators

9.1.3 逻辑运算符

Logical Operators

9.1.4 复杂类型

构造函数	操作数	描述
map	(key1, value1, key2, value2, …)	创建map
struct	(val1, val2, val3, …)	创建struct，属性名为col1, col2等
named_struct	(name1, val1, name2, val2, …)	创建struct
array	(val1, val2, …)	创建array
create_union	(tag, val1, val2, …)	创建tag指向的union

9.1.5 复杂类型的运算符

运算符	操作数类型	描述
A[n]	A是Array，n是int	返回数组A的第n个值
M[key]	M是Map<K, V>，key是K	返回key对应的value
S.x	S是struct	返回属性x的值

9.2 内置函数

9.2.1 数学函数

返回类型	函数名	描述
DOUBLE	round(DOUBLE a)	返回对a四舍五入的BIGINT值
DOUBLE	round(DOUBLE a, INT d)	小数部分d位之后数字四舍五入
DOUBLE	bround(DOUBLE a)	银行家舍入法（1~4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进）
DOUBLE	bround(DOUBLE a, INT d)	银行家舍入法,保留d位小数
BIGINT	floor(DOUBLE a)	对给定数据进行向下舍入最接近的整数
BIGINT	ceil(DOUBLE a) ceiling(DOUBLE a)	将参数向上舍入为最接近的整数
DOUBLE	rand() rand(INT seed)	返回大于或等于0且小于1的平均分布随机数
DOUBLE	exp(DOUBLE a) exp(DECIMAL a)	返回e的n次方
DOUBLE	ln(DOUBLE a) ln(DECIMAL a)	返回给定数值的自然对数
DOUBLE	log10(DOUBLE a) log10(DECIMAL a)	返回给定数值的以10为底自然对数
DOUBLE	log2(DOUBLE a) log2(DECIMAL a)	返回给定数值的以2为底自然对数
DOUBLE	log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a)	返回给定底数及指数返回自然对数
DOUBLE	pow(DOUBLE a, DOUBLE p) power(DOUBLE a, DOUBLE p)	返回某数的乘幂
DOUBLE	sqrt(DOUBLE a) sqrt(DECIMAL a)	返回数值的平方根
STRING	bin(BIGINT a)	返回二进制格式
string	hex(BIGINT a) hex(STRING a) hex(BINARY a)	将整数、字符或二进制转换为十六进制格式
binary	unhex(STRING a)	十六进制字符转换由数字表示的字符。
string	conv(BIGINT num, INT from_base, INT to_base) conv(STRING num, INT from_base, INT to_base)	将指定数值，由原来的度量体系转换为指定的体系。例如CONV(‘a’,16,2),返回。参考：’1010′
double	abs(DOUBLE a)	取绝对值
int or double	pmod(INT a, INT b) pmod(DOUBLE a, DOUBLE b)	返回a除b的余数的绝对值
double	sin(DOUBLE a) sin(DECIMAL a)	返回给定角度的正弦值
double	asin(DOUBLE a) asin(DECIMAL a)	返回x的反正弦，即是X。如果X是在-1到1的正弦值，返回NULL。
double	cos(DOUBLE a) cos(DECIMAL a)	返回余弦
double	acos(DOUBLE a) acos(DECIMAL a)	返回X的反余弦，即余弦是X，，如果-1<= A <= 1，否则返回null.
double	tan(DOUBLE a) tan(DECIMAL a)	求正切值
double	atan(DOUBLE a) atan(DECIMAL a)	求反正切值
double	degrees(DOUBLE a) degrees(DECIMAL a)	将弧度值转换角度值
double	radians(DOUBLE a) radians(DOUBLE a)	将角度值转换成弧度值
int or double	positive(INT a) positive(DOUBLE a)	返回A的值，例如positive(2)，返回2。
int or double	negative(INT a) negative(DOUBLE a)	返回A的相反数，例如negative(2),返回-2。
double or int	sign(DOUBLE a) sign(DECIMAL a)	如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
double	e()	数学常数e
double	pi()	数学常数pi
BIGINT	factorial(INT a)	求a的阶乘
double	cbrt(DOUBLE a)	求a的立方根
int bigint	shiftleft(TINYINT | SMALLINT | INT a, INT b) shiftleft(BIGINT a, INT b)	按位左移
int bigint	shiftright(TINYINT | SMALLINT | INT a, INT b) shiftright(BIGINT a, INT b)	按位右移
int bigint	shiftrightunsigned(TINYINT | SMALLINT | INT a, INT b) shiftrightunsigned(BIGINT a, INT b)	无符号按位右移（<<<）
T	greatest(T v1, T v2, …)	求最大值
T	least(T v1, T v2, …)	求最小值

9.2.2 集合函数

返回类型	函数名	描述
int	size(Map<K.V>)	返回Map的大小
int	size(Array<T>)	返回Array的大小
array<K>	map_keys(Map<K.V>)	返回Map的key集合
array<V>	map_values(Map<K.V>)	返回Map的value集合
boolean	array_contains(Array<T>, value)	返回Array是否包含value
array<T>	sort_array(Array<T>)	返回Array按自然顺序升序排列后的数组

9.2.3 类型转换函数

返回类型	函数名	描述
binary	binary(string | binary)	转换参数为binary
Expected “=” to follow “type”	cast(expr as <type>)	转换表达式expr为type类型，例如cast(‘1’ as BIGINT)。如果转换不成功，则返回null；非空字符串转换为boolean，返回true。

9.2.4 日期函数

返回类型	函数名	描述
string	from_unixtime(bigint unixtime[, string format])	转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
bigint	unix_timestamp()	获得当前时区的UNIX时间戳
bigint	unix_timestamp(string date)	转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0
bigint	unix_timestamp(string date, string pattern)	转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
pre 2.1.0: string 2.1.0 on: date	to_date(string timestamp)	返回日期时间字段中的日期部分。
int	year(string date)	返回日期中的年。
int	quarter(date/timestamp/string)	返回第几季(1.3.0开始)
int	month(string date)	返回日期中的月份
int	day(string date) dayofmonth(date)	返回日期中的天
int	hour(string date)	返回日期中的小时
int	minute(string date)	返回日期中的分钟
int	second(string date)	返回日期中的秒
int	weekofyear(string date)	返回日期在当前的周数
int	datediff(string enddate, string startdate)	返回结束日期减去开始日期的天数
pre 2.1.0: string 2.1.0 on: date	date_add(string startdate, int days)	返回开始日期startdate增加days天后的日期
pre 2.1.0: string 2.1.0 on: date	date_sub(string startdate, int days)	返回开始日期startdate减少days天后的日期
timestamp	from_utc_timestamp(timestamp, string timezone)	转换UTC时间为指定时区的时间
timestamp	to_utc_timestamp(timestamp, string timezone)	转换指定时区的时间为UTC时间
date	current_date	返回查询时的日期
timestamp	current_timestamp	返回查询时的时间
string	add_months(string start_date, int num_months)	返回当前时间下再增加num_months个月的日期，如果当前日期是最后一天，则计算后的日期也为最后一天
string	last_day(string date)	返回这个月的最后一天的日期，忽略时分秒部分（HH:mm:ss）
string	next_day(string start_date, string day_of_week)	返回当前时间的下一个星期X所对应的日期。day_of_week是星期的2个、3个或整个单词 (e.g. Mo, tue, FRIDAY).
string	trunc(string date, string format)	返回时间的最开始年份或月份 如trunc(“2016-06-26”,“MM”)=2016-06-01 trunc(“2016-06-26”,“YY”)=2016-01-01 注意所支持的格式为MONTH/MON/MM, YEAR/YYYY/YY
double	months_between(date1, date2)	返回date1与date2之间相差的月份，如date1>date2，则返回正，如果date1
string	date_format(date/timestamp/string ts, string fmt)	按指定格式返回时间date 如：date_format(“2016-06-22”,”MM-dd”)=06-22

9.2.5 条件函数

返回类型	函数名	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	testCondition为true，返回valueTrue，否则返回valueFalseOrNull
boolean	isnull(a)	a为null时，返回true；否则返回false
boolean	isnotnull (a)	a不为null时，返回true；否则返回false
T	nvl(T value, T default_value)	value为null时，返回default_value，否则返回value
T	COALESCE(T v1, T v2, …)	返回第一个不为null的值，都为null时，返回null
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	类似switch
T	CASE WHEN a THEN b [