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

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

函数调用规则

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

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

SAFE. 前缀

语法：

SAFE.function_name()

说明

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

+ 和 = 等运算符不支持 SAFE. 前缀。为防止除法运算出错，请使用 SAFE_DIVIDE。某些运算符（例如 IN、ARRAY 和 UNNEST）与函数类似，但不支持 SAFE. 前缀。CAST 和 EXTRACT 函数也不支持 SAFE. 前缀。为防止类型转换出错，请使用 SAFE_CAST。

示例

在以下示例中，SUBSTR 函数的第一处使用正常应该返回错误，因为该函数不支持长度参数为负值。但是，由于该函数带有 SAFE. 前缀，因此它返回了 NULL。SUBSTR 函数的第二处使用提供了预期输出，也就是说，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

转换规则

转换包括但不限于类型转换、强制转换和超类型。

类型转换属于显式转换，使用 CAST() 函数。
强制转换属于隐式转换，BigQuery 在下述条件下会自动执行此转换。
超类型是一种可被两个或更多表达式强制转换的常见类型。

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

类型转换和强制转换的对比

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

原类型	类型转换目标类型	强制转换目标类型
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。如需详细了解 CAST、SAFE_CAST 和其他类型转换函数的规则，请参阅转换函数。

强制转换

如果需要匹配函数签名，BigQuery 会将参数表达式的结果类型转换成其他类型。例如，如果函数 func() 定义为采用 FLOAT64 类型的单个参数，并使用表达式作为结果类型为 INT64 的参数，则表达式的结果将强制转换为 FLOAT64 类型，然后再计算 func()。

字面量强制转换

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

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

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

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

参数强制转换

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

输入数据类型	结果数据类型
STRING 参数	DATE DATETIME TIME TIMESTAMP

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

超类型

超类型是一种可被两个或更多表达式强制转换的常见类型。超类型用于设置运算（如 UNION ALL）和表达式（如 CASE），它们需要多个带匹配类型的参数。每种类型都有一个或多个超类型，包括其本身，后者定义了其超类型集。

输入类型	超类型
BOOL	BOOL
INT64	INT64 FLOAT64 NUMERIC BIGNUMERIC
FLOAT64	FLOAT64
NUMERIC	NUMERIC BIGNUMERIC FLOAT64
DECIMAL	DECIMAL BIGDECIMAL FLOAT64
BIGNUMERIC	BIGNUMERIC FLOAT64
BIGDECIMAL	BIGDECIMAL FLOAT64
STRING	STRING
DATE	DATE
TIME	TIME
DATETIME	DATETIME
TIMESTAMP	TIMESTAMP
BYTES	BYTES
STRUCT	具有相同的字段位置类型的 STRUCT。
ARRAY	具有同一元素类型的 ARRAY。
GEOGRAPHY	GEOGRAPHY

如果要查找一组输入类型的超类型，请先确定每个输入类型类型的超类型集的交集。如果该集为空，则输入类型没有共同的超类型。如果该集非空，则常见的超类型通常是该数据集中的最具体类型。通常，最特定的类型是具有最大限制域的类型。

示例

输入类型	常见超类型	返回值	备注
INT64 FLOAT64	FLOAT64	FLOAT64	如果对 INT64 和 FLOAT64 应用超类型，则超类型成功，因为它们共享超类型、FLOAT64。
INT64 BOOL	无	错误	如果对 INT64 和 BOOL 应用超类型，则超类型将失败，因为它们不共享公共超类型。

完全匹配和不完全匹配类型

数字类型可以完全匹配，也可以不完全匹配。对于超类型，如果所有输入类型均为完全匹配类型，则生成的超类型只能是完全匹配类型。

下表包含完全匹配和不完全匹配的数字数据类型的列表。

完全匹配类型	不完全匹配类型
INT64 NUMERIC BIGNUMERIC	FLOAT64

示例

输入类型	常见超类型	返回值	备注
INT64 FLOAT64	FLOAT64	FLOAT64	如果超类型应用于 INT64 和 DOUBLE，则超类型会成功，因为有完全匹配和不完全匹配的数字类型被超类型。

类型特异性

每种类型都有一个其支持的值域。较窄网域的类型要比较宽网域类型更具体。完全匹配类型比不完全匹配类型更具体，因为不完全匹配类型支持的域值的范围比完全匹配类型更广。例如，INT64 比 FLOAT64 更为具体。

超类型和字面量

字面量的超类型规则比普通表达式更为宽松，并且与隐式强制转换规则一致。当输入类型包括与字面量相关的类型集时，可使用以下算法：

如果集合中已存在非文字量超类型，请找到非字面量常见超类型集。
如果至少有一种可能的超类型，请找到可以隐式强制转换其余字面量类型的最具体类型，然后返回该超类型。否则，则不存在超类型。
如果集仅包含与字面量相关的类型，请计算字面量类型的超类型。
如果所有输入类型都与 NULL 字面量相关，则生成的超类型是 INT64。
如果未找到通用超类型，则会引发错误。

示例

输入类型	常见超类型	返回值
INT64 字面量 UINT64 表达式	UINT64	UINT64
TIMESTAMP 表达式 STRING 字面量	TIMESTAMP	TIMESTAMP
NULL 字面量 NULL 字面量	INT64	INT64
BOOL 字面量 TIMESTAMP 字面量	无	错误

聚合函数

聚合函数是将组中的行汇总为单个值的函数。例如，COUNT、MIN 和 MAX 都是聚合函数。

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 子句时，或者当某些聚合函数修饰符从要汇总的组中过滤行时，聚合函数可能需要汇总空组。在这种情况下，COUNT 和 COUNTIF 函数返回 0，而所有其他聚合函数均返回 NULL。

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

ANY_VALUE

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

说明

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

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

支持的参数类型

任意

可选子句

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 以外的所有数据类型。

可选子句

子句按照以下顺序应用：

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

可选子句

子句按照以下顺序应用：

ORDER BY：指定值的顺序。
- 对于每个排序键，默认排序方向均为 ASC。
- 不支持数组排序，因此排序键不能与 expression 相同。
- NULL：在 ORDER BY 子句的上下文中，NULL 是最小的可能值；也就是说，以 ASC 顺序排序时，NULL 会最先显示，而以 DESC 顺序排序时，NULL 则会最后显示。
- 浮点数据类型：请参阅浮点语义，了解排序和分组。
- 如果未指定 ORDER BY，则输出数组中的元素顺序是不确定的，这意味着您在每次使用此函数时可能得到不同的结果。
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。请注意，浮点输入类型的返回结果是不确定的，这意味着您在每次使用此函数时可能得到不同的结果。

可选子句

子句按照以下顺序应用：

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

返回的数据类型

输入	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

示例

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

可选子句

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

返回的数据类型

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

COUNT(*)  [OVER (...)]

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

说明

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

支持的参数类型

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

可选子句

子句按照以下顺序应用：

OVER：指定一个窗口。请参阅分析函数。
DISTINCT：expression 的每个不同值仅聚合到结果中一次。

返回数据类型

INT64

示例

您可以使用 COUNT 函数返回表中的行数或表达式的不同值的数量。例如：

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

如果要计算满足特定条件的表达式的不同值的数量，可以使用以下配方：

COUNT(DISTINCT IF(condition, expression, NULL))

此处，如果 condition 为 TRUE，IF 将返回 expression 的值；否则返回 NULL。周围的 COUNT(DISTINCT ...) 会忽略 NULL 值，因此将只计算 condition 为 TRUE 的不同 expression 值。

例如，如需计算 x 的不同正值数量，请执行以下操作：

SELECT COUNT(DISTINCT IF(x > 0, x, NULL)) AS distinct_positive
FROM UNNEST([1, -2, 4, 1, -5, 4, 1, 3, -6, 1]) AS x;

+-------------------+
| distinct_positive |
+-------------------+
| 3                 |
+-------------------+

或者，如需计算发生某种特定事件的不同日期的数量，请执行以下操作：

WITH Events AS (
  SELECT DATE '2021-01-01' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-02' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-03' AS event_date, 'SUCCESS' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
  UNION ALL
  SELECT DATE '2021-01-04' AS event_date, 'FAILURE' AS event_type
)
SELECT
  COUNT(DISTINCT IF(event_type = 'FAILURE', event_date, NULL))
    AS distinct_dates_with_failures
FROM Events;

+------------------------------+
| distinct_dates_with_failures |
+------------------------------+
| 2                            |
+------------------------------+

COUNTIF

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

说明

返回 expression 的 TRUE 值计数。如果不存在任何输入行，或所有行的 expression 计算结果均为 FALSE 或 NULL，则返回 0。

由于 expression 必须是 BOOL，因此不支持 COUNTIF(DISTINCT ...) 表单。这不会很有用：TRUE 只有一个不同的值。通常，当用户想要组合 COUNTIF 和 DISTINCT 时，他们希望计算满足特定条件的表达式的不同值的数量。一个配方可实现此目的：

COUNT(DISTINCT IF(condition, expression, NULL))

请注意，此示例使用 COUNT，而不是 COUNTIF；将 IF 部分移至内部。如需了解详情，请参阅 COUNT 示例。

支持的参数类型

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。

支持的参数类型

任何可排序的数据类型。

可选子句

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。

支持的参数类型

任何可排序的数据类型。

可选子句

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）。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL，则返回 NULL。

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

支持的参数类型

STRING BYTES

可选子句

子句按照以下顺序应用：

OVER：指定一个窗口。请参阅分析函数。此子句目前与 STRING_AGG() 内的其他所有子句均不兼容。
DISTINCT：expression 的每个不同值仅聚合到结果中一次。
ORDER BY：指定值的顺序。
- 对于每个排序键，默认排序方向均为 ASC。
- NULL：在 ORDER BY 子句的上下文中，NULL 是最小的可能值；也就是说，以 ASC 顺序排序时，NULL 会最先显示，而以 DESC 顺序排序时，NULL 则会最后显示。
- 浮点数据类型：请参阅浮点语义，了解排序和分组。
- 如果还同时指定了 DISTINCT，则排序键必须与 expression 相同。
- 如果未指定 ORDER BY，则输出数组中的元素顺序是不确定的，这意味着您在每次使用此函数时可能得到不同的结果。
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 值之和。

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

支持的参数类型

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

可选子句

子句按照以下顺序应用：

OVER：指定一个窗口。请参阅分析函数。
DISTINCT：expression 的每个不同值仅聚合到结果中一次。

返回数据类型

输入	INT64	NUMERIC	BIGNUMERIC	FLOAT64	INTERVAL
输出	INT64	NUMERIC	BIGNUMERIC	FLOAT64	INTERVAL

特殊情况：

如果输入仅包含 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 (...)]

说明

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

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

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

可选子句

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

返回数据类型

FLOAT64

COVAR_POP

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

说明

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

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

可选子句

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

返回数据类型

FLOAT64

COVAR_SAMP

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

说明

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

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

可选子句

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

返回数据类型

FLOAT64

STDDEV_POP

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

说明

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

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

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

可选子句

子句按照以下顺序应用：

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

返回数据类型

FLOAT64

STDDEV_SAMP

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

说明

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

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

可选子句

子句按照以下顺序应用：

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

返回数据类型

FLOAT64

STDDEV

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

说明

STDDEV_SAMP 的别名。

VAR_POP

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

说明

返回值的总体（偏差）方差。返回结果介于 0 和 +Inf 之间。

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

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

可选子句

子句按照以下顺序应用：

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

返回数据类型

FLOAT64

VAR_SAMP

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

说明

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

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

可选子句

子句按照以下顺序应用：

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

返回数据类型

FLOAT64

VARIANCE

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

说明

VAR_SAMP 的别名。

近似聚合函数

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

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

HyperLogLog++ 函数来估算基数。

APPROX_COUNT_DISTINCT

APPROX_COUNT_DISTINCT(
  expression
)

说明

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

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

支持的参数类型

任意数据类型（ARRAY 和 STRUCT 除外）

返回的数据类型

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 可以是任意受支持的数据类型（ARRAY 和 STRUCT 除外）

number 必须是 INT64。

可选子句

子句会按照以下顺序应用：

DISTINCT：expression 的每个不同值仅聚合到结果中一次。
IGNORE NULLS 或 RESPECT 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 包含两个字段：value 和 sum。value 字段包含输入表达式的值。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 不会忽略 expression 和 weight 参数的 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.MERGE 或 HLL_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 输入，并将这些输入合并到一个新草图。

如果合并的草图以不同的精度初始化，则精度会降级为合并中涉及的最低精度。例如，如果 MERGE_PARTIAL 遇到精度为 14 和 15 的草图，则返回的新草图的精度为 14。

如果无输入或所有输入均为 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 Dataflow或 Apache 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 [format_clause])

说明

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

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

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

某些类型转换可以包含格式子句，用于提供有关如何执行类型转换的说明。例如，您可以指示类型转换将字节序列转换为 BASE64 编码的字符串，而不是 UTF-8 编码的字符串。

每种类型的类型转换都具有独特的格式子句结构；如需了解详情，请参阅相关特定类型转换部分中的说明。

示例

如果 x 为 1，则以下查询的结果为 "true"；如果 x 为任何其他非 NULL 值，则结果为 "false"；如果 x 为 NULL，则结果为 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	如果 `x` 为 `0`，则返回 `FALSE`；否则返回 `TRUE`。
STRING	BOOL	如果 `x` 为 `"true"`，则返回 `TRUE`；如果 `x` 为 `"false"`，则返回 `FALSE` `x` 的所有其他值均无效且抛出错误，而不会转换为 BOOL。转换为 BOOL 类型时，STRING 类型不区分大小写。

CAST AS BYTES

CAST(expression AS BYTES [format_clause])

说明

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

BYTES
STRING

格式子句

将一种类型的表达式转换为另一种类型时，您可以使用格式子句来提供有关如何执行类型转换的说明。如果 expression 为 STRING，则您可以使用本部分中的格式子句。

将字符串格式化为字节

转换规则

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

CAST AS DATE

CAST(expression AS DATE [format_clause])

说明

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

STRING
TIME
DATETIME
TIMESTAMP

格式子句

将字符串格式化为日期和时间

转换规则

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

CAST AS DATETIME

CAST(expression AS DATETIME [format_clause])

说明

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	如果 `x` 为 `TRUE`，则返回 `1`；否则返回 `0`。
STRING	INT64	十六进制字符串可以转换为整数。例如，`0x123` 到 `291`，或 `-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 INTERVAL

CAST(expression AS INTERVAL)

说明

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

STRING

转换规则

原类型	目标类型	对 `x` 进行类型转换时适用的规则
STRING	INTERVAL	字符串转换为间隔时，字符串必须遵循 ISO 8601 Duration 标准或间隔字面量格式“YM DH:M:SF”。部分间隔字面量格式也属于不明确的情况，例如“H:M:S”。如果字符串表达式无效或其表示的间隔超出了所支持的最小/最大范围，则出现错误。

示例

SELECT input, CAST(input AS INTERVAL) AS output
FROM UNNEST([
  '1-2 3 10:20:30.456',
  '1-2',
  '10:20:30',
  'P1Y2M3D',
  'PT10H20M30,456S'
]) input

+--------------------+--------------------+
| input              | output             |
+--------------------+--------------------+
| 1-2 3 10:20:30.456 | 1-2 3 10:20:30.456 |
| 1-2                | 1-2 0 0:0:0        |
| 10:20:30           | 0-0 0 10:20:30     |
| P1Y2M3D            | 1-2 3 0:0:0        |
| PT10H20M30,456S    | 0-0 0 10:20:30.456 |
+--------------------+--------------------+

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 [format_clause [AT TIME ZONE timezone_expr]])

说明

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

INT64
FLOAT64
NUMERIC
BIGNUMERIC
BOOL
BYTES
TIME
DATE
DATETIME
TIMESTAMP
STRING

格式子句

将一种类型的表达式转换为另一种类型时，您可以使用格式子句来提供有关如何执行类型转换的说明。如果 expression 是以下数据类型之一，则您可以使用本部分中的格式子句：

INT64
FLOAT64
NUMERIC
BIGNUMERIC
BYTES
TIME
DATE
DATETIME
TIMESTAMP

STRING 的格式子句包含一个名为 AT TIME ZONE timezone_expr 的可选子句，您可以在 TIMESTAMP 格式化期间使用该子句来指定要使用的特定时区。如果在格式化 TIMESTAMP 时未添加此可选子句，则系统将使用您的当前时区。

如需了解详情，请参阅以下主题：

将字节格式化为字符串
将日期和时间格式化为字符串
将数字类型格式化为字符串

转换规则

原类型	目标类型	对 `x` 进行类型转换时适用的规则
FLOAT64	STRING	返回一个近似的字符串表示形式。
BOOL	STRING	如果 `x` 为 `TRUE`，则返回 `"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 位数。
INTERVAL	STRING	间隔转换为字符串的格式为 `Y-M D H:M:S`。

示例

SELECT CAST(CURRENT_DATE() AS STRING) AS current_date

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

SELECT CAST(CURRENT_DATE() AS STRING FORMAT 'DAY') AS current_day

+-------------+
| current_day |
+-------------+
| MONDAY      |
+-------------+

SELECT CAST(
  TIMESTAMP '2008-12-25 00:00:00+00:00'
  AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS date_time_to_string

-- Results depend upon where this query was executed.
+------------------------------+
| date_time_to_string          |
+------------------------------+
| 2008-12-24 16:00:00 -08:00   |
+------------------------------+

SELECT CAST(
  TIMESTAMP '2008-12-25 00:00:00+00:00'
  AS STRING FORMAT 'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  AT TIME ZONE 'Asia/Kolkata') AS date_time_to_string

-- Because the time zone is specified, the result is always the same.
+------------------------------+
| date_time_to_string          |
+------------------------------+
| 2008-12-25 05:30:00 +05:30   |
+------------------------------+

CAST AS STRUCT

CAST(expression AS STRUCT)

说明

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

STRUCT

转换规则

原类型	目标类型	对 `x` 进行类型转换时适用的规则
STRUCT	STRUCT	仅在满足以下条件时可用：这两个 STRUCT 的字段数量相同。原始 STRUCT 字段类型可显式转换为相应的目标 STRUCT 字段类型（由字段顺序定义，而不是由字段名称定义）。

CAST AS TIME

CAST(expression AS TIME [format_clause])

说明

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

STRING
TIME
DATETIME
TIMESTAMP

格式子句

将字符串格式化为日期和时间

转换规则

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

CAST AS TIMESTAMP

CAST(expression AS TIMESTAMP [format_clause [AT TIME ZONE timezone_expr]])

说明

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

STRING
TIME
DATETIME
TIMESTAMP

格式子句

将字符串格式化为日期和时间

TIMESTAMP 的格式子句包含一个名为 AT TIME ZONE timezone_expr 的可选子句，您可以在格式化期间使用该子句来指定要使用的特定时区。如果未添加此可选子句，则系统将使用您的当前时区。

转换规则

原类型	目标类型	对 `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 |
+-----------------------------+

以下示例将字符串格式的日期和时间转换为时间戳。这些示例将返回与上一个示例相同的输出。

SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE 'America/Los_Angeles') AS as_timestamp

SELECT CAST("06/02/2020 17:00:53.110" AS TIMESTAMP FORMAT 'MM/DD/YYYY HH:MI:SS' AT TIME ZONE '00') AS as_timestamp

SELECT CAST('06/02/2020 17:00:53.110 +00' AS TIMESTAMP FORMAT 'YYYY-MM-DD HH:MI:SS TZH') AS as_timestamp

PARSE_BIGNUMERIC

PARSE_BIGNUMERIC(string_expression)

说明

将字符串转换为 BIGNUMERIC 值。

字符串中包含的数字字面量不得超出 BIGNUMERIC 类型的最大精度或范围，否则将发生错误。如果小数点后的位数超过 38 位，则生成的 BIGNUMERIC 值会向远离 0 方向按中间数舍入，从而在小数点后保留 38 位数。


-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_BIGNUMERIC("123.45") AS parsed

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

-- This example shows how a string with an exponent is parsed.
SELECT PARSE_BIGNUMERIC("123.456E37") AS parsed

+-----------------------------------------+
| parsed                                  |
+-----------------------------------------+
| 123400000000000000000000000000000000000 |
+-----------------------------------------+

-- This example shows the rounding when digits after the decimal point exceeds 38.
SELECT PARSE_BIGNUMERIC("1.123456789012345678901234567890123456789") as parsed

+------------------------------------------+
| parsed                                   |
+------------------------------------------+
| 1.12345678901234567890123456789012345679 |
+------------------------------------------+

此函数类似于使用 CAST AS BIGNUMERIC 函数，不同之处在于 PARSE_BIGNUMERIC 函数仅接受字符串输入且允许在字符串中包含以下内容：

符号 (+/-) 与数字之间的空格
数字后面的符号 (+/-)

有效输入字符串适用的规则：

规则	示例输入	输出
字符串只能包含数字、英文逗号、小数点和符号。	"- 12,34567,89.0"	-123456789
空格可以在除数字之间之外的任何地方使用。	" - 12.345 "	-12.345
小数点前只能使用数字和英文逗号。	" 12,345,678"	12345678
小数点后只能使用数字。	"1.234 "	1.234
对于指数，使用 `E` 或 `e`。允许在 `e` 后面使用数字和前导符号指示符。	" 123.45e-1"	12.345
如果整数部分不为空，则必须包含至少一个数字。	" 0,.12 -"	-0.12
如果字符串包含小数点，则必须包含至少一个数字。	" .1"	0.1
字符串不能包含多个符号。	" 0.5 +"	0.5

返回数据类型

BIGNUMERIC

示例

以下示例显示了在符号与数字之前、之后和之间包含空格的输入：

SELECT PARSE_BIGNUMERIC("  -  12.34 ") as parsed;

+--------+
| parsed |
+--------+
| -12.34 |
+--------+

以下示例显示了包含指数且在数字后面包含符号的输入：

SELECT PARSE_BIGNUMERIC("12.34e-1-") as parsed;

+--------+
| parsed |
+--------+
| -1.234 |
+--------+

以下示例显示了在数字的整数部分包含多个英文逗号的输入：

SELECT PARSE_BIGNUMERIC("  1,2,,3,.45 + ") as parsed;

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

以下示例显示了包含小数点且整数部分中没有数字的输入：

SELECT PARSE_BIGNUMERIC(".1234  ") as parsed;

+--------+
| parsed |
+--------+
| 0.1234 |
+--------+

无效输入的示例

以下示例无效，因为整数部分不包含任何数字：

SELECT PARSE_BIGNUMERIC(",,,.1234  ") as parsed;

以下示例无效，因为数字之间包含空格：

SELECT PARSE_BIGNUMERIC("1  23.4 5  ") as parsed;

以下示例无效，因为除指数外，该数字为空：

SELECT PARSE_BIGNUMERIC("  e1 ") as parsed;

以下示例无效，因为该字符串包含多个符号：

SELECT PARSE_BIGNUMERIC("  - 12.3 - ") as parsed;

以下示例无效，因为该数字的值不在 BIGNUMERIC 范围内：

SELECT PARSE_BIGNUMERIC("12.34E100 ") as parsed;

以下示例无效，因为该字符串包含无效字符：

SELECT PARSE_BIGNUMERIC("$12.34") as parsed;

PARSE_NUMERIC

PARSE_NUMERIC(string_expression)

说明

将字符串转换为 NUMERIC 值。

字符串中包含的数字字面量不得超出 NUMERIC 类型的最大精度或范围，否则将发生错误。如果小数点后的位数超过 9 位，则生成的 NUMERIC 值会向远离 0 方向按中间数舍入，从而在小数点后保留 9 位数。


-- This example shows how a string with a decimal point is parsed.
SELECT PARSE_NUMERIC("123.45") AS parsed

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

-- This example shows how a string with an exponent is parsed.
SELECT PARSE_NUMERIC("12.34E27") as parsed

+-------------------------------+
| parsed                        |
+-------------------------------+
| 12340000000000000000000000000 |
+-------------------------------+

-- This example shows the rounding when digits after the decimal point exceeds 9.
SELECT PARSE_NUMERIC("1.0123456789") as parsed

+-------------+
| parsed      |
+-------------+
| 1.012345679 |
+-------------+

此函数类似于使用 CAST AS NUMERIC 函数，不同之处在于 PARSE_NUMERIC 函数仅接受字符串输入且允许在字符串中包含以下内容：

符号 (+/-) 与数字之间的空格
数字后面的符号 (+/-)

有效输入字符串适用的规则：

规则	示例输入	输出
字符串只能包含数字、英文逗号、小数点和符号。	"- 12,34567,89.0"	-123456789
空格可以在除数字之间之外的任何地方使用。	" - 12.345 "	-12.345
小数点前只能使用数字和英文逗号。	" 12,345,678"	12345678
小数点后只能使用数字。	"1.234 "	1.234
对于指数，使用 `E` 或 `e`。允许在 `e` 后面使用数字和前导符号指示符。	" 123.45e-1"	12.345
如果整数部分不为空，则必须包含至少一个数字。	" 0,.12 -"	-0.12
如果字符串包含小数点，则必须包含至少一个数字。	" .1"	0.1
字符串不能包含多个符号。	" 0.5 +"	0.5

返回数据类型

NUMERIC

示例

以下示例显示了在符号与数字之前、之后和之间包含空格的输入：

SELECT PARSE_NUMERIC("  -  12.34 ") as parsed;

+--------+
| parsed |
+--------+
| -12.34 |
+--------+

以下示例显示了包含指数且在数字后面包含符号的输入：

SELECT PARSE_NUMERIC("12.34e-1-") as parsed;

+--------+
| parsed |
+--------+
| -1.234 |
+--------+

以下示例显示了在数字的整数部分包含多个英文逗号的输入：

SELECT PARSE_NUMERIC("  1,2,,3,.45 + ") as parsed;

+--------+
| parsed |
+--------+
| 123.45 |
+--------+

以下示例显示了包含小数点且整数部分中没有数字的输入：

SELECT PARSE_NUMERIC(".1234  ") as parsed;

+--------+
| parsed |
+--------+
| 0.1234 |
+--------+

无效输入的示例

以下示例无效，因为整数部分不包含任何数字：

SELECT PARSE_NUMERIC(",,,.1234  ") as parsed;

以下示例无效，因为数字之间包含空格：

SELECT PARSE_NUMERIC("1  23.4 5  ") as parsed;

以下示例无效，因为除指数外，该数字为空：

SELECT PARSE_NUMERIC("  e1 ") as parsed;

以下示例无效，因为该字符串包含多个符号：

SELECT PARSE_NUMERIC("  - 12.3 - ") as parsed;

以下示例无效，因为该数字的值不在 BIGNUMERIC 范围内：

SELECT PARSE_NUMERIC("12.34E100 ") as parsed;

以下示例无效，因为该字符串包含无效字符：

SELECT PARSE_NUMERIC("$12.34") as parsed;

SAFE_CAST

SAFE_CAST(expression AS typename [format_clause])

说明

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

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

如果您想保护自己的查询，避免这些类型的错误，您可以使用 SAFE_CAST。SAFE_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。

其他转换函数

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

转换函数	原类型	目标类型
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
TO_JSON_STRING	所有数据类型	STRING

CAST 的格式子句

format_clause:
  FORMAT format_model

format_model:
  format_string_expression

格式子句可用于某些 CAST 函数。您可以使用格式子句提供有关如何执行类型转换的说明。例如，您可以指示类型转换将字节序列转换为 BASE64 编码的字符串，而不是 UTF-8 编码的字符串。

格式子句包含格式模型。格式模型可以包含各种格式元素，这些格式元素将组合在一起成为一个格式字符串。

将字节格式化为字符串

CAST(bytes_expression AS STRING FORMAT format_string_expression)

您可以使用格式字符串中的格式元素将字节序列转换为字符串。如果无法使用格式元素来格式化这些字节，则会返回错误。如果字节序列为 NULL，则结果为 NULL。格式元素不区分大小写。

格式元素	返回值	示例
HEX	将字节序列转换为十六进制字符串。	输入：b'\x00\x01\xEF\xFF' 输出：0001efff
BASEX	将字节序列转换为 BASEX 编码的字符串。X 代表以下数字之一：2、8、16、32、64。	输入为 BASE8：b'\x02\x11\x3B' 输出：00410473
BASE64M	根据 MIME 的 rfc 2045，将字节序列转换为 BASE64 编码的字符串。每 76 个字符生成一个换行符（“\n”）。	输入：b'\xde\xad\xbe\xef' 输出：3q2+7w==
ASCII	将 ASCII 值的字节序列转换为字符串。如果输入中包含无效 ASCII 编码的字节，则会返回错误。	输入：b'\x48\x65\x6c\x6c\x6f' 输出：Hello
UTF-8	将 UTF-8 值的字节序列转换为字符串。如果输入中包含无效 UTF-8 编码的字节，则会返回错误。	输入：b'\x24' 输出：$
UTF8	行为与 UTF-8 相同。

返回类型

STRING

示例

SELECT CAST(b'\x48\x65\x6c\x6c\x6f' AS STRING FORMAT 'ASCII') AS bytes_to_string;

+-----------------+
| bytes_to_string |
+-----------------+
| Hello           |
+-----------------+

将字符串格式化为字节

CAST(string_expression AS BYTES FORMAT format_string_expression)

您可以使用格式字符串中的格式元素将字符串转换为字节。如果无法使用格式元素来格式化该字符串，则会返回错误。格式元素不区分大小写。

如果使用了 BASE64 或 BASE64M 格式元素，则会忽略字符串表达式中的空白字符（例如 \n）。

格式元素	返回值	示例
HEX	将十六进制编码的字符串转换为字节。如果输入中包含不属于 HEX 编码字母表（0~9，不区分大小写的 a~f）的字符，则会返回错误。	输入：'0001efff' 输出：b'\x00\x01\xEF\xFF'
BASEX	将 BASEX 编码的字符串转换为字节。X 代表以下数字之一：2、8、16、32、64。如果输入中包含不属于 BASEX 编码字母表的字符，则会返回错误；但如果格式元素是 BASE64，则会返回空白字符。	输入为 BASE8：'00410473' 输出：b'\x02\x11\x3B'
BASE64M	将 BASE64 编码的字符串转换为字节。如果输入中包含不属于 rfc 2045 中定义的 BASE64 编码字母表的非空白字符，则会返回错误。BASE64M 和 BASE64 解码具有相同的行为。	输入：'3q2+7w==' 输出：b'\xde\xad\xbe\xef'
ASCII	将仅包含 ASCII 字符的字符串转换为字节。如果输入中包含非 ASCII 字符，则会返回错误。	输入：'Hello' 输出：b'\x48\x65\x6c\x6c\x6f'
UTF-8	将字符串转换为 UTF-8 字节序列。	输入：'$' 输出：b'\x24'
UTF8	行为与 UTF-8 相同。

返回类型

BYTES

示例

SELECT CAST('Hello' AS BYTES FORMAT 'ASCII') AS string_to_bytes

+-------------------------+
| string_to_bytes         |
+-------------------------+
| b'\x48\x65\x6c\x6c\x6f' |
+-------------------------+

将日期和时间格式化为字符串

您可以将以下日期和时间部分格式化为字符串：

将年部分格式化为字符串
将月部分格式化为字符串
将日部分格式化为字符串
将小时部分格式化为字符串
将分钟部分格式化为字符串
将秒部分格式化为字符串
将子午线指示符格式化为字符串
将时区格式化为字符串
将字面量格式化为字符串

如果您将某些日期或时间部分格式化为字符串且输出中包含字母，则支持大小写匹配。如需了解详情，请参阅大小写匹配。

大小写匹配

当某些格式元素的输出中包含字母时，输出中的字母大小写会与格式元素的字母大小写匹配，这意味着将根据格式元素的大小写方式指定输出中字词的大小写方式。此过程称为大小写匹配。适用的规则如下：

如果元素的前两个字母均为大写字母，则输出中的字词将会大写。例如 DAY = THURSDAY。
如果元素的第一个字母为大写，第二个字母为小写，则输出中每个单词的首字母均为大写，其他字母为小写。例如 Day = Thursday。
如果元素的第一个字母为小写，则输出中的所有字母均为小写。例如 day = thursday。

将年部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含年部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的年部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括年部分格式元素）。

以下数据类型包含年部分：

DATE
DATETIME
TIMESTAMP

如果 expression 或 format_string_expression 为 NULL，则返回值为 NULL。如果 format_string_expression 是空字符串，则输出为空字符串。如果 format_string_expression 包含不支持的格式元素值或者 expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
YYYY	年，不少于 4 位数字。	输入：DATE '2018-01-30' 输出：2018 输入：DATE '76-01-30' 输出：0076 输入：DATE '10000-01-30' 输出：10000
YYY	年，仅最后 3 位数字。	输入：DATE '2018-01-30' 输出：018 输入：DATE '98-01-30' 输出：098
YY	年，仅最后 2 位数字。	输入：DATE '2018-01-30' 输出：18 输入：DATE '8-01-30' 输出：08
是	年，仅最后一位数字。	输入：DATE '2018-01-30' 输出：8
RRRR	行为与 YYYY 相同。
RR	行为与 YY 相同。

返回类型

STRING

示例

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'YYYY') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 2018                |
+---------------------+

将月部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含月部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的月部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括月部分格式元素）。

以下数据类型包含月部分：

DATE
DATETIME
TIMESTAMP

格式元素	返回值	示例
MM	月，2 位数字。	输入：DATE '2018-01-30' 输出：01
月	3 个字符的月份缩写名称。语言区域“en-US”的月份缩写名称为：JAN、FEB、MAR、APR、May、JUN、JUL、AUG、SEP、OCT、NOV、DEC。支持大小写匹配。	输入：DATE '2018-01-30' 输出：JAN
月	月份的名称。支持大小写匹配。	输入：DATE '2018-01-30' 输出：JANUARY

返回类型

STRING

示例

SELECT CAST(DATE '2018-01-30' AS STRING FORMAT 'MONTH') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| JANUARY             |
+---------------------+

将日部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含日部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的日部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括日部分格式元素）。

以下数据类型包含日部分：

DATE
DATETIME
TIMESTAMP

格式元素	返回值	示例
DAY	星期几的本地化名称。右侧将填充空格，使得输出大小正好为 9 位字符。支持大小写匹配。	输入：DATE '2020-12-31' 输出：THURSDAY
DY	用 3 个字符表示的星期几的本地化缩写名称。语言区域“en-US”的星期几缩写名称为：MON、TUE、WED、THU、FRI、SAT、SUN。支持大小写匹配。	输入：DATE '2020-12-31' 输出：THU
D	星期几（1 到 7），从星期日 (1) 开始算起。	输入：DATE '2020-12-31' 输出：4
DD	月份中的 2 位数日期。	输入：DATE '2018-12-02' 输出：02
DDD	年份中的 3 位数日期。	输入：DATE '2018-02-03' 输出：034

返回类型

STRING

示例

SELECT CAST(DATE '2018-02-15' AS STRING FORMAT 'DD') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 15                  |
+---------------------+

将小时部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含小时部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的小时部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括小时部分格式元素）。

以下数据类型包含小时部分：

TIME
DATETIME
TIMESTAMP

格式元素	返回值	示例
HH	一天中的时段，12 小时制，2 位数字。	输入：TIME '21:30:00' 输出：09
HH12	一天中的时段，12 小时制。	输入：TIME '21:30:00' 输出：09
HH24	一天中的时段，24 小时制，2 位数字。	输入：TIME '21:30:00' 输出：21

返回类型

STRING

示例

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH24') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 21                  |
+---------------------+

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'HH12') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 09                  |
+---------------------+

将分钟部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含分钟部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的分钟部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括分钟部分格式元素）。

以下数据类型包含分钟部分：

TIME
DATETIME
TIMESTAMP

格式元素	返回值	示例
MI	分钟，2 位数字。	输入：TIME '01:02:03' 输出：02

返回类型

STRING

示例

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'MI') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 30                  |
+---------------------+

将秒部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含秒部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的秒部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括秒部分格式元素）。

以下数据类型包含秒部分：

TIME
DATETIME
TIMESTAMP

格式元素	返回值	示例
SS	一分钟中的秒数，2 位数字。	输入：TIME '01:02:03' 输出：03
SSSSS	一天中的秒数，5 位数字。	输入：TIME '01:02:03' 输出：03723
FFn	秒的小数部分，长度为 `n` 位数字。将 `n` 替换为 1 到 9 之间的值。例如，FF5。秒的小数部分会进行舍入，以适应输出的大小。	FF1 的输入：TIME '01:05:07.16' 输出：1 FF2 的输入：TIME '01:05:07.16' 输出：16 FF3 的输入：TIME '01:05:07.16' 输出：016

返回类型

STRING

示例

SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'SS') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 25                  |
+---------------------+

SELECT CAST(TIME '21:30:25.16' AS STRING FORMAT 'FF2') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| 16                  |
+---------------------+

将子午线指示符部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含子午线指示符部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的子午线指示符部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括子午线指示符部分格式元素）。

以下数据类型包含子午线指示符部分：

TIME
DATETIME
TIMESTAMP

格式元素	返回值	示例
A.M.	如果时间小于 12，则返回 A.M.；否则，返回 P.M.。输出中的字母大小写由格式元素的第一个字母的大小写决定。	A.M. 的输入：TIME '01:02:03' 输出：A.M. A.M. 的输入：TIME '16:02:03' 输出：P.M. a.m. 的输入：TIME '01:02:03' 输出：a.m. a.M. 的输入：TIME '01:02:03' 输出：a.m.
AM	如果时间小于 12，则返回 AM；否则，返回 PM。输出中的字母大小写由格式元素的第一个字母的大小写决定。	AM 的输入：TIME '01:02:03' 输出：AM AM 的输入：TIME '16:02:03' 输出：PM am 的输入：TIME '01:02:03' 输出：am aM 的输入：TIME '01:02:03' 输出：am
P.M.	输出与 A.M. 格式元素相同。
PM	输出与 AM 格式元素相同。

返回类型

STRING

示例

SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '21:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| PM                  |
+---------------------+

SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'AM') AS date_time_to_string;
SELECT CAST(TIME '01:30:00' AS STRING FORMAT 'PM') AS date_time_to_string;

+---------------------+
| date_time_to_string |
+---------------------+
| AM                  |
+---------------------+

将时区部分格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

将包含时区部分的数据类型转换为字符串。包含格式元素，用于提供有关如何进行类型转换的说明。

expression：此表达式包含需要格式化的时区部分所属的数据类型。
format_string_expression：包含格式元素的字符串（包括时区部分格式元素）。

以下数据类型包含时区部分：

DATE
TIME
DATETIME
TIMESTAMP

格式元素	返回值	示例
TZH	时区的小时偏移量；包含 `+/-` 符号和用 2 位数字表示的小时数。	输入时间戳：TIMESTAMP '2008-12-25 05:30:00+00' 输出：−08
TZM	时区的分钟偏移量；仅包含用 2 位数字表示的分钟数。	输入时间戳：TIMESTAMP '2008-12-25 05:30:00+00' 输出：00

返回类型

STRING

示例

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH') AS date_time_to_string;

-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| -08                 |
+---------------------+

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZH' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| +05                 |
+---------------------+

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM') AS date_time_to_string;

-- Results depend upon where this query was executed.
+---------------------+
| date_time_to_string |
+---------------------+
| 00                  |
+---------------------+

SELECT CAST(TIMESTAMP '2008-12-25 00:00:00+00:00' AS STRING FORMAT 'TZM' AT TIME ZONE 'Asia/Kolkata')
AS date_time_to_string;

-- Because the time zone is specified, the result is always the same.
+---------------------+
| date_time_to_string |
+---------------------+
| 30                  |
+---------------------+

将字面量格式化为字符串

CAST(expression AS STRING FORMAT format_string_expression)

格式元素	返回值	示例
-	输出与输入相同。	-
.	输出与输入相同。	.
/	输出与输入相同。	/
,	输出与输入相同。	,
'	输出与输入相同。	'
;	输出与输入相同。	;
:	输出与输入相同。	:
空格符	输出与输入相同。此处空白字符代表 ASCII 值为 32 的空格字符；而不是制表符或换行符等其他类型的空白字符。格式模型中任何不是 ASCII 32 字符的空白字符都会导致生成错误。
"文本"	输出是英文双引号内的值。如需保留英文双引号或反斜杠字符，请使用 `\"` 或 `\\` 转义序列。不支持其他转义序列。	输入："abc" 输出：abc 输入："a\"b\\c" 输出：a"b\c

将字符串格式化为日期和时间

您可以使用以下日期和时间部分格式化字符串：

将字符串格式化为年部分
将字符串格式化为月部分
将字符串格式化为日部分
将字符串格式化为小时部分
将字符串格式化为分钟部分
将字符串格式化为秒部分
将字符串格式化为子午线指示符部分
将字符串格式化为时区部分
将字符串格式化为字面量部分

在使用日期和时间部分格式化字符串时，您必须遵循格式模型规则。

格式模型规则

将字符串转换为日期和时间部分时，必须确保格式模型有效。格式模型表示作为 format_string_expression 传入到 CAST(string_expression AS type FORMAT format_string_expression) 的元素，并将根据以下规则进行验证：

最多包含以下每个部分中某一类型的值：子午线指示符、年、月、日、小时。
非字面量和非空格格式元素不能多次出现。
如果包含一年中的某一天格式元素 DDD，则不能包含月份。
如果包含 24 小时制格式元素 HH24，则不能包含 12 小时制格式元素或子午线指示符。
如果包含 12 小时制格式元素 HH12 或 HH，则还必须包含子午线指示符。
如果包含子午线指示符，则还必须包含 12 小时制格式元素。
如果包含一天中的秒数格式元素 SSSSS，则不能包含以下任何一项：小时、分钟、秒或子午线指示符。
不能包含其所设置的值在目标类型中不存在的格式元素。例如，小时格式元素（如 HH24）不能出现在要转换为 DATE 的字符串中。

将字符串格式化为年部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的年份转换为包含年部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含需要格式化的年部分所属的字符串。
type：要转换为的数据类型；必须包含年部分。
format_string_expression：包含格式元素的字符串（包括年部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含年部分：

DATE
DATETIME
TIMESTAMP

如果 string_expression 缺少 YEAR 部分，并且返回类型包含此部分，则 YEAR 会设置为当前年份。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
YYYY	如果带分隔符，则匹配 1 到 5 位数字。如果不带分隔符，则匹配 4 位数字。将年部分设置为匹配的数字。	MM-DD-YYYY 的输入：'03-12-2018' 输出为 DATE：2018-12-03 YYYY-MMDD 的输入：'10000-1203' 输出为 DATE：10000-12-03 YYYY 的输入：'18' 输出为 DATE：2018-03-01（假设当前日期为 2021 年 3 月 23 日）
YYY	匹配 3 位数字。将年部分的最后 3 位数字设置为匹配的数字。	YYY-MM-DD 的输入：'018-12-03' 输出为 DATE：2018-12-03 YYY-MM-DD 的输入：'038-12-03' 输出为 DATE：2038-12-03
YY	匹配 2 位数字。将年部分的最后 2 位数字设置为匹配的数字。	YY-MM-DD 的输入：'18-12-03' 输出为 DATE：2018-12-03 YY-MM-DD 的输入：'38-12-03' 输出为 DATE：2038-12-03
是	匹配 1 位数字。将年部分的最后一位数字设置为匹配的数字。	Y-MM-DD 的输入：'8-12-03' 输出为 DATE：2008-12-03
Y,YYY	匹配依次包含 1 到 2 位数字、英文逗号、3 位数字的格式。将年部分设置为匹配的数字。	Y,YYY-MM-DD 的输入：'2,018-12-03' 输出为 DATE：2008-12-03
RRRR	行为与 YYYY 相同。
RR	匹配 2 位数字。如果输入的 2 位数字介于 00 和 49 之间，且当前年份的最后 2 位数字介于 00 和 49 之间，则返回年份的前 2 位数字与当前年份相同。如果当前年份的最后 2 位数字介于 50 和 99 之间，则返回年份的前 2 位数字比当前年份的前 2 位数字大 1。如果输入的 2 位数字介于 50 和 99 之间，且当前年份的最后 2 位数字介于 00 和 49 之间，则返回年份的前 2 位数字比当前年份的前 2 位数字小 1。如果当前年份的最后 2 位数字介于 50 和 99 之间，则返回年份的前 2 位数字与当前年份相同。	RR-MM-DD 的输入：'18-12-03' 输出为 DATE：2018-12-03（执行于 2021 年）输出为 DATE：2118-12-03（执行于 2050 年） RR-MM-DD 的输入：'50-12-03' 输出为 DATE：2050-12-03（执行于 2021 年）输出为 DATE：2050-12-03（执行于 2050 年）

返回类型

字符串转换为的目标数据类型。此数据类型可为：

DATE
DATETIME
TIMESTAMP

示例

SELECT CAST('18-12-03' AS DATE FORMAT 'YY-MM-DD') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 2018-02-03          |
+---------------------+

将字符串格式化为月部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的月份转换为包含月部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含您需要格式化的月部分所属的字符串。
type：要转换为的数据类型；必须包含月部分。
format_string_expression：包含格式元素的字符串（包括月部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含月部分：

DATE
DATETIME
TIMESTAMP

如果 string_expression 缺少 MONTH 部分，并且返回类型包含此部分，则 MONTH 会设置为当前月份。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
MM	匹配 2 位数字。将月部分设置为匹配的数字。	MM-DD-YYYY 的输入：'03-12-2018' 输出为 DATE：2018-12-03
月	匹配 3 个字母。将月部分设置为可解释为月份缩写名称的匹配字符串。	MON DD, YYYY 的输入：'DEC 03, 2018' 输出为 DATE：2018-12-03
月	匹配 9 个字母。将月部分设置为可解释为月份名称的匹配字符串。	MONTH DD, YYYY 的输入：'DECEMBER 03, 2018' 输出为 DATE：2018-12-03

返回类型

字符串转换为的目标数据类型。此数据类型可为：

DATE
DATETIME
TIMESTAMP

示例

SELECT CAST('DEC 03, 2018' AS DATE FORMAT 'MON DD, YYYY') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 2018-12-03          |
+---------------------+

将字符串格式化为日部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的日期转换为包含日部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含需要格式化的日部分所属的字符串。
type：要转换为的数据类型；必须包含日部分。
format_string_expression：包含格式元素的字符串（包括日部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含日部分：

DATE
DATETIME
TIMESTAMP

如果 string_expression 缺少 DAY 部分，并且返回类型包含此部分，则 DAY 会设置为 1。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
DD	匹配 2 位数字。将日部分设置为匹配的数字。	MONTH DD, YYYY 的输入：'DECEMBER 03, 2018' 输出为 DATE：2018-12-03

返回类型

字符串转换为的目标数据类型。此数据类型可为：

DATE
DATETIME
TIMESTAMP

示例

SELECT CAST('DECEMBER 03, 2018' AS DATE FORMAT 'MONTH DD, YYYY') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 2018-12-03          |
+---------------------+

将字符串格式化为小时部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的小时转换为包含小时部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含需要格式化的小时部分所属的字符串。
type：要转换为的数据类型；必须包含小时部分。
format_string_expression：包含格式元素的字符串（包括小时部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含小时部分：

TIME
DATETIME
TIMESTAMP

如果 string_expression 缺少 HOUR 部分，并且返回类型包含此部分，则 HOUR 会设置为 0。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
HH	匹配 2 位数字。如果匹配的数字 `n` 为 `12`，则设置为 `temp = 0`；否则，设置为 `temp = n`。如果 A.M./P.M. 格式元素的匹配值为 P.M.，则设置为 `temp = n + 12`。将小时部分设置为 `temp`。包含 HH 时，格式模型中必须同时也包含子午线指示符。	HH:MI P.M. 的输入：'03:30 P.M.' 输出为 TIME：15:30:00
HH12	行为与 HH 相同。
HH24	匹配 2 位数字。将小时部分设置为匹配的数字。	HH24:MI 的输入：'15:30' 输出为 TIME：15:30:00

返回类型

字符串转换为的目标数据类型。此数据类型可为：

TIME
DATETIME
TIMESTAMP

示例

SELECT CAST('15:30' AS TIME FORMAT 'HH24:MI') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

将字符串格式化为分钟部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的分钟转换为包含分钟部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含需要格式化的分钟部分所属的字符串。
type：要转换为的数据类型；必须包含分钟部分。
format_string_expression：包含格式元素的字符串（包括分钟部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含分钟部分：

TIME
DATETIME
TIMESTAMP

如果 string_expression 缺少 MINUTE 部分，并且返回类型包含此部分，则 MINUTE 会设置为 0。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
MI	匹配 2 位数字。将分钟部分设置为匹配的数字。	HH:MI P.M. 的输入：'03:30 P.M.' 输出为 TIME：15:30:00

返回类型

字符串转换为的目标数据类型。此数据类型可为：

TIME
DATETIME
TIMESTAMP

示例

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI P.M.') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

将字符串格式化为秒部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的秒数转换为包含秒部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含需要格式化的秒部分所属的字符串。
type：要转换为的数据类型；必须包含秒部分。
format_string_expression：包含格式元素的字符串（包括秒部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含秒部分：

TIME
DATETIME
TIMESTAMP

如果 string_expression 缺少 SECOND 部分，并且返回类型包含此部分，则 SECOND 会设置为 0。

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
SS	匹配 2 位数字。将秒部分设置为匹配的数字。	HH:MI:SS P.M. 的输入：'03:30:02 P.M.' 输出为 TIME：15:30:02
SSSSS	匹配 5 位数字。通过将匹配的数字解释为午夜后经过的秒数来设置小时、分钟和秒部分。	SSSSS 的输入：'03723' 输出为 TIME：01:02:03
FFn	匹配 `n` 位数字，其中 `n` 是该格式元素中 FF 后面的数字。将秒部分的小数部分设置为匹配的数字。	HH24:MI:SS.FF1 的输入：'01:05:07.16' 输出为 TIME：01:05:07.2 HH24:MI:SS.FF2 的输入：'01:05:07.16' 输出为 TIME：01:05:07.16 HH24:MI:SS.FF3 的输入：'FF3: 01:05:07.16' 输出为 TIME：01:05:07.160

返回类型

字符串转换为的目标数据类型。此数据类型可为：

TIME
DATETIME
TIMESTAMP

示例

SELECT CAST('01:05:07.16' AS TIME FORMAT 'HH24:MI:SS.FF1') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 01:05:07.2          |
+---------------------+

将字符串格式化为子午线指示符部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的子午线指示符转换为包含子午线指示符部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含您需要格式化的子午线指示符部分所属的字符串。
type：要转换为的数据类型；必须包含子午线指示符部分。
format_string_expression：包含格式元素的字符串（包括子午线指示符部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含子午线指示符部分：

TIME
DATETIME
TIMESTAMP

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
A.M. 或 P.M.	使用正则表达式 `'(A\|P)\.M\.'` 进行匹配。	HH:MI A.M. 的输入：'03:30 A.M.' 输出为 TIME：03:30:00 HH:MI P.M. 的输入：'03:30 P.M.' 输出为 TIME：15:30:00 HH:MI P.M. 的输入：'03:30 A.M.' 输出为 TIME：03:30:00 HH:MI A.M. 的输入：'03:30 P.M.' 输出为 TIME：15:30:00 HH:MI a.m. 的输入：'03:30 a.m.' 输出为 TIME：03:30:00

返回类型

字符串转换为的目标数据类型。此数据类型可为：

TIME
DATETIME
TIMESTAMP

示例

SELECT CAST('03:30 P.M.' AS TIME FORMAT 'HH:MI A.M.') AS string_to_date_time

+---------------------+
| string_to_date_time |
+---------------------+
| 15:30:00            |
+---------------------+

将字符串格式化为时区部分

CAST(string_expression AS type FORMAT format_string_expression)

将字符串格式的时区转换为包含时区部分的数据类型。包含格式元素，用于提供有关如何进行类型转换的说明。

string_expression：此表达式包含您需要格式化的时区部分所属的字符串。
type：要转换为的数据类型；必须包含时区部分。
format_string_expression：包含格式元素的字符串（包括时区部分格式元素）。此字符串中的各个格式元素一起被定义为格式模型，该模型必须遵循以下规则。

以下数据类型包含时区部分：

DATE
TIME
DATETIME
TIMESTAMP

如果 format_string_expression 包含不支持的格式元素值或者 string_expression 不包含由格式元素指定的任何值，则会生成错误。

格式元素	返回值	示例
TZH	使用正则表达式 `'(\+\|\-\| )[0-9]{2}'` 进行匹配。将时区和小时部分设置为匹配的符号和数字。将时区符号设置为匹配字符串的首字母。数字 2 表示最多匹配 2 位数字以进行非完全匹配；如果正好是 2 位数字，则进行完全匹配。	YYYY-MM-DD HH:MI:SSTZH 的输入：'2008-12-25 05:30:00-08' 输出为 TIMESTAMP：2008-12-25 05:30:00-08
TZM	匹配 2 位数字。将 `n` 设置为匹配的数字。如果时区符号是减号，则将时区的分钟部分设置为 `-n`。否则，将时区的分钟部分设置为 `n`。	YYYY-MM-DD HH:MI:SSTZH 的输入：'2008-12-25 05:30:00+05.30' 输出为 TIMESTAMP：2008-12-25 05:30:00+05.30

返回类型

字符串转换为的目标数据类型。此数据类型可为：

DATE
TIME
DATETIME
TIMESTAMP

示例

SELECT CAST('2020.06.03 00:00:53+00' AS TIMESTAMP FORMAT 'YYYY.MM.DD HH:MI:SSTZH') AS string_to_date_time

+-----------------------------+
| as_timestamp                |
+-----------------------------+
| 2020-06-03 00:00:53.110 UTC |
+-----------------------------+

将字符串格式化为字面量

CAST(string_expression AS data_type FORMAT format_string_expression)

格式元素	返回值	示例
-	输出与输入相同。
.	输出与输入相同。	.
/	输出与输入相同。	/
,	输出与输入相同。	,
'	输出与输入相同。	'
;	输出与输入相同。	;
:	输出与输入相同。	:
空格符	格式模型中一个或多个空格的连续序列与输入中的一个或多个连续 Unicode 空格字符匹配。此处空格代表 ASCII 值为 32 的空格字符；而不是制表符或换行符等常规空白字符。格式模型中任何不是 ASCII 32 字符的空白字符都会导致生成错误。
"文本"	格式化期间格式元素使用此正则表达式生成的输出，其中 `s` 表示字符串输入：`regex.escape(s)`。	输入："abc" 输出：abc 输入："a\"b\\c" 输出：a"b\c

将数字类型格式化为字符串

CAST(numeric_expression AS STRING FORMAT format_string_expression)

您可以通过结合使用以下格式元素将数字类型转换为字符串：

数字
小数点
符号
货币符号
组分隔符
其他格式元素

除了指数格式元素 (EEEE) 之外，所有格式元素都会在输出中生成固定数量的字符，并且输出按小数点对齐。如需阻止显示空白字符和尾随零，请使用 FM 标志。

返回类型

STRING

示例

SELECT input, CAST(input AS STRING FORMAT '$999,999.999') AS output
FROM UNNEST([1.2, 12.3, 123.456, 1234.56, -12345.678, 1234567.89]) AS input

+------------+---------------+
|   input    |    output     |
+------------+---------------+
|        1.2 |        $1.200 |
|       12.3 |       $12.300 |
|    123.456 |      $123.456 |
|    1234.56 |    $1,234.560 |
| -12345.678 |  -$12,345.678 |
| 1234567.89 |  $###,###.### |
+------------+---------------+

将数字格式化为字符串

以下格式元素可输出数字。如果这些数字格式元素不足以表示输入，则输出中的所有数字格式元素都会被替换为 #。

格式元素返回值示例

十进制数字。包括前导零和尾随零。

格式元素	返回值	示例
0	十进制数字。包括前导零和尾随零。	输入：`12` 格式：`'000'` 输出：`' 012'` 输入：`12` 格式：`'000.000'` 输出：`' 012.000'` 输入：`-12` 格式：`'000.000'` 输出：`'-012.000'`
9	十进制数字。前导零会被替换为空格；但将包括尾随零。	输入：`12` 格式：`'999'` 输出：`' 12'` 输入：`12` 格式：`'999.999'` 输出：`' 12.000'`
X 或 x	十六进制数字。不能与其他格式元素一起使用，但 0、FM 和符号格式元素除外。格式字符串中的十六进制数字的数量上限为 16。 X 生成大写字母，x 生成小写字母。当 0 与十六进制格式元素结合使用时，由 0 生成的字母与下一个 X 或 x 元素的大小写匹配。如果没有后续的 X 或 x，则 0 会生成大写字母。	输入：`43981` 格式：`'XXXX'` 输出：`' ABCD'` 输入：`43981` 格式：`'xxxx'` 输出：`' abcd'` 输入：`43981` 格式：`'0X0x'` 输出：`' ABcd'` 输入：`43981` 格式：`'0000000X'` 输出：`' 0000ABCD'`

输入：12
格式：'000'
输出：' 012'

输入：12
格式：'000.000'
输出：' 012.000'

输入：-12
格式：'000.000'
输出：'-012.000'

十进制数字。前导零会被替换为空格；但将包括尾随零。

输入：12
格式：'999'
输出：' 12'

输入：12
格式：'999.999'
输出：' 12.000'

X 或 x

十六进制数字。不能与其他格式元素一起使用，但 0、FM 和符号格式元素除外。格式字符串中的十六进制数字的数量上限为 16。

X 生成大写字母，x 生成小写字母。

当 0 与十六进制格式元素结合使用时，由 0 生成的字母与下一个 X 或 x 元素的大小写匹配。如果没有后续的 X 或 x，则 0 会生成大写字母。

输入：43981
格式：'XXXX'
输出：' ABCD'

输入：43981
格式：'xxxx'
输出：' abcd'

输入：43981
格式：'0X0x'
输出：' ABcd'

输入：43981
格式：'0000000X'
输出：' 0000ABCD'

返回类型

STRING

示例

SELECT
  CAST(12 AS STRING FORMAT '999') as a,
  CAST(-12 AS STRING FORMAT '999') as b;

+------+------+
|  a   |  b   |
+------+------+
|   12 |  -12 |
+------+------+

将小数点格式化为字符串

以下格式元素可输出小数点。这些格式元素是互斥的。格式字符串中最多只能出现一项。

格式元素	返回值	示例
.（英文句点）	小数点。	输入：`123.58` 格式：`'999.999'` 输出：`' 123.580'`
D	当前语言区域的小数点。	输入：`123.58` 格式：`'999D999'` 输出：`' 123.580'`

返回类型

STRING

示例

SELECT CAST(12.5 AS STRING FORMAT '99.99') as a;

+--------+
|   a    |
+--------+
|  12.50 |
+--------+

将符号格式化为字符串

以下格式元素可输出符号 (+/-)。这些格式元素是互斥的。格式字符串中最多只能出现一项。

如果没有符号格式元素，则系统会为符号预留一个额外的空格。例如，如果输入为 12 且格式字符串为 '99'，则输出为 ' 12'，长度为三个字符。

符号显示在数字前面。如果格式模型包含货币符号元素，则符号会显示在货币符号前面。

格式元素	返回值	示例
S	显式符号。输出 `+`（正数）和 `-`（负数）。输出中的位置固定在数字处。	输入：`-12` 格式：`'S9999'` 输出：`' -12'` 输入：`-12` 格式：`'9999S'` 输出：`' 12-'`
MI	显式符号。输出空格（正数）和 `-`（负数）。此元素只能出现在最后一个位置。	输入：`12` 格式：`'9999MI'` 输出：`' 12 '` 输入：`-12` 格式：`'9999MI'` 输出：`' 12-'`
PR	对于负数，值将用英文尖括号括起来。对于正数，返回值将包含前导空格和尾随空格。此元素只能出现在最后一个位置。	输入：`12` 格式：`'9999PR'` 输出：`' 12 '` 输入：`-12` 格式：`'9999PR'` 输出：`' <12>'`

返回类型

STRING

示例

SELECT
  CAST(12 AS STRING FORMAT 'S99') as a,
  CAST(-12 AS STRING FORMAT 'S99') as b;

+-----+-----+
|  a  |  b  |
+-----+-----+
| +12 | -12 |
+-----+-----+

将货币符号格式化为字符串

以下格式元素可输出货币符号。这些格式元素是互斥的。格式字符串中最多只能出现一项。在输出中，货币符号出现在第一个数字或小数点前面。

格式元素	返回值	示例
$	美元符号 ($)。	输入：`-12` 格式：`'$999'` 输出：`' -$12'`
C 或 c	当前语言区域的 ISO-4217 货币代码。	输入：`-12` 格式：`'C999'` 输出：`' -USD12'` 输入：`-12` 格式：`'c999'` 输出：`' -usd12'`
L	当前语言区域的货币符号。	输入：`-12` 格式：`'L999'` 输出：`' -$12'`

返回类型

STRING

示例

SELECT
  CAST(12 AS STRING FORMAT '$99') as a,
  CAST(-12 AS STRING FORMAT '$99') as b;

+------+------+
|  a   |  b   |
+------+------+
|  $12 | -$12 |
+------+------+

将组分隔符格式化为字符串

以下格式元素可输出组分隔符。

格式元素	返回值	示例
,（英文逗号）	组分隔符。	输入：`12345` 格式：`'999,999'` 输出：`' 12,345'`
G	当前语言区域的组分隔符位。	输入：`12345` 格式：`'999G999'` 输出：`' 12,345'`

返回类型

STRING

示例

SELECT CAST(1234 AS STRING FORMAT '999,999') as a;

+----------+
|    a     |
+----------+
|    1,234 |
+----------+

其他数字格式元素

格式元素	返回值	示例
B	在整数部分为零时输出空格。如果数字的整数部分为 0，则以下格式元素会在输出中生成空格：数字（9、X、0）、小数点、组分隔符、货币、符号和指数。	输入：`0.23` 格式：`'B99.999S'` 输出：`' '` 输入：`1.23` 格式：`'B99.999S'` 输出：`' 1.230+'`
EEEE	以科学记数法输出值的指数部分。如果指数值介于 -99 到 99 之间，则输出为 4 个字符。否则，输出中将使用最小位数。	输入：`20` 格式：`'9.99EEEE'` 输出：`' 2.0E+01'` 输入：`299792458` 格式：`'S9.999EEEE'` 输出：`'+2.998E+08'`
FM	从输出中移除所有空格和尾随零。您可以使用此元素来阻止显示其他格式元素生成的空格和尾随零。	输入：`12.5` 格式：`'999999.000FM'` 输出：`'12.5'`
RN	以罗马数字的形式返回值，并舍入到最接近的整数。输入必须介于 1 到 3999 之间。输出左侧将填充空格，直至其长度达到 15。此元素不能与其他格式元素一起使用，但 `FM` 除外。	输入：`2021` 格式：`'RN'` 输出：`' MMXXI'`
V	将输入值乘以 10^n，其中 n 是 `V` 之后出现的 9 的个数。此元素不能与小数点或指数格式元素一起使用。	输入：`23.5` 格式：`'S000V00'` 输出：`'+02350'`

返回类型

STRING

示例

SELECT CAST(-123456 AS STRING FORMAT '9.999EEEE') as a;"

+------------+
|     a      |
+------------+
| -1.235E+05 |
+------------+

关于 BASE 编码

BASE 编码将字符串格式的二进制数据转换为基数 X 表示法。

如果 X 为 2、8 或 16，则编码字符串中将使用阿拉伯数字 0-9 以及拉丁字母 a-z。例如，BASE16/十六进制编码结果将包含 0~9 以及 a~f。

如果 X 为 32 或 64，则默认字符表将按 rfc 4648 标准定义。如果 X 为 2、8 或 16，对 BASE 字符串进行解码时，输入字符串中的拉丁字母将不区分大小写。例如，“3a”和“3A”都是可用于 BASE16/十六进制解码的有效输入字符串，并将输出相同的结果。

数学函数

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

如果任一输入参数为 NULL，则返回 NULL。
如果任一参数为 NaN，则返回 NaN。

ABS

ABS(X)

说明

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

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

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	INT64	NUMERIC	BIGNUMERIC	FLOAT64

SIGN

SIGN(X)

说明

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

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

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	INT64	NUMERIC	BIGNUMERIC	FLOAT64

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`	错误

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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	错误

返回数据类型

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

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

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

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

LN

LN(X)

说明

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

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

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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	错误

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

LOG10

LOG10(X)

说明

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

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

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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	错误

返回数据类型

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

INPUT	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

说明

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

X	是	SAFE_DIVIDE(X, Y)
20	4	5
0	20	`0`
20	0	`NULL`

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

说明

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

X	是	SAFE_MULTIPLY(X, Y)
20	4	80

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

说明

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

X	SAFE_NEGATE(X)
+1	-1
-1	+1
0	0

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	INT64	NUMERIC	BIGNUMERIC	FLOAT64

SAFE_ADD

SAFE_ADD(X, Y)

说明

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

X	是	SAFE_ADD(X, Y)
5	4	9

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

说明

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

X	是	SAFE_SUBTRACT(X, Y)
5	4	1

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
INT64	INT64	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64	FLOAT64

MOD

MOD(X, Y)

说明

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

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

返回数据类型

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

INPUT	INT64	NUMERIC	BIGNUMERIC
INT64	INT64	NUMERIC	BIGNUMERIC
NUMERIC	NUMERIC	NUMERIC	BIGNUMERIC
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	BIGNUMERIC

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`

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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`

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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`

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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`

返回数据类型

INPUT	INT64	NUMERIC	BIGNUMERIC	FLOAT64
输出	FLOAT64	NUMERIC	BIGNUMERIC	FLOAT64

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

如果该点为 NULL 或 NaN，则返回 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

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

数组中包含 NaN 或 NULL 值。

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 或负值，则会发生错误。

支持的参数类型

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_expression 和 percentile 必须具有以下类型之一：
- NUMERIC
- BIGNUMERIC
- FLOAT64
percentile 必须是 [0, 1] 范围内的一个字面量。

返回数据类型

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

INPUT	NUMERIC	BIGNUMERIC	FLOAT64
NUMERIC	NUMERIC	BIGNUMERIC	FLOAT64
BIGNUMERIC	BIGNUMERIC	BIGNUMERIC	FLOAT64
FLOAT64	FLOAT64	FLOAT64	FLOAT64

示例

以下示例计算一个值列中的某些百分位值，同时忽略 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 函数计算 STRING 或 BYTES 输入的指纹。此函数针对特定输入的输出从不会改变。

返回类型

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 算法计算输入的哈希值。输入可以是 STRING 或 BYTES。字符串版本会将输入作为字节数组处理。

此函数返回 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 算法计算输入的哈希值。输入可以是 STRING 或 BYTES。字符串版本会将输入作为字节数组处理。

此函数返回 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 算法计算输入的哈希值。输入可以是 STRING 或 BYTES。字符串版本会将输入作为字节数组处理。

此函数返回 32 个字节。

返回类型

BYTES

示例

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

说明

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

此函数返回 64 个字节。

返回类型

BYTES

示例

SELECT SHA512("Hello World") as sha512;

字符串函数

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

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

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

ASCII

ASCII(value)

说明

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