本页面介绍了 BigQuery 表达式，包括函数和运算符。
函数调用规则
除非函数说明中另有明确规定，否则以下规则适用于所有函数：
- 对于接受数字类型的函数，如果一个操作数是浮点型，而另一个操作数是数字型，则系统会先将这两个操作数转换为 FLOAT64，然后再对该函数进行求值。
- 如果操作数是
NULL，则结果为NULL，IS 运算符除外。 - 对于区分时区的函数（如函数说明中所述），如果您未指定时区，则系统会使用默认的“世界协调时间 (UTC)”时区。
SAFE. 前缀
语法：
SAFE.function_name()
说明
如果一个函数以 SAFE. 前缀开头，该函数会返回 NULL 而非错误。SAFE. 前缀仅会防止带有前缀的函数本身返回错误，而不能防止在计算参数表达式时出现的错误。SAFE. 前缀仅会防止因函数输入值所导致的错误，例如“值超出范围”错误，但内部错误或系统错误等其他错误仍可能会发生。如果函数未返回错误，SAFE. 对输出无任何影响。如果函数决不会返回错误，像 RAND 函数就是如此，那么 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
转换规则
“转换”包括但不限于类型转换 (casting) 和强制转换 (coercion)。
- 类型转换属于显式转换，使用
CAST()函数。 - 强制转换属于隐式转换，BigQuery 在下述条件下会自动执行此转换。
- 此外还存在第三种转换函数，它们有自己的函数名称，例如
UNIX_DATE()。
下表汇总了 BigQuery 数据类型所有可能的 CAST 和强制转换。“强制转换目标类型”适用于指定数据类型（例如列）的所有表达式，而且也支持对字面量和参数进行强制转换。如需了解详情，请参阅字面量强制转换和参数强制转换。
| 原类型 | 类型转换目标类型 | 强制转换目标类型 |
|---|---|---|
| INT64 | BOOL INT64 NUMERIC FLOAT64 STRING |
FLOAT64 NUMERIC |
| NUMERIC | INT64 NUMERIC FLOAT64 STRING |
FLOAT64 |
| FLOAT64 | INT64 NUMERIC FLOAT64 STRING |
|
| BOOL | BOOL INT64 STRING |
|
| STRING | BOOL INT64 NUMERIC FLOAT64 STRING BYTES DATE DATETIME TIME TIMESTAMP |
|
| BYTES | BYTES STRING |
|
| DATE | DATE DATETIME STRING TIMESTAMP |
|
| DATETIME | DATE DATETIME STRING TIME TIMESTAMP |
|
| TIME | STRING TIME |
|
| TIMESTAMP | DATE DATETIME STRING TIME TIMESTAMP |
|
| ARRAY | ARRAY | |
| STRUCT | STRUCT |
类型转换
语法：
CAST(expr AS typename)
类型转换语法在查询中使用，用于指示表达式的结果类型应转换成其他某种类型。
示例：
CAST(x=1 AS STRING)
如果 x 为 1，则此表达式的结果为 "true"；如果 x 为任何其他非 NULL 值，则结果为 "false"；如果 x 为 NULL，则结果为 NULL。
如果受支持的类型之间的类型转换未从原始值成功地映射到目标域，则会引发运行时错误。例如，如果字节序列不是有效的 UTF-8 字符，则将 BYTES 类型转换为 STRING 类型会导致运行时错误。
在对以下类型的表达式 x 进行类型转换时，下述规则适用：
| 原类型 | 目标类型 | 对 x 进行类型转换时适用的规则 |
|---|---|---|
| INT64 | FLOAT64 | 返回一个接近但可能不精确的 FLOAT64 值。 |
| INT64 | BOOL | 如果 x 为 0，则返回 FALSE；否则返回 TRUE。 |
| NUMERIC | 浮点 | NUMERIC 类型将转换为最接近的浮点数，可能有一定的精度损失。 |
| FLOAT64 | INT64 | 返回最接近的 INT64 值。 中间数（例如 1.5 或 -0.5）向远离 0 的方向舍入。 |
| FLOAT64 | STRING | 返回一个近似的字符串表示形式。 |
| FLOAT64 | NUMERIC | 如果浮点数的小数点后的位数超过 9 位，则会向远离 0 方向按中间数舍入。如果对 NaN、+inf 或 -inf 进行类型转换，则系统会返回错误。如果对 NUMERIC 范围以外的值进行类型转换，则系统会返回溢出错误。 |
| BOOL | INT64 | 如果 x 为 TRUE，则返回 1；否则返回 0。 |
| BOOL | STRING | 如果 x 为 TRUE，则返回 "true"；否则返回 "false"。 |
| STRING | FLOAT64 | 以 FLOAT64 值的形式返回 x，将其解释为具有与有效的 FLOAT64 字面量相同的形式。也支持从 "inf"、"+inf"、"-inf"、"nan" 进行类型转换。转换不区分大小写。 |
| STRING | NUMERIC | STRING 中包含的数字字面量不得超出 NUMERIC 类型的最大精度或范围，否则将发生错误。如果小数点后的位数超过 9 位，则生成的 NUMERIC 值会向远离 0 方向按中间数舍入，从而在小数点后保留 9 位数。 |
| STRING | BOOL | 如果 x 为 "true"，则返回 TRUE；如果 x 为 "false"，则返回 FALSEx 的所有其他值均无效且抛出错误，而不会转换为 BOOL。转换为 BOOL 类型时，STRING 类型不区分大小写。 |
| STRING | BYTES | STRING 通过 UTF-8 编码转换为 BYTES。例如，在转换为 BYTES 时，字符串“©”会转为一个包含十六进制值 C2 和 A9 的 2 字节序列。 |
| BYTES | STRING | 返回解释为 UTF-8 STRING 类型的 x。例如，在转换为 STRING 类型时，BYTES 类型的字面量 b'\xc2\xa9' 会解释为 UTF-8 字符并且转为 Unicode 字符“©”。如果 x 不是有效的 UTF-8 字符，则引发错误。 |
| ARRAY | ARRAY | 两者必须是完全相同的 ARRAY 类型。 |
| STRUCT | STRUCT | 仅在满足以下条件时可用：
|
安全类型转换
在使用 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。
将十六进制字符串转换为整数
如果您使用十六进制字符串 (0x123)，可以将这些字符串的类型转换为整数：
SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123 | 291 |
+-----------+------------+
SELECT '-0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;
+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| -0x123 | -291 |
+-----------+------------+
转换时间类型
BigQuery 支持在时间类型与字符串之间相互转换，如下所示：
CAST(time_expression AS STRING)
CAST(string_expression AS TIME)
从时间类型转换为字符串与时区无关，并采用 HH:MM:SS 格式。字符串转换为时间时，字符串必须遵循所支持的时间字面量格式，但与时区无关。如果字符串表达式无效或其表示的时间超出了所支持的最小/最大范围，则会出现错误。
转换日期类型
BigQuery 支持在日期类型与字符串之间相互转换，如下所示：
CAST(date_expression AS STRING)
CAST(string_expression AS DATE)
日期类型转换为字符串与时区无关，并采用 YYYY-MM-DD 形式。字符串转换为日期时，字符串必须遵循所支持的日期字面量格式，但与时区无关。如果字符串表达式无效或其表示的日期超出了所支持的最小/最大范围，则会出现错误。
转换日期时间类型
BigQuery 支持在日期时间类型与字符串之间相互转换，如下所示：
CAST(datetime_expression AS STRING)
CAST(string_expression AS DATETIME)
从日期时间类型转换为字符串与时区无关，并采用 YYYY-MM-DD HH:MM:SS 格式。从字符串转换为日期时间时，字符串必须遵循所支持的日期时间字面量格式，但与时区无关。如果字符串表达式无效或其表示的日期时间超出了所支持的最小/最大范围，则会出现错误。
转换时间戳类型
BigQuery 支持在时间戳类型与字符串之间相互转换，如下所示：
CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)
时间戳类型转换为字符串时，使用默认时区“世界协调时间 (UTC)”解释时间戳。所生成的亚秒位数取决于亚秒部分中尾随零的数量：CAST 函数将截取 0、3 或 6 位数。
字符串转换为时间戳时，string_expression 必须遵循所支持的时间戳字面量格式，否则将出现运行时错误。string_expression 可能自带一个 time_zone，具体请参阅时区。如果 string_expression 中存在时区，则转换时使用该时区，否则使用默认时区“世界协调时间 (UTC)”。如果字符串少于 6 位数，则将其隐式加宽。
如果 string_expression 无效，其亚秒位数超过 6 位数（即精度高于微秒），或者所表示的时间超出了支持的时间戳范围，则会引发错误。
在日期、日期时间和时间戳类型之间转换
BigQuery 支持在转化规则表中显示的日期、日期时间和时间戳类型之间转换。
CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)
如果从日期转换为时间戳，则从默认时区“世界协调时间 (UTC)”的午夜（一天之始）起解释 date_expression。如果从时间戳转换为日期，则自默认时区起实际截断时间戳。
CAST(datetime_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATETIME)
如果从日期时间转换为时间戳，则从默认时区“世界协调时间 (UTC)”的午夜（一天之始）起解释 datetime_expression。
强制转换
如果需要匹配函数签名，BigQuery 会将表达式的结果转换成其他类型。例如，如果函数 func() 定义为接受 INT64 类型的单个参数，并使用一个结果类型为 FLOAT64 的表达式作为参数，则该表达式的结果将强制转换为 INT64 类型后，才开始计算 func()。
字面量强制转换
BigQuery 支持以下字面量强制转换：
| 输入数据类型 | 结果数据类型 | 备注 |
|---|---|---|
| STRING 字面量 | DATE DATETIME TIME TIMESTAMP |
在实际字面量类型不同于相关函数预期的类型时，就需要字面量强制转换。例如，如果函数 func() 接受一个 DATE 参数，则表达式 func("2014-09-27") 有效，因为 STRING 字面量 "2014-09-27" 强制转换为 DATE 类型。
字面量转换会在分析时进行评估，如果输入字面量无法成功转换为目标类型，则引发错误。
注意：字符串字面量不会强制转换为数字类型。
参数强制转换
BigQuery 支持以下参数强制转换：
| 输入数据类型 | 结果数据类型 |
|---|---|
| STRING 参数 |
如果参数值无法成功强制转换为目标类型，则引发错误。
其他转换函数
BigQuery 还提供以下转换函数：
聚合函数
聚合函数是将组中的行汇总为单个值的函数。例如，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 元素，则会产生错误）。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的每个不同值仅聚合到结果中一次。
返回的数据类型
- 如果输入类型是 NUMERIC，则返回 NUMERIC。
- 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
1.
COUNT(*) [OVER (...)]
2.
COUNT([DISTINCT] expression) [OVER (...)]
说明
- 返回输入中的行数。
- 返回
expression的计算结果为NULL以外任何值的行数。
支持的参数类型
expression 可以是任何数据类型。
可选子句
子句按照以下顺序应用：
OVER：指定一个窗口。请参阅分析函数。DISTINCT：expression的每个不同值仅聚合到结果中一次。
返回数据类型
INT64
示例
SELECT
COUNT(*) AS count_star,
COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4 | 3 |
+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(DISTINCT x) OVER (PARTITION BY MOD(x, 3)) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;
+------+------------+--------------+
| x | count_star | count_dist_x |
+------+------------+--------------+
| 1 | 3 | 2 |
| 4 | 3 | 2 |
| 4 | 3 | 2 |
| 5 | 1 | 1 |
+------+------------+--------------+
SELECT
x,
COUNT(*) OVER (PARTITION BY MOD(x, 3)) AS count_star,
COUNT(x) OVER (PARTITION BY MOD(x, 3)) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;
+------+------------+---------+
| x | count_star | count_x |
+------+------------+---------+
| NULL | 1 | 0 |
| 1 | 3 | 3 |
| 4 | 3 | 3 |
| 4 | 3 | 3 |
| 5 | 1 | 1 |
+------+------------+---------+
COUNTIF
COUNTIF(expression) [OVER (...)]
说明
返回 expression 的 TRUE 值计数。如果不存在任何输入行，或所有行的 expression 计算结果均为 FALSE 或 NULL，则返回 0。
支持的参数类型
BOOL
可选子句
OVER：指定一个窗口。请参阅分析函数。
返回数据类型
INT64
示例
SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;
+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3 | 4 |
+--------------+--------------+
SELECT
x,
COUNTIF(x<0) OVER (ORDER BY ABS(x) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS num_negative
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;
+------+--------------+
| x | num_negative |
+------+--------------+
| NULL | 0 |
| 0 | 1 |
| -2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| -7 | 2 |
| -10 | 2 |
+------+--------------+
LOGICAL_AND
LOGICAL_AND(expression)
说明
返回对所有非 NULL 表达式执行逻辑与运算的结果。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL，则返回 NULL。
支持的参数类型
BOOL
返回数据类型
BOOL
示例
SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;
+-------------+
| logical_and |
+-------------+
| false |
+-------------+
LOGICAL_OR
LOGICAL_OR(expression)
说明
返回对所有非 NULL 表达式执行逻辑或运算的结果。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL，则返回 NULL。
支持的参数类型
BOOL
返回数据类型
BOOL
示例
SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;
+------------+
| logical_or |
+------------+
| true |
+------------+
MAX
MAX(expression) [OVER (...)]
说明
返回非 NULL 表达式的最大值。如果不存在任何输入行或 expression 针对所有行的求值结果均为 NULL，则返回 NULL。如果输入包含 NaN，则返回 NaN。
支持的参数类型
任意数据类型（ARRAY STRUCT GEOGRAPHY 除外）
可选子句
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。
支持的参数类型
任意数据类型（ARRAY STRUCT GEOGRAPHY 除外）
可选子句
OVER：指定一个窗口。请参阅分析函数。
返回数据类型
与用作输入值的数据类型相同。
示例
SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;
+-----+
| min |
+-----+
| 4 |
+-----+
SELECT x, MIN(x) OVER (PARTITION BY MOD(x, 2)) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;
+------+------+
| x | min |
+------+------+
| NULL | NULL |
| NULL | NULL |
| 8 | 4 |
| 4 | 4 |
| 37 | 37 |
| 55 | 37 |
+------+------+
STRING_AGG
STRING_AGG([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n])
[OVER (...)]
说明
返回通过连接非 NULL 值获得的值（STRING 或 BYTES）。
如果指定了 delimiter，则连接的值由此分隔符进行分隔；否则使用英文逗号作为分隔符。
支持的参数类型
STRING BYTES
可选子句
子句按照以下顺序应用：
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 值之和。
如果表达式是浮点值，则总和是不确定的，这意味着您在每次使用此函数时可能得到不同的结果。
支持的参数类型
任何支持的数字数据类型。
可选子句
子句按照以下顺序应用：
OVER：指定一个窗口。请参阅分析函数。DISTINCT：expression的每个不同值仅聚合到结果中一次。
返回数据类型
- 如果输入是整数，则返回 INT64。
- 如果输入类型是 NUMERIC，则返回 NUMERIC。
- 如果输入是浮点值，则返回 FLOAT64。
如果输入仅包含 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，则表示不相关。
此函数会忽略包含一个或多个 NULL 值的任何输入对。如果非 NULL 值的输入对少于两个，此函数会返回 NULL。
支持的输入类型
FLOAT64
可选子句
OVER：指定一个窗口。请参阅分析函数。
返回数据类型
FLOAT64
COVAR_POP
COVAR_POP(X1, X2) [OVER (...)]
说明
返回一组数字对的总体协方差。第一个数字是因变量；第二个数字是自变量。返回结果介于 -Inf 和 +Inf 之间。
此函数会忽略包含一个或多个 NULL 值的任何输入对。如果不存在没有 NULL 值的输入对，此函数会返回 NULL。如果没有 NULL 值的输入对仅有一个，则此函数返回 0。
支持的输入类型
FLOAT64
可选子句
OVER：指定一个窗口。请参阅分析函数。
返回数据类型
FLOAT64
COVAR_SAMP
COVAR_SAMP(X1, X2) [OVER (...)]
说明
返回一组数字对的样本协方差。第一个数字是因变量；第二个数字是自变量。返回结果介于 -Inf 和 +Inf 之间。
此函数会忽略包含一个或多个 NULL 值的任何输入对。如果非 NULL 值的输入对少于两个，此函数会返回 NULL。
支持的输入类型
FLOAT64
可选子句
OVER：指定一个窗口。请参阅分析函数。
返回数据类型
FLOAT64
STDDEV_POP
STDDEV_POP([DISTINCT] expression) [OVER (...)]
说明
返回值的总体（偏差）标准差。返回结果介于 0 和 +Inf 之间。
此函数会忽略任何 NULL 输入。如果所有输入均被忽略，此函数会返回 NULL。
如果此函数收到单个非 NULL 输入，它会返回 0。
支持的输入类型
FLOAT64
可选子句
子句按照以下顺序应用：
OVER：指定一个窗口。请参阅分析函数。 此子句目前与STDDEV_POP()内的其他所有子句均不兼容。DISTINCT：expression的每个不同值仅聚合到结果中一次。
返回数据类型
FLOAT64
STDDEV_SAMP
STDDEV_SAMP([DISTINCT] expression) [OVER (...)]
说明
返回值的样本（无偏差）标准差。返回结果介于 0 和 +Inf 之间。
此函数会忽略任何 NULL 输入。如果非 NULL 输入少于两个，则此函数返回 NULL。
支持的输入类型
FLOAT64
可选子句
子句按照以下顺序应用：
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。
支持的输入类型
FLOAT64
可选子句
子句按照以下顺序应用：
OVER：指定一个窗口。请参阅分析函数。 此子句目前与VAR_POP()内的其他所有子句均不兼容。DISTINCT：expression的每个不同值仅聚合到结果中一次。
返回数据类型
FLOAT64
VAR_SAMP
VAR_SAMP([DISTINCT] expression) [OVER (...)]
说明
返回值的样本（无偏差）方差。返回结果介于 0 和 +Inf 之间。
此函数会忽略任何 NULL 输入。如果非 NULL 输入少于两个，则此函数返回 NULL。
支持的输入类型
FLOAT64
可选子句
子句按照以下顺序应用：
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 元素，则会报错）。
返回的数据类型
由 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
- 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 | ±1.63% | ±3.25% | ±6.50% |
| 11 | 2 | ±1.15% | ±2.30% | ±4.60% |
| 12 | 4 | ±0.81% | ±1.63% | ±3.25% |
| 13 | 8 | ±0.57% | ±1.15% | ±1.72% |
| 14 | 16 | ±0.41% | ±0.81% | ±1.22% |
| 15（默认） | 32 | ±0.29% | ±0.57% | ±0.86% |
| 16 | 64 | ±0.20% | ±0.41% | ±0.61% |
| 17 | 128 | ±0.14% | ±0.29% | ±0.43% |
| 18 | 256 | ±0.10% | ±0.20% | ±0.41% |
| 19 | 512 | ±0.07% | ±0.14% | ±0.29% |
| 20 | 1024 | ±0.05% | ±0.10% | ±0.20% |
| 21 | 2048 | ±0.04% | ±0.07% | ±0.14% |
| 22 | 4096 | ±0.03% | ±0.05% | ±0.10% |
| 23 | 8192 | ±0.02% | ±0.04% | ±0.07% |
| 24 | 16384 | ±0.01% | ±0.03% | ±0.05% |
如果输入是 NULL，则此函数会返回 NULL。
如需了解详情，请参阅 HyperLogLog 实践：一种先进基数估计算法的算法工程。
支持的输入类型
INT64、NUMERIC、STRING、BYTES
返回类型
BYTES
示例
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country;
HLL_COUNT.MERGE
HLL_COUNT.MERGE(sketch)
说明
这个聚合函数会计算多组 HLL++ 草图的并集，并返回其基数。
每个 sketch 都必须具有相同的精度，并针对相同类型进行初始化。如果尝试合并具有不同精度的草图，或针对不同类型合并草图，则会引发错误。例如，您不能将使用 INT64 数据初始化的草图与使用 STRING 数据初始化的草图合并。
在合并草图时，此函数会忽略 NULL 值。如果合并了零行，或者只有 NULL 值发生合并，此函数会返回 0。
支持的输入类型
BYTES
返回类型
INT64
示例
SELECT HLL_COUNT.MERGE(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.MERGE_PARTIAL
HLL_COUNT.MERGE_PARTIAL(sketch)
说明
这个聚合函数会接受一个或多个 HLL++ sketch 输入，并将这些输入合并到一个新草图。
如果无输入或所有输入均为 NULL，此函数返回 NULL。
支持的输入类型
BYTES
返回类型
BYTES
示例
SELECT HLL_COUNT.MERGE_PARTIAL(respondents_hll) AS num_respondents, flavor
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country)
GROUP BY flavor;
HLL_COUNT.EXTRACT
HLL_COUNT.EXTRACT(sketch)
说明
这个标量函数会提取一个 HLL++ 草图的基数估计值。
如果 sketch 是 NULL，则此函数返回的基数估计值为 0。
支持的输入类型
BYTES
返回类型
INT64
示例
SELECT
flavor,
country,
HLL_COUNT.EXTRACT(respondents_hll) AS num_respondents
FROM (
SELECT
HLL_COUNT.INIT(respondent) AS respondents_hll,
flavor,
country
FROM UNNEST([
STRUCT(1 AS respondent, "Vanilla" AS flavor, "CH" AS country),
(1, "Chocolate", "CH"),
(2, "Chocolate", "US"),
(2, "Strawberry", "US")])
GROUP BY flavor, country);
+------------+---------+-----------------+
| flavor | country | num_respondents |
+------------+---------+-----------------+
| Vanilla | CH | 1 |
| Chocolate | CH | 1 |
| Chocolate | US | 1 |
| Strawberry | US | 1 |
+------------+---------+-----------------+
关于 HLL++ 算法
HLL++ 算法对 HLL 算法进行了改进，可以更准确地估计极小和极大的基数。HLL++ 算法包括一个 64 位哈希函数、用于减少较小基数估计值内存要求的稀疏表示法，以及用于较小基数估计值的经验偏差校正。
关于草图
草图是大型数据流的汇总。您可以从草图中提取统计信息以估算原始数据的特定统计数据，或合并草图来汇总多个数据流。草图具有以下特点：
- 它将原始数据压缩为固定内存表示法。
- 它渐近地小于输入。
- 它是内存中的次线性数据结构的序列化形式。
- 它要求的内存通常少于用于创建它的输入。
草图可以与其他系统集成。例如，您可以在外部应用（如 Cloud 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 |
+-------+--------+---------------------------------------------+--------+
数学函数
所有数学函数都具有以下行为：
- 如果任一输入参数为
NULL，则返回NULL。 - 如果任一参数为
NaN，则返回NaN。
ABS
ABS(X)
说明
计算绝对值。如果参数是整数，而输出值无法用同一类型表示，则返回一个错误；仅最大负输入值（不具有正数表示形式）会发生此情况。对 +/-inf 参数返回 +inf。
SIGN
SIGN(X)
说明
分别对负参数、零和正参数返回 -1、0 或 +1。对于浮点参数，此函数不区分正零和负零。对 NaN 参数返回 NaN。
IS_INF
IS_INF(X)
说明
如果值为正无穷或负无穷，则返回 TRUE。对 NULL 输入返回 NULL。
IS_NAN
IS_NAN(X)
说明
如果值为 NaN 值，则返回 TRUE。对 NULL 输入返回 NULL。
IEEE_DIVIDE
IEEE_DIVIDE(X, Y)
说明
用 X 除以 Y；此函数始终有效。返回 FLOAT64。不同于除法运算符 (/)，如果除数为零或溢出，此函数不会生成错误。
特殊情况：
- 如果结果溢出，则返回
+/-inf。 - 如果 Y= 0 且 X = 0，则返回
NaN。 - 如果 Y = 0 且 X != 0，则返回
+/-inf。 - 如果 X =
+/-inf且 Y =+/-inf，则返回NaN。
下表进一步说明了 IEEE_DIVIDE 的行为。
IEEE_DIVIDE 的特殊情况
下表列出了 IEEE_DIVIDE 的特殊情况。
| 分子数据类型 (X) | 分母数据类型 (Y) | 结果值 |
|---|---|---|
| 除 0 外的任何值 | 0 | +/-inf |
| 0 | 0 | NaN |
| 0 | NaN |
NaN |
NaN |
0 | NaN |
+/-inf |
+/-inf |
NaN |
RAND
RAND()
说明
在范围 [0, 1)（包含 0，不包含 1）内生成 FLOAT64 类型的伪随机值。
SQRT
SQRT(X)
说明
计算 X 的平方根。如果 X 小于 0，则生成错误。如果 X 为 +inf，则返回 +inf。
POW
POW(X, Y)
说明
返回 X 的 Y 次幂的值。如果结果下溢且不可表示，则函数返回值 0。如果满足下述任一条件，则返回一个错误：
- X 是小于 0 的有限值且 Y 是非整数
- X 是 0 且 Y 是小于 0 的有限值
下表进一步说明了 POW() 的行为。
POWER
POWER(X, Y)
说明
相当于 POW()。
POW(X, Y) 和 POWER(X, Y) 的特殊情况
下面是 POW(X, Y) 和 POWER(X, Y) 的特殊情况。
| X | Y | POW(X, Y) 或 POWER(X, Y) |
|---|---|---|
| 1.0 | 任何值（包括 NaN） |
1.0 |
任何值（包括 NaN） |
0 | 1.0 |
| -1.0 | +/-inf |
1.0 |
| ABS(X) < 1 | -inf |
+inf |
| ABS(X) > 1 | -inf |
0 |
| ABS(X) < 1 | +inf |
0 |
| ABS(X) > 1 | +inf |
+inf |
-inf |
Y < 0 | 0 |
-inf |
Y > 0 | 如果 Y 为奇整数，则为 -inf；否则为 +inf |
+inf |
Y < 0 | 0 |
+inf |
Y > 0 | +inf |
EXP
EXP(X)
说明
计算 e 的 X 次幂，也被称为自然指数函数。如果结果下溢，此函数返回 0。如果结果上溢，则生成错误。如果 X 是 +/-inf，则此函数返回 +inf 或 0。
LN
LN(X)
说明
计算 X 的自然对数。如果 X 小于或等于 0，则生成错误。如果 X 是 +inf，则此函数返回 +inf。
LOG
LOG(X [, Y])
说明
如果只存在 X，则 LOG 相当于 LN。如果还存在 Y，则 LOG 计算以 Y 为底数的 X 的对数。在以下情况下将生成错误：
- X 小于或等于 0
- Y 等于 1.0
- Y 小于或等于 0。
下表进一步说明了 LOG(X, Y) 的行为。
LOG(X, Y) 的特殊情况
| X | Y | LOG(X, Y) |
|---|---|---|
-inf |
任意值 | NaN |
| 任意值 | +inf |
NaN |
+inf |
0.0 Y < 1.0 | -inf |
+inf |
Y > 1.0 | +inf |
LOG10
LOG10(X)
说明
与 LOG 类似，但计算的是以 10 为底数的对数。
GREATEST
GREATEST(X1,...,XN)
说明
如果任一输入为 NULL，则返回 NULL。如果任一输入为 NaN，则返回 NaN。否则根据比较得出更大值，返回 X1,...,XN 中的最大值。
LEAST
LEAST(X1,...,XN)
说明
如果任一输入为 NULL，则返回 NULL。如果任一输入为 NaN，则返回 NaN。否则根据比较得出更小值，返回 X1,...,XN 中的最小值。
DIV
DIV(X, Y)
说明
返回 X 被 Y 整除的结果。如果除数为 0，则返回一个错误。除数为 -1 时可能溢出。请查看下表，了解可能的结果类型。
SAFE_DIVIDE
SAFE_DIVIDE(X, Y)
说明
等同于除法运算符 (/)，但如果发生错误（例如除以 0 这个错误），则返回 NULL。
SAFE_MULTIPLY
SAFE_MULTIPLY(X, Y)
说明
等同于乘法运算符 (*)，但如果发生溢出，则返回 NULL。
SAFE_NEGATE
SAFE_NEGATE(X)
说明
等同于一元取反运算符 (-)，但如果发生溢出，则返回 NULL。
SAFE_ADD
SAFE_ADD(X, Y)
说明
等同于加法运算符 (+)，但如果发生溢出，则返回 NULL。
SAFE_SUBTRACT
SAFE_SUBTRACT(X, Y)
说明
等同于减法运算符 (-)，但如果发生溢出，则返回 NULL。
MOD
MOD(X, Y)
说明
取模函数：返回 X 除以 Y 的余数。返回值的符号与 X 相同。如果 Y 为 0，则生成一个错误。请查看下表，了解可能的结果类型。
ROUND
ROUND(X [, N])
说明
如果只存在 X，则 ROUND 将 X 向最近的整数舍入。如果存在 N，ROUND 将 X 舍入到小数点后 N 个小数位。如果 N 为负数，则 ROUND 将舍掉小数点取整。中间数向远离 0 的方向舍入。如果发生溢出，则生成错误。
TRUNC
TRUNC(X [, N])
说明
如果只存在 X，则 TRUNC 将 X 向最近的整数舍入，且此整数的绝对值不超过 X 的绝对值。如果还存在 N，则 TRUNC 的行为类似于 ROUND(X, N)，但始终向 0 舍入且永不溢出。
CEIL
CEIL(X)
说明
返回不小于 X 的最小整数值（FLOAT64 类型）。
CEILING
CEILING(X)
说明
相当于 CEIL(X)
FLOOR
FLOOR(X)
说明
返回不大于 X 的最大整数值（FLOAT64 类型）。
舍入函数行为示例
BigQuery 舍入函数的行为示例：
| 输入“X” | ROUND(X) | TRUNC(X) | CEIL(X) | FLOOR(X) |
|---|---|---|---|---|
| 2.0 | 2.0 | 2.0 | 2.0 | 2.0 |
| 2.3 | 2.0 | 2.0 | 3.0 | 2.0 |
| 2.8 | 3.0 | 2.0 | 3.0 | 2.0 |
| 2.5 | 3.0 | 2.0 | 3.0 | 2.0 |
| -2.3 | -2.0 | -2.0 | -2.0 | -3.0 |
| -2.8 | -3.0 | -2.0 | -2.0 | -3.0 |
| -2.5 | -3.0 | -2.0 | -2.0 | -3.0 |
| 0 | 0 | 0 | 0 | 0 |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
+/-inf |
NaN |
NaN |
NaN |
NaN |
NaN |
COS
COS(X)
说明
计算 X 的余弦，其中 X 以弧度指定。始终有效。
COSH
COSH(X)
说明
计算 X 的双曲余弦，其中 X 以弧度指定。如果发生溢出，则生成错误。
ACOS
ACOS(X)
说明
计算 X 的反余弦的主值。返回值在 [0,π] 范围内。如果 X 是超出 [-1, 1] 范围的值，则生成错误。
ACOSH
ACOSH(X)
说明
计算 X 的反双曲余弦。如果 X 是小于 1 的值，则生成错误。
SIN
SIN(X)
说明
计算 X 的正弦，其中 X 以弧度指定。始终有效。
SINH
SINH(X)
说明
计算 X 的双曲正弦，其中 X 以弧度指定。如果发生溢出，则生成错误。
ASIN
ASIN(X)
说明
计算 X 的反正弦的主值。返回值在 [-π/2,π/2] 范围内。如果 X 是超出 [-1, 1] 范围的值，则生成错误。
ASINH
ASINH(X)
说明
计算 X 的反双曲正弦。不会失效。
TAN
TAN(X)
说明
计算 X 的正切，其中 X 以弧度指定。如果发生溢出，则生成错误。
TANH
TANH(X)
说明
计算 X 的双曲正切，其中 X 以弧度指定。不会失效。
ATAN
ATAN(X)
说明
计算 X 的反正切的主值。返回值在 [-π/2,π/2] 范围内。不会失效。
ATANH
ATANH(X)
说明
计算 X 的反双曲正切。如果 X 是超出 [-1, 1] 范围的值，则生成错误。
ATAN2
ATAN2(Y, X)
说明
通过使用两个参数的符号确定象限，计算 Y/X 的反正切的主值。返回值在 [-π,π] 范围内。下表进一步说明了此函数的行为。
ATAN2() 的特殊情况
| Y | X | ATAN2(Y, X) |
|---|---|---|
NaN |
任意值 | NaN |
| 任意值 | NaN |
NaN |
| 0 | 0 | 0、π 或 -π（取决于 X 和 Y 的符号） |
| 有限值 | -inf |
π 或 -π（取决于 Y 的符号） |
| 有限值 | +inf |
0 |
+/-inf |
有限值 | π/2 或 π/2（取决于 Y 的符号） |
+/-inf |
-inf |
¾π 或 -¾π（取决于 Y 的符号） |
+/-inf |
+inf |
π/4 或 -π/4（取决于 Y 的符号） |
三角函数和双曲线舍入函数的特殊情况
| X | COS(X) | COSH(X) | ACOS(X) | ACOSH(X) | SIN(X) | SINH(X) | ASIN(X) | ASINH(X) | TAN(X) | TANH(X) | ATAN(X) | ATANH(X) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
+/-inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+inf |
NaN |
=+1.0 | π/2 | NaN |
-inf |
NaN |
=+inf |
NaN |
NaN |
NaN |
-inf |
NaN |
-inf |
NaN |
-1.0 | -π/2 | NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
NaN |
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 可以是表达式支持的任意返回数据类型。
返回数据类型
任意
示例
以下示例计算各部门的最快时间。
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 可以是表达式支持的任意返回数据类型。
返回数据类型
任意
示例
以下示例计算各部门的最慢时间。
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可以是返回整数的任意常量表达式。
返回数据类型
任意
示例
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必须兼容值表达式类型。
返回数据类型
任意
示例
以下示例展示了 LEAD 函数的基本用法。
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS followed_by
FROM finishers;
+-----------------+-------------+----------+-----------------+
| name | finish_time | division | followed_by |
+-----------------+-------------+----------+-----------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Nikki Leith |
| Nikki Leith | 02:59:01 | F30-34 | Jen Edwards |
| Jen Edwards | 03:06:36 | F30-34 | Meghan Lederer |
| Meghan Lederer | 03:07:41 | F30-34 | Lauren Reasoner |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Lauren Matthews |
| Lauren Matthews | 03:01:17 | F35-39 | Desiree Berry |
| Desiree Berry | 03:05:42 | F35-39 | Suzy Slane |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+-----------------+
下一个示例使用可选的 offset 参数。
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2)
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | NULL |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | NULL |
| Lauren Reasoner | 03:10:14 | F30-34 | NULL |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | NULL |
| Suzy Slane | 03:06:24 | F35-39 | NULL |
+-----------------+-------------+----------+------------------+
以下示例使用默认值替换 NULL 值。
WITH finishers AS
(SELECT 'Sophia Liu' as name,
TIMESTAMP '2016-10-18 2:51:45' as finish_time,
'F30-34' as division
UNION ALL SELECT 'Lisa Stelzner', TIMESTAMP '2016-10-18 2:54:11', 'F35-39'
UNION ALL SELECT 'Nikki Leith', TIMESTAMP '2016-10-18 2:59:01', 'F30-34'
UNION ALL SELECT 'Lauren Matthews', TIMESTAMP '2016-10-18 3:01:17', 'F35-39'
UNION ALL SELECT 'Desiree Berry', TIMESTAMP '2016-10-18 3:05:42', 'F35-39'
UNION ALL SELECT 'Suzy Slane', TIMESTAMP '2016-10-18 3:06:24', 'F35-39'
UNION ALL SELECT 'Jen Edwards', TIMESTAMP '2016-10-18 3:06:36', 'F30-34'
UNION ALL SELECT 'Meghan Lederer', TIMESTAMP '2016-10-18 3:07:41', 'F30-34'
UNION ALL SELECT 'Carly Forte', TIMESTAMP '2016-10-18 3:08:58', 'F25-29'
UNION ALL SELECT 'Lauren Reasoner', TIMESTAMP '2016-10-18 3:10:14', 'F30-34')
SELECT name,
finish_time,
division,
LEAD(name, 2, 'Nobody')
OVER (PARTITION BY division ORDER BY finish_time ASC) AS two_runners_back
FROM finishers;
+-----------------+-------------+----------+------------------+
| name | finish_time | division | two_runners_back |
+-----------------+-------------+----------+------------------+
| Carly Forte | 03:08:58 | F25-29 | Nobody |
| Sophia Liu | 02:51:45 | F30-34 | Jen Edwards |
| Nikki Leith | 02:59:01 | F30-34 | Meghan Lederer |
| Jen Edwards | 03:06:36 | F30-34 | Lauren Reasoner |
| Meghan Lederer | 03:07:41 | F30-34 | Nobody |
| Lauren Reasoner | 03:10:14 | F30-34 | Nobody |
| Lisa Stelzner | 02:54:11 | F35-39 | Desiree Berry |
| Lauren Matthews | 03:01:17 | F35-39 | Suzy Slane |
| Desiree Berry | 03:05:42 | F35-39 | Nobody |
| Suzy Slane | 03:06:24 | F35-39 | Nobody |
+-----------------+-------------+----------+------------------+
LAG
LAG (value_expression[, offset [, default_expression]])
说明
返回上一行中的 value_expression 值。更改 offset 值会改变所返回的上一行；默认值是 1，表示窗口框架中的上一行。如果 offset 是 NULL 或负值，则会发生错误。
如果窗口框架中没有指定偏移量的行，则使用可选的 default_expression。此表达式必须是常量表达式，其类型必须可隐式强制转换为 value_expression 类型。如果未指定，则 default_expression 默认为 NULL。
支持的参数类型
value_expression可以是表达式支持的任意返回数据类型。offset必须是非负整数字面量或参数。default_expression必须兼容值表达式类型。
返回数据类型
任意
示例
以下示例展示了 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是[0, 1]范围内的一个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]范围内的一个FLOAT64字面量。
返回数据类型
ANY
示例
以下示例计算一个值列中的某些百分位值，同时忽略 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 支持将以下聚合函数作为分析函数：
- ANY_VALUE
- ARRAY_AGG
- AVG
- CORR
- COUNT
- COUNTIF
- COVAR_POP
- COVAR_SAMP
- MAX
- MIN
- ST_CLUSTERDBSCAN
- STDDEV_POP
- STDDEV_SAMP
- STRING_AGG
- SUM
- VAR_POP
- VAR_SAMP
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 规范相等性。
BYTE_LENGTH
BYTE_LENGTH(value)
说明
无论值的类型是 STRING 还是 BYTES，该函数都会返回 STRING 或 BYTES 值的长度（以 BYTES 为单位）。
返回类型
INT64
示例
WITH example AS
(SELECT "абвгд" AS characters, b"абвгд" AS bytes)
SELECT
characters,
BYTE_LENGTH(characters) AS string_example,
bytes,
BYTE_LENGTH(bytes) AS bytes_example
FROM example;
+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд | 10 | абвгд | 10 |
+------------+----------------+-------+---------------+
CHAR_LENGTH
CHAR_LENGTH(value)
说明
返回 STRING 的长度（以字符为单位）。
返回类型
INT64
示例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHAR_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CHARACTER_LENGTH
CHARACTER_LENGTH(value)
说明
相当于 CHAR_LENGTH。
返回类型
INT64
示例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
CHARACTER_LENGTH(characters) AS char_length_example
FROM example;
+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд | 5 |
+------------+---------------------+
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_BYTES(ascii_values)
说明
接受扩展 ASCII 码位的数组（由 INT64 类型值组成的 ARRAY），并返回 BYTES。
如需从 BYTES 转换为码位数组，请参阅 TO_CODE_POINTS。
返回类型
BYTES
示例
以下是使用 CODE_POINTS_TO_BYTES 的一个基本示例。
SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;
+-------+
| bytes |
+-------+
| AbCd |
+-------+
以下示例使用回转 13 位 (ROT13) 算法来编码字符串。
SELECT CODE_POINTS_TO_BYTES(ARRAY_AGG(
(SELECT
CASE
WHEN chr BETWEEN b'a' and b'z'
THEN TO_CODE_POINTS(b'a')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'a')[offset(0)],26)
WHEN chr BETWEEN b'A' and b'Z'
THEN TO_CODE_POINTS(b'A')[offset(0)] +
MOD(code+13-TO_CODE_POINTS(b'A')[offset(0)],26)
ELSE code
END
FROM
(SELECT code, CODE_POINTS_TO_BYTES([code]) chr)
) ORDER BY OFFSET)) AS encoded_string
FROM UNNEST(TO_CODE_POINTS(b'Test String!')) code WITH OFFSET;
+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat! |
+----------------+
CODE_POINTS_TO_STRING
CODE_POINTS_TO_STRING(value)
说明
接受 Unicode 码位的数组（由 INT64 类型值组成的 ARRAY），并返回一个 STRING。
如需从字符串转换为码位数组，请参阅 TO_CODE_POINTS。
返回类型
STRING
示例
下面是使用 CODE_POINTS_TO_STRING 的一个基本示例。
SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;
+--------+
| string |
+--------+
| AÿȁЀ |
+--------+
以下示例会计算一组单词中字母的出现频率。
WITH Words AS (
SELECT word
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
CODE_POINTS_TO_STRING([code_point]) AS letter,
COUNT(*) AS letter_count
FROM Words,
UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;
+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a | 5 |
| f | 3 |
| r | 2 |
| b | 2 |
| l | 2 |
| o | 2 |
| g | 1 |
| z | 1 |
| e | 1 |
| m | 1 |
| i | 1 |
+--------+--------------+
CONCAT
CONCAT(value1[, ...])
说明
将一个或多个串联成一个结果。所有值都必须为 BYTES 或可转换为 STRING 的数据类型。
如果任何输入参数为 NULL，则该函数返回 NULL。
返回类型
STRING 或 BYTES
示例
SELECT CONCAT("T.P.", " ", "Bar") as author;
+---------------------+
| author |
+---------------------+
| T.P. Bar |
+---------------------+
SELECT CONCAT("Summer", " ", 1923) as release_date;
+---------------------+
| release_date |
+---------------------+
| Summer 1923 |
+---------------------+
With Employees AS
(SELECT
"John" AS first_name,
"Doe" AS last_name
UNION ALL
SELECT
"Jane" AS first_name,
"Smith" AS last_name
UNION ALL
SELECT
"Joe" AS first_name,
"Jackson" AS last_name)
SELECT
CONCAT(first_name, " ", last_name)
AS full_name
FROM Employees;
+---------------------+
| full_name |
+---------------------+
| John Doe |
| Jane Smith |
| Joe Jackson |
+---------------------+
ENDS_WITH
ENDS_WITH(value1, value2)
说明
获取两个 STRING 或 BYTES 值。如果第二个值是第一个值的后缀，则返回 TRUE。
返回类型
BOOL
示例
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
ENDS_WITH(item, "e") as example
FROM items;
+---------+
| example |
+---------+
| True |
| False |
| True |
+---------+
FORMAT
BigQuery 支持用于设置字符串格式的 FORMAT() 函数。此函数类似于 C 语言中的 printf 函数。它会根据一个包含零个或多个格式说明符的格式字符串，以及一个包含与这些格式说明符匹配的额外参数的可变长度列表，生成一个 STRING。示例如下：
| 说明 | 语句 | 结果 |
|---|---|---|
| 简单整数 | FORMAT("%d", 10) | 10 |
| 在左侧填补空格的整数 | FORMAT("|%10d|", 11) | | 11| |
| 在左侧填补零的整数 | FORMAT("+%010d+", 12) | +0000000012+ |
| 用英文逗号分隔的整数 | FORMAT("%'d", 123456789) | 123,456,789 |
| STRING | FORMAT("-%s-", 'abcd efg') | -abcd efg- |
| FLOAT64 | FORMAT("%f %E", 1.1, 2.2) | 1.100000 2.200000E+00 |
| DATE | FORMAT("%t", date "2015-09-01") | 2015-09-01 |
| TIMESTAMP | FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") | 2015‑09‑01 19:34:56+00 |
FORMAT() 函数不会为所有类型和值提供完全可自定义的格式，也不支持设置对语言区域敏感的格式。
如果某种类型需要使用自定义格式设置，则您必须首先使用类型特定的格式函数（例如 FORMAT_DATE() 或 FORMAT_TIMESTAMP()）对其进行格式设置。例如：
SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));
返回
date: January 02, 2015!
语法
FORMAT() 语法会接受一个格式字符串和一个可变长度的参数列表，并生成一个 STRING 结果：
FORMAT(format_string, ...)
format_string 表达式可以包含零个或多个格式说明符。每个格式说明符均由 % 符号引入，且必须映射到一个或多个其余参数。除非存在 * 说明符，否则在大多数情况下，这都是一对一映射。例如，%.*i 映射到两个参数：一个长度参数和一个带符号整数参数。如果与格式说明符相关的参数数量与实际参数数量不同，则会出现错误。
支持的格式说明符
FORMAT() 函数格式说明符遵循以下原型：
%[flags][width][.precision]specifier
下表中标识了支持的格式说明符。与 printf() 函数的不同之处会用斜体表示。
| 说明符 | 说明 | 示例 | 类型 |
d 或 i |
十进制整数 | 392 |
INT64 |
o |
八进制 | 610 | INT64* |
x |
十六进制整数 | 7fa | INT64* |
X |
十六进制整数（大写） | 7FA |
INT64* |
f |
十进制记数法，[-]（整数部分）.（小数部分）表示有限值，小写字母表示非有限值 | 392.650000 inf nan |
NUMERIC FLOAT64 |
F |
十进制记数法，[-]（整数部分）.（小数部分）表示有限值，大写字母表示非有限值 | 392.650000 INF NAN |
NUMERIC FLOAT64 |
e |
科学记数法（尾数/指数）（小写） | 3.926500e+02 inf nan |
NUMERIC FLOAT64 |
E |
科学记数法（尾数/指数）（大写） | 3.926500E+02 INF NAN |
NUMERIC FLOAT64 |
g |
十进制记数法或科学记数法，具体取决于输入值的指数和指定的精度。小写。 如需了解详情，请参阅 %g 和 %G 行为。 | 392.65 3.9265e+07 inf nan |
FLOAT64 |
G |
十进制记数法或科学记数法，具体取决于输入值的指数和指定的精度。大写。 如需了解详情，请参阅 %g 和 %G 行为。 |
392.65 3.9265E+07 INF NAN |
FLOAT64 |
s |
字符串 | sample | STRING |
t |
返回表示值的可打印字符串。通常看起来类似于将参数类型转换为 STRING。
请参阅 %t 和 %T 行为。
|
sample 2014‑01‑01 |
<任意> |
T |
生成一个字符串，该字符串是一个有效的 BigQuery 常量，其类型与值类型类似（可能宽度更大，也可能是字符串）。请参阅 %t 和 %T 行为。 |
'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01' |
<任意> |
% |
'%%' 会生成一个 '%' | % | 无 |
*如果使用了负值，则说明符 %o、%x 和 %X 会抛出错误。
格式说明符可以选择性地在说明符原型中包含上面所述的子说明符。
这些子说明符必须符合以下规范。
标记
| 标记 | 说明 |
- |
在指定字段宽度内左对齐；默认设置为右对齐（请参阅宽度子说明符） |
+ |
强制在结果前面加上加号或减号（+ 或 -），即使是正数也一样。默认情况下，只有负数才会带 - 符号前缀 |
| <空格> | 如果不会写入任何符号，则在值前插入一个空格 |
# |
|
0 |
在指定填充时，在数字左侧填充零 (0) 而非空格（请参阅宽度子说明符） |
' |
使用适当的分组字符格式化整数。 例如：
此标志仅与十进制、十六进制和八进制值相关。 |
标记可以按照任意顺序指定。 标记重复不属于错误。 在标记与某些元素类型不相关时，标记会被忽略。
宽度
| 宽度 | 说明 |
| <数字> | 需要打印的最小字符数。如果需要打印的值比这个数字短，结果将用空格填充。 即便结果较大，该值也不会被截断 |
* |
宽度并非在格式字符串中指定，而是作为一个额外的整数值参数指定，置于必须格式化的参数之前 |
精度
| 精度 | 说明 |
.<数字> |
|
.* |
精度并非在格式字符串中指定，而是作为一个额外的整数值参数，在需要设置格式的参数前面指定 |
%g 和 %G 行为
%g 和 %G 格式说明符采用十进制记数法（如 %f 和 %F 说明符）或科学记数法（如 %e 和 %E 说明符），具体取决于输入值的指数和指定的精度。
p 代表指定的精度（默认值为 6；如果指定的精度小于 1，则默认为 1）。首先将输入值转换为精度为 (p - 1) 的科学记数法。如果得到的指数部分 x 小于 -4 或不小于 p，则使用精度为 (p - 1) 的科学记数法；否则，使用精度为 (p - 1 - x) 的十进制记数法。
除非存在 # 标志，否则移除小数点后的尾随零；如果小数点后面没有数字，则也会将其移除。
%t 和 %T 行为
您需要为所有类型定义 %t 和 %T 格式说明符。 宽度、精度和标志的作用与其在 %s 中的作用相同：宽度是最小宽度，STRING 将填充到此宽度，精度是要显示的最大内容宽度，STRING 将截断到该宽度，之后再填充到合适的宽度。
%t 说明符始终是值的可读形式。
%T 说明符始终是类似类型的有效 SQL 字面量，例如宽度较大的数字类型。除了非有限浮点值的特殊情况之外，该字面量不会包含类型转换或类型名称。
STRING 的格式如下：
| Type | %t | %T |
任意类型的 NULL |
NULL |
NULL |
| INT64 |
123 | 123 |
| NUMERIC | 123.0（始终带有 .0） | NUMERIC "123.0" |
| FLOAT64 | 123.0（始终带有 .0） 123e+10 inf-infNaN
|
123.0 （始终带有 .0） 123e+10 CAST("inf" AS <type>) CAST("-inf" AS <type>) CAST("nan" AS <type>) |
| STRING | 不带引号的字符串值 | 带引号的字符串字面量 |
| BYTES | 不带英文引号的转义字节 例如 abc\x01\x02 |
带英文引号的字节字面量 例如 b"abc\x01\x02" |
| DATE | 2011-02-03 | DATE "2011-02-03" |
| TIMESTAMP | 2011-02-03 04:05:06+00 | TIMESTAMP "2011-02-03 04:05:06+00" |
| ARRAY | [value, value, ...] 其中值的格式为 %t |
[value, value, ...] 其中值的格式为 %T |
| STRUCT | (value, value, ...) 其中值的格式为 %t |
(value, value, ...) 其中值的格式为 %T 特殊情况： 零个字段：STRUCT() 一个字段：STRUCT(value) |
错误条件
如果格式说明符无效，或者与相关参数类型不兼容，或者提供的数字或参数不正确，则会产生错误。例如，以下 <format_string> 表达式无效：
FORMAT('%s', 1)
FORMAT('%')
NULL 参数处理
NULL 格式字符串是一个 NULL 输出 STRING。在这种情况下中，其他任何参数都会被忽略。
如果存在 NULL 参数，函数通常会生成 NULL 值。
例如，FORMAT('%i', NULL_expression) 会生成 NULL STRING 作为输出。
但也有一些例外情况：如果格式说明符是 %t 或 %T（两者均生成有效匹配 CAST 和字面量值语义的 STRING），则 NULL 值会在结果 STRING 中生成“NULL”（不带引号）。例如，以下函数：
FORMAT('00-%t-00', NULL_expression);
返回
00-NULL-00
其他语义规则
FLOAT64 值可以是 +/-inf 或 NaN。当参数具有其中某个值时，在适当情况下，格式说明符 %f、%F、%e、%E、%g、%G 和 %t 的结果会是 inf、-inf 或 nan（或者相同的大写形式）。这与 BigQuery 将这些值的类型转换为 STRING 的方式一致。对于 %T，BigQuery 会为没有使用非字符串字面量表示形式的 FLOAT64 值返回带英文引号的字符串。
FROM_BASE32
FROM_BASE32(string_expr)
说明
将 base32 编码的输入 string_expr 转换为 BYTES 格式。如需将 BYTES 转换为 base32 编码的 STRING，请使用 TO_BASE32。
返回类型
BYTES
示例
SELECT FROM_BASE32('MFRGGZDF74======') AS byte_data;
-- Note that the result of FROM_BASE32 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| YWJjZGX/ |
+-----------+
FROM_BASE64
FROM_BASE64(string_expr)
说明
将 base64 编码的输入 string_expr 转换为 BYTES 格式。如需将 BYTES 转换为 base64 编码的 STRING，请使用 TO_BASE64。
返回类型
BYTES
示例
SELECT FROM_BASE64('3q2+7w==') AS byte_data;
-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w== |
+-----------+
FROM_HEX
FROM_HEX(string)
说明
将十六进制编码的 STRING 转换为 BYTES 格式。如果输入 STRING 包含 (0..9, A..F, a..f) 范围以外的字符，则该函数会返回错误。字符不区分大小写。如果输入 STRING 包含奇数个字符，则该函数的作用就像是输入中有一个额外的前导 0。如需将 BYTES 转换为十六进制编码的 STRING，请使用 TO_HEX。
返回类型
BYTES
示例
WITH Input AS (
SELECT '00010203aaeeefff' AS hex_str UNION ALL
SELECT '0AF' UNION ALL
SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;
-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str | bytes_str |
+------------------+--------------+
| 0AF | AAECA6ru7/8= |
| 00010203aaeeefff | AK8= |
| 666f6f626172 | Zm9vYmFy |
+------------------+--------------+
LENGTH
LENGTH(value)
说明
返回 STRING 或 BYTES 值的长度。STRING 参数的返回值以字符为单位，BYTES 参数的返回值以字节为单位。
返回类型
INT64
示例
WITH example AS
(SELECT "абвгд" AS characters)
SELECT
characters,
LENGTH(characters) AS string_example,
LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;
+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд | 5 | 10 |
+------------+----------------+---------------+
LPAD
LPAD(original_value, return_length[, pattern])
说明
返回由附带前缀 pattern 的 original_value 组成的 STRING 或 BYTES 值。return_length 是 INT64 类型，指定了返回值的长度。如果 original_value 是 BYTES 类型，return_length 就是字节数。如果 original_value 是 STRING 类型，return_length 就是字符数。
pattern 的默认值是空格。
original_value 和 pattern 必须采用相同的数据类型。
如果 return_length 小于或等于 original_value 长度，则此函数会返回 original_value 值，并截断到 return_length 值。例如，LPAD("hello world", 7); 会返回 "hello w"。
如果 original_value、return_length 或 pattern 是 NULL，则此函数会返回 NULL。
在以下情况下，此函数会返回错误：
return_length为负数pattern为空
返回类型
STRING 或 BYTES
示例
SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | LPAD |
|------|-----|----------|
| abc | 5 | " abc" |
| abc | 2 | "ab" |
| 例子 | 4 | " 例子" |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | LPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "defdeabc" |
| abc | 5 | - | "--abc" |
| 例子 | 5 | 中文 | "中文中例子" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | LPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b" abc" |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b" \xab\xcd\xef" |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | LPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"defdeabc" |
| b"abc" | 5 | b"-" | b"--abc" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\x00\x00\xab\xcd\xef" |
+-----------------+-----+---------+-------------------------+
LOWER
LOWER(value)
说明
对于 STRING 参数，此函数会返回所有字母字符均为小写的原始字符串。它根据 Unicode 字符数据库进行小写与大写的映射，而不考虑特定语言的映射。
对于 BYTES 参数，此函数会将其视为 ASCII 文本，并将所有大于 127 的字节保留原样。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT
"FOO" as item
UNION ALL
SELECT
"BAR" as item
UNION ALL
SELECT
"BAZ" as item)
SELECT
LOWER(item) AS example
FROM items;
+---------+
| example |
+---------+
| foo |
| bar |
| baz |
+---------+
LTRIM
LTRIM(value1[, value2])
说明
与 TRIM 相同，但仅移除前导字符。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", LTRIM(item), "#") as example
FROM items;
+-------------+
| example |
+-------------+
| #apple # |
| #banana # |
| #orange # |
+-------------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
LTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| apple*** |
| banana*** |
| orange*** |
+-----------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
LTRIM(item, "xyz") as example
FROM items;
+-----------+
| example |
+-----------+
| applexxx |
| bananayyy |
| orangezzz |
| pearxyz |
+-----------+
NORMALIZE
NORMALIZE(value[, normalization_mode])
说明
获取一个字符串值，并将其作为规范化字符串返回。
规范化用于确保两个字符串的相等性。规范化往往用于两个字符串在屏幕上显示相同，但具有不同 Unicode 码位的情况。
NORMALIZE 支持四种可选的标准化模式：
| 值 | 名称 | 说明 |
|---|---|---|
| NFC | 标准化形式的规范组成 | 按规范相等性分解和重新组合字符。 |
| NFKC | 规范化形式的兼容性组成 | 按兼容性分解字符，然后按规范相等性重新组合字符。 |
| NFD | 标准化形式的规范分解 | 按规范相等性分解字符，并且按特定顺序对排列多个组合字符。 |
| NFKD | 规范化形式的兼容性分解 | 按兼容性分解字符，并按特定顺序排列多个组合字符。 |
默认标准化模式是 NFC。
返回类型
STRING
示例
SELECT a, b, a = b as normalized
FROM (SELECT NORMALIZE('\u00ea') as a, NORMALIZE('\u0065\u0302') as b)
AS normalize_example;
+---+---+------------+
| a | b | normalized |
+---+---+------------+
| ê | ê | true |
+---+---+------------+
以下示例标准化不同的空格字符。
WITH EquivalentNames AS (
SELECT name
FROM UNNEST([
'Jane\u2004Doe',
'John\u2004Smith',
'Jane\u2005Doe',
'Jane\u2006Doe',
'John Smith']) AS name
)
SELECT
NORMALIZE(name, NFKC) AS normalized_name,
COUNT(*) AS name_count
FROM EquivalentNames
GROUP BY 1;
+-----------------+------------+
| normalized_name | name_count |
+-----------------+------------+
| John Smith | 2 |
| Jane Doe | 3 |
+-----------------+------------+
NORMALIZE_AND_CASEFOLD
NORMALIZE_AND_CASEFOLD(value[, normalization_mode])
说明
获取 STRING、value，并执行与 NORMALIZE 相同的操作，并针对不区分大小写的操作执行大写转换。
NORMALIZE_AND_CASEFOLD 支持四种可选的标准化模式：
| 值 | 名称 | 说明 |
|---|---|---|
| NFC | 标准化形式的规范组成 | 按规范相等性分解和重新组合字符。 |
| NFKC | 规范化形式的兼容性组成 | 按兼容性分解字符，然后按规范相等性重新组合字符。 |
| NFD | 标准化形式的规范分解 | 按规范相等性分解字符，并且按特定顺序对排列多个组合字符。 |
| NFKD | 规范化形式的兼容性分解 | 按兼容性分解字符，并按特定顺序排列多个组合字符。 |
默认标准化模式是 NFC。
返回类型
STRING
示例
WITH Strings AS (
SELECT '\u2168' AS a, 'IX' AS b UNION ALL
SELECT '\u0041\u030A', '\u00C5'
)
SELECT a, b,
NORMALIZE_AND_CASEFOLD(a, NFD)=NORMALIZE_AND_CASEFOLD(b, NFD) AS nfd,
NORMALIZE_AND_CASEFOLD(a, NFC)=NORMALIZE_AND_CASEFOLD(b, NFC) AS nfc,
NORMALIZE_AND_CASEFOLD(a, NFKD)=NORMALIZE_AND_CASEFOLD(b, NFKD) AS nkfd,
NORMALIZE_AND_CASEFOLD(a, NFKC)=NORMALIZE_AND_CASEFOLD(b, NFKC) AS nkfc
FROM Strings;
+---+----+-------+-------+------+------+
| a | b | nfd | nfc | nkfd | nkfc |
+---+----+-------+-------+------+------+
| Ⅸ | IX | false | false | true | true |
| Å | Å | true | true | true | true |
+---+----+-------+-------+------+------+
REGEXP_CONTAINS
REGEXP_CONTAINS(value, regexp)
说明
如果 value 与正则表达式 regexp 部分匹配，则此函数返回 TRUE。
如果 regexp 参数无效，此函数会返回错误。
您可使用 ^（文本开头）和 $（文本结尾）搜索完整匹配。鉴于正则表达式运算符的优先顺序，最好为 ^ 和 $ 之间的内容加上英文括号。
返回类型
BOOL
示例
SELECT
email,
REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
(SELECT
["foo@example.com", "bar@example.org", "www.example.net"]
AS addresses),
UNNEST(addresses) AS email;
+-----------------+----------+
| email | is_valid |
+-----------------+----------+
| foo@example.com | true |
| bar@example.org | true |
| www.example.net | false |
+-----------------+----------+
# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
email,
REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
AS valid_email_address,
REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
AS without_parentheses
FROM
(SELECT
["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
AS addresses),
UNNEST(addresses) AS email;
+----------------+---------------------+---------------------+
| email | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com | true | true |
| a@foo.computer | false | true |
| b@bar.org | true | true |
| !b@bar.org | false | true |
| c@buz.net | false | false |
+----------------+---------------------+---------------------+
REGEXP_EXTRACT
REGEXP_EXTRACT(value, regexp)
说明
返回 value 中与正则表达式 regexp 匹配的第一个子字符串。如果不存在匹配项，则返回 NULL。
如果正则表达式包含捕获组，则此函数返回与该捕获组匹配的子字符串。如果表达式不包含捕获组，则函数返回整个匹配的子字符串。
出现以下情况时返回一个错误：
- 正则表达式无效
- 正则表达式具有多个捕获组
返回类型
STRING 或 BYTES
示例
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
AS user_name
FROM email_addresses;
+-----------+
| user_name |
+-----------+
| foo |
| bar |
| baz |
+-----------+
WITH email_addresses AS
(SELECT "foo@example.com" as email
UNION ALL
SELECT "bar@example.org" as email
UNION ALL
SELECT "baz@example.net" as email)
SELECT
REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
AS top_level_domain
FROM email_addresses;
+------------------+
| top_level_domain |
+------------------+
| com |
| org |
| net |
+------------------+
REGEXP_EXTRACT_ALL
REGEXP_EXTRACT_ALL(value, regexp)
说明
返回一个数组，该数组由 value 中与正则表达式 regexp 匹配的所有子字符串组成。
REGEXP_EXTRACT_ALL 函数仅返回非重叠的匹配项。例如，使用此函数从 banana 中提取 ana 时，系统只会返回一个子字符串，而不是两个。
返回类型
由 STRING 或 BYTES 组成的 ARRAY
示例
WITH code_markdown AS
(SELECT "Try `function(x)` or `function(y)`" as code)
SELECT
REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;
+----------------------------+
| example |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+
REGEXP_REPLACE
REGEXP_REPLACE(value, regexp, replacement)
说明
返回一个 STRING，其中将 value 中所有与正则表达式 regexp 匹配的子字符串替换为 replacement。
您可以在 replacement 参数中使用通过反斜杠转义的数字（\1 至 \9），借此在 regexp 模式中插入与带有英文括号的对应组匹配的文本。“\0”可用于引用整个匹配文本。
REGEXP_REPLACE 函数只会替换非重叠的匹配项。例如，替换 banana 中的 ana 时，此函数只会替换一处，而不是两处。
如果 regexp 参数不是有效的正则表达式，则此函数会返回错误。
返回类型
STRING 或 BYTES
示例
WITH markdown AS
(SELECT "# Heading" as heading
UNION ALL
SELECT "# Another heading" as heading)
SELECT
REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
AS html
FROM markdown;
+--------------------------+
| html |
+--------------------------+
| <h1>Heading</h1> |
| <h1>Another heading</h1> |
+--------------------------+
REPLACE
REPLACE(original_value, from_value, to_value)
说明
将 original_value 中出现的 from_value 全部替换为 to_value。如果 from_value 为空，则不执行替换。
返回类型
STRING 或 BYTES
示例
WITH desserts AS
(SELECT "apple pie" as dessert
UNION ALL
SELECT "blackberry pie" as dessert
UNION ALL
SELECT "cherry pie" as dessert)
SELECT
REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;
+--------------------+
| example |
+--------------------+
| apple cobbler |
| blackberry cobbler |
| cherry cobbler |
+--------------------+
REPEAT
REPEAT(original_value, repetitions)
说明
返回由重复的 original_value 组成的 STRING 或 BYTES 值。repetitions 参数指定了 original_value 的重复次数。如果 original_value 或 repetitions 为 NULL，则返回 NULL。
如果 repetitions 值为负数，该函数会返回错误。
返回类型
STRING 或 BYTES
示例
SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
STRUCT('abc' AS t, 3 AS n),
('例子', 2),
('abc', null),
(null, 3)
]);
+------+------+-----------+
| t | n | REPEAT |
|------|------|-----------|
| abc | 3 | abcabcabc |
| 例子 | 2 | 例子例子 |
| abc | NULL | NULL |
| NULL | 3 | NULL |
+------+------+-----------+
REVERSE
REVERSE(value)
说明
返回输入 STRING 或 BYTES 的反转值。
返回类型
STRING 或 BYTES
示例
WITH example AS (
SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
sample_string,
REVERSE(sample_string) AS reverse_string,
sample_bytes,
REVERSE(sample_bytes) AS reverse_bytes
FROM example;
+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo | oof | bar | rab |
| абвгд | дгвба | 123 | 321 |
+---------------+----------------+--------------+---------------+
RPAD
RPAD(original_value, return_length[, pattern])
说明
返回由附带前缀 pattern 的 original_value 组成的 STRING 或 BYTES 值。return_length 参数是 INT64 类型，指定了返回值的长度。如果 original_value 是 BYTES，return_length 就是字节数。如果 original_value 是 STRING，return_length 就是字符数。
pattern 的默认值是空格。
original_value 和 pattern 必须采用相同的数据类型。
如果 return_length 小于或等于 original_value 长度，则此函数会返回 original_value 值，并截断到 return_length 值。例如，RPAD("hello world", 7); 会返回 "hello w"。
如果 original_value、return_length 或 pattern 是 NULL，则此函数会返回 NULL。
在以下情况下，此函数会返回错误：
return_length为负数pattern为空
返回类型
STRING 或 BYTES
示例
SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 5 AS len),
('abc', 2),
('例子', 4)
]);
+------+-----+----------+
| t | len | RPAD |
|------|-----|----------|
| abc | 5 | "abc " |
| abc | 2 | "ab" |
| 例子 | 4 | "例子 " |
+------+-----+----------+
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
('abc', 5, '-'),
('例子', 5, '中文')
]);
+------+-----+---------+--------------+
| t | len | pattern | RPAD |
|------|-----|---------|--------------|
| abc | 8 | def | "abcdefde" |
| abc | 5 | - | "abc--" |
| 例子 | 5 | 中文 | "例子中文中" |
+------+-----+---------+--------------+
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
STRUCT(b'abc' AS t, 5 AS len),
(b'abc', 2),
(b'\xab\xcd\xef', 4)
]);
+-----------------+-----+------------------+
| t | len | RPAD |
|-----------------|-----|------------------|
| b"abc" | 5 | b"abc " |
| b"abc" | 2 | b"ab" |
| b"\xab\xcd\xef" | 4 | b"\xab\xcd\xef " |
+-----------------+-----+------------------+
SELECT
FORMAT("%T", t) AS t,
len,
FORMAT("%T", pattern) AS pattern,
FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
(b'abc', 5, b'-'),
(b'\xab\xcd\xef', 5, b'\x00')
]);
+-----------------+-----+---------+-------------------------+
| t | len | pattern | RPAD |
|-----------------|-----|---------|-------------------------|
| b"abc" | 8 | b"def" | b"abcdefde" |
| b"abc" | 5 | b"-" | b"abc--" |
| b"\xab\xcd\xef" | 5 | b"\x00" | b"\xab\xcd\xef\x00\x00" |
+-----------------+-----+---------+-------------------------+
RTRIM
RTRIM(value1[, value2])
说明
与 TRIM 相同，但仅移除尾随字符。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
RTRIM(item, "*") as example
FROM items;
+-----------+
| example |
+-----------+
| ***apple |
| ***banana |
| ***orange |
+-----------+
WITH items AS
(SELECT "applexxx" as item
UNION ALL
SELECT "bananayyy" as item
UNION ALL
SELECT "orangezzz" as item
UNION ALL
SELECT "pearxyz" as item)
SELECT
RTRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
SAFE_CONVERT_BYTES_TO_STRING
SAFE_CONVERT_BYTES_TO_STRING(value)
说明
将 BYTES 序列转换为 STRING。任何无效的 UTF-8 字符都会替换为 Unicode 替换字符 U+FFFD。
返回类型
STRING
示例
以下语句会返回 Unicode 替换字符 �。
SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;
SPLIT
SPLIT(value[, delimiter])
说明
使用 delimiter 参数拆分 value。
对于 STRING，默认分隔符是英文逗号 ,。
对于 BYTES，您必须指定一个分隔符。
对于 STRING 值，拆分空的分隔符会生成一个 UTF-8 字符数组，对于 BYTES 值则会生成一个 BYTES 数组。
拆分空 STRING 会返回包含一个空 STRING 的 ARRAY。
返回类型
类型为 STRING 的 ARRAY，或类型为 BYTES 的 ARRAY
示例
WITH letters AS
(SELECT "" as letter_group
UNION ALL
SELECT "a" as letter_group
UNION ALL
SELECT "b c d" as letter_group)
SELECT SPLIT(letter_group, " ") as example
FROM letters;
+----------------------+
| example |
+----------------------+
| [] |
| [a] |
| [b, c, d] |
+----------------------+
STARTS_WITH
STARTS_WITH(value1, value2)
说明
获取两个 STRING 或 BYTES 值。如果第二个值是第一个值的前缀，则返回 TRUE。
返回类型
BOOL
示例
WITH items AS
(SELECT "foo" as item
UNION ALL
SELECT "bar" as item
UNION ALL
SELECT "baz" as item)
SELECT
STARTS_WITH(item, "b") as example
FROM items;
+---------+
| example |
+---------+
| False |
| True |
| True |
+---------+
STRPOS
STRPOS(string, substring)
说明
返回 string 中第一次出现 substring 处的索引（从 1 开始）。如果未找到 substring，则此函数返回 0。
返回类型
INT64
示例
WITH email_addresses AS
(SELECT
"foo@example.com" AS email_address
UNION ALL
SELECT
"foobar@example.com" AS email_address
UNION ALL
SELECT
"foobarbaz@example.com" AS email_address
UNION ALL
SELECT
"quxexample.com" AS email_address)
SELECT
STRPOS(email_address, "@") AS example
FROM email_addresses;
+---------+
| example |
+---------+
| 4 |
| 7 |
| 10 |
| 0 |
+---------+
SUBSTR
SUBSTR(value, position[, length])
说明
返回所提供 STRING 或 BYTES 值的子字符串。position 参数是一个整数，指定了子字符串的开始位置；position = 1 时，表示第一个字符或字节。length 参数是 STRING 参数的字符数上限，或 BYTES 参数的字节数上限。
如果 position 为负数，则此函数会从 value 末尾开始计数，其中 -1 表示最后一个字符。
如果 position 表示不在 STRING 左端的位置（position = 0 或 position < -LENGTH(value)），则此函数会从 position = 1 处开始计数。如果 length 超出了 value 的长度，则函数返回的字符数会少于 length。
如果 length 小于 0，则此函数会返回错误。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2) as example
FROM items;
+---------+
| example |
+---------+
| pple |
| anana |
| range |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, 2, 2) as example
FROM items;
+---------+
| example |
+---------+
| pp |
| an |
| ra |
+---------+
WITH items AS
(SELECT "apple" as item
UNION ALL
SELECT "banana" as item
UNION ALL
SELECT "orange" as item)
SELECT
SUBSTR(item, -2) as example
FROM items;
+---------+
| example |
+---------+
| le |
| na |
| ge |
+---------+
TO_BASE32
TO_BASE32(bytes_expr)
说明
将 BYTES 序列转换为 base32 编码的 STRING。如需将 base32 编码的 STRING 转换为 BYTES，请使用 FROM_BASE32。
返回类型
STRING
示例
SELECT TO_BASE32(b'abcde\xFF') AS base32_string;
+------------------+
| base32_string |
+------------------+
| MFRGGZDF74====== |
+------------------+
TO_BASE64
TO_BASE64(bytes_expr)
说明
将 BYTES 序列转换为 base64 编码的 STRING。如需将 base64 编码的 STRING 转换为 BYTES，请使用 FROM_BASE64。
返回类型
STRING
示例
SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;
+---------------+
| base64_string |
+---------------+
| 3q2+7w== |
+---------------+
TO_CODE_POINTS
TO_CODE_POINTS(value)
说明
接受一个值并返回 INT64 类型数组。
- 如果
value是STRING，则所返回数组中的每个元素都代表一个码位。每个码位都位于 [0, 0xD7FF] 和 [0xE000, 0x10FFFF] 范围内。 - 如果
value是BYTES，则数组中的每个元素都是 [0, 255] 范围内的一个扩展 ASCII 字符值。
如需将码位数组转换为 STRING 或 BYTES，请参阅 CODE_POINTS_TO_STRING 或 CODE_POINTS_TO_BYTES。
返回类型
ARRAY/INT64
示例
以下示例为一个单词数组中的每个元素获取代码点。
SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;
+---------+------------------------------------+
| word | code_points |
+---------+------------------------------------+
| foo | [102, 111, 111] |
| bar | [98, 97, 114] |
| baz | [98, 97, 122] |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama | [108, 108, 97, 109, 97] |
+---------+------------------------------------+
以下示例将 BYTES 的整数表示形式转换为对应的 ASCII 字符值。
SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;
+------------------+------------------------+
| word | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255] |
| foo | [102, 111, 111] |
+------------------+------------------------+
以下示例演示了一个 BYTES 结果与一个 STRING 结果之间的区别。
SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;
+------------+----------+
| b_result | s_result |
+------------+----------+
| [196, 128] | [256] |
+------------+----------+
请注意字符 Ā 表示为两个字节的 Unicode 序列。因此，TO_CODE_POINTS 的 BYTES 版本会返回包含两个元素的数组，而 STRING 版本会返回包含单个元素的数组。
TO_HEX
TO_HEX(bytes)
说明
将 BYTES 序列转换为十六进制 STRING。将 STRING 中的各字节转换为 (0..9, a..f) 范围内的两个十六进制字符。如需将十六进制编码的 STRING 转换为 BYTES，请使用 FROM_HEX。
返回类型
STRING
示例
WITH Input AS (
SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;
+----------------------------------+------------------+
| byte_string | hex_string |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar | 666f6f626172 |
+----------------------------------+------------------+
TRIM
TRIM(value1[, value2])
说明
移除与 value2 匹配的所有前导和尾随字符。如果未指定 value2，则移除所有前导和尾随空格（如 Unicode 标准定义）。如果第一个参数是 BYTES 类型，则必须提供第二个参数。
如果 value2 包含多个字符或字节，则该函数会移除 value2 中包含的所有前导或尾随字符或字节。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT " apple " as item
UNION ALL
SELECT " banana " as item
UNION ALL
SELECT " orange " as item)
SELECT
CONCAT("#", TRIM(item), "#") as example
FROM items;
+----------+
| example |
+----------+
| #apple# |
| #banana# |
| #orange# |
+----------+
WITH items AS
(SELECT "***apple***" as item
UNION ALL
SELECT "***banana***" as item
UNION ALL
SELECT "***orange***" as item)
SELECT
TRIM(item, "*") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
+---------+
WITH items AS
(SELECT "xxxapplexxx" as item
UNION ALL
SELECT "yyybananayyy" as item
UNION ALL
SELECT "zzzorangezzz" as item
UNION ALL
SELECT "xyzpearxyz" as item)
SELECT
TRIM(item, "xyz") as example
FROM items;
+---------+
| example |
+---------+
| apple |
| banana |
| orange |
| pear |
+---------+
UPPER
UPPER(value)
说明
对于 STRING 参数，该函数会返回所有字母字符均为大写的原始字符串。它根据 Unicode 字符数据库进行大写与小写之间的映射，而不考虑特定语言的映射。
对于 BYTES 参数，此函数会将其视为 ASCII 文本，并将所有大于 127 的字节保留原样。
返回类型
STRING 或 BYTES
示例
WITH items AS
(SELECT
"foo" as item
UNION ALL
SELECT
"bar" as item
UNION ALL
SELECT
"baz" as item)
SELECT
UPPER(item) AS example
FROM items;
+---------+
| example |
+---------+
| FOO |
| BAR |
| BAZ |
+---------+
JSON 函数
BigQuery 支持帮助您检索 JSON 格式字符串内存储的数据的函数，以及帮助您将数据转化为 JSON 格式字符串的函数。
JSON_EXTRACT 或 JSON_EXTRACT_SCALAR
JSON_EXTRACT(json_string_expr,
json_path_string_literal)，返回 STRING 格式的 JSON 值。
JSON_EXTRACT_SCALAR(json_string_expr,
json_path_string_literal)，返回 STRING 格式的标量 JSON 值。
说明
提取 STRING 格式的 JSON 值或 JSON 标量值。
json_string_expr：JSON 格式的字符串。例如：{"class" : {"students" : [{"name" : "Jane"}]}}json_path_string_literal：JSONpath 格式。 这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果json_path_string_literal返回 JSONnull，则将其转换为 SQLNULL。
如果 JSON 密钥使用无效的 JSONPath 字符，您可以使用英文单引号和英文括号对这些字符进行转义。
返回类型
STRING
示例
SELECT JSON_EXTRACT(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_EXTRACT(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_EXTRACT(json_text, "$.class['students']") AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_EXTRACT('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_EXTRACT_SCALAR('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
如果 JSON 密钥使用无效的 JSONPath 字符，您可以使用单引号和括号 [' '] 对这些字符进行转义。例如：
SELECT JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+-------+
| hello |
+-------+
| world |
+-------+
JSON_EXTRACT_ARRAY
JSON_EXTRACT_ARRAY(json_string_expr[, json_path_string_literal])
说明
从 JSON 格式的字符串中提取数组。
json_string_expr：JSON 格式的字符串。例如：{"class" : {"students" : [{"name" : "Jane"}]}}json_path_string_literal：JSONpath 格式。 这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果未提供此可选参数，则应用 JSONPath$符号，这意味着分析整个 JSON 格式的字符串。
如果 JSON 密钥使用无效的 JSONPath 字符，您可以使用英文单引号和英文括号对这些字符进行转义。
返回类型
ARRAY<STRING>
示例
以下示例将 JSON 格式的字符串中的项提取到字符串数组中：
SELECT JSON_EXTRACT_ARRAY('[1,2,3]') as string_array
+----------------+
| string_array |
+----------------+
| ['1','2','3'] |
+----------------+
以下示例提取字符串数组并将其转换为整数数组：
SELECT ARRAY(
SELECT CAST(integer_element as INT64)
FROM UNNEST(
JSON_EXTRACT_ARRAY('[1,2,3]','$')
) AS integer_element
) AS integer_array
+---------------+
| integer_array |
+---------------+
| [1,2,3] |
+---------------+
以下示例将 JSON 格式的字符串中的字符串值提取到数组中：
-- Don't strip the double quotes
SELECT JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]', '$') as string_array
+--------------------------------------+
| string_array |
+--------------------------------------+
| ['"apples"','"oranges"','"grapes"'] |
+--------------------------------------+
-- Strip the double quotes
SELECT ARRAY(
SELECT JSON_EXTRACT_SCALAR(string_element, '$')
FROM UNNEST(JSON_EXTRACT_ARRAY('["apples","oranges","grapes"]','$')) AS string_element
) AS string_array
+---------------------------------+
| string_array |
+---------------------------------+
| ['apples', 'oranges', 'grapes'] |
+---------------------------------+
以下示例仅将 fruit 中的项提取到数组中：
SELECT JSON_EXTRACT_ARRAY(
'{"fruit":[{"apples":5,"oranges":10},{"apples":2,"oranges":4}],"vegetables":[{"lettuce":7,"kale": 8}}',
'$.fruit'
) as string_array
+----------------------------------------------------------------------+
| string_array |
+----------------------------------------------------------------------+
| ['{"apples" : 5, "oranges" : 10}' , '{"apples" : 2, "oranges" : 4}'] |
+----------------------------------------------------------------------+
这些等价于：
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$[fruits]') as string_array
JSON_EXTRACT_ARRAY('{"fruits":["apples","oranges","grapes"]}','$.fruits') as string_array
-- The queries above produce this result:
+---------------------------------------+
| string_array |
+---------------------------------------+
| [""apples"", ""oranges"", ""grapes""] |
+---------------------------------------+
如果 JSON 密钥使用无效的 JSONPath 字符，您可以使用英文单引号和英文括号 [' '] 对这些字符进行转义。例如：
SELECT JSON_EXTRACT_ARRAY('{"a.b": {"c": "world"}}', "$['a.b'].c") as hello;
+---------+
| hello |
+---------+
| [world] |
+---------+
以下示例探讨了如何处理无效的请求和空数组：
- 如果 JSONPath 无效，则系统会抛出错误。
- 如果 JSON 格式的字符串无效，则输出为 NULL。
- JSON 格式的字符串中可以包含空数组。
-- An error is thrown if you provide an invalid JSONPath.
JSON_EXTRACT_ARRAY('["foo","bar","baz"]','INVALID_JSONPath') as result
-- If a key is not specified when a key is expected, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- If a key that does not exist is specified, the result is NULL.
JSON_EXTRACT_ARRAY('{"a":"foo"}','$.b') as result
+--------+
| result |
+--------+
| NULL |
+--------+
-- Empty arrays in JSON-formatted strings are supported.
JSON_EXTRACT_ARRAY('{"a":"foo","b":[]}','$.b') as result
+--------+
| result |
+--------+
| [] |
+--------+
JSON_QUERY 或 JSON_VALUE
JSON_QUERY(json_string_expr, json_path_string_literal)，返回 STRING 格式的 JSON 值。
JSON_VALUE(json_string_expr, json_path_string_literal)，返回 STRING 格式的标量 JSON 值。
说明
提取 STRING 格式的 JSON 值或 JSON 标量值。
json_string_expr：JSON 格式的字符串。例如：{"class" : {"students" : [{"name" : "Jane"}]}}json_path_string_literal：JSONpath 格式。 这用于标识要从 JSON 格式的字符串中获取的一个或多个值。如果json_path_string_literal返回 JSONnull，则将其转换为 SQLNULL。
如果 JSON 密钥使用无效的 JSONPath 字符，则可以使用双引号转义这些字符。
示例
SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------------------------------------------------+
| json_text_string |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}} |
| {"class":{"students":[]}} |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-----------------+
| first_student |
+-----------------+
| {"name":"Jane"} |
| NULL |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name" : null}]}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+-------------------+
| second_student |
+-------------------+
| NULL |
| NULL |
| NULL |
| "Jamie" |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
'{"class" : {"students" : [{"name" : "Jane"}]}}',
'{"class" : {"students" : []}}',
'{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
]) AS json_text;
+------------------------------------+
| student_names |
+------------------------------------+
| [{"name":"Jane"}] |
| [] |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;
+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob" | Jakob | "6" | 6 |
+-----------+-------------+----------+--------+
如果 JSON 密钥使用无效的 JSONPath 字符，则可以使用双引号转义这些字符。例如：
SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;
+-------+
| hello |
+-------+
| world |
+-------+
TO_JSON_STRING
TO_JSON_STRING(value[, pretty_print])
说明
返回 value 的 JSON 格式的字符串表示形式。此函数支持可选的 pretty_print 参数。如果存在 pretty_print，返回值将以易读性为目标进行格式设置。
| 输入数据类型 | 返回值 |
|---|---|
| 任意类型的 NULL | null |
| BOOL | true 或 false。 |
| INT64 | 当 -10123456789019007199254740992-9007199254740992"9007199254740993"
|
| NUMERIC | 当 -10"9007199254740993""123.56"
|
| FLOAT64 | +/-inf 和 NaN 分别表示为 Infinity、-Infinity 和 NaN。
否则，与 |
| STRING | 带英文引号的字符串值，根据 JSON 标准进行转义。具体来说，"、\ 和从 U+0000 到 U+001F 的控制字符会予以转义。 |
| BYTES | 带引号的 RFC 4648 base64 转义值。例如：
|
| DATE | 带引号的日期。例如： "2017-03-06"
|
| TIMESTAMP | 带引号的 ISO 8601 日期时间，其中 T 分隔日期和时间，Zulu/UTC 表示时区。例如： "2017-03-06T12:34:56.789012Z"
|
| DATETIME | 带引号的 ISO 8601 日期时间，其中 T 分隔日期和时间。例如： "2017-03-06T12:34:56.789012"
|
| TIME | 带引号的 ISO 8601 时间。例如： "12:34:56.789012" |
| ARRAY |
[ elem1, elem2, ... ] 其中每个元素均根据元素类型进行格式设置。空数组表示为 |
| STRUCT | {"field_name1":field_value1,"field_name2":field_value2,...}
其中每个
{
"field_name1": field_value1,
"field_name2": field_value2,
...
}
其中每个 名称重复的字段可能导致无法解析的 JSON。匿名字段用 无效 UTF-8 字段名称可能导致无法解析的 JSON。字符串值根据 JSON 标准进行转义。具体来说， |
返回类型
值的 JSON 字符串表示形式。
示例
将表中的行转换为 JSON。
WITH Input AS (
SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
t,
TO_JSON_STRING(t) AS json_row
FROM Input AS t;
以上查询会生成以下结果：
+-----------------------------------+-------------------------------------------------------+
| t | json_row |
+-----------------------------------+-------------------------------------------------------+
| {[1, 2], foo, {true, 2017-04-05}} | {"x":[1,2],"y":"foo","s":{"a":true,"b":"2017-04-05"}} |
| {NULL, , {false, 0001-01-01}} | {"x":null,"y":"","s":{"a":false,"b":"0001-01-01"}} |
| {[3], bar, {NULL, 2016-12-05}} | {"x":[3],"y":"bar","s":{"a":null,"b":"2016-12-05"}} |
+-----------------------------------+-------------------------------------------------------+
通过格式化将表中的行转换为 JSON。
WITH Input AS (
SELECT [1, 2] AS x, 'foo' AS y, STRUCT(true AS a, DATE '2017-04-05' AS b) AS s UNION ALL
SELECT NULL AS x, '' AS y, STRUCT(false AS a, DATE '0001-01-01' AS b) AS s UNION ALL
SELECT [3] AS x, 'bar' AS y, STRUCT(NULL AS a, DATE '2016-12-05' AS b) AS s
)
SELECT
TO_JSON_STRING(t, true) AS json_row
FROM Input AS t;
以上查询会生成以下结果：
+-----------------------+
| json_row |
+-----------------------+
| { |
| "x": [ |
| 1, |
| 2 |
| ], |
| "y": "foo", |
| "s": { |
| "a": true, |
| "b": "2017-04-05" |
| } |
|} |
| { |
| "x": null, |
| "y": "", |
| "s": { |
| "a": false, |
| "b": "0001-01-01" |
| } |
|} |
| { |
| "x": [ |
| 3 |
| ], |
| "y": "bar", |
| "s": { |
| "a": null, |
| "b": "2016-12-05" |
| } |
|} |
+-----------------------+
JSONPath 格式
大多数 JSON 函数都会传入 json_string_expr 和 json_path_string_literal 参数。json_string_expr 参数传入 JSON 格式的字符串，而 json_path_string_literal 参数用于标识要从 JSON 格式的字符串中获取的一个或多个值。
json_string_expr 参数必须是格式如下的 JSON 字符串：
{"class" : {"students" : [{"name" : "Jane"}]}}
您可以使用 JSONPath 格式构建 json_path_string_literal 参数。作为此格式的一部分，此参数必须以 $ 符号开头，这个符号标识 JSON 格式字符串的最外层。您可以使用英文句点来标识子值。如果 JSON 对象是数组，则可以使用英文括号指定数组索引。如果键包含 $、英文句点或英文方括号，请参阅各 JSON 函数以了解如何对其进行转义。
| JSONPath | 说明 | 示例 | 使用上述 json_string_expr 的结果 |
|---|---|---|---|
| $ | 根对象或元素 | "$" | {"class":{"students":[{"name":"Jane"}]}} |
| . | 子运算符 | "$.class.students" | [{"name":"Jane"}] |
| [] | 下标运算符 | "$.class.students[0]" | {"name":"Jane"} |
如果 json_path_string_literal 参数与 json_string_expr 中的值不匹配，则 JSON 函数会返回 NULL。如果标量函数的选定值不是标量，例如一个对象或一个数组，该函数会返回 NULL。
如果 JSONPath 无效，则函数会引发错误。
数组函数
ARRAY
ARRAY(subquery)
说明
ARRAY 函数返回一个 ARRAY，其中子查询中的每一行都对应一个元素。
如果 subquery 生成一个 SQL 表，则此表必须只能包含一列。ARRAY 输出中的每个元素都是此表中这一列某行的值。
如果 subquery 生成一个值表，则输出 ARRAY 中的每个元素都是该值表中相应的一整行。
限制条件
- 子查询是无序的，因此不保证输出
ARRAY的元素为子查询保留源表中的任何顺序。但是，如果子查询包含ORDER BY子句，则ARRAY函数将返回一个遵循此子句的ARRAY。 - 如果子查询返回多个列，则
ARRAY函数会返回错误。 - 如果子查询返回
ARRAY类型的列或ARRAY类型的行，则ARRAY函数会返回错误：BigQuery 不支持元素类型为ARRAY的ARRAY。 - 如果子查询未返回任何行，则
ARRAY函数会返回一个空的ARRAY。该函数在任何情况下都不会返回NULLARRAY。
返回类型
ARRAY
示例
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+
如需通过包含多个列的子查询构造 ARRAY，请将子查询改为使用 SELECT AS STRUCT。现在 ARRAY 函数将返回由 STRUCT 组成的 ARRAY。对于子查询中的每一行，ARRAY 都包含一个 STRUCT，并且其中每个 STRUCT 都包含一个字段，对应于该行中的每一列。
SELECT
ARRAY
(SELECT AS STRUCT 1, 2, 3
UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;
+------------------------+
| new_array |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+
同样，要通过包含一个或多个 ARRAY 的子查询构造 ARRAY，请将该子查询改为使用 SELECT AS STRUCT。
SELECT ARRAY
(SELECT AS STRUCT [1, 2, 3] UNION ALL
SELECT AS STRUCT [4, 5, 6]) AS new_array;
+----------------------------+
| new_array |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+
ARRAY_CONCAT
ARRAY_CONCAT(array_expression_1 [, array_expression_n])
说明
将一个或多个具有相同元素类型的数组串联为一个数组。
返回类型
ARRAY
示例
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
ARRAY_LENGTH
ARRAY_LENGTH(array_expression)
说明
返回数组的大小。如果数组为空，则返回 0。如果 array_expression 是 NULL，则返回 NULL。
返回类型
INT64
示例
WITH items AS
(SELECT ["coffee", NULL, "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;
+---------------------------------+------+
| list | size |
+---------------------------------+------+
| [coffee, NULL, milk] | 3 |
| [cake, pie] | 2 |
+---------------------------------+------+
ARRAY_TO_STRING
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
说明
将 array_expression 中的元素连接起来并作为 STRING 返回。array_expression 的值可以是数据类型为 STRING 或 BYTES 的数组。
如果使用了 null_text 参数，该函数会将数组中的任何 NULL 值替换为 null_text 值。
如果未使用 null_text 参数，该函数会忽略 NULL 值及其前置分隔符。
示例
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie |
+--------------------------------+
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie--MISSING |
+--------------------------------+
GENERATE_ARRAY
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
说明
返回一个值数组。start_expression 和 end_expression 参数确定数组的开始值和结束值（包含边界值）。
GENERATE_ARRAY 函数接受以下数据类型的输入：
- INT64
- NUMERIC
- FLOAT64
step_expression 参数确定用于生成数组值的增量。此参数的默认值为 1。
如果 step_expression 设置为 0 或者有任何输入为 NaN，此函数将返回错误。
如果任意参数为 NULL，该函数将返回一个 NULL 数组。
返回数据类型
ARRAY
示例
以下命令返回一个整数数组，默认步长为 1。
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
以下命令使用用户指定的步长返回一个数组。
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
以下命令使用负值步长 -3 返回一个数组。
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
以下命令返回一个 start_expression 和 end_expression 的值相同的数组。
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
以下命令返回一个空数组，因为 start_expression 大于 end_expression，且 step_expression 值为正数。
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
以下命令返回一个 NULL 数组，因为 end_expression 为 NULL。
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;
+---------------+
| example_array |
+---------------+
| NULL |
+---------------+
以下命令返回多个数组。
SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;
+---------------+
| example_array |
+---------------+
| [3, 4, 5] |
| [4, 5] |
| [5] |
+---------------+
GENERATE_DATE_ARRAY
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
说明
返回一个日期数组。start_date 和 end_date 参数确定数组的开始值和结束值（包含边界值）。
GENERATE_DATE_ARRAY 函数接受以下数据类型的输入：
start_date必须是 DATEend_date必须是 DATEINT64_expr必须是 INT64date_part必须是 DAY、WEEK、MONTH、QUARTER 或 YEAR。
INT64_expr 参数确定用于生成日期的增量。此参数的默认值为 1 天。
如果 INT64_expr 设置为 0，此函数将返回错误。
返回数据类型
包含 0 个或多个 DATE 值的 ARRAY。
示例
以下命令使用默认步长 1 返回日期数组。
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
+--------------------------------------------------+
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+
以下命令使用用户指定的步长返回一个数组。
SELECT GENERATE_DATE_ARRAY(
'2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;
+--------------------------------------+
| example |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+
以下命令使用负值步长 -3 返回一个数组。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL -3 DAY) AS example;
+--------------------------+
| example |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+
以下命令返回一个 start_date 和 end_date 的值相同的数组。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
以下命令返回一个空数组，因为 start_date 大于 end_date，且 step 值为正数。
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL 1 DAY) AS example;
+---------+
| example |
+---------+
| [] |
+---------+
以下命令返回一个 NULL 数组，因为其输入之一是 NULL。
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;
+---------+
| example |
+---------+
| NULL |
+---------+
以下命令使用 MONTH 作为 date_part 时间间隔返回一个日期数组：
SELECT GENERATE_DATE_ARRAY('2016-01-01',
'2016-12-31', INTERVAL 2 MONTH) AS example;
+--------------------------------------------------------------------------+
| example |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+
以下命令使用非常量日期生成一个数组。
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;
+--------------------------------------------------------------+
| date_range |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+
GENERATE_TIMESTAMP_ARRAY
GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
INTERVAL step_expression date_part)
说明
返回一个按指定时间间隔隔开的 TIMESTAMPS ARRAY。start_timestamp 和 end_timestamp 参数确定 ARRAY 的上限和下限（包含边界值）。
GENERATE_TIMESTAMP_ARRAY 函数接受以下数据类型的输入：
start_timestamp：TIMESTAMPend_timestamp：TIMESTAMPstep_expression：INT64- 允许的
date_part值包括：MICROSECOND、MILLISECOND、SECOND、MINUTE、HOUR或DAY。
step_expression 参数确定用于生成时间戳的增量。
返回数据类型
包含 0 个或更多个 TIMESTAMP 值的 ARRAY。
示例
以下示例返回时间间隔为 1 天的 TIMESTAMP 的 ARRAY。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-07 00:00:00',
INTERVAL 1 DAY) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-06 00:00:00+00, 2016-10-07 00:00:00+00] |
+--------------------------------------------------------------------------+
以下示例返回时间间隔为 1 秒的 TIMESTAMP ARRAY。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
INTERVAL 1 SECOND) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+
以下示例返回时间间隔为负数的 TIMESTAMPS ARRAY。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
INTERVAL -2 DAY) AS timestamp_array;
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+
以下示例返回只包含一个元素的 ARRAY，因为 start_timestamp 和 end_timestamp 的值相同。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
INTERVAL 1 HOUR) AS timestamp_array;
+--------------------------+
| timestamp_array |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+
以下示例返回一个空 ARRAY，因为 start_timestamp 晚于 end_timestamp。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
INTERVAL 1 HOUR) AS timestamp_array;
+-----------------+
| timestamp_array |
+-----------------+
| [] |
+-----------------+
以下示例返回一个 null ARRAY，因为有一项输入为 NULL。
SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
AS timestamp_array;
+-----------------+
| timestamp_array |
+-----------------+
| NULL |
+-----------------+
以下示例根据包含 start_timestamp 和 end_timestamp 值的列生成一个 TIMESTAMP ARRAY。
SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
AS timestamp_array
FROM
(SELECT
TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
UNION ALL
SELECT
TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
UNION ALL
SELECT
TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);
+--------------------------------------------------------------------------+
| timestamp_array |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+
OFFSET 和 ORDINAL
array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]
说明
按位置访问 ARRAY 元素并返回该元素。OFFSET 表示从 0 开始计数，ORDINAL 表示从 1 开始计数。
给定数组要么解释为从 0 开始，要么解释为从 1 开始。访问数组元素时，必须在数组位置之前分别附加 OFFSET 或 ORDINAL；该行为并非默认行为。
如果索引超出范围，OFFSET 和 ORDINAL 都会生成错误。
返回类型
因 ARRAY 中的元素而异。
示例
WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list
UNION ALL
SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;
+----------------------------------+-----------+-----------+
| list | offset_1 | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas | apples |
| [coffee, tea, milk] | tea | coffee |
| [cake, pie] | pie | cake |
+----------------------------------+-----------+-----------+
ARRAY_REVERSE
ARRAY_REVERSE(value)
说明
按照输入 ARRAY 中元素的反向顺序返回该 ARRAY。
返回类型
ARRAY
示例
WITH example AS (
SELECT [1, 2, 3] AS arr UNION ALL
SELECT [4, 5] AS arr UNION ALL
SELECT [] AS arr
)
SELECT
arr,
ARRAY_REVERSE(arr) AS reverse_arr
FROM example;
+-----------+-------------+
| arr | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1] |
| [4, 5] | [5, 4] |
| [] | [] |
+-----------+-------------+
SAFE_OFFSET 和 SAFE_ORDINAL
array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]
说明
基本等同于 OFFSET 和 ORDINAL，不同之处在于，如果索引超出范围，则返回 NULL。
返回类型
因 ARRAY 中的元素而异。
示例
WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list
UNION ALL
SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list,
list[SAFE_OFFSET(3)] as safe_offset_3,
list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;
+----------------------------------+---------------+----------------+
| list | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes | pears |
| [coffee, tea, milk] | NULL | milk |
| [cake, pie] | NULL | NULL |
+----------------------------------+---------------+----------------+
日期函数
BigQuery 支持以下 DATE 函数。
CURRENT_DATE
CURRENT_DATE([time_zone])
说明
返回指定或默认时区的当前日期。
此函数支持 time_zone 可选参数。此参数是一个字符串，表示要使用的时区。如果未指定时区，则使用默认时区世界协调时间 (UTC)。如需了解如何指定时区，请参阅时区定义。
如果 time_zone 参数的计算结果为 NULL，则此函数会返回 NULL。
返回数据类型
DATE
示例
SELECT CURRENT_DATE() as the_date;
+--------------+
| the_date |
+--------------+
| 2016-12-25 |
+--------------+
EXTRACT
EXTRACT(part FROM date_expression)
说明
返回与指定日期部分相对应的值。part 必须是以下其中一项：
DAYOFWEEK：返回 [1,7] 范围内的值，其中星期日是一周的第一天。DAYDAYOFYEARWEEK：返回日期属于一年中的第几周，范围为 [0, 53]。一周从星期日开始算起。一年第一个星期日之前的日期属于第 0 周。WEEK(<WEEKDAY>)：返回日期属于一年中的第几周，范围为 [0, 53]。一周从WEEKDAY开始算起。一年之中第一个WEEKDAY之前的日期属于第 0 周。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：以 ISO 8601 格式返回date_expression属于一年之中的第几周。ISOWEEK从星期一开始算起。返回值的范围为 [1, 53]。每个 ISO 格式年份的第一个ISOWEEK从公历年第一个星期四之前的星期一开始算起。MONTHQUARTER：返回 [1,4] 范围内的值。YEARISOYEAR：以 ISO 8601 格式返回周编号年份，即包含date_expression所属一周的星期四的公历年）。
返回数据类型
INT64
示例
在以下示例中，EXTRACT 返回了与 DAY 日期部分相对应的值。
SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;
+---------+
| the_day |
+---------+
| 25 |
+---------+
在以下示例中，EXTRACT 返回了与日期一列中接近年末的不同日期部分相对应的值。
SELECT
date,
EXTRACT(ISOYEAR FROM date) AS isoyear,
EXTRACT(ISOWEEK FROM date) AS isoweek,
EXTRACT(YEAR FROM date) AS year,
EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015 | 52 | 2015 | 51 |
| 2015-12-24 | 2015 | 52 | 2015 | 51 |
| 2015-12-25 | 2015 | 52 | 2015 | 51 |
| 2015-12-26 | 2015 | 52 | 2015 | 51 |
| 2015-12-27 | 2015 | 52 | 2015 | 52 |
| 2015-12-28 | 2015 | 53 | 2015 | 52 |
| 2015-12-29 | 2015 | 53 | 2015 | 52 |
| 2015-12-30 | 2015 | 53 | 2015 | 52 |
| 2015-12-31 | 2015 | 53 | 2015 | 52 |
| 2016-01-01 | 2015 | 53 | 2016 | 0 |
| 2016-01-02 | 2015 | 53 | 2016 | 0 |
| 2016-01-03 | 2015 | 53 | 2016 | 1 |
| 2016-01-04 | 2016 | 1 | 2016 | 1 |
| 2016-01-05 | 2016 | 1 | 2016 | 1 |
| 2016-01-06 | 2016 | 1 | 2016 | 1 |
| 2016-01-07 | 2016 | 1 | 2016 | 1 |
| 2016-01-08 | 2016 | 1 | 2016 | 1 |
| 2016-01-09 | 2016 | 1 | 2016 | 1 |
+------------+---------+---------+------+------+
在以下示例中，date_expression 属于星期日。EXTRACT 使用以星期日为第一天的周计算第一列，并使用以星期一为第一天的周计算第二列。
WITH table AS (SELECT DATE('2017-11-05') AS date)
SELECT
date,
EXTRACT(WEEK(SUNDAY) FROM date) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM date) AS week_monday FROM table;
+------------+-------------+-------------+
| date | week_sunday | week_monday |
+------------+-------------+-------------+
| 2017-11-05 | 45 | 44 |
+------------+-------------+-------------+
DATE
1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])
3. DATE(datetime_expression)
说明
- 使用表示年月日的 INT64 值构造 DATE。
- 从 TIMESTAMP 表达式中提取 DATE。该函数支持使用可选参数来指定时区。如果您未指定时区，则系统会使用默认时区（即世界协调时间 (UTC)）。
- 从 DATETIME 表达式中提取 DATE。
返回数据类型
DATE
示例
SELECT
DATE(2016, 12, 25) as date_ymd,
DATE(DATETIME "2016-12-25 23:59:59") as date_dt,
DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;
+------------+------------+------------+
| date_ymd | date_dt | date_tstz |
+------------+------------+------------+
| 2016-12-25 | 2016-12-25 | 2016-12-24 |
+------------+------------+------------+
DATE_ADD
DATE_ADD(date_expression, INTERVAL int64_expression date_part)
说明
将指定的时间间隔与 DATE 相加。
DATE_ADD 支持以下 date_part 值：
DAYWEEK。等于 7DAY。MONTHQUARTERYEAR
如果日期是（或接近）月份的最后一天，则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果生成月份的天数少于原始日期的天数，则生成日期作为下一月的最后一天。
返回数据类型
DATE
示例
SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;
+--------------------+
| five_days_later |
+--------------------+
| 2008-12-30 |
+--------------------+
DATE_SUB
DATE_SUB(date_expression, INTERVAL int64_expression date_part)
说明
从 DATE 中减去指定的日期间隔。
DATE_SUB 支持以下 date_part 值：
DAYWEEK。等于 7DAY。MONTHQUARTERYEAR
如果日期是（或接近）月份的最后一天，则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果生成月份的天数少于原始日期的天数，则生成日期作为下一月的最后一天。
返回数据类型
DATE
示例
SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;
+---------------+
| five_days_ago |
+---------------+
| 2008-12-20 |
+---------------+
DATE_DIFF
DATE_DIFF(date_expression_a, date_expression_b, date_part)
说明
返回两个 DATE 对象 (date_expression_a - date_expression_b) 之间完整指定的 date_part 间隔数。如果第一个 DATE 早于第二个，那么输出为负数。
DATE_DIFF 支持以下 date_part 值：
DAYWEEK：此日期部分从星期日开始算起。WEEK(<WEEKDAY>)：此日期部分从WEEKDAY开始算起。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：使用 ISO 8601 格式的周边界值。ISO 格式的周从星期一开始。MONTHQUARTERYEARISOYEAR：使用 ISO 8601 格式的周编号年份边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。
返回数据类型
INT64
示例
SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;
+-----------+
| days_diff |
+-----------+
| 559 |
+-----------+
SELECT
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;
+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1 | 1 |
+-----------+------------+
上面的示例展示了连续两天的 DATE_DIFF 结果。
日期部分为 WEEK 的 DATE_DIFF 会返回 1，因为 DATE_DIFF 会计算此日期范围内日期部分边界值的数量。每个 WEEK 都从星期日开始算起，因此在 2017-10-14 星期六与 2017-10-15 星期日之间有一个日期部分的边界值。
以下示例显示了不同年份中两个日期的 DATE_DIFF 结果。日期部分为 YEAR 的 DATE_DIFF 会返回 3，因为它会计算两个日期之间的公历年边界值数量。日期部分为 ISOYEAR 的 DATE_DIFF 会返回 2，因为第二个日期属于 ISO 格式的年份 2015。2015 日历年的第一个星期四是 2015-01-01，因此 ISO 格式的年份 2015 开始于上一个星期一（即 2014-12-29）。
SELECT
DATE_DIFF('2017-12-30', '2014-12-30', YEAR) AS year_diff,
DATE_DIFF('2017-12-30', '2014-12-30', ISOYEAR) AS isoyear_diff;
+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3 | 2 |
+-----------+--------------+
以下示例显示连续两天的 DATE_DIFF 结果。第一个日期是星期一，第二个日期是星期日。日期部分为 WEEK 的 DATE_DIFF 会返回 0，因为此日期部分使用从星期日开始算起的周。日期部分为 WEEK(MONDAY) 的 DATE_DIFF 会返回 1。日期部分为 ISOWEEK 的 DATE_DIFF 也会返回 1，因为 ISO 格式的周从星期一开始算起。
SELECT
DATE_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
DATE_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
DATE_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;
+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0 | 1 | 1 |
+-----------+-------------------+--------------+
DATE_TRUNC
DATE_TRUNC(date_expression, date_part)
说明
将日期截断至指定的粒度。
DATE_TRUNC 支持以下 date_part 值：
DAYWEEKWEEK(<WEEKDAY>)：将date_expression截断至上一个周边界值（一周开始于WEEKDAY）。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：将date_expression截断至上一个 ISO 8601 格式周的边界值。ISOWEEK从星期一开始算起。每个 ISO 格式年份的第一个ISOWEEK都包含对应公历年的第一个星期四。任何早于此日期的date_expression均会截断至上一个星期一。MONTHQUARTERYEARISOYEAR：将date_expression截断至上一个 ISO 8601 格式周编号年份的边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。
返回数据类型
DATE
示例
SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;
+------------+
| month |
+------------+
| 2008-12-01 |
+------------+
在以下示例中，原始日期属于星期日。由于 date_part 是 WEEK(MONDAY)，DATE_TRUNC 会返回上一个星期一的 DATE。
SELECT date AS original, DATE_TRUNC(date, WEEK(MONDAY)) AS truncated
FROM (SELECT DATE('2017-11-05') AS date);
+------------+------------+
| original | truncated |
+------------+------------+
| 2017-11-05 | 2017-10-30 |
+------------+------------+
在以下示例中，原始的 date_expression 属于公历 2015 年。不过，带有 ISOYEAR 日期部分的 DATE_TRUNC 会将 date_expression 截断至 ISO 格式年份（而非公历年）的开始。由于 2015 日历年的第一个星期四是 2015-01-01，ISO 格式的年份 2015 开始于上一个星期一（即 2014-12-29）。因此，date_expression 2015-06-15 之前的 ISO 格式年份边界值是 2014-12-29。
SELECT
DATE_TRUNC('2015-06-15', ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM DATE '2015-06-15') AS isoyear_number;
+------------------+----------------+
| isoyear_boundary | isoyear_number |
+------------------+----------------+
| 2014-12-29 | 2015 |
+------------------+----------------+
DATE_FROM_UNIX_DATE
DATE_FROM_UNIX_DATE(int64_expression)
说明
将 int64_expression 解释为从 1970-01-01 算起的天数。
返回数据类型
DATE
示例
SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;
+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25 |
+-----------------+
FORMAT_DATE
FORMAT_DATE(format_string, date_expr)
说明
根据指定的 format_string 设置 date_expr 格式。
如需查看此函数支持的格式元素列表，请参阅 DATE 支持的格式元素。
返回数据类型
STRING
示例
SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;
+------------+
| US_format |
+------------+
| 12/25/08 |
+------------+
SELECT FORMAT_DATE("%b-%d-%Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT FORMAT_DATE("%b %Y", DATE "2008-12-25") AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
PARSE_DATE
PARSE_DATE(format_string, date_string)
说明
将日期的字符串表示法转换为 DATE 对象。
format_string 包含用于定义 date_string 格式的格式元素。date_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 date_string 中每个元素的位置一致。
-- This works because elements on both sides match.
SELECT PARSE_DATE("%A %b %e %Y", "Thursday Dec 25 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATE("%Y %A %b %e", "Thursday Dec 25 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATE("%A %b %e", "Thursday Dec 25 2008")
-- This works because %F can find all matching elements in date_string.
SELECT PARSE_DATE("%F", "2000-12-30")
格式字符串完全支持大多数格式元素，但 %Q、%a、%A、%g、%G、%j、%u、%U、%V、%w、%W 除外。
使用 PARSE_DATE 时，请注意以下几点：
- 未指定的字段。任何未指定的字段在初始化时均以
1970-01-01为准。 - 不区分大小写的名称。
Monday、February等名称不区分大小写。 - 空格。格式字符串中的一个或多个连续空格与日期字符串中的零个或多个连续空格相匹配。此外，始终可在日期字符串中使用前导空格和尾随空格（即使格式字符串中不包含这些空格）。
- 格式优先顺序。如果两个（或更多）格式元素具有重叠信息（例如，
%F和%Y均对年份有影响），则最后一个格式元素通常会替换前面的所有元素。
返回数据类型
DATE
示例
SELECT PARSE_DATE("%x", "12/25/08") as parsed;
+------------+
| parsed |
+------------+
| 2008-12-25 |
+------------+
UNIX_DATE
UNIX_DATE(date_expression)
说明
返回从 1970-01-01 开始计算的天数。
返回数据类型
INT64
示例
SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;
+-----------------+
| days_from_epoch |
+-----------------+
| 14238 |
+-----------------+
DATE 支持的格式元素
除非另有说明，否则使用格式字符串的 DATE 函数均支持以下元素：
| 格式元素 | 说明 |
| %A | 星期几的全名。 |
| %a | 星期几的缩写名称。 |
| %B | 月份的全名。 |
| %b 或 %h | 月份的缩写名称。 |
| %C | 用十进制数 (00-99) 表示的年份的前两位数（年份除以 100 并只取整数）。 |
| %D | 采用 %m/%d/%y 格式的日期。 |
| %d | 用十进制数 (01-31) 表示的月份中的某一天。 |
| %e | 用十进制数 (1-31) 表示的月份中的某一天；个位数前面附加一个空格。 |
| %F | 采用 %Y-%m-%d 格式的日期。 |
| %G | 用十进制数表示的带有世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。 请注意，%G 和 %Y 可能会在公历年的边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 |
| %g | 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。请注意，％G 和 ％Y 可能会在公历年边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 |
| %j | 用十进制数 (001-366) 表示的一年中的某一天。 |
| %m | 用十进制数 (01-12) 表示的月份。 |
| %n | 换行符。 |
| %Q | 用十进制数 (1-4) 表示的季度。 |
| %t | 制表符。 |
| %U | 用十进制数 (00-53) 表示的一年中的周数（星期日算作一周的第一天）。 |
| %u | 用十进制数 (1-7) 表示的星期名称（星期一算作一周的第一天）。 |
| %V | 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数（星期一算作一周的第一天）。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年，则该周为第 1 周；否则为上一年的第 53 周，而下一周为新年的第 1 周。 |
| %W | 用十进制数 (00-53) 表示的一年的周数（星期一算作一周的第一天）。 |
| %w | 用十进制数 (0-6) 表示的星期名称（星期日算作一周的第一天）。 |
| %x | 采用 MM/DD/YY 格式表示的日期。 |
| %Y | 用十进制数表示的四位数的年份。 |
| %y | 用十进制数 (00-99) 表示的不带世纪信息的年份（可选用前导零）。可与 ％C 混合使用。如果未指定 %C，则 00-68 年属于 2000 年代，而 69-99 年属于 1900 年代。 |
| %E4Y | 用四个字符表示的年份 (0001 ... 9999)。注意：%Y 将生成尽量多的字符数以完整显示年份。 |
日期时间函数
BigQuery 支持以下 DATETIME 函数。
所有输出都会根据 ISO 8601 自动格式化，并使用 T 分隔日期和时间。
CURRENT_DATETIME
CURRENT_DATETIME([timezone])
说明
以 DATETIME 对象的形式返回当前时间。
此函数支持可选的 timezone 参数。如需了解如何指定时区，请参阅时区定义。
返回数据类型
DATETIME
示例
SELECT CURRENT_DATETIME() as now;
+----------------------------+
| now |
+----------------------------+
| 2016-05-19T10:38:47.046465 |
+----------------------------+
DATETIME
1. DATETIME(year, month, day, hour, minute, second)
2. DATETIME(date_expression[, time_expression])
3. DATETIME(timestamp_expression [, timezone])
说明
- 使用表示年、月、日、小时、分钟和秒钟的 INT64 值构造
DATETIME对象。 - 使用一个 DATE 对象和一个可选的 TIME 对象构造一个
DATETIME对象。 - 使用一个 TIMESTAMP 对象构造
DATETIME对象。它支持使用一个可选参数来指定时区。如果未指定时区，则使用默认时区世界协调时间 (UTC)。
返回数据类型
DATETIME
示例
SELECT
DATETIME(2008, 12, 25, 05, 30, 00) as datetime_ymdhms,
DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") as datetime_tstz;
+---------------------+---------------------+
| datetime_ymdhms | datetime_tstz |
+---------------------+---------------------+
| 2008-12-25T05:30:00 | 2008-12-24T21:30:00 |
+---------------------+---------------------+
EXTRACT
EXTRACT(part FROM datetime_expression)
说明
返回与提供的 datetime_expression 中指定的 part 相对应的值。
允许的 part 值包括：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYOFWEEKDAYDAYOFYEARWEEK：返回日期属于一年中的第几周，范围为 [0, 53]。一周从星期日开始，一年第一个星期日之前的日期属于第 0 周。WEEK(<WEEKDAY>)：返回datetime_expression属于一年中的第几周，范围为 [0, 53]。一周从WEEKDAY开始。 一年中第一个WEEKDAY之前的datetime属于第 0 周。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：以 ISO 8601 格式返回datetime_expression属于一年之中的第几周。ISOWEEK从星期一开始算起。返回值的范围为 [1, 53]。每个 ISO 格式年份的第一个ISOWEEK从公历年第一个星期四之前的星期一开始算起。MONTHQUARTERYEARISOYEAR：以 ISO 8601 格式返回周编号年份，即包含date_expression所属一周的星期四的公历年）。DATETIME
返回的值会截掉低位时间段。例如，提取秒数时，EXTRACT 会截掉毫秒值和微秒值。
返回数据类型
INT64，但以下情况除外：
- 如果
part为DATE，则返回DATE对象。 - 如果
part为TIME，则返回TIME对象。
示例
下例中，EXTRACT 返回了与 HOUR 时间部分相对应的值。
SELECT EXTRACT(HOUR FROM DATETIME(2008, 12, 25, 15, 30, 00)) as hour;
+------------------+
| hour |
+------------------+
| 15 |
+------------------+
在以下示例中，EXTRACT 返回了与日期时间列中不同时间部分对应的值。
WITH Datetimes AS (
SELECT DATETIME '2005-01-03 12:34:56' AS datetime UNION ALL
SELECT DATETIME '2007-12-31' UNION ALL
SELECT DATETIME '2009-01-01' UNION ALL
SELECT DATETIME '2009-12-31' UNION ALL
SELECT DATETIME '2017-01-02' UNION ALL
SELECT DATETIME '2017-05-26'
)
SELECT
datetime,
EXTRACT(ISOYEAR FROM datetime) AS isoyear,
EXTRACT(ISOWEEK FROM datetime) AS isoweek,
EXTRACT(YEAR FROM datetime) AS year,
EXTRACT(WEEK FROM datetime) AS week
FROM Datetimes
ORDER BY datetime;
+---------------------+---------+---------+------+------+
| datetime | isoyear | isoweek | year | week |
+---------------------+---------+---------+------+------+
| 2005-01-03T12:34:56 | 2005 | 1 | 2005 | 1 |
| 2007-12-31T00:00:00 | 2008 | 1 | 2007 | 52 |
| 2009-01-01T00:00:00 | 2009 | 1 | 2009 | 0 |
| 2009-12-31T00:00:00 | 2009 | 53 | 2009 | 52 |
| 2017-01-02T00:00:00 | 2017 | 1 | 2017 | 1 |
| 2017-05-26T00:00:00 | 2017 | 21 | 2017 | 21 |
+---------------------+---------+---------+------+------+
在以下示例中，datetime_expression 属于星期日。EXTRACT 使用从星期日开始的周数计算第一列，并使用从星期一开始的周数计算第二列。
WITH table AS (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime)
SELECT
datetime,
EXTRACT(WEEK(SUNDAY) FROM datetime) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM datetime) AS week_monday
FROM table;
+---------------------+-------------+---------------+
| datetime | week_sunday | week_monday |
+---------------------+-------------+---------------+
| 2017-11-05T00:00:00 | 45 | 44 |
+---------------------+-------------+---------------+
DATETIME_ADD
DATETIME_ADD(datetime_expression, INTERVAL int64_expression part)
说明
向 DATETIME 对象添加 int64_expression 个单位的 part。
DATETIME_ADD 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEK。等于 7DAY。MONTHQUARTERYEAR
如果日期是（或接近）月份的最后一天，则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果由此所得到的月份的天数少于原始 DATETIME 的天数，则所得到的日期作为下一月的最后一天。
返回数据类型
DATETIME
示例
SELECT
DATETIME "2008-12-25 15:30:00" as original_date,
DATETIME_ADD(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as later;
+-----------------------------+------------------------+
| original_date | later |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T15:40:00 |
+-----------------------------+------------------------+
DATETIME_SUB
DATETIME_SUB(datetime_expression, INTERVAL int64_expression part)
说明
从 DATETIME 中减去 int64_expression 个单位的 part。
DATETIME_SUB 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEK。等于 7DAY。MONTHQUARTERYEAR
如果日期是（或接近）月份的最后一天，则需要特殊处理 MONTH、QUARTER 和 YEAR 部分。如果生成月份的天数少于原始 DATETIME 的天数，则生成日期将是下一月的最后一天。
返回数据类型
DATETIME
示例
SELECT
DATETIME "2008-12-25 15:30:00" as original_date,
DATETIME_SUB(DATETIME "2008-12-25 15:30:00", INTERVAL 10 MINUTE) as earlier;
+-----------------------------+------------------------+
| original_date | earlier |
+-----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T15:20:00 |
+-----------------------------+------------------------+
DATETIME_DIFF
DATETIME_DIFF(datetime_expression_a, datetime_expression_b, part)
说明
返回两个 DATETIME 对象 (datetime_expression_a - datetime_expression_b) 之间完整指定的 part 间隔数。如果第一个 DATETIME 早于第二个，那么输出为负数。如果计算结果超过结果类型的最大值（例如两个 DATETIME 对象之间的微秒数之差超过一个 INT64 最大值），则会引发一个错误。
DATETIME_DIFF 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEK：此日期部分从星期日开始。WEEK(<WEEKDAY>)：此日期部分从WEEKDAY开始。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：使用 ISO 8601 格式的周边界值。ISO 格式的周从星期一开始。MONTHQUARTERYEARISOYEAR：使用 ISO 8601 格式的周编号年份边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。
返回数据类型
INT64
示例
SELECT
DATETIME "2010-07-07 10:20:00" as first_datetime,
DATETIME "2008-12-25 15:30:00" as second_datetime,
DATETIME_DIFF(DATETIME "2010-07-07 10:20:00",
DATETIME "2008-12-25 15:30:00", DAY) as difference;
+----------------------------+------------------------+------------------------+
| first_datetime | second_datetime | difference |
+----------------------------+------------------------+------------------------+
| 2010-07-07T10:20:00 | 2008-12-25T15:30:00 | 559 |
+----------------------------+------------------------+------------------------+
SELECT
DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
DATETIME '2017-10-14 00:00:00', DAY) as days_diff,
DATETIME_DIFF(DATETIME '2017-10-15 00:00:00',
DATETIME '2017-10-14 00:00:00', WEEK) as weeks_diff;
+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1 | 1 |
+-----------+------------+
上面的示例显示了相隔 24 小时的两个 DATETIME 的 DATETIME_DIFF 结果。对部分 WEEK 执行 DATETIME_DIFF 运算会返回 1，因为 DATETIME_DIFF 会对此 DATETIME 范围内的部分边界值数量进行计数。每个 WEEK 都从星期日开始，因此在 2017-10-14 00:00:00 星期六与 2017-10-15 00:00:00 星期日之间有一个部分的边界值。
以下示例显示了不同年份中两个日期的 DATETIME_DIFF 结果。对日期部分 YEAR 执行 DATETIME_DIFF 运算会返回 3，因为它会计算两个 DATETIME 之间的公历年边界值数量。对日期部分 ISOYEAR 执行 DATETIME_DIFF 运算会返回 2，因为第二个 DATETIME 属于 ISO 格式的年份 2015。公历年 2015 年的第一个星期四是 2015-01-01，因此 ISO 格式年份 2015 年开始于上一个星期一，也就是 2014-12-29。
SELECT
DATETIME_DIFF('2017-12-30 00:00:00',
'2014-12-30 00:00:00', YEAR) AS year_diff,
DATETIME_DIFF('2017-12-30 00:00:00',
'2014-12-30 00:00:00', ISOYEAR) AS isoyear_diff;
+-----------+--------------+
| year_diff | isoyear_diff |
+-----------+--------------+
| 3 | 2 |
+-----------+--------------+
以下示例显示了连续两天的 DATETIME_DIFF 结果。第一个日期是星期一，第二个日期是星期日。对日期部分 WEEK 执行 DATETIME_DIFF 运算会返回 0，因为此时间部分使用从星期日开始的周。对日期部分 WEEK(MONDAY) 执行 DATETIME_DIFF 运算会返回 1。对日期部分 ISOWEEK 执行 DATETIME_DIFF 运算也会返回 1，因为 ISO 格式的周从星期一开始。
SELECT
DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK) AS week_diff,
DATETIME_DIFF('2017-12-18', '2017-12-17', WEEK(MONDAY)) AS week_weekday_diff,
DATETIME_DIFF('2017-12-18', '2017-12-17', ISOWEEK) AS isoweek_diff;
+-----------+-------------------+--------------+
| week_diff | week_weekday_diff | isoweek_diff |
+-----------+-------------------+--------------+
| 0 | 1 | 1 |
+-----------+-------------------+--------------+
DATETIME_TRUNC
DATETIME_TRUNC(datetime_expression, part)
说明
将 DATETIME 对象截断到 part 的粒度。
DATETIME_TRUNC 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEKWEEK(<WEEKDAY>)：将datetime_expression截断至上一个周边界值（一周开始于WEEKDAY）。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：将datetime_expression截断至上一个 ISO 8601 格式周的边界值。ISOWEEK从星期一开始算起。每个 ISO 格式年份的第一个ISOWEEK都包含对应公历年的第一个星期四。任何早于此日期的date_expression均会截断至上一个星期一。MONTHQUARTERYEARISOYEAR：将datetime_expression截断至上一个 ISO 8601 格式周编号年份的边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。
返回数据类型
DATETIME
示例
SELECT
DATETIME "2008-12-25 15:30:00" as original,
DATETIME_TRUNC(DATETIME "2008-12-25 15:30:00", DAY) as truncated;
+----------------------------+------------------------+
| original | truncated |
+----------------------------+------------------------+
| 2008-12-25T15:30:00 | 2008-12-25T00:00:00 |
+----------------------------+------------------------+
在以下示例中，原始 DATETIME 是星期日。由于 part 是 WEEK(MONDAY)，DATE_TRUNC 会返回上一个星期一的 DATETIME。
SELECT
datetime AS original,
DATETIME_TRUNC(datetime, WEEK(MONDAY)) AS truncated
FROM (SELECT DATETIME(TIMESTAMP "2017-11-05 00:00:00+00", "UTC") AS datetime);
+---------------------+---------------------+
| original | truncated |
+---------------------+---------------------+
| 2017-11-05T00:00:00 | 2017-10-30T00:00:00 |
+---------------------+---------------------+
在以下示例中，原始的 datetime_expression 属于公历 2015 年。不过，带有 ISOYEAR 日期部分的 DATETIME_TRUNC 会将 datetime_expression 截断至 ISO 格式年份（而非公历年）的开始。公历年 2015 年的第一个星期四是 2015-01-01，因此 ISO 格式年份 2015 年开始于上一个星期一，也就是 2014-12-29。因此，datetime_expression 2015-06-15 00:00:00 之前的 ISO 格式年份边界值是 2014-12-29。
SELECT
DATETIME_TRUNC('2015-06-15 00:00:00', ISOYEAR) AS isoyear_boundary,
EXTRACT(ISOYEAR FROM DATETIME '2015-06-15 00:00:00') AS isoyear_number;
+---------------------+----------------+
| isoyear_boundary | isoyear_number |
+---------------------+----------------+
| 2014-12-29T00:00:00 | 2015 |
+---------------------+----------------+
FORMAT_DATETIME
FORMAT_DATETIME(format_string, datetime_expression)
说明
根据指定的 format_string 设置 DATETIME 对象的格式。如需查看此函数支持的格式元素列表，请参阅 DATETIME 支持的格式元素。
返回数据类型
STRING
示例
SELECT
FORMAT_DATETIME("%c", DATETIME "2008-12-25 15:30:00")
AS formatted;
+--------------------------+
| formatted |
+--------------------------+
| Thu Dec 25 15:30:00 2008 |
+--------------------------+
SELECT
FORMAT_DATETIME("%b-%d-%Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
+-------------+
| formatted |
+-------------+
| Dec-25-2008 |
+-------------+
SELECT
FORMAT_DATETIME("%b %Y", DATETIME "2008-12-25 15:30:00")
AS formatted;
+-------------+
| formatted |
+-------------+
| Dec 2008 |
+-------------+
PARSE_DATETIME
PARSE_DATETIME(format_string, datetime_string)
说明
将日期时间的字符串表示形式转换为 DATETIME 对象。
format_string 包含用于定义 datetime_string 格式的格式元素。datetime_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 datetime_string 中每个元素的位置一致。
-- This works because elements on both sides match.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because the year element is in different locations.
SELECT PARSE_DATETIME("%a %b %e %Y %I:%M:%S", "Thu Dec 25 07:30:00 2008")
-- This doesn't work because one of the year elements is missing.
SELECT PARSE_DATETIME("%a %b %e %I:%M:%S %Y", "Thu Dec 25 07:30:00 2008")
-- This works because %c can find all matching elements in datetime_string.
SELECT PARSE_DATETIME("%c", "Thu Dec 25 07:30:00 2008")
格式字符串完全支持大多数格式元素，但 %Q、%a、%A、%g、%G、%j、%P、%u、%U、%V、%w 和 %W 除外。
PARSE_DATETIME 根据以下规则解析 string：
- 未指定的字段。任何未指定的字段在初始化时均以
1970-01-01 00:00:00.0为准。例如，如果未指定年份，则默认为1970。 - 名称不区分大小写。名称（例如
Monday和February等）不区分大小写。 - 空格。格式字符串中的一个或多个连续空格与
DATETIME字符串中的零个或更多连续空格相匹配。DATETIME字符串始终支持使用前导空格和尾随空格（即使这些空格不在格式字符串中）。 - 格式优先级。如果两个或更多格式元素的信息出现重叠，则最后一个元素通常会替换前面的所有元素，但也存在一些例外情况。例如，
%F和%Y均对年份有影响，因此前面的元素会替换后面的元素。请参阅 DATETIME 支持的格式元素中关于%s、%C和%y的说明。
返回数据类型
DATETIME
示例
以下示例将 STRING 字面量解析为 DATETIME。
SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '1998-10-18 13:45:55') AS datetime;
+---------------------+
| datetime |
+---------------------+
| 1998-10-18T13:45:55 |
+---------------------+
SELECT PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', '8/30/2018 2:23:38 PM') AS datetime
+---------------------+
| datetime |
+---------------------+
| 2018-08-30T14:23:38 |
+---------------------+
以下示例将包含日期（以自然语言格式显示）的 STRING 字面量解析为 DATETIME。
SELECT PARSE_DATETIME('%A, %B %e, %Y','Wednesday, December 19, 2018')
AS datetime;
+---------------------+
| datetime |
+---------------------+
| 2018-12-19T00:00:00 |
+---------------------+
DATETIME 支持的格式元素
除非另有说明，否则使用格式字符串的 DATETIME 函数支持以下元素：
| 格式元素 | 说明 |
| %A | 星期几的全名。 |
| %a | 星期几的缩写名称。 |
| %B | 月份的全名。 |
| %b 或 %h | 月份的缩写名称。 |
| %C | 用十进制数 (00-99) 表示的世纪（年份除以 100 并截断为整数）。 |
| %c | 表示日期和时间。 |
| %D | 采用 %m/%d/%y 格式的日期。 |
| %d | 用十进制数 (01-31) 表示的月份中的某一天。 |
| %e | 用十进制数 (1-31) 表示的月份中的某一天；个位数前面附加一个空格。 |
| %F | 采用 %Y-%m-%d 格式的日期。 |
| %G | 用十进制数表示的带有世纪信息的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。 请注意，%G 和 %Y 可能会在公历年的边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 |
| %g | 用十进制数 (00-99) 表示的两位数的 ISO 8601 格式年份。每个 ISO 格式年份开始于公历年的第一个星期四之前的星期一。请注意，％G 和 ％Y 可能会在公历年边界值附近产生不同的结果，其中公历年和 ISO 格式年份可能会有所不同。 |
| %H | 用十进制数 (00-23) 表示的小时数（24 小时制）。 |
| %I | 用十进制数 (01-12) 表示的小时数（12 小时制）。 |
| %j | 用十进制数 (001-366) 表示的一年中的某一天。 |
| %k | 用十进制数 (0-23) 表示的小时数（24 小时制）；个位数前面附加一个空格。 |
| %l | 用十进制数 (1-12) 表示的小时数（12 小时制）；个位数前面附加一个空格。 |
| %M | 用十进制数 (00-59) 表示的分钟数。 |
| %m | 用十进制数 (01-12) 表示的月份。 |
| %n | 换行符。 |
| %P | am 或 pm。 |
| %p | AM 或 PM。 |
| %Q | 用十进制数 (1-4) 表示的季度。 |
| %R | 采用 %H:%M 格式表示的时间。 |
| %r | 使用 AM/PM 标记法表示的 12 小时制的时间。 |
| %S | 用十进制数 (00-60) 表示的秒数。 |
| %s | 从 1970-01-01 00:00:00 开始计算的秒数。无论 %s 出现在字符串的哪个位置，始终替换所有其他格式元素。如果出现多个 %s 元素，则以最后一个元素为准。 |
| %T | 采用 %H:%M:%S 格式表示的时间。 |
| %t | 制表符。 |
| %U | 用十进制数 (00-53) 表示的一年中的周数（星期日算作一周的第一天）。 |
| %u | 用十进制数 (1-7) 表示的星期名称（星期一算作一周的第一天）。 |
| %V | 用十进制数 (01-53) 表示的一年中的 ISO 8601 格式周数（星期一算作一周的第一天）。如果包含 1 月 1 日的那一周有 4 天或超过 4 天属于新的一年，则该周为第 1 周；否则为上一年的第 53 周，而下一周为新年的第 1 周。 |
| %W | 用十进制数 (00-53) 表示的一年的周数（星期一算作一周的第一天）。 |
| %w | 用十进制数 (0-6) 表示的星期名称（星期日算作一周的第一天）。 |
| %X | 采用 HH:MM:SS 格式表示的时间。 |
| %x | 采用 MM/DD/YY 格式表示的日期。 |
| %Y | 用十进制数表示的四位数的年份。 |
| %y | 用十进制数 (00-99) 表示的不带世纪信息的年份（可选用前导零）。可与 ％C 混合使用。如果未指定 ％C，则 00-68 年属于 2000 年代，而 69-99 年属于 1900 年代。 |
| %% | 单个 % 字符。 |
| %E#S | 具有 # 位小数精度的秒数。 |
| %E*S | 具有完整小数精度的秒数（字面量“*”）。 |
| %E4Y | 四个字符的年份 (0001 ... 9999)。注意：%Y 将生成尽量多的字符数以完整显示年份。 |
时间函数
BigQuery 支持以下 TIME 函数。
CURRENT_TIME
CURRENT_TIME([timezone])
说明
以 TIME 对象的形式返回当前时间。
此函数支持可选的 timezone 参数。如需了解如何指定时区，请参阅时区定义。
返回数据类型
TIME
示例
SELECT CURRENT_TIME() as now;
+----------------------------+
| now |
+----------------------------+
| 15:31:38.776361 |
+----------------------------+
TIME
1. TIME(hour, minute, second)
2. TIME(timestamp, [timezone])
3. TIME(datetime)
说明
- 使用表示时、分和秒的
INT64值构建TIME对象。 - 使用一个
TIMESTAMP对象构造一个TIME对象。它支持使用一个可选参数来指定时区。如果未指定时区，则使用默认时区，即世界协调时间 (UTC)。 - 使用一个
DATETIME对象构建一个TIME对象。
返回数据类型
TIME
示例
SELECT
TIME(15, 30, 00) as time_hms,
TIME(TIMESTAMP "2008-12-25 15:30:00+08", "America/Los_Angeles") as time_tstz;
+----------+-----------+
| time_hms | time_tstz |
+----------+-----------+
| 15:30:00 | 23:30:00 |
+----------+-----------+
SELECT TIME(DATETIME "2008-12-25 15:30:00.000000") AS time_dt;
+----------+
| time_dt |
+----------+
| 15:30:00 |
+----------+
EXTRACT
EXTRACT(part FROM time_expression)
说明
返回与提供的 time_expression 中指定的 part 相对应的值。
允许的 part 值包括：
MICROSECONDMILLISECONDSECONDMINUTEHOUR
返回的值会掉低位时间段。例如，提取秒数时，EXTRACT 会截掉毫秒值和微秒值。
返回数据类型
INT64
示例
下例中，EXTRACT 返回了与 HOUR 时间部分相对应的值。
SELECT EXTRACT(HOUR FROM TIME "15:30:00") as hour;
+------------------+
| hour |
+------------------+
| 15 |
+------------------+
TIME_ADD
TIME_ADD(time_expression, INTERVAL int64_expression part)
说明
向 TIME 对象添加 int64_expression 个单位的 part。
TIME_ADD 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR
当值超出 00:00:00 到 24:00:00 的边界值时，此函数会自动调整。例如，如果您向 23:30:00 添加了 1 小时，返回值则为 00:30:00。
返回数据类型
TIME
示例
SELECT
TIME "15:30:00" as original_time,
TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;
+-----------------------------+------------------------+
| original_time | later |
+-----------------------------+------------------------+
| 15:30:00 | 15:40:00 |
+-----------------------------+------------------------+
TIME_SUB
TIME_SUB(time_expression, INTERVAL int64_expression part)
说明
从 TIME 对象中减去 int64_expression 个单位的 part。
TIME_SUB 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR
当值超出 00:00:00 到 24:00:00 的边界值时，此函数会自动调整。例如，如果您从 00:30:00 减去了 1 小时，返回值则为 23:30:00。
返回数据类型
TIME
示例
SELECT
TIME "15:30:00" as original_date,
TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;
+-----------------------------+------------------------+
| original_date | earlier |
+-----------------------------+------------------------+
| 15:30:00 | 15:20:00 |
+-----------------------------+------------------------+
TIME_DIFF
TIME_DIFF(time_expression_a, time_expression_b, part)
说明
返回两个 TIME 对象 (time_expression_a - time_expression_b) 之间完整指定的 part 间隔数。如果第一个 TIME 早于第二个，那么输出为负数。如果计算结果超过结果类型的最大值（例如两个 TIME 对象之间的微秒数之差超过一个 INT64 最大值），则会引发一个错误。
TIME_DIFF 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR
返回数据类型
INT64
示例
SELECT
TIME "15:30:00" as first_time,
TIME "14:35:00" as second_time,
TIME_DIFF(TIME "15:30:00", TIME "14:35:00", MINUTE) as difference;
+----------------------------+------------------------+------------------------+
| first_time | second_time | difference |
+----------------------------+------------------------+------------------------+
| 15:30:00 | 14:35:00 | 55 |
+----------------------------+------------------------+------------------------+
TIME_TRUNC
TIME_TRUNC(time_expression, part)
说明
将 TIME 对象截断到 part 的粒度。
TIME_TRUNC 支持以下 part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR
返回数据类型
TIME
示例
SELECT
TIME "15:30:00" as original,
TIME_TRUNC(TIME "15:30:00", HOUR) as truncated;
+----------------------------+------------------------+
| original | truncated |
+----------------------------+------------------------+
| 15:30:00 | 15:00:00 |
+----------------------------+------------------------+
FORMAT_TIME
FORMAT_TIME(format_string, time_object)
说明
根据指定的 format_string 设置 TIME 对象的格式。如需查看此函数支持的格式元素列表，请参阅 TIME 支持的格式元素。
返回数据类型
STRING
示例
SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;
+----------------+
| formatted_time |
+----------------+
| 15:30 |
+----------------+
PARSE_TIME
PARSE_TIME(format_string, time_string)
说明
将时间的字符串表示形式转换为 TIME 对象。
format_string 包含用于定义 time_string 格式的格式元素。time_string 中的每个元素都必须在 format_string 中有对应的元素。format_string 中每个元素的位置必须与 time_string 中每个元素的位置一致。
-- This works because elements on both sides match.
SELECT PARSE_TIME("%I:%M:%S", "07:30:00")
-- This doesn't work because the seconds element is in different locations.
SELECT PARSE_TIME("%S:%I:%M", "07:30:00")
-- This doesn't work because one of the seconds elements is missing.
SELECT PARSE_TIME("%I:%M", "07:30:00")
-- This works because %T can find all matching elements in time_string.
SELECT PARSE_TIME("%T", "07:30:00")
格式字符串完全支持除 %P 之外的大多数格式元素。
使用 PARSE_TIME 时，请注意以下几点：
- 未指定的字段。任何未指定的字段在初始化时均以
00:00:00.0为准。例如，如果未指定seconds，则默认为00，依此类推。 - 空格。格式字符串中的一个或多个连续空格与
TIME字符串中的零个或更多连续空格相匹配。此外，TIME字符串始终支持使用前导空格和尾随空格（即使这些空格不在格式字符串中）。 - 格式优先级。如果两个（或更多）格式元素的信息出现重叠，则最后一个元素通常会替换前面的所有元素。
返回数据类型
TIME
示例
SELECT PARSE_TIME("%H", "15") as parsed_time;
+-------------+
| parsed_time |
+-------------+
| 15:00:00 |
+-------------+
SELECT PARSE_TIME('%I:%M:%S %p', '2:23:38 PM') AS parsed_time
+-------------+
| parsed_time |
+-------------+
| 14:23:38 |
+-------------+
TIME 支持的格式元素
除非另有说明，否则使用格式字符串的 TIME 函数支持以下元素：
| 格式元素 | 说明 |
| %H | 用十进制数 (00-23) 表示的小时数（24 小时制）。 |
| %I | 用十进制数 (01-12) 表示的小时数（12 小时制）。 |
| %j | 用十进制数 (001-366) 表示的一年中的某一天。 |
| %k | 用十进制数 (0-23) 表示的小时数（24 小时制）；个位数前面附加一个空格。 |
| %l | 用十进制数 (1-12) 表示的小时数（12 小时制）；个位数前面附加一个空格。 |
| %M | 用十进制数 (00-59) 表示的分钟数。 |
| %n | 换行符。 |
| %P | am 或 pm。 |
| %p | AM 或 PM。 |
| %R | 采用 %H:%M 格式表示的时间。 |
| %r | 使用 AM/PM 标记法表示的 12 小时制的时间。 |
| %S | 用十进制数 (00-60) 表示的秒数。 |
| %T | 采用 %H:%M:%S 格式表示的时间。 |
| %t | 制表符。 |
| %X | 采用 HH:MM:SS 格式表示的时间。 |
| %% | 单个 % 字符。 |
| %E#S | 具有 # 位小数精度的秒数。 |
| %E*S | 具有完整小数精度的秒数（字面量“*”）。 |
时间戳函数
BigQuery 支持以下 TIMESTAMP 函数。
注意：如果发生溢出，这些函数会返回运行时错误；结果值受到所定义的日期和时间戳最小值/最大值限制。
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
说明
英文括号是可选项。此函数处理闰秒的方式，是将闰秒均匀分布于其前后共 20 个小时的时段中。CURRENT_TIMESTAMP() 生成连续、非模糊的 TIMESTAMP 值，每分钟刚好 60 秒，并且在闰秒时不会重复该值。
支持的输入类型
不适用
结果数据类型
TIMESTAMP
示例
SELECT CURRENT_TIMESTAMP() as now;
+--------------------------------+
| now |
+--------------------------------+
| 2020-06-02 23:57:12.120174 UTC |
+--------------------------------+
EXTRACT
EXTRACT(part FROM timestamp_expression [AT TIME ZONE timezone])
说明
返回与提供的 timestamp_expression 中指定的 part 相对应的值。 此函数支持 timezone 可选参数。如需了解如何指定时区，请参阅时区定义。
允许的 part 值包括：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYOFWEEKDAYDAYOFYEARWEEK：返回日期属于一年中的第几周，范围为 [0, 53]。一周从星期日开始，一年第一个星期日之前的日期属于第 0 周。WEEK(<WEEKDAY>)：返回timestamp_expression属于一年中的第几周，范围为 [0, 53]。一周从WEEKDAY开始。一年之中第一个WEEKDAY之前的datetime属于第 0 周。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：以 ISO 8601 格式返回datetime_expression属于一年之中的第几周。ISOWEEK从星期一开始算起。返回值的范围为 [1, 53]。每个 ISO 格式年份的第一个ISOWEEK从公历年第一个星期四之前的星期一开始算起。MONTHQUARTERYEARISOYEAR：以 ISO 8601 格式返回周编号年份，即包含date_expression所属一周的星期四的公历年）。DATEDATETIMETIME
返回的值会截掉低位时间段。例如，提取秒数时，EXTRACT 会截掉毫秒值和微秒值。
返回数据类型
INT64，但以下情况除外：
part为DATE，则返回DATE对象。part为DATETIME，则返回DATETIME对象。part为TIME，则返回TIME对象。
示例
在以下示例中，EXTRACT 返回了 DAY 时间部分对应的值。
WITH Input AS (SELECT TIMESTAMP("2008-12-25 05:30:00+00") AS timestamp_value)
SELECT
EXTRACT(DAY FROM timestamp_value AT TIME ZONE "UTC") AS the_day_utc,
EXTRACT(DAY FROM timestamp_value AT TIME ZONE "America/Los_Angeles") AS the_day_california
FROM Input
+-------------+--------------------+
| the_day_utc | the_day_california |
+-------------+--------------------+
| 25 | 24 |
+-------------+--------------------+
在以下示例中，EXTRACT 返回了与时间戳的一列中不同的时间部分相对应的值。
WITH Timestamps AS (
SELECT TIMESTAMP("2005-01-03 12:34:56+00") AS timestamp_value UNION ALL
SELECT TIMESTAMP("2007-12-31 12:00:00+00") UNION ALL
SELECT TIMESTAMP("2009-01-01 12:00:00+00") UNION ALL
SELECT TIMESTAMP("2009-12-31 12:00:00+00") UNION ALL
SELECT TIMESTAMP("2017-01-02 12:00:00+00") UNION ALL
SELECT TIMESTAMP("2017-05-26 12:00:00+00")
)
SELECT
timestamp_value,
EXTRACT(ISOYEAR FROM timestamp_value) AS isoyear,
EXTRACT(ISOWEEK FROM timestamp_value) AS isoweek,
EXTRACT(YEAR FROM timestamp_value) AS year,
EXTRACT(WEEK FROM timestamp_value) AS week
FROM Timestamps
ORDER BY timestamp_value;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+---------+---------+------+------+
| timestamp_value | isoyear | isoweek | year | week |
+-------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56 UTC | 2005 | 1 | 2005 | 1 |
| 2007-12-31 12:00:00 UTC | 2008 | 1 | 2007 | 52 |
| 2009-01-01 12:00:00 UTC | 2009 | 1 | 2009 | 0 |
| 2009-12-31 12:00:00 UTC | 2009 | 53 | 2009 | 52 |
| 2017-01-02 12:00:00 UTC | 2017 | 1 | 2017 | 1 |
| 2017-05-26 12:00:00 UTC | 2017 | 21 | 2017 | 21 |
+-------------------------+---------+---------+------+------+
在以下示例中，timestamp_expression 属于星期一。EXTRACT 使用从星期日开始的周数计算第一列，并使用从星期一开始的周数计算第二列。
WITH table AS (SELECT TIMESTAMP("2017-11-05 00:00:00+00") AS timestamp_value)
SELECT
timestamp_value,
EXTRACT(WEEK(SUNDAY) FROM timestamp_value) AS week_sunday,
EXTRACT(WEEK(MONDAY) FROM timestamp_value) AS week_monday
FROM table;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------+---------------+
| timestamp_value | week_sunday | week_monday |
+-------------------------+-------------+---------------+
| 2017-11-05 00:00:00 UTC | 45 | 44 |
+-------------------------+-------------+---------------+
STRING
STRING(timestamp_expression[, timezone])
说明
将 timestamp_expression 转换为 STRING 数据类型。支持使用可选参数来指定时区。如需了解如何指定时区，请参阅时区定义。
返回数据类型
STRING
示例
SELECT STRING(TIMESTAMP "2008-12-25 15:30:00+00", "UTC") AS string;
+-------------------------------+
| string |
+-------------------------------+
| 2008-12-25 15:30:00+00 |
+-------------------------------+
TIMESTAMP
TIMESTAMP(string_expression[, timezone])
TIMESTAMP(date_expression[, timezone])
TIMESTAMP(datetime_expression[, timezone])
说明
string_expression[, timezone]：将 STRING 表达式转换为 TIMESTAMP 数据类型。string_expression必须包含一个时间戳字面量。如果string_expression在时间戳字面量中包含时区，请勿明确包含timezone参数。date_expression[, timezone]：将 DATE 对象转换为 TIMESTAMP 数据类型。datetime_expression[, timezone]：将 DATETIME 对象转换为 TIMESTAMP 数据类型。
此函数支持使用可选参数来指定时区。如果您未指定时区，则系统会使用默认时区（即世界协调时间 (UTC)）。
返回数据类型
TIMESTAMP
示例
SELECT TIMESTAMP("2008-12-25 15:30:00+00") AS timestamp_str;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_str |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS timestamp_str;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_str |
+-------------------------+
| 2008-12-25 23:30:00 UTC |
+-------------------------+
SELECT TIMESTAMP("2008-12-25 15:30:00 UTC") AS timestamp_str;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_str |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+
SELECT TIMESTAMP(DATETIME "2008-12-25 15:30:00") AS timestamp_datetime;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_str |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+
SELECT TIMESTAMP(DATE "2008-12-25") AS timestamp_date;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+
| timestamp_str |
+-------------------------+
| 2008-12-25 00:00:00 UTC |
+-------------------------+
TIMESTAMP_ADD
TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)
说明
向时间戳中添加 int64_expression 单位的 date_part，与时区无关。
TIMESTAMP_ADD 支持以下 date_part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR。等于 60MINUTE。DAY。等于 24HOUR。
返回数据类型
TIMESTAMP
示例
SELECT
TIMESTAMP("2008-12-25 15:30:00+00") AS original,
TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS later;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+
| original | later |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:40:00 UTC |
+-------------------------+-------------------------+
TIMESTAMP_SUB
TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)
说明
从时间戳中减去 int64_expression 个单位的 date_part，与时区无关。
TIMESTAMP_SUB 支持以下 date_part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR。等于 60MINUTE。DAY。等于 24HOUR。
返回数据类型
TIMESTAMP
示例
SELECT
TIMESTAMP("2008-12-25 15:30:00+00") AS original,
TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00+00", INTERVAL 10 MINUTE) AS earlier;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+
| original | earlier |
+-------------------------+-------------------------+
| 2008-12-25 15:30:00 UTC | 2008-12-25 15:20:00 UTC |
+-------------------------+-------------------------+
TIMESTAMP_DIFF
TIMESTAMP_DIFF(timestamp_expression_a, timestamp_expression_b, date_part)
说明
返回两个 TIMESTAMP 对象 (timestamp_expression_a - timestamp_expression_b) 之间完整指定的 date_part 间隔数。如果第一个 TIMESTAMP 早于第二个，那么输出为负数。如果计算结果超过结果类型的最大值（例如两个 TIMESTAMP 对象之间的微秒数之差超过一个 INT64 最大值），则会引发一个错误。
TIMESTAMP_DIFF 支持以下 date_part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOUR。等于 60MINUTE。DAY。等于 24HOUR。
返回数据类型
INT64
示例
SELECT
TIMESTAMP("2010-07-07 10:20:00+00") AS later_timestamp,
TIMESTAMP("2008-12-25 15:30:00+00") AS earlier_timestamp,
TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00+00", TIMESTAMP "2008-12-25 15:30:00+00", HOUR) AS hours;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-------------------------+-------+
| later_timestamp | earlier_timestamp | hours |
+-------------------------+-------------------------+-------+
| 2010-07-07 10:20:00 UTC | 2008-12-25 15:30:00 UTC | 13410 |
+-------------------------+-------------------------+-------+
在以下示例中，第一个时间戳出现在第二个时间戳之前，从而产生负输出。
SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);
+---------------+
| negative_diff |
+---------------+
| -61 |
+---------------+
在本示例中，结果为 0，因为仅包含整个指定的 HOUR 间隔数。
SELECT TIMESTAMP_DIFF("2001-02-01 01:00:00", "2001-02-01 00:00:01", HOUR)
+---------------+
| negative_diff |
+---------------+
| 0 |
+---------------+
TIMESTAMP_TRUNC
TIMESTAMP_TRUNC(timestamp_expression, date_part[, timezone])
说明
将时间戳截断为 date_part 的粒度。
TIMESTAMP_TRUNC 支持以下 date_part 值：
MICROSECONDMILLISECONDSECONDMINUTEHOURDAYWEEKWEEK(<WEEKDAY>):将timestamp_expression截断到前一周的边界，一周开始于WEEKDAY。WEEKDAY的有效值包括SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY和SATURDAY。ISOWEEK：将timestamp_expression截断至上一个 ISO 8601 格式周的边界值。ISOWEEK从星期一开始算起。每个 ISO 格式年份的第一个ISOWEEK都包含对应公历年的第一个星期四。任何早于此日期的date_expression均会截断至上一个星期一。MONTHQUARTERYEARISOYEAR：将timestamp_expression截断至上一个 ISO 8601 格式周编号年份的边界值。ISO 格式年份的边界值是星期四属于相应公历年的第一周的星期一。
TIMESTAMP_TRUNC 函数支持可选的 timezone 参数。此参数适用于以下 date_parts：
MINUTEHOURDAYWEEKWEEK(<WEEKDAY>)ISOWEEKMONTHQUARTERYEARISOYEAR
如果您想要在截断操作过程中使用默认时区 (UTC) 以外的时区，请使用此参数。
将 TIMESTAMP 截断到 MINUTE 或 HOUR 时，TIMESTAMP_TRUNC 会确定 TIMESTAMP 在指定（或默认）时区中的民用时间，并且从该 TIMESTAMP 中减去分钟和秒（截断到 HOUR 时）或减去秒（截断到 MINUTE 时）。虽然这在大多数情况下都可以提供直观的结果，但在临近夏令时转换时，小时并不一致，因此结果会不太直观。
返回数据类型
TIMESTAMP
示例
SELECT
TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "UTC") AS utc,
TIMESTAMP_TRUNC(TIMESTAMP "2008-12-25 15:30:00+00", DAY, "America/Los_Angeles") AS la;
-- Results may differ, depending upon the environment and time zone where this query was executed.
+-------------------------+-----------------=-------+
| utc | la |
+-------------------------+-------------------------+
| 2008-12-25 00:00:00 UTC | 2008-12-25 08:00:00 UTC |
+-------------------------+-