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 之间的余弦相似度:
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 代表的数值:
SELECT from_base('0110',2);--2
SELECT from_base('0110',8);--456
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)
例子:
SELECT ABS(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)
例子:
SELECT ACOS(1);
SELECT ACOS(1.0001);
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)
例子:
SELECT ASIN(0.2);
ATAN
Returns the arc tangent of X, that is, the value whose tangent is X.
支持的语法:
ATAN(X) , ATAN(Y,X)
例子:
SELECT ATAN(2);
SELECT ATAN(-2);
SELECT ATAN2(-2,2);
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)
例子:
SELECT ATAN(2);
SELECT ATAN(-2);
SELECT ATAN2(-2,2);
SELECT ATAN2(PI(),0);
CEILING
Returns the smallest integer value not less than X.
支持的语法:
CEILING(X)
例子:
SELECT CEILING(1.23);
SELECT CEILING(-1.23);
CEIL
Returns the smallest integer value not less than X.
支持的语法:
CEIL(X)
例子:
SELECT CEIL(1.23);
SELECT CEIL(-1.23);
COS
Returns the cosine of X, where X is given in radians.
支持的语法:
COS(X)
例子:
SELECT COS(PI());
COT
Returns the cotangent of X.
支持的语法:
COT(X)
例子:
SELECT COT(12);
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)
例子:
SELECT CRC32('MySQL');
SELECT CRC32('mysql');
DEGREES
Returns the argument X, converted from radians to degrees.
支持的语法:
DEGREES(X)
例子:
SELECT DEGREES(PI());
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)
例子:
SELECT EXP(2);
SELECT EXP(-2);
SELECT EXP(0);
FLOOR
Returns the largest integer value not greater than X.
支持的语法:
FLOOR(X)
例子:
SELECT FLOOR(1.23);
SELECT FLOOR(-1.23);
LN
Returns the natural logarithm of X; that is, the base-e logarithm of X.
支持的语法:
LN(X)
例子:
SELECT LN(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)
例子:
SELECT LOG(2);
SELECT LOG(-2);
SELECT LOG(2,65536);
SELECT LOG(10,100);
SELECT LOG(1,100);
SELECT LOG2(65536);
SELECT LOG2(-100);
SELECT LOG10(2);
SELECT LOG10(100);
SELECT LOG10(-100);
MOD
Modulo operation. Returns the remainder of N divided by M.
支持的语法:
MOD(N,M)
例子:
SELECT MOD(234,10);
SELECT253%7;
SELECT MOD(34.5,3);
POW
Returns the value of X raised to the power of Y.
支持的语法:
POW(X,Y)
例子:
SELECT POW(2,2);
SELECT POW(2,-2);
POWER
Returns the value of X raised to the power of Y.
支持的语法:
POWER(X,Y)
例子:
SELECT POWER(2,2);
SELECT POWER(2,-2);
RADIANS
Returns the argument X, converted from degrees to radians.
支持的语法:
RADIANS(X)
例子:
SELECT RADIANS(90);
RAND
Returns a random floating-point value v in the range 0 <= v < 1.0.
支持的语法:
RAND([N])
例子:
SELECT RAND();
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)
例子:
SELECT ROUND(-1.23);
SELECT ROUND(23.298,-1);
SELECT ROUND(1.298,0);
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)
例子:
SELECT SIGN(-32);
SELECT SIGN(0);
SELECT SIGN(234);
SIN
Returns the sine of X, where X is given in radians.
支持的语法:
SIN(X)
例子:
SELECT SIN(PI());
SELECT ROUND(SIN(PI()));
SQRT
Returns the square root of a nonnegative number X.
支持的语法:
SQRT(X)
例子:
SELECT SQRT(4);
SELECT SQRT(20);
SELECT SQRT(-16);
TAN
Returns the tangent of X, where X is given in radians.
支持的语法:
TAN(X)
例子:
SELECT TAN(PI());
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)
例子:
SELECT TRUNCATE(1.223,1);
SELECT TRUNCATE(1.999,1);
SELECT TRUNCATE(1.999,0);
SELECT TRUNCATE(-1.999,1);
SELECT TRUNCATE(122,2);
Math函数Oracle兼容性(ADS 已经支持的Oracle函数)
REMAINDER
REMAINDER returns the remainder of n2 divided by n1.
支持的语法:
REMAINDER(n1, n2)
例子:
SELECT REMAINDER(3.5,2);
SELECT REMAINDER(11,4);
TANH
TANH returns the hyperbolic tangent of n.
支持的语法:
TANH(n)
例子:
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)
例子:
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)
例子:
SELECT WIDTH_BUCKET(5,3,4,5);