一.简单函数

一.简单函数
1.数学函数 返回对a四舍五入的BIGINT值 1 返回值：
2 hive> select round(2.5);
3 OK
4 3.0
5 Time taken: 0.093 seconds, Fetched: 1 row(s) 返回DOUBLE型d的保留n位小数的DOUBLW型的近似值 round(DOUBLE a, INT d) 1 返回值：double
2 hive> select round(0.5002,2);
3 OK
4 0.5
5 Time taken: 0.074 seconds, Fetched: 1 row(s) 银行家舍入法（1_4：舍，69：进，5->前位数是偶：舍，5->前位数是奇：进） bround(DOUBLE a)
返回值：double
bround(2.5) = 2, bround(3.5) = 4.银行家舍入法,保留d位小数 bround(DOUBLE a, INT d)1 返回值：double
2 bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4向下取整，最数轴上最接近要求的值的左边的值 如：6.10->6 -3.4->-4 floor(DOUBLE a)
1 返回值：double
2 hive> select floor(6.10);
3 OK
4 6
5 Time taken: 0.07 seconds, Fetched: 1 row(s)
6 hive> select floor(-3.4);
7 OK
8 -4
9 Time taken: 0.104 seconds, Fetched: 1 row(s)求其不小于小给定实数的最小整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6 ceil(DOUBLE a), ceiling(DOUBLE a) 1 返回值：BIGINT
2 hive> select ceil(6);
3 OK
4 6
5 Time taken: 0.2 seconds, Fetched: 1 row(s)
6 hive> select ceil(6.1);
7 OK
8 7
9 Time taken: 0.061 seconds, Fetched: 1 row(s)
10 hive> select ceil(6.9);
11 OK
12 7
13 Time taken: 0.153 seconds, Fetched: 1 row(s)每行返回一个DOUBLE型随机数seed是随机因子 rand(), rand(INT seed)1 返回值：DOUBLE
2 hive> select rand(2);
3 OK
4 0.7311469360199058
5 Time taken: 0.068 seconds, Fetched: 1 row(s)
6 hive> select rand();
7 OK
8 0.7859071491095923
9 Time taken: 0.064 seconds, Fetched: 1 row(s)返回e的a幂次方， a可为小数 exp(DOUBLE a), exp(DECIMAL a)1 返回值：double
2 hive> select exp(2);
3 OK
4 7.38905609893065
5 Time taken: 0.1 seconds, Fetched: 1 row(s) 以自然数为底d的对数，a可为小数 ln(DOUBLE a), ln(DECIMAL a) 1 返回值：double
2 ln(DOUBLE a), ln(DECIMAL a)
3 > select ln(3);
4 OK
5 1.0986122886681098
6 Time taken: 0.081 seconds, Fetched: 1 row(s)
7 hive> select ln(3.2);
8 OK
9 1.1631508098056809
10 Time taken: 0.067 seconds, Fetched: 1 row(s)以10为底d的对数，a可为小数 log10(DOUBLE a), log10(DECIMAL a)1 返回值：double
2 hive> select log10(3.2);
3 OK
4 0.505149978319906
5 Time taken: 0.084 seconds, Fetched: 1 row(s)
6 hive> select log10(3);
7 OK
8 0.47712125471966244
9 Time taken: 0.075 seconds, Fetched: 1 row(s) 以2为底数d的对数，a可为小数 log2(DOUBLE a), log2(DECIMAL a) 1 返回值：double
2 hive>
3 > select log2(3);
4 OK
5 1.5849625007211563
6 Time taken: 0.083 seconds, Fetched: 1 row(s)
7 hive> select log2(3.2);
8 OK
9 1.6780719051126378
10 Time taken: 0.07 seconds, Fetched: 1 row(s)以base为底的对数，base 与 a都是DOUBLE类型log(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a)1 返回值：double
2 hive> select log(2,3.2);
3 OK
4 1.6780719051126378
5 Time taken: 0.084 seconds, Fetched: 1 row(s)
6 hive> select log(2,3);
7 OK
8 1.5849625007211563
9 Time taken: 0.066 seconds, Fetched: 1 row(s)计算a的p次幂 pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)1 返回值:double
2 hive> select pow(2,4);
3 OK
4 16.0
5 Time taken: 0.065 seconds, Fetched: 1 row(s)计算a的平方根 sqrt(DOUBLE a), sqrt(DECIMAL a)1 返回值：double
2 select sqrt(2);计算二进制a的STRING类型，a为BIGINT类型 bin(BIGINT a)返回值：string
hive> select bin(2);
OK
10
Time taken: 0.194 seconds, Fetched: 1 row(s)计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制 hex(BIGINT a) hex(STRING a) hex(BINARY a)1 返回值：STRING
2 hive> select hex(2);
3 OK
4 2
5 Time taken: 0.097 seconds, Fetched: 1 row(s)hex的逆方法unhex(STRING a)1 返回值：BINARY
2 hive> select unhex(2);
3 OK
4
5 Time taken: 0.077 seconds, Fetched: 1 row(s)将GIGINT/STRING类型的num从from_base进制转换成to_base进制 conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)1 返回值：STRING
2 hive> select conv(2,10,2);
3 OK
4 10
5 Time taken: 0.075 seconds, Fetched: 1 row(s)计算a的绝对值 abs(DOUBLE a)1 返回值：DOUBLE
2 hive> select abs(-2);
3 OK
4 2
5 Time taken: 0.077 seconds, Fetched: 1 row(s)a对b取模 pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)1 返回值：double
2 hive> select pmod(4,2);
3 OK
4 0
5 Time taken: 0.077 seconds, Fetched: 1 row(s)求a的正弦值 sin(DOUBLE a), sin(DECIMAL a)1 返回值：double
2 hive> select sin(2.5);
3 OK
4 0.5984721441039564
5 Time taken: 0.092 seconds, Fetched: 1 row(s)求d的反正弦值 asin(DOUBLE a), asin(DECIMAL a)1 返回值：double
2 hive> select asin(2.5);
3 OK
4 NaN
5 Time taken: 0.097 seconds, Fetched: 1 row(s)求余弦值 cos(DOUBLE a), cos(DECIMAL a)1 返回值：double
2 hive> select cos(2.5);
3 OK
4 -0.8011436155469337
5 Time taken: 0.087 seconds, Fetched: 1 row(s)求反余弦值 acos(DOUBLE a), acos(DECIMAL a)1 返回值：double
2 hive> select acos(2.5);
3 OK
4 NaN
5 Time taken: 0.091 seconds, Fetched: 1 row(s)求正切值 tan(DOUBLE a), tan(DECIMAL a)1 返回值：double
2 hive> select tan(2.5);
3 OK
4 -0.7470222972386603
5 Time taken: 0.076 seconds, Fetched: 1 row(s)求反正切值 atan(DOUBLE a), atan(DECIMAL a)1 返回值：double
2 hive> select atan(2.5);
3 OK
4 1.1902899496825317
5 Time taken: 0.074 seconds, Fetched: 1 row(s)奖弧度值转换角度值 degrees(DOUBLE a), degrees(DECIMAL a)1 返回值：DOUBLE
2 hive> select degrees(30);
3 OK
4 1718.8733853924698
5 Time taken: 0.114 seconds, Fetched: 1 row(s)将角度值转换成弧度值 radians(DOUBLE a), radians(DOUBLE a)1 返回值：double
2 hive> select radians(30);
3 OK
4 0.5235987755982988
5 Time taken: 0.093 seconds, Fetched: 1 row(s)返回a positive(INT a), positive(DOUBLE a)1 返回值：INT or DOUBLE
2 hive> select positive(2);
3 OK
4 2
5 Time taken: 0.124 seconds, Fetched: 1 row(s)返回a的相反数 negative(INT a), negative(DOUBLE a)1 返回值：double
2 hive> select negative(2);
3 OK
4 -2
5 Time taken: 0.066 seconds, Fetched: 1 row(s)如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0 sign(DOUBLE a), sign(DECIMAL a)1 返回值：DOUBLE or INT
2 hive> select sign(2);
3 OK
4 1.0
5 Time taken: 0.091 seconds, Fetched: 1 row(s)数学常数e e()1 返回值：double
2 hive> select e();
3 OK
4 2.718281828459045
5 Time taken: 0.07 seconds, Fetched: 1 row(s)数学常数pi pi()1 返回值：double
2 hive> select pi();
3 OK
4 3.141592653589793
5 Time taken: 0.082 seconds, Fetched: 1 row(s)求a的阶乘 factorial(INT a)1 返回值：BIGINT
2 select factorial(2);求a的立方根 cbrt(DOUBLE a)1 返回值：DOUBLE
2 select cbrt(2);按位左移shiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b)1 返回值：int bigint
2 hive> select shiftleft(2,3);按拉右移shiftright(TINYINT|SMALLINT|INT a, INTb)shiftright(BIGINT a, INT b)1 返回值：INT BIGINT
2 hive> select shiftrigth(2,3);无符号按位右移（<<<）shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),shiftrightunsigned(BIGINT a, INT b)1 返回值：INT BIGINT
2 select shiftrightunsigned(2,3);求最大值 greatest(T v1, T v2, …)1 返回值：T
2 hive> select greatest(2,3,6,7);
3 OK
4 7
5 Time taken: 0.072 seconds, Fetched: 1 row(s)求最小值 least(T v1, T v2, …)1 返回值：double
2 hive> select least(2,3,6,7);
3 OK
4 2
5 Time taken: 0.079 seconds, Fetched: 1 row(s)