MaxCompute 其他函数

array array(<value>,<value>, ...)

使用指定的值构造ARRAY。

value：必填。可以为任意类型。所有参数的类型必须一致。

返回ARRAY类型。

boolean array_contains(array <a>, value <v>)

检测指定ARRAY类型a中是否包含v。

• a：必填。ARRAY类型。待检测的ARRAY数组名称。
• v：必填。必须与ARRAY数组中的数据类型一致。待检测的值。

array array_intersect(<array1>, <array2>)  

计算array1和array2的交集。

array1、array2：必填。ARRAY类型。两个待比较的ARRAY。

返回ARRAY类型。

--返回[20,30,null]。
select array_intersect(array(10, 20, 20, 30, 30, null, null), array(30, 30, 20, 20, 40, null, null)); 

根据value或_condition的计算结果，灵活地返回不同的result值。

• value：必填。比较的值。
• _condition：必填。指定条件。
• result：必填。返回值。

cast(<expr> as <type>)

• expr：必填。表达式，待转换数据来源。
• type：必填。目标数据类型。

返回值为转换后的目标数据类型。

• 示例1：常见用法。命令示例如下：

  --返回1。
  select cast('1' as bigint);

• 示例2：异常用法，如果转换不成功或遇到不支持的类型转换，则会返回NULL。错误命令示例如下：

  --返回NULL。
  select cast('abc' as bigint);

coalesce(<expr1>, <expr2>, ...)

返回<expr1>, <expr2>, ...中第一个非NULL的值，如果列表中所有的值都是NULL，则返回NULL。

expr：必填。待验证的值。所有值类型必须相同或为NULL，不一致的数据类型会返回报错。至少要有一个参数非NULL，否则会返回报错。

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

• 示例1：常见用法。命令示例如下：

  --返回1。
  select coalesce(null,null,1,null,3,5,7);

• 示例2：参数值类型不一致，返回报错。错误命令示例如下：

  --返回报错，无法识别abc。
  select coalesce(null,null,1,null,abc,5,7);

• 示例3：参数值无非NULL值，返回报错。错误命令示例如下：

  --返回报错，至少有一个参数值非NULL。
  select coalesce(null,null,null,null);

decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])

实现if-then-else分支选择的功能。

• expression：必填。要比较的表达式。
• search：必填。与expression进行比较的搜索项。
• result：必填。search和expression的值匹配时的返回值。
• default：可选。如果所有的搜索项都不匹配，则返回default值，如果未指定，则返回NULL。

• 如果匹配，返回result。
• 如果没有匹配，返回default。
• 如果没有指定default，返回NULL。
• 如果search选项有重复且匹配时，会返回第一个值。
• 通常，MaxCompute SQL在计算NULL=NULL时返回NULL，但在该函数中，NULL与NULL的值是相等的。

• 在一个select中只能出现一个explode函数，不可以出现表的其他列。
• 不可以与group by、cluster by、distribute by、sort by一起使用。

explode (<var>)

var：必填。array<T>类型或map<K, V>类型。

返回转换后的行。

select explode(array(null, 'a', 'b', 'c')) col;
--返回结果如下。
+------------+
| col        |
+------------+
| NULL       |
| a          |
| b          |
| c          |
+------------+

get_idcard_age(<idcardno>)

根据身份证号码返回当前的年龄，即当前年份减去身份证号码中标识的出生年份的差值。

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

返回BIGINT类型。输入为NULL时，返回NULL。

get_idcard_birthday(<idcardno>)

根据身份证号码返回出生日期。

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

返回DATETIME类型。输入为NULL时，返回NULL。

get_idcard_sex(<idcardno>)

根据身份证号码返回性别，值为M（男）或F（女）。

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

返回STRING类型。输入为NULL时，返回NULL。

get_user_id()

获取当前账号的账号ID，即用户ID或UID。

不需要输入参数，输入会报错。

返回当前账号的账号ID。

select get_user_id();
--返回结果如下。
+------------+
| _c0        |
+------------+
| 1117xxxxxxxx8519 |
+------------+

greatest(<var1>, <var2>, …)

返回输入参数中最大的值。

var：必填。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING类型。如果所有的参数值都为NULL，则返回NULL。

• 返回输入参数中的最大值。当不存在隐式转换时，返回值同输入参数类型。
• NULL为最小值。
• 当输入参数类型不同时，DOUBLE、BIGINT、DECIMAL、STRING之间的比较会转换为DOUBLE类型；STRING、DATETIME的比较会转换为DATETIME类型。不允许其他的隐式转换。
• 当set odps.sql.hive.compatible=true;时，任意参数输入为NULL，都会返回NULL。

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

判断testCondition是否为真。如果为真，返回valueTrue的值，否则返回valueFalseOrNull的值。

• testCondition：必填。要判断的表达式，BOOLEAN类型。
• valueTrue：必填。表达式testCondition为True时，返回的值。
• valueFalseOrNull：表达式testCondition为False时，返回的值，可以设为NULL。

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

--返回200。
select if(1=2,100,200); 

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

将给定的STRUCT数组展开。每个数组元素对应一行，每行每个STRUCT元素对应一列。

f1:T1、f2:T2：必填。可以为任意类型。

返回STRUCT数组展开的数据。

least(<var1>, <var2>, …)

返回输入参数中最小的值。

var ：必填。输入参数值。BIGINT、DOUBLE、DECIMAL、DATETIME或STRING类型。如果所有参数值都为NULL，则返回NULL。

• 输入参数中的最小值。当不存在隐式转换时，返回值同输入参数类型。
• 当有类型转换时，DOUBLE、BIGINT、STRING之间的转换返回DOUBLE类型；STRING、DATETIME之间的转换返回DATETIME类型；DECIMAL、DOUBLE、BIGINT和STRING之间的转换返回DECIMAL类型。不允许其他的隐式类型转换。
• NULL为最小值。

map map(K <key1>, V <value1>, K <key2>, V <value2>, ...)

使用给定的Key-Value对建立MAP。

• key：必填。所有key类型一致（包括隐式转换后类型一致），必须是基本类型。
• value：必填。所有value类型一致（包括隐式转换后类型一致），可为任意类型。

返回MAP类型。

array map_keys(map<K, V>)

将MAP中的所有Key作为数组返回。

参数为MAP类型的数据。

返回ARRAY类型。输入NULL时，则返回NULL。

array map_values(map<K, V>)

将MAP中的所有Values作为数组返回。

参数为MAP类型的数据。

返回ARRAY类型。输入NULL时，返回NULL。

select map_values(map('a',123,'b',456));
--返回结果如下。
[123, 456]

max_pt(<table_full_name>)

返回分区表的一级分区中有数据的分区的最大值，按字母排序，且读取该分区下对应的数据。

max_pt函数也可以使用标准SQL实现，select * from table where pt = max_pt("table");可以改写为select * from table where pt = (select max(pt) from table);。

table_full_name：必填。STRING类型。指定表名。必须对表有读权限。

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

• 示例1：例如tbl是分区表，该表对应的分区为20120901和20120902，且都有数据。则以下语句中max_pt返回值为‘20120902’。MaxCompute SQL语句会读出pt=‘20120902’分区下的数据。命令示例如下。

  select * from tbl where pt=max_pt('myproject.tbl');
  --等效于如下语句。
  select * from tbl where pt = (select max(pt) from myproject.tbl);

• 示例2：在多级分区场景，使用标准SQL实现获取最大分区下的数据。命令示例如下。

  select * from table where pt1 = (select max(pt1) from table) and pt2 = (select max(pt2) from table where pt1 = (select max(pt1) from table));

struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>, ...)

使用指定的name、value列表建立STRUCT。

• value：必填。可以为任意类型。
• name：必填。指定STRING类型的Field名称。此参数为常量。

返回STRUCT类型。Field的名称依次为name1，name2，…。

select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50);
--返回结果如下。
+------------+
| _c0        |
+------------+
| {"user_id":10001,"user_name":"LiLei","married":"F","weight":63.5} |
+------------+

nvl(T <value>, T <default_value>)

如果value值为NULL，返回default_value，否则返回value。两个参数数据类型需要一致。

ordinal(bigint <nth>, <var1>, <var2>, …)

将输入变量按从小到大排序后，返回nth指定位置的值。

• nth：必填。BIGINT类型。指定要返回的位置值为NULL时，返回NULL。
• var：必填。BIGINT、DOUBLE、DATETIME或STRING类型。

• 排在第nth位的值，当不存在隐式转换时返回值同输入参数类型。
• 当有类型转换时，DOUBLE、BIGINT、STRING之间的转换返回DOUBLE类型；STRING、DATETIME之间的转换返回DATETIME类型。不允许其他的隐式转换。
• NULL为最小值。

--返回2。
select ordinal(3, 1, 3, 2, 5, 2, 4, 6); 

boolean partition_exists(string <table_name>， string... <partitions>)

查询指定的分区是否存在。

• table_name：必填。表名称，STRING类型。表名称中可以指定项目空间名称，例如my_proj.my_table。如果不指定项目空间名称则默认为当前项目空间。
• partitions : 必填。分区名称，STRING类型。按照表分区列的顺序依次写出分区值，分区值数目必须与分区列数目一致。

返回BOOLEAN类型。如果指定的分区存在返回True，否则返回False。

--创建分区表foo。
create table foo (id bigint) partitioned by (ds string, hr string);
--为分区表foo新增分区。
alter table foo add partition (ds='20190101', hr='1');
--查询分区ds='20190101'和hr='1'是否存在。返回结果为True。
select partition_exists('foo', '20190101', '1');

posexplode(array<T>)

将指定的ARRAY展开，每个Value一行，每行两列分别对应数组从0开始的下标和数组元素。

array<T>：必填。ARRAY类型的数据。可以为任意类型。

返回生成的表。

select posexplode(array('a','c','f','b'));
--返回结果如下。
+------------+------------+
| pos        | val        |
+------------+------------+
| 0          | a          |
| 1          | c          |
| 2          | f          |
| 3          | b          |
+------------+------------+

boolean sample(<x>, <y>, [<column_name1>, <column_name2>,...])

基于所有读入的column_name的值，系统根据x、y的设置做采样，并过滤掉不满足采样条件的行。

• x、y：x必填。BIGINT类型，取值范围为大0的整型常量。表示哈希为x份，取第y份。

  y可选，省略时默认取第一份。如果省略参数中的y，则必须同时省略column_name。

  x、y为其它类型或小于等于0时抛异常，如果y大于x时也返回异常。x、y任一输入为NULL时，返回NULL。

• column_name：可选。采样的目标列。该参数省略时将根据x、y的值随机采样。任意类型，列的值可以为NULL。不做隐式类型转换。如果column_name为常量NULL，则返回报错。
  说明 为避免NULL值带来的数据倾斜，对于
  column_name中为NULL的值，会在
  x份中进行均匀哈希。如果不指定
  column_name，则数据量比较少时输出不一定均匀，在这种情况下建议指定
  column_name，以获得较好的输出结果。

返回BOOLEAN类型。

例如存在表tbla，表内有列名为cola的列。

--表示数值会根据cola hash为4份，取第1份。返回值为True。
select * from tbla where sample (4, 1 , cola);
--表示数值会对每行数据做随机哈希分配为4份，取第2份。返回值为True。
select * from tbla where sample (4, 2);

int size(<map>)
int size(<array>)

size(<map>)返回指定map中的K/V对数。size(<array>)返回指定array中的元素数目。

• map：必填。MAP类型的数据。
• array：必填。ARRAY类型的数据。

--返回2。
select size(map('a',123,'b',456)); 
--返回3。
select size(map('a',123,'b',456,'c',789)); 
--返回2。
select size(array('a','b'));
--返回3。 
select size(array(123,456,789)); 

array sort_array(array<T>[, isasc])

为指定的数组排序。

• array<T>：必填。ARRAY类型的数据。数组中的数据可为任意类型。
  升级到MaxCompute 2.0后，产品扩展了部分函数。如果您用到的函数涉及新数据类型（TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP或BINARY），在使用扩展函数时，需要执行如下语句开启新数据类型开关：

  • Session级别：如果使用新数据类型，您需要在SQL语句前加上语句set odps.sql.type.system.odps2=true;，并与SQL语句一起提交执行。
  • Project级别：Project Owner可根据需要对Project进行设置，等待10~15分钟后才会生效。命令如下。

    setproject odps.sql.type.system.odps2=true;

    对setproject的详细说明请参见项目空间操作。关于开启Project级别数据类型的注意事项，请参见数据类型版本说明。

• isasc：可选。用于设置排序顺序。取值为True（升序）或False（降序）。默认为升序。

返回经过排序的ARRAY。

• 示例1：例如表t_array的字段为c1 array<string>,c2 array<int> ,c3 array<string>，包含数据如下：

  +------------+---------+--------------+
  | c1         | c2      | c3           |
  +------------+---------+--------------+
  | [a, c, f, b]  | [4, 5, 7, 2, 5, 8]  |  [你, 我, 他] |
  +------------+---------+--------------+

  对表的每列数据进行排序。命令示例如下：

  --返回[a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]。
  select sort_array(c1),sort_array(c2),sort_array(c3) from t_array;

• 示例2：对ARRAY类型数据进行排序。命令示例如下：

  --返回[null,10,20,30,30,40,50]。
  select sort_array(array(10, 20, 40, 30, 30, null, 50));
  --返回[null,10,20,30,30,40,50]。
  select sort_array(array(10, 20, 40, 30, 30, null, 50),true);
  --返回[50,40,30,30,20,10,null]。
  select sort_array(array(10, 20, 40, 30, 30, null, 50),false);

split(<str>, <pat>)

通过pat将str分割后返回数组。

• str：必填。STRING类型，指被分割的字符串。
• pat：必填。STRING类型的分隔符，支持正则表达式。

返回array <string>。

--返回["a","b","c"]。
select split("a,b,c",",");

stack(n, expr1, ..., exprk) 

将expr1, ..., exprk分割为n行，除非另有说明，否则输出结果使用默认的列名col0、col1...。

• n：必填。分割的行数。
• expr：必填。待分割的参数。expr1, ..., exprk必须是整型，且参数数目必须是n的整数倍，需要能分割为完整的n行，否则报错。

返回n行，列数为参数数量除以n的商的数据集。

--将1, 2, 3, 4, 5, 6排为3行。
select stack(3, 1, 2, 3, 4, 5, 6);
--返回结果如下。
+------+------+
| col0 | col1 |
+------+------+
| 1    | 2    |
| 3    | 4    |
| 5    | 6    |
+------+------+

--将'A',10,date '2015-01-01','B',20,date '2016-01-01'排为两行.
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
--返回结果如下。
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

--将a、b、c、d排列为两行，源表如果有多行，按行执行stack的分行处理。
select stack(2,a,b,c,d) as (col,value)
from values 
    (1,1,2,3,4),
    (2,5,6,7,8),
    (3,9,10,11,12),
    (4,13,14,15,null)
as t(key,a,b,c,d);
--返回结果如下。
+------+-------+
| col  | value |
+------+-------+
| 1    | 2     |
| 3    | 4     |
| 5    | 6     |
| 7    | 8     |
| 9    | 10    |
| 11   | 12    |
| 13   | 14    |
| 15   | NULL  |
+------+-------+

--配合lateral view使用。
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2;
--返回结果如下。
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

str_to_map(<text> [, <delimiter1> [, <delimiter2>]])

使用delimiter1将text分割成Key-Value对，然后使用delimiter2分割每个Key-Value对的Key和Value。

• text：必填。STRING类型，指被分割的字符串。
• delimiter1：可选。STRING类型，分隔符，不指定时默认为英文逗号（,）。
• delimiter2：可选。STRING类型，分隔符，不指定时默认为等于号（=）。
  说明 当分隔符是正则表达式或特殊字符时，要在前面加两个反斜线（\\）来做转义。特殊字符包括冒号（:）、英文句点（.）、问号（?）、加号（+）或星号（*）。

返回值类型为map<string, string>。返回值是text被delimiter1和delimiter2分割后的结果。

--返回{"test2":"2","test1":"1"}。
select str_to_map('test1&1-test2&2','-','&');
--返回{"test2":"2","test1":"1"}。
select str_to_map("test1.1,test2.2", ",", "\\.");

struct struct(<value1>,<value2>, ...)

使用指定value列表建立STRUCT。

value：必填。可以为任意类型。

返回STRUCT类型。Field的名称依次为col1，col2，…。

select struct('a',123,'ture',56.90);
--返回结果如下。
+------------+
| _c0        |
+------------+
| {"col1":"a","col2":123,"col3":"ture","col4":56.9} |
+------------+

boolean table_exists(string <table_name>)

查询指定的表是否存在。

table_name：必填。表名称。STRING类型。表名称中可以指定项目空间名称（例如my_proj.my_table）。如果不指定项目空间名称则默认为当前项目空间。

返回BOOLEAN类型。如果指定的表存在返回True，否则返回False。

--在select列表中使用。
select if(table_exists('abd'), col1, col2) from src;

• 所有作为key的列必须位于在前面，而要转置的列必须放在后面。
• 在一个select中只能有一个UDTF，不可以再出现其他的列。
• 不可以与group by、cluster by、distribute by、sort by一起使用。

trans_array (<num_keys>, <separator>, <key1>,<key2>,…,<col1>,<col2>,<col3>) as (<key1>,<key2>,…,<col1>, <col2>)

将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。

• num_keys：必填。 BIGINT类型常量，值必须>=0。在转为多行时作为转置key的列的个数。
• separator：必填。STRING类型常量，用于将字符串拆分成多个元素的分隔符。为空时返回报错。
• keys：必填。转置时作为key的列， 个数由num_keys指定。如果num_keys指定所有的列都作为key（即num_keys等于所有列的个数），则只返回一行。
• cols： 必填。要转为行的数组，keys之后的所有列视为要转置的数组，必须为STRING类型，存储的内容是字符串格式的数组，例如Hangzhou;Beijing;shanghai，是以分号（;）分隔的数组。

返回转置后的行，新的列名由as指定。作为key的列类型保持不变，其余所有的列是STRING类型。拆分成的行数以个数多的数组为准，不足的补NULL。

• 所有作为key的列必须处在前面，而要转置的列必须放在后面。
• 在一个select中只能有一个UDTF，不可以再出现其他的列。

trans_cols (<num_keys>, <key1>,<key2>,…,<col1>, <col2>,<col3>) as (<idx>, <key1>,<key2>,…,<col1>, <col2>)

将一行数据转为多行数据的UDTF，将不同的列拆分为不同的行。

• num_keys：必填。BIGINT类型常量，值必须>=0。在转为多行时作为转置key的列的个数。
• keys：必填。转置时作为key的列， 个数由num_keys指定。如果num_keys指定所有的列都作为key（即num_keys等于所有列的个数），则只返回一行。
• idx：必填。转换后的行号。
• cols：必填。 要转为行的列。

返回转置后的行，新的列名由as指定。输出的第一列是转置的下标，下标从1开始。作为key的列类型保持不变，其余所有的列与原来的数据类型一致。

string uuid()

返回一个随机ID，格式为29347a88-1e57-41ae-bb68-a9edbdd9****。