Impala数学函数
Impala中数学函数用来执行数值计算，比如基本加法，减法，乘法和除法及更复杂的运算
函数列表
abs(numeric_type a)
返回参数的绝对值
--得到-12的绝对值
[master:21000] > select abs(-12) as abs;
+-----+
| abs |
+-----+
| 12 |
+-----+
--得到-12.5的绝对值
[master:21000] > select abs(-12.5) as abs;
+------+
| abs |
+------+
| 12.5 |
+------+
bin(bigint a)
返回整数的二进制表示形式，即0和1位数字符串。
--得到15的二进制字符串
[master:21000] > select bin(15) as bin;
+------+
| bin |
+------+
| 1111 |
+------+
ceil(double a)
ceiling(double a)
dceil(double a)
返回大于或等于参数的最小整数
--使用ceil得到大于12.5的最小整数
[master:21000] > select ceil(12.5) as ceil;
+------+
| ceil |
+------+
| 13 |
+------+
--使用ceiling得到大于12.5的最小整数
[master:21000] > select ceiling(12.5) as ceiling;
+---------+
| ceiling |
+---------+
| 13 |
+---------+
--使用dceil得到大于12.5的最小整数
[master:21000] > select dceil(12.5) as dceil;
+-------+
| dceil |
+-------+
| 13 |
+-------+
conv(bigint num,int from_base,int to_base)
conv(string num,int from_base,int to_base)
将输入值进行基底转换并返回(进制转换)。
--将16进制的A转换为2进制
[master:21000] > select conv('A',16,2) as conv;
+------+
| conv |
+------+
| 1010 |
+------+
--将16进制的5转换为2进制
[master:21000] > select conv(5,16,2) as conv;
+------+
| conv |
+------+
| 101 |
+------+
e()
返回数学常量e
[master:21000] > select e() as e;
+-------------------+
| e |
+-------------------+
| 2.718281828459045 |
+-------------------+
exp(double a)
dexp(double a)
返回常数e的n次方
--通过exp返回e的2次方
[master:21000] > select exp(2) as exp;
+------------------+
| exp |
+------------------+
| 7.38905609893065 |
+------------------+
--通过dexp返回e的2次方
[master:21000] > select dexp(2) as dexp;
+------------------+
| dexp |
+------------------+
| 7.38905609893065 |
+------------------+
factorial(integer_type a)
返回整数的的阶乘
--返回10的阶乘
[master:21000] > select factorial(10) as factorial;
+-----------+
| factorial |
+-----------+
| 3628800 |
+-----------+
floor(double a)
dfloor(double a)
返回小于或等于参数的最大整数
--通过floor返回小于12.5的最大整数
[master:21000] > select floor(12.5) as floor;
+-------+
| floor |
+-------+
| 12 |
+-------+
--通过dfloor返回小于12.5的最大整数
[master:21000] > select dfloor(12.5) as dfloor;
+--------+
| dfloor |
+--------+
| 12 |
+--------+
fmod(double a, double b)
mod(numeric_typea, numeric_typeb)
求余数
--5除以2求余
[master:21000] > select fmod(5,2) as fmod;
+------+
| fmod |
+------+
| 1 |
+------+
--5除以2求余
[master:21000] > select mod(5,2) as mod;
+-----+
| mod |
+-----+
| 1 |
+-----+
fnv_hash(type v)
得到任意值的64位哈希值
--得到字符串hello的哈希值
[master:21000] > select fnv_hash('hello') as fnv_hash;
+---------------------+
| fnv_hash |
+---------------------+
| 6414202926103426347 |
+---------------------+
greatest(some-type a,[some-type b...])
返回表达式列表中的最大值
-从整数5、16、2中得到最大值
[master:21000] > select greatest(5,16,2) as greatest;
+----------+
| greatest |
+----------+
| 16 |
+----------+
-从字符a、b、c中得到最大值
[master:21000] > select greatest('a','b','c') as greatest;
+----------+
| greatest |
+----------+
| c |
+----------+
least(bigint a,[bigint b])
返回表达式列表中的最小值
-从整数5、16、2中得到最小值
[master:21000] > select least(5,16,2) as least;
+-------+
| least |
+-------+
| 2 |
+-------+
-从字符a、b、c中得到最小值
[master:21000] > select least('a','b','c') as least;
+-------+
| least |
+-------+
| a |
+-------+
hex(bigint a)
hex(string a)
将整数或字符串中的字符转换为十六进制并返回
--返回整数15的十六进制值
[master:21000] > select hex(15) as hex;
+-----+
| hex |
+-----+
| F |
+-----+
--返回字符a的十六进制值
[master:21000] > select hex('a') as hex;
+-----+
| hex |
+-----+
| 61 |
+-----+
unhex(string a)
将16进制字符串转换为字符串输出
[master:21000] > select unhex('61') as unhex;
+-------+
| unhex |
+-------+
| a |
+-------+
is_inf(double a)
判断一个值是否等于无穷大并返回
--判断8是不是等于无穷大
[master:21000] > select is_inf(8) as is_inf;
+--------+
| is_inf |
+--------+
| false |
+--------+
--判断1/0是不是等于无穷大的值Inf
[master:21000] > select is_inf(1/0) as is_inf;
+--------+
| is_inf |
+--------+
| true |
+--------+
is_nan(double a)
测试值是否等于不存在的值NaN
--判断8是不是不存在的值NaN
[master:21000] > select is_nan(8) as is_nan;
+--------+
| is_nan |
+--------+
| false |
+--------+
--判断-1的平方根是不是不存在的值NaN
[master:21000] > select is_nan(pow(-1,0.5)) as is_nan;
+--------+
| is_nan |
+--------+
| true |
+--------+
ln(double a)
dlog1(double a)
返回数值的对数
--通过ln返回e的对数
[master:21000] > select ln(e()) as ln;
+----+
| ln |
+----+
| 1 |
+----+
--通过dlog1返回e的对数
[master:21000] > select dlog1(e()) as dlog1;
+-------+
| dlog1 |
+-------+
| 1 |
+-------+
log(double base, double a)
以数值base为基底返回a的对数
--返回以2为底，4的对数
[master:21000] > select log(2,4) as log;
+-----+
| log |
+-----+
| 2 |
+-----+
negative(numeric_type a)
返回数值的负数，负负得正
--返回5的相反数
[master:21000] > select negative(5) as negative;
+----------+
| negative |
+----------+
| -5 |
+----------+
--返回-5的相反数
[master:21000] > select negative(-5) as negative;
+----------+
| negative |
+----------+
| 5 |
+----------+
pi()
返回圆周率π的值
[master:21000] > select pi() as pi;
+-------------------+
| pi |
+-------------------+
| 3.141592653589793 |
+-------------------+
pmod(bigint a, bigint b)
pmod(double a, double b)
返回数字的余数的绝对值
[master:21000] > select pmod(-5,2) as pmod;
+------+
| pmod |
+------+
| 1 |
+------+
positive(numeric_type a)
返回与输入值相同
[master:21000] > select positive(-5) as positive;
+----------+
| positive |
+----------+
| -5 |
+----------+
pow(double a, double p)
power(double a, double p)
dpow(double a, double p)
fpow(double a, double p)
返回数值a的p次方
--通过pow返回2的3次方
[master:21000] > select pow(2,3) as pow;
+-----+
| pow |
+-----+
| 8 |
+-----+
--通过power返回2的3次方
[master:21000] > select power(2,3) as power;
+-------+
| power |
+-------+
| 8 |
+-------+
--通过dpow返回2的3次方
[master:21000] > select dpow(2,3) as dpow;
+------+
| dpow |
+------+
| 8 |
+------+
--通过fpow返回2的3次方
[master:21000] > select fpow(2,3) as fpow;
+------+
| fpow |
+------+
| 8 |
+------+
quotient(bigint numerator, bigint denominator)
quotient(double numerator, double denominator)
求商（去掉余数）
[master:21000] > select quotient(5,2) as quotient;
+----------+
| quotient |
+----------+
| 2 |
+----------+
radians(double a)
将参数值从度数转换为弧度
[master:21000] > select radians(90) as radians;
+-------------------+
| radians |
+-------------------+
| 1.570796326794897 |
+-------------------+
rand([int seed])
random([int seed])
返回0和1之间的随机值,不同参数返回不同值
[master:21000] > select rand() as rand;
-不加参数时将返回同一个值
+-----------------------+
| rand |
+-----------------------+
| 0.0004714746030380365 |
+-----------------------+
[master:21000] > select random() as random;
+-----------------------+
| random |
+-----------------------+
| 0.0004714746030380365 |
+-----------------------+
[master:21000] > select random(10) as random;
+--------------------+
| random |
+--------------------+
| 0.2185320766728986 |
+--------------------+
round(double a,[int d])
dround(double a,[int d])
截取数值，四舍五入
--数值2.1求整
[master:21000] > select round(2.1) as round;
+-------+
| round |
+-------+
| 2 |
+-------+
--数值2.123保留两位小数
[master:21000] > select round(2.123,2) as round;
+-------+
| round |
+-------+
| 2.12 |
+-------+
--数值2.1求整
[master:21000] > select dround(2.1) as dround;
+--------+
| dround |
+--------+
| 2 |
+--------+
--数值2.123保留两位小数
[master:21000] > select dround(2.123,2) as dround;
+--------+
| dround |
+--------+
| 2.12 |
+--------+
scale(numeric_expression)
求小数的位数
--求数值2.1的小数位数
[master:21000] > select scale(2.1) as scale;
+-------+
| scale |
+-------+
| 1 |
+-------+
sign(double a)
返回参数值的符号,判断正负以及是否为0
[master:21000] > select sign(2.1) as sign;
+------+
| sign |
+------+
| 1 |
+------+
[master:21000] > select sign(-2.1) as sign;
+------+
| sign |
+------+
| -1 |
+------+
[master:21000] > select sign(0) as sign;
+------+
| sign |
+------+
| 0 |
+------+
sqrt(double a)
dsqrt(double a)
求参数的平方根
[master:21000] > select sqrt(4) as sqrt;
+------+
| sqrt |
+------+
| 2 |
+------+
[master:21000] > select dsqrt(4) as dsqrt;
+-------+
| dsqrt |
+-------+
| 2 |
+-------+
truncate(double_or_decimal a,[digits_to_leave])
dtrunc(double_or_decimal a,[digits_to_leave])
删除所有小数点以后的数或删除N位小数
[master:21000] > select truncate(3.45);
+----------------+
| truncate(3.45) |
+----------------+
| 3 |
+----------------+
[master:21000] > select truncate(3.45,1);
+-------------------+
| truncate(3.45, 1) |
+-------------------+
| 3.4 |
+-------------------+
[master:21000] > select dtrunc(3.45);
+--------------+
| dtrunc(3.45) |
+--------------+
| 3 |
+--------------+
[master:21000] > select dtrunc(3.45,1);
+-----------------+
| dtrunc(3.45, 1) |
+-----------------+
| 3.4 |
+-----------------+
