阅读677 返回首页    go 人物

内建函数-上__SQL_大数据计算服务-阿里云

ABS

函数定义：

  1.     double abs(double number)
  2.     bigint abs(bigint number)

用途：返回绝对值。

参数说明：

  • number：Double或bigint类型，输入为bigint时返回bigint，输入为double时返回double类型。若输入为string类型会隐式转换到double类型后参与运算，其它类型抛异常。

返回值：Double或者bigint类型，取决于输入参数的类型。若输入为null，返回null。

备注：

  • 当输入bigint类型的值超过bigint的最大表示范围时，会返回double类型，这种情况下可能会损失精度。

示例：

  1.     abs(null) =  null
  2.     abs(-1) = 1
  3.     abs(-1.2) = 1.2
  4.     abs("-2") = 2.0
  5.     abs(122320837456298376592387456923748) = 1.2232083745629837e32

下面是一个完整的abs函数在SQL中使用的例子，其他内建函数(除窗口函数、聚合函数外)的使用方式与其类似，不再一一举例：

  1.     select abs(id) from tbl1;
  2.     -- 取tbl1表内id字段的绝对值

ACOS

函数定义：

  1.     double acos(double number)

用途：计算number的反余弦函数。

参数说明：

  • number：Double类型，-1≤number≤1。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。

返回值：Double类型，值域在0 ~ π 之间。若number为NULL，返回NULL。

示例：

  1.     acos("0.87") = 0.5155940062460905
  2.     acos(0) = 1.5707963267948966

ASIN

函数定义：

  1.     double asin(double number)

用途：反正弦函数。

参数说明：

  • number：Double类型，-1≤number≤1。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。

返回值：Double类型，值域在-π/2 ~π/2之间。若number为NULL，返回NULL。

示例：

  1.     asin(1) = 1.5707963267948966
  2.     asin(-1) = -1.5707963267948966

ATAN

函数定义：

  1.     double atan(double number)

用途：反正切函数。

参数说明：

  • number：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。

返回值：Double类型，值域在-π/2 ~π/2之间。若number为NULL，返回NULL。

示例：

  1.     atan(1) = 0.7853981633974483
  2.     atan(-1) = -0.7853981633974483

CEIL

函数定义：

  1.     bigint ceil(double value)

用途：返回不小于输入值value的最小整数

参数说明：

  • value：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。

返回值：Bigint类型。任一输入为NULL，返回NULL。

示例：

  1.     ceil(1.1) = 2
  2.     ceil(-1.1) = -1

CONV

函数定义：

  1.     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位精度工作，溢出时报异常。输入如果是负值，即以”-“开头，报异常。如果输入的是小数，则会转为整数值后进行进制转换，小数部分会被舍弃。

示例

  1.     conv('1100', 2, 10) = '12'
  2.     conv('1100', 2, 16) = 'c'
  3.     conv('ab', 16, 10) = '171'
  4.     conv('ab', 16, 16) = 'ab'

COS

函数定义：

  1.     double cos(double number)

用途：余弦函数，输入为弧度值。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。

返回值：Double类型。若number为NULL，返回NULL。

示例：

  1.     cos(3.1415926/2)=2.6794896585028633e-8
  2.     cos(3.1415926)=0.9999999999999986

COSH

函数定义：

  1.     double cosh(double number)

用途：双曲余弦函数。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

COT

函数定义：

  1.     double cot(double number)

用途：余切函数，输入为弧度值。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

EXP

函数定义：

  1.     double exp(double number)

用途：指数函数。返回number的指数值。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

FLOOR

函数定义：

  1.     bigint floor(double number)

用途：向下取整，返回比number小的整数值。

参数说明：

  • number：Double类型，若输入为string类型或bigint型会隐式转换到double类型后参与运算，其他类型抛异常返回值：返回Bigint类型。若number为NULL，返回NULL。

示例

  1.     floor(1.2)=1
  2.     floor(1.9)=1
  3.     floor(0.1)=0
  4.     floor(-1.2)=-2
  5.     floor(-0.1)=-1
  6.     floor(0.0)=0
  7.     floor(-0.0)=0

LN

函数定义：

  1.     double ln(double number)

用途：返回number的自然对数。

参数说明：

  • number：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。若number为NULL返回NULL,若number为负数或零，则抛异常。返回值：Double类型。

LOG

函数定义：

  1.     double log(double base, double x)

用途：返回以base为底的x的对数。

参数说明：

  • base：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。
  • x：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型的对数值，若base和x中存在NULL，则返回NULL；若base和x中某一个值为负数或0，会引发异常；若base为1(会引发一个除零行为)也会引发异常。

POW

函数定义：

  1.     double pow(double x, double y)

用途：返回x的y次方，即x^y。

参数说明：

  • X：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。
  • Y：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若x或y为NULL，则返回NULL

RAND

函数定义：

  1.     double rand(bigint seed)

用途：以seed为种子返回double类型或Decimal类型的随机数，返回值区间是的0～1。

参数说明：

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

返回值：Double类型或Decimal类型。

示例： 

  1. select rand() from dual;
  2. select rand(1) from dual;

ROUND

函数定义：

  1.     double round(double number, [bigint decimal_places])

用途：四舍五入到指定小数点位置。

参数说明：

  • number：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。
  • decimal_place：Bigint类型常量，四舍五入计算到小数点后的位置，其他类型参数会引发异常. 如果省略表示四舍五入到个位数。默认值为0。返回值：返回Double类型。若number或decimal_places为NULL，返回NULL。

备注：

  • decimal_places可以是负数。负数会从小数点向左开始计数，并且不保留小数部分；如果decimal_places超过了整数部分长度，返回0.示例：
  1.     round(125.315) = 125.0
  2.     round(125.315, 0) = 125.0
  3.     round(125.315, 1) = 125.3
  4.     round(125.315, 2) = 125.32
  5.     round(125.315, 3) = 125.315
  6.     round(-125.315, 2) = -125.32
  7.     round(123.345, -2) = 100.0
  8.     round(null) = null
  9.     round(123.345, 4) = 123.345
  10.     round(123.345, -4) = 0.0

SIGN

函数声明：

  1.  sign(x)

用途：判断x是否为正值或者是否为负值。参数说明：x:Double类型或者Decimal类型，可以为常量、函数或者表达式。返回值：

  • 当x为正值时，返回1.0。
  • 当x为负值时，返回-1.0。
  • 当x为0时，返回0.0。
  • 当x为空时，抛异常。

示例： 

  1. select sign(5-13) from dual;
  2. 返回：  
  3. +------------+
  4. | _c0        |
  5. +------------+
  6. | -1.0       |
  7. +------------+

SIN

函数定义：

  1.     double sin(double number)

用途：正弦函数，输入为弧度值。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

SINH

函数定义：

  1.     double sinh(double number)

用途：双曲正弦函数。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

SQRT

函数定义：

  1.     double sqrt(double number)

用途：计算平方根。

参数说明：

  • number：Double类型，必须大于0。小于0时引发异常。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：返回double类型。若number为NULL，返回NULL。

TAN

函数声明：

  1.     double tan(double number)

用途：正切函数，输入为弧度值。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

TANH

函数声明：

  1.     double tanh(double number)

用途：双曲正切函数。

参数说明：

  • number：Double类型。若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。返回值：Double类型。若number为NULL，返回NULL。

TRUNC

函数声明：

  1.     double trunc(double number[, bigint decimal_places])

用途：将输入值number截取到指定小数点位置。

参数说明：

  • number：Double类型，若输入为string类型或bigint类型会隐式转换到double类型后参与运算，其他类型抛异常。
  • decimal_places：Bigint类型常量，要截取到的小数点位置，其他类型参数会隐式转为bigint，省略此参数时默认到截取到个位数。返回值：返回值类型为Double。若number或decimal_places为NULL，返回NULL。

备注:

  • 截取掉的部分补0。
  • decimal_places可以是负数，负数会从小数点向左开始截取，并且不保留小数部分；如果decimal_places超过了整数部分长度，返回0。

示例：

  1.     trunc(125.815) = 125.0
  2.     trunc(125.815, 0) =125.0
  3.     trunc(125.815, 1) = 125.8
  4.     trunc(125.815, 2) = 125.81
  5.     trunc(125.815, 3) = 125.815
  6.     trunc(-125.815, 2) = -125.81
  7.     trunc(125.815, -1) = 120.0
  8.     trunc(125.815, -2) = 100.0
  9.     trunc(125.815, -3) = 0.0
  10.     trunc(123.345, 4) = 123.345
  11.     trunc(123.345, -4) = 0.0

字符串函数

CHAR_MATCHCOUNT

函数声明：

  1.     bigint char_matchcount(string str1, string str2)

用途：用于计算str1中有多少个字符出现在str2中。

参数说明：

  • str1，str2：String类型，必须为有效的UTF-8字符串，如果对比中发现有无效字符则函数返回负值。

返回值：Bigint类型。任一输入为NULL返回NULL。

示例：

  1.     char_matchcount('abd', 'aabc') = 2
  2.     -- str1中得两个字符串'a', 'b'在str2中出现过

CHR

函数声明：

  1.     string chr(bigint ascii)

用途：将给定ASCII码ascii转换成字符。

参数说明：

  • ascii：Bigint类型ASCII值，若输入为string类型或double类型会隐式转换到bigint类型后参与运算，其它类型抛异常。

返回值：String类型。参数范围是0~255，超过此范围会引发异常。输入值为NULL返回NULL。

CONCAT

函数声明：

  1.     string concat(string a, string b...)

用途：返回值是将参数中的所有字符串连接在一起的结果。

参数说明：

  • a，b等为String类型，若输入为bigint，double或datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：String类型。如果没有参数或者某个参数为NULL，结果均返回NULL。

示例：

  1.     concat('ab', 'c') = 'abc'
  2.     concat() = NULL
  3.     concat('a', null, 'b') = NULL

GET_JSON_OBJECT

函数声明:

  1.     STRING GET_JSON_OBJECT(STRING json,STRING path)

用途：在一个标准json字符串中，按照path抽取指定的字符串.

参数说明：

  • json:String类型，标准的json格式字符串。
  • path: String类型，用于描述在json中的path，以$开头。 关于新实现中json path的说明：参考https://goessner.net/articles/JsonPath/index.html#e2 $表示根节点 “.”表示child “[number]”表示数组下标 对于数组，格式为 key[sub1][sub2][sub3]…… []返回整个数组* 不支持转义

返回值：String类型

注解：

  • 如果json为空或者非法的json格式，返回NULL
  • 如果path为空或者不合法（json中不存在）返回NULL
  • 如果json合法，path也存在则返回对应字符串

示例1

  1. +----+
  2.  json
  3. +----+
  4. {"store":
  5.    {"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
  6.       "bicycle":{"price":19.95,"color":"red"}
  7.    },
  8.    "email":"amy@only_for_json_udf_test.net",
  9.    "owner":"amy"
  10.  }

通过以下查询，可以提取json对象中的信息：

  1.  odps> SELECT get_json_object(src_json.json, '$.owner') FROM src_json;
  2.  amy
  3.  odps> SELECT get_json_object(src_json.json, '$.store.fruit[0]') FROM src_json;
  4.  {"weight":8,"type":"apple"}
  5.  odps> SELECT get_json_object(src_json.json, '$.non_exist_key') FROM src_json;
  6.  NULL

示例2

  1. get_json_object('{"array":[[aaaa,1111],[bbbb,2222],[cccc,3333]]}','$.array[1].[1]') = "2222"
  3. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]') = "["h0","h1","h2"]"
  5. get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]') = "h1"

INSTR

函数声明：

  1.     bigint instr(string str1, string str2[, bigint start_position[, bigint nth_appearance]])

用途：计算一个子串str2在字符串str1中的位置。

参数说明：

  • str1：String类型，搜索的字符串，若输入为bigint，decimal，double或datetime类型会隐式转换为string后参与运算，其它类型报异常。
  • str2：String类型，要搜索的子串，若输入为bigint，decimal，double或datetime类型会隐式转换为string后参与运算，其它类型报异常。
  • start_position：Bigint类型，其它类型会抛异常，表示从str1的第几个字符开始搜索，默认起始位置是第一个字符位置1。当start_position为负数时表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1，往前数依次就是-2，-3…。
  • nth_appearance：Bigint类型，大于0，表示子串在字符串中的第nth_appearance次匹配的位置，如果nth_appearance为其它类型或小于等于0会抛异常。

返回值：Bigint类型。

备注:

  • 如果在str1中未找到str2，返回0。
  • 任一输入参数为NULL返回NULL
  • 如果str2为空串时总是能匹配成功，因此instr(‘abc’, ‘’) 会返回1。

示例：

  1.     instr('Tech on the net', 'e') = 2
  2.     instr('Tech on the net', 'e', 1, 1) = 2
  3.     instr('Tech on the net', 'e', 1, 2) = 11
  4.     instr('Tech on the net', 'e', 1, 3) = 14
  5.     instr('Tech on the net', 'e', -1, 1) = 14
  6.     instr('Tech on the net', 'e', -3, 2) = 2
  7.     instr('Tech on the net', 'o', -1, 2) = 0

IS_ENCODING

函数声明：

  1.     boolean is_encoding(string str, string from_encoding, string to_encoding)

用途：判断输入字符串str是否可以从指定的一个字符集from_encoding转为另一个字符集to_encoding。可用于判断输入是否为”乱码”，通常的用法是将from_encoding设为”utf-8”，to_encoding设为”gbk”。

参数说明：

  • str：String类型，输入为NULL返回NULL。空字符串则可以被认为属于任何字符集。
  • from_encoding，to_encoding：String类型，源及目标字符集。输入为NULL返回NULL。

返回值：Boolean类型，如果str能够成功转换，则返回true，否则返回false

示例：

  1.     is_encoding('测试', 'utf-8', 'gbk') = true
  2.     is_encoding('测试', 'utf-8', 'gbk') = true
  3.     -- gbk字库中有这两个繁体字
  4.     is_encoding('测试', 'utf-8', 'gb2312') = false
  5.     -- gb2312库中不包括这两个字

KEYVALUE

函数声明:

  1. KEYVALUE(STRING srcStr，STRING split1，STRING split2， STRING key)
  2. KEYVALUE(STRING srcStr， STRING key) //split1 = ";"，split2 = ":"

用途: 将srcStr（源字符串）按split1分成“key-value”对，按split2将key-value对分开，返回“key”所对应的value。

参数说明：

  • srcStr 输入待拆分的字符串。
  • key：string类型。源字符串按照split1和split2拆分后，根据该key值的指定，返回其对应的value。
  • split1, split2：用来作为分隔符的字符串，按照指定的这两个分隔符拆分源字符串。如果表达式中没有指定这两项，默认split1为’;’, split2为’:’。当某个被split1拆分后的字符串中有多个split2时，返回结果未定义；

返回值:

  • String类型。
  • Split1或split2为NULL时，返回NULL。
  • srcStr,key为NULL或者没有匹配的key时，返回NULL。
  • 如果有多个key-value匹配，返回第一个匹配上的key对应的value。

示例：

  1. keyvalue('0:1;1:2', 1) = '2'
  2.  -源字符串为“0:1;1:2”，因为没有指定split1和split2，默认split1为";"，split2为“:”。经过split1拆分后，key-value对为：
  3.  0:1,1:2  
  4.  经过split2拆分后变成：  
  5.  0 1/  
  6.  1 2  
  7.  返回key为1所对应的value值，为2。
  9. keyvalue(";decreaseStore:1;xcard:1;isB2C:1;tf:21910;cart:1;shipping:2;pf:0;market:shoes;instPayAmount:0;", ";",":","tf") = "21910"  
  10.   -源字符串为“;decreaseStore:1;xcard:1;isB2C:1;tf:21910;cart:1;shipping:2;pf:0;market:shoes;instPayAmount:0;”，按照 split1“;”拆分后，得出的key-value对为：  
  11.  decreaseStore:1，xcard:1，isB2C:1，tf:21910，cart:1，shipping:2，pf:0，market:shoes，instPayAmount:0  
  12.  按照split2":"拆分后变成：  
  13.  decreaseStore 1  
  14.  xcard 1  
  15.  isB2C 1  
  16.  tf 21910  
  17.  cart 1  
  18.  shipping 2  
  19.  pf 0  
  20.  market shoes  
  21.  instPayAmount 0  
  22.  key值为“tf”,返回其对应的value:21910。  
  24. keyvalue("阿里云=飞天=2;飞天=数据平台", ";","=", "阿里云") 返回NULL，请用户避免这种用法。

LENGTH

命令格式：

  1.     bigint length(string str)

用途：返回字符串str的长度。

参数说明：

  • str：String类型，若输入为bigint，double或datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：Bigint类型。若str是NULL返回NULL。如果str非UTF-8编码格式，返回-1。

示例

  1.     length('hi! 中国') = 6

LENGTHB

函数声明：

  1.     bigint lengthb(string str)

用途：返回字符串str的以字节为单位的长度。

参数说明：

  • str：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：Bigint类型。若str是NULL返回NULL。

示例

  1.     lengthb('hi! 中国') = 10

MD5

函数声明：

  1.     string md5(string value)

用途：计算输入字符串value的md5值

参数说明：

  • value：String类型，如果输入类型是bigint，double或者datetime会隐式转换成string类型参与运算，其它类型报异常。输入为NULL，返回NULL。

返回值：String类型。

REGEXP_EXTRACT

函数声明：

  1.     string regexp_extract(string source, string pattern[, bigint occurrence])

用途：将字符串source按照pattern正则表达式的规则拆分，返回第occurrence个group的字符。

参数说明：

  • source：String类型，待搜索的字符串。
  • pattern：String类型常量，pattern为空串时抛异常，pattern中如果没有指定group，抛异常。
  • occurrence：Bigint类型常量，必须>=0，其它类型或小于0时抛异常，不指定时默认为1，表示返回第一个group。若occurrence = 0，返回满足整个pattern的子串。

返回值：String类型，任一输入为NULL返回NULL。

示例：

  1.     regexp_extract('foothebar', 'foo(.*?)(bar)', 1) = the
  2.     regexp_extract('foothebar', 'foo(.*?)(bar)', 2) = bar
  3.     regexp_extract('foothebar', 'foo(.*?)(bar)', 0) = foothebar
  4.     regext_extract('8d99d8', '8d(\d+)d8') = 99
  5.     -- 如果是在MaxCompute客户端上提交正则计算的SQL，需要使用两个""作为转移字符
  7.     regexp_extract('foothebar', 'foothebar')
  8.     -- 异常返回，pattern中没有指定group

REGEXP_INSTR

命令格式：

  1.     bigint regexp_instr(string source, string pattern[,
  2.         bigint start_position[, bigint nth_occurrence[, bigint return_option]])

用途：返回字符串source从start_position开始，和pattern第n次(nth_occurrence)匹配的子串的起始/结束位置。任一输入参数为NULL时返回NULL。

参数说明：

  • source：String类型，待搜索的字符串。
  • pattern：String类型常量，pattern为空串时抛异常。
  • start_position：Bigint类型常量，搜索的开始位置。不指定时默认值为1，其它类型或小于等于0的值会抛异常。
  • nth_occurrence：Bigint类型常量，不指定时默认值为1，表示搜索第一次出现的位置。小于等于0或者其它类型抛异常。
  • return_option：Bigint类型常量，值为0或1，其它类型或不允许的值会抛异常。0表示返回匹配的开始位置，1表示返回匹配的结束位置。

返回值：Bigint类型。视return_option指定的类型返回匹配的子串在source中的开始或结束位置。

示例：

  1.     regexp_instr("i love www.taobao.com", "o[[:alpha:]]{1}", 3, 2) = 14

REGEXP_REPLACE

函数声明：

  1.     string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])

用途：将source字符串中第occurrence次匹配pattern的子串替换成指定字符串replace_string后返回。

参数说明：

  • source：String类型，要替换的字符串。
  • pattern：String类型常量，要匹配的模式，pattern为空串时抛异常。
  • replace_string：String类型，将匹配的pattern替换成的字符串。
  • occurrence：Bigint类型常量，必须大于等于0，表示将第几次匹配替换成replace_string，为0时表示替换掉所有的匹配子串。其它类型或小于0抛异常。可缺省，默认值为0。

返回值：String类型，当引用不存在的组时，不进行替换。当输入source，pattern，occurrence参数为NULL时返回NULL，若replace_string为NULL且pattern有匹配，返回NULL，replace_string为NULL但pattern不匹配，则返回原串 。

备注：

  • 当引用不存在的组时，行为未定义。

示例：

  1.     regexp_replace("123.456.7890", "([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})",
  2.         "(\1)\2-\3", 0) = "(123)456-7890"
  3.     regexp_replace("abcd", "(.)", "\1 ", 0) = "a b c d "
  4.     regexp_replace("abcd", "(.)", "\1 ", 1) = "a bcd"
  5.     regexp_replace("abcd", "(.)", "\2", 1) = "abcd"
  6.     -- 因为pattern中只定义了一个组，引用的第二个组不存在，
  7.     -- 请避免这样使用，引用不存在的组的结果未定义。
  8.     regexp_replace("abcd", "(.*)(.)$", "\2", 0) = "d"
  9.     regexp_replace("abcd", "a", "\1", 0) = "bcd"
  10.     -- 因为在pattern中没有组的定义，所以1引用了不存在的组，
  11.     -- 请避免这样使用，引用不存在的组的结果未定义。

REGEXP_SUBSTR

函数声明：

  1.     string regexp_substr(string source, string pattern[, bigint start_position[, bigint nth_occurrence]])

用途：从start_position位置开始，source中第nth_occurrence次匹配指定模式pattern的子串。

参数说明：

  • source：String类型，搜索的字符串。
  • pattern：String类型常量，要匹配的模型，pattern为空串时抛异常。
  • start_position：Bigint常量，必须大于0。其它类型或小于等于0时抛异常，不指定时默认为1，表示从source的第一个字符开始匹配。不指定时默认为1，表示从source的第一个字符开始匹配。
  • nth_occurrence：Bigint常量，必须大于0，其它类型或小于等于0时抛异常。不指定时默认为1，表示返回第一次匹配的子串。不指定时默认为1，表示返回第一次匹配的子串。

返回值：String类型。任一输入参数为NULL返回NULL。没有匹配时返回NULL。

示例：

  1.     regexp_substr ("I love aliyun very much", "a[[:alpha:]]{5}") = "aliyun"
  2.     regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) = " have"
  3.     regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 2) = " 2"

REGEXP_COUNT

函数声明：

  1.     bigint regexp_count(string source, string pattern[, bigint start_position])

用途：计算source中从start_position开始，匹配指定模式pattern的子串的次数。

参数说明：

  • source：String类型，搜索的字符串，其它类型报异常。
  • pattern：String类型常量，要匹配的模型，pattern为空串时抛异常，其它类型报异常。
  • start_position：Bigint类型常量，必须大于0。其它类型或小于等于0时抛异常，不指定时默认为1，表示从source的第一个字符开始匹配。

返回值：Bigint类型。没有匹配时返回0。任一输入参数为NULL返回NULL。

示例：

  1.     regexp_count('abababc', 'a.c') = 1
  2.     regexp_count('abcde', '[[:alpha:]]{2}', 3) = 1

SPLIT_PART

函数声明：

  1.     string split_part(string str, string separator, bigint start[, bigint end])

用途：依照分隔符separator拆分字符串str，返回从第start部分到第end部分的子串(闭区间)。

参数说明：

  • Str：String类型，要拆分的字符串。如果是bigint，double或者datetime类型会隐式转换到string类型后参加运算，其它类型报异常。
  • Separator：String类型常量，拆分用的分隔符，可以是一个字符，也可以是一个字符串，其它类型会引发异常。
  • start：Bigint类型常量，必须大于0。非常量或其它类型抛异常。返回段的开始编号(从1开始)，如果没有指定end，则返回start指定的段。
  • end：Bigint类型常量，大于等于start，否则抛异常。返回段的截止编号，非常量或其他类型会引发异常。可省略，缺省时表示最后一部分。

返回值：String类型。若任意参数为NULL，返回NULL；若separator为空串，返回原字符串str。

备注:

  • 如果separator不存在于str中，且start指定为1，返回整个str。若输入为空串，输出为空串。
  • 如果start的值大于切分后实际的分段数，例如：字符串拆分完有6个片段，但start大于6，返回空串””。
  • 若end大于片段个数，按片段个数处理。

示例：

  1.     split_part('a,b,c,d', ',', 1) = 'a'
  2.     split_part('a,b,c,d', ',', 1, 2) = 'a,b'
  3.     split_part('a,b,c,d', ',', 10) =  ''

SUBSTR

函数声明：

  1.     string substr(string str, bigint start_position[, bigint length])

用途：返回字符串str从start_position开始往后数，长度为length的子串。

参数说明：

  • str：String类型，若输入为bigint，decimal，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。
  • start_position：Bigint类型，起始位置为1。当start_position为负数时表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1，往前数依次就是-2，-3…，其它类型抛异常。
  • length：Bigint类型，大于0，其它类型或小于等于0抛异常。子串的长度。

返回值：String类型。若任一输入为NULL，返回NULL。

备注 :

  • 当length被省略时，返回到str结尾的子串。

示例

  1.     substr("abc", 2) = "bc"
  2.     substr("abc", 2, 1) = "b"
  3.     substr("abc",-2,2")="bc"
  4.     substr("abc",-3)="abc"

TOLOWER

函数声明：

  1.     string tolower(string source)

用途：输出英文字符串source对应的小写字符串。

参数说明：

  • source：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：String类型。输入为NULL时返回NULL。

示例：

  1.     tolower("aBcd") = "abcd"
  2.     tolower("哈哈Cd") = "哈哈cd"

TOUPPER

函数声明：

  1.     string toupper(string source)

用途：输出英文字符source串对应的大写字符串。

参数说明：

  • source：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：String类型。输入为NULL时返回NULL。

示例：

  1.     toupper("aBcd") = "ABCD"
  2.     toupper("哈哈Cd") = "哈哈CD"

TO_CHAR

函数声明：

  1.     string to_char(boolean value)
  2.     string to_char(bigint value)
  3.     string to_char(double value)

用途：将Boolean类型、bigint类型或者double类型转为对应的string类型表示

参数说明：

  • value：可以接受boolean类型、bigint类型或者double类型输入，其它类型抛异常。对datetime类型的格式化输出请参考另一同名函数 TO_CHAR 。

返回值：String类型。如果输入为NULL，返回NULL。

示例：

  1.     to_char(123) = '123'
  2.     to_char(true) = 'TRUE'
  3.     to_char(1.23) = '1.23'
  4.     to_char(null) = NULL

TRIM

函数声明：

  1.     string trim(string str)

用途：将输入字符串str去除左右空格。

参数说明：

  • str：String类型，若输入为bigint，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。

返回值：String类型。输入为NULL时返回NULL。

LTRIM

函数声明：

  1.  string ltrim(string str)

用途：将输入的字符串str去除左边空格。

参数说明：

  • str：String类型，若输入为bigint，decimal，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。返回值：String类型。输入为NULL时返回NULL。

示例:

  1. select ltrim(' abc ') from dual;  
  2. 返回：  
  3. +-----+
  4. | _c0 |
  5. +-----+
  6. | abc  |
  7. +-----+

RTRIM

函数声明：

  1.  string rtrim(string str)

用途：将输入的字符串str去除右边空格。

参数说明：

  • str：String类型，若输入为bigint，decimal，double或者datetime类型会隐式转换为string后参与运算，其它类型报异常。返回值：String类型。输入为NULL时返回NULL。

示例:

  1. select rtrim('a abc ') from dual;  
  2. 返回：  
  3. +-----+
  4. | _c0 |
  5. +-----+
  6. | a abc |
  7. +-----+

REVERSE

函数申明： 

  1.  STRING REVERSE(string str)

用途：返回倒序字符串。参数说明：

  • str：String类型，若输入为bigint，double，decimal或datetime类型会隐式转换为string后参与运算，其它类型报异常。返回值：String类型。输入为NULL时返回NULL。

示例： 

  1. select reverse('abcedfg') from dual;
  3. 返回：  
  4. +-----+
  5. | _c0 |
  6. +-----+
  7. | gfdecba |
  8. +-----+

SPACE

函数声明：

  1. STRING SPACE(bigint n)

用途：空格字符串函数，返回长度为n的字符串。参数说明：

  • n: bigint类型。长度不超过2M。如果为空，则抛异常。返回值：String类型。

示例： 

  1. select length(space(10)) from dual;    ----返回10。  
  2. select space(400000000000) from dual；   ----报错，长度超过2M。

REPEAT

函数声明：

  1. STRING REPEAT(string str, bigint n)

用途：返回重复n次后的str字符串。参数说明：

  • str：String类型，若输入为bigint，double，decimal或datetime类型会隐式转换为string后参与运算，其它类型报异常。
  • n: Bigint类型。长度不超过2M。如果为空，则抛异常。返回值：String类型。

示例： 

  1.  select repeat('abc',5) from lxw_dual;  
  2.  返回：abcabcabcabcabc

ASCII

函数声明： 

  1. Bigint ASCII(string str)

用途：返回字符串str第一个字符的ascii码。参数说明：

  • str：String类型，若输入为bigint，double，decimal或datetime类型会隐式转换为string后参与运算，其它类型报异常。返回值：Bigint类型。

示例： 

  1. select ascii('abcde') from dual;  
  2. 返回值：97

日期函数

MaxCompute SQL提供了针对datetime类型的操作函数。

DATEADD

函数声明：

  1.     datetime dateadd(datetime date, bigint delta, string datepart)

用途：按照指定的单位datepart和幅度delta修改date的值。

参数说明：

  • date：Datetime类型，日期值。若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。。
  • delta：Bigint类型，修改幅度。若输入为string类型或double型会隐式转换到bigint类型后参与运算，其他类型会引发异常。若delta大于0，加；否则减。
  • datepart：String类型常量。此字段的取值遵循string与datetime类型转换的约定，即”yyyy”表示年，”mm”表示月….关于类型转换的规则请参考 String类型与Datetime类型之间的转换 。此外也支持扩展的日期格式：年-“year”，月-“month”或”mon”，日-“day”，小时-“hour”。非常量、不支持的格式会或其它类型抛异常。

返回值：Datetime类型。若任一输入参数为NULL，返回NULL。

备注:

  • 按照指定的单位增减delta时导致的对更高单位的进位或退位，年、月、时、分、秒分别按照10进制、12进制、24进制、60进制、60进制计算。当delta的单位是月时，计算规则如下：若datetime的月部分在增加delta值之后不造成day溢出，则保持day值不变，否则把day值设置为结果月份的最后一天。
  • datepart的取值遵循string与datetime类型转换的约定，即”yyyy”表示年，”mm”表示月…. datetime相关的内建函数如无特殊说明均遵守此约定。同时如果没有特殊说明，所有datetime相关的内建函数的part部分也同样支持扩展的日期格式：年-“year”，月-“month”或”mon”，日-“day”，小时-“hour”。

示例：

  1.     若trans_date = 2005-02-28 00:00:00：
  2.     dateadd(trans_date, 1, 'dd') = 2005-03-01 00:00:00
  3.         -- 加一天，结果超出当年2月份的最后一天，实际值为下个月的第一天
  4.     dateadd(trans_date, -1, 'dd') = 2005-02-27 00:00:00
  5.         -- 减一天
  6.     dateadd(trans_date, 20, 'mm') = 2006-10-28 00:00:00
  7.         -- 加20个月，月份溢出，年份加1
  9.     若trans_date = 2005-02-28 00:00:00, dateadd(transdate, 1, 'mm') = 2005-03-28 00:00:00
  10.     若trans_date = 2005-01-29 00:00:00, dateadd(transdate, 1, 'mm') = 2005-02-28 00:00:00
  11.         -- 2005年2月没有29日，日期截取至当月最后一天
  12.     若trans_date = 2005-03-30 00:00:00, dateadd(transdate, -1, 'mm') = 2005-02-28 00:00:00

此处对trans_date的数值表示仅作示例使用，在文档中有关datetime介绍会经常使用到这种简易的表达方式。在MaxCompute SQL中，datetime类型没有直接的常数表示方式，如下使用方式是错误的：

  1.     select dateadd(2005-03-30 00:00:00, -1, 'mm') from tbl1;

如果一定要描述datetime类型常量，请尝试如下方法：

  1.     select dateadd(cast("2005-03-30 00:00:00" as datetime), -1, 'mm') from tbl1;
  2.         -- 将String类型常量显式转换为Datetime类型

DATEDIFF

命令格式：

  1.     bigint datediff(datetime date1, datetime date2, string datepart)

用途：计算两个时间date1，date2在指定时间单位datepart的差值。

参数说明：

  • datet1，date2：Datetime类型，被减数和减数，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。
  • datepart：String类型常量。支持扩展的日期格式。若datepart不符合指定格式或者其它类型则会发生异常。

返回值：Bigint类型。任一输入参数是NULL，返回NULL。如果date1小于date2，返回值可以为负数。

备注 :

  • 计算时会按照datepart切掉低单位部分，然后再计算结果。

示例：

  1.     若start = 2005-12-31 23:59:59，end = 2006-01-01 00:00:00:
  2.     datediff(end, start, 'dd') = 1
  3.     datediff(end, start, 'mm') = 1
  4.     datediff(end, start, 'yyyy') = 1
  5.     datediff(end, start, 'hh') = 1
  6.     datediff(end, start, 'mi') = 1
  7.     datediff(end, start, 'ss') = 1
  9.     datediff(2013-05-31 13:00:00, 2013-05-31 12:30:00, 'ss') = 1800
  10.     datediff(2013-05-31 13:00:00, 2013-05-31 12:30:00, 'mi') = 30

DATEPART

函数声明：

  1.     bigint datepart(datetime date, string datepart)

用途：提取日期date中指定的时间单位datepart的值。

参数说明：

  • date：Datetime类型，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。
  • datepart：String类型常量。支持扩展的日期格式。若datepart不符合指定格式或者其它类型则会发生异常。

返回值：Bigint类型。若任一输入参数为NULL，返回NULL。

示例：

  1.     datepart('2013-06-08 01:10:00', 'yyyy')  =  2013
  2.     datepart('2013-06-08 01:10:00', 'mm')  =  6

DATETRUNC

函数声明：

  1.     datetime datetrunc (datetime date, string datepart)

用途：返回日期date被截取指定时间单位datepart后的日期值。

参数说明：

  • date：Datetime类型，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。
  • datepart：String类型常量。支持扩展的日期格式。若datepartt不符合指定格式或者其它类型则会发生异常。

返回值：Datetime类型。任意一个参数为NULL的时候返回NULL。

示例：

  1.     datetrunc(2011-12-07 16:28:46, 'yyyy') = 2011-01-01 00:00:00
  2.     datetrunc(2011-12-07 16:28:46, 'month') = 2011-12-01 00:00:00
  3.     datetrunc(2011-12-07 16:28:46, 'DD') = 2011-12-07 00:00:00

FROM_UNIXTIME

函数声明：

  1.     datetime from_unixtime(bigint unixtime)

用途：将数字型的unix时间日期值unixtime转为日期值。

参数说明：

  • unixtime：Bigint类型，秒数，unix格式的日期时间值，若输入为string，double类型会隐式转换为bigint后参与运算。

返回值：Datetime类型的日期值，unixtime为NULL时返回NULL。

示例：

  1.     from_unixtime(123456789) = 2009-01-20 21:06:29

GETDATE

函数声明：

  1.     datetime getdate()

用途：获取当前系统时间。使用东八区时间作为MaxCompute标准时间。

返回值：返回当前日期和时间，datetime类型。

备注：

  • 在一个MaxCompute SQL任务中(以分布式方式执行)，getdate总是返回一个固定的值。返回结果会是MaxCompute SQL执行期间的任意时间，时间精度精确到秒。

ISDATE

函数声明：

  1.     boolean isdate(string date, string format)

用途：判断一个日期字符串能否根据对应的格式串转换为一个日期值，如果转换成功返回TRUE，否则返回FALSE。

参数说明：

  • date：String格式的日期值，若输入为bigint，double或者datetime类型会隐式转换为string类型后参与运算，其它类型报异常。
  • format：String类型常量，不支持日期扩展格式。其它类型或不支持的格式会抛异常。如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。如isdate(“1234-yyyy “, “yyyy-yyyy “)，会返回TRUE。

返回值：Boolean类型，如任意参数为NULL，返回NULL。

LASTDAY

函数声明：

  1.     datetime lastday(datetime date)

用途：取date当月的最后一天，截取到天，时分秒部分为00:00:00。

参数说明：

  • date：Datetime类型，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型报异常。

返回值：Datetime类型，如输入为NULL，返回NULL

TO_DATE

函数声明：

  1.     datetime to_date(string date, string format)

用途：将一个字符串date按照format指定的格式转成日期值。

参数说明：

  • date：String类型，要转换的字符串格式的日期值，若输入为bigint，double或者datetime类型会隐式转换为String类型后参与运算，为其它类型抛异常，为空串时抛异常。
  • format：String类型常量，日期格式。非常量或其他类型会引发异常。format不支持日期扩展格式，其他字符作为无用字符在解析时忽略。format参数至少包含”yyyy”，否则引发异常，如果format中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。如to_date(“1234-2234 “, “yyyy-yyyy “)会返回1234-01-01 00:00:00。

返回值：Datetime类型。若任一输入为NULL，返回NULL值。

示例：

  1.     to_date('阿里巴巴2010-12*03', '阿里巴巴yyyy-mm*dd') = 2010-12-03 00:00:00
  2.     to_date('20080718', 'yyyymmdd') = 2008-07-18 00:00:00
  4.     to_date('2008718', 'yyyymmdd')
  5.     -- 格式不符合，引发异常
  6.     to_date('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd')
  7.     -- 格式不符合，引发异常
  8.     to_date('2010-24-01', 'yyyy')
  9.     -- 格式不符合，引发异常

TO_CHAR

函数声明：

  1.     string to_char(datetime date, string format)

用途：将日期类型date按照format指定的格式转成字符串

参数类型：

  • date：Datetime类型，要转换的日期值，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。
  • format：String类型常量。非常量或其他类型会引发异常。format中的日期格式部分会被替换成相应的数据，其它字符直接输出。

返回值：String类型。任一输入参数为NULL，返回NULL。

示例：

  3.     to_char('2010-12-03 00:00:00', '阿里金融yyyy-mm*dd') = '阿里金融2010-12*03'
  4.     to_char('2008-07-18 00:00:00', 'yyyymmdd') = '20080718' 
  5.     to_char('阿里巴巴2010-12*3', '阿里巴巴yyyy-mm*dd') -- 引发异常
  6.     to_char('2010-24-01', 'yyyy') -- 会引发异常
  7.     to_char('2008718', 'yyyymmdd') -- 会引发异常

备注:

  • 关于其他类型向string类型转换请参考 字符串函数 TO_CHAR 。

UNIX_TIMESTAMP

函数声明：

  1.     bigint unix_timestamp(datetime date)

用途：将日期date转化为整型的unix格式的日期时间值。

参数说明：

  • date：Datetime类型日期值，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。

返回值：Bigint类型，表示unix格式日期值，date为NULL时返回NULL。

WEEKDAY

函数声明：

  1.     bigint weekday (datetime date)

用途：返回date日期当前周的第几天。

参数说明：

  • date：Datetime类型，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。

返回值：Bigint类型，若输入参数为NULL，返回NULL。周一作为一周的第一天，返回值为0。其他日期依次递增，周日返回6。

WEEKOFYEAR

函数声明：

  2. bigint weekofyear(datetime date)

用途：返回日期date位于那一年的第几周。周一作为一周的第一天。需要注意的是，关于这一周算上一年， 还是下一年，主要是看这一周大多数日期（4天以上）在哪一年多。 算在前一年，就是前一年的最后一周。算在后一年就是后一年的第一周。

参数说明:

  • date：Datetime类型日期值，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。

返回值：Bigint类型。若输入为NULL，返回NULL。

示例说明： 

  1. select weekofyear(to_date("20141229", "yyyymmdd")) from dual;  
  2. 返回结果：  
  3. +------------+
  4. | _c0        |
  5. +------------+
  6. | 1          |
  7. +------------+
  8.  -虽然20141229属于2014年，但是这一周的大多数日期是在2015年，因此返回结果为1，表示是2015年的第一周。    
  10.  select weekofyear(to_date("20141231", "yyyymmdd")) from dual；--返回结果为1。  
  11.  select weekofyear(to_date("20151229", "yyyymmdd")) from dual；--返回结果为53。

最后更新：2016-11-23 17:16:04

  上一篇：go DML语句__SQL_大数据计算服务-阿里云
  下一篇：go 内建函数-下__SQL_大数据计算服务-阿里云