MaxCompute SQL提供了开发过程中常见的其他函数,您可以根据实际需要选择合适的函数。本文为您提供MaxCompute SQL支持的CAST、DECODE、LEAST、ARRAY、SPLIT、MAP等函数的命令格式、参数说明及示例。

函数 功能
ARRAY 使用给定的值构造ARRAY。
ARRAY_CONTAINS 检测指定的ARRAY中是否包含指定的值。
ARRAY_INTERSECT 计算两个ARRAY的交集。
CASE WHEN表达式 根据表达式的计算结果,灵活地返回不同的值。
CAST 将表达式的结果转换成目标类型。
COALESCE 返回参数列表中第一个非NULL的值。
DECODE 实现if-then-else分支选择的功能。
EXPLODE 将一行数据转为多行的UDTF。
GET_IDCARD_AGE 根据身份证号码返回当前的年龄。
GET_IDCARD_BIRTHDAY 根据身份证号码返回出生日期。
GET_IDCARD_SEX 根据身份证号码返回性别。
GET_USER_ID 获取当前账号的账号ID。
GREATEST 返回输入参数中最大的值。
IF表达式 判断指定的条件是否为真。
INLINE 将指定的STRUCT数组展开。每个数组元素对应一行,每行每个STRUCT元素对应一列。
LEAST 返回输入参数中最小的值。
MAP 使用指定的Key-Value对建立MAP。
MAP_KEYS 将参数MAP中的所有Key作为数组返回。
MAP_VALUES 将参数MAP中的所有Values作为数组返回。
MAX_PT 返回分区表的一级分区的最大值。
NAMED_STRUCT 使用给定的Name、Value列表建立STRUCT。
NVL 指定值为NULL的参数的返回结果。
ORDINAL 将输入变量按从小到大排序后,返回指定位置的值。
PARTITION_EXISTS 查询指定的分区是否存在。
POSEXPLODE 将指定的ARRAY展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。
SAMPLE 对所有读入的列值,采样并过滤掉不满足采样条件的行。
SIZE 返回指定MAP中的K/V对数或ARRAY中的元素数目。
SORT_ARRAY 为指定的数组排序。
SPLIT 将字符串按照指定的分隔符分割后返回数组。
STACK 将指定的参数组分割为指定的行数。
STR_TO_MAP 将字符串按照指定的分隔符分割得到Key和Value。
STRUCT 使用给定Value列表建立STRUCT。
TABLE_EXISTS 查询指定的表是否存在。
TRANS_ARRAY 将一行数据转为多行的UDTF,将列中存储的以固定分隔符格式分隔的数组转为多行。
TRANS_COLS 将一行数据转为多行数据的UDTF,将不同的列拆分为不同的行。
UUID 返回一个随机ID。

ARRAY

  • 命令格式
    array array(<value>,<value>, ...)
  • 命令说明

    使用指定的值构造ARRAY。

  • 参数说明

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

    升级到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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

    返回ARRAY类型。

  • 示例
    例如表t_table的字段为c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint,包含数据如下:
    +------------+----+----+------------+------------+
    | c1         | c2 | c3 | c4         | c5         |
    +------------+----+----+------------+------------+
    | 1000       | k11 | k21 | 86         | 15         |
    | 1001       | k12 | k22 | 97         | 2          |
    | 1002       | k13 | k23 | 99         | 1          |
    +------------+----+----+------------+------------+
    命令示例如下:
    --根据c2、c4、c3、c5列的数据构造ARRAY数组。
    select array(c2,c4,c3,c5) from t_table;
    
    --返回结果如下。
    +------+
    | _c0  |
    +------+
    | [k11, 86, k21, 15] |
    | [k12, 97, k22, 2] |
    | [k13, 99, k23, 1] |
    +------+

ARRAY_CONTAINS

  • 命令格式
    boolean array_contains(array <a>, value <v>)
  • 命令说明

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

  • 参数说明
    • a:必填。ARRAY类型。待检测的ARRAY数组名称。
    • v:必填。必须与ARRAY数组中的数据类型一致。待检测的值。
  • 示例
    例如表t_table_array的字段为c1 bigint, t_array array<string>,包含数据如下:
    +------------+---------+
    | c1         | t_array |
    +------------+---------+
    | 1000       | [k11, 86, k21, 15] |
    | 1001       | [k12, 97, k22, 2] |
    | 1002       | [k13, 99, k23, 1] |
    +------------+---------+
    命令示例如下:
    --检测t_array列是否包含1。
    select c1, array_contains(t_array,'1') from t_table_array;
    --返回结果如下。
    +------------+------+
    | c1         | _c1  |
    +------------+------+
    | 1000       | false |
    | 1001       | false |
    | 1002       | true |
    +------------+------+

ARRAY_INTERSECT

  • 命令格式
    array array_intersect(<array1>, <array2>)  
  • 命令说明

    计算array1array2的交集。

  • 参数说明

    array1array2:必填。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)); 

CASE WHEN表达式

  • 命令格式
    MaxCompute提供以下两种case when格式:
    • case <value>
      when <value1> then <result1>
      when <value2> then <result2>
      ...
      else <resultn>
      end
    • case
      when (<_condition1>) then <result1>
      when (<_condition2>) then <result2>
      when (<_condition3>) then <result3>
      ...
      else <resultn>
      end
  • 命令说明

    根据value_condition的计算结果,灵活地返回不同的result值。

  • 参数说明
    • value:必填。比较的值。
    • _condition:必填。指定条件。
    • result:必填。返回值。
    说明
    • 如果result类型只有BIGINT、DOUBLE,统一转为DOUBLE后,再返回结果。
    • 如果result类型中有STRING类型,则统一转为STRING后,再返回结果。如果无法进行类型转换,例如BOOLEAN类型无法转换为STRING类型,则会返回报错。
    • 不允许其他类型之间的转换。
  • 示例
    例如表sale_detail的字段为shop_name string, customer_id string, total_price double,,包含数据如下:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    | null       | c5          | NULL        | 2014       | shanghai   |
    | s6         | c6          | 100.4       | 2014       | shanghai   |
    | s7         | c7          | 100.5       | 2014       | shanghai   |
    +------------+-------------+-------------+------------+------------+
    命令示例如下:
    select 
    case  
    when region='china' then 'default_region'
    when region like 'shang%' then 'sh_region'
    end as region 
    from sale_detail;
    返回结果如下:
    +------------+
    | region     |
    +------------+
    | default_region |
    | default_region |
    | default_region |
    | sh_region  |
    | sh_region  |
    | sh_region  |
    +------------+

CAST

  • 命令格式
    cast(<expr> as <type>)
  • 命令说明
    expr的结果转换成目标数据类型,用法如下:
    • 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
  • 参数说明
    • expr:必填。表达式,待转换数据来源。
    • type:必填。目标数据类型。
  • 返回值说明

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

  • 示例
    • 示例1:常见用法。命令示例如下:
      --返回1。
      select cast('1' as bigint);
    • 示例2:异常用法,如果转换不成功或遇到不支持的类型转换,则会返回NULL。错误命令示例如下:
      --返回NULL。
      select cast('abc' as bigint);

COALESCE

  • 命令格式
    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

  • 命令格式
    decode(<expression>, <search>, <result>[, <search>, <result>]...[, <default>])
  • 命令说明

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

  • 参数说明
    • expression:必填。要比较的表达式。
    • search:必填。与expression进行比较的搜索项。
    • result:必填。searchexpression的值匹配时的返回值。
    • default:可选。如果所有的搜索项都不匹配,则返回default值,如果未指定,则返回NULL。
    说明
    • 所有的result类型必须一致或为NULL。不一致的数据类型会返回报错。
    • 所有的searchexpression类型必须一致,否则会返回报错。
  • 返回值说明
    • 如果匹配,返回result
    • 如果没有匹配,返回default
    • 如果没有指定default,返回NULL。
    • 如果search选项有重复且匹配时,会返回第一个值。
    • 通常,MaxCompute SQL在计算NULL=NULL时返回NULL,但在该函数中,NULL与NULL的值是相等的。
  • 示例
    例如表sale_detail的字段为shop_name string, customer_id string, total_price double,,包含数据如下:
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2013       | china      |
    | s2         | c2          | 100.2       | 2013       | china      |
    | s3         | c3          | 100.3       | 2013       | china      |
    | null       | c5          | NULL        | 2014       | shanghai   |
    | s6         | c6          | 100.4       | 2014       | shanghai   |
    | s7         | c7          | 100.5       | 2014       | shanghai   |
    +------------+-------------+-------------+------------+------------+
    命令示例如下:
    --当customer_id的值为c1时,返回Taobao;值为c2时,返回Alipay;值为c3时,返回Aliyun;值为NULL时,返回N/A;其他场景返回Others。
    select
    decode(customer_id,
    'c1', 'Taobao',
    'c2', 'Alipay',
    'c3', 'Aliyun',
    Null, 'N/A',
    'Others') as result
    from sale_detail;
    --等效于如下语句。
    if customer_id = c1 then
    result := 'Taobao';
    elsif customer_id = c2 then
    result := 'Alipay';
    elsif customer_id = c3 then
    result := 'Aliyun';
    ...
    else
    result := 'Others';
    end if;
    返回结果如下:
    +------------+
    | result     |
    +------------+
    | Others     |
    | Others     |
    | Others     |
    | Taobao     |
    | Alipay     |
    | Aliyun     |
    +------------+

EXPLODE

  • 使用限制
    • 在一个select中只能出现一个explode函数,不可以出现表的其他列。
    • 不可以与group bycluster bydistribute bysort by一起使用。
  • 命令格式
    explode (<var>)
  • 命令说明
    将一行数据转为多行的UDTF。
    • 如果参数是array<T>类型,则将列中存储的ARRAY转为多行。
    • 如果参数是map<K, V>类型,则将列中存储的MAP的每个Key-Value对转换为包含两列的行,其中一列存储Key,令一列存储Value。
  • 参数说明

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

  • 返回值说明

    返回转换后的行。

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

GET_IDCARD_AGE

  • 命令格式
    get_idcard_age(<idcardno>)
  • 命令说明

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

  • 参数说明

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

  • 返回值说明

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

GET_IDCARD_BIRTHDAY

  • 命令格式
    get_idcard_birthday(<idcardno>)
  • 命令说明

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

  • 参数说明

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

  • 返回值说明

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

GET_IDCARD_SEX

  • 命令格式
    get_idcard_sex(<idcardno>)
  • 命令说明

    根据身份证号码返回性别,值为M(男)或F(女)。

  • 参数说明

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

  • 返回值说明

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

GET_USER_ID

  • 命令格式
    get_user_id()
  • 命令说明

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

  • 参数说明

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

  • 返回值说明

    返回当前账号的账号ID。

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

GREATEST

  • 命令格式
    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表达式

  • 命令格式
    if(<testCondition>, <valueTrue>, <valueFalseOrNull>)
  • 命令说明

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

  • 参数说明
    • testCondition:必填。要判断的表达式,BOOLEAN类型。
    • valueTrue:必填。表达式testCondition为True时,返回的值。
    • valueFalseOrNull:表达式testCondition为False时,返回的值,可以设为NULL。
  • 返回值说明

    返回值类型和参数valueTruevalueFalseOrNull的类型一致。

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

INLINE

  • 命令格式
    inline(array<struct<f1:T1, f2:T2, ...>>)
  • 命令说明

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

  • 参数说明

    f1:T1f2:T2:必填。可以为任意类型。

    升级到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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

    返回STRUCT数组展开的数据。

  • 示例
    例如表t_table的字段为t_struct struct<user_id:bigint,user_name:string,married:string,weight:double>,包含数据如下:
    +----------+
    | t_struct |
    +----------+
    | {user_id:10001, user_name:LiLei, married:N, weight:63.5} |
    | {user_id:10002, user_name:HanMeiMei, married:Y, weight:43.5} |
    +----------+
    命令示例如下:
    --将t_struct列展开。
    select inline(array(t_struct)) from t_table;
    --返回结果如下。
    +------------+-----------+---------+------------+
    | user_id    | user_name | married | weight     |
    +------------+-----------+---------+------------+
    | 10001      | LiLei     | N       | 63.5       |
    | 10002      | HanMeiMei | Y       | 43.5       |
    +------------+-----------+---------+------------+

LEAST

  • 命令格式
    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 map(K <key1>, V <value1>, K <key2>, V <value2>, ...)
  • 命令说明

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

  • 参数说明
    • key:必填。所有key类型一致(包括隐式转换后类型一致),必须是基本类型。
    • value:必填。所有value类型一致(包括隐式转换后类型一致),可为任意类型。
    升级到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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

    返回MAP类型。

  • 示例
    例如表t_table的字段为c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint,包含数据如下:
    +------------+----+----+------------+------------+
    | c1         | c2 | c3 | c4         | c5         |
    +------------+----+----+------------+------------+
    | 1000       | k11 | k21 | 86         | 15         |
    | 1001       | k12 | k22 | 97         | 2          |
    | 1002       | k13 | k23 | 99         | 1          |
    +------------+----+----+------------+------------+
    命令示例如下:
    --将c2、c4,c3、c5组成MAP。
    select map(c2,c4,c3,c5) from t_table;
    --返回结果如下。
    +------+
    | _c0  |
    +------+
    | {k11:86, k21:15} |
    | {k12:97, k22:2} |
    | {k13:99, k23:1} |
    +------+

MAP_KEYS

  • 命令格式
    array map_keys(map<K, V>)
  • 命令说明

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

  • 参数说明

    参数为MAP类型的数据。

  • 返回值说明

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

  • 示例
    例如表t_table_map的字段为c1 bigint,t_map map<string,bigint>,包含数据如下:
    +------------+-------+
    | c1         | t_map |
    +------------+-------+
    | 1000       | {k11:86, k21:15} |
    | 1001       | {k12:97, k22:2} |
    | 1002       | {k13:99, k23:1} |
    +------------+-------+
    命令示例如下:
    --将t_map中的Key作为数组返回。
    select c1, map_keys(t_map) from t_table_map;
    --返回结果如下。
    +------------+------+
    | c1         | _c1  |
    +------------+------+
    | 1000       | [k11, k21] |
    | 1001       | [k12, k22] |
    | 1002       | [k13, k23] |
    +------------+------+

MAP_VALUES

  • 命令格式
    array map_values(map<K, V>)
  • 命令说明

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

  • 参数说明

    参数为MAP类型的数据。

  • 返回值说明

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

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

MAX_PT

  • 命令格式
    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);

    说明 MaxCompute未提供min_pt函数。如果您需要获取分区表中有数据的最小分区,无法使用SQL语句select * from table where pt = min_pt("table");实现与max_pt函数类似的功能,但可以使用标准SQL语句select * from table where pt = (select min(pt) from table);实现相同的效果。
  • 参数说明

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

  • 返回值说明

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

    说明

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

  • 示例
    • 示例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));

NAMED_STRUCT

  • 命令格式
    struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>, ...)
  • 命令说明

    使用指定的namevalue列表建立STRUCT。

  • 参数说明
    • value:必填。可以为任意类型。
    • name:必填。指定STRING类型的Field名称。此参数为常量。
    升级到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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

    返回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

  • 命令格式
    nvl(T <value>, T <default_value>)
  • 命令说明

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

  • 示例
    例如表t_data的3个列分别为c1 stringc2 bigintc3 datetime。表中数据如下:
    +----+------------+------------+
    | c1 | c2 | c3 |
    +----+------------+------------+
    | NULL | 20 | 2017-11-13 05:00:00 |
    | ddd | 25 | NULL |
    | bbb | NULL | 2017-11-12 08:00:00 |
    | aaa | 23 | 2017-11-11 00:00:00 |
    +----+------------+------------+
    通过nvl函数将c1中为NULL的值输出为00000,c2中为NULL的值输出为0,c3中为NULL的值输出为-,命令示例如下:
    select nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
    --返回结果如下。
    +-----+------------+-----+
    | _c0 | _c1 | _c2 |
    +-----+------------+-----+
    | 00000 | 20 | 2017-11-13 05:00:00 |
    | ddd | 25 | - |
    | bbb | 0 | 2017-11-12 08:00:00 |
    | aaa | 23 | 2017-11-11 00:00:00 |
    +-----+------------+-----+

ORDINAL

  • 命令格式
    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); 

PARTITION_EXISTS

  • 命令格式
    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

  • 命令格式
    posexplode(array<T>)
  • 命令说明

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

  • 参数说明

    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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

    返回生成的表。

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

SAMPLE

  • 命令格式
    boolean sample(<x>, <y>, [<column_name1>, <column_name2>,...])
  • 命令说明

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

  • 参数说明
    • xyx必填。BIGINT类型,取值范围为大0的整型常量。表示哈希为x份,取第y份。

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

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

    • column_name:可选。采样的目标列。该参数省略时将根据xy的值随机采样。任意类型,列的值可以为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);

SIZE

  • 命令格式
    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)); 

SORT_ARRAY

  • 命令格式
    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

  • 命令格式
    split(<str>, <pat>)
  • 命令说明

    通过patstr分割后返回数组。

  • 参数说明
    • str:必填。STRING类型,指被分割的字符串。
    • pat:必填。STRING类型的分隔符,支持正则表达式。
  • 返回值说明

    返回array <string>

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

STACK

  • 命令格式
    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

  • 命令格式
    str_to_map(<text> [, <delimiter1> [, <delimiter2>]])
  • 命令说明

    使用delimiter1text分割成Key-Value对,然后使用delimiter2分割每个Key-Value对的Key和Value。

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

    返回值类型为map<string, string>。返回值是textdelimiter1delimiter2分割后的结果。

  • 示例
    --返回{"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 struct(<value1>,<value2>, ...)
  • 命令说明

    使用指定value列表建立STRUCT。

  • 参数说明

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

    升级到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级别数据类型的注意事项,请参见数据类型版本说明
  • 返回值说明

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

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

TABLE_EXISTS

  • 命令格式
    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;

TRANS_ARRAY

  • 使用限制
    • 所有作为key的列必须位于在前面,而要转置的列必须放在后面。
    • 在一个select中只能有一个UDTF,不可以再出现其他的列。
    • 不可以与group bycluster bydistribute bysort 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。

  • 示例
    例如表t_table中的数据如下。
    +----------+----------+------------+
    | login_id | login_ip | login_time |
    +----------+----------+------------+
    | wangwangA | 192.168.0.1,192.168.0.2 | 20120101010000,20120102010000 |
    | wangwangB | 192.168.45.10,192.168.67.22,192,168.6.3 | 20120111010000,20120112010000,20120223080000 |
    +----------+----------+------------+
    --执行SQL。
    select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;
    结果如下。
    +----------+----------+------------+
    | login_id | login_ip | login_time |
    +----------+----------+------------+
    | wangwangB | 192.168.45.10 | 20120111010000 |
    | wangwangB | 192.168.67.22 | 20120112010000 |
    | wangwangB | 192.168.6.3 | 20120223080000 |
    | wangwangA | 192.168.0.1 | 20120101010000 |
    | wangwangA | 192.168.0.2 | 20120102010000 |
    +----------+----------+------------+
    
    --如果表中的数据如下所示。
    Login_id LOGIN_IP LOGIN_TIME 
    wangwangA 192.168.0.1,192.168.0.2 20120101010000
    --会对数组中不足的数据补NULL。 
    Login_id Login_ip Login_time 
    wangwangA 192.168.0.1 20120101010000
    wangwangA 192.168.0.2 NULL

TRANS_COLS

  • 使用限制
    • 所有作为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的列类型保持不变,其余所有的列与原来的数据类型一致。

  • 示例
    例如表t_table中的数据如下。
    +----------+----------+------------+
    | Login_id | Login_ip1 | Login_ip2 |
    +----------+----------+------------+
    | wangwangA | 192.168.0.1 | 192.168.0.2 |
    +----------+----------+------------+
    --执行SQL。
    select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table;
    --返回结果如下。
    idx    login_id    login_ip
    1    wangwangA    192.168.0.1
    2    wangwangA    192.168.0.2

UUID

  • 命令格式
    string uuid()
  • 命令说明

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

    说明 UUID返回的是一个随机的全局ID,其重复的概率很小。