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进行比较的搜索项。
  • result:search和expression的值匹配时的返回值。
  • default:可选项,如果所有的搜索项都不匹配,则返回此default值,如果未指定,则返回Null。
返回值
  • 返回匹配的search。
  • 如果没有匹配,返回default。
  • 如果没有指定default,返回Null。
    说明
    • 至少要指定三个参数。
    • 所有的result类型必须一致,或为Null。不一致的数据类型会引发异常。所有的search和expression类型必须一致,否则报异常。
    • 如果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, …)

命令说明如下

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

参数说明

var1,var2可以为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。
  • var1,var2:类型可以为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, …)

命令说明如下

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

参数说明

var1,var2可以为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 uuid()

命令说明如下

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

说明

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

SAMPLE

命令格式如下
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的列。
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 (_condition1) then result1
when (_condition2) 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型)。
  • 除此之外不允许其它类型之间的转换。

IF表达式

命令格式如下
if(testCondition, valueTrue, valueFalseOrNull)

命令说明如下

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

参数说明
  • testCondition:要判断的表达式,Boolean类型。
  • valueTrue:表达式testCondition为True时,返回的值。
  • valueFalseOrNull:不满足表达式testCondition时,返回的值可以设为Null。

返回值

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

示例如下
select if(1=2,100,200) from dual; 
返回值:
+------------+
| _c0 |
+------------+
| 200 |
+------------+

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

SPLIT

命令格式如下
split(str, pat)

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

参数说明
  • str:String类型,指被分隔的字符串。
  • pat:String类型,分隔符,支持正则。

返回值

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

示例如下
select split("a,b,c",",") from dual;
结果如下:
+------+
| _c0 |
+------+
| [a, b, c] |
+------+
说明

目前MaxCompute SQL及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

STR_TO_MAP

函数声明:

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

用途:使用delimiter1将text分割成K-V对,然后使用delimiter2分割每个K-V对的K和V。

参数说明

  • text:String类型,指被分割的字符串;

  • delimiter1: String类型,分隔符, 不指定时默认为’,’;

  • delimiter2:String类型,分隔符, 不指定时默认为’=’;

返回值:map < string, string >, 元素是text被delimiter1和delimiter2分割后的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 by/cluster by/distribute by/sort by一起使用。
示例如下
explode(array(null, 'a', 'b', 'c')) col
说明

目前MaxCompute SQL及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

MAP

命令格式如下
MAP map(K key1, V value1, K key2, V value2, ...)

命令说明如下

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

参数说明

key/value
  • 所有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} |
+------+

MAP_KEYS

命令格式如下
ARRAY map_keys(map<K, V> )

命令说明如下

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

参数说明

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

返回值

返回ARRAY类型,输入Null,返回Null。

示例如下
select map_values(map('a',123,'b',456));
--结果如下:
[123, 456]
说明

目前MaxCompute SQL及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

ARRAY

命令格式如下
ARRAY array(value1,value2, ...)

命令说明如下

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

参数说明

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

返回值

返回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及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

SIZE

命令格式如下
INT size(map)
INT size(array)
命令说明如下
  • size(map<K,V>)返回给定map中K/V对数。
  • size(array<T>)返回给定的array中的元素数目。
参数说明
  • map<K, V>:map类型的数据。
  • array<T>: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
说明

目前MaxCompute SQL及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

ARRAY_CONTAINS

命令格式如下
boolean array_contains(ARRAY<T> a,value v)

命令说明如下

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

参数说明
  • a:array类型的数据。
  • v:给出的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类型。

示例如下
select sort_array(array('a','c','f','b')),sort_array(array(4,5,7,2,5,8)),sort_array(array('你','我','他')) from dual;
结果如下:
[a, b, c, f] [2, 4, 5, 5, 7, 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及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

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 |
+------------+-----+

STRUCT

命令格式如下
STRUCT struct(value1,value2, ...)

函数说明

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

参数说明

value:各value可为任意类型。

返回值

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

示例如下
select struct('a',123,'ture',56.90) from dual;
结果如下:
{col1:a, col2:123, col3:ture, col4:56.9}
说明

目前MaxCompute SQL及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

NAMED_STRUCT

命令格式如下
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,…。

示例如下
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及新版本Mapreduce支持的Set命令分为以下两种方式:

  • session级别:要使用新数据类型(Tinyint、Smallint、 Int、 Float、Varchar、TIMESTAMP BINARY),需在建表语句前加上set语句set odps.sql.type.system.odps2=true;,并与建表语句一起提交执行。
  • project级别:即支持对project级别进行新类型打开。project的Owner可根据需要对project进行设置,命令为:
    - setproject odps.sql.type.system.odps2=true;
    
    对setproject的详细说明请参见:其他操作

INLINE

命令格式如下:
inline(array<struct<f1:T1, f2:T2, ...>>)

命令说明如下:

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

参数说明:

STRUCT<f1:T1, f2:T2, ...>:数组中的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       |
+------------+-----------+---------+------------+

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 by/cluster by/distribute by/sort 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

S28新函数

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 |
+-----+------------+-----+