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表示不可以转换,不涉及表示不需要转换。不支持的显式类型转换会失败并报错退出。

示例
SELECT CAST(user_id AS DOUBLE) AS new_id;
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 Y N N N N N
TINYINT N Y Y Y Y Y
SMALLINT N N Y Y Y Y
INT N N Y Y Y Y
BIGINT N N N N Y Y
FLOAT N N N N Y Y
From/To DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
DOUBLE Y Y Y Y N N
DECIMAL N Y Y Y N N
STRING Y Y Y Y N N
VARCHAR Y Y N N 不涉及 不涉及
TIMESTAMP N N Y Y Y N
BINARY N N N N N Y

其中,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