HIVE内部函数

 

Hive内部提供了很多函数给开发者使用，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表生成函数等等，这些函数都统称为内置函数。

 

 

数学函数

Return Type	Name (Signature)	Description
DOUBLE	round(DOUBLE a)	Returns the rounded BIGINT value of a. 返回对a四舍五入的BIGINT值
DOUBLE	round(DOUBLE a, INT d)	Returns a rounded to d decimal places. 返回DOUBLE型d的保留n位小数的DOUBLW型的近似值
DOUBLE	bround(DOUBLE a)	Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4. 银行家舍入法（1~4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进）
DOUBLE	bround(DOUBLE a, INT d)	Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of Hive 1.3.0, 2.0.0). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4. 银行家舍入法,保留d位小数
BIGINT	floor(DOUBLE a)	Returns the maximum BIGINT value that is equal to or less than a 向下取整，最数轴上最接近要求的值的左边的值  如：6.10->6   -3.4->-4
BIGINT	ceil(DOUBLE a), ceiling(DOUBLE a)	Returns the minimum BIGINT value that is equal to or greater than a. 求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLE	rand(), rand(INT seed)	Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic. 每行返回一个DOUBLE型随机数seed是随机因子
DOUBLE	exp(DOUBLE a), exp(DECIMAL a)	Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0. 返回e的a幂次方， a可为小数
DOUBLE	ln(DOUBLE a), ln(DECIMAL a)	Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0. 以自然数为底d的对数，a可为小数
DOUBLE	log10(DOUBLE a), log10(DECIMAL a)	Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0. 以10为底d的对数，a可为小数
DOUBLE	log2(DOUBLE a), log2(DECIMAL a)	Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0. 以2为底数d的对数，a可为小数
DOUBLE	log(DOUBLE base, DOUBLE a) log(DECIMAL base, DECIMAL a)	Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0. 以base为底的对数，base 与 a都是DOUBLE类型
DOUBLE	pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)	Returns ap. 计算a的p次幂
DOUBLE	sqrt(DOUBLE a), sqrt(DECIMAL a)	Returns the square root of a. Decimal version added in Hive 0.13.0. 计算a的平方根
STRING	bin(BIGINT a)	Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin). 计算二进制a的STRING类型，a为BIGINT类型
STRING	hex(BIGINT a) hex(STRING a) hex(BINARY a)	If the argument is an INT or binary, hex returns the number as a STRING in hexadecimal format. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.) 计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制
BINARY	unhex(STRING a)	Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.) hex的逆方法
STRING	conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)	Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv). 将GIGINT/STRING类型的num从from_base进制转换成to_base进制
DOUBLE	abs(DOUBLE a)	Returns the absolute value. 计算a的绝对值
INT or DOUBLE	pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)	Returns the positive value of a mod b. a对b取模
DOUBLE	sin(DOUBLE a), sin(DECIMAL a)	Returns the sine of a (a is in radians). Decimal version added in Hive 0.13.0. 求a的正弦值
DOUBLE	asin(DOUBLE a), asin(DECIMAL a)	Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0. 求d的反正弦值
DOUBLE	cos(DOUBLE a), cos(DECIMAL a)	Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0. 求余弦值
DOUBLE	acos(DOUBLE a), acos(DECIMAL a)	Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0. 求反余弦值
DOUBLE	tan(DOUBLE a), tan(DECIMAL a)	Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0. 求正切值
DOUBLE	atan(DOUBLE a), atan(DECIMAL a)	Returns the arctangent of a. Decimal version added in Hive 0.13.0. 求反正切值
DOUBLE	degrees(DOUBLE a), degrees(DECIMAL a)	Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0. 奖弧度值转换角度值
DOUBLE	radians(DOUBLE a), radians(DOUBLE a)	Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0. 将角度值转换成弧度值
INT or DOUBLE	positive(INT a), positive(DOUBLE a)	Returns a. 返回a
INT or DOUBLE	negative(INT a), negative(DOUBLE a)	Returns -a. 返回a的相反数
DOUBLE or INT	sign(DOUBLE a), sign(DECIMAL a)	Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0. 如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
DOUBLE	e()	Returns the value of e. 数学常数e
DOUBLE	pi()	Returns the value of pi. 数学常数pi
BIGINT	factorial(INT a)	Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20]. 求a的阶乘
DOUBLE	cbrt(DOUBLE a)	Returns the cube root of a double value (as of Hive 1.2.0). 求a的立方根
INT BIGINT	shiftleft(TINYINT|SMALLINT|INT a, INT b) shiftleft(BIGINT a, INT b)	Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left. Returns int for tinyint, smallint and int a. Returns bigint for bigint a. 按位左移
INT BIGINT	shiftright(TINYINT|SMALLINT|INT a, INTb) shiftright(BIGINT a, INT b)	Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right. Returns int for tinyint, smallint and int a. Returns bigint for bigint a. 按拉右移
INT BIGINT	shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b), shiftrightunsigned(BIGINT a, INT b)	Bitwise unsigned right shift (as of Hive 1.2.0). Shifts a b positions to the right. Returns int for tinyint, smallint and int a. Returns bigint for bigint a. 无符号按位右移（<<<）
T	greatest(T v1, T v2, ...)	Returns the greatest value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive 2.0.0). 求最大值
T	least(T v1, T v2, ...)	Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive 2.0.0). 求最小值