一、数学函数
返回类型 |
函数名/参数 |
描述 |
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. |
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 向下取整,最数轴上最接近要求的值的左边的值 如: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]. |
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. 按位左移 |