表达式、函数和运算符

本页面介绍 Cloud Spanner SQL 表达式,包括函数和运算符。

函数调用规则

除非函数说明中另有明确规定,否则以下规则适用于所有函数:

  • 对于接受数字类型的函数,如果一个操作数是浮点型,而另一个操作数是数字型,则系统会先将这两个操作数转换为 FLOAT64,然后再对该函数进行求值。
  • 如果操作数是 NULL,则结果为 NULL,IS 运算符除外。
  • 对于区分时区的函数(如函数说明中所示),则在未指定时区的情况下使用默认时区 America/Los_Angeles。

SAFE. 前缀

语法

SAFE.function_name()

说明

如果一个标量函数以 SAFE. 前缀开头,该函数会返回 NULL 而非错误。SAFE. 前缀仅会防止带有前缀的函数本身返回错误,而不能防止在计算参数表达式时出现的错误。SAFE. 前缀仅会防止因函数输入值所导致的错误,例如“值超出范围”错误,但内部错误或系统错误等其他错误仍可能会发生。如果函数未返回错误,SAFE. 对输出无任何影响。如果函数决不会返回错误,像 RAND 函数就是如此,那么 SAFE. 也没有任何影响。

+=运算符不支持 SAFE. 前缀。为防止除法运算出错,请使用 SAFE_DIVIDE。某些运算符(例如 INARRAYUNNEST)与函数类似,但不支持 SAFE. 前缀。CASTEXTRACT 函数也不支持 SAFE. 前缀。为防止类型转换出错,请使用 SAFE_CAST

示例

在以下示例中,SUBSTR 函数的第一处使用正常应该返回错误,因为该函数不支持长度参数为负值。但是,由于该函数带有 SAFE. 前缀,因此它返回了 NULLSUBSTR 函数的第二处使用提供了预期输出,也就是说,SAFE. 前缀未产生任何影响。

SELECT SAFE.SUBSTR('foo', 0, -2) AS safe_output UNION ALL
SELECT SAFE.SUBSTR('bar', 0, 2) AS safe_output;

+-------------+
| safe_output |
+-------------+
| NULL        |
| ba          |
+-------------+

转换规则

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

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

下表总结了 Cloud Spanner SQL 数据类型所有可能的 CAST 和强制转换。“强制转换目标类型”适用于指定数据类型(例如列)的所有表达式

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

类型转换

语法:

CAST(expr AS typename)

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

示例:

CAST(x=1 AS STRING)

如果 x1,则此表达式的结果为 "true";如果 x 为任何其他非 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 浮点数将向远离 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;如果 x"false",则返回 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 时,如果 Cloud Spanner SQL 无法执行类型转换,则查询可能会失败。例如,以下查询会引发错误:

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       |
+-----------+------------+

转换日期类型

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

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

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

转换时间戳类型

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

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

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

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

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

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

Cloud Spanner SQL 支持在日期与时间戳类型之间转换,如转换规则表中所示。

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

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

Coercion 转换

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

其他转换函数

Cloud Spanner SQL 还提供以下转换函数:

聚合函数

聚合函数是将组中的行汇总为单个值的函数。例如,COUNTMINMAX 都是聚合函数。

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

GROUP BY 子句一起使用时,汇总的组通常至少具有一行。当关联的 SELECT 没有 GROUP BY 子句时,或者当某些聚合函数修饰符从要汇总的组中过滤行时,聚合函数可能需要汇总空组。在这种情况下,COUNTCOUNTIF 函数返回 0,而所有其他聚合函数均返回 NULL

以下部分介绍了 Cloud Spanner SQL 支持的聚合函数。

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

说明

对从组中选择的某一行返回 expression。选择哪一行不是确定性的,也不是随机的。如果输入未生成任何行,则返回 NULL。当组中的所有行的 NULLexpression 时,返回 NULL

ANY_VALUE 的行为相当于指定了 IGNORE NULLS;系统不会考虑也不会选择 expressionNULL 的行。

支持的参数类型

任意

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

与输入数据类型相匹配。

示例

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

说明

返回一个由 expression 值组成的 ARRAY。

支持的参数类型

除 ARRAY 以外的所有数据类型。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. IGNORE NULLSRESPECT NULLS:如果指定了 IGNORE NULLS,则结果中不会包含 NULL 值。如果指定了 RESPECT NULLS 或者未指定任何一个子句,则结果中会包含 NULL 值。
  3. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回的数据类型

ARRAY

如果不存在输入行,则此函数返回 NULL

示例

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

说明

连接类型为 ARRAY 的 expression 中的元素,并返回一个数组作为结果。此函数会忽略 NULL 输入数组,但会沿用非 NULL 输入数组中的 NULL 元素。

支持的参数类型

ARRAY

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回的数据类型

ARRAY

如果不存在任何输入行或 expression 针对所有行的计算结果均为 NULL,则返回 NULL

示例

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回非 NULL 输入值的平均值;如果输入包含 NaN,则返回 NaN

支持的参数类型

任何数字输入类型,例如 INT64。请注意,浮点输入类型的返回结果是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

输入INT64NUMERICFLOAT64
输出FLOAT64NUMERICFLOAT64

示例

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

expression 执行按位与运算并返回结果。

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

INT64

示例

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

expression 执行按位或运算并返回结果。

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

INT64

示例

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

expression 执行按位异或运算并返回结果。

支持的参数类型

  • INT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回的数据类型

INT64

示例

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

  1. 返回输入中的行数。
  2. 返回 expression 的计算结果为 NULL 以外任何值的行数。

支持的参数类型

expression 可以是任何数据类型。如果存在 DISTINCT,则 expression 只能是可分组的数据类型。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

INT64

示例

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回 expressionTRUE 值计数。如果不存在任何输入行,或所有行的 expression 计算结果均为 FALSENULL,则返回 0

支持的参数类型

BOOL

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

INT64

示例

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

说明

返回对所有非 NULL 表达式执行逻辑与运算的结果。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL,则返回 NULL

支持的参数类型

BOOL

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

BOOL

示例

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

说明

返回对所有非 NULL 表达式执行逻辑或运算的结果。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL,则返回 NULL

支持的参数类型

BOOL

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

BOOL

示例

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

说明

返回非 NULL 表达式的最大值。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL,则返回 NULL。如果输入包含 NaN,则返回 NaN

支持的参数类型

任意数据类型(ARRAY STRUCT 除外)

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

与用作输入值的数据类型相同。

示例

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

说明

返回非 NULL 表达式的最小值。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL,则返回 NULL。如果输入包含 NaN,则返回 NaN

支持的参数类型

任意数据类型(ARRAY STRUCT 除外)

可选子句

HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

与用作输入值的数据类型相同。

示例

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

说明

返回通过连接非 NULL 值获得的值(STRING 或 BYTES)。

如果指定了 delimiter,则连接的值由此分隔符进行分隔;否则使用英文逗号作为分隔符。

支持的参数类型

STRING BYTES

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

输出元素顺序

输出中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

返回数据类型

STRING BYTES

示例

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回非 NULL 值之和。

如果表达式是浮点值,则总和是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。

支持的参数类型

任何支持的数字数据类型。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

输入INT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

特殊情况:

如果输入仅包含 NULL,则返回 NULL

如果输入不包含行,则返回 NULL

如果输入包含 Inf,则返回 Inf

如果输入包含 -Inf,则返回 -Inf

如果输入包含 NaN,则返回 NaN

如果输入包含 Inf-Inf 的组合,则返回 NaN

示例

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

常用子句

HAVING MAX 和 HAVING MIN 子句

大多数聚合函数支持两个名为 HAVING MAXHAVING MIN 的可选子句,这些子句将函数聚合的行限制为在某个特定列中具有最大值或最小值的行。语法通常如下所示:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX:将函数聚合的行限制为 expression2 的值等于该组中 expression2 最大值的行。最大值等于 MAX(expression2) 的结果。
  • HAVING MIN:将函数聚合的行限制为 expression2 的值等于该组中 expression2 最小值的行。最小值等于 MIN(expression2) 的结果。

这些子句在计算最大值或最小值时会忽略 NULL 值,除非所有行的 expression2 求值结果均为 NULL

这些子句不支持以下数据类型:ARRAY STRUCT

示例

在此示例中,返回最近一年(2001 年)的平均降雨量。

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

首先,查询获取 year 列中具有最大值的行。这样的行有两个:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

最后,查询会对 inches 列中的值(9 和 1)求平均值:

+---------+
| average |
+---------+
| 5       |
+---------+

统计聚合函数

Cloud Spanner SQL 支持以下统计聚合函数。

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回值的样本(无偏差)标准差。返回结果介于 0+Inf 之间。

此函数会忽略任何 NULL 输入。如果非 NULL 输入少于两个,则此函数返回 NULL。

支持的输入类型

FLOAT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

FLOAT64

STDDEV

STDDEV([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

STDDEV_SAMP 的别名。

VAR_SAMP

VAR_SAMP([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

返回值的样本(无偏差)方差。返回结果介于 0+Inf 之间。

此函数会忽略任何 NULL 输入。如果非 NULL 输入少于两个,则此函数返回 NULL。

支持的输入类型

FLOAT64

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. HAVING MAXHAVING MIN:通过最大值或最小值限制函数聚合的行。如需了解详情,请参阅 HAVING MAX 和 HAVING MIN 子句

返回数据类型

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression [HAVING {MAX | MIN} expression2])

说明

VAR_SAMP 的别名。

数学函数

所有数学函数都具有以下行为:

  • 如果任一输入参数为 NULL,则返回 NULL
  • 如果任一参数为 NaN,则返回 NaN

ABS

ABS(X)

说明

计算绝对值。如果参数是整数,而输出值无法用同一类型表示,则返回一个错误;仅最大负输入值(不具有正数表示形式)会发生此情况。对 +/-inf 参数返回 +inf。 对 NUMERIC 参数返回 NUMERIC

返回数据类型

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

SIGN

SIGN(X)

说明

分别对负参数、零和正参数返回 -1、0 或 +1。对于浮点参数,此函数不区分正零和负零。对于 NUMERIC 参数,此函数会以 NUMERIC 形式返回 -1、0 或 +1。对 NaN 参数返回 NaN

返回数据类型

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

IS_INF

IS_INF(X)

说明

如果值为正无穷或负无穷,则返回 TRUE

NUMERIC 输入返回 FALSE,因为 NUMERIC 不能为 INF

NULL 输入返回 NULL

IS_NAN

IS_NAN(X)

说明

如果值为 NaN 值,则返回 TRUE

NUMERIC 输入返回 FALSE,因为 NUMERIC 不能为 NAN。

NULL 输入返回 NULL

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

说明

用 X 除以 Y;此函数始终有效。返回 FLOAT64。不同于除法运算符 (/),如果除数为零或溢出,此函数不会生成错误。

特殊情况:

  • 如果结果溢出,则返回 +/-inf
  • 如果 Y= 0 且 X = 0,则返回 NaN
  • 如果 Y = 0 且 X != 0,则返回 +/-inf
  • 如果 X = +/-inf 且 Y = +/-inf,则返回 NaN

下表进一步说明了 IEEE_DIVIDE 的行为。

IEEE_DIVIDE 的特殊情况

下表列出了 IEEE_DIVIDE 的特殊情况。

分子数据类型 (X) 分母数据类型 (Y) 结果值
除 0 外的任何值 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

SQRT

SQRT(X)

说明

计算 X 的平方根。如果 X 小于 0,则生成错误。

如果 X 为 +inf,则返回 +inf

返回数据类型

不支持 NUMERIC 直接作为输入。您必须先将 NUMERIC 明确转换为 FLOAT64。输出将是 FLOAT64

POW

POW(X, Y)

说明

返回 X 的 Y 次幂的值。如果结果下溢且不可表示,则函数返回值 0。如果满足下述任一条件,则返回一个错误:

  • X 是小于 0 的有限值且 Y 是非整数
  • X 是 0 且 Y 是小于 0 的有限值

返回数据类型:返回数据类型由下表中的参数类型决定。

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

POW(X, Y) 的特殊情况

下面是 POW(X, Y) 的特殊情况。

X Y POW(X, Y) 或 POWER(X, Y)
1.0 任何值(包括 NaN 1.0
任何值(包括 NaN 0 1.0
-1.0 +/-inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0
ABS(X) < 1 +inf 0
ABS(X) > 1 +inf +inf
-inf Y < 0 0
-inf Y > 0 如果 Y 为奇整数,则为 -inf;否则为 +inf
+inf Y < 0 0
+inf Y > 0 +inf

POWER

POWER(X, Y)

说明

相当于 POW(X, Y)

EXP

EXP(X)

说明

计算 e 的 X 次幂,也被称为自然指数函数。如果结果下溢,此函数返回 0。如果结果上溢,则生成错误。如果 X 是 +/-inf,则此函数返回 +inf 或 0。

返回数据类型

不支持 NUMERIC 直接作为输入。您必须先将 NUMERIC 输入明确转换为 FLOAT64。输出将是 FLOAT64

LN

LN(X)

说明

计算 X 的自然对数。如果 X 小于或等于 0,则生成错误。如果 X 是 +inf,则此函数返回 +inf

返回数据类型

不支持 NUMERIC 直接作为输入。您必须先将 NUMERIC 输入明确转换为 FLOAT64。输出将是 FLOAT64

LOG

LOG(X [, Y])

说明

如果只存在 X,则 LOG 相当于 LN。如果还存在 Y,则 LOG 计算以 Y 为底数的 X 的对数。在以下情况下将生成错误:

  • X 小于或等于 0
  • Y 等于 1.0
  • Y 小于或等于 0。

返回数据类型

不支持 NUMERIC 直接作为输入。您必须先将 NUMERIC 输入明确转换为 FLOAT64。输出将是 FLOAT64

下表进一步说明了 LOG(X, Y) 的行为。

LOG(X, Y) 的特殊情况

X Y LOG(X, Y)
-inf 任意值 NaN
任意值 +inf NaN
+inf 0.0 Y < 1.0 -inf
+inf Y > 1.0 +inf

LOG10

LOG10(X)

说明

LOG 类似,但计算的是以 10 为底数的对数。

返回数据类型

不支持 NUMERIC 直接作为输入。您必须先将 NUMERIC 输入明确转换为 FLOAT64。输出将是 FLOAT64

GREATEST

GREATEST(X1,...,XN)

说明

如果任一输入为 NULL,则返回 NULL。如果任一输入为 NaN,则返回 NaN。否则根据比较得出更大值,返回 X1,...,XN 中的最大值。

返回数据类型

输入值的数据类型。

LEAST

LEAST(X1,...,XN)

说明

如果任一输入为 NULL,则返回 NULL。如果任一输入为 NaN,则返回 NaN。否则根据比较得出更小值,返回 X1,...,XN 中的最小值。

返回数据类型

输入值的数据类型。

DIV

DIV(X, Y)

说明

返回 X 被 Y 整除的结果。如果除数为 0,则返回一个错误。除数为 -1 时可能溢出。如果两个输入都为 NUMERIC 且结果溢出,则会返回 numeric overflow 错误。

返回数据类型

返回数据类型由下表中的参数类型决定。

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

说明

等同于除法运算符 (X / Y),但如果发生错误(例如除以 0 这个错误),则返回 NULL

返回数据类型

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

说明

等同于乘法运算符 (*),但如果发生溢出,则返回 NULL

返回数据类型

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

说明

等同于一元取反运算符 (-),但如果发生溢出,则返回 NULL

返回数据类型

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

SAFE_ADD

SAFE_ADD(X, Y)

说明

等同于加法运算符 (+),但如果发生溢出,则返回 NULL

返回数据类型

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

说明

返回 X 减去 Y 的结果。等同于减法运算符 (-),但如果发生溢出,则返回 NULL

返回数据类型

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

MOD

MOD(X, Y)

说明

取模函数:返回 X 除以 Y 的余数。返回值的符号与 X 相同。如果 Y 为 0,则生成一个错误。

返回数据类型

返回数据类型由下表中的参数类型决定。

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

ROUND

ROUND(X [, N])

说明

如果只存在 X,则 ROUND 将 X 向最近的整数舍入。如果存在 N,ROUND 将 X 舍入到小数点后 N 个小数位。如果 N 为负数,则 ROUND 将舍掉小数点取整。中间数向远离 0 的方向舍入。如果发生溢出,则生成错误。

如果 X 是 NUMERIC,则输出为 NUMERIC

TRUNC

TRUNC(X [, N])

说明

如果只存在 X,则 TRUNC 将 X 向最近的整数舍入,且此整数的绝对值不超过 X 的绝对值。如果还存在 N,则 TRUNC 的行为类似于 ROUND(X, N),但始终向 0 舍入且永不溢出。

如果 X 是 NUMERIC,则输出为 NUMERIC

CEIL

CEIL(X)

说明

返回不小于 X 的最小整数值(FLOAT64 类型)。

如果 X 是 NUMERIC,则输出为 NUMERIC

CEILING

CEILING(X)

说明

相当于 CEIL(X)

FLOOR

FLOOR(X)

说明

返回不大于 X 的最大整数值(FLOAT64 类型)。

如果 X 是 NUMERIC,则输出为 NUMERIC

舍入函数行为示例

Cloud Spanner SQL 舍入函数的行为示例:

输入“X” ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2.0 2.0 2.0 2.0 2.0
2.3 2.0 2.0 3.0 2.0
2.8 3.0 2.0 3.0 2.0
2.5 3.0 2.0 3.0 2.0
-2.3 -2.0 -2.0 -2.0 -3.0
-2.8 -3.0 -2.0 -2.0 -3.0
-2.5 -3.0 -2.0 -2.0 -3.0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

说明

计算 X 的余弦,其中 X 以弧度指定。始终有效。

COSH

COSH(X)

说明

计算 X 的双曲余弦,其中 X 以弧度指定。如果发生溢出,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

ACOS

ACOS(X)

说明

计算 X 的反余弦的主值。返回值在 [0,π] 范围内。如果 X 是超出 [-1, 1] 范围的值,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

ACOSH

ACOSH(X)

说明

计算 X 的反双曲余弦。如果 X 是小于 1 的值,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

SIN

SIN(X)

说明

计算 X 的正弦,其中 X 以弧度指定。始终有效。

SINH

SINH(X)

说明

计算 X 的双曲正弦,其中 X 以弧度指定。如果发生溢出,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

ASIN

ASIN(X)

说明

计算 X 的反正弦的主值。返回值在 [-π/2,π/2] 范围内。如果 X 是超出 [-1, 1] 范围的值,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

ASINH

ASINH(X)

说明

计算 X 的反双曲正弦。不会失效。

如果 X 是 NUMERIC,则输出为 FLOAT64

TAN

TAN(X)

说明

计算 X 的正切,其中 X 以弧度指定。如果发生溢出,则生成错误。

TANH

TANH(X)

说明

计算 X 的双曲正切,其中 X 以弧度指定。不会失效。

如果 X 是 NUMERIC,则输出为 FLOAT64

ATAN

ATAN(X)

说明

计算 X 的反正切的主值。返回值在 [-π/2,π/2] 范围内。不会失效。

如果 X 是 NUMERIC,则输出为 FLOAT64

ATANH

ATANH(X)

说明

计算 X 的反双曲正切。如果 X 是超出 [-1, 1] 范围的值,则生成错误。

如果 X 是 NUMERIC,则输出为 FLOAT64

ATAN2

ATAN2(Y, X)

说明

通过使用两个参数的符号确定象限,计算 Y/X 的反正切的主值。返回值在 [-π,π] 范围内。

如果 X 是 NUMERIC,则输出为 FLOAT64

下表进一步说明了此函数的行为。

ATAN2() 的特殊情况

Y X ATAN2(Y, X)
NaN 任意值 NaN
任意值 NaN NaN
0 0 0、π 或 -π(取决于 X 和 Y 的符号)
有限值 -inf π 或 -π(取决于 Y 的符号)
有限值 +inf 0
+/-inf 有限值 π/2 或 π/2(取决于 Y 的符号)
+/-inf -inf ¾π 或 -¾π(取决于 Y 的符号)
+/-inf +inf π/4 或 -π/4(取决于 Y 的符号)

三角函数和双曲线舍入函数的特殊情况

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) TAN(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1.0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1.0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

哈希函数

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

说明

使用开源 FarmHash 库中的 Fingerprint64 函数计算 STRINGBYTES 输入的指纹。此函数针对特定输入的输出从不会改变。

返回类型

INT64

示例

WITH example AS (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
)
SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM example;
+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

SHA1

SHA1(input)

说明

使用 SHA-1 算法计算输入的哈希值。输入可以是 STRINGBYTES。字符串版本会将输入作为字节数组处理。

此函数返回 20 个字节。

返回类型

BYTES

示例

SELECT SHA1("Hello World") as sha1;

-- Note that the result of SHA1 is of type BYTES, displayed as a base64-encoded string.
+------------------------------+
| sha1                         |
+------------------------------+
| Ck1VqNd45QIvq3AZd8XYQLvEhtA= |
+------------------------------+

SHA256

SHA256(input)

说明

使用 SHA-256 算法计算输入的哈希值。输入可以是 STRINGBYTES。字符串版本会将输入作为字节数组处理。

此函数返回 32 个字节。

返回类型

BYTES

示例

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

说明

使用 SHA-512 算法计算输入的哈希值。输入可以是 STRINGBYTES。字符串版本会将输入作为字节数组处理。

此函数返回 64 个字节。

返回类型

BYTES

示例

SELECT SHA512("Hello World") as sha512;

字符串函数

这些字符串函数作用于两种不同的值:STRINGBYTES 数据类型。STRING 值必须采用正确的 UTF-8 格式。

返回位置值的函数(如 STRPOS)会将这些位置编码为 INT64 类型。值 1 表示第一个字符(或字节),2 表示第二个字符(或字节),以此类推。值 0 表示无效索引。处理 STRING 类型时,返回的位置表示字符位置。

按字节逐一比较所有字符串,而不考虑 Unicode 规范相等性。

BYTE_LENGTH

BYTE_LENGTH(value)

说明

无论值的类型是 STRING 还是 BYTES,该函数都会返回 STRINGBYTES 值的长度(以 BYTES 为单位)。

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

说明

返回 STRING 的长度(以字符为单位)。

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

说明

相当于 CHAR_LENGTH

返回类型

INT64

示例

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

说明

接受扩展 ASCII 码位的数组(由 INT64 类型值组成的 ARRAY),并返回 BYTES

如需从 BYTES 转换为码位数组,请参阅 TO_CODE_POINTS

返回类型

BYTES

示例

以下是使用 CODE_POINTS_TO_BYTES 的一个基本示例。

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'AbCd' is the result.
+----------+
| bytes    |
+----------+
| QWJDZA== |
+----------+

以下示例使用回转 13 位 (ROT13) 算法来编码字符串。

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' AND b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' AND b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

-- Note that the result of CODE_POINTS_TO_BYTES is of type BYTES, displayed as a base64-encoded string.
-- In BYTES format, b'Grfg Fgevat!' is the result.
+------------------+
| encoded_string   |
+------------------+
| R3JmZyBGZ2V2YXQh |
+------------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

说明

接受 Unicode 码位的数组(由 INT64 类型值组成的 ARRAY),并返回一个 STRING。如果码位为 0,则不会在 STRING 中为其返回字符。

如需从字符串转换为码位数组,请参阅 TO_CODE_POINTS

返回类型

STRING

示例

以下是使用 CODE_POINTS_TO_STRING 的基本示例。

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+
SELECT CODE_POINTS_TO_STRING([97, 0, 0xF9B5]) AS string;

+--------+
| string |
+--------+
| a例    |
+--------+
SELECT CODE_POINTS_TO_STRING([65, 255, NULL, 1024]) AS string;

+--------+
| string |
+--------+
| NULL   |
+--------+

以下示例会计算一组单词中字母的出现频率。

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

说明

将一个或多个 STRINGBYTE 值连接成一个结果。

如果任何输入参数为 NULL,则该函数返回 NULL

返回类型

STRINGBYTES

示例

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

说明

获取两个 STRINGBYTES 值。如果第二个值是第一个值的后缀,则返回 TRUE

返回类型

BOOL

示例

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

Cloud Spanner SQL 支持用于设置字符串格式的 FORMAT() 函数。此函数类似于 C 语言中的 printf 函数。它会根据一个包含零个或多个格式说明符的格式字符串,以及一个包含与这些格式说明符匹配的额外参数的可变长度列表,生成一个 STRING。以下是一些示例:

说明 语句 结果
简单整数 FORMAT("%d", 10) 10
在左侧填补空格的整数 FORMAT("|%10d|", 11) |           11|
在左侧填补零的整数 FORMAT("+%010d+", 12) +0000000012+
用英文逗号分隔的整数 FORMAT("%'d", 123456789) 123,456,789
STRING FORMAT("-%s-", 'abcd efg') -abcd efg-
FLOAT64 FORMAT("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATE FORMAT("%t", date "2015-09-01") 2015-09-01
TIMESTAMP FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

FORMAT() 函数不会为所有类型和值提供完全可自定义的格式,也不支持设置对语言区域敏感的格式。

如果某种类型需要使用自定义格式设置,则您必须首先使用类型特定的格式函数(例如 FORMAT_DATE()FORMAT_TIMESTAMP())对其进行格式设置。例如:

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

返回

date: January 02, 2015!

语法

FORMAT() 语法会接受一个格式字符串和一个可变长度的参数列表,并生成一个 STRING 结果:

FORMAT(format_string, ...)

format_string 表达式可以包含零个或多个格式说明符。每个格式说明符均由 % 符号引入,且必须映射到一个或多个其余参数。除非存在 * 说明符,否则在大多数情况下,这都是一对一映射。例如,%.*i 映射到两个参数:一个长度参数和一个带符号整数参数。如果与格式说明符相关的参数数量与实际参数数量不同,则会出现错误。

支持的格式说明符

FORMAT() 函数格式说明符遵循以下原型:

%[flags][width][.precision]specifier

下表中标识了支持的格式说明符。与 printf() 函数的不同之处会用斜体表示。

说明符 说明 示例 类型
di 十进制整数 392 INT64
o 八进制 610
INT64*
x 十六进制整数 7fa
INT64*
X 十六进制整数(大写) 7FA
INT64*
f 十进制记数法,[-](整数部分).(小数部分)表示有限值,小写字母表示非有限值 392.650000
inf
nan
NUMERIC
FLOAT64
F 十进制记数法,[-](整数部分).(小数部分)表示有限值,大写字母表示非有限值 392.650000
INF
NAN
NUMERIC
FLOAT64
e 科学记数法(尾数/指数)(小写) 3.926500e+02
inf
nan
NUMERIC
FLOAT64
E 科学记数法(尾数/指数)(大写) 3.926500E+02
INF
NAN
NUMERIC
FLOAT64
g 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。小写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265e+07
inf
nan
NUMERIC
FLOAT64
G 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。大写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265E+07
INF
NAN
NUMERIC
FLOAT64
s 字符串 sample STRING
t 返回表示值的可打印字符串。通常看起来类似于将参数类型转换为 STRING。 请参阅 %t 和 %T 行为 sample
2014‑01‑01
<任意>
T 生成一个字符串,该字符串是一个有效的 Cloud Spanner SQL 常量,其类型与值类型类似(可能宽度更大,也可能是字符串)。 请参阅 %t 和 %T 行为 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<任意>
% '%%' 会生成一个 '%' % 不适用

*如果使用了负值,则说明符 %o%x%X 会抛出错误。

格式说明符可以选择性地在说明符原型中包含上面所述的子说明符。

这些子说明符必须符合以下规范。

标记
标记 说明
- 在指定字段宽度内左对齐;默认设置为右对齐(请参阅宽度子说明符)
+ 强制在结果前面加上加号或减号(+-),即使是正数也一样。默认情况下,只有负数才会带 - 符号前缀
<空格> 如果不会写入任何符号,则在值前插入一个空格
#
  • 对于“%o”、“%x”和“%X”,此标志表示,对于 0 以外的值,在值前分别加上 0、0x 或 0X 前缀。
  • 对于“%f”、“%F”、“%e”和“%E”,此标志表示即使没有小数部分,也要添加小数点,除非该值为非有限值。
  • 对于“%g”和“%G”,此标志表示即使没有小数部分,也要添加小数点(除非该值为非有限值),并且绝不移除小数点后的尾随零。
0 在指定填充时,在数字左侧填充零 (0) 而非空格(请参阅宽度子说明符)
'

使用适当的分组字符设置整数的格式。 例如:

  • FORMAT("%'d", 12345678) 返回 12,345,678
  • FORMAT("%'x", 12345678) 返回 bc:614e
  • FORMAT("%'o", 55555) 返回 15,4403
  • 此标志仅与十进制、十六进制和八进制值相关。

标记可以按照任意顺序指定。标记重复不属于错误。在标记与某些元素类型不相关时,标记会被忽略。

宽度
宽度 说明
<数字> 需要打印的最小字符数。如果需要打印的值比这个数字短,结果将用空格填充。即便结果较大,该值也不会被截断
* 宽度并非在格式字符串中指定,而是作为一个额外的整数值参数指定,置于必须格式化的参数之前
精度
精度 说明
.<数字>
  • 对于整数说明符(“%d”、“%i”、“%o”、“%u”、“%x”、“%X”),精度指定要写入的最小位数。如果要写入的值比这个数字短,则用尾随零填充结果。 即便结果较长,该值也不会被截断。精度为 0 则表示不会为值 0 写入字符。
  • 对于说明符“%a”、“%A”、“%e”、“%E”、“%f”、“%F”,这是要在小数点后输出的位数。默认值为 6。
  • 对于说明符“%g”和“%G”,这是在移除小数点后的尾随零之前要输出的有效位数。默认值为 6。
.* 精度并非在格式字符串中指定,而是作为一个额外的整数值参数,在需要设置格式的参数前面指定

%g 和 %G 行为

%g%G 格式说明符采用十进制记数法(如 %f%F 说明符)或科学记数法(如 %e%E 说明符),具体取决于输入值的指数和指定的精度

p 代表指定的精度(默认值为 6;如果指定的精度小于 1,则默认为 1)。首先将输入值转换为精度为 (p - 1) 的科学记数法。如果得到的指数部分 x 小于 -4 或不小于 p,则使用精度为 (p - 1) 的科学记数法;否则,使用精度为 (p - 1 - x) 的十进制记数法。

除非存在 # 标志,否则移除小数点后的尾随零;如果小数点后面没有数字,则也会将其移除。

%t 和 %T 行为

您需要为所有类型定义 %t%T 格式说明符。宽度精度标志的作用与其在 %s 中的作用相同:宽度是最小宽度,STRING 将填充到此宽度,精度是要显示的最大内容宽度,STRING 将截断到该宽度,之后再填充到合适的宽度。

%t 说明符始终是值的可读形式。

%T 说明符始终是类似类型的有效 SQL 字面量,例如宽度较大的数字类型。除了非有限浮点值的特殊情况之外,该字面量不会包含类型转换或类型名称。

STRING 的格式如下:

类型 %t %T
任意类型的 NULL NULL NULL
INT64
123 123
NUMERIC 123.0(始终带有 .0) NUMERIC "123.0"
FLOAT64 123.0(始终带有 .0)
123e+10
inf
-inf
NaN
123.0(始终带有 .0)
123e+10
CAST("inf" AS <type>)
CAST("-inf" AS <type>)
CAST("nan" AS <type>)
STRING 不带引号的字符串值 带引号的字符串字面量
BYTES 不带英文引号的转义字节
例如 abc\x01\x02
带英文引号的字节字面量
例如 b"abc\x01\x02"
DATE 2011-02-03 DATE "2011-02-03"
TIMESTAMP 2011-02-03 04:05:06+00 TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY [value, value, ...]
其中值的格式为 %t
[value, value, ...]
其中值的格式为 %T

错误条件

如果格式说明符无效,或者与相关参数类型不兼容,或者提供的数字或参数不正确,则会产生错误。例如,以下 <format_string> 表达式无效:

FORMAT('%s', 1)
FORMAT('%')

NULL 参数处理

NULL 格式字符串是一个 NULL 输出 STRING。在这种情况下中,其他任何参数都会被忽略。

如果存在 NULL 参数,函数通常会生成 NULL 值。例如,FORMAT('%i', NULL_expression) 会生成 NULL STRING 作为输出。

但也有一些例外情况:如果格式说明符是 %t 或 %T(两者均生成有效匹配 CAST 和字面量值语义的 STRING),则 NULL 值会在结果 STRING 中生成“NULL”(不带引号)。例如,以下函数:

FORMAT('00-%t-00', NULL_expression);

返回

00-NULL-00

其他语义规则

FLOAT64 值可以是 +/-infNaN。当参数具有其中某个值时,在适当情况下,格式说明符 %f%F%e%E%g%G%t 的结果会是 inf-infnan(或者相同的大写形式)。这与 Cloud Spanner SQL 将这些值的类型转换为 STRING 的方式一致。对于 %T,Cloud Spanner SQL 会为没有使用非字符串字面量表示形式的 FLOAT64 值返回带英文引号的字符串。

FROM_BASE32

FROM_BASE32(string_expr)

说明

将 base32 编码的输入 string_expr 转换为 BYTES 格式。如需将 BYTES 转换为 base32 编码的 STRING,请使用 TO_BASE32

返回类型

BYTES

示例

SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;

-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/  |
+-----------+

FROM_BASE64

FROM_BASE64(string_expr)

说明

将 base64 编码的输入 string_expr 转换为 BYTES 格式。如需将 BYTES 转换为 base64 编码的 STRING,请使用 TO_BASE64

返回类型

BYTES

示例

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w==  |
+-----------+

FROM_HEX

FROM_HEX(string)

说明

将十六进制编码的 STRING 转换为 BYTES 格式。如果输入 STRING 包含 (0..9, A..F, a..f) 范围以外的字符,则该函数会返回错误。字符不区分大小写。如果输入 STRING 包含奇数个字符,则该函数的作用就像是输入中有一个额外的前导 0。如需将 BYTES 转换为十六进制编码的 STRING,请使用 TO_HEX

返回类型

BYTES

示例

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

说明

返回 STRINGBYTES 值的长度。STRING 参数的返回值以字符为单位,BYTES 参数的返回值以字节为单位。

返回类型

INT64

示例


WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

说明

返回由附带前缀 patternoriginal_value 组成的 STRINGBYTES 值。return_lengthINT64 类型,指定了返回值的长度。如果 original_valueBYTES 类型,return_length 就是字节数。如果 original_valueSTRING 类型,return_length 就是字符数。

pattern 的默认值是空格。

original_valuepattern 必须采用相同的数据类型。

如果 return_length 小于或等于 original_value 长度,则此函数会返回 original_value 值,并截断到 return_length 值。例如,LPAD("hello world", 7); 会返回 "hello w"

如果 original_valuereturn_lengthpatternNULL,则此函数会返回 NULL

在以下情况下,此函数会返回错误:

  • return_length 为负数
  • pattern 为空

返回类型

STRINGBYTES

示例

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | LPAD     |
|------|-----|----------|
| abc  | 5   | "  abc"  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "  例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | LPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "defdeabc"   |
| abc  | 5   | -       | "--abc"      |
| 例子  | 5   | 中文    | "中文中例子"   |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | LPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"  abc"         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | LPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"defdeabc"             |
| b"abc"          | 5   | b"-"    | b"--abc"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+

LOWER

LOWER(value)

说明

对于 STRING 参数,此函数会返回所有字母字符均为小写的原始字符串。它根据 Unicode 字符数据库进行小写与大写的映射,而不考虑特定语言的映射。

对于 BYTES 参数,此函数会将其视为 ASCII 文本,并将所有大于 127 的字节保留原样。

返回类型

STRINGBYTES

示例


WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

说明

TRIM 相同,但仅移除前导字符。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)
SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

说明

如果 value 与正则表达式 regexp 部分匹配,则此函数返回 TRUE

如果 regexp 参数无效,此函数会返回错误。

您可使用 ^(文本开头)和 $(文本结尾)搜索完整匹配。鉴于正则表达式运算符的优先顺序,最好为 ^$ 之间的内容加上英文括号。

返回类型

BOOL

示例

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

说明

返回 value 中与正则表达式 regexp 匹配的第一个子字符串。如果不存在匹配项,则返回 NULL

如果正则表达式包含捕获组,则此函数返回与该捕获组匹配的子字符串。如果表达式不包含捕获组,则函数返回整个匹配的子字符串。

出现以下情况时返回一个错误:

  • 正则表达式无效
  • 正则表达式具有多个捕获组

返回类型

STRINGBYTES

示例

WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+
WITH email_addresses AS
  (SELECT "foo@example.com" as email
  UNION ALL
  SELECT "bar@example.org" as email
  UNION ALL
  SELECT "baz@example.net" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

说明

返回一个数组,该数组由 value 中与正则表达式 regexp 匹配的所有子字符串组成。

REGEXP_EXTRACT_ALL 函数仅返回非重叠的匹配项。例如,使用此函数从 banana 中提取 ana 时,系统只会返回一个子字符串,而不是两个。

返回类型

STRINGBYTES 组成的 ARRAY

示例

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

说明

返回一个 STRING,其中将 value 中所有与正则表达式 regexp 匹配的子字符串替换为 replacement

您可以在 replacement 参数中使用通过反斜杠转义的数字(\1 至 \9),借此在 regexp 模式中插入与带有英文括号的对应组匹配的文本。“\0”可用于引用整个匹配文本。

REGEXP_REPLACE 函数只会替换非重叠的匹配项。例如,替换 banana 中的 ana 时,此函数只会替换一处,而不是两处。

如果 regexp 参数不是有效的正则表达式,则此函数会返回错误。

返回类型

STRINGBYTES

示例

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

说明

original_value 中出现的 from_value 全部替换为 to_value。如果 from_value 为空,则不执行替换。

返回类型

STRINGBYTES

示例

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

说明

返回由重复的 original_value 组成的 STRINGBYTES 值。repetitions 参数指定了 original_value 的重复次数。如果 original_valuerepetitionsNULL,则返回 NULL

如果 repetitions 值为负数,该函数会返回错误。

返回类型

STRINGBYTES

示例

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);

+------+------+-----------+
| t    | n    | REPEAT    |
|------|------|-----------|
| abc  | 3    | abcabcabc |
| 例子 | 2    | 例子例子  |
| abc  | NULL | NULL      |
| NULL | 3    | NULL      |
+------+------+-----------+

REVERSE

REVERSE(value)

说明

返回输入 STRINGBYTES 的反转值。

返回类型

STRINGBYTES

示例

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

说明

返回由附带前缀 patternoriginal_value 组成的 STRINGBYTES 值。return_length 参数是 INT64 类型,指定了返回值的长度。如果 original_valueBYTESreturn_length 就是字节数。如果 original_valueSTRINGreturn_length 就是字符数。

pattern 的默认值是空格。

original_valuepattern 必须采用相同的数据类型。

如果 return_length 小于或等于 original_value 长度,则此函数会返回 original_value 值,并截断到 return_length 值。例如,RPAD("hello world", 7); 会返回 "hello w"

如果 original_valuereturn_lengthpatternNULL,则此函数会返回 NULL

在以下情况下,此函数会返回错误:

  • return_length 为负数
  • pattern 为空

返回类型

STRINGBYTES

示例

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

+------+-----+----------+
| t    | len | RPAD     |
|------|-----|----------|
| abc  | 5   | "abc  "  |
| abc  | 2   | "ab"     |
| 例子  | 4   | "例子  " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

+------+-----+---------+--------------+
| t    | len | pattern | RPAD         |
|------|-----|---------|--------------|
| abc  | 8   | def     | "abcdefde"   |
| abc  | 5   | -       | "abc--"      |
| 例子  | 5   | 中文     | "例子中文中"  |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

+-----------------+-----+------------------+
| t               | len | RPAD             |
|-----------------|-----|------------------|
| b"abc"          | 5   | b"abc  "         |
| b"abc"          | 2   | b"ab"            |
| b"\xab\xcd\xef" | 4   | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

+-----------------+-----+---------+-------------------------+
| t               | len | pattern | RPAD                    |
|-----------------|-----|---------|-------------------------|
| b"abc"          | 8   | b"def"  | b"abcdefde"             |
| b"abc"          | 5   | b"-"    | b"abc--"                |
| b"\xab\xcd\xef" | 5   | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+

RTRIM

RTRIM(value1[, value2])

说明

TRIM 相同,但仅移除尾随字符。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+
WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

说明

BYTES 序列转换为 STRING。任何无效的 UTF-8 字符都会替换为 Unicode 替换字符 U+FFFD

返回类型

STRING

示例

以下语句会返回 Unicode 替换字符 �。

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

说明

使用 delimiter 参数拆分 value

对于 STRING,默认分隔符是英文逗号 ,

对于 BYTES,您必须指定一个分隔符。

对于 STRING 值,拆分空的分隔符会生成一个 UTF-8 字符数组,对于 BYTES 值则会生成一个 BYTES 数组。

拆分空 STRING 会返回包含一个空 STRINGARRAY

返回类型

类型为 STRINGARRAY,或类型为 BYTESARRAY

示例

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

说明

获取两个 STRINGBYTES 值。如果第二个值是第一个值的前缀,则返回 TRUE

返回类型

BOOL

示例

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

说明

返回 string 中第一次出现 substring 处的索引(从 1 开始)。如果未找到 substring,则此函数返回 0

返回类型

INT64

示例

WITH email_addresses AS
  (SELECT
    "foo@example.com" AS email_address
  UNION ALL
  SELECT
    "foobar@example.com" AS email_address
  UNION ALL
  SELECT
    "foobarbaz@example.com" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

说明

返回所提供 STRINGBYTES 值的子字符串。position 参数是一个整数,指定了子字符串的开始位置;position = 1 时,表示第一个字符或字节。length 参数是 STRING 参数的字符数上限,或 BYTES 参数的字节数上限。

如果 position 为负数,则此函数会从 value 末尾开始计数,其中 -1 表示最后一个字符。

如果 position 表示不在 STRING 左端的位置(position = 0 或 position < -LENGTH(value)),则此函数会从 position = 1 处开始计数。如果 length 超出了 value 的长度,则函数返回的字符数会少于 length

如果 length 小于 0,则此函数会返回错误。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+
WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE32

TO_BASE32(bytes_expr)

说明

BYTES 序列转换为 base32 编码的 STRING。如需将 base32 编码的 STRING 转换为 BYTES,请使用 FROM_BASE32

返回类型

STRING

示例

SELECT TO_BASE32(b'abcde\xFF') AS base32_string;

+------------------+
| base32_string    |
+------------------+
| MFRGGZDF74====== |
+------------------+

TO_BASE64

TO_BASE64(bytes_expr)

说明

BYTES 序列转换为 base64 编码的 STRING。如需将 base64 编码的 STRING 转换为 BYTES,请使用 FROM_BASE64

返回类型

STRING

示例

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

说明

接受一个并返回 INT64 类型数组。

  • 如果 valueSTRING,则所返回数组中的每个元素都代表一个码位。每个码位都位于 [0, 0xD7FF] 和 [0xE000, 0x10FFFF] 范围内。
  • 如果 valueBYTES,则数组中的每个元素都是 [0, 255] 范围内的一个扩展 ASCII 字符值。

如需将码位数组转换为 STRINGBYTES,请参阅 CODE_POINTS_TO_STRINGCODE_POINTS_TO_BYTES

返回类型

INT64 类型的 ARRAY

示例

以下示例为一个单词数组中的每个元素获取代码点。

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

以下示例将 BYTES 的整数表示形式转换为对应的 ASCII 字符值。

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

以下示例演示了一个 BYTES 结果与一个 STRING 结果之间的区别。

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

请注意字符 Ā 表示为两个字节的 Unicode 序列。因此,TO_CODE_POINTSBYTES 版本会返回包含两个元素的数组,而 STRING 版本会返回包含单个元素的数组。

TO_HEX

TO_HEX(bytes)

说明

BYTES 序列转换为十六进制 STRING。将 STRING 中的各字节转换为 (0..9, a..f) 范围内的两个十六进制字符。如需将十六进制编码的 STRING 转换为 BYTES,请使用 FROM_HEX

返回类型

STRING

示例

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

说明

移除与 value2 匹配的所有前导和尾随字符。如果未指定 value2,则移除所有前导和尾随空格(如 Unicode 标准定义)。如果第一个参数是 BYTES 类型,则必须提供第二个参数。

如果 value2 包含多个字符或字节,则该函数会移除 value2 中包含的所有前导或尾随字符或字节。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+
WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+
WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

说明

对于 STRING 参数,该函数会返回所有字母字符均为大写的原始字符串。它根据 Unicode 字符数据库进行大写与小写之间的映射,而不考虑特定语言的映射。

对于 BYTES 参数,此函数会将其视为 ASCII 文本,并将所有大于 127 的字节保留原样。

返回类型

STRINGBYTES

示例

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

JSON 函数

Cloud Spanner SQL 支持可检索 JSON 格式字符串内存储的数据的函数,以及可将数据转化为 JSON 格式字符串的函数。

JSON_QUERY 或 JSON_VALUE

JSON_QUERY(json_string_expr, json_path_format),返回 STRING 格式的 JSON 值。

JSON_VALUE(json_string_expr, json_path_format),返回 STRING 格式的标量 JSON 值。

说明

提取 STRING 格式的 JSON 值或 JSON 标量值。

  • json_string_expr:JSON 格式的字符串。例如:

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_path_formatJSONpath 格式。 这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果 json_path_format 返回 JSON null,则将其转换为 SQL NULL

如果 JSON 密钥使用无效的 JSONPath 字符,则可以使用双引号转义这些字符。

示例

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| NULL              |
| "Jamie"           |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

如果 JSON 密钥使用无效的 JSONPath 字符,则可以使用双引号转义这些字符。例如:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;

+-------+
| hello |
+-------+
| world |
+-------+

JSONPath 格式

大多数 JSON 函数都会传入 json_string_exprjson_path_format 参数。json_string_expr 参数传入 JSON 格式的字符串,而 json_path_format 参数用于标识要从 JSON 格式的字符串中获取的一个或多个值。

json_string_expr 参数必须是格式如下的 JSON 字符串:

{"class" : {"students" : [{"name" : "Jane"}]}}

您可以使用 JSONPath 格式构建 json_path_format 参数。作为此格式的一部分,此参数必须以 $ 符号开头,这个符号标识 JSON 格式字符串的最外层。您可以使用英文句点来标识子值。如果 JSON 对象是数组,则可以使用英文括号指定数组索引。如果键包含 $、英文句点或英文方括号,请参阅各 JSON 函数以了解如何对其进行转义。

JSONPath 说明 示例 使用上述 json_string_expr 的结果
$ 根对象或元素 "$" {"class":{"students":[{"name":"Jane"}]}}
. 子运算符 "$.class.students" [{"name":"Jane"}]
[] 下标运算符 "$.class.students[0]" {"name":"Jane"}

如果 json_path_format 参数与 json_string_expr 中的值不匹配,则 JSON 函数会返回 NULL。如果标量函数的选定值不是标量,例如一个对象或一个数组,该函数会返回 NULL

如果 JSONPath 无效,则函数会引发错误。

数组函数

ARRAY

ARRAY(subquery)

说明

ARRAY 函数返回一个 ARRAY,其中子查询中的每一行都对应一个元素。

如果 subquery 生成一个 SQL 表,则此表必须只能包含一列。ARRAY 输出中的每个元素都是此表中这一列某行的值。

限制条件

  • 子查询是无序的,因此不保证输出 ARRAY 的元素为子查询保留源表中的任何顺序。但是,如果子查询包含 ORDER BY 子句,则 ARRAY 函数将返回一个遵循此子句的 ARRAY
  • 如果子查询返回多个列,则 ARRAY 函数会返回错误。
  • 如果子查询返回 ARRAY 类型的列或 ARRAY 类型的行,则 ARRAY 函数会返回错误,说明 Cloud Spanner SQL 不支持元素为 ARRAY 类型的 ARRAY
  • 如果子查询未返回任何行,则 ARRAY 函数会返回一个空的 ARRAY。该函数在任何情况下都不会返回 NULL ARRAY

返回类型

ARRAY

示例

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

如需通过包含多个列的子查询构造 ARRAY,请将子查询改为使用 SELECT AS STRUCT。现在 ARRAY 函数将返回由 STRUCT 组成的 ARRAY。对于子查询中的每一行,ARRAY 都包含一个 STRUCT,并且其中每个 STRUCT 都包含一个字段,对应于该行中的每一列。

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

同样,要通过包含一个或多个 ARRAY 的子查询构造 ARRAY,请将该子查询改为使用 SELECT AS STRUCT

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

说明

将一个或多个具有相同元素类型的数组串联为一个数组。

返回类型

ARRAY

示例

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

说明

返回数组的大小。如果数组为空,则返回 0。如果 array_expressionNULL,则返回 NULL

返回类型

INT64

示例

WITH items AS
  (SELECT ["coffee", NULL, "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [coffee, NULL, milk]            | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

说明

array_expression 中的元素连接起来并作为 STRING 返回。array_expression 的值可以是数据类型为 STRING 或 BYTES 的数组。

如果使用了 null_text 参数,该函数会将数组中的任何 NULL 值替换为 null_text 值。

如果未使用 null_text 参数,该函数会忽略 NULL 值及其前置分隔符。

示例

WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+
WITH items AS
  (SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

说明

返回一个值数组。start_expressionend_expression 参数确定数组的开始值和结束值(包含边界值)。

GENERATE_ARRAY 函数接受以下数据类型的输入:

  • INT64
  • NUMERIC
  • FLOAT64

step_expression 参数确定用于生成数组值的增量。此参数的默认值为 1

如果 step_expression 设置为 0 或者有任何输入为 NaN,此函数将返回错误。

如果任意参数为 NULL,该函数将返回一个 NULL 数组。

返回数据类型

ARRAY

示例

以下命令返回一个整数数组,默认步长为 1。

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

以下命令使用用户指定的步长返回一个数组。

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

以下命令使用负值步长 -3 返回一个数组。

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

以下命令返回一个 start_expressionend_expression 的值相同的数组。

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

以下命令返回一个空数组,因为 start_expression 大于 end_expression,且 step_expression 值为正数。

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

以下命令返回一个 NULL 数组,因为 end_expressionNULL

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

以下命令返回多个数组。

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

说明

返回一个日期数组。start_dateend_date 参数确定数组的开始值和结束值(包含边界值)。

GENERATE_DATE_ARRAY 函数接受以下数据类型的输入:

  • start_date 必须是 DATE
  • end_date 必须是 DATE
  • INT64_expr 必须是 INT64
  • date_part 必须是 DAY、WEEK、MONTH、QUARTER 或 YEAR。

INT64_expr 参数确定用于生成日期的增量。此参数的默认值为 1 天。

如果 INT64_expr 设置为 0,此函数将返回错误。

返回数据类型

包含 0 个或多个 DATE 值的 ARRAY。

示例

以下命令使用默认步长 1 返回日期数组。

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

以下命令使用用户指定的步长返回一个数组。

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

以下命令使用负值步长 -3 返回一个数组。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

以下命令返回一个 start_dateend_date 的值相同的数组。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

以下命令返回一个空数组,因为 start_date 大于 end_date,且 step 值为正数。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

以下命令返回一个 NULL 数组,因为其输入之一是 NULL

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

以下命令使用 MONTH 作为 date_part 时间间隔返回一个日期数组:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

以下命令使用非常量日期生成一个数组。

SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

OFFSET 和 ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

说明

按位置访问 ARRAY 元素并返回该元素。OFFSET 表示从 0 开始计数,ORDINAL 表示从 1 开始计数。

给定数组要么解释为从 0 开始,要么解释为从 1 开始。访问数组元素时,必须在数组位置之前分别附加 OFFSETORDINAL;该行为并非默认行为。

如果索引超出范围,OFFSETORDINAL 都会生成错误。

返回类型

因 ARRAY 中的元素而异。

示例

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

说明

按照输入 ARRAY 中元素的反向顺序返回该 ARRAY。

返回类型

ARRAY

示例

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr
)
SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

ARRAY_IS_DISTINCT

ARRAY_IS_DISTINCT(value)

说明

如果数组不包含重复元素,且使用与 SELECT DISTINCT 相同的相等比较逻辑,则返回 true。

返回类型

BOOL

示例

WITH example AS (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [1, 1, 1] AS arr UNION ALL
  SELECT [1, 2, NULL] AS arr UNION ALL
  SELECT [1, 1, NULL] AS arr UNION ALL
  SELECT [1, NULL, NULL] AS arr UNION ALL
  SELECT [] AS arr UNION ALL
  SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
  arr,
  ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;

+-----------------+-------------+
| arr             | is_distinct |
+-----------------+-------------+
| [1, 2, 3]       | true        |
| [1, 1, 1]       | false       |
| [1, 2, NULL]    | true        |
| [1, 1, NULL]    | false       |
| [1, NULL, NULL] | false       |
| []              | true        |
| NULL            | NULL        |
+-----------------+-------------+

SAFE_OFFSET 和 SAFE_ORDINAL

array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]

说明

基本等同于 OFFSETORDINAL,不同之处在于,如果索引超出范围,则返回 NULL

返回类型

因 ARRAY 中的元素而异。

示例

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list,
  list[SAFE_OFFSET(3)] as safe_offset_3,
  list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;

+----------------------------------+---------------+----------------+
| list                             | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes        | pears          |
| [coffee, tea, milk]              | NULL          | milk           |
| [cake, pie]                      | NULL          | NULL           |
+----------------------------------+---------------+----------------+

日期函数

Cloud Spanner SQL 支持以下 DATE 函数。

CURRENT_DATE

CURRENT_DATE([time_zone])

说明

返回指定或默认时区的当前日期。

此函数支持 time_zone 可选参数。此参数是表示要使用的时区的字符串。如果未指定时区,则使用默认时区 America/Los_Angeles。如需了解如何指定时区,请参阅时区定义

如果 time_zone 参数的计算结果为 NULL,则此函数会返回 NULL

返回数据类型

DATE

示例

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

说明

返回与指定日期部分相对应的值。part 必须是以下其中一项:

  • DAYOFWEEK:返回 [1,7] 范围内的值,其中星期日是一周的第一天。
  • DAY
  • DAYOFYEAR
  • WEEK:返回日期属于一年中的第几周,范围为 [0, 53]。一周从星期日开始算起。一年第一个星期日之前的日期属于第 0 周。
  • ISOWEEK:以 ISO 8601 格式返回 date_expression 属于一年之中的第几周。ISOWEEK 从星期一开始算起。返回值的范围为 [1, 53]。每个 ISO 格式年份的第一个 ISOWEEK 从公历年第一个星期四之前的星期一开始算起。
  • MONTH
  • QUARTER:返回 [1,4] 范围内的值。
  • YEAR
  • ISOYEAR:以 ISO 8601 格式返回周编号年份,即包含 date_expression 所属一周的星期四的公历年)。

返回数据类型

INT64

示例

在以下示例中,EXTRACT 返回了与 DAY 日期部分相对应的值。

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

在以下示例中,EXTRACT 返回了与日期一列中接近年末的不同日期部分相对应的值。

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

说明

  1. 使用表示年月日的 INT64 值构造 DATE。
  2. 从 TIMESTAMP 表达式中提取 DATE。它支持使用可选参数来指定时区。如果未指定时区,则使用默认时区 America/Los_Angeles。

返回数据类型

DATE

示例

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL int64_expression date_part)

说明

将指定的时间间隔与 DATE 相加。

DATE_ADD 支持以下 date_part 值:

  • DAY
  • WEEK。等于 7 DAY
  • MONTH
  • QUARTER
  • YEAR

如果日期是(或接近)月份的最后一天,则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果生成月份的天数少于原始日期的天数,则生成日期作为下一月的最后一天。

返回数据类型

DATE

示例

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL int64_expression date_part)

说明

从 DATE 中减去指定的日期间隔。

DATE_SUB 支持以下 date_part 值:

  • DAY
  • WEEK。等于 7 DAY
  • MONTH
  • QUARTER
  • YEAR

如果日期是(或接近)月份的最后一天,则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果生成月份的天数少于原始日期的天数,则生成日期作为下一月的最后一天。

返回数据类型

DATE

示例

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression_a, date_expression_b, date_part)

说明

返回两个 DATE 对象 (date_expression_a - date_expression_b) 之间完整指定的 date_part 间隔数。如果第一个 DATE 早于第二个,那么输出为负数。

DATE_DIFF 支持以下 date_part 值:

  • DAY
  • WEEK:此日期部分从星期日开始算起。
  • ISOWEEK:使用 ISO 8601 格式的周边界值。ISO 格式的周从星期一开始。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:使用 ISO 8601 格式的周编号年份边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。

返回数据类型

INT64

示例

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

上面的示例展示了连续两天的 DATE_DIFF 结果。 日期部分为 WEEKDATE_DIFF 会返回 1,因为 DATE_DIFF 会计算此日期范围内日期部分边界值的数量。每个 WEEK 都从星期日开始算起,因此在 2017-10-14 星期六与 2017-10-15 星期日之间有一个日期部分的边界值。

以下示例显示了不同年份中两个日期的 DATE_DIFF 结果。日期部分为 YEARDATE_DIFF 会返回 3,因为它会计算两个日期之间的公历年边界值数量。日期部分为 ISOYEARDATE_DIFF 会返回 2,因为第二个日期属于 ISO 格式的年份 2015。2015 日历年的第一个星期四是 2015-01-01,因此 ISO 格式的年份 2015 开始于上一个星期一(即 2014-12-29)。

SELECT
  DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
  DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;

+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3         | 2            |
+-----------+--------------+

以下示例显示连续两天的 DATE_DIFF 结果。第一个日期是星期一,第二个日期是星期日。日期部分为 WEEKDATE_DIFF 会返回 0,因为此日期部分使用从星期日开始算起的周。日期部分为 ISOWEEKDATE_DIFF 会返回 1,因为 ISO 格式的周从星期一开始算起。

SELECT
  DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
  DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;

+-----------+--------------+
| week_diff | isoweek_diff |
+-----------+--------------+
| 0         | 1            |
+-----------+--------------+

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

说明

将日期截断至指定的粒度。

DATE_TRUNC 支持以下 date_part 值:

  • DAY
  • WEEK
  • ISOWEEK:将 date_expression 截断至上一个 ISO 8601 格式周的边界值。ISOWEEK 从星期一开始算起。每个 ISO 格式年份的第一个 ISOWEEK 都包含对应公历年的第一个星期四。任何早于此日期的 date_expression 均会截断至上一个星期一。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:将 date_expression 截断至上一个 ISO 8601 格式周编号年份的边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。

返回数据类型

DATE

示例

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

在以下示例中,原始的 date_expression 属于公历 2015 年。不过,带有 ISOYEAR 日期部分的 DATE_TRUNC 会将 date_expression 截断至 ISO 格式年份(而非公历年)的开始。由于 2015 日历年的第一个星期四是 2015-01-01,ISO 格式的年份 2015 开始于上一个星期一(即 2014-12-29)。因此,date_expression 2015-06-15 之前的 ISO 格式年份边界值是 2014-12-29。

SELECT
  DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;

+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29       | 2015           |
+------------------+----------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(int64_expression)

说明

int64_expression 解释为从 1970-01-01 算起的天数。

返回数据类型

DATE

示例

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

说明

根据指定的 format_string 设置 date_expr 格式。

如需查看此函数支持的格式元素列表,请参阅 DATE 支持的格式元素

返回数据类型

STRING

示例

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

说明

日期的字符串表示法转换为 DATE 对象。

format_string 包含用于定义 date_string 格式的格式元素date_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 date_string 中每个元素的位置一致。

-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")

-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")

格式字符串完全支持大多数格式元素,但 %a%A%g%G%j%u%U%V%w%W 除外。

使用 PARSE_DATE 时,请注意以下几点:

  • 未指定的字段。任何未指定的字段在初始化时均以 1970-01-01 为准。
  • 不区分大小写的名称MondayFebruary 等名称不区分大小写。
  • 空格。格式字符串中的一个或多个连续空格与日期字符串中的零个或多个连续空格相匹配。此外,始终可在日期字符串中使用前导空格和尾随空格(即使格式字符串中不包含这些空格)。
  • 格式优先顺序。如果两个(或更多)格式元素具有重叠信息(例如,%F%Y 均对年份有影响),则最后一个格式元素通常会替换前面的所有元素。

返回数据类型

DATE

示例

以下示例将 MM/DD/YY 格式的字符串转换为 DATE 对象:

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

以下示例将 YYYYMMDD 格式的字符串转换为 DATE 对象:

SELECT PARSE_DATE("%Y%m%d", "20081225") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

说明

返回从 1970-01-01 开始计算的天数。

返回数据类型

INT64

示例

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

DATE 支持的格式元素

除非另有说明,否则使用格式字符串的 DATE 函数均支持以下元素:

格式元素 说明
%A 星期几的全名。
%a 星期几的缩写名称。
%B 月份的全名。
%b 或 %h 月份的缩写名称。
%C 用十进制数 (00-99) 表示的年份的前两位数(年份除以 100 并只取整数)。
%D 采用 %m/%d/%y 格式的日期。
%d 用十进制数 (01-31) 表示的月份中的某一天。
%e 用十进制数 (1-31) 表示的月份中的某一天;个位数前面附加一个空格。
%F 采用 %Y-%m-%d 格式的日期。
%G 用十进制数表示的带有世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。 请注意,%G 和 %Y 可能会在公历年的边界值附近产生不同的结果,其中公历年和 ISO 格式年份可能会有所不同。
%g 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。请注意,%G 和 %Y 可能会在公历年边界值附近产生不同的结果,其中公历年和 ISO 格式年份可能会有所不同。
%j 用十进制数 (001-366) 表示的一年中的某一天。
%m 用十进制数 (01-12) 表示的月份。
%n 换行符。
%t 制表符。
%U 用十进制数 (00-53) 表示的一年中的周数(星期日算作一周的第一天)。
%u 用十进制数 (1-7) 表示的星期名称(星期一算作一周的第一天)。
%V 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数(星期一算作一周的第一天)。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年,则该周为第 1 周;否则为上一年的第 53 周,而下一周为新年的第 1 周。
%W 用十进制数 (00-53) 表示的一年的周数(星期一算作一周的第一天)。
%w 用十进制数 (0-6) 表示的星期名称(星期日算作一周的第一天)。
%x 采用 MM/DD/YY 格式表示的日期。
%Y 用十进制数表示的四位数的年份。
%y 用十进制数 (00-99) 表示的不带世纪信息的年份(可选用前导零)。可与 %C 混合使用。如果未指定 %C,则 00-68 年属于 2000 年代,而 69-99 年属于 1900 年代。
%E4Y 用四个字符表示的年份 (0001 ... 9999)。注意:%Y 将生成尽量多的字符数以完整显示年份。

时间戳函数

Cloud Spanner SQL 支持以下 TIMESTAMP 函数。

注意:如果发生溢出,这些函数会返回运行时错误;结果值受到所定义的日期时间戳最小值/最大值限制。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

说明

英文括号是可选项。此函数处理闰秒的方式,是将闰秒均匀分布于其前后共 20 个小时的时段中。CURRENT_TIMESTAMP() 生成连续、非模糊的 TIMESTAMP 值,每分钟刚好 60 秒,并且在闰秒时不会重复该值。

支持的输入类型

不适用

结果数据类型

TIMESTAMP

示例

SELECT CURRENT_TIMESTAMP() as now;

+--------------------------------+
| now                            |
+--------------------------------+
| 2020-06-02T23:58:40.347847393Z |
+--------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE timezone])

说明

返回与提供的 timestamp_expression 中指定的 part 相对应的值。此函数支持 timezone 可选参数。如需了解如何指定时区,请参阅时区定义

允许的 part 值包括:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK:返回日期属于一年中的第几周,范围为 [0, 53]。一周从星期日开始算起。一年第一个星期日之前的日期属于第 0 周。
  • ISOWEEK:以 ISO 8601 格式返回 datetime_expression 属于一年之中的第几周。ISOWEEK 从星期一开始算起。返回值的范围为 [1, 53]。每个 ISO 格式年份的第一个 ISOWEEK 从公历年第一个星期四之前的星期一开始算起。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:以 ISO 8601 格式返回周编号年份,即包含 date_expression 所属一周的星期四的公历年)。
  • DATE

返回的值会截掉低位时间段。例如,提取秒数时,EXTRACT 会截掉毫秒值和微秒值。

返回数据类型

INT64,但以下情况除外:

  • partDATE,则返回 DATE 对象。

示例

在以下示例中,EXTRACT 返回了 DAY 时间部分对应的值。

WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
  EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input

+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25          | 24                 |
+-------------+--------------------+

在以下示例中,EXTRACT 返回了与时间戳的一列中不同的时间部分相对应的值。

WITH Timestamps AS (
  SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
  SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
  SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
  timestamp_value,
  EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
  EXTRACT(YEAR FROM timestamp_value) AS year,
  EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+---------+---------+------+------+
| timestamp_value        | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03T12:34:56Z   | 2005    | 1       | 2005 | 1    |
| 2007-12-31T12:00:00Z   | 2008    | 1       | 2007 | 52   |
| 2009-01-01T12:00:00Z   | 2009    | 1       | 2009 | 0    |
| 2009-12-31T12:00:00Z   | 2009    | 53      | 2009 | 52   |
| 2017-01-02T12:00:00Z   | 2017    | 1       | 2017 | 1    |
| 2017-05-26T12:00:00Z   | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

STRING

STRING(timestamp_expression[, timezone])

说明

timestamp_expression 转换为 STRING 数据类型。支持使用可选参数来指定时区。如需了解如何指定时区,请参阅时区定义

返回数据类型

STRING

示例

SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;

+-------------------------------+
| string                        |
+-------------------------------+
| 2008-12-25 15:30:00+00        |
+-------------------------------+

TIMESTAMP

TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])

说明

  • string_expression[, timezone]:将 STRING 表达式转换为 TIMESTAMP 数据类型。string_expression 必须包含一个时间戳字面量。如果 string_expression 在时间戳字面量中包含时区,请勿明确包含 timezone 参数。
  • date_expression[, timezone]:将 DATE 对象转换为 TIMESTAMP 数据类型。

此函数支持使用可选参数来指定时区。如果未指定时区,则使用默认时区 America/Los_Angeles。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T23:30:00Z |
+----------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T15:30:00Z |
+----------------------+
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+----------------------+
| timestamp_str        |
+----------------------+
| 2008-12-25T08:00:00Z |
+----------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

说明

向时间戳中添加 int64_expression 单位的 date_part,与时区无关。

TIMESTAMP_ADD 支持以下 date_part 值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE
  • DAY。等于 24 HOUR

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:40:00Z   |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

说明

从时间戳中减去 int64_expression 个单位的 date_part,与时区无关。

TIMESTAMP_SUB 支持以下 date_part 值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE
  • DAY。等于 24 HOUR

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP("2008-12-25 15:30:00+00") AS original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25T15:30:00Z   | 2008-12-25T15:20:00Z   |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)

说明

返回两个 TIMESTAMP 对象 (timestamp_expression_a - timestamp_expression_b) 之间完整指定的 date_part 间隔数。如果第一个 TIMESTAMP 早于第二个,那么输出为负数。如果计算结果超过结果类型的最大值(例如两个 TIMESTAMP 对象之间的纳秒数之差超过一个 INT64 最大值),则会引发一个错误。

TIMESTAMP_DIFF 支持以下 date_part 值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等于 60 MINUTE
  • DAY。等于 24 HOUR

返回数据类型

INT64

示例

SELECT
  TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
  TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07T10:20:00Z   | 2008-12-25T15:30:00Z   | 13410 |
+------------------------+------------------------+-------+

在以下示例中,第一个时间戳出现在第二个时间戳之前,从而产生负输出。

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

在本示例中,结果为 0,因为仅包含整个指定的 HOUR 间隔数。

SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)

+---------------+
| negative_diff |
+---------------+
| 0             |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])

说明

将时间戳截断为 date_part 的粒度。

TIMESTAMP_TRUNC 支持以下 date_part 值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • ISOWEEK:将 timestamp_expression 截断至上一个 ISO 8601 格式周的边界值。ISOWEEK 从星期一开始算起。每个 ISO 格式年份的第一个 ISOWEEK 都包含对应公历年的第一个星期四。任何早于此日期的 date_expression 均会截断至上一个星期一。
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:将 timestamp_expression 截断至上一个 ISO 8601 格式周编号年份的边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。

TIMESTAMP_TRUNC 函数支持可选的 timezone 参数。此参数适用于以下 date_parts

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • ISOWEEK
  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR

如果您想要在截断操作过程中使用默认时区 America/Los_Angeles 以外的时区,请使用此参数。

TIMESTAMP 截断到 MINUTEHOUR 时,TIMESTAMP_TRUNC 会确定 TIMESTAMP 在指定(或默认)时区中的民用时间,并且从该 TIMESTAMP 中减去分钟和秒(截断到 HOUR 时)或减去秒(截断到 MINUTE 时)。虽然这在大多数情况下都可以提供直观的结果,但在临近夏令时转换时,小时并不一致,因此结果会不太直观。

返回数据类型

TIMESTAMP

示例

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc,
  TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25T00:00:00Z   | 2008-12-25T08:00:00Z   |
+------------------------+------------------------+

在以下示例中,原始的 timestamp_expression 属于公历 2015 年。但对日期部分 ISOYEAR 执行 TIMESTAMP_TRUNC 会将 timestamp_expression 截断到 ISO 格式年份的开端,而非公历年的开端。公历年 2015 年的第一个星期四是 2015-01-01,因此 ISO 格式年份 2015 年开始于上一个星期一,也就是 2014-12-29。因此,ISO 格式的 timestamp_expression 2015-06-15 00:00:00+00 的上一个年份边界值是 2014-12-29。

SELECT
  TIMESTAMP_TRUNC("2015-06-15 00:00:00+00", ISOYEAR) AS isoyear_boundary,
  EXTRACT(ISOYEAR FROM TIMESTAMP "2015-06-15 00:00:00+00") AS isoyear_number;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+----------------+
| parsed                 | isoyear_number |
+------------------------+----------------+
| 2014-12-29T08:00:00Z   | 2015           |
+------------------------+----------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, timezone])

说明

根据指定的 format_string 设置时间戳格式。

如需查看此函数支持的格式元素列表,请参阅 TIMESTAMP 支持的格式元素

返回数据类型

STRING

示例

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", TIMESTAMP "2008-12-25 15:30:00+00") AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_TIMESTAMP("%b %Y", TIMESTAMP "2008-12-25 15:30:00+00")
  AS formatted;

+-------------+
| formatted   |
+-------------+
| Dec 2008    |
+-------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, timestamp_string[, timezone])

说明

时间戳的字符串表示法转换为 TIMESTAMP 对象。

format_string 包含用于定义 timestamp_string 格式的格式元素timestamp_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 timestamp_string 中每个元素的位置一致。

-- This works because elements on both sides match.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because the year element is in different locations.
SELECT PARSE_TIMESTAMP("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This doesn't work because one of the year elements is missing.
SELECT PARSE_TIMESTAMP("%a %b %e %I:%M:%S", "Thu Dec 25 07:30:00 2008")

-- This works because %c can find all matching elements in timestamp_string.
SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008")

格式字符串完全支持大多数格式元素,但 %a%A%g%G%j%P%u%U%V%w%W 除外。

使用 PARSE_TIMESTAMP 时,请注意以下几点:

  • 未指定的字段。任何未指定的字段在初始化时均以 1970-01-01 00:00:00.0 为准。此初始化值使用函数的时区参数(如果存在)所指定的时区。如果未指定,则初始化值使用默认时区 America/Los_Angeles。例如,如果未指定年份,则默认为 1970,依此类推。
  • 名称不区分大小写。MondayFebruary 等名称不区分大小写。
  • 空格。格式字符串中的一个或更多连续空格与时间戳字符串中的零个或更多连续空格相匹配。此外,始终可在时间戳字符串中使用前导空格和尾随空格(即使这些空格不在格式字符串中)。
  • 格式优先顺序。如果两个(或更多)格式元素的信息出现重叠(例如 %F%Y 均对年份有影响),则最后一个元素通常会替换前面的所有元素,但也存在一些例外情况(请参阅 %s%C%y 的说明)。

返回数据类型

TIMESTAMP

示例

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008") AS parsed;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| parsed                 |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

说明

int64_expression 解释为自 1970-01-01 00:00:00 UTC 后的秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_SECONDS(1230219000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

说明

int64_expression 解释为从 1970-01-01 00:00:00 UTC 开始计算的毫秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_MILLIS(1230219000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

说明

int64_expression 解释从 1970-01-01 00:00:00 UTC 开始计算的微秒数。

返回数据类型

TIMESTAMP

示例

SELECT TIMESTAMP_MICROS(1230219000000000) AS timestamp_value;

-- Results may differ, depending upon the environment and time zone where this query was executed.
+------------------------+
| timestamp_value        |
+------------------------+
| 2008-12-25T15:30:00Z   |
+------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00+00") AS seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的毫秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00+00") AS millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

说明

返回从 1970-01-01 00:00:00 UTC 开始计算的微秒数。截断更高级别的精度。

返回数据类型

INT64

示例

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00+00") AS micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

PENDING_COMMIT_TIMESTAMP

PENDING_COMMIT_TIMESTAMP()

说明

在 DML INSERTUPDATE 语句中使用 PENDING_COMMIT_TIMESTAMP() 函数将待处理的提交时间戳(即写入提交时的提交时间戳)写入一个类型为 TIMESTAMP 的列。

Cloud Spanner SQL 会在事务提交时选择提交时间戳。PENDING_COMMIT_TIMESTAMP 函数只能用作正确分配了类型的列的 INSERT 或 UPDATE 值。该函数不能用于 SELECT,也不能用作任何其他标量表达式的输入。

返回数据类型

TIMESTAMP

示例

以下 DML 语句使用提交时间戳更新 Singers 表中的 LastUpdated 列。

UPDATE Performances SET LastUpdated = PENDING_COMMIT_TIMESTAMP()
   WHERE SingerId=1 AND VenueId=2 AND EventDate="2015-10-21"

TIMESTAMP 支持的格式元素

除非另有说明,否则使用格式字符串的 TIMESTAMP 函数均支持以下元素:

格式元素 说明
%A 星期几的全名。
%a 星期几的缩写名称。
%B 月份的全名。
%b 或 %h 月份的缩写名称。
%C 用十进制数 (00-99) 表示的世纪(年份除以 100 并截断为整数)。
%c 日期和时间表示格式为 %a %b %e %T %Y。
%D 采用 %m/%d/%y 格式的日期。
%d 用十进制数 (01-31) 表示的月份中的某一天。
%e 用十进制数 (1-31) 表示的月份中的某一天;个位数前面附加一个空格。
%F 采用 %Y-%m-%d 格式的日期。
%G 用十进制数表示的带有世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。 请注意,%G 和 %Y 可能会在公历年的边界值附近产生不同的结果,其中公历年和 ISO 格式年份可能会有所不同。
%g 用十进制数 (00-99) 表示的不带世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。请注意,%G 和 %Y 可能会在公历年边界值附近产生不同的结果,其中公历年和 ISO 格式年份可能会有所不同。
%H 用十进制数 (00-23) 表示的小时数(24 小时制)。
%I 用十进制数 (01-12) 表示的小时数(12 小时制)。
%j 用十进制数 (001-366) 表示的一年中的某一天。
%k 用十进制数 (0-23) 表示的小时数(24 小时制);个位数前面附加一个空格。
%l 用十进制数 (1-12) 表示的小时数(12 小时制);个位数前面附加一个空格。
%M 用十进制数 (00-59) 表示的分钟数。
%m 用十进制数 (01-12) 表示的月份。
%n 换行符。
%P am 或 pm。
%p AM 或 PM。
%R 采用 %H:%M 格式表示的时间。
%r 使用 AM/PM 标记法表示的 12 小时制的时间。
%S 用十进制数 (00-60) 表示的秒数。
%s 从 1970-01-01 00:00:00 UTC 开始计算的秒数。无论 %s 出现在字符串的哪个位置,始终替换所有其他格式元素。如果出现多个 %s 元素,则以最后一个元素为准。
%T 采用 %H:%M:%S 格式表示的时间。
%t 制表符。
%U 用十进制数 (00-53) 表示的一年中的周数(星期日算作一周的第一天)。
%u 用十进制数 (1-7) 表示的星期名称(星期一算作一周的第一天)。
%V 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数(星期一算作一周的第一天)。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年,则该周为第 1 周;否则为上一年的第 53 周,而下一周为新年的第 1 周。
%W 用十进制数 (00-53) 表示的一年的周数(星期一算作一周的第一天)。
%w 用十进制数 (0-6) 表示的星期名称(星期日算作一周的第一天)。
%X 采用 HH:MM:SS 格式表示的时间。
%x 采用 MM/DD/YY 格式表示的日期。
%Y 用十进制数表示的四位数的年份。
%y 用十进制数 (00-99) 表示的不带世纪信息的年份(可选用前导零)。可与 %C 混合使用。如果未指定 %C,则 00-68 年属于 2000 年代,而 69-99 年属于 1900 年代。
%Z 时区名称。
%z 根据情况采用 +HHMM 或 -HHMM 格式表示与本初子午线的偏移量,其中正值表示格林威治东区。
%% 单个 % 字符。
%Ez 与 RFC 3339 兼容的数字时区(+HH:MM 或 -HH:MM)。
%E#S 具有 # 位小数精度的秒数。
%E*S 具有完整小数精度的秒数(字面量“*”)。
%E4Y 四个字符的年份 (0001 ... 9999)。注意:%Y 将生成尽量多的字符数以完整显示年份。

时区定义

某些日期和时间戳函数可用于替换默认时区并指定其他时区。您可以通过提供时区名称(例如:America/Los_Angeles)或相对于世界协调时间 (UTC) 的时区偏离值(例如:-08)来指定时区。

如果您选择使用时区偏离值,请使用以下格式:

(+|-)H[H][:M[M]]

以下时间戳是等效的,因为对于指定日期和时间,America/Los_Angeles的时区偏离值为 -08

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 America/Los_Angeles") as millis;
SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00-08:00") as millis;

网络函数

NET.IP_FROM_STRING

NET.IP_FROM_STRING(addr_str)

说明

将 IPv4 或 IPv6 地址从文本 (STRING) 格式转换为网络字节顺序的二进制 (BYTES) 格式。

此函数支持以下 addr_str 格式:

  • IPv4:点分四组格式。例如 10.1.2.3
  • IPv6:英文冒号分隔格式。例如 1234:5678:90ab:cdef:1234:5678:90ab:cdef。如需查看更多示例,请参阅 IP 版本 6 寻址架构

此函数不支持 CIDR 表示法,例如 10.1.2.3/32

如果此函数收到一个 NULL 输入,则会返回 NULL。如果输入视为无效,则发生 OUT_OF_RANGE 错误。

返回数据类型

BYTES

示例

SELECT
  addr_str, FORMAT("%T", NET.IP_FROM_STRING(addr_str)) AS ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128'
]) AS addr_str;
addr_str ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"

NET.SAFE_IP_FROM_STRING

NET.SAFE_IP_FROM_STRING(addr_str)

说明

类似于 NET.IP_FROM_STRING,但在输入无效的情况下会返回 NULL,而不是抛出错误。

返回数据类型

BYTES

示例

SELECT
  addr_str,
  FORMAT("%T", NET.SAFE_IP_FROM_STRING(addr_str)) AS safe_ip_from_string
FROM UNNEST([
  '48.49.50.51',
  '::1',
  '3031:3233:3435:3637:3839:4041:4243:4445',
  '::ffff:192.0.2.128',
  '48.49.50.51/32',
  '48.49.50',
  '::wxyz'
]) AS addr_str;
addr_str safe_ip_from_string
48.49.50.51 b"0123"
::1 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01"
3031:3233:3435:3637:3839:4041:4243:4445 b"0123456789@ABCDE"
::ffff:192.0.2.128 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
48.49.50.51/32 NULL
48.49.50 NULL
::wxyz NULL

NET.IP_TO_STRING

NET.IP_TO_STRING(addr_bin)

说明 将 IPv4 或 IPv6 地址从网络字节顺序的二进制 (BYTES) 格式转换为文本 (STRING) 格式。

如果输入为 4 个字节,则此函数返回 STRING 格式的 IPv4 地址。如果输入为 16 个字节,则返回 STRING 格式的 IPv6 地址。

如果此函数收到一个 NULL 输入,则会返回 NULL。如果输入的长度并非 4 或 16,则发生 OUT_OF_RANGE 错误。

返回数据类型

STRING

示例

SELECT FORMAT("%T", x) AS addr_bin, NET.IP_TO_STRING(x) AS ip_to_string
FROM UNNEST([
  b"0123",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01",
  b"0123456789@ABCDE",
  b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80"
]) AS x;
addr_bin ip_to_string
b"0123" 48.49.50.51
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x01" ::1
b"0123456789@ABCDE" 3031:3233:3435:3637:3839:4041:4243:4445
b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xff\xff\xc0\x00\x02\x80" ::ffff:192.0.2.128

NET.IP_NET_MASK

NET.IP_NET_MASK(num_output_bytes, prefix_length)

说明

返回一个网络掩码:长度等于 num_output_bytes 的一个字节序列,其中第一个 prefix_length 位设置为 1,其他位设置为 0。num_output_bytesprefix_length 为 INT64 类型。如果 num_output_bytes 不是 4(对于 IPv4)或 16(对于 IPv6),则此函数会抛出错误。如果 prefix_length 为负或者大于 8 * num_output_bytes,此函数也会抛出错误。

返回数据类型

BYTES

示例

SELECT x, y, FORMAT("%T", NET.IP_NET_MASK(x, y)) AS ip_net_mask
FROM UNNEST([
  STRUCT(4 as x, 0 as y),
  (4, 20),
  (4, 32),
  (16, 0),
  (16, 1),
  (16, 128)
]);
x y ip_net_mask
4 0 b"\x00\x00\x00\x00"
4 20 b"\xff\xff\xf0\x00"
4 32 b"\xff\xff\xff\xff"
16 0 b"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 1 b"\x80\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00"
16 128 b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff"

NET.IP_TRUNC

NET.IP_TRUNC(addr_bin, prefix_length)

说明 接受 addr_bin(以网络字节顺序排列的二进制 (BYTES) 格式的 IPv4 或 IPv6 地址),并以相同格式返回子网地址。结果具有与 addr_bin 相同的长度,其中第一个 prefix_length 位等于 addr_bin 中的位,其余位是 0。

如果 LENGTH(addr_bin) 不是 4 或 16,或者 prefix_len 为负或者大于 LENGTH(addr_bin) * 8,此函数会抛出错误。

返回数据类型

BYTES

示例

SELECT
  FORMAT("%T", x) as addr_bin, prefix_length,
  FORMAT("%T", NET.IP_TRUNC(x, prefix_length)) AS ip_trunc
FROM UNNEST([
  STRUCT(b"\xAA\xBB\xCC\xDD" as x, 0 as prefix_length),
  (b"\xAA\xBB\xCC\xDD", 11), (b"\xAA\xBB\xCC\xDD", 12),
  (b"\xAA\xBB\xCC\xDD", 24), (b"\xAA\xBB\xCC\xDD", 32),
  (b'0123456789@ABCDE', 80)
]);
addr_bin prefix_length ip_trunc
b"\xaa\xbb\xcc\xdd" 0 b"\x00\x00\x00\x00"
b"\xaa\xbb\xcc\xdd" 11 b"\xaa\xa0\x00\x00"
b"\xaa\xbb\xcc\xdd" 12 b"\xaa\xb0\x00\x00"
b"\xaa\xbb\xcc\xdd" 24 b"\xaa\xbb\xcc\x00"
b"\xaa\xbb\xcc\xdd" 32 b"\xaa\xbb\xcc\xdd"
b"0123456789@ABCDE" 80 b"0123456789\x00\x00\x00\x00\x00\x00"

NET.IPV4_FROM_INT64

NET.IPV4_FROM_INT64(integer_value)

说明

将 IPv4 地址从整数格式转换为网络字节顺序的二进制 (BYTES) 格式。在整数输入中,无论主机或客户端采用何种架构,IP 地址的最低有效位都存储为整数最低有效位。例如,1 表示 0.0.0.10x1FF 表示 0.0.1.255

此函数会检查是否所有最高有效 32 位均为 0,或者是否所有最高有效 33 位均为 1(从 32 位整数进行有符号扩展)。换句话说,输入应该在 [-0x80000000, 0xFFFFFFFF] 范围内;否则,此函数会抛出错误。

此函数不支持 IPv6。

返回数据类型

BYTES

示例

SELECT x, x_hex, FORMAT("%T", NET.IPV4_FROM_INT64(x)) AS ipv4_from_int64
FROM (
  SELECT CAST(x_hex AS INT64) x, x_hex
  FROM UNNEST(["0x0", "0xABCDEF", "0xFFFFFFFF", "-0x1", "-0x2"]) AS x_hex
);
x x_hex ipv4_from_int64
0 0x0 b"\x00\x00\x00\x00"
11259375 0xABCDEF b"\x00\xab\xcd\xef"
4294967295 0xFFFFFFFF b"\xff\xff\xff\xff"
-1 -0x1 b"\xff\xff\xff\xff"
-2 -0x2 b"\xff\xff\xff\xfe"

NET.IPV4_TO_INT64

NET.IPV4_TO_INT64(addr_bin)

说明

将 IPv4 地址从网络字节顺序的二进制 (BYTES) 格式转换为整数格式。在整数输出中,无论主机或客户端采用何种架构,IP 地址的最低有效位都存储为整数最低有效位。例如,1 表示 0.0.0.10x1FF 表示 0.0.1.255。输出在 [0, 0xFFFFFFFF] 范围内。

如果输入长度不是 4,则此函数会抛出错误。

此函数不支持 IPv6。

返回数据类型

INT64

示例

SELECT
  FORMAT("%T", x) AS addr_bin,
  FORMAT("0x%X", NET.IPV4_TO_INT64(x)) AS ipv4_to_int64
FROM
UNNEST([b"\x00\x00\x00\x00", b"\x00\xab\xcd\xef", b"\xff\xff\xff\xff"]) AS x;
addr_bin ipv4_to_int64
b"\x00\x00\x00\x00" 0x0
b"\x00\xab\xcd\xef" 0xABCDEF
b"\xff\xff\xff\xff" 0xFFFFFFFF

NET.HOST

NET.HOST(url)

说明

获取一个 STRING 格式的网址,并返回 STRING 格式的主机。为获得最佳结果,网址值应符合 RFC 3986 定义的格式。如果网址值不符合 RFC 3986 格式,此函数会尽最大努力解析输入并返回相关结果。如果函数无法解析输入,则返回 NULL。

注意:此函数不执行任何标准化。

返回数据类型

STRING

示例

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
输入 说明 主机 后缀 域名
"" 无效输入 NULL NULL NULL
"http://abc.xyz" 标准网址 "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" 具有相对架构、端口、路径和查询的标准网址,但没有公共后缀 "a.b" NULL NULL
"https://[::1]:80" IPv6 主机的标准网址 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" 采用国际化域名的标准网址 "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " 带有空格、大写字母但没有架构的非标准网址 "www.Example.Co.UK" "Co.UK" "Example.Co.UK"
"mailto:?to=&subject=&body=" URI 而非网址 - 不支持 "mailto" NULL NULL

NET.PUBLIC_SUFFIX

NET.PUBLIC_SUFFIX(url)

说明

接受一个 STRING 格式的网址,并返回 STRING 格式的公共后缀(例如 comorgnet)。公共后缀是在 publicsuffix.org 注册的一个 ICANN 域名。为获得最佳结果,网址值应符合 RFC 3986 定义的格式。如果网址值不符合 RFC 3986 格式,此函数会尽最大努力解析输入并返回相关结果。

如果下述任一条件为 true,则此函数返回 NULL:

  • 它无法通过输入解析主机;
  • 解析得出的主机中间包含相邻点(而非前导或尾随位置);
  • 解析得到的主机不包含任何公共后缀。

在查找公共后缀之前,此函数会临时标准化主机,方法是将大写英文字母转换为小写字母,并使用 Punycode 编码所有非 ASCII 字符。随后,此函数将公共后缀作为原始主机(而非标准化主机)的一部分返回。

注意:此函数不执行 Unicode 标准化

注意publicsuffix.org 的公共后缀数据还包含私有域名。此函数会忽略私有域名。

注意:公共后缀数据可能会随着时间的推移而变化。因此,现在生成 NULL 结果的输入可能会在将来生成非 NULL 值。

返回数据类型

STRING

示例

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
输入 说明 主机 后缀 域名
"" 无效输入 NULL NULL NULL
"http://abc.xyz" 标准网址 "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" 具有相对架构、端口、路径和查询的标准网址,但没有公共后缀 "a.b" NULL NULL
"https://[::1]:80" IPv6 主机的标准网址 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" 采用国际化域名的标准网址 "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " 带有空格、大写字母但没有架构的非标准网址 "www.Example.Co.UK" "Co.UK" "Example.Co.UK
"mailto:?to=&subject=&body=" URI 而非网址 - 不支持 "mailto" NULL NULL

NET.REG_DOMAIN

NET.REG_DOMAIN(url)

说明

接受一个 STRING 格式的网址,并返回 STRING 格式的已注册或可注册域名(公共后缀加一个前置标签)。为获得最佳结果,网址值应符合 RFC 3986 定义的格式。如果网址值不符合 RFC 3986 格式,此函数会尽最大努力解析输入并返回相关结果。

如果下述任一条件为 true,则此函数返回 NULL:

  • 它无法通过输入解析主机;
  • 解析得出的主机中间包含相邻点(而非前导或尾随位置);
  • 解析得到的主机不包含任何公共后缀;
  • 解析得出的主机只包含公共后缀,而无任何前置标签。

在查找公共后缀之前,此函数会临时标准化主机,方法是将大写英文字母转换为小写字母,并使用 Punycode 编码所有非 ASCII 字符。随后,此函数将已注册或可注册域名作为原始主机(而非标准化主机)的一部分返回。

注意:此函数不执行 Unicode 标准化

注意publicsuffix.org 的公共后缀数据还包含私有域名。此函数不会将私有域名视为公共后缀。例如,如果“us.com”是公共后缀数据中的一个私有域名,NET.REG_DOMAIN("foo.us.com") 会返回“us.com”(公共后缀“com”加上前置标签“us”)而非“foo.us.com”(私有域名“us.com”加上前置标签“foo”)。

注意:公共后缀数据可能会随着时间的推移而变化。因此,现在生成 NULL 结果的输入可能会在将来生成非 NULL 值。

返回数据类型

STRING

示例

SELECT
  FORMAT("%T", input) AS input,
  description,
  FORMAT("%T", NET.HOST(input)) AS host,
  FORMAT("%T", NET.PUBLIC_SUFFIX(input)) AS suffix,
  FORMAT("%T", NET.REG_DOMAIN(input)) AS domain
FROM (
  SELECT "" AS input, "invalid input" AS description
  UNION ALL SELECT "http://abc.xyz", "standard URL"
  UNION ALL SELECT "//user:password@a.b:80/path?query",
                   "standard URL with relative scheme, port, path and query, but no public suffix"
  UNION ALL SELECT "https://[::1]:80", "standard URL with IPv6 host"
  UNION ALL SELECT "http://例子.卷筒纸.中国", "standard URL with internationalized domain name"
  UNION ALL SELECT "    www.Example.Co.UK    ",
                   "non-standard URL with spaces, upper case letters, and without scheme"
  UNION ALL SELECT "mailto:?to=&subject=&body=", "URI rather than URL--unsupported"
);
输入 说明 主机 后缀 域名
"" 无效输入 NULL NULL NULL
"http://abc.xyz" 标准网址 "abc.xyz" "xyz" "abc.xyz"
"//user:password@a.b:80/path?query" 具有相对架构、端口、路径和查询的标准网址,但没有公共后缀 "a.b" NULL NULL
"https://[::1]:80" IPv6 主机的标准网址 "[::1]" NULL NULL
"http://例子.卷筒纸.中国" 采用国际化域名的标准网址 "例子.卷筒纸.中国" "中国" "卷筒纸.中国"
"    www.Example.Co.UK    " 带有空格、大写字母但没有架构的非标准网址 "www.Example.Co.UK" "Co.UK" "Example.Co.UK"
"mailto:?to=&subject=&body=" URI 而非网址 - 不支持 "mailto" NULL NULL

运算符

运算符由特殊字符或关键字表示;它们不使用函数调用语法。一个运算符可操作任意数量的数据输入(也称为操作数),并返回结果。

常见惯例:

  • 除非另有指定,否则只要有一个操作数是 NULL,所有运算符都会返回 NULL
  • 如果计算结果溢出,所有运算符都将抛出错误。
  • 对于所有浮点运算,仅当其中一个操作数是 +/-infNaN 时才可能返回 +/-infNaN。在其他情况下,系统会返回错误。

下表按从高到低的优先顺序(即在语句中计算的顺序)列出所有 Cloud Spanner SQL 运算符。

优先顺序 运算符 输入数据类型 名称 运算符元数
1 . STRUCT
成员字段访问运算符 二元
  [ ] ARRAY 数组位置。必须与 OFFSET 或 ORDINAL 一起使用 - 请参阅 ARRAY 函数 二元
2 - 所有数字类型 一元取反 一元
  ~ 整数或 BYTES 按位非 一元
3 * 所有数字类型 二元
  / 所有数字类型 二元
  || STRING、BYTES 或 ARRAY<T> 串联运算符 二元
4 + 所有数字类型 二元
  - 所有数字类型 二元
5 << 整数或 BYTES 按位左移 二元
  >> 整数或 BYTES 按位右移 二元
6 & 整数或 BYTES 按位与 二元
7 ^ 整数或 BYTES 按位异或 二元
8 | 整数或 BYTES 按位或 二元
9(比较运算符) = 任何可比较类型。请参阅数据类型以获取完整列表。 等于 二元
  < 任何可比较类型。请参阅数据类型以获取完整列表。 小于 二元
  > 任何可比较类型。请参阅数据类型以获取完整列表。 大于 二元
  <= 任何可比较类型。请参阅数据类型以获取完整列表。 小于或等于 二元
  >= 任何可比较类型。请参阅数据类型以获取完整列表。 大于或等于 二元
  !=、<> 任何可比较类型。请参阅数据类型以获取完整列表。 不等于 二元
  [NOT] LIKE STRING 和字节 值与指定的模式 [不] 匹配 二元
  [NOT] BETWEEN 任何可比较类型。请参阅数据类型以获取完整列表。 值 [不] 在指定的范围内 二元
  [NOT] IN 任何可比较类型。请参阅数据类型以获取完整列表。 值[不]属于指定的值集 二元
  IS [NOT] NULL 全部 值 [不] 是 NULL 一元
  IS [NOT] TRUE BOOL 值 [不] 为 TRUE。 一元
  IS [NOT] FALSE BOOL 值 [不] 为 FALSE。 一元
10 NOT BOOL 逻辑非 一元
11 AND BOOL 逻辑与 二元
12 OR BOOL 逻辑或 二元

优先级相同的运算符采用左结合方式。这表示这些运算符会从左开始向右组合在一起。例如,以下表达式:

x AND y AND z

会被解释为

( ( x AND y ) AND z )

以下表达式:

x * y / z

会被解释为:

( ( x * y ) / z )

所有比较运算符的优先级相同,但比较运算符不具有结合性。因此,需要使用括号来避免歧义。例如:

(x < y) IS FALSE

元素访问运算符

运算符 语法 输入数据类型 结果数据类型 说明
. expression.fieldname1... STRUCT
fieldname1 中存储的类型 T 点运算符。可用于访问嵌套字段,例如 expression.fieldname1.fieldname2...
[ ] array_expression [position_keyword (int_expression ) ] 参见 ARRAY 函数。 ARRAY 中存储的类型 T position_keyword 是 OFFSET 或 ORDINAL。如需了解使用此运算符的两个函数,请参阅 ARRAY 函数

算术运算符

除非以下说明中另有指定,否则所有算术运算符都接受数字类型 T 的输入且结果类型为类型 T:

名称 语法
X + Y
X - Y
X * Y
X / Y
一元取反 - X

注意:除以 0 的运算会返回一个错误。如需返回不同的结果,请考虑使用 IEEE_DIVIDE 或 SAFE_DIVIDE 函数。

加法、减法和乘法的结果类型:

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

除法的结果类型:

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

一元取反的结果类型:

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

按位运算符

所有按位运算符均返回与第一个操作符相同的类型和长度。

名称 语法 输入数据类型 说明
按位非 ~ X 整数或 BYTES 对每一位进行逻辑非运算,生成给定二进制值的补码。
按位或 X | Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑兼或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位异或 X ^ Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑排斥或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位与 X & Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑与运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
向左移位 X << Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向左移位。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。
向右移位 X >> Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向右移位。此运算符不会对带符号的类型执行符号位扩展(也就是说,它会在左侧的空位上填入 0)。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。

逻辑运算符

Cloud Spanner SQL 支持 ANDORNOT 逻辑运算符。逻辑运算符仅允许 BOOL 或 NULL 输入,并使用三值逻辑来生成结果。结果可以是 TRUEFALSENULL

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE FALSE NULL
NULL NULL NULL NULL
x NOT x
TRUE FALSE
FALSE TRUE
NULL NULL

示例

本部分中的示例引用名为 entry_table 的表:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

比较运算符

比较运算符始终返回 BOOL。通常情况下,两个操作数的类型必须相同才能进行比较运算。如果操作数的类型不同,并且 Cloud Spanner SQL 可以在没有精度损失的情况下将这些类型的值转换为某种通用类型,那么 Cloud Spanner SQL 通常会将这些类型强制转换为该通用类型以进行比较;Cloud Spanner SQL 通常会将字面量强制转换为非字面量类型(如果存在)。如需了解可以进行比较的数据类型,请参阅数据类型

STRUCT 仅支持 4 种比较运算符:等于 (=)、不等于(!= 和 <>)及 IN

这些数据类型在比较时遵循以下规则:

  • FLOAT64:所有带 NaN 的比较都返回 FALSE(!=<> 除外,它们返回的是 TRUE)。
  • BOOL:FALSE 小于 TRUE。
  • STRING:字符串按代码点逐一进行比较,这意味着只有先对字符串进行标准化处理,才能保证按规范相等的字符串比较为相等。
  • NULL:根据惯例,带 NULL 输入的任何运算均返回 NULL
名称 语法 说明
小于 X < Y 如果 X 小于 Y,则返回 TRUE。
小于或等于 X <= Y 如果 X 小于或等于 Y,则返回 TRUE。
大于 X > Y 如果 X 大于 Y,则返回 TRUE。
大于或等于 X >= Y 如果 X 大于或等于 Y,则返回 TRUE。
等于 X = Y 如果 X 等于 Y,则返回 TRUE。
不等于 X != Y
X <> Y
如果 X 不等于 Y,则返回 TRUE。
BETWEEN X [NOT] BETWEEN Y AND Z 如果 X [不] 在指定的范围内,则返回 TRUE。“X BETWEEN Y AND Z”的结果等于“Y <= X AND X <= Z”,但 X 在第一项运算中只计算一次。
LIKE X [NOT] LIKE Y 检查第一个操作数 X 中的 STRING 是否与第二个操作数 Y 指定的模式相匹配。表达式可包含以下字符:
  • 百分号“%”会匹配任意数量的字符或字节
  • 下划线“_”与单个字符或字节相匹配
  • 您可以使用两个反斜线转义“\”、“_”或“%”,例如 "\\%"。如果使用原始字符串,则只需要一个反斜线,例如 r"\%"
IN 多个 - 见下文 如果右操作数为空,则返回 FALSE。如果左操作数为 NULL,则返回 NULL。如果右操作数包含 NULL,则返回 TRUE 或 NULL(永远不会返回 FALSE)。IN 运算符任一侧的参数是一般表达式。不是所有操作数都必须要是文本,但最常见的情况是在右侧使用文本。X 只计算一次。

在测试 STRUCT 数据类型的数个值是否相等时,可能出现一个或多个字段为 NULL 的情况。在此类情况下:

  • 如果所有非 NULL 字段值均相等,则比较运算会返回 NULL。
  • 如果任一 NULL 字段值不相等,则比较返回 False。

下表显示了在 STRUCT 数据类型具有带 NULL 值的字段时如何进行比较。

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

IN 运算符

IN 运算符支持以下语法:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

IN 运算符任一侧的参数是一般表达式。右侧表达式通常使用文本;但这并非必要条件。

以下表达式的语义:

x IN (y, z, ...)

被定义为等效于:

(x = y) OR (x = z) OR ...

并且子查询和数组形式具有相似定义。

x NOT IN ...

等效于:

NOT(x IN ...)

UNNEST 形式会像 FROM 子句中的 UNNEST 一样处理数组扫描:

x [NOT] IN UNNEST(<array expression>)

该形式通常与 ARRAY 参数一起使用。例如:

x IN UNNEST(@array_parameter)

注意NULL ARRAY 的处理方式与空白 ARRAY 相同。

如需详细了解如何使用此语法,请参阅数组主题。

使用 IN 运算符时,需遵循以下语义:

  • 右侧表达式为空的 IN 始终为 FALSE
  • 左侧表达式为 NULL 且右侧表达式非空的 IN 始终返回 NULL
  • IN 列表中具有 NULLIN 仅可返回 TRUE 或 NULL,永远不会返回 FALSE
  • NULL IN (NULL) 返回 NULL
  • IN UNNEST(<NULL array>) 返回 FALSE(而非 NULL
  • IN 列表中具有 NULLNOT IN 只能返回 FALSE 或 NULL,永远不会返回 TRUE

IN 可通过 Struct 构造函数语法与多部分键搭配使用。例如:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

如需详细了解此语法,请参阅“数据类型”主题的结构体类型部分。

IS 运算符

IS 运算符会针对其所测试的条件返回 TRUE 或 FALSE。与数学函数中定义的 IS_INF 和 IS_NAN 函数不同,IS 运算符永远不会返回 NULL(即使是对于 NULL 输入也是如此)。如果存在 NOT,则该运算符会对输出 BOOL 值执行取反运算。

函数语法 输入数据类型 结果数据类型 说明

X IS [NOT] NULL
任何值类型 BOOL 如果操作数 X 计算结果为 NULL,则返回 TRUE;否则返回 FALSE。

X IS [NOT] TRUE
BOOL BOOL 如果 BOOL 操作数计算结果为 TRUE,则返回 TRUE;否则返回 FALSE。

X IS [NOT] FALSE
BOOL BOOL 如果 BOOL 操作数计算结果为 FALSE,则返回 TRUE;否则返回 FALSE。

串联运算符

串联运算符用于将多个值合并为一个值。

函数语法 输入数据类型 结果数据类型

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>

条件表达式

条件表达式对其输入的计算顺序施加约束。实质上,它们按从左到右的顺序进行计算,具有短路机制,并且只计算所选的输出值。与此相反,在调用函数之前,对常规函数的所有输入都要进行计算。条件表达式中的短路机制可用于处理错误或调整性能。

CASE expr

CASE expr
  WHEN expr_to_match THEN result
  [ ... ]
  [ ELSE else_result ]
END

说明

expr 与每个连续 WHEN 子句的 expr_to_match 进行比较,并在该比较运算返回 true 的情况下返回第一个结果。其余 WHEN 子句和 else_result 均不进行计算。如果 expr = expr_to_match 比较针对所有 WHEN 子句都返回 false 或 NULL,则返回 else_result(如果有);如果没有,则返回 NULL。

exprexpr_to_match 可以是任何类型。它们必须可隐式强制转换为常用超类型,并对强制转换后的值执行相等性比较。可能存在多个 result 类型。resultelse_result 表达式必须可强制转换为常用超类型。

返回数据类型

result[, ...] 和 else_result 超类型。

示例

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 8 UNION ALL
  SELECT 60, 6 UNION ALL
  SELECT 50, 10)
SELECT A, B,
  CASE A
    WHEN 90 THEN 'red'
    WHEN 50 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 8  | blue   |
| 60 | 6  | green  |
| 50 | 10 | blue   |
+------------------+

CASE

CASE
  WHEN condition THEN result
  [ ... ]
  [ ELSE else_result ]
  END

说明

计算每个连续 WHEN 子句的条件,并在条件为 true 的情况下返回第一个结果;其余所有 WHEN 子句和 else_result 均不进行计算。如果所有条件均为 False 或 NULL,则返回 else_result(如果有);如果没有,则返回 NULL。

condition 必须是布尔表达式。可能存在多个 result 类型。resultelse_result 表达式必须可隐式强制转换为常用超类型。

返回数据类型

result[, ...] 和 else_result 超类型。

示例

WITH Numbers AS
 (SELECT 90 as A, 2 as B UNION ALL
  SELECT 50, 6 UNION ALL
  SELECT 20, 10)
SELECT A, B,
  CASE
    WHEN A > 60 THEN 'red'
    WHEN A > 30 THEN 'blue'
    ELSE 'green'
  END
  AS result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 90 | 2  | red    |
| 50 | 6  | blue   |
| 20 | 10 | green  |
+------------------+

COALESCE

COALESCE(expr[, ...])

说明

返回第一个非 NULL 表达式的值。其余表达式不进行计算。输入表达式可以是任何类型。可能存在多个输入表达式类型。所有输入表达式必须均可隐式强制转换为常用超类型。

返回数据类型

expr[, ...] 超类型。

示例

SELECT COALESCE('A', 'B', 'C') as result

+--------+
| result |
+--------+
| A      |
+--------+
SELECT COALESCE(NULL, 'B', 'C') as result

+--------+
| result |
+--------+
| B      |
+--------+

IF

IF(expr, true_result, else_result)

说明

如果 expr 为 true,则返回 true_result,否则返回 else_result。如果 expr 为 true,则不计算 else_result。如果 expr 为 false 或 NULL,则不计算 true_result

expr 必须是布尔表达式。true_resultelse_result 必须可强制转换为常用超类型。

返回数据类型

true_resultelse_result 超类型。

示例

WITH Numbers AS
 (SELECT 10 as A, 20 as B UNION ALL
  SELECT 50, 30 UNION ALL
  SELECT 60, 60)
SELECT
  A, B,
  IF( A<B, 'true', 'false') as result
FROM Numbers

+------------------+
| A  | B  | result |
+------------------+
| 10 | 20 | true   |
| 50 | 30 | false  |
| 60 | 60 | false  |
+------------------+

IFNULL

IFNULL(expr, null_result)

说明

如果 expr 是 NULL,则返回 null_result。否则返回 expr。如果 expr 不是 NULL,则不计算 null_result

exprnull_result 可以是任何类型,并且必须可隐式强制转换为常用超类型。相当于 COALESCE(expr, null_result)

返回数据类型

exprnull_result 超类型。

示例

SELECT IFNULL(NULL, 0) as result

+--------+
| result |
+--------+
| 0      |
+--------+
SELECT IFNULL(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

NULLIF

NULLIF(expr, expr_to_match)

说明

如果 expr = expr_to_match 为 true,则返回 NULL,否则返回 expr

exprexpr_to_match 必须可隐式强制转换为常用超类型,并且必须可比较。

NULLIF 不支持 STRUCT 类型。

返回数据类型

exprexpr_to_match 超类型。

示例

SELECT NULLIF(0, 0) as result

+--------+
| result |
+--------+
| NULL   |
+--------+
SELECT NULLIF(10, 0) as result

+--------+
| result |
+--------+
| 10     |
+--------+

表达式子查询

表达式子查询(即作为表达式使用的子查询)有四种类型。与列或表格相反,表达式子查询返回 NULL 或单个值,且必须用括号括起来。如需查看有关子查询的更完整讨论,请参阅子查询

子查询类型 结果数据类型 说明
标量 任何 T 类型 表达式中用英文括号括起的子查询(例如在 SELECT 列表或 WHERE 子句中)被解释为标量子查询。标量子查询中的 SELECT 列表只能有一个字段。如果子查询只返回一行,则该单一值就是标量子查询的结果。如果子查询未返回任何行,则标量子查询的值为 NULL。如果子查询返回多个行,则查询会失败并返回运行时错误。如果子查询是使用 SELECT AS STRUCT 编写的,则该查询可包含多个列,且返回的值是构造的 STRUCT。如果在不使用 SELECT AS 的情况下选择多个列,则会发生错误。
ARRAY ARRAY 可使用 SELECT AS STRUCT 构建 Struct 数组;相反地,如果在未使用 SELECT AS 的情况下选择多个列,则会发生错误。如果子查询未返回任何行,则会返回一个空的 ARRAY。永远不会返回 NULL ARRAY。
IN BOOL 出现在 IN 运算符后接的表达式中。子查询必须生成单个列,且该列的类型相等性必须与 IN 运算符左侧的表达式兼容。如果子查询未返回任何行,则返回 FALSE。 x IN () 等效于 x IN (value, value, ...)。如需了解完整的语义,请参阅比较运算符中的 IN 运算符。
EXISTS BOOL 如果子查询生成一行或多行,则返回 TRUE。如果子查询未生成任何行,则返回 FALSE。永远不会返回 NULL。与其他所有表达式子查询不同,该查询不存在有关列列表的规则。可选用任意数量的列,这不影响查询结果。

示例

以下表达式子查询示例假定 t.int_array 的类型为 ARRAY<INT64>

类型 子查询 结果数据类型 备注
标量 (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64,可能出现运行时错误  
(SELECT i FROM t.int_array i WHERE i=5) INT64,可能出现运行时错误  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY 使用 ARRAY_AGG 聚合函数返回 ARRAY。
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) 错误 由于存在多个列,因此返回一个错误
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) 大小为 1 的 ARRAY  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) 错误 由于存在多个列,因此返回一个错误
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY 创建由单字段 STRUCT 构成的 ARRAY
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY 返回具有匿名或重复字段的 STRUCT 的 ARRAY。
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<TypeName> 选择一个命名类型。假定 TypeName 是具有字段 a、b 和 c 的 STRUCT 类型。
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT 构造具有匿名或重复字段的 STRUCT。
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

调试函数

Cloud Spanner SQL 支持以下调试函数。

ERROR

ERROR(error_message)

说明

返回错误。error_message 参数为 STRING 类型。

Cloud Spanner SQL 处理 ERROR 的方式与处理可能导致错误的任何表达式相同,并没有针对计算顺序的特别保证。

返回数据类型

Cloud Spanner SQL 会根据上下文推断返回类型。

示例

在以下示例中,如果行的值与两个已定义值均不匹配,则查询将返回一条错误消息。

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

在以下示例中,Cloud Spanner SQL 可能会在 x > 0 条件之前或之后对 ERROR 函数求值,因为 Cloud Spanner SQL 通常不保证在 WHERE 子句条件之间进行排序,对于 ERROR 函数也不提供任何特别保证。

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

在下一个示例中,WHERE 子句会对 IF 条件求值,这能确保在条件失败时,Cloud Spanner SQL 仅对 ERROR 函数求值。

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1