全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

其他函数

更新时间:2017-10-11 11:03:40

CAST

函数声明:

  1. cast(expr as <type>)

函数说明:

该函数将表达式的结果转换成目标类型,如 cast(‘1’ as bigint) 将字符串 1 转为整数类型的 1,如果转换不成功或不支持的类型转换会引发异常。

注意:

  • cast(double as bigint),将 Double 值转换成 Bigint。
  • cast(string as bigint) 在将字符串转为 Bigint 时,如果字符串中是以整型表达的数字,会直接转为 Bigint 类型。如果字符串中是以浮点数或指数形式表达的数字,则会先转为 Double 类型,再转为 Bigint 类型。
  • cast(string as datetime) 或 cast(datetime as string) 时,会采用默认的日期格式:yyyy-mm-dd hh:mi:ss。

COALESCE

函数声明:

  1. coalesce(expr1, expr2, ...)

函数说明:

该函数用于返回列表中第一个非 Null 的值,如果列表中所有的值都是 Null,则返回 Null。

参数说明:

expri 是要测试的值,所有这些值类型必须相同或为 Null,否则会引发异常。

返回值:

返回值类型和参数类型相同。

注意:

至少要有一个参数,否则引发异常。

DECODE

函数声明:

  1. decode(expression, search, result[, search, result]...[, default])

函数说明:

该函数用于实现 if-then-else 分支选择的功能。

参数说明:

  • expression:要比较的表达式。

  • search:和 expression 进行比较的搜索项。

  • result:search 和 expression 的值匹配时的返回值。

  • default:可选项,如果所有的搜索项都不匹配,则返回此 default 值,如果未指定,则返回 Null。

返回值:

  • 返回匹配的 search。

  • 如果没有匹配,返回 default。

  • 如果没有指定 default,返回 Null。

    注意:

    • 至少要指定三个参数。
    • 所有的 result 类型必须一致,或为 Null。不一致的数据类型会引发异常。所有的 search 和 expression 类型必须一致,否则报异常。
    • 如果 decode 中的 search 选项有重复时且匹配时,会返回第一个值。

示例如下:

  1. select
  2. decode(customer_id,
  3. 1, 'Taobao',
  4. 2, 'Alipay',
  5. 3, 'Aliyun',
  6. Null, 'N/A',
  7. 'Others') as result
  8. from sale_detail;

上面的 decode 函数实现了下面 if-then-else 语句中的功能:

  1. if customer_id = 1 then
  2. result := 'Taobao';
  3. elsif customer_id = 2 then
  4. result := 'Alipay';
  5. elsif customer_id = 3 then
  6. result := 'Aliyun';
  7. ...
  8. else
  9. result := 'Others';
  10. end if;

注意:

  • 通常情况下 MaxCompute SQL 在计算 Null = Null 时返回 Null,但在 decode 函数中,Null 与 Null 的值是相等的。
  • 上述示例中,当 customer_id 的值为 Null 时,decode 函数返回“N/A”。

GET_IDCARD_AGE

函数声明:

  1. get_idcard_age(idcardno)

函数说明:

该函数用于根据身份证号返回当前的年龄,当前年份减去身份证中标识的出生年份的差值。

参数说明:

idcardno:String 类型, 15 位或 18 位身份证号。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性,如果校验不通过会返回 Null。

返回值:

返回 Bigint 类型,输入为 Null,返回 Null。如果当前年份减去出生年份差值大于 100,返回 Null。

GET_IDCARD_BIRTHDAY

函数声明:

  1. get_idcard_birthday(idcardno)

函数说明:

该函数用于根据身份证号返回出生日期。

参数说明:

idcardno:String 类型,15 位或 18 位身份证号。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性,如果校验不通过,则返回 Null。

返回值:

返回 Datetime 类型,输入为 Null,返回 Null。

GET_IDCARD_SEX

函数声明:

  1. get_idcard_sex(idcardno)

函数说明:

该函数用于根据身份证号返回性别,值为 M(男)或 F(女)。

参数说明:

idcardno:String 类型,15 位或 18 位身份证号。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性,如果校验不通过,则返回 Null。

返回值:

返回 String 类型,输入为 Null,返回 Null。

GREATEST

函数声明:

  1. greatest(var1, var2, …)

函数说明:

该函数用于返回输入参数中最大的一个。

参数说明:

var1,var2 可以为 Bigint,Double,Decimal,Datetime 或 String 类型。若所有值都为 Null,则返回 Null。

返回值:

  • 输入参数中的最大值,当不存在隐式转换时返回同输入参数类型。

  • Null 为最小值。

    当输入参数类型不同时:

    • Double,Bigint,Decimal,String 之间的比较转为 Double 类型。

    • String,Datetime 的比较转为 Datetime 类型。

    • 不允许其它的隐式转换。

ORDINAL

函数声明:

  1. ordinal(bigint nth, var1, var2, …)

函数说明:

该函数用于将输入变量按从小到大排序后,返回 nth 指定位置的值。

参数说明:

  • nth:Bigint 类型,指定要返回的位置,为 Null 时,返回 Null。

  • var1,var2:类型可以为 Bigint,Double,Datetime 或 String 类型。

返回值:

  • 排在第 nth 位的值,当不存在隐式转换时返回同输入参数类型。

  • 当有类型转换时:

    • Double,Bigint,String 之间的转换返回 Double 类型。

    • String,Datetime 之间的转换返回 Datetime 类型。

    • 不允许其它的隐式转换。

  • Null 为最小。

示例如下:

  1. ordinal(3, 1, 3, 2, 5, 2, 4, 6) = 2

LEAST

函数声明:

  1. least(var1, var2, …)

函数说明:

该函数用于返回输入参数中最小的一个。

参数说明:

var1,var2 可以为 Bigint,Double,Decimal,Datetime 或 String 类型。若所有值都为 Null,则返回 Null。

返回值:

  • 输入参数中的最小值,当不存在隐式转换时返回同输入参数类型。

  • 当有类型转换时:

    • Double,Bigint,String 之间的转换返回 Double 类型。

    • String,Datetime 之间的转换返回 Datetime 类型。

    • Decimal 和 Double,Bigint,String 之间比较时转为 Decimal 类型。

    • 不允许其它的隐式类型转换。

  • Null 为最小。

MAX_PT

函数声明:

  1. max_pt(table_full_name)

函数说明:

对于分区的表,此函数返回该分区表的一级分区的最大值,按字母排序,且该分区下有对应的数据文件。

参数说明:

table_full_name:String 类型,指定表名(必须带上 project 名,例如:prj.src),您必须对此表有读权限。

返回值:

返回最大的一级分区的值。

示例如下:

假设 tbl 是分区表,该表对应的分区如下,且都有数据文件:

  1. pt =‘20120901
  2. pt =‘20120902

则以下语句中 max_pt 返回值为:‘20120902’,MaxCompute SQL 语句读出pt =‘20120902’ 分区下的数据。

  1. select * from tbl where pt=max_pt('myproject.tbl');

注意

如果只是用 alter table 的方式新加了一个分区,但是此分区中并无任何数据文件,则此分区不会做为返回值。

UUID

函数声明:

  1. string uuid()

函数说明:

该函数用于返回一个随机 ID,示例样式为:29347a88-1e57-41ae-bb68-a9edbdd94212

SAMPLE

函数声明:

  1. boolean sample(x, y, column_name)

函数说明:

对所有读入的 column_name 的值,sample 根据 x,y 的设置做采样,并过滤掉不满足采样条件的行。

参数说明:

  • x,y:Bigint 类型,表示哈希为 x 份,取第 y 份。

    • y 可省略,省略时取第一份,如果省略参数中的 y,则必须同时省略 column_name。

    • x,y 为整型常量,大于 0,其它类型或小于等于 0 时抛异常,若 y&gt,x 也抛异常。x,y 任一输入为 Null 时,返回 Null。

  • column_name:是采样的目标列。

    • column_name 可以省略,省略时根据 x,y 的值随机采样。

    • 任意类型,列的值可以为 Null,不做隐式类型转换。

    • 如果 column_name 为常量 Null,则报异常。

返回值:

返回 Boolean 类型。

注意:

为避免 Null 值带来的数据倾斜,对于 column_name 中为 Null 的值,会在 x 份中进行均匀哈希。如果不加 column_name,则数据量比较少时输出不一定均匀,在这种情况下建议加上 column_name,以获得比较好的输出结果。

示例如下:

假定存在表 tbla,表内有列名为 cola 的列。

  1. select * from tbla where sample (4, 1 , cola) = true;
  2. -- 表示数值会根据cola hash4份,取第1
  3. select * from tbla where sample (4, 2) = true;
  4. -- 表示数值会对每行数据做随机哈希分配为4份,取第2

CASE WHEN 表达式

MaxCompute 提供两种 case when 的语法格式,如下所示:

  1. case value
  2. when (_condition1) then result1
  3. when (_condition2) then result2
  4. ...
  5. else resultn
  6. end
  7. case
  8. when (_condition1) then result1
  9. when (_condition2) then result2
  10. when (_condition3) then result3
  11. ...
  12. else resultn
  13. end

case when 表达式可以根据表达式 value 的计算结果灵活返回不同的值。

根据 shop_name 的不同情况得出所属区域,示例如下:

  1. select
  2. case
  3. when shop_name is null then 'default_region'
  4. when shop_name like 'hang%' then 'zj_region'
  5. end as region
  6. from sale_detail;

注意:

  • 如果 result 类型只有 Bigint,Double,统一转为 Double 后,再返回。
  • 如果 result 类型中有 String 类型,统一转为 String 后,再返回。如果不能转则报错(如 Boolean 型)。
  • 除此之外不允许其它类型之间的转换。

IF

函数声明:

  1. if(testCondition, valueTrue, valueFalseOrNull)

函数说明:

判断 testCondition 是否为真。如果为真,返回 valueTrue,如果不满足则返回另一个值(valueFalse 或者 Null)。

参数说明:

  • testCondition:要判断的表达式,Boolean 类型。

  • valueTrue:表达式 testCondition 为 True 时,返回的值。

  • valueFalseOrNull:不满足表达式 testCondition 时,返回的值,可以设为 Null。

返回值:

返回值类型和参数 valueTrue 或者 valueFalseOrNul 的类型一致。

示例如下:

  1. select if(1=2,100,200) from dual;
  2. 返回值:
  3. +------------+
  4. | _c0 |
  5. +------------+
  6. | 200 |
  7. +------------+

MaxCompute2.0 扩展支持的其他函数类型

SPLIT

函数声明:

  1. split(str, pat)

函数说明:

使用 pat 分隔 str。

参数说明:

  • str:String 类型,指被分隔的字符串。

  • pat:String 类型,分隔符,支持正则。

返回值:

array <string >,元素是 str 被 pat 分隔后的结果。

示例如下:

  1. select split("a,b,c",",") from dual;
  2. 结果如下:
  3. +------+
  4. | _c0 |
  5. +------+
  6. | [a, b, c] |
  7. +------+

EXPLODE

函数声明:

  1. explode (var)

函数说明:

该函数用于将一行数据转为多行的 UDTF。

  • 如果 var 是 array,则将列中存储的 array 转为多行。

  • 如果 var 是 map,则将列中存储的 map 的每个 key-value 转换为包含两列的行,其中一列存储 key,令一列存储 value。

参数说明:

var:array<T> 类型或者 map<K,V> 类型。

返回值:

返回转换后的行。

注意:

UDTF 在使用上有以下限制:

  • 在一个 select 中只能有一个 UDTF,不可出现其它的列。
  • 不可以与 group by/cluster by/distribute by/sort by 一起使用。

示例如下:

  1. explode(array(null, 'a', 'b', 'c')) col

MAP

函数声明:

  1. MAP map(K key1, V value1, K key2, V value2, ...)

函数说明:

该函数用于使用给定的 key-value 对建立 map。

参数说明:

  • key/value

    • 所有 key 类型一致,必须是基本类型。

    • 所有 value 类型一致,可为任意类型。

返回值:

返回map<K:V>类型。

示例如下:

  1. select map('a',123,'b',456) from dual;

结果如下

  1. {a:123, b:456}

MAP_KEYS

函数声明:

  1. ARRAY map_keys(map<K, V> )

函数说明:

该函数用于将参数 map 中的所有 key 作为数组返回。

参数说明:

map<K,V>:map 类型的数据。

返回值:

返回array<K>类型,输入 Null,则返回 Null。

示例如下:

  1. select map_keys(map('a',123,'b',456)) from dual;
  2. 结果如下:
  3. [a, b]

MAP_VALUES

函数声明:

  1. ARRAY map_values(map<K, V>)

函数说明:

该函数用于将参数 map 中的所有 values 作为数组返回。

参数说明:

map<K,V>:map 类型的数据。

返回值:

返回array<V>类型,输入 Null,返回 Null。

示例如下:

  1. select map_keys(map('a',123,'b',456)) from dual;
  2. 结果如下:
  3. [123, 456]

ARRAY

函数声明:

  1. ARRAY array(value1,value2, ...)

函数说明:

该函数用于使用给定的 value 构造 array。

参数说明:

value:value 可为任意类型,但是所有 value 的类型必须一致。

返回值:

返回 array 类型。

示例如下:

  1. select array(123,456,789) from dual;

结果如下:

  1. [123, 456, 789]

SIZE

函数声明:

  1. INT size(map<K, V>)
  2. INT size(array<T>)

函数说明:

  • size(map<K,V>)返回给定 map 中 K/V 对数。

  • size(array<T>)返回给定的 array 中的元素数目。

参数说明:

  • map<K, V>:map 类型的数据。

  • array<T>:array 类型的数据。

返回值:

返回 int 类型。

示例如下:

  1. select size(map('a',123,'b',456)) from dual;--返回2
  2. select size(map('a',123,'b',456,'c',789)) from dual;--返回3
  3. select size(array('a','b')) from dual;--返回2
  4. select size(array(123,456,789)) from dual;--返回3

ARRAY_CONTAINS

函数声明:

  1. boolean array_contains(ARRAY<T> a,value v)

函数说明:

该函数用于检测给定 array a 中是否包含 v。

参数说明:

  • a:array 类型的数据。

  • v:给出的 v 必须与 array 数组中的数据类型一致。

返回值:

返回 Boolean 类型。

示例如下:

  1. select array_contains(array('a','b'), 'a') from dual; --返回true
  2. select array_contains(array(456,789),123) from dual; -- 返回false

SORT_ARRAY

函数声明:

  1. ARRAY sort_array(ARRAY<T>)

函数说明:

该函数用于为给定的数组排序。

参数说明:

ARRAY<T>:array 类型的数据,数组中的数据可为任意类型。

返回值:

返回 array 类型。

示例如下:

  1. select sort_array(array('a','c','f','b')),sort_array(array(4,5,7,2,5,8)),sort_array(array('你','我','他')) from dual;
  2. 结果如下:
  3. [a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]

POSEXPLODE

函数声明:

  1. posexplode(ARRAY<T>)

函数说明:

该函数用于将给定的 array 展开,每个 value 一行,每行两列分别对应数组从 0 开始的下标和数组元素。

参数说明:

ARRAY<T>:array 类型的数据,数组中的数据可为任意类型。

返回值:

返回表生成的函数。

示例如下:

  1. select posexplode(array('a','c','f','b')) from dual;
  2. 结果如下:
  3. +------------+-----+
  4. | pos | val |
  5. +------------+-----+
  6. | 0 | a |
  7. | 1 | c |
  8. | 2 | f |
  9. | 3 | b |
  10. +------------+-----+

STRUCT

函数声明:

  1. STRUCT struct(value1,value2, ...)

函数说明:

该函数用于使用给定 value 列表建立 struct。

参数说明:

value:各 value 可为任意类型。

返回值:

返回STRUCT<col1:T1, col2:T2, ...>类型。field 的名称依次为 col1,col2,…。

示例如下:

  1. select struct('a',123,'ture',56.90) from dual;
  2. 结果如下:
  3. {col1:a, col2:123, col3:ture, col4:56.9}

NAMED_STRUCT

函数声明:

  1. STRUCT named_struct(string name1, T1 value1, string name2, T2 value2, ...)

函数说明:

该函数用于使用给定的 name/value 列表建立 struct。

参数说明:

  • value:各 value 可为任意类型。

  • name:指定的 String 类型的 field 名称。

返回值:

返回STRUCT<name1:T1, name2:T2, ...>类型,生成 struct 的 field 的名称依次为 name1,name2,…。

示例如下:

  1. select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50) from dual;
  2. 结果如下:
  3. {user_id:10001, user_name:LiLei, married:F, weight:63.5}

INLINE

函数声明:

  1. inline(array<struct<f1:T1, f2:T2, ...>>)

函数说明:

该函数用于将给定的 struct 数组展开,每个元素对应一行,每行每个 struct 元素对应一列。

参数说明:

STRUCT<f1:T1, f2:T2, ...>:数组中的 value 可为任意类型。

返回值:

返回表生成的函数。

示例如下:

  1. select inline(array(named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50))) from dual;
  2. 结果如下:
  3. +------------+-----------+---------+------------+
  4. | user_id | user_name | married | weight |
  5. +------------+-----------+---------+------------+
  6. | 10001 | LiLei | F | 63.5 |
  7. +------------+-----------+---------+------------+

BETWEEN AND 表达式

函数声明:

  1. A [NOT] BETWEEN B AND C

返回值:

  • 如果 a、b 或 c 为空,则为空。

  • 如果 a 大于或等于 b 且小于或等于 C,则为 true,否则为 false。

示例如下:

假设表 emp 中的数据如下所示:

  1. | empno | ename | job | mgr | hiredate| sal| comm | deptno |
  2. 7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
  3. 7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
  4. 7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
  5. 7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
  6. 7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
  7. 7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
  8. 7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
  9. 7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
  10. 7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
  11. 7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
  12. 7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
  13. 7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
  14. 7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
  15. 7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
  16. 7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
  17. 7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
  18. 7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

查询 sal 在大于等于 1000 小于等于 1500 之间的数据:

  1. select * from emp where sal BETWEEN 1000 and 1500;
  2. 结果如下:
  3. +-------+-------+-----+------------+------------+------------+------------+------------+
  4. | empno | ename | job | mgr | hiredate | sal | comm | deptno |
  5. +-------+-------+-----+------------+------------+------------+------------+------------+
  6. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
  7. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
  8. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
  9. | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.0 | Null | 20 |
  10. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.0 | Null | 10 |
  11. | 7956 | TEBAGE | CLERK | 7748 | 1982-12-30 00:00:00 | 1300.0 | Null | 10 |
  12. +-------+-------+-----+------------+------------+------------+------------+------------+
本文导读目录