MaxCompute SQL允许数据类型之间的转换,类型转换方式包括显式类型转换和隐式类型转换。

显式类型转换

显式类型转换是通过CAST函数将一种数据类型的值转换为另一种类型的值,在MaxCompute SQL中支持的显式类型转换,如下表所示。 关于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 from user;
select cast('2015-10-01 00:00:00' as datetime) as new_date from user;
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可能会出现最高位溢出报错、最低位溢出截断等情况。
  • 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 DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
BOOLEAN Y N N N N N N N N N N N
TINYINT N Y Y Y Y Y Y Y Y Y N N
SMALLINT N N Y Y Y Y Y Y Y Y N N
INT N N Y Y Y Y Y Y Y Y N 不涉及
BIGINT N N N N Y Y Y Y Y Y N N
FLOAT N N N N Y Y Y Y Y Y N 不涉及
DOUBLE N N N N N N Y Y Y Y N N
DECIMAL N N N N N N N Y Y Y N N
STRING N N N N N N Y Y Y Y N N
VARCHAR N N N N Y Y Y Y N N 不涉及 不涉及
TIMESTAMP N N N N N N N N Y Y Y N
BINARY N N N N N N N N N N N Y

其中,Y表示可以转换,N表示不可以转换,– 表示不需要转换。不支持的隐式类型转换会导致异常。如果在执行时转换失败,也会导致异常。

说明
  • MaxCompute2.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的格式。
类似于下面的转换尝试,将会失败导致异常。
cast("2013/12/31 02/34/34" as datetime)  
cast("20131231023434" as datetime)  
cast("2013-12-31 2:34:34" as datetime)
部分的阈值上限取决于月份实际拥有的天数,如果超出对应月份实际拥有的天数,将会导致异常退出。
cast("2013-02-29 12:12:12" as datetime)      -- 异常返回,2013年2月没有29日。 
cast("2013-11-31 12:12:12" as datetime)      -- 异常返回,2013年11月没有31日。

MaxCompute提供了TO_DATE函数,用以将不满足日期格式的STRING类型数据转换为DATETIME类型。详情请参见TO_DATE