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类型后参与运算,其它类型抛异常。

返回值

返回Double、Bigint或Decimal类型,取决于输入参数的类型。若输入为null,则返回null。

说明
当输入Bigint类型的值超过Bigint的最大表示范围时,会返回Double类型,这种情况下可能会损失精度。
示例如下:
abs(null)=null
abs(-1)=1
abs(-1.2)=1.2
abs("-2")=2.0
abs(122320837456298376592387456923748)=1.2232083745629837e32
下面是一个完整的ABS函数在SQL中使用的示例,其他内建函数(除窗口函数、聚合函数外)的使用方式与其类似,不再举例。
select abs(id) from tbl1;
-- 取tbl1表内id字段的绝对值

ACOS

命令格式如下
Double acos(Double number)
Decimal acos(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型,-1 ≤ number ≤1。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型,值域在0 ~ π之间。若number为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 ≤ number ≤1。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型,值域在-π/2 ~π/2之间。若number为null,返回null。

示例如下
asin(1)=1.5707963267948966
asin(-1)=-1.5707963267948966

ATAN

命令格式如下
Double atan(Double number)

命令说明如下

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

参数说明

number:Double类型,若输入为String类型或Bigint类型,会隐式转换到Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型,值域在-π/2~π/2之间。若number为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_baseto_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类型。若number为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类型。若number为null,返回null。

COT

命令格式如下
Double cot(Double number)
Decimal cot(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为null,则返回null。

EXP

命令格式如下
Double exp(Double number)
Decimal exp(Decimal number)

命令说明如下

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

返回值

返回number的指数值。

参数说明

number:Double类型或Decimal类型。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为null,则返回null。

FLOOR

命令格式如下
Bigint floor(Double number)
Bigint floor(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型,若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Bigint类型。若number为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类型后参与运算,其他类型抛异常。
  • 若number为null,则返回null。若number为负数或零,则执行报错。

返回值

返回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类型的对数值。
  • 若base和x中存在null,则返回null。
  • 若base和x中某一个值为负数或 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类型。若x或y为null,则返回null。

RAND

命令格式如下
Double rand(Bigint seed)

命令说明如下

该函数以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_place:Bigint类型常量,四舍五入计算到小数点后的位置,其他类型参数会引发异常。如果省略表示四舍五入到个位数,默认值为0。

返回值

返回Double类型或Decimal类型。若numberDecimal_places为null,则返回null。

说明
Decimal_places可以是负数。负数会从小数点向左开始计数,并且不保留小数部分。如果Decimal_places超过了整数部分长度,返回0。
示例如下
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类型。若number为null,则返回null。

SINH

命令格式如下
Double sinh(Double number)
Decimal sinh(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为null,则返回null。

SQRT

命令格式如下
Double sqrt(Double number)
Decimal sqrt(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型,必须大于0,小于0时引发异常。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为null,则返回null。

TAN

命令说明如下
Double tan(Double number)
Decimal tan(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为null,则返回null。

TANH

命令格式如下
Double tanh(Double number)
Decimal tanh(Decimal number)

命令说明如下

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

参数说明

number:Double类型或Decimal类型。若输入为String类型或Bigint类型,会隐式转换为Double类型后参与运算,其他类型抛异常。

返回值

返回Double类型或Decimal类型。若number为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,省略此参数时默认到截取到个位数。

返回值

返回值类型为Double或Decimal类型。若numberDecimal_places为null,则返回null。

说明
  • 返回Double类型时,返回的结果的显示可能不符合预期,如示例trunc(125.815, 1)(这个Double类型显示问题任何系统都存在)。
  • 截取掉的部分补0。
  • Decimal_places可以是负数,负数会从小数点向左开始截取,并且不保留小数部分。如果Decimal_places超过了整数部分长度,则返回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后,产品扩展部分数学函数,新函数若用到新数据类型时,在使用新函数的SQL前,需要加一个set语句:
set odps.sql.type.system.odps2=true;

下文将为您详细介绍新扩展的函数。

LOG2

命令格式如下
Double log2(Double number)
Double log2(Decimal number)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于以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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

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

参数说明

number:Double或Decimal类型。

返回值

返回Double类型。若输入为0或null,则返回null。

示例如下
log10(null)=null
log10(0)=null
log10(8)=0.9030899869919435

BIN

命令格式如下
String bin(Bigint number)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于返回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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

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

参数说明

number:如果number是Bigint类型,那么返回number的十六进制表示。如果变量是String类型,则返回该字符串的十六进制表示。

返回值

返回String类型。若输入为0,返回0,输入为null,则返回异常。

示例如下
hex(0)=0
hex('abc')='616263'
hex(17)='11'
hex('17')='3137'
hex(null)异常返回失败
说明
当输入参数为Binary类型时,请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

UNHEX

命令格式如下
BINARY unhex(String number)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

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

参数说明

number:为十六进制字符串。

返回值

返回Binary类型,若输入0,返回失败,若输入为null,则返回null。

示例如下
unhex('616263')='abc'
unhex(616263)='abc'

RADIANS

命令格式如下
Double radians(Double number)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

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

参数说明

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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

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

参数说明

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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于取输入数据的符号,1.0表示正,-1.0表示负,否则0.0。

参数说明

number:Double或Decimal类型数据。

返回值

返回Double类型,若输入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()
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于返回e的值。

返回值

返回Double类型。

示例如下
e()=2.718281828459045

PI

命令格式如下
Double pi()
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于返回π的值。

返回值

返回Double类型。

示例如下
pi()=3.141592653589793

FACTORIAL

命令格式如下
Bigint factorial(Int number)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于返回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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于计算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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于按位左移(<<)。

参数说明
  • number1:Tinyint|Smallint|Int|Bigint整型数据。
  • number2:Int整型数据。

返回值

返回Int或Bingint类型。

示例如下
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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于按位右移(>>)。

参数说明
  • 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)
说明
请在用到该函数的SQL语句前加set odps.sql.type.system.odps2=true;,并与SQL一起提交运行,以便正常使用新数据类型。

命令说明如下

该函数用于无符号按位右移(>>>)。

参数说明
  • 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)