全部产品
MaxCompute

类型转换

更新时间:2017-08-15 09:01:31   分享:   

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

显式类型转换

显式类型转换是用 cast 将一种数据类型的值转换为另一种类型的值的行为,在 MaxCompute SQL 中支持的显式类型转换如下:

From/To Bigint Double String Datetime Boolean Decimal
Bigint Y Y N N Y
Double Y Y N N Y
String Y Y Y N Y
Datetime N N Y N N
Boolean N N N N N
Decimal Y Y Y N N -

其中,Y 表示可以转换,N 表示不可以转换, 表示不需要转换。

比如:

  1. select cast(user_id as double) as new_id from user;
  2. select cast('2015-10-01 00:00:00' as datetime) as new_date from user;

备注:

  • 将 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,详细说明信息请参考String类型与Datetime类型之间的转换;
  • 部分类型之间不可以通过显式的类型转换,但可以通过 SQL 内建函数进行转换,例如:从 boolean 类型转换到 string 类型,可使用函数 to_char,详细介绍请参见: TO_CHAR ,而 to_date 函数同样支持从 string 类型到 datetime 类型的转换,详细介绍请参见: TO_DATE
  • 关于 cast 的介绍请参见: CAST
  • DECIMAL 超出值域,CAST STRING TO DECIMAL 可能会出现最高位溢出报错,最低位溢出截断等情况。

隐式类型转换及其作用域

隐式类型转换是指在运行时,由 MaxCompute 依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute 支持的隐式类型转换规则如下所示:

boolean tinyint smallint int bigint float double decimal string varchar timestamp binary
boolean to T F F F F F F F F F F F
tinyint to F T T T T T T T T T F F
smallint to F F T T T T T T T T F F
int to F F F T T T T T T T F F
bigint to F F F F T T T T T T F F
float to F F F F F T T T T T F F
double to F F F F F F T T T T F F
decimal to F F F F F F F T T T F F
string to F F F F F F T T T T F F
varchar to F F F F F F T T T T F F
timestamp to F F F F F F F F T T T F
binary to F F F F F F F F F F F T

其中,T 表示可以转换,F 表示不可以转换。

注释:

  • MaxCompute2.0 新增了 DECIMAL 类型与 DATETIME 的常量定义方式,100BD 就是数值为 100 的 DECIMAL,datetime ‘2017-11-11 00:00:00’就是 datetime 类型的常量。常量定义的方便之处在于可以直接用到 values 子句和 values 表中。
  • 旧版 MaxCompute 中,因为历史原因,DOUBLE 可以隐式的转换为 BIGINT,这个转换潜在可能有数据丢失,一般数据库系统都不允许。

常见用法如下:

  1. select user_id+age+'12345',
  2. concat(user_name,user_id,age)
  3. from user;

备注:

  • 不支持的隐式类型转换会导致异常;
  • 如果在执行时转换失败,也会导致异常;
  • 由于隐式类型转换是 MaxCompute 依据上下文使用环境自动进行的类型转换,因此,我们推荐在类型不匹配时显式的用 cast 进行转换;
  • 隐式类型转换规则是有发生作用域的。在某些作用域中,只有一部分规则可以生效。详细信息请参考隐式类型转换的作用域;

关系运算符作用下的隐式转换

关系运算符包括:=, <>, <, <=, >, >=, 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 的使用方式形如:

  1. source like pattern;
  2. source rlike pattern;

二者在隐式类型转换中的注意事项:

  • LIKE 和 RLIKE 的 source 和 pattern 参数均仅接受 string 类型;
  • 其他类型不允许参与运算,也不能进行到 string 类型的隐式类型转换;

IN 的使用方式形如:

  1. key in (value1, value2, …)

In 的隐式转换规则:

  • 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 的隐式转换规则:

  • 如果返回类型只有 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的格式。

类似于下面的转换尝试,将会失败导致异常,例如:

  1. cast("2013/12/31 02/34/34" as datetime)
  2. cast("20131231023434" as datetime)
  3. cast("2013-12-31 2:34:34" as datetime)

值得注意的是,“dd”部分的阈值上限取决于月份实际拥有的天数,如果超出对应月份实际拥有的天数,将会导致异常退出,例如:

  1. cast("2013-02-29 12:12:12" as datetime) -- 异常返回,20132月没有29
  2. cast("2013-11-31 12:12:12" as datetime) -- 异常返回,201311月没有31

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

本文导读目录
本文导读目录
以上内容是否对您有帮助?