标准 SQL 中的表达式、函数和运算符

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

函数调用规则

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

  • 对于接受数字类型的函数,如果一个操作数是浮点型,而另一个操作数是数字型,则系统会先将这两个操作数转换为 FLOAT64,然后再对该函数进行求值。
  • 如果操作数是 NULL,则结果为 NULL,IS 运算符除外。
  • 对于区分时区的函数(如函数说明中所述),如果您未指定时区,则系统会使用默认的“世界协调时间 (UTC)”时区。

SAFE. 前缀

语法

SAFE.function_name()

说明

如果一个函数以 SAFE. 前缀开头,该函数会返回 NULL 而非错误。SAFE. 前缀仅会防止带有前缀的函数本身返回错误,而不能防止在计算参数表达式时出现的错误。SAFE. 前缀仅会防止因函数输入值所导致的错误,例如“值超出范围”错误,但内部错误或系统错误等其他错误仍可能会发生。如果函数未返回错误,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          |
+-------------+

支持的函数

BigQuery 支持对大多数可能抛出错误的标量函数使用 SAFE. 前缀,此类函数包括 STRING 函数数学函数DATE 函数DATETIME 函数TIMESTAMP 函数。BigQuery 不支持对聚合函数分析函数用户定义的函数使用 SAFE. 前缀。

调用永久性用户定义的函数 (UDF)

创建永久性 UDF 后,您可以像调用其他任何函数一样调用该函数,并附加它所在的数据集的名称作为前缀。

语法

[`project_name`].dataset_name.function_name([parameter_value[, ...]])

如果用于调用 UDF 的项目与用于运行查询的项目不同,则需要使用 project_name

示例

以下示例创建了名为 multiply_by_three 的 UDF,并从同一项目中调用该函数。

CREATE FUNCTION my_dataset.multiply_by_three(x INT64) AS (x * 3);

SELECT my_dataset.multiply_by_three(5) AS result; -- returns 15

以下示例从其他项目中调用永久性 UDF。


CREATE `other_project`.other_dataset.other_function(x INT64, y INT64)
  AS (x * y * 2);

SELECT `other_project`.other_dataset.other_function(3, 4); --returns 24

转换规则

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

  • 类型转换属于显式转换,使用 CAST() 函数。
  • 强制转换属于隐式转换,BigQuery 在下述条件下会自动执行此转换。

此外还存在一些其他转换,它们有自己的函数名称,例如 PARSE_DATE()。如需详细了解这些函数,请参阅转换函数

比较图表

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

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

类型转换

大多数数据类型可以通过 CAST 函数从一种类型转换为另一种类型。在使用 CAST 时,如果 BigQuery 无法执行类型转换,查询就会失败。如果您想使自己的查询避免这些类型的错误,则可以使用 SAFE_CAST。如需详细了解 CASTSAFE_CAST 和其他类型转换函数的规则,请参阅转换函数

强制转换

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

字面量强制转换

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

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

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

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

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

参数强制转换

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

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

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

聚合函数

聚合函数是将组中的行汇总为单个值的函数。例如,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

以下部分介绍了 BigQuery 支持的聚合函数。

ANY_VALUE

ANY_VALUE(expression)  [OVER (...)]

说明

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

ANY_VALUE 的行为相当于指定了 RESPECT NULLS;系统会考虑且可以选择 expressionNULL 的行。

支持的参数类型

任意

可选子句

OVER:指定一个窗口。请参阅分析函数

返回的数据类型

与输入数据类型相匹配。

示例

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

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+
SELECT
  fruit,
  ANY_VALUE(fruit) OVER (ORDER BY LENGTH(fruit) ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+--------+-----------+
| fruit  | any_value |
+--------+-----------+
| pear   | pear      |
| apple  | pear      |
| banana | apple     |
+--------+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
          [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

说明

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

支持的参数类型

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 ARRAY_AGG() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  3. IGNORE NULLSRESPECT NULLS:如果指定了 IGNORE NULLS,则结果中不会包含 NULL 值。如果指定了 RESPECT NULLS,则结果中会包含 NULL 值。如果都没有指定,则结果中会包含 NULL 值。 如果最终查询结果中的数组包含 NULL 元素,则会引发错误。
  4. ORDER BY:指定值的顺序。
    • 对于每个排序键,默认排序方向均为 ASC
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小的可能值;也就是说,以 ASC 顺序排序时,NULL 会最先显示,而以 DESC 顺序排序时,NULL 则会最后显示。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果还同时指定了 DISTINCT,则排序键必须与 expression 相同。
    • 如果未指定 ORDER BY,则输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  5. LIMIT:指定结果中 expression 输入的最大数量。上限 n 必须是 INT64 常量。

返回的数据类型

ARRAY

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

示例

SELECT FORMAT("%T", 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 FORMAT("%T", 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 FORMAT("%T", 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] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(x ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [1, 1, 2, -2, -2, 2, 3] |
+-------------------------+
SELECT FORMAT("%T", ARRAY_AGG(x LIMIT 5)) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [2, 1, -2, 3, -2] |
+-------------------+
SELECT FORMAT("%T", ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 2)) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-----------+
| array_agg |
+-----------+
| [-2, 1]   |
+-----------+
SELECT
  x,
  FORMAT("%T", ARRAY_AGG(x) OVER (ORDER BY ABS(x))) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

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

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])

说明

连接类型为 ARRAY 的 expression 中的元素,并返回一个数组作为结果。此函数会忽略 NULL 输入数组,但会沿用非 NULL 输入数组中的 NULL 元素(不过,如果最终查询结果中的数组包含 NULL 元素,则会产生错误)。

支持的参数类型

ARRAY

可选子句

子句按照以下顺序应用

  1. ORDER BY:指定值的顺序。
    • 对于每个排序键,默认排序方向均为 ASC
    • 不支持数组排序,因此排序键不能与 expression 相同。
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小的可能值;也就是说,以 ASC 顺序排序时,NULL 会最先显示,而以 DESC 顺序排序时,NULL 则会最后显示。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果未指定 ORDER BY,则输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  2. LIMIT:指定结果中 expression 输入的最大数量。此上限适用于输入数组数量,而非数组中的元素数量。空数组会计为 1。系统不会计入 NULL 数组。 上限 n 必须是 INT64 常量。

返回的数据类型

ARRAY

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

示例

SELECT FORMAT("%T", 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] |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x))) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [5, 6, 7, 8, 9, 1, 2, 3, 4]       |
+-----------------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+--------------------------+
| array_concat_agg         |
+--------------------------+
| [1, 2, 3, 4, 5, 6]       |
+--------------------------+
SELECT FORMAT("%T", ARRAY_CONCAT_AGG(x ORDER BY ARRAY_LENGTH(x) LIMIT 2)) AS array_concat_agg FROM (
  SELECT [1, 2, 3, 4] AS x
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+------------------+
| array_concat_agg |
+------------------+
| [5, 6, 7, 8, 9]  |
+------------------+

AVG

AVG([DISTINCT] expression)  [OVER (...)]

说明

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

支持的参数类型

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 AVG() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回的数据类型

输入INT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

示例

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 |
+------+
SELECT
  x,
  AVG(x) OVER (ORDER BY x ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+------+
| x    | avg  |
+------+------+
| NULL | NULL |
| 0    | 0    |
| 2    | 1    |
| 4    | 3    |
| 4    | 4    |
| 5    | 4.5  |
+------+------+

BIT_AND

BIT_AND(expression)

说明

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

支持的参数类型

  • INT64

返回的数据类型

INT64

示例

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

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

BIT_OR

BIT_OR(expression)

说明

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

支持的参数类型

  • INT64

返回的数据类型

INT64

示例

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

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

BIT_XOR

BIT_XOR([DISTINCT] expression)

说明

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

支持的参数类型

  • INT64

可选子句

DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回的数据类型

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(*) [OVER (...)]

2. COUNT([DISTINCT] expression) [OVER (...)]

说明

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

支持的参数类型

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

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
  x,
  COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
  COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

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

+------+------------+---------+
| x    | count_star | count_x |
+------+------------+---------+
| NULL | 1          | 0       |
| 1    | 3          | 3       |
| 4    | 3          | 3       |
| 4    | 3          | 3       |
| 5    | 1          | 1       |
+------+------------+---------+

COUNTIF

COUNTIF(expression)  [OVER (...)]

说明

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

支持的参数类型

BOOL

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

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            |
+--------------+--------------+
SELECT
  x,
  COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+------+--------------+
| x    | num_negative |
+------+--------------+
| NULL | 0            |
| 0    | 1            |
| -2   | 1            |
| 3    | 1            |
| 4    | 0            |
| 5    | 0            |
| 6    | 1            |
| -7   | 2            |
| -10  | 2            |
+------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

说明

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

支持的参数类型

BOOL

返回数据类型

BOOL

示例

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

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

LOGICAL_OR

LOGICAL_OR(expression)

说明

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

支持的参数类型

BOOL

返回数据类型

BOOL

示例

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

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

MAX

MAX(expression)  [OVER (...)]

说明

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

支持的参数类型

任意数据类型(ARRAYSTRUCTGEOGRAPHY 除外)

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

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

示例

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

+-----+
| max |
+-----+
| 55  |
+-----+
SELECT x, MAX(x) OVER (PARTITION BY MOD(x, 2)) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | max  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 8    |
| 4    | 8    |
| 37   | 55   |
| 55   | 55   |
+------+------+

MIN

MIN(expression)  [OVER (...)]

说明

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

支持的参数类型

任意数据类型(ARRAYSTRUCTGEOGRAPHY 除外)

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

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

示例

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

+-----+
| min |
+-----+
| 4   |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+------+------+
| x    | min  |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8    | 4    |
| 4    | 4    |
| 37   | 37   |
| 55   | 37   |
+------+------+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter]  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n])
[OVER (...)]

说明

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

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

支持的参数类型

STRING BYTES

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 STRING_AGG() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  3. ORDER BY:指定值的顺序。
    • 对于每个排序键,默认排序方向均为 ASC
    • NULL:在 ORDER BY 子句的上下文中,NULL 是最小的可能值;也就是说,以 ASC 顺序排序时,NULL 会最先显示,而以 DESC 顺序排序时,NULL 则会最后显示。
    • 浮点数据类型:请参阅浮点语义,了解排序和分组。
    • 如果还同时指定了 DISTINCT,则排序键必须与 expression 相同。
    • 如果未指定 ORDER BY,则输出数组中的元素顺序是不确定的,这意味着您在每次使用此函数时可能得到不同的结果。
  4. LIMIT:指定结果中 expression 输入的最大数量。此上限适用于输入字符串数量,而非输入中的字符数量或字节数量。空字符串计为 1。系统不会计入 NULL 字符串。 上限 n 必须是 INT64 常量。

返回数据类型

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 |
+-----------------------+
SELECT STRING_AGG(fruit, " & " ORDER BY LENGTH(fruit)) AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

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

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

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

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

SUM

SUM([DISTINCT] expression)  [OVER (...)]

说明

返回非 NULL 值之和。

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

支持的参数类型

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

输入INT64NUMERICBIGNUMERICFLOAT64
输出INT64NUMERICBIGNUMERICFLOAT64

特殊情况:

如果输入仅包含 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
  x,
  SUM(x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+---+-----+
| x | sum |
+---+-----+
| 3 | 6   |
| 3 | 6   |
| 1 | 10  |
| 4 | 10  |
| 4 | 10  |
| 1 | 10  |
| 2 | 9   |
| 5 | 9   |
| 2 | 9   |
+---+-----+
SELECT
  x,
  SUM(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

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

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

统计聚合函数

BigQuery 支持以下统计聚合函数。

CORR

CORR(X1, X2)  [OVER (...)]

说明

返回一组数字对的皮尔逊相关系数。针对每个数字对,第一个数字都是因变量,第二个数字都是自变量。返回结果介于 -11 之间。结果如果为 0,则表示不相关。

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

此函数会忽略包含一个或多个 NULL 值的任何输入对。如果非 NULL 值的输入对少于两个,此函数会返回 NULL。

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

FLOAT64

COVAR_POP

COVAR_POP(X1, X2)  [OVER (...)]

说明

返回一组数字对的总体协方差。第一个数字是因变量;第二个数字是自变量。返回结果介于 -Inf+Inf 之间。

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

此函数会忽略包含一个或多个 NULL 值的任何输入对。如果不存在没有 NULL 值的输入对,此函数会返回 NULL。如果没有 NULL 值的输入对仅有一个,则此函数返回 0。

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

FLOAT64

COVAR_SAMP

COVAR_SAMP(X1, X2)  [OVER (...)]

说明

返回一组数字对的样本协方差。第一个数字是因变量;第二个数字是自变量。返回结果介于 -Inf+Inf 之间。

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

此函数会忽略包含一个或多个 NULL 值的任何输入对。如果非 NULL 值的输入对少于两个,此函数会返回 NULL。

可选子句

OVER:指定一个窗口。请参阅分析函数

返回数据类型

FLOAT64

STDDEV_POP

STDDEV_POP([DISTINCT] expression)  [OVER (...)]

说明

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

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

此函数会忽略任何 NULL 输入。如果所有输入均被忽略,此函数会返回 NULL。

如果此函数收到单个非 NULL 输入,它会返回 0

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 STDDEV_POP() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

FLOAT64

STDDEV_SAMP

STDDEV_SAMP([DISTINCT] expression)  [OVER (...)]

说明

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

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 STDDEV_SAMP() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

FLOAT64

STDDEV

STDDEV([DISTINCT] expression)  [OVER (...)]

说明

STDDEV_SAMP 的别名。

VAR_POP

VAR_POP([DISTINCT] expression)  [OVER (...)]

说明

返回值的总体(偏差)方差。返回结果介于 0+Inf 之间。

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

此函数会忽略任何 NULL 输入。如果所有输入均被忽略,此函数会返回 NULL。

如果此函数收到单个非 NULL 输入,它会返回 0

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 VAR_POP() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

FLOAT64

VAR_SAMP

VAR_SAMP([DISTINCT] expression)  [OVER (...)]

说明

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

支持所有数值类型。如果输入为 NUMERICBIGNUMERIC,则内部聚合稳定,并且最终输出转换为 FLOAT64。否则,在聚合之前,输入会转换为 FLOAT64,从而导致可能不稳定的结果。

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

可选子句

子句按照以下顺序应用

  1. OVER:指定一个窗口。请参阅分析函数。 此子句目前与 VAR_SAMP() 内的其他所有子句均不兼容。
  2. DISTINCTexpression 的每个不同值仅聚合到结果中一次。

返回数据类型

FLOAT64

VARIANCE

VARIANCE([DISTINCT] expression)  [OVER (...)]

说明

VAR_SAMP 的别名。

近似聚合函数

近似聚合函数在内存使用量和时间方面具有可扩展的特点,但其生成的结果为近似结果,而非准确结果。这些函数通常所需的内存少于精确聚合函数(例如 COUNT(DISTINCT ...)),但也会引入统计不确定性。因此,近似聚合适用于无法使用线性内存的大数据流以及已经取近似值的数据。

本部分的近似聚合函数直接作用于输入数据,而不是数据的中间估算值。这些函数不允许用户使用草图为估算值指定精度。如果您想使用草图指定精度,请参阅:

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(expression)

说明

返回 COUNT(DISTINCT expression) 的近似结果。返回值为统计预计值,不一定是实际值。

此函数的准确性低于 COUNT(DISTINCT expression),但在处理海量输入时性能较为出众。

支持的参数类型

任意数据类型(ARRAYSTRUCT 除外

返回的数据类型

INT64

示例

SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;

+-----------------+
| approx_distinct |
+-----------------+
| 5               |
+-----------------+

APPROX_QUANTILES

APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])

说明

返回一组 expression 值的近似边界,其中 number 表示要创建的分位数的数量。此函数返回一个数组,该数组中包含 number + 1 个元素,其中第一个元素是近似最小值,最后一个元素是近似最大值。

支持的参数类型

expression 可以是任意受支持的数据类型(ARRAYSTRUCT 除外

number 必须是 INT64。

可选子句

子句会按照以下顺序应用

  1. DISTINCTexpression 的每个不同值仅聚合到结果中一次。
  2. IGNORE NULLSRESPECT NULLS:如果指定了 IGNORE NULLS,则结果中不会包含 NULL 值。如果指定了 RESPECT NULLS,则结果中会包含 NULL 值。如果都没有指定,则从结果中排除 NULL 值。如果最终查询结果中的数组包含 NULL 元素,则会引发错误。

返回的数据类型

expression 参数指定的类型组成的 ARRAY。

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

示例

SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10]       |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;

+---------------+
| percentile_90 |
+---------------+
| 9             |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10]       |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10]    |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;

+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10]    |
+------------------+

APPROX_TOP_COUNT

APPROX_TOP_COUNT(expression, number)

说明

返回 expression 的近似顶级元素。number 参数指定返回的元素数量。

支持的参数类型

expression 可以是 GROUP BY 子句支持的任意数据类型。

number 必须是 INT64。

返回的数据类型

返回 ARRAY 类型的结果,其元素为 STRUCT 类型。该 STRUCT 包含两个字段。第一个字段(名为 value)包含一个输入值。第二个字段(名为 count)包含一个 INT64 值,指定 value 返回的次数。

如果不存在任何输入行,则返回 NULL

示例

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;

+-------------------------+
| approx_top_count        |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+

NULL 处理

APPROX_TOP_COUNT 不会忽略输入中的 NULL。例如:

SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;

+------------------------+
| approx_top_count       |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+

APPROX_TOP_SUM

APPROX_TOP_SUM(expression, weight, number)

说明

根据指定 weight 的总和,返回 expression 的近似顶级元素。number 参数指定返回的元素数量。

如果 weight 输入为负或者 NaN,此函数会返回错误。

支持的参数类型

expression 可以是 GROUP BY 子句支持的任意数据类型。

weight 必须是以下项之一:

  • INT64
  • NUMERIC
  • BIGNUMERIC
  • FLOAT64

number 必须是 INT64。

返回的数据类型

返回 ARRAY 类型的结果,其元素为 STRUCT 类型。该 STRUCT 包含两个字段:valuesumvalue 字段包含输入表达式的值。sum 字段与 weight 类型相同,是与 value 字段关联的输入权重的近似总和。

如果不存在任何输入行,则返回 NULL

示例

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
  STRUCT("apple" AS x, 3 AS weight),
  ("pear", 2),
  ("apple", 0),
  ("banana", 5),
  ("pear", 4)
]);

+--------------------------+
| approx_top_sum           |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+

NULL 处理

APPROX_TOP_SUM 不会忽略 expressionweight 参数的 NULL 值。

SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);

+-------------------------+
| approx_top_sum          |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);

+----------------------------+
| approx_top_sum             |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+

HyperLogLog++ 函数

HyperLogLog++ 算法 (HLL++) 根据草图估算基数。如果您不想使用草图且不需要自定义精度,请考虑使用具有系统定义精度的近似聚合函数

HLL++ 函数是近似聚合函数。与 COUNT(DISTINCT)精确聚合函数相比,近似聚合通常需要较少的内存,但是会引入统计不确定性。因此,HLL++ 函数适用于无法使用线性内存的大数据流以及已经取近似值的数据。

BigQuery 支持以下 HLL++ 函数:

HLL_COUNT.INIT

HLL_COUNT.INIT(input [, precision])

说明

这个聚合函数会接受一个或多个 input 值,并将这些值聚合到一个 HLL++ 草图。每个草图均使用 BYTES 数据类型表示。然后,您可以使用 HLL_COUNT.MERGEHLL_COUNT.MERGE_PARTIAL 合并这些草图。如果不需要合并,您可以使用 HLL_COUNT.EXTRACT 从草图中提取不同值的最终计数。

此函数支持可选的 precision 参数。此参数用于定义估计值的准确性;定义的准确性越高,处理草图或将草图存储在磁盘上所需的内存就越多。下表展示了允许的精度值、每组最大草图大小,以及典型精度的置信区间 (CI):

精度 最大草图大小 (KiB) 65% CI 95% CI 99% CI
10 1 ±3.25% ±6.50% ±9.75%
11 2 ±2.30% ±4.60% ±6.89%
12 4 ±1.63% ±3.25% ±4.88%
13 8 ±1.15% ±2.30% ±3.45%
14 16 ±0.81% ±1.63% ±2.44%
15(默认) 32 ±0.57% ±1.15% ±1.72%
16 64 ±0.41% ±0.81% ±1.22%
17 128 ±0.29% ±0.57% ±0.86%
18 256 ±0.20% ±0.41% ±0.61%
19 512 ±0.14% ±0.29% ±0.43%
20 1024 ±0.10% ±0.20% ±0.30%
21 2048 ±0.07% ±0.14% ±0.22%
22 4096 ±0.05% ±0.10% ±0.15%
23 8192 ±0.04% ±0.07% ±0.11%
24 16384 ±0.03% ±0.05% ±0.08%

如果输入是 NULL,则此函数会返回 NULL。

如需了解详情,请参阅 HyperLogLog 实践:一种先进基数估计算法的算法工程

支持的输入类型

INT64、NUMERIC、BIGNUMERIC、STRING、BYTES

返回类型

BYTES

示例

SELECT
  HLL_COUNT.INIT(respondent) AS respondents_hll,
  flavor,
  country
FROM UNNEST([
  STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
  (1, "Chocolate", "CH"),
  (2, "Chocolate", "US"),
  (2, "Strawberry", "US")])
GROUP BY flavor, country;

HLL_COUNT.MERGE

HLL_COUNT.MERGE(sketch)

说明

这个聚合函数会计算多组 HLL++ 草图的并集,并返回其基数。

每个 sketch 都必须具有相同的精度,并针对相同类型进行初始化。如果尝试合并具有不同精度的草图,或针对不同类型合并草图,则会引发错误。例如,您不能将使用 INT64 数据初始化的草图与使用 STRING 数据初始化的草图合并。

在合并草图时,此函数会忽略 NULL 值。如果合并了零行,或者只有 NULL 值发生合并,此函数会返回 0

支持的输入类型

BYTES

返回类型

INT64

示例

SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.MERGE_PARTIAL

HLL_COUNT.MERGE_PARTIAL(sketch)

说明

这个聚合函数会接受一个或多个 HLL++ sketch 输入,并将这些输入合并到一个新草图。

如果无输入或所有输入均为 NULL,此函数返回 NULL。

支持的输入类型

BYTES

返回类型

BYTES

示例

SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country)
GROUP BY flavor;

HLL_COUNT.EXTRACT

HLL_COUNT.EXTRACT(sketch)

说明

这个标量函数会提取一个 HLL++ 草图的基数估计值。

如果 sketch 是 NULL,则此函数返回的基数估计值为 0

支持的输入类型

BYTES

返回类型

INT64

示例

SELECT
  flavor,
  country,
  HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
  SELECT
    HLL_COUNT.INIT(respondent) AS respondents_hll,
    flavor,
    country
  FROM UNNEST([
    STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
    (1, "Chocolate", "CH"),
    (2, "Chocolate", "US"),
    (2, "Strawberry", "US")])
  GROUP BY flavor, country);

+------------+---------+-----------------+
| flavor     | country | num_respondents |
+------------+---------+-----------------+
| Vanilla    | CH      | 1               |
| Chocolate  | CH      | 1               |
| Chocolate  | US      | 1               |
| Strawberry | US      | 1               |
+------------+---------+-----------------+

关于 HLL++ 算法

HLL++ 算法HLL 算法进行了改进,可以更准确地估计极小和极大的基数。HLL++ 算法包括一个 64 位哈希函数、用于减少较小基数估计值内存要求的稀疏表示法,以及用于较小基数估计值的经验偏差校正。

关于草图

草图是大型数据流的汇总。您可以从草图中提取统计信息以估算原始数据的特定统计数据,或合并草图来汇总多个数据流。草图具有以下特点:

  • 它将原始数据压缩为固定内存表示法。
  • 它渐近地小于输入。
  • 它是内存中的次线性数据结构的序列化形式。
  • 它要求的内存通常少于用于创建它的输入。

草图可以与其他系统集成。例如,您可以在外部应用(如 Cloud DataflowApache Spark)中构建草图,并在 BigQuery 中使用它们,反之亦然。草图还可以为 COUNT(DISTINCT) 等非叠加函数构建中间聚合。

编号函数

以下部分介绍了 BigQuery 支持的编号函数。编号函数是分析函数的一部分。 如需了解分析函数的工作原理,请参阅分析函数概念。如需了解编号函数的工作原理,请参阅编号函数概念

OVER 子句要求:

  • PARTITION BY:可选。
  • ORDER BY:必需(ROW_NUMBER() 除外)。
  • window_frame_clause:禁止。

RANK

说明

返回排序分区中各行的序数(从 1 开始)。所有对等行均获得相同的排序值。下一行或下一组对等行获得的排序值按照具有先前排序值的对等数量递增,而非按 DENSE_RANK 递增,后者始终按 1 递增。

支持的参数类型

INT64

DENSE_RANK

说明

返回窗口分区中各行的序数(从 1 开始)排名。所有对等行获得相同的排名值,后续排名值按 1 递增。

支持的参数类型

INT64

PERCENT_RANK

说明

返回定义为 (RK-1)/(NR-1) 的百分位排名,其中 RK 是行的 RANK,NR 是分区内的行数。如果 NR=1,则返回 0。

支持的参数类型

FLOAT64

CUME_DIST

说明

返回一行的相对排名,定义为 NP/NR。NP 定义为在当前行之前或与当前行对等的行数。NR 是分区内的行数。

支持的参数类型

FLOAT64

NTILE

NTILE(constant_integer_expression)

说明

此函数根据行排序将行划分为 constant_integer_expression 个存储分区,返回分配给各行的存储分区编号(从 1 开始)。存储分区内的行数最多可以相差 1。从存储分区 1 开始,为每个存储分区分配一个剩余值(剩余的行数除以存储分区数)。如果 constant_integer_expression 计算结果为 NULL、0 或负数,则会引发错误。

支持的参数类型

INT64

ROW_NUMBER

说明

不需要 ORDER BY 子句。返回每个排序分区中各行的顺序行序号(从 1 开始)。如果未指定 ORDER BY 子句,则结果是非确定性的。

支持的参数类型

INT64

位函数

BigQuery 支持以下位函数。

BIT_COUNT

BIT_COUNT(expression)

说明

输入 expression 必须是整数或 BYTES。

返回在输入 expression 中设置的位数。如果是带符号整数,则为补码的位数。

返回数据类型

INT64

示例

SELECT a, BIT_COUNT(a) AS a_bits, FORMAT("%T", b) as b, BIT_COUNT(b) AS b_bits
FROM UNNEST([
  STRUCT(0 AS a, b'' AS b), (0, b'\x00'), (5, b'\x05'), (8, b'\x00\x08'),
  (0xFFFF, b'\xFF\xFF'), (-2, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFE'),
  (-1, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF'),
  (NULL, b'\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF')
]) AS x;

+-------+--------+---------------------------------------------+--------+
| a     | a_bits | b                                           | b_bits |
+-------+--------+---------------------------------------------+--------+
| 0     | 0      | b""                                         | 0      |
| 0     | 0      | b"\x00"                                     | 0      |
| 5     | 2      | b"\x05"                                     | 2      |
| 8     | 1      | b"\x00\x08"                                 | 1      |
| 65535 | 16     | b"\xff\xff"                                 | 16     |
| -2    | 63     | b"\xff\xff\xff\xff\xff\xff\xff\xfe"         | 63     |
| -1    | 64     | b"\xff\xff\xff\xff\xff\xff\xff\xff"         | 64     |
| NULL  | NULL   | b"\xff\xff\xff\xff\xff\xff\xff\xff\xff\xff" | 80     |
+-------+--------+---------------------------------------------+--------+

转换函数

BigQuery 支持以下转换函数。这些数据类型转换是显式的,但某些转换是隐式的。您可以在此处详细了解隐式和显式转换。

CAST 概览

CAST(expression AS typename)

说明

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

在使用 CAST 时,如果 BigQuery 无法执行类型转换,查询就会失败。如果您想使自己的查询避免这些类型的错误,则可以使用 SAFE_CAST

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

示例

如果 x1,则以下查询的结果为 "true";如果 x 为任何其他非 NULL 值,则结果为 "false";如果 xNULL,则结果为 NULL

CAST(x=1 AS STRING)

CAST AS ARRAY

CAST(expression AS ARRAY<element_type>)

说明

BigQuery 支持转换为 ARRAY。expression 参数可表示以下数据类型的表达式:

  • ARRAY

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
ARRAY ARRAY 两者必须是完全相同的 ARRAY 类型。

CAST AS BIGNUMERIC

CAST(expression AS BIGNUMERIC)

说明

BigQuery 支持转换为 BIGNUMERIC。expression 参数可表示以下数据类型的表达式:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
FLOAT64 BIGNUMERIC 浮点数将向远离 0 方向按中间数舍入。如果对 NaN+inf-inf 进行类型转换,则系统会返回错误。如果对 BIGNUMERIC 范围以外的值进行类型转换,则系统会返回溢出错误。
STRING BIGNUMERIC STRING 中包含的数字字面量不得超出 BIGNUMERIC 类型的最大精度或范围,否则将发生错误。如果小数点后的位数超过 38 位,则生成的 BIGNUMERIC 值会向远离 0 方向按中间数舍入,从而在小数点后保留 38 位数。

CAST AS BOOL

CAST(expression AS BOOL)

说明

BigQuery 支持转换为 BOOL。expression 参数可表示以下数据类型的表达式:

  • INT64
  • BOOL
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
INT64 BOOL 如果 x0,则返回 FALSE;否则返回 TRUE
STRING BOOL 如果 x"true",则返回 TRUE;如果 x"false",则返回 FALSE
x 的所有其他值均无效且抛出错误,而不会转换为 BOOL。
转换为 BOOL 类型时,STRING 类型不区分大小写。

CAST AS BYTES

CAST(expression AS BYTES)

说明

BigQuery 支持转换为 BYTES 类型。expression 参数可表示以下数据类型的表达式:

  • BYTES
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRING BYTES STRING 通过 UTF-8 编码转换为 BYTES。例如,在转换为 BYTES 时,字符串“©”会转为一个包含十六进制值 C2 和 A9 的 2 字节序列。

CAST AS DATE

CAST(expression AS DATE)

说明

BigQuery 支持转换为 DATE 类型。expression 参数可表示以下数据类型的表达式:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRING DATE 字符串转换为日期时,字符串必须遵循所支持的日期字面量格式,但与时区无关。如果字符串表达式无效或其表示的日期超出了所支持的最小/最大范围,则出现错误。
TIMESTAMP DATE 如果从时间戳转换为日期,则实际自默认时区起截断时间戳。

CAST AS DATETIME

CAST(expression AS DATETIME)

说明

BigQuery 支持转换为 DATETIME 类型。expression 参数可表示以下数据类型的表达式:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRING DATETIME 字符串转换为日期时间时,字符串必须遵循所支持的日期时间字面量格式,但与时区无关。如果字符串表达式无效或其表示的日期时间超出了所支持的最小/最大范围,则出现错误。
TIMESTAMP DATETIME 如果从时间戳转换为日期时间,则实际自默认时区起截断时间戳。

CAST AS FLOAT64

CAST(expression AS FLOAT64)

说明

BigQuery 支持转换为浮点类型。expression 参数可表示以下数据类型的表达式:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
INT64 FLOAT64 返回一个接近但可能不精确的浮点值。
NUMERIC FLOAT64 NUMERIC 类型将转换为最接近的浮点数,可能有一定的精度损失。
BIGNUMERIC FLOAT64 BIGNUMERIC 类型将转换为最接近的浮点数,可能有一定的精度损失。
STRING FLOAT64 以浮点值的形式返回 x,将其解释为具有与有效的浮点字面量相同的形式。也支持从 "[+,-]inf" 转换为 [,-]Infinity、从 "[+,-]infinity" 转换为 [,-]Infinity 以及从 "[+,-]nan" 转换为 NaN。转换不区分大小写。

CAST AS INT64

CAST(expression AS INT64)

说明

BigQuery 支持转换为整数类型。expression 参数可表示以下数据类型的表达式:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
FLOAT64 INT64 返回最接近的整数值。
中间数(例如 1.5 或 -0.5)向远离 0 的方向舍入。
BOOL INT64 如果 xTRUE,则返回 1;否则返回 0
STRING INT64 十六进制字符串可以转换为整数。例如,0x123291,或 -0x123-291

示例

如果您使用十六进制字符串 (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       |
+-----------+------------+

CAST AS NUMERIC

CAST(expression AS NUMERIC)

说明

BigQuery 支持转换为 NUMERIC。expression 参数可表示以下数据类型的表达式:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
FLOAT64 NUMERIC 浮点数将向远离 0 方向按中间数舍入。如果对 NaN+inf-inf 进行类型转换,则系统会返回错误。如果对 NUMERIC 范围以外的值进行类型转换,则系统会返回溢出错误。
STRING NUMERIC STRING 中包含的数字字面量不得超出 NUMERIC 类型的最大精度或范围,否则将发生错误。如果小数点后的位数超过 9 位,则生成的 NUMERIC 值会向远离 0 方向按中间数舍入,从而在小数点后保留 9 位数。

CAST AS STRING

CAST(expression AS STRING)

说明

BigQuery 支持转换为 STRING 类型。expression 参数可表示以下数据类型的表达式:

  • INT64
  • FLOAT64
  • NUMERIC
  • BIGNUMERIC
  • BOOL
  • BYTES
  • TIME
  • DATE
  • DATETIME
  • TIMESTAMP
  • STRING

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
FLOAT64 STRING 返回一个近似的字符串表示形式。
BOOL STRING 如果 xTRUE,则返回 "true";否则返回 "false"
BYTES STRING 返回解释为 UTF-8 STRING 类型的 x
例如,在转换为 STRING 类型时,BYTES 类型的字面量 b'\xc2\xa9' 会解释为 UTF-8 字符并且转为 Unicode 字符“©”。
如果 x 不是有效的 UTF-8 字符,则引发错误。
TIME STRING 时间类型转换为字符串与时区无关,并采用 HH:MM:SS 形式。
DATE STRING 日期类型转换为字符串与时区无关,并采用 YYYY-MM-DD 形式。
DATETIME STRING 日期时间类型转换为字符串与时区无关,并采用 YYYY-MM-DD HH:MM:SS 形式。
TIMESTAMP STRING 时间戳类型转换为字符串时,使用默认时区“世界协调时间 (UTC)”解释时间戳。所生成的亚秒位数取决于亚秒部分中尾随零的数量:CAST 函数将截取 0、3 或 6 位数。

示例

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

+---------------+
| current_date  |
+---------------+
| 2021-03-09    |
+---------------+

CAST AS STRUCT

CAST(expression AS STRUCT)

说明

BigQuery 支持转换为 STRUCT 类型。expression 参数可表示以下数据类型的表达式:

  • STRUCT

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRUCT STRUCT 仅在满足以下条件时可用:
  1. 这两个 STRUCT 的字段数量相同。
  2. 原始 STRUCT 字段类型可显式转换为相应的目标 STRUCT 字段类型(由字段顺序定义,而不是由字段名称定义)。

CAST AS TIME

CAST(expression AS TIME)

说明

BigQuery 支持转换为 TIME 类型。expression 参数可表示以下数据类型的表达式:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRING TIME 字符串转换为时间时,字符串必须遵循所支持的时间字面量格式,但与时区无关。如果字符串表达式无效或其表示的时间超出了所支持的最小/最大范围,则出现错误。

CAST AS TIMESTAMP

CAST(expression AS TIMESTAMP)

说明

BigQuery 支持转换为 TIMESTAMP 类型。expression 参数可表示以下数据类型的表达式:

  • STRING
  • TIME
  • DATETIME
  • TIMESTAMP

转换规则

发件人 目标类型 x 进行类型转换时适用的规则
STRING TIMESTAMP 字符串转换为时间戳时,string_expression 必须遵循所支持的时间戳字面量格式,否则将出现运行时错误。string_expression 本身可能包含时区。
如果 string_expression 中存在时区,则转换时使用该时区,否则使用默认时区“世界协调时间 (UTC)”。如果字符串少于 6 位数,则将其隐式加宽。
如果 string_expression 无效,其亚秒位数超过 6 位数(即精度高于微秒),或者所表示的时间超出了支持的时间戳范围,则会引发错误。
DATE TIMESTAMP 如果从日期转换为时间戳,则从默认时区“世界协调时间 (UTC)”的午夜(一天之始)起解释 date_expression
DATETIME TIMESTAMP 如果从日期时间转换为时间戳,则从默认时区“世界协调时间 (UTC)”的午夜(一天之始)起解释 datetime_expression

示例

以下示例将字符串格式的时间戳转换为时间戳:

SELECT CAST("2020-06-02 17:00:53.110+00:00" AS TIMESTAMP) AS as_timestamp

-- Results depend upon where this query was executed.
+-----------------------------+
| as_timestamp                |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+

SAFE_CAST

SAFE_CAST(expression AS typename)

说明

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

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

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

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

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

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

其他转换函数

您可以在文档的其他地方详细了解以下转换函数:

转换函数 发件人 To
ARRAY_TO_STRING ARRAY STRING
DATE 各种数据类型 DATE
DATETIME 各种数据类型 DATETIME
FROM_BASE32 STRING BYTES
FROM_BASE64 STRING BYTES
FROM_HEX STRING BYTES
PARSE_DATE STRING DATE
PARSE_DATETIME STRING DATETIME
PARSE_TIME STRING TIME
PARSE_TIMESTAMP STRING TIMESTAMP
SAFE_CONVERT_BYTES_TO_STRING BYTES STRING
STRING TIMESTAMP STRING
TIME 各种数据类型 TIME
TIMESTAMP 各种数据类型 TIMESTAMP
TO_BASE32 BYTES STRING
TO_BASE64 BYTES STRING
TO_HEX BYTES STRING

数学函数

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

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

ABS

ABS(X)

说明

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

X ABS(X)
25 25
-25 25
+inf +inf
-inf +inf

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出INT64NUMERICBIGNUMERICFLOAT64

SIGN

SIGN(X)

说明

分别对负参数、零和正参数返回 -10+1。对于浮点参数,此函数不区分正零和负零。

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出INT64NUMERICBIGNUMERICFLOAT64

IS_INF

IS_INF(X)

说明

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

X IS_INF(X)
+inf TRUE
-inf TRUE
25 FALSE

IS_NAN

IS_NAN(X)

说明

如果值为 NaN 值,则返回 TRUE

X IS_NAN(X)
NaN TRUE
25 FALSE

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

说明

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

X IEEE_DIVIDE(X, Y)
20.0 4.0 5.0
0.0 25.0 0.0
25.0 0.0 +inf
-25.0 0.0 -inf
0.0 0.0 NaN
0.0 NaN NaN
NaN 0.0 NaN
+inf +inf NaN
-inf -inf NaN

RAND

RAND()

说明

在范围 [0, 1)(包含 0,不包含 1)内生成 FLOAT64 类型的伪随机值。

SQRT

SQRT(X)

说明

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

X SQRT(X)
25.0 5.0
+inf +inf
X < 0 错误

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

POW

POW(X, Y)

说明

返回 X 的 Y 次幂的值。如果结果下溢且不可表示,则函数返回值 0。

X POW(X, Y)
2.0 3.0 8.0
1.0 任何值(包括 NaN 1.0
任何值(包括 NaN 0 1.0
-1.0 +inf 1.0
-1.0 -inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0.0
ABS(X) < 1 +inf 0.0
ABS(X) > 1 +inf +inf
-inf Y < 0 0.0
-inf Y > 0 如果 Y 为奇整数,则为 -inf;否则为 +inf
+inf Y < 0 0
+inf Y > 0 +inf
有限值 < 0 非整数 错误
0 有限值 < 0 错误

返回数据类型

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

输入INT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

POWER

POWER(X, Y)

说明

相当于 POW(X, Y)

EXP

EXP(X)

说明

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

X EXP(X)
0.0 1.0
+inf +inf
-inf 0.0

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

LN

LN(X)

说明

计算 X 的自然对数。如果 X 小于或等于 0,则生成错误。

X LN(X)
1.0 0.0
+inf +inf
X < 0 错误

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

LOG

LOG(X [, Y])

说明

如果只存在 X,则 LOG 相当于 LN。如果还存在 Y,则 LOG 计算以 Y 为底数的 X 的对数。

X Y LOG(X, Y)
100.0 10.0 2.0
-inf 任意值 NaN
任意值 +inf NaN
+inf 0.0 < Y < 1.0 -inf
+inf Y > 1.0 +inf
X <= 0 任意值 错误
任意值 Y <= 0 错误
任意值 1.0 错误

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

LOG10

LOG10(X)

说明

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

X LOG10(X)
100.0 2.0
-inf NaN
+inf NaN
X <= 0 错误

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

GREATEST

GREATEST(X1,...,XN)

说明

根据比较得出更大值,返回 X1,...,XN 中的最大值。 如果 X1,...,XN 的任何部分是 NULL,则返回值为 NULL

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

返回数据类型

输入值的数据类型。

LEAST

LEAST(X1,...,XN)

说明

根据比较得出更小值,返回 X1,...,XN 中的最小值。 如果 X1,...,XN 的任何部分是 NULL,则返回值为 NULL

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

返回数据类型

输入值的数据类型。

DIV

DIV(X, Y)

说明

返回 X 被 Y 整除的结果。如果除数为 0,则返回一个错误。除数为 -1 时可能溢出。

X DIV(X, Y)
20 4 5
0 20 0
20 0 错误

返回数据类型

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

INPUTINT64NUMERICBIGNUMERIC
INT64INT64NUMERICBIGNUMERIC
NUMERICNUMERICNUMERICBIGNUMERIC
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

说明

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

XSAFE_DIVIDE(X, Y)
2045
0200
200NULL

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

说明

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

XSAFE_MULTIPLY(X, Y)
20480

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

说明

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

XSAFE_NEGATE(X)
+1-1
-1+1
00

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出INT64NUMERICBIGNUMERICFLOAT64

SAFE_ADD

SAFE_ADD(X, Y)

说明

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

XSAFE_ADD(X, Y)
549

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

说明

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

XSAFE_SUBTRACT(X, Y)
541

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

MOD

MOD(X, Y)

说明

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

X MOD(X, Y)
25 12 1
25 0 错误

返回数据类型

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

INPUTINT64NUMERICBIGNUMERIC
INT64INT64NUMERICBIGNUMERIC
NUMERICNUMERICNUMERICBIGNUMERIC
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERIC

ROUND

ROUND(X [, N])

说明

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

X ROUND(X)
2.0 2.0
2.3 2.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -3.0
-2.5 -3.0
0 0
+inf +inf
-inf -inf
NaN NaN

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

TRUNC

TRUNC(X [, N])

说明

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

X TRUNC(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
+inf +inf
-inf -inf
NaN NaN

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

CEIL

CEIL(X)

说明

返回不小于 X 的最小整数值。

X CEIL(X)
2.0 2.0
2.3 3.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
+inf +inf
-inf -inf
NaN NaN

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

CEILING

CEILING(X)

说明

相当于 CEIL(X)

FLOOR

FLOOR(X)

说明

返回不大于 X 的最大整数值。

X FLOOR(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -3.0
-2.8 -3.0
-2.5 -3.0
0 0
+inf +inf
-inf -inf
NaN NaN

返回数据类型

INPUTINT64NUMERICBIGNUMERICFLOAT64
输出FLOAT64NUMERICBIGNUMERICFLOAT64

COS

COS(X)

说明

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

X COS(X)
+inf NaN
-inf NaN
NaN NaN

COSH

COSH(X)

说明

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

X COSH(X)
+inf +inf
-inf +inf
NaN NaN

ACOS

ACOS(X)

说明

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

X ACOS(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 错误
X > 1 错误

ACOSH

ACOSH(X)

说明

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

X ACOSH(X)
+inf +inf
-inf NaN
NaN NaN
X < 1 错误

SIN

SIN(X)

说明

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

X SIN(X)
+inf NaN
-inf NaN
NaN NaN

SINH

SINH(X)

说明

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

X SINH(X)
+inf +inf
-inf -inf
NaN NaN

ASIN

ASIN(X)

说明

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

X ASIN(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 错误
X > 1 错误

ASINH

ASINH(X)

说明

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

X ASINH(X)
+inf +inf
-inf -inf
NaN NaN

TAN

TAN(X)

说明

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

X TAN(X)
+inf NaN
-inf NaN
NaN NaN

TANH

TANH(X)

说明

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

X TANH(X)
+inf 1.0
-inf -1.0
NaN NaN

ATAN

ATAN(X)

说明

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

X ATAN(X)
+inf π/2
-inf -π/2
NaN NaN

ATANH

ATANH(X)

说明

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

X ATANH(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 错误
X > 1 错误

ATAN2

ATAN2(X, Y)

说明

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

X ATAN2(X, Y)
NaN 任意值 NaN
任意值 NaN NaN
0.0 0.0 0.0
正的有限值 -inf π
负的有限值 -inf
有限值 +inf 0.0
+inf 有限值 π/2
-inf 有限值 -π/2
+inf -inf ¾π
-inf -inf -¾π
+inf +inf π/4
-inf +inf -π/4

RANGE_BUCKET

RANGE_BUCKET(point, boundaries_array)

说明

RANGE_BUCKET 会扫描已排序的数组,并返回某个点最后一次出现的位置(从 0 开始)。如果您需要将数据分组以构建分区、直方图,业务定义的规则等,此函数将非常有用。

RANGE_BUCKET 遵循以下规则:

  • 如果数组中存在该点,则返回下一个较大值的索引。

    RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
    RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
    
  • 如果数组中不存在该点,但该点落在两个值之间,则返回较大值的索引。

    RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
    
  • 如果该点小于数组中的第一个值,则返回 0。

    RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
    
  • 如果该点大于或等于数组中的最后一个值,则返回此数组的长度。

    RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
    
  • 如果数组为空,则返回 0。

    RANGE_BUCKET(80, []) -- 0 is return value
    
  • 如果该点为 NULLNaN,则返回 NULL

    RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
    
  • 点和数组的数据类型必须兼容。

    RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
    RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
    RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
    

以下情况会导致函数运行失败:

  • 数组中包含 NaNNULL 值。

    RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
    
  • 数组未按升序排序。

    RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
    

参数

  • point:一个常规值。
  • boundaries_array:一组常规值。

返回值

INT64

示例

在名为 students 的表中,根据学生的年龄,检查每个 age_group 范围中有多少条记录:

  • age_group 0(年龄在 10 岁以下)
  • age_group 1(年龄在 10 岁到 20 岁之间,不含 20 岁)
  • age_group 2(年龄在 20 岁到 30 岁之间,不含 30 岁)
  • age_group 3(年龄在 30 岁以上,含 30 岁)
WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age UNION ALL
  SELECT 32 AS age UNION ALL
  SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+

以下部分介绍了 BigQuery 支持的导航函数。导航函数是分析函数的子集。如需了解分析函数的工作原理,请参阅分析函数概念。如需了解导航函数的工作原理,请参阅导航函数概念

FIRST_VALUE

FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

说明

返回当前窗口框架中第一行的 value_expression 值。

除非存在 IGNORE NULLS,否则此函数在计算中包含 NULL 值。如果存在 IGNORE NULLS,此函数会从计算中排除 NULL 值。

支持的参数类型

value_expression 可以是表达式支持的任意返回数据类型。

返回数据类型

类型与 value_expression 相同。

示例

以下示例计算各部门的最快时间。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  TIMESTAMP_DIFF(finish_time, fastest_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  FIRST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | fastest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 0                |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 436              |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 891              |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 956              |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 1109             |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 0                |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 426              |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 691              |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 733              |
+-----------------+-------------+----------+--------------+------------------+

LAST_VALUE

LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS])

说明

返回当前窗口框架中最后一行的 value_expression 值。

除非存在 IGNORE NULLS,否则此函数在计算中包含 NULL 值。如果存在 IGNORE NULLS,此函数会从计算中排除 NULL 值。

支持的参数类型

value_expression 可以是表达式支持的任意返回数据类型。

返回数据类型

类型与 value_expression 相同。

示例

以下示例计算各部门的最慢时间。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', slowest_time) AS slowest_time,
  TIMESTAMP_DIFF(slowest_time, finish_time, SECOND) AS delta_in_seconds
FROM (
  SELECT name,
  finish_time,
  division,
  LAST_VALUE(finish_time)
    OVER (PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS slowest_time
  FROM finishers);

+-----------------+-------------+----------+--------------+------------------+
| name            | finish_time | division | slowest_time | delta_in_seconds |
+-----------------+-------------+----------+--------------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | 0                |
| Sophia Liu      | 02:51:45    | F30-34   | 03:10:14     | 1109             |
| Nikki Leith     | 02:59:01    | F30-34   | 03:10:14     | 673              |
| Jen Edwards     | 03:06:36    | F30-34   | 03:10:14     | 218              |
| Meghan Lederer  | 03:07:41    | F30-34   | 03:10:14     | 153              |
| Lauren Reasoner | 03:10:14    | F30-34   | 03:10:14     | 0                |
| Lisa Stelzner   | 02:54:11    | F35-39   | 03:06:24     | 733              |
| Lauren Matthews | 03:01:17    | F35-39   | 03:06:24     | 307              |
| Desiree Berry   | 03:05:42    | F35-39   | 03:06:24     | 42               |
| Suzy Slane      | 03:06:24    | F35-39   | 03:06:24     | 0                |
+-----------------+-------------+----------+--------------+------------------+

NTH_VALUE

NTH_VALUE (value_expression, constant_integer_expression [{RESPECT | IGNORE} NULLS])

说明

返回当前窗口框架中第 N 行的 value_expression 值,其中的 N 由 constant_integer_expression 定义。如果不存在此类行,则返回 NULL。

除非存在 IGNORE NULLS,否则此函数在计算中包含 NULL 值。如果存在 IGNORE NULLS,此函数会从计算中排除 NULL 值。

支持的参数类型

  • value_expression 可以是表达式支持的任意返回数据类型。
  • constant_integer_expression 可以是返回整数的任意常量表达式。

返回数据类型

类型与 value_expression 相同。

示例

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  FORMAT_TIMESTAMP('%X', finish_time) AS finish_time,
  division,
  FORMAT_TIMESTAMP('%X', fastest_time) AS fastest_time,
  FORMAT_TIMESTAMP('%X', second_fastest) AS second_fastest
FROM (
  SELECT name,
  finish_time,
  division,finishers,
  FIRST_VALUE(finish_time)
    OVER w1 AS fastest_time,
  NTH_VALUE(finish_time, 2)
    OVER w1 as second_fastest
  FROM finishers
  WINDOW w1 AS (
    PARTITION BY division ORDER BY finish_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING));

+-----------------+-------------+----------+--------------+----------------+
| name            | finish_time | division | fastest_time | second_fastest |
+-----------------+-------------+----------+--------------+----------------+
| Carly Forte     | 03:08:58    | F25-29   | 03:08:58     | NULL           |
| Sophia Liu      | 02:51:45    | F30-34   | 02:51:45     | 02:59:01       |
| Nikki Leith     | 02:59:01    | F30-34   | 02:51:45     | 02:59:01       |
| Jen Edwards     | 03:06:36    | F30-34   | 02:51:45     | 02:59:01       |
| Meghan Lederer  | 03:07:41    | F30-34   | 02:51:45     | 02:59:01       |
| Lauren Reasoner | 03:10:14    | F30-34   | 02:51:45     | 02:59:01       |
| Lisa Stelzner   | 02:54:11    | F35-39   | 02:54:11     | 03:01:17       |
| Lauren Matthews | 03:01:17    | F35-39   | 02:54:11     | 03:01:17       |
| Desiree Berry   | 03:05:42    | F35-39   | 02:54:11     | 03:01:17       |
| Suzy Slane      | 03:06:24    | F35-39   | 02:54:11     | 03:01:17       |
+-----------------+-------------+----------+--------------+----------------+

LEAD

LEAD (value_expression[, offset [, default_expression]])

说明

返回后续行的 value_expression 值。更改 offset 值会改变所返回的后续行;默认值是 1,表示窗口框架中的下一行。如果 offset 是 NULL 或负值,则会发生错误。

如果窗口框架中没有指定偏移量的行,则使用可选的 default_expression。此表达式必须是常量表达式,其类型必须可隐式强制转换为 value_expression 类型。如果未指定,则 default_expression 默认为 NULL。

支持的参数类型

  • value_expression 可以是表达式支持的任意返回数据类型。
  • offset 必须是非负整数字面量或参数。
  • default_expression 必须兼容值表达式类型。

返回数据类型

类型与 value_expression 相同。

示例

以下示例展示了 LEAD 函数的基本用法。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;

+-----------------+-------------+----------+-----------------+
| name            | finish_time | division | followed_by     |
+-----------------+-------------+----------+-----------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL            |
| Sophia Liu      | 02:51:45    | F30-34   | Nikki Leith     |
| Nikki Leith     | 02:59:01    | F30-34   | Jen Edwards     |
| Jen Edwards     | 03:06:36    | F30-34   | Meghan Lederer  |
| Meghan Lederer  | 03:07:41    | F30-34   | Lauren Reasoner |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL            |
| Lisa Stelzner   | 02:54:11    | F35-39   | Lauren Matthews |
| Lauren Matthews | 03:01:17    | F35-39   | Desiree Berry   |
| Desiree Berry   | 03:05:42    | F35-39   | Suzy Slane      |
| Suzy Slane      | 03:06:24    | F35-39   | NULL            |
+-----------------+-------------+----------+-----------------+

下一个示例使用可选的 offset 参数。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | NULL             |
| Lauren Reasoner | 03:10:14    | F30-34   | NULL             |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | NULL             |
| Suzy Slane      | 03:06:24    | F35-39   | NULL             |
+-----------------+-------------+----------+------------------+

以下示例使用默认值替换 NULL 值。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LEAD(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody           |
| Sophia Liu      | 02:51:45    | F30-34   | Jen Edwards      |
| Nikki Leith     | 02:59:01    | F30-34   | Meghan Lederer   |
| Jen Edwards     | 03:06:36    | F30-34   | Lauren Reasoner  |
| Meghan Lederer  | 03:07:41    | F30-34   | Nobody           |
| Lauren Reasoner | 03:10:14    | F30-34   | Nobody           |
| Lisa Stelzner   | 02:54:11    | F35-39   | Desiree Berry    |
| Lauren Matthews | 03:01:17    | F35-39   | Suzy Slane       |
| Desiree Berry   | 03:05:42    | F35-39   | Nobody           |
| Suzy Slane      | 03:06:24    | F35-39   | Nobody           |
+-----------------+-------------+----------+------------------+

LAG

LAG (value_expression[, offset [, default_expression]])

说明

返回上一行中的 value_expression 值。更改 offset 值会改变所返回的上一行;默认值是 1,表示窗口框架中的上一行。如果 offset 是 NULL 或负值,则会发生错误。

如果窗口框架中没有指定偏移量的行,则使用可选的 default_expression。此表达式必须是常量表达式,其类型必须可隐式强制转换为 value_expression 类型。如果未指定,则 default_expression 默认为 NULL。

支持的参数类型

  • value_expression 可以是表达式支持的任意返回数据类型。
  • offset 必须是非负整数字面量或参数。
  • default_expression 必须兼容值表达式类型。

返回数据类型

类型与 value_expression 相同。

示例

以下示例展示了 LAG 函数的基本用法。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS preceding_runner
FROM finishers;

+-----------------+-------------+----------+------------------+
| name            | finish_time | division | preceding_runner |
+-----------------+-------------+----------+------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL             |
| Sophia Liu      | 02:51:45    | F30-34   | NULL             |
| Nikki Leith     | 02:59:01    | F30-34   | Sophia Liu       |
| Jen Edwards     | 03:06:36    | F30-34   | Nikki Leith      |
| Meghan Lederer  | 03:07:41    | F30-34   | Jen Edwards      |
| Lauren Reasoner | 03:10:14    | F30-34   | Meghan Lederer   |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL             |
| Lauren Matthews | 03:01:17    | F35-39   | Lisa Stelzner    |
| Desiree Berry   | 03:05:42    | F35-39   | Lauren Matthews  |
| Suzy Slane      | 03:06:24    | F35-39   | Desiree Berry    |
+-----------------+-------------+----------+------------------+

下一个示例使用可选的 offset 参数。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2)
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | NULL              |
| Sophia Liu      | 02:51:45    | F30-34   | NULL              |
| Nikki Leith     | 02:59:01    | F30-34   | NULL              |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | NULL              |
| Lauren Matthews | 03:01:17    | F35-39   | NULL              |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

以下示例使用默认值替换 NULL 值。

WITH finishers AS
 (SELECT 'Sophia Liu' as name,
  TIMESTAMP '2016-10-18 2:51:45' as finish_time,
  'F30-34' as division
  UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
  UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
  UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
  UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
  UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
  UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
  UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
  UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
  UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
  finish_time,
  division,
  LAG(name, 2, 'Nobody')
    OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_ahead
FROM finishers;

+-----------------+-------------+----------+-------------------+
| name            | finish_time | division | two_runners_ahead |
+-----------------+-------------+----------+-------------------+
| Carly Forte     | 03:08:58    | F25-29   | Nobody            |
| Sophia Liu      | 02:51:45    | F30-34   | Nobody            |
| Nikki Leith     | 02:59:01    | F30-34   | Nobody            |
| Jen Edwards     | 03:06:36    | F30-34   | Sophia Liu        |
| Meghan Lederer  | 03:07:41    | F30-34   | Nikki Leith       |
| Lauren Reasoner | 03:10:14    | F30-34   | Jen Edwards       |
| Lisa Stelzner   | 02:54:11    | F35-39   | Nobody            |
| Lauren Matthews | 03:01:17    | F35-39   | Nobody            |
| Desiree Berry   | 03:05:42    | F35-39   | Lisa Stelzner     |
| Suzy Slane      | 03:06:24    | F35-39   | Lauren Matthews   |
+-----------------+-------------+----------+-------------------+

PERCENTILE_CONT

PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS])

说明

使用线性插值计算 value_expression 的指定百分位值。

如果不存在 RESPECT NULLS,此函数会忽略 NULL 值。如果存在 RESPECT NULLS

  • 两个 NULL 值之间的插值返回 NULL
  • 一个 NULL 值与一个非 NULL 值之间的插值返回非 NULL 值。

支持的参数类型

  • value_expressionpercentile 必须具有以下类型之一:
    • NUMERIC
    • BIGNUMERIC
    • FLOAT64
  • percentile 必须是 [0, 1] 范围内的一个字面量。

返回数据类型

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

INPUTNUMERICBIGNUMERICFLOAT64
NUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

示例

以下示例计算一个值列中的某些百分位值,同时忽略 null。

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+-----+-------------+--------+--------------+-----+
| min | percentile1 | median | percentile90 | max |
+-----+-------------+--------+--------------+-----+
| 0   | 0.03        | 1.5    | 2.7          | 3   |
+-----+-------------+--------+--------------+-----+

以下示例计算一个值列中的某些百分位值,同时保留 null。

SELECT
  PERCENTILE_CONT(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_CONT(x, 0.01 RESPECT NULLS) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_CONT(x, 0.9 RESPECT NULLS) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST([0, 3, NULL, 1, 2]) AS x LIMIT 1;

+------+-------------+--------+--------------+-----+
| min  | percentile1 | median | percentile90 | max |
+------+-------------+--------+--------------+-----+
| NULL | 0           | 1      | 2.6          | 3   |
+------+-------------+--------+--------------+-----+

PERCENTILE_DISC

PERCENTILE_DISC (value_expression, percentile [{RESPECT | IGNORE} NULLS])

说明

计算离散 value_expression 的指定百分位值。返回值是 percentile 的第一个经过排序的值,累积分布大于或等于给定 value_expression 值。

除非存在 RESPECT NULLS,否则此函数会忽略 NULL 值。

支持的参数类型

  • value_expression 可以是任意可排序的类型。
  • percentile 必须是 [0, 1] 范围内的一个字面量,并且具有以下类型之一:
    • NUMERIC
    • BIGNUMERIC
    • FLOAT64

返回数据类型

类型与 value_expression 相同。

示例

以下示例计算一个值列中的某些百分位值,同时忽略 null。

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+-----+--------+-----+
| x    | min | median | max |
+------+-----+--------+-----+
| c    | a   | b      | c   |
| NULL | a   | b      | c   |
| b    | a   | b      | c   |
| a    | a   | b      | c   |
+------+-----+--------+-----+

以下示例计算一列值中的某些百分位值,同时保留 null。

SELECT
  x,
  PERCENTILE_DISC(x, 0 RESPECT NULLS) OVER() AS min,
  PERCENTILE_DISC(x, 0.5 RESPECT NULLS) OVER() AS median,
  PERCENTILE_DISC(x, 1 RESPECT NULLS) OVER() AS max
FROM UNNEST(['c', NULL, 'b', 'a']) AS x;

+------+------+--------+-----+
| x    | min  | median | max |
+------+------+--------+-----+
| c    | NULL | a      | c   |
| NULL | NULL | a      | c   |
| b    | NULL | a      | c   |
| a    | NULL | a      | c   |
+------+------+--------+-----+

聚合分析函数

以下部分介绍了 BigQuery 支持的聚合分析函数。如需了解分析函数的工作原理,请参阅分析函数概念。如需了解聚合分析函数的工作原理,请参阅聚合分析函数概念

BigQuery 支持将以下聚合函数作为分析函数:

OVER 子句要求:

  • PARTITION BY:可选。
  • ORDER BY:可选。如果存在 DISTINCT,则不得使用。
  • window_frame_clause:可选。如果存在 DISTINCT,则不得使用。

示例:

COUNT(*) OVER (ROWS UNBOUNDED PRECEDING)
SUM(DISTINCT x) OVER ()

哈希函数

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

MD5

MD5(input)

说明

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

此函数返回 16 个字节。

返回类型

BYTES

示例

SELECT MD5("Hello World") as md5;

-- Note that the result of MD5 is of type BYTES, displayed as a base64-encoded string.
+--------------------------+
| md5                      |
+--------------------------+
| sQqNsWTgdUEFt6mb5y4/5Q== |
+--------------------------+

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 规范相等性。

ASCII

ASCII(value)

说明

返回 value 中第一个字符或字节的 ASCII 代码。如果 value 为空或者第一个字符或字节的 ASCII 代码为 0,则返回 0

返回类型

INT64

示例

SELECT ASCII('abcd') as A, ASCII('a') as B, ASCII('') as C, ASCII(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 97    | 97    | 0     | NULL  |
+-------+-------+-------+-------+

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

CHR

CHR(value)

说明

接受 Unicode 代码点并返回与代码点匹配的字符。每个有效的代码点都应位于 [0, 0xD7FF] 和 [0xE000, 0x10FFFF] 范围内。如果代码点为 0,则返回空字符串。如果指定了无效的 Unicode 代码点,则系统会返回错误。

如需使用 Unicode 代码点数组,请参阅 CODE_POINTS_TO_STRING

返回类型

STRING

示例

SELECT CHR(65) AS A, CHR(255) AS B, CHR(513) AS C, CHR(1024)  AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| A     | ÿ     | ȁ     | Ѐ     |
+-------+-------+-------+-------+
SELECT CHR(97) AS A, CHR(0xF9B5) AS B, CHR(0) AS C, CHR(NULL) AS D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| a     | 例    |       | NULL  |
+-------+-------+-------+-------+

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_AGG(
  (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([code]) chr)
  ) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;

-- 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[, ...])

说明

将一个或多个串联成一个结果。所有值都必须为 BYTES 或可转换为 STRING 的数据类型。

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

返回类型

STRINGBYTES

示例

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

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;

+---------------------+
| release_date        |
+---------------------+
| Summer 1923         |
+---------------------+

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

BigQuery 支持用于设置字符串格式的 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
BIGNUMERIC
FLOAT64
F 十进制记数法,[-](整数部分).(小数部分)表示有限值,大写字母表示非有限值 392.650000
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
e 科学记数法(尾数/指数)(小写) 3.926500e+02
inf
nan
NUMERIC
BIGNUMERIC
FLOAT64
E 科学记数法(尾数/指数)(大写) 3.926500E+02
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
g 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。小写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265e+07
inf
nan
NUMERIC
BIGNUMERIC
FLOAT64
G 十进制记数法或科学记数法,具体取决于输入值的指数和指定的精度。大写。 如需了解详情,请参阅 %g 和 %G 行为 392.65
3.9265E+07
INF
NAN
NUMERIC
BIGNUMERIC
FLOAT64
s 字符串 sample STRING
t 返回表示值的可打印字符串。通常看起来类似于将参数类型转换为 STRING。 请参阅 %t 和 %T 行为 sample
2014‑01‑01
<任意>
T 生成一个字符串,该字符串是一个有效的 BigQuery 常量,其类型与值类型类似(可能宽度更大,也可能是字符串)。请参阅 %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
STRUCT (value, value, ...)
其中值的格式为 %t
(value, value, ...)
其中值的格式为 %T

特殊情况:
零个字段:STRUCT()
一个字段:STRUCT(value)

错误条件

如果格式说明符无效,或者与相关参数类型不兼容,或者提供的数字或参数不正确,则会产生错误。例如,以下 <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(或者相同的大写形式)。这与 BigQuery 将这些值的类型转换为 STRING 的方式一致。对于 %T,BigQuery 会为没有使用非字符串字面量表示形式的 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

有几种常用的 base64 编码,它们的区别在于使用 65 个 ASCII 字符的字母来编码 64 位数字和内边距。如需了解详情,请参阅 RFC 4648。此函数要求字母 [A-Za-z0-9+/=]

返回类型

BYTES

示例

SELECT FROM_BASE64('/+A=') AS byte_data;

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

如需使用其他 base64 字符进行编码,您可能需要使用 REPLACE 函数编写 FROM_BASE64。例如,Web 编程中常用的 base64url 网址安全和文件名安全编码方法使用 -_= 作为最后的字符,而不是 +/=。如需对 base64url 编码的字符串进行解码,请将 +/ 分别替换为 -_

SELECT FROM_BASE64(REPLACE(REPLACE("_-A=", "-", "+"), "_", "/")) AS binary;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+--------+
| binary |
+--------+
| /+A=   |
+--------+

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

INITCAP

INITCAP(value[, delimiters])

说明

接受 STRING 并按以下格式返回它:每个单词的第一个字符大写,所有其他字符均小写。非字母字符保持不变。

delimiters 是可选的字符串参数,用于替换分隔字词的默认字符集。如果未指定 delimiters,则默认为以下字符:
<whitespace> [ ] ( ) { } / | \ < > ! ? @ " ^ # $ & ~ _ , . : ; * % + -

如果 valuedelimitersNULL,则该函数会返回 NULL

返回类型

STRING

示例

WITH example AS
(
  SELECT "Hello World-everyone!" AS value UNION ALL
  SELECT "tHe dog BARKS loudly+friendly" AS value UNION ALL
  SELECT "apples&oranges;&pears" AS value UNION ALL
  SELECT "καθίσματα ταινιών" AS value
)
SELECT value, INITCAP(value) AS initcap_value FROM example

+-------------------------------+-------------------------------+
| value                         | initcap_value                 |
+-------------------------------+-------------------------------+
| Hello World-everyone!         | Hello World-Everyone!         |
| tHe dog BARKS loudly+friendly | The Dog Barks Loudly+Friendly |
| apples&oranges;&pears         | Apples&Oranges;&Pears         |
| καθίσματα ταινιών             | Καθίσματα Ταινιών             |
+-------------------------------+-------------------------------+

WITH example AS
(
  SELECT "hello WORLD!" AS value, "" AS delimiters UNION ALL
  SELECT "καθίσματα ταιντιώ@ν" AS value, "τ@" AS delimiters UNION ALL
  SELECT "Apples1oranges2pears" AS value, "12" AS delimiters UNION ALL
  SELECT "tHisEisEaESentence" AS value, "E" AS delimiters
)
SELECT value, delimiters, INITCAP(value, delimiters) AS initcap_value FROM example;

+----------------------+------------+----------------------+
| value                | delimiters | initcap_value        |
+----------------------+------------+----------------------+
| hello WORLD!         |            | Hello world!         |
| καθίσματα ταιντιώ@ν  | τ@         | ΚαθίσματΑ τΑιντΙώ@Ν  |
| Apples1oranges2pears | 12         | Apples1Oranges2Pears |
| tHisEisEaESentence   | E          | ThisEIsEAESentence   |
+----------------------+------------+----------------------+

INSTR

INSTR(source_value, search_value[, position[, occurrence]])

说明

返回 source_valuesearch_value 的最小索引值(从 1 开始)。如果找不到匹配项,则返回 0。source_valuesearch_value 必须是同一类型(STRINGBYTES)。

如果指定了 position,则搜索会从 source_value 中的此位置开始,否则会从 source_value 的开头开始。如果 position 为负数,则该函数会从 source_value 末尾反向搜索,其中 -1 表示最后一个字符。position 不能为 0。

如果指定了 occurrence,则搜索会返回 source_valuesearch_value 的特定实例的位置,否则会返回第一次出现的索引。如果 occurrence 大于找到的匹配数,则返回 0。如果 occurrence > 1,则该函数会搜索重叠的出现项,换句话说,该函数会从上一个出现项中第二个字符开始搜索额外的出现项。occurrence 不能为 0 或负数。

返回类型

INT64

示例

WITH example AS
(SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 2 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 1 as position, 3 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, 3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -1 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'an' as search_value, -3 as position, 1 as
occurrence UNION ALL
SELECT 'banana' as source_value, 'ann' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 1 as
occurrence UNION ALL
SELECT 'helloooo' as source_value, 'oo' as search_value, 1 as position, 2 as
occurrence
)
SELECT source_value, search_value, position, occurrence, INSTR(source_value,
search_value, position, occurrence) AS instr
FROM example;

+--------------+--------------+----------+------------+-------+
| source_value | search_value | position | occurrence | instr |
+--------------+--------------+----------+------------+-------+
| banana       | an           | 1        | 1          | 2     |
| banana       | an           | 1        | 2          | 4     |
| banana       | an           | 1        | 3          | 0     |
| banana       | an           | 3        | 1          | 4     |
| banana       | an           | -1       | 1          | 4     |
| banana       | an           | -3       | 1          | 4     |
| banana       | ann          | 1        | 1          | 0     |
| helloooo     | oo           | 1        | 1          | 5     |
| helloooo     | oo           | 1        | 2          | 6     |
+--------------+--------------+----------+------------+-------+

LEFT

LEFT(value, length)

说明

返回 STRINGBYTES 值,其中包含 value 中最左侧的指定数量字符或字节。lengthINT64 类型,指定了返回值的长度。如果 valueBYTES 类型,则 length 是要返回的最左侧的字节数。如果 valueSTRING,则 length 是要返回的最左侧的字符数。

如果 length 为 0,则返回空的 STRINGBYTES 值。如果 length 为负数,则返回错误。如果 length 超出了 value 中的字符数或字节数,则返回原始的 value

返回类型

STRINGBYTES

示例

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

+---------+--------------+
| example | left_example |
+---------+--------------+
| apple   | app          |
| banana  | ban          |
| абвгд   | абв          |
+---------+--------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, LEFT(example, 3) AS left_example
FROM examples;

-- Note that the result of LEFT is of type BYTES, displayed as a base64-encoded string.
+----------+--------------+
| example  | left_example |
+----------+--------------+
| YXBwbGU= | YXBw         |
| YmFuYW5h | YmFu         |
| q83vqrs= | q83v         |
+----------+--------------+

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

NORMALIZE

NORMALIZE(value[, normalization_mode])

说明

获取一个 STRING 类型的 value,并以标准化字符串的形式将其返回。

规范化用于确保两个字符串的相等性。规范化往往用于两个字符串在屏幕上显示相同,但具有不同 Unicode 码位的情况。

NORMALIZE 支持四种可选的标准化模式:

名称 说明
NFC 标准化形式的规范组成 按规范相等性分解和重新组合字符。
NFKC 规范化形式的兼容性组成 按兼容性分解字符,然后按规范相等性重新组合字符。
NFD 标准化形式的规范分解 按规范相等性分解字符,并且按特定顺序对排列多个组合字符。
NFKD 规范化形式的兼容性分解 按兼容性分解字符,并按特定顺序排列多个组合字符。

默认标准化模式是 NFC

返回类型

STRING

示例

SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;

+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true       |
+---+---+------------+

以下示例标准化不同的空格字符。

WITH EquivalentNames AS (
  SELECT name
  FROM UNNEST([
      'Jane\u2004Doe',
      'John\u2004Smith',
      'Jane\u2005Doe',
      'Jane\u2006Doe',
      'John Smith']) AS name
)
SELECT
  NORMALIZE(name, NFKC) AS normalized_name,
  COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;

+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith      | 2          |
| Jane Doe        | 3          |
+-----------------+------------+

NORMALIZE_AND_CASEFOLD

NORMALIZE_AND_CASEFOLD(value[, normalization_mode])

说明

获取 STRINGvalue,并执行与 NORMALIZE 相同的操作,并针对不区分大小写的操作执行大写转换

NORMALIZE_AND_CASEFOLD 支持四种可选的标准化模式:

名称 说明
NFC 标准化形式的规范组成 按规范相等性分解和重新组合字符。
NFKC 规范化形式的兼容性组成 按兼容性分解字符,然后按规范相等性重新组合字符。
NFD 标准化形式的规范分解 按规范相等性分解字符,并且按特定顺序对排列多个组合字符。
NFKD 规范化形式的兼容性分解 按兼容性分解字符,并按特定顺序排列多个组合字符。

默认标准化模式是 NFC

返回类型

STRING

示例

WITH Strings AS (
  SELECT '\u2168' AS a, 'IX' AS b UNION ALL
  SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
  NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
  NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
  NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
  NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;

+---+----+-------+-------+------+------+
| a | b  | nfd   | nfc   | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å  | true  | true  | true | true |
+---+----+-------+-------+------+------+

OCTET_LENGTH

OCTET_LENGTH(value)

BYTE_LENGTH 的别名。

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[, position[, occurrence]])

说明

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

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

如果指定了 position,则搜索会从 value 中的此位置开始,否则会从 value 的开头开始。position 必须为正整数,不能为 0。如果 position 大于 value 的长度,则返回 NULL

如果指定了 occurrence,则搜索会返回 valueregexp 的特定出现项,否则会返回第一个匹配项。如果 occurrence 大于找到的匹配数,则返回 NULL。如果 occurrence > 1,则该函数会从上一个出现项后面的字符开始搜索额外的出现项。

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

  • 正则表达式无效
  • 正则表达式具有多个捕获组
  • position 不是正整数
  • occurrence 不是正整数

返回类型

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              |
+------------------+
WITH example AS
(SELECT 'Hello Helloo and Hellooo' AS value, 'H?ello+' AS regex, 1 as position,
1 AS occurrence UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 1, 4 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 2, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 1 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 2 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 3, 3 UNION ALL
SELECT 'Hello Helloo and Hellooo', 'H?ello+', 20, 1 UNION ALL
SELECT 'cats&dogs&rabbits' ,'\\w+&', 1, 2 UNION ALL
SELECT 'cats&dogs&rabbits', '\\w+&', 2, 3
)
SELECT value, regex, position, occurrence, REGEXP_EXTRACT(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------------+---------+----------+------------+--------------+
| value                    | regex   | position | occurrence | regexp_value |
+--------------------------+---------+----------+------------+--------------+
| Hello Helloo and Hellooo | H?ello+ | 1        | 1          | Hello        |
| Hello Helloo and Hellooo | H?ello+ | 1        | 2          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 1        | 3          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 1        | 4          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 2        | 1          | ello         |
| Hello Helloo and Hellooo | H?ello+ | 3        | 1          | Helloo       |
| Hello Helloo and Hellooo | H?ello+ | 3        | 2          | Hellooo      |
| Hello Helloo and Hellooo | H?ello+ | 3        | 3          | NULL         |
| Hello Helloo and Hellooo | H?ello+ | 20       | 1          | NULL         |
| cats&dogs&rabbits        | \w+&    | 1        | 2          | dogs&        |
| cats&dogs&rabbits        | \w+&    | 2        | 3          | NULL         |
+--------------------------+---------+----------+------------+--------------+

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_INSTR

REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]])

说明

返回 source_value 中正则表达式 regexp 的最小索引值(从 1 开始)。如果未找到匹配项或正则表达式为空,则返回 0。如果正则表达式无效或具有多个捕获组,则返回错误。source_valueregexp 必须是同一类型(STRINGBYTES)。

如果指定了 position,则搜索会从 source_value 中的此位置开始,否则会从 source_value 的开头开始。如果 position 为负数,则该函数会从 source_value 末尾反向搜索,其中 -1 表示最后一个字符。position 不能为 0。

如果指定了 occurrence,则搜索会返回 source_valueregexp 的特定实例的位置,否则会返回第一次出现的索引。如果 occurrence 大于找到的匹配数,则返回 0。如果 occurrence > 1,则该函数会搜索重叠的出现项,换句话说,该函数会从上一个出现项中第二个字符开始搜索额外的出现项。occurrence 不能为 0 或负数。

您可以选择使用 occurrence_position 来指定相对于 occurrence 的位置。您的选择是:+ 0:返回出现项的起始位置。+ 1:返回出现项末尾后面的第一个位置。如果出现项的末尾也是输入的末尾,则返回出现项末尾对应的数字加上 1 之后的结果。例如,字符串长度 + 1。

返回类型

INT64

示例

WITH example AS (
  SELECT 'ab@gmail.com' AS source_value, '@[^.]*' AS regexp UNION ALL
  SELECT 'ab@mail.com', '@[^.]*' UNION ALL
  SELECT 'abc@gmail.com', '@[^.]*' UNION ALL
  SELECT 'abc.com', '@[^.]*')
SELECT source_value, regexp, REGEXP_INSTR(source_value, regexp) AS instr
FROM example;

+---------------+--------+-------+
| source_value  | regexp | instr |
+---------------+--------+-------+
| ab@gmail.com  | @[^.]* | 3     |
| ab@mail.com   | @[^.]* | 3     |
| abc@gmail.com | @[^.]* | 4     |
| abc.com       | @[^.]* | 0     |
+---------------+--------+-------+
WITH example AS (
  SELECT 'a@gmail.com b@gmail.com' AS source_value, '@[^.]*' AS regexp, 1 AS position UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 3 UNION ALL
  SELECT 'a@gmail.com b@gmail.com', '@[^.]*', 4)
SELECT
  source_value, regexp, position,
  REGEXP_INSTR(source_value, regexp, position) AS instr
FROM example;

+-------------------------+--------+----------+-------+
| source_value            | regexp | position | instr |
+-------------------------+--------+----------+-------+
| a@gmail.com b@gmail.com | @[^.]* | 1        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 2        | 2     |
| a@gmail.com b@gmail.com | @[^.]* | 3        | 14    |
| a@gmail.com b@gmail.com | @[^.]* | 4        | 14    |
+-------------------------+--------+----------+-------+
WITH example AS (
  SELECT 'a@gmail.com b@gmail.com c@gmail.com' AS source_value,
         '@[^.]*' AS regexp, 1 AS position, 1 AS occurrence UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 2 UNION ALL
  SELECT 'a@gmail.com b@gmail.com c@gmail.com', '@[^.]*', 1, 3)
SELECT
  source_value, regexp, position, occurrence,
  REGEXP_INSTR(source_value, regexp, position, occurrence) AS instr
FROM example;

+-------------------------------------+--------+----------+------------+-------+
| source_value                        | regexp | position | occurrence | instr |
+-------------------------------------+--------+----------+------------+-------+
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 1          | 2     |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 2          | 14    |
| a@gmail.com b@gmail.com c@gmail.com | @[^.]* | 1        | 3          | 26    |
+-------------------------------------+--------+----------+------------+-------+
WITH example AS (
  SELECT 'a@gmail.com' AS source_value, '@[^.]*' AS regexp,
         1 AS position, 1 AS occurrence, 0 AS o_position UNION ALL
  SELECT 'a@gmail.com', '@[^.]*', 1, 1, 1)
SELECT
  source_value, regexp, position, occurrence, o_position,
  REGEXP_INSTR(source_value, regexp, position, occurrence, o_position) AS instr
FROM example;

+--------------+--------+----------+------------+------------+-------+
| source_value | regexp | position | occurrence | o_position | instr |
+--------------+--------+----------+------------+------------+-------+
| a@gmail.com  | @[^.]* | 1        | 1          | 0          | 2     |
| a@gmail.com  | @[^.]* | 1        | 1          | 1          | 8     |
+--------------+--------+----------+------------+------------+-------+

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

REGEXP_SUBSTR

REGEXP_SUBSTR(value, regexp[, position[, occurrence]])

说明

相当于 REGEXP_EXTRACT

返回类型

STRINGBYTES

示例

WITH example AS
(SELECT 'Hello World Helloo' AS value, 'H?ello+' AS regex, 1 AS position, 1 AS
occurrence
)
SELECT value, regex, position, occurrence, REGEXP_SUBSTR(value, regex,
position, occurrence) AS regexp_value FROM example;

+--------------------+---------+----------+------------+--------------+
| value              | regex   | position | occurrence | regexp_value |
+--------------------+---------+----------+------------+--------------+
| Hello World Helloo | H?ello+ | 1        | 1          | Hello        |
+--------------------+---------+----------+------------+--------------+

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           |
+---------------+----------------+--------------+---------------+
RIGHT(value, length)

说明

返回 STRINGBYTES 值,其中包含 value 中最右侧的指定数量字符或字节。lengthINT64 类型,指定了返回值的长度。如果 valueBYTES,则 length 是要返回的最右侧的字节数。如果 valueSTRING,则 length 是要返回的最右侧的字符数。

如果 length 为 0,则返回空的 STRINGBYTES 值。如果 length 为负数,则返回错误。如果 length 超出了 value 中的字符数或字节数,则返回原始的 value

返回类型

STRINGBYTES

示例

WITH examples AS
(SELECT 'apple' as example
UNION ALL
SELECT 'banana' as example
UNION ALL
SELECT 'абвгд' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

+---------+---------------+
| example | right_example |
+---------+---------------+
| apple   | ple           |
| banana  | ana           |
| абвгд   | вгд           |
+---------+---------------+
WITH examples AS
(SELECT b'apple' as example
UNION ALL
SELECT b'banana' as example
UNION ALL
SELECT b'\xab\xcd\xef\xaa\xbb' as example
)
SELECT example, RIGHT(example, 3) AS right_example
FROM examples;

-- Note that the result of RIGHT is of type BYTES, displayed as a base64-encoded string.
+----------+---------------+
| example  | right_example |
+----------+---------------+
| YXBwbGU= | cGxl          |
| YmFuYW5h | YW5h          |
| q83vqrs= | 76q7          |
+----------+---------------+

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;

SOUNDEX

SOUNDEX(value)

说明

返回表示 valueSoundex 代码的 STRING

SOUNDEX 会生成字符串的语音表示形式。它会按声音(如英语中的发音)将字词编入索引。它通常用于帮助确定两个字符串(例如姓氏 Levine 和 Lavine 或者单词 to 和 too)是否具有相似的英语发音。

SOUNDEX 的结果由 1 个字母后跟 3 个数字组成。系统会忽略非拉丁字符。如果在移除非拉丁字符后剩余的字符串为空,则返回空的 STRING

返回类型

STRING

示例

WITH example AS (
  SELECT 'Ashcraft' AS value UNION ALL
  SELECT 'Raven' AS value UNION ALL
  SELECT 'Ribbon' AS value UNION ALL
  SELECT 'apple' AS value UNION ALL
  SELECT 'Hello world!' AS value UNION ALL
  SELECT '  H3##!@llo w00orld!' AS value UNION ALL
  SELECT '#1' AS value UNION ALL
  SELECT NULL AS value
)
SELECT value, SOUNDEX(value) AS soundex
FROM example;

+----------------------+---------+
| value                | soundex |
+----------------------+---------+
| Ashcraft             | A261    |
| Raven                | R150    |
| Ribbon               | R150    |
| apple                | a140    |
| Hello world!         | H464    |
|   H3##!@llo w00orld! | H464    |
| #1                   |         |
| NULL                 | NULL    |
+----------------------+---------+

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(value1, value2)

说明

获取两个 STRINGBYTES 值。返回 value1 中第一次出现 value2 处的索引(从 1 开始)。如果未找到 value2,则此函数返回 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      |
+---------+

SUBSTRING

SUBSTRING(value, position[, length])

SUBSTR 的别名。

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

有几种常用的 base64 编码,它们的区别在于使用 65 个 ASCII 字符的字母来编码 64 位数字和内边距。如需了解详情,请参阅 RFC 4648。此函数会添加内边距并使用字母 [A-Za-z0-9+/=]

返回类型

STRING

示例

SELECT TO_BASE64(b'\377\340') AS base64_string;

+---------------+
| base64_string |
+---------------+
| /+A=          |
+---------------+

如需使用其他 base64 字符进行编码,您可能需要使用 REPLACE 函数编写 TO_BASE64。例如,Web 编程中常用的 base64url 网址安全和文件名安全编码方法使用 -_= 作为最后的字符,而不是 +/=。如需对 base64url 编码的字符串进行编码,请将 -_ 分别替换为 +/

SELECT REPLACE(REPLACE(TO_BASE64(b"\377\340"), "+", "-"), "/", "_") as websafe_base64;

+----------------+
| websafe_base64 |
+----------------+
| _-A=           |
+----------------+

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

TRANSLATE

TRANSLATE(expression, source_characters, target_characters)

说明

expression 中,将 source_characters 的每个字符替换为 target_characters 的相应字符。所有输入都必须是同一类型(STRINGBYTES)。

  • expression 中的每个字符最多转换一次。
  • 如果 expression 中的字符不在 source_characters 中,则该字符在 expression 中保持不变。
  • 如果 source_characters 中的字符在 target_characters 中没有对应的字符,则该字符会从结果中省略。
  • source_characters 中的重复字符会导致错误。

返回类型

STRINGBYTES

示例

WITH example AS (
  SELECT 'This is a cookie' AS expression, 'sco' AS source_characters, 'zku' AS
  target_characters UNION ALL
  SELECT 'A coaster' AS expression, 'co' AS source_characters, 'k' as
  target_characters
)
SELECT expression, source_characters, target_characters, TRANSLATE(expression,
source_characters, target_characters) AS translate
FROM example;

+------------------+-------------------+-------------------+------------------+
| expression       | source_characters | target_characters | translate        |
+------------------+-------------------+-------------------+------------------+
| This is a cookie | sco               | zku               | Thiz iz a kuukie |
| A coaster        | co                | k                 | A kaster         |
+------------------+-------------------+-------------------+------------------+

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

UNICODE

UNICODE(value)

说明

返回 value 中第一个字符的 Unicode 代码点。如果 value 为空或者生成的 Unicode 代码点为 0,则返回 0

返回类型

INT64

示例

SELECT UNICODE('âbcd') as A, UNICODE('â') as B, UNICODE('') as C, UNICODE(NULL) as D;

+-------+-------+-------+-------+
| A     | B     | C     | D     |
+-------+-------+-------+-------+
| 226   | 226   | 0     | NULL  |
+-------+-------+-------+-------+

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 函数

BigQuery 支持帮助您检索 JSON 格式字符串内存储的数据的函数,以及帮助您将数据转化为 JSON 格式字符串的函数。

函数概览

以下函数使用英文双引号来转义无效的 JSONPath 字符:"a.b"

此行为与 ANSI 标准一致。

JSON 函数 说明 返回类型
JSON_QUERY 提取 JSON 值(如数组或对象)或 JSON 格式的标量值(如字符串、整数或布尔值)。 JSON 格式的 STRING
JSON_VALUE 提取标量值。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义值。如果选择了非标量值,则返回 SQL NULL STRING
JSON_QUERY_ARRAY 提取一组 JSON 值(例如数组或对象),以及 JSON 格式的标量值(如字符串、整数和布尔值)。 ARRAY<JSON-formatted STRING>
JSON_VALUE_ARRAY 提取一个标量值数组。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义值。如果所选值不是数组或不是仅包含标量值的数组,则会返回 SQL NULL ARRAY<STRING>

旧版 JSON 提取函数

以下函数使用英文单引号和英文括号来转义无效的 JSONPath 字符:['a.b']

虽然 BigQuery 支持这些函数,但我们建议您使用上表中的函数。

JSON 函数 说明 返回类型
JSON_EXTRACT 提取 JSON 值(如数组或对象)或 JSON 格式的标量值(如字符串、整数或布尔值)。 JSON 格式的 STRING
JSON_EXTRACT_SCALAR 提取标量值。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义值。如果选择了非标量值,则返回 SQL NULL STRING
JSON_EXTRACT_ARRAY 提取一组 JSON 值(例如数组或对象),以及 JSON 格式的标量值(如字符串、整数和布尔值)。 ARRAY<JSON-formatted STRING>
JSON_EXTRACT_STRING_ARRAY 提取一个标量值数组。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义值。如果所选值不是数组或不是仅包含标量值的数组,则会返回 SQL NULL ARRAY<STRING>

其他 JSON 函数

JSON 函数 说明 返回类型
TO_JSON_STRING 接受 SQL 值并返回 JSON 格式的值字符串表示形式。 JSON 格式的 STRING

JSON_EXTRACT

JSON_EXTRACT(json_string_expr, json_path)

说明

提取 JSON 值(如数组或对象)或 JSON 格式的标量值(如字符串、整数或布尔值)。如果 JSON 密钥使用无效的 JSONPath 字符,那么您可以使用英文单引号和英文括号对这些字符进行转义。

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

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

如果您要在提取中包含非标量值(如数组),请使用 JSON_EXTRACT。如果您只想提取标量值(如字符串、整数和布尔值),请使用 JSON_EXTRACT_SCALAR

返回类型

JSON 格式的 STRING

示例

SELECT JSON_EXTRACT(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_EXTRACT(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_EXTRACT(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_EXTRACT(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"}] |
+------------------------------------+

JSON_QUERY

JSON_QUERY(json_string_expr, json_path)

说明

提取 JSON 值(如数组或对象)或 JSON 格式的标量值(如字符串、整数或布尔值)。如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

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

如果您要在提取中包含非标量值(如数组),请使用 JSON_QUERY。如果您只想提取标量值(如字符串、整数和布尔值),请使用 JSON_VALUE

返回类型

JSON 格式的 STRING

示例

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"}] |
+------------------------------------+

JSON_EXTRACT_SCALAR

JSON_EXTRACT_SCALAR(json_string_expr[, json_path])

说明

提取标量值,然后将其作为字符串返回。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义返回值。如果 JSON 密钥使用无效的 JSONPath 字符,那么您可以使用英文单引号和英文括号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果 json_path 返回 JSON null 或非标量值(换句话说,如果 json_path 表示对象或数组),则返回 SQL NULL。 如果未提供此可选参数,则应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

如果您只想提取标量值(如字符串、整数和布尔值),那么使用 JSON_EXTRACT_SCALAR。如果您要在提取中包含非标量值(如数组),那么使用 JSON_EXTRACT

返回类型

STRING

示例

以下示例将如何返回 JSON_EXTRACTJSON_EXTRACT_SCALAR 函数的结果进行比较。

SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') AS json_name,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') AS scalar_name,
  JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') AS json_age,
  JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') AS scalar_age;

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

SELECT JSON_EXTRACT('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract,
  JSON_EXTRACT_SCALAR('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_extract_scalar;

+--------------------+---------------------+
| json_extract       | json_extract_scalar |
+--------------------+---------------------+
| ["apple","banana"] | NULL                |
+--------------------+---------------------+

如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用单引号和括号 [' '] 对这些字符进行转义。例如:

SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello;

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

JSON_VALUE

JSON_VALUE(json_string_expr[, json_path])

说明

提取标量值,然后将其作为字符串返回。标量值可以表示字符串、整数或布尔值。移除最外层的英文引号并取消转义返回值。如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果 json_path 返回 JSON null 或非标量值(换句话说,如果 json_path 表示对象或数组),则返回 SQL NULL。 如果未提供此可选参数,则应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

如果您只想提取标量值(如字符串、整数和布尔值),那么使用 JSON_VALUE。如果您要在提取中包含非标量值(如数组),那么使用 JSON_QUERY

返回类型

STRING

示例

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_age;

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

SELECT JSON_QUERY('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_query,
  JSON_VALUE('{"fruits": ["apple", "banana"]}', '$.fruits') AS json_value;

+--------------------+------------+
| json_query         | json_value |
+--------------------+------------+
| ["apple","banana"] | NULL       |
+--------------------+------------+

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

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

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

JSON_EXTRACT_ARRAY

JSON_EXTRACT_ARRAY(json_string_expr[, json_path])

说明

提取一组 JSON 值(例如数组或对象),以及 JSON 格式的标量值(如字符串、整数和布尔值)。如果 JSON 密钥使用无效的 JSONPath 字符,那么您可以使用英文单引号和英文括号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果未提供此可选参数,那么应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

返回类型

ARRAY<JSON-FORMATTED STRING>

示例

以下示例将 JSON 格式的字符串中的项提取到字符串数组中:

SELECT JSON_EXTRACT_ARRAY('[1,2,3]') AS string_array;

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

以下示例提取字符串数组并将其转换为整数数组:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

以下示例将 JSON 格式的字符串中的字符串值提取到数组中:

-- Doesn't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

这只会将 fruit 属性中的项提取到数组中:

SELECT JSON_EXTRACT_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

这些等价于:

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文单引号和英文括号 [' '] 对这些字符进行转义。例如:

SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

以下示例探讨了如何处理无效的请求和空数组:

  • 如果 JSONPath 无效,则系统会抛出错误。
  • 如果 JSON 格式的字符串无效,则输出为 NULL。
  • JSON 格式的字符串中可以包含空数组。
-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_QUERY_ARRAY

JSON_QUERY_ARRAY(json_string_expr[, json_path])

说明

提取一组 JSON 值(例如数组或对象),以及 JSON 格式的标量值(如字符串、整数和布尔值)。如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果未提供此可选参数,那么应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

返回类型

ARRAY<JSON-FORMATTED STRING>

示例

以下示例将 JSON 格式的字符串中的项提取到字符串数组中:

SELECT JSON_QUERY_ARRAY('[1,2,3]') AS string_array;

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

以下示例提取字符串数组并将其转换为整数数组:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_QUERY_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

以下示例将 JSON 格式的字符串中的字符串值提取到数组中:

-- Doesn't strip the double quotes
SELECT JSON_QUERY_ARRAY('["apples","oranges","grapes"]', '$') AS string_array;

+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

-- Strips the double quotes
SELECT ARRAY(
  SELECT JSON_EXTRACT_SCALAR(string_element, '$')
  FROM UNNEST(JSON_QUERY_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array;

+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

这只会将 fruit 属性中的项提取到数组中:

SELECT JSON_QUERY_ARRAY(
  '{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}]}',
  '$.fruit'
) AS string_array;

+-------------------------------------------------------+
| string_array                                          |
+-------------------------------------------------------+
| [{"apples":5,"oranges":10}, {"apples":2,"oranges":4}] |
+-------------------------------------------------------+

这些等价于:

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

SELECT JSON_QUERY_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------------+
| string_array                    |
+---------------------------------+
| ["apples", "oranges", "grapes"] |
+---------------------------------+

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

SELECT JSON_QUERY_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+-----------+
| hello     |
+-----------+
| ["world"] |
+-----------+

以下示例说明了如何处理无效的请求和空数组:

-- An error is returned if you provide an invalid JSONPath.
SELECT JSON_QUERY_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSONPath does not refer to an array, then NULL is returned.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a key that does not exist is specified, then the result is NULL.
SELECT JSON_QUERY_ARRAY('{"a":"foo"}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- Empty arrays in JSON-formatted strings are supported.
SELECT JSON_QUERY_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

JSON_EXTRACT_STRING_ARRAY

JSON_EXTRACT_STRING_ARRAY(json_string_expr[, json_path])

说明

提取一个标量值数组,并返回一个字符串格式的标量值数组。标量值可以表示字符串、整数或布尔值。 如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文单引号和英文括号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果未提供此可选参数,那么应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

返回类型

ARRAY<STRING>

示例

以下示例将如何返回 JSON_EXTRACT_ARRAYJSON_EXTRACT_STRING_ARRAY 函数的结果进行比较。

SELECT JSON_EXTRACT_ARRAY('["apples","oranges"]') AS json_array,
JSON_EXTRACT_STRING_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

以下示例将 JSON 格式的字符串中的项提取到字符串数组中:

-- Strips the double quotes
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-------------------+
| string_array      |
+-------------------+
| [foo, bar, baz]   |
+-------------------+

以下示例提取字符串数组并将其转换为整数数组:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_EXTRACT_STRING_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

这些等价于:

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') AS string_array;

SELECT JSON_EXTRACT_STRING_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文单引号和英文括号 [' '] 对这些字符进行转义。例如:

SELECT JSON_EXTRACT_STRING_ARRAY('{"a.b": {"c": ["world"]}}', "$['a.b'].c") AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

以下示例探讨了如何处理无效的请求和空数组:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_EXTRACT_STRING_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON formatted string is invalid, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_EXTRACT_STRING_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects, then the output is NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_EXTRACT_STRING_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar values and a JSON null,
-- then the output of the JSON_EXTRACT_STRING_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_EXTRACT_STRING_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY(json_string_expr[, json_path])

说明

提取一个标量值数组,并返回一个字符串格式的标量值数组。标量值可以表示字符串、整数或布尔值。 如果 JSON 密钥使用无效的 JSONPath 字符,您可以使用英文双引号对这些字符进行转义。

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

    {"class" : {"students" : [{"name" : "Jane"}]}}
    
  • json_pathJSONPath。这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果未提供此可选参数,那么应用 JSONPath $ 符号,这意味着分析整个 JSON 格式的字符串。

返回类型

ARRAY<STRING>

示例

以下示例将如何返回 JSON_QUERY_ARRAYJSON_VALUE_ARRAY 函数的结果进行比较。

SELECT JSON_QUERY_ARRAY('["apples","oranges"]') AS json_array,
       JSON_VALUE_ARRAY('["apples","oranges"]') AS string_array;

+-----------------------+-------------------+
| json_array            | string_array      |
+-----------------------+-------------------+
| ["apples", "oranges"] | [apples, oranges] |
+-----------------------+-------------------+

以下示例将 JSON 格式的字符串中的项提取到字符串数组中:

-- Strips the double quotes
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','$') AS string_array;

+-----------------+
| string_array    |
+-----------------+
| [foo, bar, baz] |
+-----------------+

以下示例提取字符串数组并将其转换为整数数组:

SELECT ARRAY(
  SELECT CAST(integer_element AS INT64)
  FROM UNNEST(
    JSON_VALUE_ARRAY('[1,2,3]','$')
  ) AS integer_element
) AS integer_array;

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

这些等价于:

SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') AS string_array;
SELECT JSON_VALUE_ARRAY('{"fruits":["apples","oranges","grapes"]}','$."fruits"') AS string_array;

-- The queries above produce the following result:
+---------------------------+
| string_array              |
+---------------------------+
| [apples, oranges, grapes] |
+---------------------------+

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

SELECT JSON_VALUE_ARRAY('{"a.b": {"c": ["world"]}}', '$."a.b".c') AS hello;

+---------+
| hello   |
+---------+
| [world] |
+---------+

以下示例探讨了如何处理无效的请求和空数组:

-- An error is thrown if you provide an invalid JSONPath.
SELECT JSON_VALUE_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') AS result;

-- If the JSON-formatted string is invalid, then NULL is returned.
SELECT JSON_VALUE_ARRAY('}}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If the JSON document is NULL, then NULL is returned.
SELECT JSON_VALUE_ARRAY(NULL,'$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath does not match anything, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":["foo","bar","baz"]}','$.b') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an object that is not an array, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo"}','$') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of non-scalar objects, then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[{"b":"foo","c":1},{"b":"bar","c":2}],"d":"baz"}','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an array of mixed scalar and non-scalar objects,
-- then the output is NULL.
SELECT JSON_VALUE_ARRAY('{"a":[10, {"b": 20}]','$.a') AS result;

+--------+
| result |
+--------+
| NULL   |
+--------+

-- If a JSONPath matches an empty JSON array, then the output is an empty array instead of NULL.
SELECT JSON_VALUE_ARRAY('{"a":"foo","b":[]}','$.b') AS result;

+--------+
| result |
+--------+
| []     |
+--------+

-- If a JSONPath matches an array that contains scalar objects and a JSON null,
-- then the output of the JSON_VALUE_ARRAY function must be transformed
-- because the final output cannot be an array with NULL values. This example
-- uses the UNNEST operator to convert the output array into a table as the final output.
SELECT string_value FROM UNNEST(JSON_VALUE_ARRAY('["world", 1, null]')) AS string_value;

+--------------+
| string_value |
+--------------+
| world        |
| 1            |
| NULL         |
+--------------+

TO_JSON_STRING

TO_JSON_STRING(value[, pretty_print])

说明

接受 SQL 值并返回 JSON 格式的值字符串表示形式。值必须是受支持的 BigQuery 数据类型。您可以在此处查看此函数支持的 BigQuery 数据类型及其 JSON 编码。

此函数支持一个名为 pretty_print 的可选布尔值参数。如果 pretty_printtrue,返回值将以易读性为目标进行格式设置。

返回类型

JSON 格式的 STRING

示例

将表中的行转换为 JSON 格式的字符串。

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates) UNION ALL
    (SELECT 3 AS id, [50,60] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------------------+
| id | coordinates | json_data                      |
+----+-------------+--------------------------------+
| 1  | [10, 20]    | {"id":1,"coordinates":[10,20]} |
| 2  | [30, 40]    | {"id":2,"coordinates":[30,40]} |
| 3  | [50, 60]    | {"id":3,"coordinates":[50,60]} |
+----+-------------+--------------------------------+

将表中的行转换为易于阅读的 JSON 格式字符串。

With CoordinatesTable AS (
    (SELECT 1 AS id, [10,20] AS coordinates) UNION ALL
    (SELECT 2 AS id, [30,40] AS coordinates))
SELECT id, coordinates, TO_JSON_STRING(t, true) AS json_data
FROM CoordinatesTable AS t;

+----+-------------+--------------------+
| id | coordinates | json_data          |
+----+-------------+--------------------+
| 1  | [10, 20]    | {                  |
|    |             |   "id": 1,         |
|    |             |   "coordinates": [ |
|    |             |     10,            |
|    |             |     20             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+
| 2  | [30, 40]    | {                  |
|    |             |   "id": 2,         |
|    |             |   "coordinates": [ |
|    |             |     30,            |
|    |             |     40             |
|    |             |   ]                |
|    |             | }                  |
+----+-------------+--------------------+

JSON 编码

下表列出了 SQL 值通过 TO_JSON_STRING 函数编码为 JSON 值时使用的常见编码。

从 SQL 到 JSON 示例
NULL

null

SQL 输入:NULL
JSON 输出:null
BOOL 布尔值 SQL 输入:TRUE
JSON 输出:true

SQL 输入:FALSE
JSON 输出:false
INT64

数字或字符串

当值位于 [-253, 253] 范围(即可以无损地表示为 IEEE 754 双精度浮点数的整数的范围)内时,编码为数字。此范围以外的值编码为字符串。

SQL 输入:9007199254740992
JSON 输出:9007199254740992

SQL 输入:9007199254740993
JSON 输出:"9007199254740993"
NUMERIC
BIGNUMERIC

数字或字符串

如果值在 [-253, 253] 范围内并且没有小数部分,则编码为数字。此范围以外的值编码为字符串。

SQL 输入:-1
JSON 输出:-1

SQL 输入:0
JSON 输出:0

SQL 输入:9007199254740993
JSON 输出:"9007199254740993"

SQL 输入:123.56
JSON 输出:"123.56"
FLOAT64

数字或字符串

+/-infNaN 编码为 Infinity-InfinityNaN。 否则,此值会被编码为字符串。

SQL 输入:1.0
JSON 输出:1

SQL 输入:9007199254740993
JSON 输出:9007199254740993

SQL 输入:"+inf"
JSON 输出:"Infinity"

SQL 输入:"-inf"
JSON 输出:"-Infinity"

SQL 输入:"NaN"
JSON 输出:"NaN"
STRING

字符串

已编码为字符串,根据 JSON 标准进行转义。 具体来说,"\ 和从 U+0000U+001F 的控制字符会被转义。

SQL 输入:"abc"
JSON 输出:"abc"

SQL 输入:"\"abc\""
JSON 输出:"\"abc\""
BYTES

字符串

使用 RFC 4648 Base64 数据编码。

SQL 输入:b"Google"
JSON 输出:"R29vZ2xl"
DATE 字符串 SQL 输入:DATE '2017-03-06'
JSON 输出:"2017-03-06"
TIMESTAMP

字符串

采用 ISO 8601 日期和时间编码,其中 T 分隔日期和时间,Z (Zulu/UTC) 表示时区。

SQL 输入:TIMESTAMP '2017-03-06 12:34:56.789012'
JSON 输出:"2017-03-06T12:34:56.789012Z"
DATETIME

字符串

采用 ISO 8601 日期和时间编码,其中 T 分隔日期和时间。

SQL 输入:DATETIME '2017-03-06 12:34:56.789012'
JSON 输出:"2017-03-06T12:34:56.789012"
TIME

字符串

编码为 ISO 8601 时间。

SQL 输入:TIME '12:34:56.789012'
JSON 输出:"12:34:56.789012"
ARRAY

数组

可以包含零个或多个元素。每个元素根据其类型进行格式设置。

SQL 输入:["red", "blue", "green"]
JSON 输出:["red", "blue", "green"]

SQL 输入:[1, 2, 3]
JSON 输出:[1, 2, 3]
STRUCT

对象

该对象可以包含零个或多个键值对。每个值根据其类型进行格式设置。

对于 TO_JSON_STRING,输出字符串中包括此字段和此字段的所有重复项。

匿名字段用 "" 表示。如果字段是非空数组或对象,则元素/字段会缩进到适当的级别。

无效 UTF-8 字段名称可能导致无法解析的 JSON。字符串值根据 JSON 标准进行转义。具体来说,"\ 和从 U+0000U+001F 的控制字符会被转义。

SQL 输入:STRUCT(12 AS purchases, TRUE AS inStock)
JSON 输出:{"purchases":12,"inStock": true}

JSONPath

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

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

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

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

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

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

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

数组函数

ARRAY

ARRAY(subquery)

说明

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

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

如果 subquery 生成一个值表,则输出 ARRAY 中的每个元素都是该值表中相应的一整行。

限制条件

  • 子查询是无序的,因此不保证输出 ARRAY 的元素为子查询保留源表中的任何顺序。但是,如果子查询包含 ORDER BY 子句,则 ARRAY 函数将返回一个遵循此子句的 ARRAY
  • 如果子查询返回多个列,则 ARRAY 函数会返回错误。
  • 如果子查询返回 ARRAY 类型的列或 ARRAY 类型的行,则 ARRAY 函数会返回错误:BigQuery 不支持元素类型为 ARRAYARRAY
  • 如果子查询未返回任何行,则 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
  • BIGNUMERIC
  • 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] |
+--------------------------------------------------------------+

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

说明

返回一个按指定时间间隔隔开的 TIMESTAMPS ARRAYstart_timestampend_timestamp 参数确定 ARRAY 的上限和下限(包含边界值)。

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

  • start_timestampTIMESTAMP
  • end_timestampTIMESTAMP
  • step_expressionINT64
  • 允许的 date_part 值为 MICROSECONDMILLISECONDSECONDMINUTEHOURDAY

step_expression 参数确定用于生成时间戳的增量。

返回数据类型

包含 0 个或更多个 TIMESTAMP 值的 ARRAY

示例

以下示例返回时间间隔为 1 天的 TIMESTAMPARRAY

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
                                INTERVAL 1 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+

以下示例返回时间间隔为 1 秒的 TIMESTAMP ARRAY

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

以下示例返回时间间隔为负数的 TIMESTAMPS ARRAY

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

以下示例返回只包含一个元素的 ARRAY,因为 start_timestampend_timestamp 的值相同。

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

以下示例返回一个空 ARRAY,因为 start_timestamp 晚于 end_timestamp

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

以下示例返回一个 null ARRAY,因为有一项输入为 NULL

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

以下示例根据包含 start_timestampend_timestamp 值的列生成一个 TIMESTAMP ARRAY

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016