本文为您介绍如何使用CAST、DECODE、LEAST、ARRAY、SPLIT、MAP等函数。

CAST

函数声明格式如下。
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

函数声明格式如下。
coalesce(expr1, expr2, ...)
用途:返回列表中第一个非NULL的值,如果列表中所有的值都是NULL,则返回NULL。
参数说明:
  • expr是要测试的值,所有这些值类型必须相同或为NULL,否则会引发异常。
  • 至少要有一个参数,否则会引发异常。
返回值说明:
  • 返回值类型和参数类型相同。

DECODE

函数声明格式如下。
decode(expression, search, result[, search, result]...[, default])
用途:实现if-then-else分支选择的功能。
参数说明:
  • expression:要比较的表达式。
  • search:与expression进行比较的搜索项。
  • resultsearchexpression的值匹配时的返回值。
  • default:可选项,如果所有的搜索项都不匹配,则返回此default值,如果未指定,则返回NULL。
返回值说明:
  • 返回匹配的search
  • 如果没有匹配,返回default
  • 如果没有指定default,返回NULL。
    说明
    • 至少要指定三个参数。
    • 所有的result类型必须一致,或为NULL。不一致的数据类型会引发异常。所有的searchexpression类型必须一致,否则会引发异常。
    • 如果decode中的search选项有重复时且匹配时,会返回第一个值。
示例
select
decode(customer_id,
1, 'Taobao',
2, 'Alipay',
3, 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
示例中的decode函数实现了下面if-then-else语句中的功能。
if customer_id = 1 then
result := 'Taobao';
elsif customer_id = 2 then
result := 'Alipay';
elsif customer_id = 3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;
说明
  • 通常情况下,MaxCompute SQL在计算NULL=NULL时返回NULL,但在decode函数中,NULL与NULL的值是相等的。
  • 上述示例中,当customer_id的值为NULL时,decode函数返回N/A。

GET_IDCARD_AGE

函数声明格式如下。
get_idcard_age(idcardno)
用途:根据身份证号返回当前的年龄。返回值为当前年份减去身份证中标识的出生年份的差值。
参数说明:
  • idcardno:STRING类型,15位或18位身份证号。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性。如果校验不通过会返回NULL。
返回值说明:
  • 返回BIGINT类型。输入为NULL时,返回NULL。如果当前年份减去出生年份差值大于100,返回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。

GREATEST

函数声明格式如下。
greatest(var1, var2, …)
用途:返回输入参数中最大的一个。
参数说明:
  • var1var2可以为BIGINT、DOUBLE、DECIMAL、DATETIME或STRING类型。如果所有的参数值都为NULL,则返回NULL。
返回值说明:
  • 输入参数中的最大值。当不存在隐式转换时,返回同输入参数类型。
  • NULL为最小值。
  • 当输入参数类型不同时,DOUBLE、BIGINT、DECIMAL、STRING之间的比较转为DOUBLE类型;STRING、DATETIME的比较转为DATETIME类型。不允许其它的隐式转换。

ORDINAL

函数声明格式如下。
ordinal(BIGINT nth, var1, var2, …)
用途:将输入变量按从小到大排序后,返回nth指定位置的值。
参数说明:
  • nth:BIGINT类型,指定要返回的位置为NULL时,返回NULL。
  • var1var2:类型可以为BIGINT、DOUBLE、DATETIME或STRING类型。
返回值说明:
  • 排在第nth位的值,当不存在隐式转换时返回同输入参数类型。
  • 当有类型转换时,DOUBLE、BIGINT、STRING之间的转换返回DOUBLE类型;STRING、DATETIME之间的转换返回DATETIME类型。不允许其它的隐式转换。
  • NULL为最小值。
示例
ordinal(3, 1, 3, 2, 5, 2, 4, 6) = 2

LEAST

函数声明格式如下。
least(var1, var2, …)
用途:返回输入参数中最小的一个。
参数说明:
  • var1var2可以为BIGINT、DOUBLE、DECIMAL、DATETIME或STRING类型。如果所有参数值都为NULL,则返回NULL。
返回值说明:
  • 输入参数中的最小值。当不存在隐式转换时,返回同输入参数类型。
  • 当有类型转换时,DOUBLE、BIGINT、STRING之间的转换返回DOUBLE类型;STRING、DATETIME之间的转换返回DATETIME类型;DECIMAL和DOUBLE、BIGINT、STRING之间比较时,转为DECIMAL类型。不允许其它的隐式类型转换。
  • NULL为最小值。

MAX_PT

函数声明格式如下。
max_pt(table_full_name)
用途:对于分区的表,此函数返回该分区表的一级分区的最大值,按字母排序,且该分区下有对应的数据文件。
参数说明:
  • table_full_name:STRING类型,指定表名(必须带上Project名,例如prj.src),您必须对此表有读权限。
返回值说明:
  • 返回最大的一级分区的值。

示例

例如tbl是分区表,该表对应的分区如下,且都有数据文件。
pt =‘20120901’
pt =‘20120902’
则以下语句中max_pt返回值为‘20120902’。MaxCompute SQL语句读出pt=‘20120902’分区下的数据。
select * from tbl where pt=max_pt('myproject.tbl');
说明

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

UUID

函数声明格式如下。
string TRING uuid()
用途:返回一个随机ID,形式为29347a88-1e57-41ae-bb68-a9edbdd94212
说明 UUID返回的是一个随机的全局ID,其重复的概率很小。

SAMPLE

函数声明格式如下。
boolean sample(x, y, column_name1,column_name2,...)
用途:对所有读入的column_name的值,sample根据xy的设置做采样,并过滤掉不满足采样条件的行。
参数说明:
  • xy:BIGINT类型,表示哈希为x份,取第y份。y可省略,省略时取第一份,如果省略参数中的y,则必须同时省略column_namexy为整型常量,大于0,其它类型或小于等于0时抛异常,如果y>gtx也抛异常。xy任一输入为NULL时,返回NULL。
  • column_name为采样的目标列。该参数可以省略,省略时将根据xy的值随机采样。任意类型,列的值可以为NULL。不做隐式类型转换。如果column_name为常量NULL,则报异常。
返回值说明:
  • 返回BOOLEAN类型。
说明 为避免NULL值带来的数据倾斜,对于column_name中为NULL的值,会在x份中进行均匀哈希。如果不加column_name,则数据量比较少时输出不一定均匀,在这种情况下建议加上column_name,以获得较好的输出结果。

示例

例如存在表tbla,表内有列名为cola的列。
select * from tbla where sample (4, 1 , cola) = true;
-- 表示数值会根据cola hash为4份,取第1份。
select * from tbla where sample (4, 2) = true;
-- 表示数值会对每行数据做随机哈希分配为4份,取第2份。

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
case when表达式可以根据表达式value的计算结果,灵活地返回不同的值。根据shop_name的不同情况得出所属区域,示例如下。
select
case
when shop_name is null then 'default_region'
when shop_name like 'hang%' then 'zj_region'
end as region
from sale_detail;
说明
  • 如果result类型只有BIGINT、DOUBLE,统一转为DOUBLE后,再返回。
  • 如果result类型中有STRING类型,则统一转为STRING后,再返回。如果无法进行类型转换(例如BOOLEAN类型无法转换为STRING类型),则会发生报错。
  • 除此之外不允许其它类型之间的转换。

IF表达式

表达式语法如下。
if(testCondition, valueTrue, valueFalseOrNull)
用途:判断testCondition是否为真。如果为真,返回valueTrue,否则返回另一个值(valueFalse或者NULL)。
参数说明:
  • testCondition:要判断的表达式,BOOLEAN类型。
  • valueTrue:表达式testCondition为True时,返回的值。
  • valueFalseOrNull:不满足表达式testCondition时,返回的值可以设为NULL。
返回值说明:
  • 返回值类型和参数valueTrue或者valueFalseOrNull的类型一致。
示例
select if(1=2,100,200) from dual; 
--结果如下。
+------------+
| _c0 |
+------------+
| 200 |
+------------+

SPLIT

函数声明格式如下。
split(str, pat)
用途:通过patstr分割后返回数组。
参数说明:
  • str:STRING类型,指被分隔的字符串。
  • pat:STRING类型的分隔符,支持正则。
返回值说明:
  • 返回array <STRING>。元素是strpat分隔后的结果。
示例
select split("a,b,c",",") from dual;
--结果如下。
+------+
| _c0 |
+------+
| [a, b, c] |
+------+

STR_TO_MAP

函数声明格式如下。
str_to_map(text [, delimiter1 [, delimiter2]])
用途:使用delimiter1text分割成K-V对,然后使用delimiter2分割每个K-V对的K和V。
参数说明:
  • text:STRING类型,指被分割的字符串。
  • delimiter1:STRING类型,分隔符,不指定时默认为逗号(,)。
  • delimiter2:STRING类型,分隔符,不指定时默认为句号(=
返回值说明:
  • 返回MAP<STRING, STRING>。元素是textdelimiter1delimiter2分割后的K-V结果。
示例
select str_to_map('test1&1-test2&2','-','&');
--结果如下。
+------------+
| a          |
+------------+
| {test1:1, test2:2} |

EXPLODE

函数声明格式如下。
explode (var)
用途:将一行数据转为多行的UDTF。如果var是ARRAY,则将列中存储的ARRAY转为多行;如果var是MAP,则将列中存储的MAP的每个Key-Value转换为包含两列的行,其中一列存储Key,令一列存储Value。
参数说明:
  • var:ARRAY<T>类型或者MAP<K, V>类型。
返回值说明:
  • 返回转换后的行。
说明
UDTF在使用上有以下限制:
  • 在一个select中只能有一个UDTF,不可出现其它的列。
  • 不可以与group bycluster bydistribute bysort by一起使用。
示例
explode(array(null, 'a', 'b', 'c')) col

MAP

函数声明格式如下。
MAP map(K key1, V value1, K key2, V value2, ...)
用途:使用给定的Key-Value对建立MAP。
参数说明:
  • 所有key类型一致(包括隐式转换后类型一致),必须是基本类型。
  • 所有value类型一致(包括隐式转换后类型一致),可为任意类型。
返回值说明:
  • 返回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          |
+------------+----+----+------------+------------+
执行SQL。
select  map(c2,c4,c3,c5) from t_table;
--结果如下。
+------+
| _c0  |
+------+
| {k11:86, k21:15} |
| {k12:97, k22:2} |
| {k13:99, k23:1} |
+------+
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

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} |
+------------+-------+
执行SQL。
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]

ARRAY

函数声明格式如下。
ARRAY array(value1,value2, ...)
用途:使用给定的Value构造ARRAY。
参数说明:
  • 参数可为任意类型,但是所有参数的类型必须一致。
返回值说明:
  • 返回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          |
+------------+----+----+------------+------------+
执行SQL。
select array(c2,c4,c3,c5) from t_table;
--结果如下。
+------+
| _c0  |
+------+
| [k11, 86, k21, 15] |
| [k12, 97, k22, 2] |
| [k13, 99, k23, 1] |
+------+
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

SIZE

函数声明格式如下。
INT size(map)
INT size(array)
用途:size(map)返回给定map中K/V对数。size(array)返回给定的array中的元素数目。
参数说明:
  • map:MAP类型的数据。
  • array:ARRAY类型的数据。
返回值说明:
  • 返回INT类型。
示例
select size(map('a',123,'b',456)) from dual; --返回2。
select size(map('a',123,'b',456,'c',789)) from dual; --返回3。
select size(array('a','b')) from dual; --返回2。
select size(array(123,456,789)) from dual; --返回3。

ARRAY_CONTAINS

函数声明格式如下。
boolean array_contains(ARRAY<T> a,value v)
用途:检测给定ARRAY a中是否包含v
参数说明:
  • a:ARRAY类型的数据。
  • v:必须与ARRAY数组中的数据类型一致。
返回值说明:
  • 返回BOOLEAN类型。

示例

例如表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] |
+------------+---------+
执行SQL。
select c1, array_contains(t_array,'1') from t_table_array;
--结果如下。
+------------+------+
| c1         | _c1  |
+------------+------+
| 1000       | false |
| 1001       | false |
| 1002       | true |
+------------+------+

SORT_ARRAY

函数声明格式如下。
ARRAY sort_array(ARRAY<T>)
用途:为给定的数组排序。
参数说明:
  • ARRAY<T>:ARRAY类型的数据。数组中的数据可为任意类型。
返回值说明:
  • 返回ARRAY类型。

示例

例如表t_array的字段为c1 ARRAY<STRING>,c2 ARRAY<INT> ,c3 ARRAY<STRING>,数据如下。
+------------+---------+--------------+
| c1         | c2      | c3           |
+------------+---------+--------------+
| [a, c, f, b]  | [4, 5, 7, 2, 5, 8]  |  [你, 我, 他] |
+------------+---------+--------------+
执行SQL。
select sort_array(c1),sort_array(c2),sort_array(c3) from t_array;
--结果如下。
[a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

POSEXPLODE

函数声明格式如下:
posexplode(ARRAY<T>)
用途:将给定的ARRAY展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。
参数说明:
  • ARRAY:ARRAY类型的数据。数组中的数据可为任意类型。
返回值说明:
  • 返回表生成的函数。
示例
select posexplode(array('a','c','f','b')) from dual;
--结果如下。
+------------+-----+
| pos | val |
+------------+-----+
| 0 | a |
| 1 | c |
| 2 | f |
| 3 | b |
+------------+-----+
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

STRUCT

函数声明格式如下。
STRUCT struct(value1,value2, ...)
用途:使用给定value列表建立STRUCT。
参数说明:
  • value:各value可为任意类型。
返回值说明:
  • 返回STRUCT类型。Field的名称依次为col1,col2,…
示例
select struct('a',123,'ture',56.90) from dual;
--结果如下。
{col1:a, col2:123, col3:ture, col4:56.9}
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

NAMED_STRUCT

函数声明格式如下。
STRUCT named_struct(string name1, T1 value1, string name2, T2 value2, ...)
用途:使用给定的name/value列表建立STRUCT。
参数说明:
  • value:各value可为任意类型。
  • name:指定的STRING类型的Field名称。
返回值说明:
  • 返回STRUCT类型。Field的名称依次为col1,col2,…
示例
select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50) from dual;
--结果如下。
{user_id:10001, user_name:LiLei, married:F, weight:63.5}
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

INLINE

函数声明格式如下。
inline(array<struct<f1:T1, f2:T2, ...>>)
用途:将给定的STRUCT数组展开。每个元素对应一行,每行每个STRUCT元素对应一列。
参数说明:
  • STRUCT:数组中的value可为任意类型。
返回值说明:
  • 返回表生成的函数。

示例

例如表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} |
+----------+
执行SQL。
select inline(array(t_struct)) from t_table;
--结果如下。
+------------+-----------+---------+------------+
| user_id    | user_name | married | weight     |
+------------+-----------+---------+------------+
| 10001      | LiLei     | N       | 63.5       |
| 10002      | HanMeiMei | Y       | 43.5       |
+------------+-----------+---------+------------+
说明 目前MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,需要用set命令开启:
  • Session级别:要使用新数据类型,需在SQL语句前加上set语句set odps.sql.type.system.odps2=true;,并与SQL语句一起提交执行。
  • Project级别:支持对Project级别进行新类型打开。Project Owner可根据需要对Project进行设置,命令为:
    setproject odps.sql.type.system.odps2=true;
    setproject的详细说明请参见其他操作。关于Project级别开启数据类型时的注意事项,请参见数据类型

TRANS_ARRAY

函数声明格式如下。
trans_array (num_keys, separator, key1,key2,…,col1, col2,col3) as (key1,key2,…,col1, col2)
用途:将一行数据转为多行的UDTF,将列中存储的以固定分隔符格式分隔的数组转为多行。
参数说明:
  • num_keys: BIGINT类型常量,值必须>=0。在转为多行时作为转置key的列的个数。
  • key:是指在将一行转为多行时,在多行中重复的列。
  • separator:STRING类型常量,用于将字符串拆分成多个元素的分隔符。为空时报异常。
  • keys:转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。
  • cols: 要转为行的数组,keys之后的所有列视为要转置的数组,必须为STRING类型,存储的内容是字符串格式的数组,例如Hangzhou;Beijing;shanghai,是以分号(;)分隔的数组。
返回值说明: 返回转置后的行,新的列名由as指定。作为key的列类型保持不变,其余所有的列是STRING类型。拆分成的行数以个数多的数组为准,不足的补NULL。
说明
UDTF的使用限制:
  • 所有作为key的列必须处在前面,而要转置的列必须放在后面。
  • 在一个select中只能有一个UDTF,不可以再出现其它的列。
  • 不可以与group bycluster bydistribute bysort by一起使用。

示例

  • 例如表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

函数声明格式如下。
trans_cols (num_keys, key1,key2,…,col1, col2,col3) as (idx, key1,key2,…,col1, col2)
用途:用于将一行数据转为多行的UDTF,将不同的列转为不同的行。
参数说明:
  • num_keys: BIGINT类型常量,值必须>=0。在转为多行时作为转置key的列的个数。
  • key:是指在将一行转为多行时,在多行中重复的列。
  • separator:STRING类型常量,用于将字符串拆分成多个元素的分隔符。为空时报异常。
  • keys:转置时作为key的列, 个数由num_keys指定。如果num_keys指定所有的列都作为key(即num_keys等于所有列的个数),则只返回一行。
  • cols: 要转为行的列,类型必须相同。
返回值说明: 返回转置后的行,新的列名由as指定。输出的第一列是转置的下标,下标从1开始。作为key的列类型保持不变,其余所有的列与原来类型一致。
说明
UDTF的使用限制:
  • 所有作为key的列必须处在前面,而要转置的列必须放在后面。
  • 在一个select中只能有一个UDTF,不可以再出现其它的列。
  • 不可以与group bycluster bydistribute bysort by一起使用。

示例

例如表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

NVL

函数声明格式如下。
nvl(T value, T default_value)
用途:如果value值为NULL,返回default_value,否则返回value。两个参数数据类型需要一致。

示例

例如表t_data的3个列分别为c1 STRING、c2 BIGINT、c3 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的值输出为-,执行如下SQL。
SELECT nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') from nvl_test;
--结果如下。
+-----+------------+-----+
| _c0 | _c1 | _c2 |
+-----+------------+-----+
| bbb | 0 | 2017-11-12 08:00:00 |
| ddd | 25 | - |
| 00000 | 20 | 2017-11-13 05:00:00 |
| aaa | 23 | 2017-11-11 00:00:00 |
+-----+------------+-----+