标准 SQL 中的转换规则

“转换”包括但不限于类型转换 (casting) 和强制转换 (coercion)。

  • 类型转换属于显式转换,使用 CAST() 函数。
  • 强制转换属于隐式转换,BigQuery 在下述条件下会自动执行此转换。
  • 此外还存在第三种转换函数,它们有自己的函数名称,例如 UNIX_DATE()

下表汇总了 BigQuery 数据类型所有可能的 CAST 和强制转换。“强制转换为”可应用于指定数据类型(例如列类型)的所有表达式,而且也支持对字面量和参数进行强制转换。如需了解详情,请参阅字面量强制转换参数强制转换

原类型 类型转换目标类型 强制转换目标类型
INT64 BOOL
INT64
NUMERIC
FLOAT64
STRING
FLOAT64
NUMERIC
NUMERIC INT64
NUMERIC
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
NUMERIC
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
NUMERIC
FLOAT64
STRING
BYTES
DATE
DATETIME
TIME
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
DATETIME
STRING
TIMESTAMP
 
DATETIME DATE
DATETIME
STRING
TIME
TIMESTAMP
 
TIME STRING
TIME
 
TIMESTAMP DATE
DATETIME
STRING
TIME
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

类型转换

语法:

CAST(expr AS typename)

类型转换语法在查询中使用,用于指示表达式的结果类型应转换成其他某种类型。

示例:

CAST(x=1 AS STRING)

如果 x1,则此表达式的结果为 "true";如果为任何其他非 NULL 值,结果为 "false";如果 xNULL,则结果为 NULL

如果受支持的类型之间的类型转换未从原始值成功地映射到目标域,则会引发运行时错误。例如,如果字节序列不是有效的 UTF-8,则将 BYTES 类型转换为 STRING 类型会导致运行时错误。

在对以下类型的表达式 x 进行类型转换时,下述规则适用:

原类型 目标类型 x 进行类型转换时适用的规则
INT64 FLOAT64 返回一个接近但可能不精确的 FLOAT64 值。
INT64 BOOL 如果 x0,则返回 FALSE;否则返回 TRUE
NUMERIC 浮点 NUMERIC 类型将转换为最接近的浮点数,可能有一定的精度损失。
FLOAT64 INT64 返回最接近的 INT64 值。
中间数(例如 1.5 或 -0.5)向远离 0 的方向舍入。
FLOAT64 STRING 返回一个近似的字符串表示形式。
FLOAT64 NUMERIC 如果浮点数的小数点后的位数超过 9 位,则会向远离 0 方向按中间数舍入。对 NaN+inf-inf 进行类型转化会返回错误。对 NUMERIC 范围以外的值进行类型转换会返回溢出错误。
BOOL INT64 如果 xTRUE,则返回 1;否则返回 0
BOOL STRING 如果 xTRUE,则返回 "true",否则返回 "false"
STRING FLOAT64 以 FLOAT64 值的形式返回 x,将其解释为具有与有效的 FLOAT64 字面量相同的形式。
也支持从 "inf""+inf""-inf""nan" 进行类型转换。
转换不区分大小写。
STRING NUMERIC STRING 中包含的数字字面量不得超出 NUMERIC 类型的最大精度或范围,否则将发生错误。如果小数点后的位数超过 9 位,则生成的 NUMERIC 值会向远离 0 方向舍入,从而在小数点后保留 9 位数。
STRING BOOL 如果 x"true",则返回 TRUE;如果 xFALSE,则返回 "false"
x 的所有其他值均无效且引发错误,而不会转换为 BOOL。
转换为 BOOL 时,STRING 不区分大小写。
STRING BYTES STRING 通过 UTF-8 编码转换为 BYTES。例如,在转换为 BYTES 时,字符串“©”会转为一个包含十六进制值 C2 和 A9 的 2 字节序列。
BYTES STRING 返回解释为 UTF-8 STRING 的 x
例如,在转换为 STRING 时,BYTES 字面量 b'\xc2\xa9' 会解释为 UTF-8 并且转为 Unicode 字符“©”。
如果 x 不是有效的 UTF-8,则引发错误。
ARRAY ARRAY 两者必须是完全相同的 ARRAY 类型。
STRUCT STRUCT 仅在满足以下条件时可用:
  1. 这两个 STRUCT 的字段数量相同。
  2. 原始 STRUCT 字段类型可显式转换为相应的目标 STRUCT 字段类型(由字段顺序定义,而不是由字段名称定义)。

安全类型转换

在使用 CAST 时,如果 BigQuery 无法执行类型转换,查询就会失败。例如,以下查询会生成错误:

SELECT CAST("apple" AS INT64) AS not_a_number;

若要保护您的查询,避免这些类型的错误,您可以使用 SAFE_CASTSAFE_CAST 基本等同于 CAST,唯一的差别就在于前者返回 NULL,而非引发错误。

SELECT SAFE_CAST("apple" AS INT64) AS not_a_number;

+--------------+
| not_a_number |
+--------------+
| NULL         |
+--------------+

如果将字节转换为字符串,您还可以使用 SAFE_CONVERT_BYTES_TO_STRING 函数。任何无效的 UTF-8 字符都会替换为 Unicode 替换字符 U+FFFD。如需了解详情,请参阅 SAFE_CONVERT_BYTES_TO_STRING

将十六进制字符串转换为整数

如果您使用十六进制字符串 (0x123),可以将这些字符串的类型转换为整数:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | -291       |
+-----------+------------+

转换日期类型

BigQuery 支持在日期类型与字符串之间相互转换,如下所示:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

日期类型转换为字符串与时区无关,并采用 YYYY-MM-DD 形式。字符串转换为日期时,字符串必须遵循所支持的日期字面量格式,但与时区无关。如果字符串表达式无效或其表示的日期超出了所支持的最小/最大范围,则出现错误。

转换时间戳类型

BigQuery 支持在时间戳类型与字符串之间相互转换,如下所示:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

时间戳类型转换为字符串时,使用默认时区世界协调时间 (UTC) 解释时间戳。所生成的亚秒位数取决于亚秒部分中尾随零的数量:CAST 函数将截取 0、3 或 6 位数。

字符串转换为时间戳时,string_expression 必须遵循所支持的时间戳字面量格式,否则将出现运行时错误。string_expression 可自带一个 time_zone - 请参阅时区。如果 string_expression 中存在时区,则转换时使用该时区,否则使用默认时区世界协调时间 (UTC)。如果字符串少于 6 位数,则将其隐式加宽。

如果 string_expression 无效,其亚秒位数超过 6 位数(即精度高于微秒),或者所表示的时间超出了支持的时间戳范围,则会引发错误。

在日期和时间戳类型之间转换

BigQuery 支持在日期与时间戳类型之间相互转换,如下所示:

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

如果从日期转换为时间戳,则从默认时区世界协调时间 (UTC) 的午夜(一天之始)起解释 date_expression。如果从时间戳转换为日期,则自默认时区起实际截断时间戳。

强制转换

如果需要匹配函数签名,BigQuery 会将表达式的结果转换成其他类型。例如,如果函数 func() 定义为获取 INT64 类型的单个参数,并使用一个表达式作为结果类型为 FLOAT64 的参数,则在该表达式的结果转换为 INT64 类型后,才开始计算 func()。

字面量强制转换

BigQuery 支持以下字面量强制转换:

输入数据类型 结果数据类型 备注
STRING 字面量 DATE
TIMESTAMP

在实际字面量类型不同于相关函数预期的类型时,就需要字面量强制转换。举例来说,如果函数 func() 获取一个 DATE 参数,则表达式 func("2014-09-27") 有效,因为 STRING 字面量 "2014-09-27" 强制转换为 DATE。

字面量转换会在分析时进行评估,如果输入子面子无法成功转换为目标类型,则引发错误。

注意:字符串字面量不会强制转换为数字类型。

参数强制转换

BigQuery 支持以下参数强制转换:

输入数据类型 结果数据类型
STRING 参数

如果参数值无法成功强制转换为目标类型,则引发错误。

其他转换函数

BigQuery 还提供以下转换函数:

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面