[Hive] Hive常用数学函数

Numeric and Mathematical Functions in Hive

The Numerical functions are listed below in alphabetical order. Use these functions in SQL queries.

ABS( double n )

The ABS function returns the absolute value of a number.

Example: ABS(-100)

ACOS( double n )

The ACOS function returns the arc cosine of value n. This function returns Null if the value n is not in the range of -1<=n<=1.

Example: ACOS(0.5)

ASIN( double n )

The ASIN function returns the arc sin of value n. This function returns Null if the value n is not in the range of -1<=n<=1.

Example: ASIN(0.5)

BIN( bigint n )

The BIN function returns the number n in the binary format.

Example: BIN(100)

CEIL( double n ), CEILING( double n )

The CEILING or CEILING function returns the smallest integer greater than or equal to the decimal value n.

Example: CEIL(9.5)

FLOOR( double n )

The FLOOR function returns the largest integer less than or equal to the given value n.

Example: FLOOR(10.9)

CONV( bigint n, int from_base, int to_base )

The CONV function converts the given number n from one base to another base. (进制转换)

EXAMPLE: CONV(4, 10,2) 
return 100

COS( double n )

The COS function returns the cosine of the value n. Here n should be specified in radians.

Example: COS(180*3.1415926/180) 
return -0.9999

EXP( double n )

The EXP function returns e to the power of n. Where e is the base of natural logarithm and its value is 2.718.

Example: EXP(50)

HEX( bigint n)

This HEX function converts the value n into hexadecimal format.

Example: HEX(16) 
return 10

HEX( string n )

This function converts each character into hex representation format.

Example: HEX(‘ABC’)

LN( double n )

The LN function returns the natural log of a number.

Example: LN(123.45)

LOG( double base, double n )

The LOG function returns the base logarithm of the number n.

Example: LOG(2, 128)
return 7.0

LOG2( double n )

The LOG2 function returns the base-2 logarithm of the number n.

Example: LOG2(44)

LOG10( double n )

The LOG10 function returns the base-10 logarithm of the number n.

Example: LOG10(100)

NEGATIVE( int n ), NEGATIVE( double n )

The NEGATIVE function returns –n

Example: NEGATIVE(10)

POSITIVE( int n ), POSITIVE( double n )

The POSITIVE function returns n

Example: POSITIVE(-10)

PMOD( int m, int n ), PMOD( double m, double n )

The PMOD function returns the positive modulus of a number.

Example: PMOD(3,2) return 1

OW( double m, double n ), POWER( double m, double n )

The POW or POWER function returns m value raised to the n power.

Example: POW(10,2) return 100

RAND( [int seed] )

The RAND function returns a random number. If you specify the seed value, the generated random number will become deterministic.

Example: RAND( )

ROUND( double value [, int n] )

The ROUND function returns the value rounded to n integer places.

Example: ROUND(123.456,2) return 123.46

SIN( double n )

The SIN function returns the sin of a number. Here n should be specified in radians.

Example: SIN(2)

SQRT( double n )

The SQRT function returns the square root of the number

Example: SQRT(4)

UNHEX( string n )

The UNHEX function is the inverse of HEX function. It converts the specified string to the number format.

Example: UNHEX(‘AB’)