Impala 数值函数大全

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             |
+-----------------+