旧版 SQL 语法、函数和运算符
本文档详细介绍了旧版 SQL 查询语法、函数和运算符。BigQuery 的首选查询语法是 GoogleSQL。如需了解 GoogleSQL，请参阅 GoogleSQL 查询语法。
查询语法
注意：关键字不区分大小写。在本文档中，出于说明目的将 SELECT 等关键字大写。
SELECT 子句
SELECT 子句指定要计算的表达式列表。SELECT 子句中的表达式可以包含字段名称、文字和函数调用（包括聚合函数和窗口函数），以及这三者的组合。表达式列表以英文逗号分隔。
通过在表达式后添加空格后跟标识符，可以为每个表达式赋予别名。可以视情况在表达式和别名之间添加 AS 关键字，以提高可读性。SELECT 子句中定义的别名可以由查询的 GROUP BY、HAVING、ORDER BY 子句引用，但不能由 FROM、WHERE 或 OMIT RECORD IF 子句或同一 SELECT 子句中的其他表达式引用。
注意：
-
如果在
SELECT子句中使用聚合函数，那么您必须在所有表达式中使用聚合函数，或者查询必须具有GROUP BY子句，且该子句必须包含SELECT子句中的所有非聚合字段作为分组键。例如：#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
可以使用方括号来转义保留字，以便将其用作字段名称和别名。例如，如果有一个名为“partition”的列，其为 BigQuery 语法中的保留字，除非使用方括号将其转义，否则引用该字段的查询将会失败并显示含糊的错误消息：
SELECT [partition] FROM ...
示例
此示例在 SELECT 子句中定义别名，然后在 ORDER BY 子句中引用其中一个别名。请注意，不能在 WHERE 子句中使用 word_alias 引用 word 列；该列必须通过名称引用。len 别名在 WHERE 子句中也不可见。其将在 HAVING 子句中显示。
#legacySQL SELECT word AS word_alias, LENGTH(word) AS len FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS 'th' ORDER BY len;
聚合函数的 WITHIN 修饰符
aggregate_function WITHIN RECORD [ [ AS ] alias ]
WITHIN 关键字使聚合函数在每个记录中的重复值中聚合。对于每个输入记录，将产生一个聚合输出。这种类型的聚合称为“范围限定的聚合”。由于范围限定的聚合为每条记录生成输出，因此可以在不使用 GROUP BY 子句的情况下选择非聚合表达式和范围限定的聚合表达式。
在大多数情况下，您可以搭配 RECORD 范围使用范围限定的聚合。如果您的架构具有非常复杂的嵌套重复性，您可能需要在子记录范围内执行聚合。这可以通过将上述语法中的 RECORD 关键字替换为架构中用于执行聚合的节点名称来实现。如需详细了解这种高级行为，请参阅处理数据。
示例
此示例执范围行限定的 COUNT 聚合，然后按聚合值对记录进行过滤和排序。
#legacySQL SELECT repository.url, COUNT(payload.pages.page_name) WITHIN RECORD AS page_count FROM [bigquery-public-data:samples.github_nested] HAVING page_count > 80 ORDER BY page_count DESC;
FROM 子句
FROM [project_name:]datasetId.tableId [ [ AS ] alias ] | (subquery) [ [ AS ] alias ] |JOINclause |FLATTENclause | table wildcard function
FROM 子句指定要查询的源数据。您可以直接对表、子查询、联接表以及由特殊用途运算符（如下所述）修饰的表执行 BigQuery 查询。可以使用英文逗号查询这些数据源的组合，英文逗号是 BigQuery 中的 UNION ALL 运算符。
引用表
引用表时，必须同时指定 datasetId 和 tableId；project_name 为可选。如果未指定 project_name，则 BigQuery 会默认为当前项目project_name。如果项目名称包含短划线，则必须用括号括住整个表引用。
示例
[my-dashed-project:dataset1.tableName]
通过在表名称后添加空格后跟标识符，可以为表赋予别名。您可以选择在 tableId 和别名之间添加 AS 关键字来提高可读性tableId。
引用表中的列时，可以使用简单列名，也可以将别名（如果指定了别名）用作列名的前缀，或将 datasetId 和 tableId 作为前缀（只要未指定 project_name）。 project_name 不能包含在列前缀中，因为字段名称中不允许使用冒号字符project_name。
示例
此示例引用未设置表前缀的列。
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare];
此示例将 datasetId 和 tableId 用作列名的前缀。注意，此示例不得添加 project_nameproject_name。只有在数据集属于当前默认项目的情况下，这种方法才有效。
#legacySQL SELECT samples.shakespeare.word FROM samples.shakespeare;
此示例将表别名用作列名的前缀。
#legacySQL SELECT t.word FROM [bigquery-public-data:samples.shakespeare] AS t;
整数范围分区表
旧版 SQL 支持使用表修饰器来处理整数范围分区表中的特定分区。处理范围分区的关键是范围的起始值。
以下示例查询起始值为 30 的范围分区。
#legacySQL SELECT * FROM dataset.table$30;
请注意，您不能使用旧版 SQL 来查询整个整数范围分区表。查询会返回如下错误：
Querying tables partitioned on a field is not supported in Legacy SQL
使用子查询
子查询是前后带有括号的嵌套 SELECT 语句。如同表的列一样，子查询的 SELECT 子句中计算用的表达式也可供外部查询使用。
子查询可用于计算聚合和其他表达式，您可在子查询中使用所有 SQL 运算符。这意味着子查询本身可以包含其他子查询，而且子查询可以执行联接和分组聚合等操作。
英文逗号为 UNION ALL
与 GoogleSQL 不同，旧版 SQL 将英文逗号用作 UNION ALL 运算符，而不是 CROSS JOIN 运算符。这是一种演变而来的旧行为，因为 BigQuery 过去不支持 CROSS JOIN，而 BigQuery 用户经常需要编写 UNION ALL 查询。在 GoogleSQL 中，执行联合的查询特别冗长。将英文逗号用作联合运算符可以更高效地编写此类查询。例如，此查询可用于对多天的日志运行单个查询。
#legacySQL SELECT FORMAT_UTC_USEC(event.timestamp_in_usec) AS time, request_url FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503] WHERE event.username = 'root' AND NOT event.source_ip.is_internal;
联合大量表的查询通常比处理单个表的相同数据量的查询运行得慢。 每增加一个表，性能差异可达 50 毫秒。单个查询最多可以联合 1000 个表。
表通配符函数
术语“表通配符函数”是指 BigQuery 特有的一种特殊类型的函数。
在 FROM 子句中，这些函数用于通过几种类型的过滤器之一来匹配表名称的集合。例如，TABLE_DATE_RANGE 函数可用于仅查询一组特定的日常表。如需详细了解这些函数，请参阅表通配符函数。
FLATTEN 运算符
(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened)) (FLATTEN((subquery), field_to_be_flattened))
与典型的 SQL 处理系统不同，BigQuery 用于处理重复的数据。因此，BigQuery 用户有时需要编写控制重复记录结构的查询。一种方法是使用 FLATTEN 运算符。
FLATTEN 将架构中的一个节点从重复转换为可选。如果一条记录中包含重复字段的一个或多个值，FLATTEN 将创建多条记录，每条记录对应重复字段中的一个值。从该记录中选择的其他所有字段都会复制到每个新输出记录中。您可以重复应用 FLATTEN，以移除多重重复。
如需详细了解相关信息和示例，请参阅处理数据。
JOIN 运算符
BigQuery 支持在每个 FROM 子句中使用多个 JOIN 运算符。
后续 JOIN 运算会将前一个 JOIN 运算的结果用作 JOIN 左输入。前面的 JOIN 输入中的字段可以用作后续 JOIN 运算符 ON 子句中的键。
JOIN 类型
BigQuery 支持 INNER、[FULL|RIGHT|LEFT] OUTER、CROSS JOIN 运算。如果未指定，则默认为 INNER。
CROSS JOIN 运算不允许 ON 子句。CROSS JOIN 可能返回大量数据，并且可能导致查询速度慢且效率低，或者查询超出每个查询允许的最大资源。此类查询将失败并报错。在可能的情况下，建议不要在查询中使用 CROSS JOIN。例如，CROSS JOIN
通常用在窗口函数较有效率的位置。
EACH 修饰符
EACH 修饰符是一种提示，告知 BigQuery 使用多个分区执行 JOIN。 这在您知道 JOIN 两侧都很大时尤其有用。EACH 修饰符不能用于 CROSS JOIN 子句。
过去在许多情况下常常建议使用 EACH，但现在不同了。请尽量在不添加 EACH 修饰符的情况下使用 JOIN，以提高性能。发生查询资源超出上限的错误消息时才需使用 JOIN EACH。
半联接和反联接
除了支持在 FROM 子句中使用 JOIN 之外，BigQuery 还支持在 WHERE 子句中使用两种类型的联接，即半联接和反半联接。 半联接的指定方式是使用 IN 关键字和子查询；反联接的指定方式是使用 NOT IN 关键字。
示例
以下查询使用半联接来查找符合特定条件的 n 元语法，即该 n 元语法中的第一个字词也是另一个 n 元语法中的第二个字词，且后者的第三个字词是“AND”。
#legacySQL SELECT ngram FROM [bigquery-public-data:samples.trigrams] WHERE first IN (SELECT second FROM [bigquery-public-data:samples.trigrams] WHERE third = "AND") LIMIT 10;
以下查询使用半联接返回符合特定条件的女性数量，即这类女性的年龄超过 50 岁，且曾在美国出生率最高的 10 个州生育过。
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
如需查看其他 40 个州的数字，您可以使用反联接。以下查询与前一个示例几乎完全相同，但使用 NOT IN（而非 IN）来返回符合特定条件的女性数量，即这类女性的年龄超过 50 岁，且曾在 40 个出生率最低的州生育过。
#legacySQL SELECT mother_age, COUNT(mother_age) total FROM [bigquery-public-data:samples.natality] WHERE state NOT IN (SELECT state FROM (SELECT state, COUNT(state) total FROM [bigquery-public-data:samples.natality] GROUP BY state ORDER BY total DESC LIMIT 10)) AND mother_age > 50 GROUP BY mother_age ORDER BY mother_age DESC
注意：
- BigQuery 不支持关联的半联接或反半联接。子查询无法引用外部查询中的任何字段。
- 在半联接或反半联接中使用的子查询必须仅选择一个字段。
-
所选字段类型和
WHERE子句中外部查询使用的字段必须完全匹配。BigQuery 不会对半联接或反半联接执行任何类型强制转换。
WHERE 子句
WHERE 子句（有时称为谓词）使用布尔值表达式过滤 FROM 子句生成的记录。 可以通过布尔值 AND 和 OR 子句连接多个条件，可选择用括号 () 括起来将其分组。不需要在相应的 SELECT 子句中选择 WHERE 子句中列出的字段，并且 WHERE 子句表达式不能引用 WHERE 子句所属查询的 SELECT 子句中计算的表达式。
注意：WHERE 子句中不能使用聚合函数。如果您需要过滤聚合函数的输出，请使用 HAVING 子句和外部查询。
示例
以下示例在 WHERE 子句中使用布尔值表达式的析取 - 两个表达式由 OR 运算符连接。 如果任一表达式返回 true，则输入记录将通过 WHERE 过滤条件。
#legacySQL SELECT word FROM [bigquery-public-data:samples.shakespeare] WHERE (word CONTAINS 'prais' AND word CONTAINS 'ing') OR (word CONTAINS 'laugh' AND word CONTAINS 'ed');
OMIT RECORD IF 子句
OMIT RECORD IF 子句是 BigQuery 独有的构造。该子句对于处理重复的嵌套架构特别有用。它类似于 WHERE
子句，但在两个重要方面存在区别。首先，它使用排除条件，这意味着如果表达式返回 true，则省略记录，但如果表达式返回 false 或 null，则保留记录。其次，OMIT RECORD IF
子句可以（并且通常会）在其条件中使用范围限定的聚合函数。
除了过滤完整记录之外，OMIT...IF 还可以指定更小的范围来过滤记录的部分内容。为此，在架构中使用非叶节点的名称，而不是在 OMIT...IF 子句中使用 RECORD。BigQuery 用户很少使用此功能。您可以通过上文的 WITHIN 文档链接，找到有关此高级行为的更多说明。
如果在重复字段中使用 OMIT...IF 排除记录的一部分，且查询也选择其他独立重复字段，则 BigQuery 会省略查询中其他重复记录的一部分。如果您看到错误 Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>,，我们建议您改用 GoogleSQL。如需了解如何将 OMIT...IF 语句迁移到 GoogleSQL，请参阅迁移到 GoogleSQL。
示例
再次参考用于 WITHIN 修饰符的示例，OMIT RECORD IF
可用于实现与该示例中 WITHIN 和 HAVING 相同的功能。
#legacySQL SELECT repository.url FROM [bigquery-public-data:samples.github_nested] OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;
GROUP BY 子句
GROUP BY 子句可对给定字段或字段集中具有相同值的行进行分组，以计算相关字段的聚合。在 WHERE 子句中执行过滤之后但在计算 SELECT 子句中的表达式之前进行分组。表达式结果不能用作 GROUP BY 子句中的组键。
示例
此查询查找 trigrams 示例数据集中十个最常见的第一个字词。除了演示 GROUP BY 子句的用途之外，该示例还演示了如何在 GROUP BY 和 ORDER BY 子句中使用位置索引（而非字段名称）。
#legacySQL SELECT first, COUNT(ngram) FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 ORDER BY 2 DESC LIMIT 10;
使用 GROUP BY 子句执行的聚合称为分组聚合。与范围限定的聚合不同，分组聚合常见于大多数 SQL 处理系统中。
EACH 修饰符
EACH 修饰符是一种提示，告知 BigQuery 使用多个分区执行 GROUP BY。 这在您知道数据集包含大量不同的组键值时尤其有用。
过去在许多情况下常常建议使用 EACH，但现在不同了。
使用不带 EACH 修饰符的 GROUP BY 通常可以提高性能。
发生查询资源超出上限的错误消息时才需使用 GROUP EACH BY。
ROLLUP 函数
使用 ROLLUP 函数时，BigQuery 会在查询结果中添加额外的行，以表示汇总的聚合。所有列在 ROLLUP 后面的字段都必须用一组括号括住。在因 ROLLUP 函数而添加的行中，NULL 指示汇总聚合的列。
示例
以下查询通过样本出生率数据集生成每年的男性和女性新生儿数量。
#legacySQL SELECT year, is_male, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
查询结果如下。请注意，有些行的一个或两个组键为 NULL。这些行就是汇总行。
+------+---------+----------+ | year | is_male | count | +------+---------+----------+ | NULL | NULL | 12122730 | | 2000 | NULL | 4063823 | | 2000 | false | 1984255 | | 2000 | true | 2079568 | | 2001 | NULL | 4031531 | | 2001 | false | 1970770 | | 2001 | true | 2060761 | | 2002 | NULL | 4027376 | | 2002 | false | 1966519 | | 2002 | true | 2060857 | +------+---------+----------+
使用 ROLLUP 函数时，您可以使用 GROUPING 函数来区分因 ROLLUP 函数而添加的行以及组键实际为 NULL 值的行。
示例
以下查询在上一个示例中添加了 GROUPING 函数，以便更好地识别因 ROLLUP 函数而添加的行。
#legacySQL SELECT year, GROUPING(year) as rollup_year, is_male, GROUPING(is_male) as rollup_gender, COUNT(1) as count FROM [bigquery-public-data:samples.natality] WHERE year >= 2000 AND year <= 2002 GROUP BY ROLLUP(year, is_male) ORDER BY year, is_male;
新查询返回的结果如下。
+------+-------------+---------+---------------+----------+ | year | rollup_year | is_male | rollup_gender | count | +------+-------------+---------+---------------+----------+ | NULL | 1 | NULL | 1 | 12122730 | | 2000 | 0 | NULL | 1 | 4063823 | | 2000 | 0 | false | 0 | 1984255 | | 2000 | 0 | true | 0 | 2079568 | | 2001 | 0 | NULL | 1 | 4031531 | | 2001 | 0 | false | 0 | 1970770 | | 2001 | 0 | true | 0 | 2060761 | | 2002 | 0 | NULL | 1 | 4027376 | | 2002 | 0 | false | 0 | 1966519 | | 2002 | 0 | true | 0 | 2060857 | +------+-------------+---------+---------------+----------+
注意：
-
SELECT子句中的非聚合字段必须列在GROUP BY子句中。#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus; /* Succeeds because all non-aggregated fields are group keys. */
#legacySQL SELECT word, corpus, COUNT(word) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word; /* Fails because corpus is not aggregated nor is it a group key. */
-
不能在相应的
GROUP BY子句中使用SELECT子句中计算的表达式。#legacySQL SELECT word, corpus, COUNT(word) word_count FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th" GROUP BY word, corpus, word_count; /* Fails because word_count is not visible to this
GROUP BYclause. */ - 不支持按浮点值和双精度值分组，因为未针对这些类型明确定义相等函数。
-
由于系统是互动式的，因此如果查询会生成大量组，就可能会失败。使用
TOP函数代替GROUP BY可能会解决一些扩缩问题。
HAVING 子句
HAVING 子句的行为与 WHERE 子句完全相同，但其在 SELECT 子句之后进行计算，因此所有计算表达式的结果都对 HAVING 子句可见。HAVING 子句只能引用相应 SELECT 子句的输出。
示例
此查询计算 ngram 样本数据集中包含字母 a 且最多出现 10000 次的最常见的第一个字词。
#legacySQL SELECT first, COUNT(ngram) ngram_count FROM [bigquery-public-data:samples.trigrams] GROUP BY 1 HAVING first contains "a" AND ngram_count < 10000 ORDER BY 2 DESC LIMIT 10;
ORDER BY 子句
ORDER BY 子句使用一个或多个键字段将查询结果按升序或降序排序。如需按多个字段或别名排序，请以英文逗号分隔列表的形式将其输入。结果按字段以其列出的顺序排序。
使用 DESC（降序）或 ASC（升序）指定排序方向。
默认为 ASC。可以为每个排序键指定不同的排序方向。
ORDER BY 子句在 SELECT 子句之后计算，因此其可以引用在 SELECT 中计算的任何表达式的输出。如果在 SELECT 子句中为字段指定了别名，则必须在 ORDER BY 子句中使用该别名。
LIMIT 子句
LIMIT 子句会限制返回结果集中的行数。由于 BigQuery 查询是定期对大量行执行，因此您可以借助 LIMIT 仅对一部分行执行处理，从而有效地避免长时间运行查询。
注意：
-
LIMIT子句将在满足要求后停止处理并返回结果。 这样可以减少某些查询的处理时间，但如果您指定聚合函数（如 COUNT 或ORDER BY子句），系统还是会先处理整个结果集，然后才会返回结果。LIMIT子句是最后计算的项目。 -
如果查询中没有任何运算符可保证输出结果集的排序，则包含
LIMIT子句的查询可能仍然是非确定性的。 这是因为 BigQuery 执行时会使用大量并行工作器，而并行作业返回结果的顺序没有一定的保证。 LIMIT子句不能包含任何函数；它只能接受数字常量。-
使用
LIMIT子句时，对于同一查询，处理的总字节数和结算的字节数可能会有所不同。
查询语法
上文详细介绍了 BigQuery SELECT 语句的各个子句。这里我们以紧凑的形式呈现 SELECT 语句的完整语法，其中链接可以返回各个部分。
query: SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ] [ FROM from_body [ WHERE bool_expression ] [ OMIT RECORD IF bool_expression] [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ] [ HAVING bool_expression ] [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ] [ LIMIT n ] ]; from_body: { from_item [, ...] | # Warning: Comma means UNION ALL here from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] | (FLATTEN({ table_name | (query) }, field_name_or_alias)) | table_wildcard_function } from_item: { table_name | (query) } [ [ AS ] alias ] join_type: { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS } join_predicate: field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...] expression: { literal_value | field_name_or_alias | function_call } bool_expression: { expression_which_results_in_a_boolean_value | bool_expression AND bool_expression | bool_expression OR bool_expression | NOT bool_expression }
批注：
- 方括号“[ ]”表示可选的子句。
- 大括号“{ }”括起一组选项。
- 竖线“|”表示逻辑“或”(OR)。
- 方括号“[, … ]”中的英文逗号或关键字后跟省略号表示前面的内容在包含指定分隔符的列表中重复。
- 圆括号“( )”表示文本括号。
支持的函数和运算符
大多数 SELECT 语句子句都支持函数。函数中引用的字段不需要在任何 SELECT 子句中列出。因此，即使不直接显示 clicks 字段，以下查询也是有效的：
#legacySQL SELECT country, SUM(clicks) FROM table GROUP BY country;
| 聚合函数 | |
|---|---|
AVG() |
返回一组行中各值的平均值… |
BIT_AND() |
返回按位 AND 运算的结果… |
BIT_OR() |
返回按位 OR 运算的结果… |
BIT_XOR() |
返回按位 XOR 运算的结果… |
CORR() |
返回一组数字对的皮尔逊相关系数。 |
COUNT() |
返回值的总数… |
COUNT([DISTINCT]) |
返回非 NULL 值的总数… |
COVAR_POP() |
计算值的总体协方差… |
COVAR_SAMP() |
计算值的样本协方差… |
EXACT_COUNT_DISTINCT() |
返回指定字段的不同非 NULL 值的确切数量。 |
FIRST() |
返回函数范围内按顺序第一个值。 |
GROUP_CONCAT() |
将多个字符串连接成单个字符串… |
GROUP_CONCAT_UNQUOTED() |
将多个字符串连接成单个字符串…且不添加双引号… |
LAST() |
返回最后一个序列值… |
MAX() |
返回最大值… |
MIN() |
返回最小值… |
NEST() |
将当前聚合范围内的所有值聚合到一个重复字段。 |
NTH() |
返回第 n 个序列值… |
QUANTILES() |
计算近似最小值、最大值和分位数… |
STDDEV() |
返回标准差… |
STDDEV_POP() |
计算总体标准差… |
STDDEV_SAMP() |
计算样本标准差… |
SUM() |
返回值的总和… |
TOP() ... COUNT(*) |
返回按频率排在前面的 max_records 个记录。 |
UNIQUE() |
返回一组唯一的非 NULL 值… |
VARIANCE() |
计算值的方差… |
VAR_POP() |
计算值的总体方差… |
VAR_SAMP() |
计算值的样本方差… |
| 算术运算符 | |
|---|---|
+ |
加 |
- |
减 |
* |
乘 |
/ |
除 |
% |
Modulo |
| 按位函数 | |
|---|---|
& |
按位 AND |
| |
按位 OR |
^ |
按位 XOR |
<< |
按位左移 |
>> |
按位右移 |
~ |
按位 NOT |
BIT_COUNT() |
返回位数… |
| 类型转换函数 | |
|---|---|
BOOLEAN() |
转换为布尔值。 |
BYTES() |
转换为字节。 |
CAST(expr AS type) |
将 expr 转换为 type 类型的变量。 |
FLOAT() |
转换为双精度型。 |
HEX_STRING() |
转换为十六进制字符串。 |
INTEGER() |
转换为整数。 |
STRING() |
转换为字符串。 |
| 比较函数 | |
|---|---|
expr1 = expr2 |
如果表达式相等，则返回 true。 |
expr1 != expr2expr1 <> expr2
|
如果表达式不相等，则返回 true。 |
expr1 > expr2 |
如果 expr1 大于 expr2，则返回 true。 |
expr1 < expr2 |
如果 expr1 小于 expr2，则返回 true。 |
expr1 >= expr2 |
如果 expr1 大于或等于 expr2，则返回 true。 |
expr1 <= expr2 |
如果 expr1 小于或等于 expr2，则返回 true。 |
expr1 BETWEEN expr2 AND expr3 |
如果 expr1 的值介于 expr2 和 expr3 之间（含边界值），则返回 true。 |
expr IS NULL |
如果 expr 为 NULL，则返回 true。 |
expr IN() |
如果 expr 与 expr1、expr2 或括号中的任何值匹配，则返回 true。 |
COALESCE() |
返回第一个非 NULL 的参数。 |
GREATEST() |
返回最大的 numeric_expr 参数。 |
IFNULL() |
如果参数不为 null，则返回该参数。 |
IS_INF() |
如果值为正无穷大或负无穷大，则返回 true。 |
IS_NAN() |
如果参数为 NaN，则返回 true。 |
IS_EXPLICITLY_DEFINED() |
已弃用：请改用 expr IS NOT NULL。 |
LEAST() |
返回最小的 numeric_expr 参数。 |
NVL() |
如果 expr 不为 null，则返回 expr，否则返回 null_default。 |
| 日期和时间函数 | |
|---|---|
CURRENT_DATE() |
以 %Y-%m-%d 格式返回当前日期。 |
CURRENT_TIME() |
以 %H:%M:%S 格式返回服务器的当前时间。 |
CURRENT_TIMESTAMP() |
以 %Y-%m-%d %H:%M:%S 格式返回服务器的当前时间。 |
DATE() |
以 %Y-%m-%d 格式返回日期。 |
DATE_ADD() |
将指定的时间间隔添加到时间戳数据类型。 |
DATEDIFF() |
返回两个时间戳数据类型之间的天数。 |
DAY() |
以 1 到 31 之间的整数形式返回一个月中的某天。 |
DAYOFWEEK() |
以 1（星期日）和 7（星期六）之间的整数形式返回一周中的某天。 |
DAYOFYEAR() |
以 1 到 366 之间的整数形式返回一年中的某天。 |
FORMAT_UTC_USEC() |
以 YYYY-MM-DD HH:MM:SS.uuuuuu 格式返回 UNIX 时间戳。 |
HOUR() |
以 0 到 23 之间的整数形式返回时间戳的小时数。 |
MINUTE() |
以 0 到 59 之间的整数形式返回时间戳的分钟数。 |
MONTH() |
以 1 到 12 之间的整数形式返回时间戳的月份。 |
MSEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以毫秒为单位）转换为时间戳。 |
NOW() |
返回当前的 UNIX 时间戳（以微秒为单位）。 |
PARSE_UTC_USEC() |
将日期字符串转换为 UNIX 时间戳（以微秒为单位）。 |
QUARTER() |
返回一个介于 1 到 4 之间的整数，该值表示 TIMESTAMP 属于相应年份中的哪个季度。 |
SEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以秒为单位）转换为 TIMESTAMP。 |
SECOND() |
返回一个介于 0 到 59 之间的整数，该值表示 TIMESTAMP 的秒数部分。 |
STRFTIME_UTC_USEC() |
以 date_format_str 格式返回日期字符串date_format_str。 |
TIME() |
以 %H:%M:%S 格式返回 TIMESTAMP。 |
TIMESTAMP() |
将日期字符串转换为 TIMESTAMP。 |
TIMESTAMP_TO_MSEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以毫秒为单位）。 |
TIMESTAMP_TO_SEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以秒为单位）。 |
TIMESTAMP_TO_USEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以微秒为单位）。 |
USEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以微秒为单位）转换为 TIMESTAMP。 |
UTC_USEC_TO_DAY() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生日期的开头。 |
UTC_USEC_TO_HOUR() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生时间（小时）的开头。 |
UTC_USEC_TO_MONTH() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生月份的开头。 |
UTC_USEC_TO_WEEK() |
以微秒为单位返回 UNIX 时间戳，表示星期几。 |
UTC_USEC_TO_YEAR() |
以微秒为单位返回 UNIX 时间戳，表示年份。 |
WEEK() |
返回一个介于 1 到 53 之间的整数，该值表示 TIMESTAMP 的周数部分。 |
YEAR() |
返回 TIMESTAMP 的年份部分。 |
| IP 函数 | |
|---|---|
FORMAT_IP() |
将 integer_value 的 32 个最低有效位转换为人类可读的 IPv4 地址字符串。 |
PARSE_IP() |
将表示 IPv4 地址的字符串转换为无符号整数值。 |
FORMAT_PACKED_IP() |
以 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f 的形式返回人类可读的 IP 地址。 |
PARSE_PACKED_IP() |
以BYTES形式返回 IP 地址。 |
| JSON 函数 | |
|---|---|
JSON_EXTRACT() |
根据 JSONPath 表达式选择值并返回 JSON 字符串。 |
JSON_EXTRACT_SCALAR() |
根据 JSONPath 表达式选择值并返回 JSON 标量。 |
| 逻辑运算符 | |
|---|---|
expr AND expr |
如果两个表达式都为 true，则返回 true。 |
expr OR expr |
如果一个或两个表达式为 true，则返回 true。 |
NOT expr |
如果表达式为 false，则返回 true。 |
| 数学函数 | |
|---|---|
ABS() |
返回参数的绝对值。 |
ACOS() |
返回参数的反余弦值。 |
ACOSH() |
返回参数的反双曲余弦值。 |
ASIN() |
返回参数的反正弦值。 |
ASINH() |
返回参数的反双曲正弦值。 |
ATAN() |
返回参数的反正切值。 |
ATANH() |
返回参数的反双曲正切值。 |
ATAN2() |
返回两个参数的反正切值。 |
CEIL() |
将参数向上舍入到最接近的整数并返回舍入后的值。 |
COS() |
返回参数的余弦值。 |
COSH() |
返回参数的双曲余弦值。 |
DEGREES() |
从弧度转换为角度。 |
EXP() |
返回 e 的参数次幂。 |
FLOOR() |
将参数向下舍入到最接近的整数。 |
LN()LOG()
|
返回参数的自然对数。 |
LOG2() |
返回参数以 2 为底的对数。 |
LOG10() |
返回参数以 10 为底的对数。 |
PI() |
返回常数 π。 |
POW() |
返回第一个参数的第二个参数次幂。 |
RADIANS() |
从角度转换为弧度。 |
RAND() |
返回 0.0 <= 值 < 1.0 范围内的随机浮点值。 |
ROUND() |
将参数向上或向下舍入到最接近的整数。 |
SIN() |
返回参数的正弦值。 |
SINH() |
返回参数的双曲正弦值。 |
SQRT() |
返回表达式的平方根。 |
TAN() |
返回参数的正切值。 |
TANH() |
返回参数的双曲正切值。 |
| 正则表达式函数 | |
|---|---|
REGEXP_MATCH() |
如果参数与正则表达式匹配，则返回 true。 |
REGEXP_EXTRACT() |
返回与正则表达式中的捕获组匹配的参数部分。 |
REGEXP_REPLACE() |
替换与正则表达式匹配的子字符串。 |
| 字符串函数 | |
|---|---|
CONCAT() |
返回两个或多个字符串的串联，如果任何值为 NULL，则返回 NULL。 |
expr CONTAINS 'str' |
如果 expr 包含指定的字符串参数，则返回 true。 |
INSTR() |
返回第一次出现的字符串的从 1 开始的索引。 |
LEFT() |
返回字符串的最左侧字符。 |
LENGTH() |
返回字符串的长度。 |
LOWER() |
返回所有字符都是小写的原始字符串。 |
LPAD() |
在字符串的左侧插入字符。 |
LTRIM() |
移除字符串左侧的字符。 |
REPLACE() |
替换所有出现的子字符串。 |
RIGHT() |
返回字符串最右侧的字符。 |
RPAD() |
在字符串的右侧插入字符。 |
RTRIM() |
移除字符串右侧的尾随字符。 |
SPLIT() |
将字符串拆分为重复的子字符串。 |
SUBSTR() |
返回子字符串… |
UPPER() |
返回所有字符都是大写的原始字符串。 |
| 表通配符函数 | |
|---|---|
TABLE_DATE_RANGE() |
查询跨日期范围的多个日常表。 |
TABLE_DATE_RANGE_STRICT() |
查询跨日期范围的多个日常表，且不缺少日期。 |
TABLE_QUERY() |
查询名称与指定谓词匹配的表。 |
| 网址函数 | |
|---|---|
HOST() |
对于给定网址，以字符串形式返回主机名。 |
DOMAIN() |
对于给定网址，以字符串形式返回网域。 |
TLD() |
对于给定网址，返回网址中的顶级域名和任何国家/地区域名。 |
| 窗口函数 | |
|---|---|
AVG()COUNT(*)COUNT([DISTINCT])MAX()MIN()STDDEV()SUM() |
与相应的聚合函数的运算相同，但是通过由 OVER 子句定义的窗口执行计算。 |
CUME_DIST() |
返回一个双精度浮点值，表示一组值中某个值的累积分布… |
DENSE_RANK() |
返回一组值中某个值的整数排名。 |
FIRST_VALUE() |
返回窗口中指定字段的第一个值。 |
LAG() |
允许从窗口中的上一行读取数据。 |
LAST_VALUE() |
返回窗口中指定字段的最后一个值。 |
LEAD() |
让您可读取窗口中的下一行数据。 |
NTH_VALUE() |
返回窗口框架中位置 <n> 处的 <expr> 值...
|
NTILE() |
将窗口划分为指定数量的区间。 |
PERCENT_RANK() |
返回当前行相对于分区中的其他行的排名。 |
PERCENTILE_CONT() |
返回内插值，该值将映射到相对于窗口中其它值的百分位参数… |
PERCENTILE_DISC() |
返回窗口中最接近参数百分位数的值。 |
RANK() |
返回一组值中值的整数排名。 |
RATIO_TO_REPORT() |
返回每个值与值之和的比率。 |
ROW_NUMBER() |
返回查询结果在窗口中的当前行号。 |
| 其他函数 | |
|---|---|
CASE WHEN ... THEN |
使用 CASE 在查询的两个或多个备用表达式中进行选择。 |
CURRENT_USER() |
返回运行查询的用户的电子邮件地址。 |
EVERY() |
如果其所有输入的参数都为 true，则返回 true。 |
FROM_BASE64() |
将以 base-64 编码的输入字符串转换为字节格式。 |
HASH() |
计算并返回 64 位有符号哈希值… |
FARM_FINGERPRINT() |
计算并返回 64 位有符号指纹值… |
IF() |
如果第一个参数为 true，则返回第二个参数；否则返回第三个参数。 |
POSITION() |
返回参数在序列中的位置（从 1 开始）。 |
SHA1() |
以字节格式返回 SHA1 哈希值。 |
SOME() |
如果参数至少有一个输入为 true，则返回 true。 |
TO_BASE64() |
将字节参数转换为以 base-64 编码的字符串。 |
聚合函数
聚合函数返回表示较大数据集汇总的值，因此这些函数对于分析日志特别有用。聚合函数针对值集合进行运算，并为每个表、组或范围返回一个值：
- 表聚合
使用聚合函数汇总表中的所有符合条件的行。例如：
SELECT COUNT(f1) FROM ds.Table; - 组聚合
使用聚合函数和指定非聚合字段按组汇总行的
GROUP BY子句。例如：SELECT COUNT(f1) FROM ds.Table GROUP BY b1;TOP 函数表示一种特殊情况的组聚合。
- 范围限定的聚合
此功能仅适用于具有嵌套字段的表。
使用聚合函数和WITHIN关键字聚合定义范围内的重复值。例如：SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;范围可以是
RECORD，其对应于整行，或者可以是节点（一行中的重复字段）。聚合函数对范围内的值进行运算，并返回每个记录或节点的聚合结果。
您可以使用以下选项之一对聚合函数施加限制：
-
子选择查询中的别名。该限制在外部
WHERE子句中指定。#legacySQL SELECT corpus, count_corpus_words FROM (SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus) AS sub_shakespeare WHERE count_corpus_words > 4000
-
HAVING 子句中的别名。
#legacySQL SELECT corpus, count(word) AS count_corpus_words FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus HAVING count_corpus_words > 4000;
还可以在 GROUP BY 或 ORDER BY 子句中引用别名。
语法
| 聚合函数 | |
|---|---|
AVG() |
返回一组行中各值的平均值… |
BIT_AND() |
返回按位 AND 运算的结果… |
BIT_OR() |
返回按位 OR 运算的结果… |
BIT_XOR() |
返回按位 XOR 运算的结果… |
CORR() |
返回一组数字对的皮尔逊相关系数。 |
COUNT() |
返回值的总数… |
COUNT([DISTINCT]) |
返回非 NULL 值的总数… |
COVAR_POP() |
计算值的总体协方差… |
COVAR_SAMP() |
计算值的样本协方差… |
EXACT_COUNT_DISTINCT() |
返回指定字段的不同非 NULL 值的确切数量。 |
FIRST() |
返回函数范围内按顺序第一个值。 |
GROUP_CONCAT() |
将多个字符串连接成单个字符串… |
GROUP_CONCAT_UNQUOTED() |
将多个字符串连接成单个字符串…且不添加双引号… |
LAST() |
返回最后一个序列值… |
MAX() |
返回最大值… |
MIN() |
返回最小值… |
NEST() |
将当前聚合范围内的所有值聚合到一个重复字段。 |
NTH() |
返回第 n 个序列值… |
QUANTILES() |
计算近似最小值、最大值和分位数… |
STDDEV() |
返回标准差… |
STDDEV_POP() |
计算总体标准差… |
STDDEV_SAMP() |
计算样本标准差… |
SUM() |
返回值的总和… |
TOP() ... COUNT(*) |
返回按频率排在前面的 max_records 个记录。 |
UNIQUE() |
返回一组唯一的非 NULL 值… |
VARIANCE() |
计算值的方差… |
VAR_POP() |
计算值的总体方差… |
VAR_SAMP() |
计算值的样本方差… |
AVG(numeric_expr)- 返回由
numeric_expr计算的一组行的值的平均值。具有 NULL 值的行不包括在计算中。 BIT_AND(numeric_expr)- 返回所有行中各
numeric_expr实例之间按位AND运算的结果。系统会忽略NULL值。如果numeric_expr的所有实例的计算结果均为NULL，则该函数返回NULL。 BIT_OR(numeric_expr)- 返回所有行中各
numeric_expr实例之间按位OR运算的结果。系统会忽略NULL值。如果numeric_expr的所有实例的计算结果均为NULL，则该函数返回NULL。 BIT_XOR(numeric_expr)- 返回所有行中各
numeric_expr实例之间按位XOR运算的结果。系统会忽略NULL值。如果numeric_expr的所有实例的计算结果均为NULL，则该函数返回NULL。 CORR(numeric_expr, numeric_expr)- 返回一组数字对的皮尔逊相关系数。
COUNT(*)- 返回函数范围内值（NULL 和非 NULL）的总数。除非搭配
TOP函数使用COUNT(*)，否则最好明确指定要计算的字段。 COUNT([DISTINCT] field [, n])- 返回函数范围内非 NULL 值的总数。
如果您使用
DISTINCT关键字，则该函数会针对指定字段返回不同值的数量。请注意，DISTINCT的返回值是概略统计结果，不一定是实际值。使用
EXACT_COUNT_DISTINCT()即可获取准确的答案。如果您需要从
COUNT(DISTINCT)n，这样就能保证低于此阈值的结果都准确无误。默认情况下，n为 1000，但如果您将n设为更大的数字，那么在n达到该设定值为止，COUNT(DISTINCT)会返回精确的结果。但是，指定较大的n值会降低此运算符的可扩缩性，并且可能会大大增加查询执行时间或导致查询失败。如需计算不同值的精确数量，请使用 EXACT_COUNT_DISTINCT。您也可以考虑一种扩缩性更高的方法，即对相关字段使用
GROUP EACH BY，然后应用COUNT(*)。GROUP EACH BY方法更具可扩缩性，但可能会产生轻微的前期性能损失。 COVAR_POP(numeric_expr1, numeric_expr2)- 针对通过
numeric_expr1和numeric_expr2算出的值计算总体协方差。 COVAR_SAMP(numeric_expr1, numeric_expr2)- 针对通过
numeric_expr1和numeric_expr2算出的值计算样本协方差。 EXACT_COUNT_DISTINCT(field)- 返回指定字段的不同非 NULL 值的确切数量。如要提高可扩缩性和性能，请使用 COUNT(DISTINCT field)。
FIRST(expr)- 返回函数范围内的第一个序列值。
GROUP_CONCAT('str' [, separator])-
将多个字符串连接成一个字符串，可选用
separator参数来分隔各个值。如果省略separator，BigQuery 将返回以英文逗号分隔的字符串。如果源数据中的字符串包含双引号字符，
GROUP_CONCAT将返回添加了双引号的字符串。例如，字符串a"b将以"a""b"的形式返回。如果您希望在不添加双引号的情况下返回这些字符串，请使用GROUP_CONCAT_UNQUOTED。示例：
#legacySQL SELECT GROUP_CONCAT(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
GROUP_CONCAT_UNQUOTED('str' [, separator])-
将多个字符串连接成一个字符串，可选用
separator参数来分隔各个值。如果省略separator，BigQuery 将返回以英文逗号分隔的字符串。与
GROUP_CONCAT不同，此函数不会为包括英文双引号字符的返回值添加英文双引号。例如，字符串a"b将以a"b的形式返回。示例：
#legacySQL SELECT GROUP_CONCAT_UNQUOTED(x) FROM ( SELECT 'a"b' AS x), ( SELECT 'cd' AS x);
LAST(field)- 返回函数范围内按顺序最后一个值。
MAX(field)- 返回函数范围内的最大值。
MIN(field)- 返回函数范围内的最小值。
NEST(expr)-
将当前聚合范围内的所有值聚合到一个重复字段。例如，查询
"SELECT x, NEST(y) FROM ... GROUP BY x"为每个不同的x值返回一条输出记录，且包含查询输入中与x配对的所有y值的重复字段。NEST函数需要GROUP BY子句。BigQuery 会自动平展查询结果，因此如果在顶级查询中使用
NEST函数，则结果将不包含重复的字段。使用会产生中间结果（以供同一查询立即使用）的子选择时，请使用NEST函数。 NTH(n, field)- 返回函数范围内第
n个序列值，其中n是常量。NTH函数从 1 开始计数，因此不存在“第 0 个”的用语。如果函数范围内含有小于n的值，则该函数返回NULL。 QUANTILES(expr[, buckets])-
计算输入表达式的近似最小值、最大值和分位数。忽略
NULL输入值。如果输入的内容为空或全为NULL，则会输出NULL。计算的分位数数量由可选的buckets参数控制，该参数包括计数中的最小值和最大值。如需计算近似的 N 分位数，请使用 N+1 个buckets。buckets的默认值为 100。（注意：默认值 100 不估算百分位数。如需估算百分位数，至少要使用 101 个buckets）。如果明确指定，则buckets必须至少为 2。每个分位数的分数误差为 epsilon = 1/
buckets，也就是说，误差会随着区间数量的增加而减小。例如：QUANTILES(<expr>, 2) # computes min and max with 50% error. QUANTILES(<expr>, 3) # computes min, median, and max with 33% error. QUANTILES(<expr>, 5) # computes quartiles with 25% error. QUANTILES(<expr>, 11) # computes deciles with 10% error. QUANTILES(<expr>, 21) # computes vigintiles with 5% error. QUANTILES(<expr>, 101) # computes percentiles with 1% error.
NTH函数可用于选择特定分位数，但请记住，NTH是从 1 开始，并且QUANTILES会在第一个位置返回最小值（“第 0 个”分位数），并在最后一个位置返回最大值（“第 100 个”百分位数或“第 N 个”N 分位数）。例如，NTH(11, QUANTILES(expr, 21))会估算expr的中位数，而NTH(20, QUANTILES(expr, 21))会估算expr的第 19 个二十分位数（第 95 个百分位数）。两项估算值都有 5% 的误差范围。如要提高准确性，请使用更多区间。例如，如要将先前计算结果的误差范围从 5% 减至 0.1%，可使用 1001 个（而非 21 个）区间，并针对
NTH函数相应地调整参数。如要让算出的中位数误差范围为 0.1%，请使用NTH(501, QUANTILES(expr, 1001))；如要让算出的第 95 个百分位数误差范围为 0.1%，请使用NTH(951, QUANTILES(expr, 1001))。 STDDEV(numeric_expr)- 返回通过
numeric_expr算出的值的标准差。值为 NULL 的行不会列入计算。STDDEV函数是STDDEV_SAMP的别名。 STDDEV_POP(numeric_expr)- 针对通过
numeric_expr算出的值计算总体标准差。 使用STDDEV_POP()计算包含整个感兴趣总体的数据集的标准偏差。如果数据集仅包含该总体的代表性样本，请改用STDDEV_SAMP()。如需详细了解总体与样本标准差，请在维基百科上参阅标准差。 STDDEV_SAMP(numeric_expr)- 针对通过
numeric_expr算出的值计算样本标准差。使用STDDEV_SAMP()根据总体的代表性样本计算整个总体的标准偏差。如果数据集包含整个总体，请改用STDDEV_POP()。如需详细了解总体与样本标准差，请在维基百科上参阅标准差。 SUM(field)- 返回函数范围内值的总和。仅适用于数值数据类型。
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)- 返回按频率排在前面的 max_recordsmax_records 个记录。详情请参阅下文的 TOP 说明。
UNIQUE(expr)- 以未定义的顺序返回函数范围内的一组唯一非 NULL 值。与不带
EACH关键字的大型GROUP BY子句类似，如果有太多不同的值，查询将失败并显示“资源超出 (Resources Exceeded)”错误。但是，与GROUP BY不同，UNIQUE函数可以应用于范围限定的聚合，允许对具有有限数量值的嵌套字段进行高效运算。 VARIANCE(numeric_expr)- 针对通过
numeric_expr算出的值计算方差。值为 NULL 的行不会列入计算。VARIANCE函数是VAR_SAMP的别名。 VAR_POP(numeric_expr)- 针对通过
numeric_expr算出的值计算总体方差。如需详细了解总体与样本标准差，请在维基百科上参阅标准差。 VAR_SAMP(numeric_expr)- 针对通过
numeric_expr算出的值计算样本方差。如需详细了解总体与样本标准差，请在维基百科上参阅标准差。
TOP() 函数
TOP 是替代 GROUP BY 子句的函数。该函数可用作 GROUP BY ... ORDER BY ... LIMIT ... 的简化语法。通常，TOP 函数执行速度比完整的 ... GROUP BY ... ORDER BY ... LIMIT ... 查询快，但可能只返回近似结果。以下是 TOP 函数的语法：
TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)
在 SELECT 子句中使用 TOP 时，必须将 COUNT(*) 添加为其中一个字段。
使用 TOP() 函数的查询只能返回两个字段：TOP 字段和 COUNT(*) 值。
field|alias- 需要返回的字段或别名。
max_values- [可选] 需要返回的最大结果数。默认值为 20。
multiplier- 一个正整数，用于将
COUNT(*)返回的值增加指定的倍数。
TOP() 示例
-
使用
TOP()的基本示例查询以下查询使用
TOP()返回 10 行。示例 1：
#legacySQL SELECT TOP(word, 10) as word, COUNT(*) as cnt FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th";
示例 2：
#legacySQL SELECT word, left(word, 3) FROM (SELECT TOP(word, 10) AS word, COUNT(*) FROM [bigquery-public-data:samples.shakespeare] WHERE word CONTAINS "th");
-
将
TOP()和GROUP BY...ORDER BY...LIMIT相比较查询会按顺序返回最常用且包含“th”的前 10 个字词，以及使用这些字词的文档数量。
TOP查询的执行速度将会快很多：不含
TOP()的示例：#legacySQL SELECT word, COUNT(*) AS cnt FROM ds.Table WHERE word CONTAINS 'th' GROUP BY word ORDER BY cnt DESC LIMIT 10;
包含
TOP()的示例：#legacySQL SELECT TOP(word, 10), COUNT(*) FROM ds.Table WHERE word contains 'th';
-
使用
multiplier参数。以下查询演示了
multiplier参数对查询结果有何影响。 第一项查询返回怀俄明州每月出生人数。 第二项查询使用multiplier参数将cnt值乘以 100。不含
multiplier参数的示例：#legacySQL SELECT TOP(month,3) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
返回：
+-------+-------+ | month | cnt | +-------+-------+ | 7 | 19594 | | 5 | 19038 | | 8 | 19030 | +-------+-------+
包含
multiplier参数的示例：#legacySQL SELECT TOP(month,3,100) as month, COUNT(*) as cnt FROM [bigquery-public-data:samples.natality] WHERE state = "WY";
返回：
+-------+---------+ | month | cnt | +-------+---------+ | 7 | 1959400 | | 5 | 1903800 | | 8 | 1903000 | +-------+---------+
注意：您必须在 SELECT 子句中加入 COUNT(*) 才能使用 TOP。
高级示例
-
按条件分组的平均值和标准差
以下查询返回 2003 年俄亥俄州新生儿体重的平均值和标准差，按照母亲是否吸烟分组。
示例：
#legacySQL SELECT cigarette_use, /* Finds average and standard deviation */ AVG(weight_pounds) baby_weight, STDDEV(weight_pounds) baby_weight_stdev, AVG(mother_age) mother_age FROM [bigquery-public-data:samples.natality] WHERE year=2003 AND state='OH' /* Group the result values by those */ /* who smoked and those who didn't. */ GROUP BY cigarette_use;
-
使用聚合值过滤查询结果
如需使用聚合值过滤查询结果（例如，按
SUM的值过滤），请使用HAVING函数。HAVING将值与聚合函数确定的结果进行比较，与WHERE相反，其在聚合之前对每一行进行运算。示例：
#legacySQL SELECT state, /* If 'is_male' is True, return 'Male', */ /* otherwise return 'Female' */ IF (is_male, 'Male', 'Female') AS sex, /* The count value is aliased as 'cnt' */ /* and used in the HAVING clause below. */ COUNT(*) AS cnt FROM [bigquery-public-data:samples.natality] WHERE state != '' GROUP BY state, sex HAVING cnt > 3000000 ORDER BY cnt DESC
返回：
+-------+--------+---------+ | state | sex | cnt | +-------+--------+---------+ | CA | Male | 7060826 | | CA | Female | 6733288 | | TX | Male | 5107542 | | TX | Female | 4879247 | | NY | Male | 4442246 | | NY | Female | 4227891 | | IL | Male | 3089555 | +-------+--------+---------+
算术运算符
算术运算符采用数字参数并返回数字结果。每个参数可以是数字文本或查询返回的数值。如果算术运算计算出未定义的结果，则运算返回 NULL。
语法
| 运算符 | 说明 | 示例 |
|---|---|---|
| + | 加 |
返回：10 |
| - | 减 |
返回：1 |
| * | 乘 |
返回：24 |
| / | 除 |
返回：1.5 |
| % | Modulo |
返回：2 |
按位函数
按位函数在各个位级别上执行运算，并且需要数值参数。如需详细了解按位函数，请参阅按位运算。
有关另外三个按位函数（BIT_AND、BIT_OR 和 BIT_XOR）的说明，请参阅聚合函数。
语法
| 运算符 | 说明 | 示例 |
|---|---|---|
| & | 按位 AND |
返回：0 |
| | | 按位 OR |
返回：28 |
| ^ | 按位 XOR |
返回：1 |
| << | 按位左移 |
返回：16 |
| >> | 按位右移 |
返回：2 |
| ~ | 按位非 |
返回：-3 |
BIT_COUNT(<numeric_expr>) |
返回 |
返回：4 |
类型转换函数
类型转换函数会更改数值表达式的数据类型。类型转换函数对于确保比较函数中的参数具有相同的数据类型特别有用。
语法
| 类型转换函数 | |
|---|---|
BOOLEAN() |
转换为布尔值。 |
BYTES() |
转换为字节。 |
CAST(expr AS type) |
将 expr 转换为 type 类型的变量。 |
FLOAT() |
转换为双精度型。 |
HEX_STRING() |
转换为十六进制字符串。 |
INTEGER() |
转换为整数。 |
STRING() |
转换为字符串。 |
BOOLEAN(<numeric_expr>)-
- 如果
<numeric_expr>不为 0 且不为 NULL，则返回true。 - 如果
<numeric_expr>为 0，则返回false。 - 如果
<numeric_expr>为 NULL，则返回NULL。
- 如果
BYTES(string_expr)- 以
bytes类型的值返回string_expr。 CAST(expr AS type)- 将
expr转换为type类型的变量。 FLOAT(expr)- 以双精度类型的值返回
expr。expr可以是类似'45.78'的字符串，但对于非数字值，该函数返回NULL。 HEX_STRING(numeric_expr)- 以十六进制字符串形式返回
numeric_expr。 INTEGER(expr)- 将
expr转换为 64 位整数。- 如果
expr是不会对应到整数值的字符串，则返回 NULL。 - 如果
expr是时间戳，则返回从 Unix Epoch 开始所经过的微秒数。
- 如果
STRING(numeric_expr)- 以字符串形式返回
numeric_expr。
比较函数
比较函数基于以下类型的比较返回 true 或 false：
- 两个表达式的比较。
- 表达式或表达式集与特定条件（例如在指定列表中、为 NULL，或者是非默认可选值）的比较。
下面列出的某些函数返回的值不是 true 或 false，它们返回基于比较运算的值。
您可以使用数字或字符串表达式作为比较函数的参数 （字符串常量必须用单引号或双引号括起来）。表达式可以是查询提取的文本或值。比较函数最常用作 WHERE 子句中的过滤条件，但也可将其用于其他子句中。
语法
| 比较函数 | |
|---|---|
expr1 = expr2 |
如果表达式相等，则返回 true。 |
expr1 != expr2expr1 <> expr2
|
如果表达式不相等，则返回 true。 |
expr1 > expr2 |
如果 expr1 大于 expr2，则返回 true。 |
expr1 < expr2 |
如果 expr1 小于 expr2，则返回 true。 |
expr1 >= expr2 |
如果 expr1 大于或等于 expr2，则返回 true。 |
expr1 <= expr2 |
如果 expr1 小于或等于 expr2，则返回 true。 |
expr1 BETWEEN expr2 AND expr3 |
如果 expr1 的值介于 expr2 和 expr3 之间（含边界值），则返回 true。 |
expr IS NULL |
如果 expr 为 NULL，则返回 true。 |
expr IN() |
如果 expr 与 expr1、expr2 或括号中的任何值匹配，则返回 true。 |
COALESCE() |
返回第一个非 NULL 的参数。 |
GREATEST() |
返回最大的 numeric_expr 参数。 |
IFNULL() |
如果参数不为 null，则返回该参数。 |
IS_INF() |
如果值为正无穷大或负无穷大，则返回 true。 |
IS_NAN() |
如果参数为 NaN，则返回 true。 |
IS_EXPLICITLY_DEFINED() |
已弃用：请改用 expr IS NOT NULL。 |
LEAST() |
返回最小的 numeric_expr 参数。 |
NVL() |
如果 expr 不为 null，则返回 expr，否则返回 null_default。 |
expr1 = expr2- 如果表达式相等，则返回
true。 expr1 != expr2
expr1 <> expr2- 如果表达式不相等，则返回
true。 expr1 > expr2- 如果
expr1大于expr2，则返回true。 expr1 < expr2- 如果
expr1小于expr2，则返回true。 expr1 >= expr2- 如果
expr1大于或等于expr2，则返回true。 expr1 <= expr2- 如果
expr1小于或等于expr2，则返回true。 expr1 BETWEEN expr2 AND expr3-
如果
expr1的值大于或等于expr2且小于或等于expr3，则返回true。 expr IS NULL- 如果
expr为 NULL，则返回true。 expr IN(expr1, expr2, ...)- 如果
expr与expr1、expr2或括号中的任何值匹配，则返回true。IN关键字是(expr = expr1 || expr = expr2 || ...)的有效简写。与IN关键字一起使用的表达式必须是常量，且其必须与expr的数据类型匹配。IN子句还可用于创建半联接和反联接。详情请参阅半联接和反联接。 COALESCE(<expr1>, <expr2>, ...)- 返回第一个非 NULL 的参数。
GREATEST(numeric_expr1, numeric_expr2, ...)-
返回最大的
numeric_expr参数。所有参数必须为数字，且必须为相同类型。如果任何参数为NULL，则此函数返回NULL。如需忽略
NULL值，请使用IFNULL函数将NULL值更改为不影响比较的值。在以下代码示例中，IFNULL函数用于将NULL值更改为-1，这不会影响正数之间的比较。SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);
IFNULL(expr, null_default)- 如果
expr不为 null，则返回expr，否则返回null_default。 IS_INF(numeric_expr)- 如果
numeric_expr为正无穷大或负无穷大，则返回true。 IS_NAN(numeric_expr)- 如果
numeric_expr为特殊NaN数值，则返回true。 IS_EXPLICITLY_DEFINED(expr)-
此函数已弃用。请改用
expr IS NOT NULL。 LEAST(numeric_expr1, numeric_expr2, ...)-
返回最小的
numeric_expr参数。所有参数必须为数字，且必须为相同类型。如果任何参数为NULL，则此函数返回NULL NVL(expr, null_default)- 如果
expr不为 null，则返回expr，否则返回null_default。NVL函数是IFNULL的别名。
日期和时间函数
以下函数为 UNIX 时间戳、日期字符串和时间戳数据类型启用日期和时间操作。如需详细了解如何使用时间戳数据类型，请参阅使用时间戳。
通过适用于 UNIX 时间戳的日期和时间函数对 UNIX 时间进行运算。日期和时间函数根据世界协调时间 (UTC) 时区返回值。
语法
| 日期和时间函数 | |
|---|---|
CURRENT_DATE() |
以 %Y-%m-%d 格式返回当前日期。 |
CURRENT_TIME() |
以 %H:%M:%S 格式返回服务器的当前时间。 |
CURRENT_TIMESTAMP() |
以 %Y-%m-%d %H:%M:%S 格式返回服务器的当前时间。 |
DATE() |
以 %Y-%m-%d 格式返回日期。 |
DATE_ADD() |
将指定的时间间隔添加到时间戳数据类型。 |
DATEDIFF() |
返回两个时间戳数据类型之间的天数。 |
DAY() |
以 1 到 31 之间的整数形式返回一个月中的某天。 |
DAYOFWEEK() |
以 1（星期日）和 7（星期六）之间的整数形式返回一周中的某天。 |
DAYOFYEAR() |
以 1 到 366 之间的整数形式返回一年中的某天。 |
FORMAT_UTC_USEC() |
以 YYYY-MM-DD HH:MM:SS.uuuuuu 格式返回 UNIX 时间戳。 |
HOUR() |
以 0 到 23 之间的整数形式返回时间戳的小时数。 |
MINUTE() |
以 0 到 59 之间的整数形式返回时间戳的分钟数。 |
MONTH() |
以 1 到 12 之间的整数形式返回时间戳的月份。 |
MSEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以毫秒为单位）转换为时间戳。 |
NOW() |
返回当前的 UNIX 时间戳（以微秒为单位）。 |
PARSE_UTC_USEC() |
将日期字符串转换为 UNIX 时间戳（以微秒为单位）。 |
QUARTER() |
返回一个介于 1 到 4 之间的整数，该值表示 TIMESTAMP 属于相应年份中的哪个季度。 |
SEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以秒为单位）转换为 TIMESTAMP。 |
SECOND() |
返回一个介于 0 到 59 之间的整数，该值表示 TIMESTAMP 的秒数部分。 |
STRFTIME_UTC_USEC() |
以 date_format_str 格式返回日期字符串date_format_str。 |
TIME() |
以 %H:%M:%S 格式返回 TIMESTAMP。 |
TIMESTAMP() |
将日期字符串转换为 TIMESTAMP。 |
TIMESTAMP_TO_MSEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以毫秒为单位）。 |
TIMESTAMP_TO_SEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以秒为单位）。 |
TIMESTAMP_TO_USEC() |
将 TIMESTAMP 转换为 UNIX 时间戳（以微秒为单位）。 |
USEC_TO_TIMESTAMP() |
将 UNIX 时间戳（以微秒为单位）转换为 TIMESTAMP。 |
UTC_USEC_TO_DAY() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生日期的开头。 |
UTC_USEC_TO_HOUR() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生时间（小时）的开头。 |
UTC_USEC_TO_MONTH() |
将 UNIX 时间戳（以微秒为单位）转换为该时间戳发生月份的开头。 |
UTC_USEC_TO_WEEK() |
以微秒为单位返回 UNIX 时间戳，表示星期几。 |
UTC_USEC_TO_YEAR() |
以微秒为单位返回 UNIX 时间戳，表示年份。 |
WEEK() |
返回一个介于 1 到 53 之间的整数，该值表示 TIMESTAMP 的周数部分。 |
YEAR() |
返回时间戳的年份。 |
CURRENT_DATE()以
%Y-%m-%d格式返回当前日期的人类可读字符串。示例：
SELECT CURRENT_DATE();返回：2013-02-01
CURRENT_TIME()以
%H:%M:%S格式返回服务器当前时间的人类可读字符串。示例：
SELECT CURRENT_TIME();返回：01:32:56
CURRENT_TIMESTAMP()以
%Y-%m-%d %H:%M:%S格式返回服务器当前时间的时间戳数据类型。示例：
SELECT CURRENT_TIMESTAMP();返回：2013-02-01 01:33:35 世界协调时间 (UTC)
DATE(<timestamp>)以
%Y-%m-%d格式返回时间戳数据类型的人类可读字符串。示例：
SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));返回：2012-10-01
DATE_ADD(<timestamp>,<interval>,
<interval_units>)将指定的时间间隔添加到时间戳数据类型。可能的
interval_units值包括YEAR、MONTH、DAY、HOUR、MINUTE和SECOND。如果interval是负数，则从时间戳数据类型中减去间隔。示例：
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");返回：2017-10-01 02:03:04 世界协调时间 (UTC)
SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");返回时间：2007-10-01 02:03:04 世界协调时间 (UTC)
DATEDIFF(<timestamp1>,<timestamp2>)返回两个时间戳数据类型之间的天数。如果第一个时间戳数据类型在第二个时间戳数据类型之后，则结果为正数，否则结果为负数。
示例：
SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));返回：466
示例：
SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));返回：-466
DAY(<timestamp>)以 1 到 31（含 1 和 31）之间的整数形式返回时间戳数据类型的一个月中的某天。
示例：
SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));返回：2
DAYOFWEEK(<timestamp>)以 1（星期日）到 7（星期六）（含 1 和 7）之间的整数形式返回时间戳数据类型的一周中的某天。
示例：
SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));返回：2
DAYOFYEAR(<timestamp>)以 1 到 366（含 1 和 366）之间的整数形式返回时间戳数据类型的一年中的某天。整数 1 表示 1 月 1 日。
示例：
SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));返回：275
FORMAT_UTC_USEC(<unix_timestamp>)以
YYYY-MM-DD HH:MM:SS.uuuuuu格式返回 UNIX 时间戳的人类可读字符串表示形式。示例：
SELECT FORMAT_UTC_USEC(1274259481071200);返回：2010-05-19 08:58:01.071200
HOUR(<timestamp>)以 0 到 23（包括 0 和 23）之间的整数形式返回时间戳数据类型的小时数。
示例：
SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));返回：5
MINUTE(<timestamp>)以 0 到 59（包括 0 和 59）之间的整数形式返回时间戳数据类型的分钟数。
示例：
SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));返回：23
MONTH(<timestamp>)以 1 到 12（包括 1 和 12）之间的整数形式返回时间戳数据类型的月份。
示例：
SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));返回：10
MSEC_TO_TIMESTAMP(<expr>)- 将 UNIX 时间戳（以毫秒为单位）转换为时间戳数据类型。
示例：
SELECT MSEC_TO_TIMESTAMP(1349053323000);返回：2012-10-01 01:02:03 世界协调时间 (UTC)
SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)返回：2012-10-01 01:02:04 世界协调时间 (UTC)
NOW()以微秒为单位返回当前的 UNIX 时间戳。
示例：
SELECT NOW();返回：1359685811687920
PARSE_UTC_USEC(<date_string>)-
将日期字符串转换为 UNIX 时间戳（以微秒为单位）。
date_string的格式必须为YYYY-MM-DD HH:MM:SS[.uuuuuu]。秒的小数部分最多可达 6 位，也可将其省略。TIMESTAMP_TO_USEC 是一个等效函数，其转换时间戳数据类型参数而不是日期字符串。
示例：
SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");返回：1349056984000000
QUARTER(<timestamp>)以 1 到 4（包括 1 和 4）之间的整数形式返回时间戳数据类型的一年的季度。
示例：
SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));返回：4
SEC_TO_TIMESTAMP(<expr>)将 UNIX 时间戳（以秒为单位）转换为时间戳数据类型。
示例：
SELECT SEC_TO_TIMESTAMP(1355968987);返回：2012-12-20 02:03:07 世界协调时间 (UTC)
SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));返回：2012-12-20 02:03:07 世界协调时间 (UTC)
SECOND(<timestamp>)-
以 0 到 59（包括 0 和 59）之间的整数形式返回时间戳数据类型的秒数。
在一闰秒期间，整数范围介于 0 和 60（包括 0 和 60）之间。
示例：
SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));返回：48
STRFTIME_UTC_USEC(<unix_timestamp>,
<date_format_str>)-
以 date_format_str 格式返回人类可读的字符串。date_format_str 可以包含与日期相关的标点符号（例如 / 和 -）以及 C++ 中的 strftime 函数接受的特殊字符（例如，%d 表示一个月中的某天）。
如果您打算按时间间隔对查询数据进行分组（例如，获取特定月份的所有数据），请使用
UTC_USEC_TO_<function_name>函数，因为这些函数更高效。示例：
SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");返回：2010-05-19
TIME(<timestamp>)以
%H:%M:%S格式返回时间戳数据类型的人类可读字符串。示例：
SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));返回：02:03:04
TIMESTAMP(<date_string>)将日期字符串转换为时间戳数据类型。
示例：
SELECT TIMESTAMP("2012-10-01 01:02:03");返回：2012-10-01 01:02:03 世界协调时间 (UTC)
TIMESTAMP_TO_MSEC(<timestamp>)将时间戳数据类型转换为 UNIX 时间戳（以毫秒为单位）。
示例：
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));返回：1349053323000
TIMESTAMP_TO_SEC(<timestamp>)- 将时间戳数据类型转换为 UNIX 时间戳（以秒为单位）。
示例：
SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));返回：1349053323
TIMESTAMP_TO_USEC(<timestamp>)-
将时间戳数据类型转换为 UNIX 时间戳（以微秒为单位）。
PARSE_UTC_USEC 是一个等效函数，其转换数据字符串参数而不是时间戳数据类型。
示例：
SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));返回：1349053323000000
USEC_TO_TIMESTAMP(<expr>)将 UNIX 时间戳（以微秒为单位）转换为时间戳数据类型。
示例：
SELECT USEC_TO_TIMESTAMP(1349053323000000);返回：2012-10-01 01:02:03 世界协调时间 (UTC)
SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)返回：2012-10-01 01:02:04 世界协调时间 (UTC)
UTC_USEC_TO_DAY(<unix_timestamp>)-
将 UNIX 时间戳（以微秒为单位）移至该时间戳当天的开头。
例如，如果
unix_timestamp出现在 5 月 19 日 08:58，则此函数返回 5 月 19 日 00:00（午夜）的 UNIX 时间戳。示例：
SELECT UTC_USEC_TO_DAY(1274259481071200);返回：1274227200000000
UTC_USEC_TO_HOUR(<unix_timestamp>)-
将 UNIX 时间戳（以微秒为单位）移至该时间戳的小时的开头。
例如，如果
unix_timestamp出现在 08:58，则此函数返回同一天 08:00 的 UNIX 时间戳。示例：
SELECT UTC_USEC_TO_HOUR(1274259481071200);返回：1274256000000000
UTC_USEC_TO_MONTH(<unix_timestamp>)-
将 UNIX 时间戳（以微秒为单位）移至该时间戳的月份的开头。
例如，如果
unix_timestamp出现在 3 月 19 日，则此函数返回同一年 3 月 1 日的 UNIX 时间戳。示例：
SELECT UTC_USEC_TO_MONTH(1274259481071200);返回：1272672000000000
UTC_USEC_TO_WEEK(<unix_timestamp>,
<day_of_week>)-
返回表示
unix_timestamp参数的一周中的某天的 UNIX 时间戳（以微秒为单位）。此函数有两个参数：UNIX 时间戳（以微秒为单位），以及从 0（星期日）到 6（星期六）的一周中的某天。例如，如果
unix_timestamp出现在 2008-04-11，星期五，并且将day_of_week设置为 2（星期二），则该函数返回 2008-04-08，星期二的 UNIX 时间戳。示例：
SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;返回：1207612800000000
UTC_USEC_TO_YEAR(<unix_timestamp>)-
返回表示
unix_timestamp参数的年份的 UNIX 时间戳（以微秒为单位）。例如，如果
unix_timestamp出现在 2010 年，则此函数返回1274259481071200，这是2010-01-01 00:00的微秒表示形式。示例：
SELECT UTC_USEC_TO_YEAR(1274259481071200);返回：1262304000000000
WEEK(<timestamp>)以 1 到 53（包括 1 和 53）之间的整数形式返回时间戳数据类型的周数。
周从星期日开始，所以如果 1 月 1 日不是星期日，则第 1 周少于 7 天，而该年第 1 个星期日是第 2 周的第一天。
示例：
SELECT WEEK(TIMESTAMP('2014-12-31'));返回：53
YEAR(<timestamp>)- 返回时间戳数据类型的年份。
示例：
SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));返回：2012
高级示例
-
将整数时间戳结果转换为人类可读的格式
以下查询查找发生最多维基百科修订的前 5 个时刻。如需以人类可读的格式显示结果，请使用 BigQuery 的
FORMAT_UTC_USEC()函数，该函数使用时间戳（以微秒为单位）作为输入。此查询将维基百科 POSIX 格式的时间戳（以秒为单位）乘以 1000000，以将值转换为微秒。示例：
#legacySQL SELECT /* Multiply timestamp by 1000000 and convert */ /* into a more human-readable format. */ TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5) AS top_revision_time, COUNT (*) AS revision_count FROM [bigquery-public-data:samples.wikipedia];
返回：
+----------------------------+----------------+ | top_revision_time | revision_count | +----------------------------+----------------+ | 2002-02-25 15:51:15.000000 | 20976 | | 2002-02-25 15:43:11.000000 | 15974 | | 2010-02-02 03:34:51.000000 | 3 | | 2010-02-02 01:04:59.000000 | 3 | | 2010-02-01 23:55:05.000000 | 3 | +----------------------------+----------------+
-
按时间戳分组到存储桶
如需将查询结果分组到与特定年、月或日相对应的存储桶中，使用日期和时间函数非常有用。以下示例使用
UTC_USEC_TO_MONTH()函数显示每个维基百科贡献者每月在其修订注释中使用的字符数。示例：
#legacySQL SELECT contributor_username, /* Return the timestamp shifted to the * start of the month, formatted in * a human-readable format. Uses the * 'LEFT()' string function to return only * the first 7 characters of the formatted timestamp. */ LEFT (FORMAT_UTC_USEC( UTC_USEC_TO_MONTH(timestamp * 1000000)),7) AS month, SUM(LENGTH(comment)) as total_chars_used FROM [bigquery-public-data:samples.wikipedia] WHERE (contributor_username != '' AND contributor_username IS NOT NULL) AND timestamp > 1133395200 AND timestamp < 1157068800 GROUP BY contributor_username, month ORDER BY total_chars_used DESC;
返回（截断）：
+--------------------------------+---------+-----------------------+ | contributor_username | month | total_chars_used | +--------------------------------+---------+-----------------------+ | Kingbotk | 2006-08 | 18015066 | | SmackBot | 2006-03 | 7838365 | | SmackBot | 2006-05 | 5148863 | | Tawkerbot2 | 2006-05 | 4434348 | | Cydebot | 2006-06 | 3380577 | etc ...
IP 函数
IP 函数将 IP 地址转换为人类可读的形式。
语法
| IP 函数 | |
|---|---|
FORMAT_IP() |
将 integer_value 的 32 个最低有效位转换为人类可读的 IPv4 地址字符串。 |
PARSE_IP() |
将表示 IPv4 地址的字符串转换为无符号整数值。 |
FORMAT_PACKED_IP() |
以 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f 的形式返回人类可读的 IP 地址。 |
PARSE_PACKED_IP() |
以BYTES形式返回 IP 地址。 |
FORMAT_IP(integer_value)- 将
integer_value的 32 个最低有效位转换为人类可读的 IPv4 地址字符串。例如，FORMAT_IP(1)将返回字符串'0.0.0.1'。 PARSE_IP(readable_ip)- 将表示 IPv4 地址的字符串转换为无符号整数值。例如，
PARSE_IP('0.0.0.1')将返回1。如果字符串不是有效的 IPv4 地址，则PARSE_IP将返回NULL。
BigQuery 支持以网络字节顺序的 4 字节或 16 字节二进制数据形式，将 IPv4 和 IPv6 地址写入压缩字符串中。下面描述的函数支持将地址解析为人类可读形式和从人类可读形式解析为地址。这些函数仅适用于具有 IP 的字符串字段。
语法
FORMAT_PACKED_IP(packed_ip)以
10.1.5.23或2620:0:1009:1:216:36ff:feef:3f的形式返回人类可读的 IP 地址。示例：FORMAT_PACKED_IP('0123456789@ABCDE')会返回'3031:3233:3435:3637:3839:4041:4243:4445'FORMAT_PACKED_IP('0123')返回'48.49.50.51'
PARSE_PACKED_IP(readable_ip)以BYTES形式返回 IP 地址。如果输入字符串不是有效的 IPv4 或 IPv6 地址，则
PARSE_PACKED_IP将返回NULL。示例：PARSE_PACKED_IP('48.49.50.51')会返回'MDEyMw=='PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445')会返回'MDEyMzQ1Njc4OUBBQkNERQ=='
JSON 函数
通过使用类 JSONPath 表达式，BigQuery 的 JSON 函数可在存储的 JSON 数据中查找值。
存储 JSON 数据比声明表架构中的所有单个字段更灵活，但可能成本更高。当您从 JSON 字符串中选择数据时，扫描整个字符串会产生费用，而相较于让每个字段位于单独的列中，这样做的成本更高。此外，由于系统在查询时需要解析整个字符串，因此查询的执行速度也会较慢。但对于临时或快速变化的架构，值得花费额外的费用来实现 JSON 的灵活性。
如需处理结构化数据，请使用 JSON 函数而不是 BigQuery 的正则表达式函数，因为 JSON 函数更易于使用。
语法
| JSON 函数 | |
|---|---|
JSON_EXTRACT() |
根据 JSONPath 表达式选择值并返回 JSON 字符串。 |
JSON_EXTRACT_SCALAR() |
根据 JSONPath 表达式选择值并返回 JSON 标量。 |
JSON_EXTRACT(json, json_path)-
根据 JSONPath 表达式
json_path选择json中的值。json_path必须是字符串常量。以 JSON 字符串格式返回值。 JSON_EXTRACT_SCALAR(json, json_path)-
根据 JSONPath 表达式
json_path选择json中的值。json_path必须是字符串常量。返回标量 JSON 值。
逻辑运算符
逻辑运算符对表达式执行二元或三元逻辑。二元逻辑返回 true 或 false。三元逻辑提供 NULL 值且返回 true、false 或 NULL。
语法
| 逻辑运算符 | |
|---|---|
expr AND expr |
如果两个表达式都为 true，则返回 true。 |
expr OR expr |
如果一个或两个表达式为 true，则返回 true。 |
NOT expr |
如果表达式为 false，则返回 true。 |
expr AND expr- 如果两个表达式都为 true，则返回
true。 - 如果一个或两个表达式为 false，则返回
false。 - 如果两个表达式都为 NULL 或一个表达式为 true 且另一个表达式为 NULL，则返回
NULL。
- 如果两个表达式都为 true，则返回
expr OR expr- 如果一个或两个表达式为 true，则返回
true。 - 如果两个表达式都为 false，则返回
false。 - 如果两个表达式都为 NULL 或一个表达式为 false 且另一个表达式为 NULL，则返回
NULL。
- 如果一个或两个表达式为 true，则返回
NOT expr- 如果表达式为 false，则返回
true。 - 如果表达式为 true，则返回
false。 - 如果表达式为 NULL，则返回
NULL。
可以将
NOT作为否定运算符与其他函数一起使用。例如NOT IN(expr1, expr2)或IS NOT NULL。- 如果表达式为 false，则返回
数学函数
数学函数采用数字参数且返回数字结果。每个参数可以是数字文本或查询返回的数值。如果数学函数计算出未定义的结果，则运算返回 NULL。
语法
| 数学函数 | |
|---|---|
ABS() |
返回参数的绝对值。 |
ACOS() |
返回参数的反余弦值。 |
ACOSH() |
返回参数的反双曲余弦值。 |
ASIN() |
返回参数的反正弦值。 |
ASINH() |
返回参数的反双曲正弦值。 |
ATAN() |
返回参数的反正切值。 |
ATANH() |
返回参数的反双曲正切值。 |
ATAN2() |
返回两个参数的反正切值。 |
CEIL() |
将参数向上舍入到最接近的整数并返回舍入后的值。 |
COS() |
返回参数的余弦值。 |
COSH() |
返回参数的双曲余弦值。 |
DEGREES() |
从弧度转换为角度。 |
EXP() |
返回 e 的参数次幂。 |
FLOOR() |
将参数向下舍入到最接近的整数。 |
LN()LOG()
|
返回参数的自然对数。 |
LOG2() |
返回参数以 2 为底的对数。 |
LOG10() |
返回参数以 10 为底的对数。 |
PI() |
返回常数 π。 |
POW() |
返回第一个参数的第二个参数次幂。 |
RADIANS() |
从角度转换为弧度。 |
RAND() |
返回 0.0 <= 值 < 1.0 范围内的随机浮点值。 |
ROUND() |
将参数向上或向下舍入到最接近的整数。 |
SIN() |
返回参数的正弦值。 |
SINH() |
返回参数的双曲正弦值。 |
SQRT() |
返回表达式的平方根。 |
TAN() |
返回参数的正切值。 |
TANH() |
返回参数的双曲正切值。 |
ABS(numeric_expr)- 返回参数的绝对值。
ACOS(numeric_expr)- 返回参数的反余弦值。
ACOSH(numeric_expr)- 返回参数的反双曲余弦值。
ASIN(numeric_expr)- 返回参数的反正弦值。
ASINH(numeric_expr)- 返回参数的反双曲正弦值。
ATAN(numeric_expr)- 返回参数的反正切值。
ATANH(numeric_expr)- 返回参数的反双曲正切值。
ATAN2(numeric_expr1, numeric_expr2)- 返回两个参数的反正切值。
CEIL(numeric_expr)- 将参数向上舍入到最接近的整数并返回舍入后的值。
COS(numeric_expr)- 返回参数的余弦值。
COSH(numeric_expr)- 返回参数的双曲余弦值。
DEGREES(numeric_expr)- 返回
numeric_expr（从弧度转换为角度）。 EXP(numeric_expr)- 返回常数“e”（自然对数的底数）的 numeric_exprnumeric_expr 次幂的结果。
FLOOR(numeric_expr)- 将参数向下舍入到最接近的整数并返回舍入后的值。
LN(numeric_expr)
LOG(numeric_expr)- 返回参数的自然对数。
LOG2(numeric_expr)- 返回参数以 2 为底的对数。
LOG10(numeric_expr)- 返回参数以 10 为底的对数。
PI()- 返回常数 π。
PI()函数需要括号来表示其为函数，但这些括号中不包含参数。您可以将PI()作为常数与数学函数和算术函数一起使用。 POW(numeric_expr1, numeric_expr2)- 返回
numeric_expr1的numeric_expr2次幂的结果。 RADIANS(numeric_expr)- 返回
numeric_expr（从角度转换为弧度）。（注意：弧度 π 等于角度 180 度）。 RAND([int32_seed])- 返回 0.0 <= 值 < 1.0 范围内的随机浮点值。只要不使用
LIMIT子句，每个int32_seed值始终会在给定查询中生成相同的随机数序列。如果未指定int32_seed，BigQuery 将使用当前时间戳作为种子值。 ROUND(numeric_expr [, digits])- 将参数向上或向下舍入到最接近的整数（或者如果指定，向上或向下舍入到指定的位数）并返回舍入后的值。
SIN(numeric_expr)- 返回参数的正弦值。
SINH(numeric_expr)- 返回参数的双曲正弦值。
SQRT(numeric_expr)- 返回表达式的平方根。
TAN(numeric_expr)- 返回参数的正切值。
TANH(numeric_expr)- 返回参数的双曲正切值。
高级示例
-
边界框查询
以下查询以旧金山 (37.46, -122.50) 为中心返回其矩形边界框内的一组点。
示例：
#legacySQL SELECT year, month, AVG(mean_temp) avg_temp, MIN(min_temperature) min_temp, MAX(max_temperature) max_temp FROM [weather_geo.table] WHERE /* Return values between a pair of */ /* latitude and longitude coordinates */ lat / 1000 > 37.46 AND lat / 1000 < 37.65 AND long / 1000 > -122.50 AND long / 1000 < -122.30 GROUP BY year, month ORDER BY year, month ASC;
-
近似边界圆查询
以科罗拉多州丹佛 (39.73, -104.98) 为中心，使用球面余弦定律在其周围确定一个近似边界圆，然后返回该边界圆中的一组点（最多 100 个点）。此查询使用 BigQuery 的数学函数和三角函数，例如
PI()、SIN()、COS()。由于地球并非绝对球体，而经纬度会在极点交会，因此这项查询返回的只是近似值，但对于许多数据类型来说或许很实用。
示例：
#legacySQL SELECT distance, lat, long, temp FROM (SELECT ((ACOS(SIN(39.73756700 * PI() / 180) * SIN((lat/1000) * PI() / 180) + COS(39.73756700 * PI() / 180) * COS((lat/1000) * PI() / 180) * COS((-104.98471790 - (long/1000)) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance, AVG(mean_temp) AS temp, AVG(lat/1000) lat, AVG(long/1000) long FROM [weather_geo.table] WHERE month=1 GROUP BY distance) WHERE distance < 100 ORDER BY distance ASC LIMIT 100;
正则表达式函数
BigQuery 使用 re2 库提供正则表达式支持；请参阅相关文档了解其正则表达式语法。
注意：正则表达式是全局匹配；如需在字词的开头匹配，必须使用 ^ 字符。
语法
| 正则表达式函数 | |
|---|---|
REGEXP_MATCH() |
如果参数与正则表达式匹配，则返回 true。 |
REGEXP_EXTRACT() |
返回与正则表达式中的捕获组匹配的参数部分。 |
REGEXP_REPLACE() |
替换与正则表达式匹配的子字符串。 |
REGEXP_MATCH('str', 'reg_exp')如果 str 与正则表达式匹配，则返回 true。对于没有正则表达式的字符串匹配，请使用 CONTAINS 而不是 REGEXP_MATCH。
示例：
#legacySQL SELECT word, COUNT(word) AS count FROM [bigquery-public-data:samples.shakespeare] WHERE (REGEXP_MATCH(word,r'\w\w\'\w\w')) GROUP BY word ORDER BY count DESC LIMIT 3;
返回：
+-------+-------+ | word | count | +-------+-------+ | ne'er | 42 | | we'll | 35 | | We'll | 33 | +-------+-------+
REGEXP_EXTRACT('str', 'reg_exp')返回与正则表达式中的捕获组匹配的 str 部分str。
示例：
#legacySQL SELECT REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment FROM [bigquery-public-data:samples.shakespeare] GROUP BY fragment ORDER BY fragment LIMIT 3;
返回：
+----------+ | fragment | +----------+ | NULL | | Al'ce | | As'es | +----------+
REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')返回一个字符串，其中系统会将任何与 reg_exp 匹配的 orig_str 子字符串都替换为 replace_str。例如，REGEXP_REPLACE ('Hello', 'lo', 'p') 返回 Help。
示例：
#legacySQL SELECT REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word FROM [bigquery-public-data:samples.shakespeare] WHERE REGEXP_MATCH(word, r'ne\'er') GROUP BY expanded_word ORDER BY expanded_word LIMIT 5;
返回：
+---------------+ | expanded_word | +---------------+ | Whenever | | never | | nevertheless | | whenever | +---------------+
高级示例
-
按正则表达式匹配过滤结果集
BigQuery 的正则表达式函数可用于过滤
WHERE子句中的结果，还可显示SELECT中的结果。以下示例将这两个正则表达式用例组合到一个查询中。示例：
#legacySQL SELECT /* Replace white spaces in the title with underscores. */ REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions FROM (SELECT title, COUNT(revision_id) as revisions FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace=0 /* Match titles that start with 'G', end with * 'e', and contain at least two 'o's. */ AND REGEXP_MATCH(title, r'^G.*o.*o.*e$') GROUP BY title ORDER BY revisions DESC LIMIT 100);
-
对整数或浮点数据使用正则表达式
虽然 BigQuery 的正则表达式函数仅适用于字符串数据，但可以使用
STRING()函数将整数或浮点数据强制转换为字符串格式。在此示例中，STRING()用于将整数值corpus_date强制转换为字符串，然后由REGEXP_REPLACE更改。示例：
#legacySQL SELECT corpus_date, /* Cast the corpus_date to a string value */ REGEXP_REPLACE(STRING(corpus_date), '^16', 'Written in the sixteen hundreds, in the year \'' ) AS date_string FROM [bigquery-public-data:samples.shakespeare] /* Cast the corpus_date to string, */ /* match values that begin with '16' */ WHERE REGEXP_MATCH(STRING(corpus_date), '^16') GROUP BY corpus_date, date_string ORDER BY date_string DESC LIMIT 5;
字符串函数
字符串函数对字符串数据进行运算。字符串常量必须用单引号或双引号括起来。默认情况下，字符串函数区分大小写。
可以将 IGNORE CASE 附加到查询的末尾，以实现不区分大小写的匹配。IGNORE CASE 仅适用于 ASCII 字符，且仅适用于顶级查询。
这些函数不支持通配符；如需使用正则表达式功能，请使用正则表达式函数。
语法
| 字符串函数 | |
|---|---|
CONCAT() |
返回两个或多个字符串的串联，如果任何值为 NULL，则返回 NULL。 |
expr CONTAINS 'str' |
如果 expr 包含指定的字符串参数，则返回 true。 |
INSTR() |
返回第一次出现的字符串的从 1 开始的索引。 |
LEFT() |
返回字符串的最左侧字符。 |
LENGTH() |
返回字符串的长度。 |
LOWER() |
返回所有字符都是小写的原始字符串。 |
LPAD() |
在字符串的左侧插入字符。 |
LTRIM() |
移除字符串左侧的字符。 |
REPLACE() |
替换所有出现的子字符串。 |
RIGHT() |
返回字符串最右侧的字符。 |
RPAD() |
在字符串的右侧插入字符。 |
RTRIM() |
移除字符串右侧的尾随字符。 |
SPLIT() |
将字符串拆分为重复的子字符串。 |
SUBSTR() |
返回子字符串… |
UPPER() |
返回所有字符都是大写的原始字符串。 |
CONCAT('str1', 'str2', '...')
str1 + str2 + ...- 返回两个或多个字符串的串联，如果任何值为 NULL，则返回 NULL。示例：如果
str1为Java且str2为Script，则CONCAT会返回JavaScript。 expr CONTAINS 'str'- 如果
expr包含指定的字符串参数，则返回true。这是一个区分大小写的比较。 INSTR('str1', 'str2')- 返回 str1 中第一次出现 str2 的索引（从 1 开始），或者如果 str2 不在 str1 中出现，则返回 0。
LEFT('str', numeric_expr)- 返回
str最左侧的 numeric_exprnumeric_expr 个字符。如果数字比 str 长，则将返回完整的字符串str。示例：LEFT('seattle', 3)会返回sea。 LENGTH('str')- 返回代表字符串长度的数值。示例：如果
str为'123456'，LENGTH会返回6。 LOWER('str')- 返回所有字符都是小写的原始字符串。
LPAD('str1', numeric_expr, 'str2')- 在
str1的左侧填充str2并重复填充str2，直到结果字符串正好是numeric_expr个字符。示例：LPAD('1', 7, '?')会返回??????1。 LTRIM('str1' [, str2])-
从 str1 的左侧移除字符str1。如果省略 str2，
LTRIM将移除 str1 左侧的空格。否则，LTRIM将从 str1 左侧移除 str2 中的任何字符（区分大小写）。示例：
SELECT LTRIM("Say hello", "yaS")会返回" hello"。SELECT LTRIM("Say hello", " ySa")会返回"hello"。 REPLACE('str1', 'str2', 'str3')-
用 str3 替换 str1 中 str2 的所有实例。
RIGHT('str', numeric_expr)- 返回
str最右侧的 numeric_exprnumeric_expr 个字符。如果数字比字符串长，将返回整个字符串。示例：RIGHT('kirkland', 4)会返回land。 RPAD('str1', numeric_expr, 'str2')- 在
str1的右侧填充str2并重复填充str2，直到结果字符串正好是numeric_expr个字符。示例：RPAD('1', 7, '?')会返回1??????。 RTRIM('str1' [, str2])-
从 str1 的右侧移除尾随字符str1。如果省略 str2，
RTRIM将移除 str1 的尾随空格。否则，RTRIM将从 str1 右侧移除 str2 中的任何字符（区分大小写）。示例：
SELECT RTRIM("Say hello", "leo")会返回"Say h"。SELECT RTRIM("Say hello ", " hloe")会返回"Say"。 SPLIT('str' [, 'delimiter'])- 将字符串拆分为重复的子字符串。如果指定了
delimiter，则SPLIT函数会将str拆分为多个子字符串，并使用delimiter作为分隔符。 SUBSTR('str', index [, max_len])- 从
index开始返回str的子字符串。如果使用了可选的max_len参数，则返回的字符串最多包含max_len个字符。计数从 1 开始，因此字符串中的第一个字符位于第 1（而非 0）位。如果index为5，则子字符串以str左起第 5 个字符为开头。如果index为-4，则子字符串以str右起第 4 个字符为开头。示例：SUBSTR('awesome', -4, 4)会返回子字符串some。 UPPER('str')- 返回所有字符都是大写的原始字符串。
转义字符串中的特殊字符
如需转义特殊字符，请使用以下方法之一：
- 使用
'\xDD'表示法，其中'\x'后跟字符的两位十六进制表示形式。 - 在斜杠、单引号和双引号前面使用转义斜杠。
- 对其他字符使用 C 语言风格的序列（
'\a', '\b', '\f', '\n', '\r', '\t',和'\v'）。
一些转义的示例：
'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported
表通配符函数
表通配符函数是查询特定表集的数据的一种便捷方式。表通配符函数等效于通配符函数匹配的所有表的逗号分隔联合。当使用表通配符函数时，BigQuery 仅访问与通配符匹配的表且仅对这些表收费。表通配符函数在查询的 FROM 子句中指定。
如果在查询中使用表通配符函数，则不再需要将这些函数包含在括号中。例如，以下一些示例使用括号，而其他示例则不使用括号。
使用通配符函数的针对多个表的查询不支持缓存的结果（即使选中了使用缓存的结果选项）。如果多次运行相同的通配符查询，您要为每个相应查询付费。
语法
| 表通配符函数 | |
|---|---|
TABLE_DATE_RANGE() |
查询跨日期范围的多个日常表。 |
TABLE_DATE_RANGE_STRICT() |
查询跨日期范围的多个日常表，且不缺少日期。 |
TABLE_QUERY() |
查询名称与指定谓词匹配的表。 |
TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)-
查询时间范围与
<timestamp1>到<timestamp2>这段时间重叠的日常表。表名必须采用
<prefix><day>格式，其中<day>的格式为YYYYMMDD。您可以使用日期和时间函数来生成时间戳参数。例如：
TIMESTAMP('2012-10-01 02:03:04')DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')
示例：获取两天之间的表
此示例假定存在以下表：
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
#legacySQL SELECT name FROM TABLE_DATE_RANGE([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27')) WHERE age >= 35
匹配以下表：
- mydata.people20140325
- mydata.people20140326
- mydata.people20140327
示例：获取截至“现在”两天范围内的表
此示例假定名为
myproject-1234的项目中存在以下表：- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
#legacySQL SELECT name FROM (TABLE_DATE_RANGE([myproject-1234:mydata.people], DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'), CURRENT_TIMESTAMP())) WHERE age >= 35
匹配以下表：
- mydata.people20140323
- mydata.people20140324
- mydata.people20140325
TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)-
此函数等效于
TABLE_DATE_RANGE。唯一的区别是，如果序列中缺少任何日常表，TABLE_DATE_RANGE_STRICT会失败并返回Not Found: Table <table_name>错误。示例：因缺少表而发生错误
此示例假定存在以下表：
- people20140325
- people20140327
#legacySQL SELECT name FROM (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people], TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))) WHERE age >= 35
上面的示例返回表“people20140326”的错误“未找到”。
TABLE_QUERY(dataset, expr)-
查询名称与提供的
expr匹配的表。expr参数必须表示为字符串，并且必须包含要计算的表达式。例如'length(table_id) < 3'。示例：匹配名称中包含“oo”且长度大于 4 的表
此示例假定存在以下表：
- mydata.boo
- mydata.fork
- mydata.ooze
- mydata.spoon
#legacySQL SELECT speed FROM (TABLE_QUERY([myproject-1234:mydata], 'table_id CONTAINS "oo" AND length(table_id) >= 4'))
匹配以下表：
- mydata.ooze
- mydata.spoon
示例：匹配名称以“boo”开头，后跟 3-5 个数字的表
此示例假定名为
myproject-1234的项目中存在以下表：- mydata.book4
- mydata.book418
- mydata.boom12345
- mydata.boom123456789
- mydata.taboo999
#legacySQL SELECT speed FROM TABLE_QUERY([myproject-1234:mydata], 'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')
匹配以下表：
- mydata.book418
- mydata.boom12345
网址函数
语法
| 网址函数 | |
|---|---|
HOST() |
对于给定网址，以字符串形式返回主机名。 |
DOMAIN() |
对于给定网址，以字符串形式返回网域。 |
TLD() |
对于给定网址，返回网址中的顶级域名和任何国家/地区域名。 |
HOST('url_str')- 对于给定网址，以字符串形式返回主机名。示例：HOST('http://www.google.com:80/index.html') 会返回 'www.google.com'
DOMAIN('url_str')- 对于给定网址，以字符串形式返回网域。示例：DOMAIN('http://www.google.com:80/index.html') 会返回 'google.com'。
TLD('url_str')- 对于给定网址，返回网址中的顶级域名和任何国家/地区域名。 示例：TLD('http://www.google.com:80/index.html') 会返回 '.com'。 TLD('http://www.google.co.uk:80/index.html') 会返回 '.co.uk'。
注意：
- 这些函数不执行反向 DNS 查找，因此如果使用 IP 地址调用这些函数，函数将返回 IP 地址的段，而不是主机名的段。
- 所有网址解析函数都需要小写字符。网址中的大写字符将导致 NULL 或其他不正确的结果。如果数据混杂了大小写，请考虑通过 LOWER() 将输入传递到此函数。
高级示例
从网址数据中解析域名
此查询使用 DOMAIN() 函数返回 GitHub 上列为代码库首页的最热门网域。注意：使用 HAVING 通过 DOMAIN() 函数的结果过滤记录。此函数对于确定网址数据中的引荐来源网址信息非常有用。
示例：
#legacySQL SELECT DOMAIN(repository_homepage) AS user_domain, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_domain HAVING user_domain IS NOT NULL AND user_domain != '' ORDER BY activity_count DESC LIMIT 5;
返回：
+-----------------+----------------+ | user_domain | activity_count | +-----------------+----------------+ | github.com | 281879 | | google.com | 34769 | | khanacademy.org | 17316 | | sourceforge.net | 15103 | | mozilla.org | 14091 | +-----------------+----------------+
如需专门查看 TLD 信息，请使用 TLD() 函数。此示例显示常见示例列表以外的排在最前面的 TLD。
#legacySQL SELECT TLD(repository_homepage) AS user_tld, COUNT(*) AS activity_count FROM [bigquery-public-data:samples.github_timeline] GROUP BY user_tld HAVING /* Only consider TLDs that are NOT NULL */ /* or in our list of common TLDs */ user_tld IS NOT NULL AND NOT user_tld IN ('','.com','.net','.org','.info','.edu') ORDER BY activity_count DESC LIMIT 5;
返回：
+----------+----------------+ | user_tld | activity_count | +----------+----------------+ | .de | 22934 | | .io | 17528 | | .me | 13652 | | .fr | 12895 | | .co.uk | 9135 | +----------+----------------+
窗口函数
窗口函数（也称为分析函数）支持对结果集的特定子集（或“窗口”）进行计算。借助窗口函数，可以更轻松地创建包括复杂分析（例如过去平均值和运行总计）的报告。
每个窗口函数都需要一个指定窗口顶部和底部的 OVER 子句。OVER 子句的三个部分（分区、排序和框架）提供对窗口的额外控制。通过分区可以将输入数据划分为具有共同特征的逻辑组。借助排序功能，可以在分区内将结果排序。借助框架功能，可以在分区内创建相对于当前行移动的滑动窗口框架。可以基于许多行或一系列值（例如时间间隔）配置移动窗口框架的大小。
#legacySQL SELECT <window_function> OVER ( [PARTITION BY <expr>] [ORDER BY <expr> [ASC | DESC]] [<window-frame-clause>] )
PARTITION BY- 定义此函数执行运算的基本分区。
指定一个或多个以英文逗号分隔的列名；将为这些列的每个不同值集创建一个分区，类似于
GROUP BY子句。如果省略PARTITION BY，则基本分区为窗口函数输入中的所有行。 PARTITION BY子句还允许窗口函数对数据进行分区且并行执行。如需将窗口函数用于allowLargeResults，或者将更多联接或聚合应用于窗口函数的输出，请使用PARTITION BY来并行执行。- 不能对窗口函数的输出使用
JOIN EACH和GROUP EACH BY子句。如需在使用窗口函数时生成大量查询结果，您必须使用PARTITION BY。 ORDER BY- 对分区进行排序。如果缺少
ORDER BY，则无法保证任何默认排序顺序。排序在应用任何窗口框架子句之前在分区级别进行。如果指定RANGE窗口，则应添加ORDER BY子句。默认顺序为ASC。 ORDER BY在某些情况下是可选的，但某些窗口函数（例如 rank() 或 dense_rank()）必须使用该子句。- 如果在未指定
ROWS或RANGE的情况下使用ORDER BY，则ORDER BY表示窗口从分区的开头扩展到当前行。在缺少ORDER BY子句的情况下，窗口为整个分区。 <window-frame-clause>-
{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>} - 需要执行运算的分区的子集。这可以等于或小于分区的大小。如果在不使用
window-frame-clause的情况下使用ORDER BY，则默认窗口框架为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果省略ORDER BY和window-frame-clause，则默认窗口框架为整个分区。ROWS- 相对于当前行，根据行位置定义窗口。例如，如需添加显示前 5 行工资值总和的列，需要查询SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)。行集通常包括当前行，但这不是必需的。RANGE- 相对于当前行中该列的值，根据给定列中的一系列值定义窗口。 仅对数字和日期执行运算，其中日期值是简单整数（从计时原点到现在的微秒数）。 具有相同值的相邻行称为对等行。CURRENT ROW的对等行包括在指定CURRENT ROW的窗口框架中。例如，如果将窗口末尾指定为CURRENT ROW并且窗口中的下一行具有相同的值，则该行将包括在函数计算中。BETWEEN <start> AND <end>- 一个范围，包括开头行和末尾行。范围不需要包括当前行，但<start>必须先于或等于<end>。<start>- 指定此窗口相对于当前行的起始偏移量。支持的选项如下：{UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}<expr>是正整数，PRECEDING表示前面的行号或范围值，FOLLOWING表示后面的行号或范围值。UNBOUNDED PRECEDING表示分区的第一行。如果开头在窗口之前，它将被设置为分区的第一行。<end>- 指定此窗口相对于当前行的结束偏移量。支持的选项如下：{UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}<expr>是正整数，PRECEDING表示前面的行号或范围值，FOLLOWING表示后面的行号或范围值。UNBOUNDED FOLLOWING表示分区的最后一行。如果结尾超出窗口的末尾，它将被设置为分区的最后一行。
与将许多输入行收起为一个输出行的聚合函数不同，窗口函数为每个输入行返回一个输出行。
借助此函数，可以更轻松地创建计算运行总计和移动平均值的查询。 例如，以下查询返回 SELECT 语句定义的五行小数据集的运行总计：
#legacySQL SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
返回值：
+------+-------+--------------+ | name | value | RunningTotal | +------+-------+--------------+ | a | 0 | 0 | | b | 1 | 1 | | c | 2 | 3 | | d | 3 | 6 | | e | 4 | 10 | +------+-------+--------------+
以下示例计算当前行及其前一行中的值的移动平均值。窗口框架包括与当前行一起移动的两行。
#legacySQL SELECT name, value, AVG(value) OVER (ORDER BY value ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS MovingAverage FROM (SELECT "a" AS name, 0 AS value), (SELECT "b" AS name, 1 AS value), (SELECT "c" AS name, 2 AS value), (SELECT "d" AS name, 3 AS value), (SELECT "e" AS name, 4 AS value);
返回值：
+------+-------+---------------+ | name | value | MovingAverage | +------+-------+---------------+ | a | 0 | 0.0 | | b | 1 | 0.5 | | c | 2 | 1.5 | | d | 3 | 2.5 | | e | 4 | 3.5 | +------+-------+---------------+
语法
| 窗口函数 | |
|---|---|
AVG()COUNT(*)COUNT([DISTINCT])MAX()MIN()STDDEV()SUM() |
与相应的聚合函数的运算相同，但是通过由 OVER 子句定义的窗口执行计算。 |
CUME_DIST() |
返回一个双精度浮点值，表示一组值中某个值的累积分布… |
DENSE_RANK() |
返回一组值中某个值的整数排名。 |
FIRST_VALUE() |
返回窗口中指定字段的第一个值。 |
LAG() |
允许从窗口中的上一行读取数据。 |
LAST_VALUE() |
返回窗口中指定字段的最后一个值。 |
LEAD() |
让您可读取窗口中的下一行数据。 |
NTH_VALUE() |
返回窗口框架中位置 <n> 处的 <expr> 值...
|
NTILE() |
将窗口划分为指定数量的区间。 |
PERCENT_RANK() |
返回当前行相对于分区中的其他行的排名。 |
PERCENTILE_CONT() |
返回内插值，该值将映射到相对于窗口中其它值的百分位参数… |
PERCENTILE_DISC() |
返回窗口中最接近参数百分位数的值。 |
RANK() |
返回一组值中值的整数排名。 |
RATIO_TO_REPORT() |
返回每个值与值之和的比率。 |
ROW_NUMBER() |
返回查询结果在窗口中的当前行号。 |
-
AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)
- 这些窗口函数执行与相应的聚合函数相同的运算，但是对 OVER 子句定义的窗口执行计算。
另一个显著区别是
COUNT([DISTINCT] field)函数在用作窗口函数时会生成精确结果，行为类似于EXACT_COUNT_DISTINCT()聚合函数。在示例查询中，
ORDER BY子句使窗口从分区的开头计算到当前行，进而生成该年的累积总和。#legacySQL SELECT corpus_date, corpus, word_count, SUM(word_count) OVER ( PARTITION BY corpus_date ORDER BY word_count) annual_total FROM [bigquery-public-data:samples.shakespeare] WHERE word='love' ORDER BY corpus_date, word_count
返回：
corpus_date corpus word_count annual_total 0 various 37 37 0 sonnets 157 194 1590 2kinghenryvi 18 18 1590 1kinghenryvi 24 42 1590 3kinghenryvi 40 82 CUME_DIST()-
使用公式
<number of rows preceding or tied with the current row> / <total rows>进行计算并返回一个双精度浮点值，指示一组值中某个值的累积分布。并列的值返回相同的累积分布值。此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
返回：
word word_count cume_dist handkerchief 29 0.2 satisfaction 5 0.4 displeasure 4 0.8 instruments 4 0.8 circumstance 3 1.0 DENSE_RANK()-
返回一组值中某个值的整数排名。排名是根据与这组值中其他值的比较结果计算而来。
相等值的排名会相同。下一个值的排名会按 1 递增。例如，如果两个值的排名都是 2，则下一个排名值是 3。如果您想在排名列表中出现间隔，请使用 rank()。
此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count dense_rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 4 FIRST_VALUE(<field_name>)-
返回窗口中
<field_name>的第一个值。#legacySQL SELECT word, word_count, FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
word word_count fv imperfectly 1 imperfectly LAG(<expr>[, <offset>[, <default_value>]])-
让您可读取窗口中的上一行数据。 具体而言，
LAG()会针对当前行之前的第<offset>行返回<expr>的值。如果该行不存在，则返回<default_value>。#legacySQL SELECT word, word_count, LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
返回：
word word_count lag handkerchief 29 null satisfaction 5 handkerchief displeasure 4 satisfaction instruments 4 displeasure circumstance 3 instruments LAST_VALUE(<field_name>)-
返回窗口中
<field_name>的最后一个值。#legacySQL SELECT word, word_count, LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 1
返回：
word word_count lv imperfectly 1 imperfectly LEAD(<expr>[, <offset>[, <default_value>]])-
让您可读取窗口中的下一行数据。 具体而言，
LEAD()会针对当前行之后的第<offset>行返回<expr>的值。如果该行不存在，则返回<default_value>。#legacySQL SELECT word, word_count, LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count lead handkerchief 29 satisfaction satisfaction 5 displeasure displeasure 4 instruments instruments 4 circumstance circumstance 3 null NTH_VALUE(<expr>, <n>)-
返回窗口框架中位置
<n>处的<expr>值，其中<n>是从 1 开始的索引。 NTILE(<num_buckets>)-
将一系列行划分为
<num_buckets>个区间，并以整数形式针对每个行分配相应的分区编号。ntile()函数会尽可能平均地分配区间编号，并针对每个行返回一个介于 1 到<num_buckets>之间的值。#legacySQL SELECT word, word_count, NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count ntile handkerchief 29 1 satisfaction 5 1 displeasure 4 1 instruments 4 2 circumstance 3 2 PERCENT_RANK()-
返回当前行相对于分区中其他行的排名。返回值的范围在 0 和 1 之间，包括 0 和 1。第一个返回值是 0.0。
此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_rank handkerchief 29 0.0 satisfaction 5 0.25 displeasure 4 0.5 instruments 4 0.5 circumstance 3 1.0 PERCENTILE_CONT(<percentile>)-
在按
ORDER BY子句对其进行排序后，返回一个内插值，该值将映射到相对于窗口中其它值的百分位参数。<percentile>必须介于 0 到 1 之间。此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_cont handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 PERCENTILE_DISC(<percentile>)-
返回窗口中最接近参数百分位数的值。
<percentile>必须介于 0 到 1 之间。此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count p_disc handkerchief 29 4 satisfaction 5 4 displeasure 4 4 instruments 4 4 circumstance 3 4 RANK()-
返回一组值中某个值的整数排名。排名是根据与这组值中其他值的比较结果计算而来。
相等值的排名会相同。下一个值的排名根据在其之前出现的并列值的数量递增。例如，如果两个值排名都是 2，则下一个排名值是 4，而不是 3。如果不想在排名列表中出现间隔，请使用 dense_rank()。
此窗口函数的
OVER子句中需要使用ORDER BY。#legacySQL SELECT word, word_count, RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count rank handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 3 circumstance 3 5 RATIO_TO_REPORT(<column>)-
以 0 到 1 之间的双精度浮点值形式，返回每个值在值总和中所占的比率。
#legacySQL SELECT word, word_count, RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count r_to_r handkerchief 29 0.6444444444444445 satisfaction 5 0.1111111111111111 displeasure 4 0.08888888888888889 instruments 4 0.08888888888888889 circumstance 3 0.06666666666666667 ROW_NUMBER()-
返回窗口中查询结果的当前行号，从 1 开始。
#legacySQL SELECT word, word_count, ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num, FROM [bigquery-public-data:samples.shakespeare] WHERE corpus='othello' and length(word) > 10 LIMIT 5
word word_count row_num handkerchief 29 1 satisfaction 5 2 displeasure 4 3 instruments 4 4 circumstance 3 5
其他函数
语法
| 其他函数 | |
|---|---|
CASE WHEN ... THEN |
使用 CASE 在查询的两个或多个备用表达式中进行选择。 |
CURRENT_USER() |
返回运行查询的用户的电子邮件地址。 |
EVERY() |
如果其所有输入的参数都为 true，则返回 true。 |
FROM_BASE64() |
将以 base-64 编码的输入字符串转换为字节格式。 |
HASH() |
计算并返回 64 位有符号哈希值… |
FARM_FINGERPRINT() |
计算并返回 64 位有符号指纹值… |
IF() |
如果第一个参数为 true，则返回第二个参数；否则返回第三个参数。 |
POSITION() |
返回参数在序列中的位置（从 1 开始）。 |
SHA1() |
以字节格式返回 SHA1 哈希值。 |
SOME() |
如果参数至少有一个输入为 true，则返回 true。 |
TO_BASE64() |
将 BYTES 参数转换为采用 base-64 编码的字符串。 |
CASE WHEN when_expr1 THEN then_expr1
WHEN when_expr2 THEN then_expr2 ...
ELSE else_expr END- 使用 CASE 在查询的两个或多个备用表达式中进行选择。WHEN 表达式必须是布尔值，而 THEN 子句和 ELSE 子句中的所有表达式都必须是兼容的类型。
CURRENT_USER()- 返回运行查询的用户的电子邮件地址。
EVERY(<condition>)- 如果
condition的所有输入都为 true，则返回true。与OMIT IF子句一起使用时，此函数对涉及重复字段的查询很实用。 FROM_BASE64(<str>)- 将采用 base64 编码的输入字符串
str转换为BYTES格式。如需将字节转换为 base64 编码的字符串，请使用 TO_BASE64()。 HASH(expr)- 根据 CityHash 库（1.0.3 版）的定义，针对
expr的字节计算并返回 64 位有符号哈希值。支持任何字符串或整数表达式，且此函数对字符串遵守IGNORE CASE，返回大小写不变的值。 FARM_FINGERPRINT(expr)- 使用开源 FarmHash 库中的
Fingerprint64函数，计算并返回STRING或BYTES输入的 64 位有符号指纹值。此函数针对特定输入的输出不会更改，并且与使用 GoogleSQL 时FARM_FINGERPRINT函数的输出一致。此函数对字符串遵守IGNORE CASE，返回大小写不变的值。 IF(condition, true_return, false_return)- 返回
true_return或false_return，具体取决于condition是 true 还是 false。返回值可以是文本或字段派生值，但必须是相同的数据类型。字段派生值不需要包括在SELECT子句中。 POSITION(field)- 返回一组重复字段中字段的从 1 开始的顺序位置。
SHA1(<str>)- 以字节格式返回输入字符串
str的 SHA1 哈希值。 可以使用 TO_BASE64() 将结果转换为 base64。例如：#legacySQL SELECT TO_BASE64(SHA1(corpus)) FROM [bigquery-public-data:samples.shakespeare] LIMIT 100;
SOME(<condition>)- 如果
condition至少有一个输入为 true，则返回true。与OMIT IF子句一起使用时，此函数对涉及重复字段的查询很实用。 TO_BASE64(<bin_data>)- 将BYTES输入
bin_data转换为 base64 编码的字符串。例如：#legacySQL SELECT TO_BASE64(SHA1(title)) FROM [bigquery-public-data:samples.wikipedia] LIMIT 100;
高级示例
-
使用条件将结果分桶为类别
以下查询使用
CASE/WHEN块并根据州列表，将分桶结果分类到不同的“区域”。如果该州不是其中一个WHEN语句中的选项，则其值将默认为“None”。示例：
#legacySQL SELECT CASE WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID', 'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM') THEN 'West' WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL', 'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV', 'MD', 'DC', 'DE') THEN 'South' WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA', 'MO', 'WI', 'IL', 'IN', 'MI', 'OH') THEN 'Midwest' WHEN state IN ('NY', 'PA', 'NJ', 'CT', 'RI', 'MA', 'VT', 'NH', 'ME') THEN 'Northeast' ELSE 'None' END as region, average_mother_age, average_father_age, state, year FROM (SELECT year, state, SUM(mother_age)/COUNT(mother_age) as average_mother_age, SUM(father_age)/COUNT(father_age) as average_father_age FROM [bigquery-public-data:samples.natality] WHERE father_age < 99 GROUP BY year, state) ORDER BY year LIMIT 5;
返回：
+--------+--------------------+--------------------+-------+------+ | region | average_mother_age | average_father_age | state | year | +--------+--------------------+--------------------+-------+------+ | South | 24.342600163532296 | 27.683769419460344 | AR | 1969 | | West | 25.185041908446163 | 28.268214055448098 | AK | 1969 | | West | 24.780776677578217 | 27.831181063905248 | CA | 1969 | | West | 25.005834769924412 | 27.942978384829598 | AZ | 1969 | | South | 24.541730952905738 | 27.686430093306885 | AL | 1969 | +--------+--------------------+--------------------+-------+------+
-
模拟数据透视表
您可以使用条件语句将子选择查询的结果归入不同的行和列。以下示例搜索以“Google”值开头且最常修订的 Wikipedia 文章，并将搜索结果归入不同的列中。如果这些文章满足各种条件，则相应列会显示修订次数。
示例：
#legacySQL SELECT page_title, /* Populate these columns as True or False, */ /* depending on the condition */ IF (page_title CONTAINS 'search', INTEGER(total), 0) AS search, IF (page_title CONTAINS 'Earth' OR page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo, FROM /* Subselect to return top revised Wikipedia articles */ /* containing 'Google', followed by additional text. */ (SELECT TOP (title, 5) as page_title, COUNT (*) as total FROM [bigquery-public-data:samples.wikipedia] WHERE REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0 );
返回：
+---------------+--------+------+ | page_title | search | geo | +---------------+--------+------+ | Google search | 4261 | 0 | | Google Earth | 0 | 3874 | | Google Chrome | 0 | 0 | | Google Maps | 0 | 2617 | | Google bomb | 0 | 0 | +---------------+--------+------+
-
使用 HASH 选择数据的随机样本
一些查询可以使用结果集的随机子采样提供有用的结果。如需检索值的随机采样，请使用
HASH函数返回哈希值的模“n”等于 0 的结果。例如，以下查询将查找“title”值的
HASH()，然后检查值模“2”的值是否为 0。这应该会使大约 50% 的值被标记为“已采样”。要对更少的值进行采样，请将模运算的值从“2”增加到更大的值。该查询将ABS函数与HASH结合使用，因为HASH可以返回负值，且对负值使用模数运算符会产生负值。示例：
#legacySQL SELECT title, HASH(title) AS hash_value, IF(ABS(HASH(title)) % 2 == 1, 'True', 'False') AS included_in_sample FROM [bigquery-public-data:samples.wikipedia] WHERE wp_namespace = 0 LIMIT 5;