【十五】hive常用内置函数之Mathematical、Collection、Conditional、Type Conversion Functions

本文链接：https://blog.csdn.net/jy02268879/article/details/81322754

Mathematical Functions

Return Type	Name (Signature)	Description
DOUBLE	round(DOUBLE a)	Returns the rounded `BIGINT` value of `a`.
DOUBLE	round(DOUBLE a, INT d)	Returns `a` rounded to `d` decimal places.
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.
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.
BIGINT	floor(DOUBLE a)	Returns the maximum `BIGINT` value that is equal to or less than `a`.
BIGINT	ceil(DOUBLE a), ceiling(DOUBLE a)	Returns the minimum BIGINT value that is equal to or greater than `a`.
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	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.
DOUBLE	ln(DOUBLE a), ln(DECIMAL a)	Returns the natural logarithm of the argument `a`. Decimal version added in Hive 0.13.0.
DOUBLE	log10(DOUBLE a), log10(DECIMAL a)	Returns the base-10 logarithm of the argument `a`. Decimal version added in Hive 0.13.0.
DOUBLE	log2(DOUBLE a), log2(DECIMAL a)	Returns the base-2 logarithm of the argument `a`. Decimal version added in Hive 0.13.0.
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.
DOUBLE	pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)	Returns `ap`.
DOUBLE	sqrt(DOUBLE a), sqrt(DECIMAL a)	Returns the square root of `a`. Decimal version added in Hive 0.13.0.
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).
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`. (See http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, `BINARY`version as of Hive 0.12.0.)
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.)
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).
DOUBLE	abs(DOUBLE a)	Returns the absolute value.
INT or DOUBLE	pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)	Returns the positive value of `a mod 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.
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.
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`.
INT or DOUBLE	negative(INT a), negative(DOUBLE a)	Returns `-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.
DOUBLE	e()	Returns the value of `e`.
DOUBLE	pi()	Returns the value of `pi`.
BIGINT	factorial(INT a)	Returns the factorial of `a` (as of Hive 1.2.0). Valid `a` is [0..20].
DOUBLE	cbrt(DOUBLE a)	Returns the cube root of `a` double value (as of Hive 1.2.0).
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, INT b) 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).
INT	width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)	Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by dividing [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. See https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm (as of Hive 3.0.0)

Collection Functions

Return Type	Name(Signature)	Description
array<K>	map_keys(Map<K.V>)	Returns an unordered array containing the keys of the input map.
array<t>	sort_array(Array<T>)	Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version 0.9.0).
array<V>	map_values(Map<K.V>)	Returns an unordered array containing the values of the input map.
boolean	array_contains(Array<T>, value)	Returns TRUE if the array contains value.
int	size(Map<K.V>)	Returns the number of elements in the map type.
int	size(Array<T>)	Returns the number of elements in the array type.

Type Conversion Functions

Return Type	Name(Signature)	Description
binary	binary(string\|binary)	Casts the parameter into a binary.
Expected "=" to follow "type"	cast(expr as <type>)	Converts the results of the expression expr to <type>. For example, cast('1' as BIGINT) will convert the string '1' to its integral representation. A null is returned if the conversion does not succeed. If cast(expr as boolean) Hive returns true for a non-empty string.

Conditional Functions

Return Type	Name(Signature)	Description
Return Type	Name(Signature)	Description
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	Returns valueTrue when testCondition is true, returns valueFalseOrNull otherwise.
boolean	isnull( a )	Returns true if a is NULL and false otherwise.
boolean	isnotnull ( a )	Returns true if a is not NULL and false otherwise.
T	nvl(T value, T default_value)	Returns default value if value is null else returns value (as of HIve 0.11).
T	COALESCE(T v1, T v2, ...)	Returns the first v that is not NULL, or NULL if all v's are NULL.
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	When a = b, returns c; when a = d, returns e; else returns f.
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	When a = true, returns b; when c = true, returns d; else returns e.
T	nullif( a, b )	Returns NULL if a=b; otherwise returns a (as of Hive 2.2.0). Shorthand for: CASE WHEN a = b then NULL else a
void	assert_true(boolean condition)	Throw an exception if 'condition' is not true, otherwise return null (as of Hive 0.8.0). For example, select assert_true (2<1).