全部产品
阿里云办公

数学函数

更新时间:2017-10-11 17:41:26

abs(x) → [same as input]

返回 x 的绝对值.

cbrt(x) → double

返回 x 的立方根.

ceil(x) → [same as input]

ceiling() 的同名方法.

ceiling(x) → [same as input]

返回 x 的向上取整的数值.

cosine_similarity(x, y) → double

返回稀疏向量 x 和 y 之间的余弦相似度:

  1. SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.0

degrees(x) → double

将角度 x 以弧度转换为度.

e() → double

返回欧拉常量.

exp(x) → double

返回 x 的欧拉常量次幂.

floor(x) → [same as input]

返回 x 向下取整的最近整数值.

from_base(string, radix) → bigint

返回 radix 进制的字符串 string 代表的数值:

  1. SELECT from_base('0110', 2); -- 2
  2. SELECT from_base('0110', 8); -- 456
  3. SELECT from_base('00a0', 16); -- 160

ln(x) → double

返回 x 的自然对数.

log2(x) → double

返回 x 以2为底的对数.

log10(x) → double

返回 x 以10为底的对数.

log(x, b) → double

返回 x 以 b 为底的对数.

mod(n, m) → [same as input]

返回 n 除 m 的模数(余数).

pi() → double

返回常量Pi.

pow(x, p) → double

power() 的同名方法.

power(x, p) → double

返回 x 的 p 次幂.

radians(x) → double

将角度 x 以度为单位转换为弧度.

rand() → double

random() 的同名方法.

random() → double

返回 0.0 <= x < 1.0 范围内的伪随机数.

random(n) → [same as input]

返回 0 <= x < n 范围内的伪随机数.

round(x) → [same as input]

返回 x 四舍五入后的最近的整数值.

round(x, d) → [same as input]

返回 x 四舍五入到 d 位小数位的值.

sign(x) → [same as input]

x 的正负号函数, 即:

  • x 为0, 返回0,

  • x 为正, 返回1,

  • x 为负, 返回-1.

对于double类型参数, 则:

  • x 为NaN, 返回NaN,

  • x 为正无穷, 返回1,

  • x 为负无穷, 返回-1.

sqrt(x) → double

返回 x 的平方根.

to_base(x, radix) → varchar

返回 x 的 radix 进制表示的字符串.

truncate(x) → double

舍弃 x 的小数位,返回整数值.

width_bucket(x, bound1, bound2, n) → bigint

bound1 到 bound2 范围等长划分成n个桶, 返回x在其中的桶号.

width_bucket(x, bins) → bigint

返回 x 在数组 bins 描述的分桶中的桶号. 参数 bins 必须是一个double类型的数组, 并且嘉定是按照升序排序的.

三角函数

所有三角函数都是以弧度表示. 单位转换请参考 degrees() 和 radians().

acos(x) → double

返回 x 的反余弦.

asin(x) → double

返回 x 的反正弦.

atan(x) → double

返回 x 的反正切.

atan2(y, x) → double

返回 y / x 的反正切.

cos(x) → double

返回 x 的余弦值.

cosh(x) → double

返回 x 的双曲余弦值.

sin(x) → double

返回 x 的正弦值.

tan(x) → double

返回 x 的正切值.

tanh(x) → double

返回 x 的双曲正切.

浮点函数

infinity() → double

返回表示正无穷大的常量.

is_finite(x) → boolean

判定 x 是否有限.

is_infinite(x) → boolean

判定 x 是否无限.

is_nan(x) → boolean

判定 x 是非法数值.

nan() → double

返回代表非数值的常量值.

Math 函数MySQL兼容性(ADS 已经支持的MySQL函数)

ABS

Returns the absolute value of X.

支持的语法:

ABS(X)

例子:

  1. SELECT ABS(2);
  2. SELECT ABS(-32);
ACOS

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1.

支持的语法:

ACOS(X)

例子:

  1. SELECT ACOS(1);
  2. SELECT ACOS(1.0001);
  3. SELECT ACOS(0);
ASIN

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1.

支持的语法:

ASIN(X)

例子:

  1. SELECT ASIN(0.2);
ATAN

Returns the arc tangent of X, that is, the value whose tangent is X.

支持的语法:

ATAN(X) , ATAN(Y,X)

例子:

  1. SELECT ATAN(2);
  2. SELECT ATAN(-2);
  3. SELECT ATAN2(-2,2);
  4. SELECT ATAN2(PI(),0);
ATAN2

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.

支持的语法:

ATAN2(Y,X)

例子:

  1. SELECT ATAN(2);
  2. SELECT ATAN(-2);
  3. SELECT ATAN2(-2,2);
  4. SELECT ATAN2(PI(),0);
CEILING

Returns the smallest integer value not less than X.

支持的语法:

CEILING(X)

例子:

  1. SELECT CEILING(1.23);
  2. SELECT CEILING(-1.23);
CEIL

Returns the smallest integer value not less than X.

支持的语法:

CEIL(X)

例子:

  1. SELECT CEIL(1.23);
  2. SELECT CEIL(-1.23);
COS

Returns the cosine of X, where X is given in radians.

支持的语法:

COS(X)

例子:

  1. SELECT COS(PI());
COT

Returns the cotangent of X.

支持的语法:

COT(X)

例子:

  1. SELECT COT(12);
  2. SELECT COT(0);
CRC32

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL.

支持的语法:

CRC32(expr)

例子:

  1. SELECT CRC32('MySQL');
  2. SELECT CRC32('mysql');
DEGREES

Returns the argument X, converted from radians to degrees.

支持的语法:

DEGREES(X)

例子:

  1. SELECT DEGREES(PI());
  2. SELECT DEGREES(PI() / 2);
EXP

Returns the value of e (the base of natural logarithms) raised to the power of X. The inverse of this function is LOG()

支持的语法:

EXP(X)

例子:

  1. SELECT EXP(2);
  2. SELECT EXP(-2);
  3. SELECT EXP(0);
FLOOR

Returns the largest integer value not greater than X.

支持的语法:

FLOOR(X)

例子:

  1. SELECT FLOOR(1.23);
  2. SELECT FLOOR(-1.23);
LN

Returns the natural logarithm of X; that is, the base-e logarithm of X.

支持的语法:

LN(X)

例子:

  1. SELECT LN(2);
  2. SELECT LN(-2);
LOG, LOG2, LOG10

If called with one parameter, this function returns the natural logarithm of X.

支持的语法:

LOG(X) , LOG2(X) , LOG10(X)

例子:

  1. SELECT LOG(2);
  2. SELECT LOG(-2);
  3. SELECT LOG(2,65536);
  4. SELECT LOG(10,100);
  5. SELECT LOG(1,100);
  6. SELECT LOG2(65536);
  7. SELECT LOG2(-100);
  8. SELECT LOG10(2);
  9. SELECT LOG10(100);
  10. SELECT LOG10(-100);
MOD

Modulo operation. Returns the remainder of N divided by M.

支持的语法:

MOD(N,M)

例子:

  1. SELECT MOD(234, 10);
  2. SELECT 253 % 7;
  3. SELECT MOD(34.5,3);
POW

Returns the value of X raised to the power of Y.

支持的语法:

POW(X,Y)

例子:

  1. SELECT POW(2,2);
  2. SELECT POW(2,-2);
POWER

Returns the value of X raised to the power of Y.

支持的语法:

POWER(X,Y)

例子:

  1. SELECT POWER(2,2);
  2. SELECT POWER(2,-2);
RADIANS

Returns the argument X, converted from degrees to radians.

支持的语法:

RADIANS(X)

例子:

  1. SELECT RADIANS(90);
RAND

Returns a random floating-point value v in the range 0 <= v < 1.0.

支持的语法:

RAND([N])

例子:

  1. SELECT RAND();
  2. SELECT RAND(2);
ROUND

Rounds the argument X to D decimal places. The rounding algorithm depends on the data type of X. D defaults to 0 if not specified.

支持的语法:

ROUND(X) , ROUND(X,D)

例子:

  1. SELECT ROUND(-1.23);
  2. SELECT ROUND(23.298, -1);
  3. SELECT ROUND(1.298, 0);
  4. SELECT ROUND(1.298, 1);
SIGN

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.

支持的语法:

SIGN(X)

例子:

  1. SELECT SIGN(-32);
  2. SELECT SIGN(0);
  3. SELECT SIGN(234);
SIN

Returns the sine of X, where X is given in radians.

支持的语法:

SIN(X)

例子:

  1. SELECT SIN(PI());
  2. SELECT ROUND(SIN(PI()));
SQRT

Returns the square root of a nonnegative number X.

支持的语法:

SQRT(X)

例子:

  1. SELECT SQRT(4);
  2. SELECT SQRT(20);
  3. SELECT SQRT(-16);
TAN

Returns the tangent of X, where X is given in radians.

支持的语法:

TAN(X)

例子:

  1. SELECT TAN(PI());
  2. SELECT TAN(PI()+1);
TRUNCATE

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part.

支持的语法:

TRUNCATE(X,D)

例子:

  1. SELECT TRUNCATE(1.223,1);
  2. SELECT TRUNCATE(1.999,1);
  3. SELECT TRUNCATE(1.999,0);
  4. SELECT TRUNCATE(-1.999,1);
  5. SELECT TRUNCATE(122,2);

Math函数Oracle兼容性(ADS 已经支持的Oracle函数)

REMAINDER

REMAINDER returns the remainder of n2 divided by n1.

支持的语法:

REMAINDER(n1, n2)

例子:

  1. SELECT REMAINDER(3.5, 2);
  2. SELECT REMAINDER(11, 4);
TANH

TANH returns the hyperbolic tangent of n.

支持的语法:

TANH(n)

例子:

  1. SELECT TANH(0.5);
BITAND

BITAND computes an AND operation on the bits of expr1 and expr2, both of which must resolve to nonnegative integers, and returns an integer.

支持的语法:

BITAND(expr1, expr2)

例子:

  1. SELECT BITAND(5, 6);
WIDTH_BUCKET

Returns the bin number of x in an equi-width histogram with the specified bound1 and bound2 bounds and n number of buckets.

支持的语法:

WIDTH_BUCKET(x, bound1, bound2, n)

例子:

  1. SELECT WIDTH_BUCKET(5,3,4,5);