MaxCompute SQL允许数据类型之间的转换,类型转换方式包括显式类型转换和隐式类型转换。
显式类型转换
显式类型转换是通过CAST函数将一种数据类型的值转换为另一种类型的值,在MaxCompute SQL中支持的显式类型转换,如下表所示。 关于CAST的介绍请参见CAST。
From/To | BIGINT | DOUBLE | STRING | DATETIME | BOOLEAN | DECIMAL | FLOAT |
BIGINT | 不涉及 | Y | Y | N | Y | Y | Y |
DOUBLE | Y | 不涉及 | Y | N | Y | Y | Y |
STRING | Y | Y | 不涉及 | Y | Y | Y | Y |
DATETIME | N | N | Y | 不涉及 | N | N | N |
BOOLEAN | Y | Y | Y | N | 不涉及 | Y | Y |
DECIMAL | Y | Y | Y | N | Y | 不涉及 | Y |
FLOAT | Y | Y | Y | N | Y | Y | 不涉及 |
其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的显式类型转换会失败并报错退出。
示例数据:
说明本文中使用示例涉及
user
表的示例,都可使用此示例数据。create table if not exists user ( user_name string, user_id bigint, age bigint ); insert into user values ('zhangsan',111,20);
使用示例:
SELECT CAST(user_id AS DOUBLE) AS new_id from user; SELECT CAST('2015-10-01 00:00:00' AS DATETIME) AS new_date; SELECT CAST(ARRAY(1,2,3) AS ARRAY<STRING>); SELECT CONCAT_WS(',', CAST(ARRAY(1, 2) AS ARRAY<STRING>));
使用说明和限制
将DOUBLE类型转为BIGINT类型时,小数部分会被截断,例如
CAST(1.6 AS BIGINT) = 1
。满足DOUBLE格式的STRING类型转换为BIGINT时,会先将STRING转换为DOUBLE,再将DOUBLE转换为BIGINT,因此,小数部分会被截断,例如
CAST(“1.6” AS BIGINT) = 1
。满足BIGINT格式的STRING类型可以被转换为DOUBLE类型,小数点后保留一位,例如
CAST(“1” AS DOUBLE) = 1.0
。日期类型转换时采用默认格式
yyyy-mm-dd hh:mi:ss
。部分类型之间不可以通过显式的类型转换,但可以通过SQL内建函数进行转换,例如从BOOLEAN类型转换到STRING类型,可使用函数
TO_CHAR
,详情请参见TO_CHAR。而TO_DATE
函数同样支持从STRING类型到DATETIME类型的转换,详情请参见TO_DATE。DECIMAL超出值域,
CAST STRING TO DECIMAL
可能会出现最高位溢出报错、最低位溢出截断等情况。DECIMAL类型显示转换为DOUBLE、FLOAT等类型会产生精度损失,对于精度有要求的场景,例如计算金额、费率等,建议使用DECIMAL类型。
MaxCompute支持复杂类型的类型转换功能。其中复杂类型的隐式类型转换要求子类型能够隐式转换,而显示转换要求子类型能够显示转换。STRUCT类型转换不要求字段名称一致,但是要求字段的数量一致,且对应的字段能够隐式或显示转换。例如:
ARRAY<BIGINT>
能隐式转换或显示转换为ARRAY<STRING>
。ARRAY<BIGINT>
能显示转换为ARRAY<INT>
,但是不能隐式转换。ARRAY<BIGINT>
不能隐式转换或显示转换为ARRAY<DATETIME>
。STRUCT<a:BIGINT,b:INT>
能隐式转换为STRUCT<col1:STRING,col2:BIGINT>
,但是不能隐式或显示转换为STRUCT<a:STRING>
。
隐式类型转换及其作用域
隐式类型转换是指在运行时,由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute支持的隐式类型转换规则,如下表所示。
From/To | BOOLEAN | TINYINT | SMALLINT | INT | BIGINT | FLOAT |
BOOLEAN | 不涉及 | N | N | N | N | N |
TINYINT | N | 不涉及 | Y | Y | Y | Y |
SMALLINT | N | N | 不涉及 | Y | Y | Y |
INT | N | N | Y | 不涉及 | Y | Y |
BIGINT | N | N | N | N | 不涉及 | Y |
FLOAT | N | N | N | N | Y | 不涉及 |
From/To | DOUBLE | DECIMAL | STRING | VARCHAR | TIMESTAMP | BINARY |
DOUBLE | 不涉及 | Y | Y | Y | N | N |
DECIMAL | N | 不涉及 | Y | Y | N | N |
STRING | Y | Y | 不涉及 | Y | N | N |
VARCHAR | Y | Y | Y | 不涉及 | N | N |
TIMESTAMP | N | N | Y | Y | 不涉及 | N |
BINARY | N | N | N | N | N | 不涉及 |
其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的隐式类型转换会导致异常。如果在执行时转换失败,也会导致异常。
MaxCompute 2.0新增了DECIMAL类型与DATETIME的常量定义方式,100BD是数值为100的DECIMAL,
2017-11-11 00:00:00
是DATETIME类型的常量。VALUES子句和VALUES表中可以直接使用常量定义。由于隐式类型转换是MaxCompute依据上下文使用环境自动进行的类型转换,因此推荐您在类型不匹配时,显式地用CAST进行转换。
隐式类型转换规则是有发生作用域的。在某些作用域中,只有一部分规则可以生效。
示例
SELECT user_id+age+'12345', CONCAT(user_name,user_id,age) FROM user;
不同运算符作用下的隐式转换如下:
关系运算符作用下的隐式转换
关系运算符包括
=、<>、<、<=、>、>=、IS NULL、IS NOT NULL、LIKE、RLIKE、IN
。由于LIKE、RLIKE、IN
的隐式类型转换规则不同于其他关系运算符,将单独对其进行说明。此处的说明不包含这三种特殊的关系运算符。当不同类型的数据共同参与关系运算时,按照下述原则进行隐式类型转换。
From/To
BIGINT
DOUBLE
STRING
DATETIME
BOOLEAN
DECIMAL
BIGINT
不涉及
DOUBLE
DOUBLE
N
N
DECIMAL
DOUBLE
DOUBLE
不涉及
DOUBLE
N
N
DECIMAL
STRING
DOUBLE
DOUBLE
不涉及
DATETIME
N
DECIMAL
DATETIME
N
N
DATETIME
不涉及
N
N
BOOLEAN
N
N
N
N
不涉及
N
DECIMAL
DECIMAL
DECIMAL
DECIMAL
N
N
不涉及
说明如果进行比较的两个类型间不能进行隐式类型转换,则该关系运算不能完成,报错退出。
关系运算符的更多详情,请参见运算符。
特殊的关系运算符作用下的隐式转换
特殊的关系运算符包括
LIKE、RLIKE、IN
。LIKE和RLIKE的使用方式,如下所示。
source LIKE pattern; source RLIKE pattern;
说明LIKE和RLIKE的source和pattern参数均仅接受STRING类型。
其他类型不允许参与运算,也不能进行到STRING类型的隐式类型转换。
IN的使用方式如下。
key IN (value1, value2, …)
说明IN右侧的VALUE值列表中的数据类型必须保持一致。
当KEY与VALUES之间比较时,如果数据类型包含BIGINT、DOUBLE、STRING,建议统一转换为DOUBLE类型;如果数据类型包含DATETIME、STRING,建议统一转换为DATETIME类型。除此之外不允许其他类型之间的转换。
算术运算符作用下的隐式转换
算术运算符包括
+、-、*、/、%
,其隐式转换规则如下:只有STRING、BIGINT、DOUBLE和DECIMAL才能参与算术运算。
STRING在参与运算前会进行隐式类型转换到DOUBLE。
BIGINT和DOUBLE共同参与计算时,会将BIGINT隐式转换为DOUBLE。
日期型和布尔型不允许参与算数运算。
逻辑运算符作用下的隐式转换
逻辑运算符包括
and、or、not
,其隐式转换规则,如下:只有BOOLEAN才能参与逻辑运算。
其他类型不允许参与逻辑运算,也不允许其他类型的隐式类型转换。
内建函数涉及到的隐式转换
MaxCompute SQL提供了大量的系统函数,方便您对任意行的一列或多列进行计算,输出任意种类的数据类型。其隐式转换规则如下:
在调用函数时,如果输入参数的数据类型与函数定义的参数数据类型不一致,把输入参数的数据类型转换为函数定义的数据类型。
每个MaxCompute SQL内建函数的参数对于允许的隐式类型转换的要求不同,详情请参见内建函数概述。
CASE WHEN作用下的隐式转换
CASE WHEN的详情介绍请参见CASE WHEN表达式。它的隐式转换规则,如下:
如果返回类型只有BIGINT、DOUBLE,统一转换为DOUBLE。
如果返回类型中有STRING类型,统一转换为STRING,如果不能转换(如BOOLEAN类型)则报错。
除此之外不允许其他类型之间的转换。
STRING与DATETIME类型之间的转换
MaxCompute支持STRING类型和DATETIME类型之间的相互转换。转换时使用的格式为yyyy-mm-dd hh:mi:ss
。
单位 | 字符串(忽略大小写) | 有效值域 |
年 | yyyy | 0001~9999 |
月 | mm | 01~12 |
日 | dd | 01~28|29|30|31 |
时 | hh | 00~23 |
分 | mi | 00~59 |
秒 | ss | 00~59 |
各个单位的值域中,如果首位为0,不可省略。例如
2014-1-9 12:12:12
为非法的DATETIME格式,无法从STRING类型数据转换为DATETIME类型,必须写为2014-01-09 12:12:12
。只有符合上述格式描述的STRING类型才能够转换为DATETIME类型,例如
CAST(“2013-12-31 02:34:34” AS DATETIME)
,将会把STRING类型2013-12-31 02:34:34
转换为DATETIME类型。同理,DATETIME转换为STRING时,默认转换为yyyy-mm-dd hh:mi:ss
格式。
MaxCompute提供了TO_DATE函数,用于将不满足日期格式的STRING类型数据转换为DATETIME类型。详情请参见TO_DATE。