本文向您介绍MaxCompute SQL支持的数学函数命令及说明。

ABS

  • 命令格式 
    Double abs(Double number)
Bigint abs(Bigint number)
Decimal abs(Decimal number)
  • 命令说明

    该函数用于返回number的绝对值。

  • 参数说明
    number：当number为DOUBLE、BIGINT或DECIMAL类型时会返回相应的类型。
    • 输入为BIGINT，则返回BIGINT类型。
    • 输入为DOUBLE，则返回DOUBLE类型。
    • 输入为DECIMAL，则返回DECIMAL类型。
    • 输入为STRING，则隐式转换为DOUBLE类型后参与运算。
    • 输入非以上4种类型，则返回异常。
    说明 当输入BIGINT类型的值超过BIGINT的最大表示范围时，会返回DOUBLE类型，但可能会损失精度。
  • 返回值

    返回值类型取决于输入参数的类型，包含DOUBLE、BIGINT或DECIMAL类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    abs(null)=null
abs(-1)=1
abs(-1.2)=1.2
abs("-2")=2.0
abs(122320837456298376592387456923748)=1.2232083745629837e32
    下面是一个完整的ABS函数在SQL中使用的示例，其他内建函数（除窗口函数和聚合函数外）的使用方式与其类似。 
    //取tbl1表内id字段的绝对值。
select abs(id) from tbl1;

ACOS

  • 命令格式 
    Double acos(Double number)
Decimal acos(Decimal number)
  • 命令说明

    该函数用于计算number的反余弦函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型，取值范围为[-1,1]。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。输入为其他数据类型时则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型，值域在0~π之间。如果输入参数为NULL，则返回NULL。

  • 示例 
    acos("0.87")=0.5155940062460905
acos(0)=1.5707963267948966

ASIN

  • 命令格式 
    Double asin(Double number)
Decimal asin(Decimal number)
  • 命令说明

    该函数用于计算number的反正弦函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型，取值范围为[-1,1]。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他数据类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型，值域在-π/2~π/2之间。如果输入参数为NULL，则返回NULL。

  • 示例 
    asin(1)=1.5707963267948966
asin(-1)=-1.5707963267948966

ATAN

  • 命令格式 
    Double atan(Double number)
  • 命令说明

    该函数用于计算number的反正切函数。

  • 参数说明

    number：DOUBLE类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他数据类型则返回异常。

  • 返回值说明

    返回DOUBLE类型，值域在-π/2~π/2之间。如果输入参数为NULL，则返回NULL。

  • 示例 
    atan(1)=0.7853981633974483
atan(-1)=-0.7853981633974483

CEIL

  • 命令格式 
    Bigint ceil(Double value)
Bigint ceil(Decimal value)
  • 命令说明

    向上取整，函数返回不小于输入值value的最小整数。

  • 参数说明

    value：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回BIGINT类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    ceil(1.1)=2
ceil(-1.1)=-1

CONV

  • 命令格式 
    String conv(String input, Bigint from_base, Bigint to_base)
  • 命令说明

    该函数为进制转换函数。

  • 参数说明
    • input：以STRING表示的要转换的整数值，支持BIGINT和DOUBLE的隐式转换。
    • from_base、to_base：以十进制表示的进制值，支持的值为2、8、10和16。支持STRING及DOUBLE类型的隐式转换。
  • 返回值说明

    返回STRING类型。如果任一输入参数为NULL，则返回NULL。转换过程以64位精度工作，溢出时返回异常。输入如果是负值，即以短划线（-）开头，返回异常。如果输入的是小数，会转为整数值后进行进制转换，小数部分会被舍弃。

  • 示例 
    conv('1100', 2, 10)='12'
conv('1100', 2, 16)='c'
conv('ab', 16, 10)='171'
conv('ab', 16, 16)='ab'

COS

  • 命令格式 
    Double cos(Double number)
Decimal cos(Decimal number)
  • 命令说明

    该函数用于计算number的余弦函数，输入为弧度值。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    cos(3.1415926/2)=2.6794896585028633e-8
cos(3.1415926)=-0.9999999999999986

COSH

  • 命令格式 
    Double cosh(Double number)
Decimal cosh(Decimal number)
  • 命令说明

    该函数用于计算number的双曲余弦函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

COT

  • 命令格式 
    Double cot(Double number)
Decimal cot(Decimal number)
  • 命令说明

    该函数用于计算number的余切函数，输入为弧度值。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

EXP

  • 命令格式 
    Double exp(Double number)
Decimal exp(Decimal number)
  • 命令说明

    该函数用于计算number的指数函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回number的指数值。返回值为DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

FLOOR

  • 命令格式 
    Bigint floor(Double number)
Bigint floor(Decimal number)
  • 命令说明

    向下取整，函数返回不大于number的最大整数值。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。

  • 返回值说明

    返回BIGINT类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    floor(1.2)=1
floor(1.9)=1
floor(0.1)=0
floor(-1.2)=-2
floor(-0.1)=-1
floor(0.0)=0
floor(-0.0)=0

LN

  • 命令格式 
    Double ln(Double number)
Decimal ln(Decimal number)
  • 命令说明

    该函数用于返回number的自然对数。

  • 参数说明
    number：DOUBLE类型或DECIMAL类型。
    • 如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。
    • 如果输入参数为NULL，则返回NULL；如果输入参数为负数或0，则返回异常。
  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。

LOG

  • 命令格式 
    Double log(Double base, Double x)
Decimal log(Decimal base, Decimal x)
  • 命令说明

    该函数用于返回以base为底的x的对数。

  • 参数说明
    • base：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。
    • x：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。
  • 返回值说明
    返回DOUBLE类型或DECIMAL类型的对数值。
    • 如果任一输入参数为NULL，则返回NULL。
    • 如果任一输入参数为负数或0，则返回异常。
    • 如果base为1（会引发一个除零行为），也会返回异常。

POW

  • 命令格式 
    Double pow(Double x, Double y)
Decimal pow(Decimal x, Decimal y)
  • 命令说明

    该函数用于返回x的y次方，即x^y。

  • 参数说明
    • x：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。
    • y：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入为其他类型则返回异常。
  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果任一输入参数为NULL，则返回NULL。

RAND

  • 命令格式 
    Double rand(Bigint seed)
  • 命令说明

    该函数返回DOUBLE类型的随机数，返回值区间是0～1。

  • 参数说明

    seed：可选参数，BIGINT类型，随机数种子，决定随机数序列的起始值。

  • 返回值说明

    返回DOUBLE类型。

  • 示例 
    select rand() from dual;
select rand(1) from dual;

ROUND

  • 命令格式 
    Double round(Double number, [Bigint Decimal_places])
Decimal round(Decimal number, [Bigint Decimal_places])
  • 命令说明

    该函数四舍五入到指定小数点位置。

  • 参数说明
    • number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入是其他类型则返回异常。
    • Decimal_places：BIGINT类型常量，四舍五入计算到小数点后的位置，其他类型参数会引发异常。如果省略表示四舍五入到个位数，默认值为0。
      说明 Decimal_places可以是负数。负数会从小数点向左开始计数，并且不保留小数部分。如果Decimal_places超过了整数部分长度，返回0。
  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果任一输入参数为NULL，则返回NULL。

  • 示例 
    round(125.315)=125.0
round(125.315, 0)=125.0
round(125.315, 1)=125.3
round(125.315, 2)=125.32
round(125.315, 3)=125.315
round(-125.315, 2)=-125.32
round(123.345, -2)=100.0
round(null)=null
round(123.345, 4)=123.345
round(123.345, -4)=0.0

SIN

  • 命令格式 
    Double sin(Double number)
Decimal sin(Decimal number)
  • 命令说明

    该函数用于计算number的正弦函数，输入为弧度值。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

SINH

  • 命令格式 
    Double sinh(Double number)
Decimal sinh(Decimal number)
  • 命令说明

    该函数用于计算number的双曲正弦函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

SQRT

  • 命令格式 
    Double sqrt(Double number)
Decimal sqrt(Decimal number)
  • 命令说明

    该函数用于计算number的平方根。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型，必须大于0，小于0时返回异常。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

TAN

  • 命令说明 
    Double tan(Double number)
Decimal tan(Decimal number)
  • 命令说明

    该函数用于计算number的正切函数，输入为弧度值。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

TANH

  • 命令格式 
    Double tanh(Double number)
Decimal tanh(Decimal number)
  • 命令说明

    该函数用于计算number的双曲正切函数。

  • 参数说明

    number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。

  • 返回值说明

    返回DOUBLE类型或DECIMAL类型。如果输入参数为NULL，则返回NULL。

TRUNC

  • 命令格式 
    Double trunc(Double number[, Bigint Decimal_places])
Decimal trunc(Decimal number[, Bigint Decimal_places])
  • 命令说明

    该函数用于将输入值number截取到指定小数点位置。

  • 参数说明
    • number：DOUBLE类型或DECIMAL类型。如果输入为STRING类型或BIGINT类型，会隐式转换为DOUBLE类型后参与运算。如果输入其他类型则返回异常。
    • Decimal_places：BIGINT类型常量，要截取到的小数点位置。其他类型参数会隐式转为BIGINT，省略此参数时默认截取到个位数。
      说明 Decimal_places可以是负数，负数会从小数点向左开始截取，并且不保留小数部分。如果Decimal_places超过了整数部分长度，则返回0。
  • 返回值说明

    返回值类型为DOUBLE或DECIMAL类型。如果任一输入参数为NULL，则返回NULL。

    说明
    • 返回DOUBLE类型时，返回的结果显示可能不符合预期，例如trunc(125.815,1)（这个DOUBLE类型显示问题任何系统都存在）。
    • 截取掉的部分补0。
  • 示例 
    trunc(125.815)=125.0
trunc(125.815,0)=125.0
trunc(125.815,1)=125.80000000000001
trunc(125.815,2)=125.81
trunc(125.815,3)=125.815
trunc(-125.815,2)=-125.81
trunc(125.815,-1)=120.0
trunc(125.815,-2)=100.0
trunc(125.815,-3)=0.0
trunc(123.345,4)=123.345
trunc(123.345,-4)=0.0

MaxCompute2.0扩展函数

在使用MaxCompute2.0扩展函数涉及到新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY）时，需要使用set命令开启新数据类型。
  • Session级别：如果使用新数据类型，您需要在SQL语句前加上set语句set odps.sql.type.system.odps2=true;，并与SQL语句一起提交执行。
  • Project级别：Project Owner可根据需要对Project进行设置，等待10~15分钟后才会生效。命令如下。 
    setproject odps.sql.type.system.odps2=true;
    对setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项，请参见数据类型版本说明。

LOG2

  • 命令格式 
    Double log2(Double number)
Double log2(Decimal number)
  • 命令说明

    该函数用于返回以2为底，number的对数。

  • 参数说明

    number：DOUBLE或DECIMAL类型。

  • 返回值说明

    返回DOUBLE类型。如果输入参数为0或NULL，则返回NULL。

  • 示例 
    log2(null)=null
log2(0)=null
log2(8)=3.0

LOG10

  • 命令格式 
    Double log10(Double number)
Double log10(Decimal number)
  • 命令说明

    该函数用于返回以10为底，number的对数。

  • 参数说明

    number：DOUBLE或DECIMAL类型。

  • 返回值说明

    返回DOUBLE类型。如果输入参数为0或NULL，则返回NULL。

  • 示例 
    log10(null)=null
log10(0)=null
log10(8)=0.9030899869919435

BIN

  • 命令格式 
    String bin(Bigint number)
  • 命令说明

    该函数用于返回number的二进制代码表示。

  • 参数说明

    number：BIGINT类型。

  • 返回值说明

    返回STRING类型。如果输入参数为0，则返回0；如果输入参数为NULL，则返回NULL。

  • 示例 
    bin(0)='0'
bin(null)='null'
bin(12)='1100'

HEX

  • 命令格式 
    String hex(Bigint number) 
String hex(String number)
String hex(BINARY number)
  • 命令说明

    该函数用于将整数或字符串转换为十六进制格式。

  • 参数说明

    number：如果number是BIGINT类型，那么返回number的十六进制表示。如果number是STRING类型，则返回该字符串的十六进制表示。

  • 返回值说明
    • 如果输入非0或非NULL参数，则返回STRING类型。
    • 如果输入参数为0，则返回0。
    • 如果输入参数为NULL，则返回异常。
  • 示例 
    hex(0)=0
hex('abc')='616263'
hex(17)='11'
hex('17')='3137'
hex(null)返回异常信息执行失败。

UNHEX

  • 命令格式 
    BINARY unhex(String number)
  • 命令说明

    该函数用于返回十六进制字符串所代表的字符串。

  • 参数说明

    number：为十六进制字符串。

  • 返回值说明

    返回BINARY类型。如果输入参数为0，则返回异常；如果输入参数为NULL，则返回NULL。

  • 示例 
    unhex('616263')='abc'
unhex(616263)='abc'

RADIANS

  • 命令格式 
    Double radians(Double number)
  • 命令说明

    该函数用于将角度转换为弧度。

  • 参数说明

    number：DOUBLE类型数据。

  • 返回值说明

    返回DOUBLE类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    radians(90)=1.5707963267948966
radians(0)=0.0
radians(null)=null

DEGREES

  • 命令格式 
    Double degrees(Double number) 
Double degrees(Decimal number)
  • 命令说明

    该函数用于将弧度转换为角度。

  • 参数说明

    number：DOUBLE或DECIMAL类型数据。

  • 返回值说明

    返回DOUBLE类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    degrees(1.5707963267948966)=90.0
degrees(0)=0.0
degrees(null)=null

SIGN

  • 命令格式 
    Double sign(Double number)
Double sign(Decimal number)
  • 命令说明

    该函数用于获取输入参数的符号。

  • 参数说明

    number：DOUBLE或DECIMAL类型数据。

  • 返回值说明
    返回DOUBLE类型。
    • 如果输入参数为正数，则返回1.0。
    • 如果输入参数为负数，则返回-1.0。
    • 如果输入参数为0，则返回0.0。
    • 如果输入参数为NULL，则返回NULL。
  • 示例 
    sign(-2.5)=-1.0
sign(2.5)=1.0
sign(0)=0.0
sign(null)=null

E

  • 命令格式 
    Double e()
  • 命令说明

    该函数用于返回e的值。

  • 返回值说明

    返回DOUBLE类型。

  • 示例 
    e()=2.718281828459045

PI

  • 命令格式 
    Double pi()
  • 命令说明

    该函数用于返回π的值。

  • 返回值说明

    返回DOUBLE类型。

  • 示例 
    pi()=3.141592653589793

FACTORIAL

  • 命令格式 
    Bigint factorial(Int number)
  • 命令说明

    该函数用于返回number的阶乘。

  • 参数说明

    number：INT类型数据，且在[0,20]之间。

  • 返回值说明

    返回BIGINT类型。如果输入参数为0，则返回1；如果输入参数为NULL或[0,20]之外的值，则返回NULL。

  • 示例 
    factorial(5)=120 --5!=5*4*3*2*1=120

CBRT

  • 命令格式 
    Double cbrt(Double number)
  • 命令说明

    该函数用于计算number的立方根。

  • 参数说明

    number：DOUBLE类型数据。

  • 返回值说明

    返回DOUBLE类型。如果输入参数为NULL，则返回NULL。

  • 示例 
    cbrt(8)=2
cbrt(null)=null

SHIFTLEFT

  • 命令格式 
    Int shiftleft(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftleft(Bigint number1, Int number2)
  • 命令说明

    该函数用于按位左移（<<）。

  • 参数说明
    • number1：TINYINT、SMALLINT、INT或BIGINT整型数据。
    • number2：INT整型数据。
  • 返回值说明

    返回INT或BIGINT类型。

  • 示例 
    shiftleft(1,2)=4  //1的二进制左移2位（1<<2,0001左移两位是0100）。
shiftleft(4,3)=32  //4的二进制左移3位（4<<3,0100左移3位是100000）。

SHIFTRIGHT

  • 命令格式 
    Int shiftright(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftright(Bigint number1, Int number2)
  • 命令说明

    该函数用于按位右移（>>）。

  • 参数说明
    • number1：TINYINT、SMALLINT、INT或BIGINT整型数据。
    • number2：INT整型数据。
  • 返回值说明

    返回INT或BIGINT类型。

  • 示例 
    shiftright(4,2)=1 //4的二进制右移2位（4>>2,0100右移两位是0001）。
shiftright(32,3)=4 //32的二进制右移3位（32>>3,100000右移3位是0100）。

SHIFTRIGHTUNSIGNED

  • 命令格式 
    Int shiftrightunsigned(Tinyint|Smallint|Int number1, Int number2)
Bigint shiftrightunsigned(Bigint number1, Int number2)
  • 命令说明

    该函数用于无符号按位右移（>>>）。

  • 参数说明
    • number1：TINYINT、SMALLINT、INT或BIGINT整型数据。
    • number2：INT整型数据。
  • 返回值说明

    返回INT或BIGINT类型。

  • 示例 
    shiftrightunsigned(8,2)=2  //8的二进制无符号右移2位(8>>>2,1000右移两位是0010)。
shiftrightunsigned(-14,2)=1073741820  //-14的二进制右移2位(-14>>>2, 11111111 11111111 11111111 11110010右移2位是00111111 11111111 11111111 11111100)。

FORMAT_NUMBER

  • 命令格式 
    string format_number(float|double|decimal expr1, expr2)
  • 命令说明

    将数字转化为指定格式的字符串。

  • 参数说明
    • expr1：需要格式化的数据。
    • expr2：需要转化的目标格式。可以为INT类型，指定需要保留小数位数。也可以为类似#,###,###.##格式描述。
      • 如果expr2>0，则四舍五入到小数点后指定位数。
      • 如果expr2=0，则没有小数点或小数部分。
      • 如果expr2<0，或者expr2>340，则返回异常。
  • 返回值说明

    返回STRING类型数据。

  • 示例 
    select format_number(5.230134523424545456,3);//返回5.230。
select format_number(12332.123456, '#,###,###,###.###');//返回12,332.123。