全部产品
MaxCompute

其他函数

更新时间:2017-09-15 14:22:35   分享:   

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。

返回值:

  • 输入参数中的最小值,当不存在隐式转换时返回同输入参数类型。
  • NULL为最小。
  • 有类型转换时,double, bigint, string之间的转换返回double。string, datetime之间的转换返回datetime; decimal和double,bigint,string之间比较时转为decimal。不允许其它的隐式类型转换。

MAX_PT

函数声明:

  1. max_pt(table_full_name)

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

参数说明:

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

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

示例:

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

pt=’20120901’

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>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时2,返回的值,可以设为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 , 元素是str被pat分隔后的结果.

示例:

  1. select split("a,b,c",",") from dual;

返回结果

  1. +------+
  2. | _c0 |
  3. +------+
  4. | [a, b, c] |
  5. +------+

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 类型。

示例如下:

  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:map类型数据。

返回值:ARRAY类型,输入NULL,返回NULL。

示例如下:

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

结果如下

  1. [a, b]

MAP_VALUES

函数声明:

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

用途:将参数map中的所有values作为数组返回。

参数说明:

  • map:map类型数据。

返回值:ARRAY类型,输入NULL,返回NULL。

示例如下:

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

结果如下

  1. [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)返回给定MAP中K/V对数;size(array)返回给定的array中的元素数目。

参数说明:

  • map:map类型数据;
  • array:array类型数据。

返回值:int类型。

示例如下:

  1. select map_values(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: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;

返回结果:

  1. [a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]

POSEXPLODE

函数声明:

  1. posexplode(ARRAY<T>)

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

参数说明:

  • ARRAY:array类型数据,数组中的数据可为任意类型;

返回值:表生成函数。

示例如下:

  1. select select posexplode(array('a','c','f','b')) from dual;

返回结果:

  1. +------------+-----+
  2. | pos | val |
  3. +------------+-----+
  4. | 0 | a |
  5. | 1 | c |
  6. | 2 | f |
  7. | 3 | b |
  8. +------------+-----+

STRUCT

函数声明:

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

用途:使用给定value列表建立struct。

参数说明:

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

返回值:STRUCT类型,field的名称依次为col1, col2, …。

示例如下:

  1. select struct('a',123,'ture',56.90) from dual;

返回结果:

  1. {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类型,生成struct的field的名称依次为name1, name2, …。

示例如下:

  1. select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50) from dual;

返回结果:

  1. {user_id:10001, user_name:LiLei, married:F, weight:63.5}

INLINE

函数声明:

  1. inline(ARRAY<STRUCT<f1:T1, f2:T2, ...>>)

用途:将给定struct数组展开,每个元素对应一行,每行每个struct元素对应一列。

参数说明:

  • STRUCT:数组中的value可为任意类型。

返回值:表生成函数。

示例如下:

  1. select select inline(array(named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50))) from dual;

返回结果:

  1. +------------+-----------+---------+------------+
  2. | user_id | user_name | married | weight |
  3. +------------+-----------+---------+------------+
  4. | 10001 | LiLei | F | 63.5 |
  5. +------------+-----------+---------+------------+

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;

结果如下

  1. +-------+-------+-----+------------+------------+------------+------------+------------+
  2. | empno | ename | job | mgr | hiredate | sal | comm | deptno |
  3. +-------+-------+-----+------------+------------+------------+------------+------------+
  4. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.0 | 500.0 | 30 |
  5. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.0 | 1400.0 | 30 |
  6. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.0 | 0.0 | 30 |
  7. | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.0 | NULL | 20 |
  8. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.0 | NULL | 10 |
  9. | 7956 | TEBAGE | CLERK | 7748 | 1982-12-30 00:00:00 | 1300.0 | NULL | 10 |
  10. +-------+-------+-----+------------+------------+------------+------------+------------+
本文导读目录
本文导读目录
以上内容是否对您有帮助?