旧版 SQL 函数和运算符

本文档详细介绍了旧版 SQL 函数和运算符。BigQuery 的首选查询语法是 GoogleSQL。如需了解 GoogleSQL，请参阅 GoogleSQL 函数和运算符。

支持的函数和运算符

大多数 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()`	计算值的样本方差…

算术运算符
`+`	加
`-`	减
`*`	乘
`/`	除
`%`	取模

按位函数
`&`	按位 AND
`\|`	按位 OR
`^`	按位 XOR
`<<`	按位左移
`>>`	按位右移
`~`	按位 NOT
`BIT_COUNT()`	返回位数…

类型转换函数
`BOOLEAN()`	转换为布尔值。
`BYTES()`	转换为字节。
`CAST(expr AS type)`	将 `expr` 转换为 `type` 类型的变量。
`FLOAT()`	转换为双精度型。
`HEX_STRING()`	转换为十六进制字符串。
`INTEGER()`	转换为整数。
`STRING()`	转换为字符串。

比较函数
`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()`	如果 `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()`	以字节形式返回 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 等关键字大写。

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 列；该列必须通过名称引用。lenlen 别名在 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 ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

FROM 子句指定要查询的源数据。您可以直接对表、子查询、联接表以及由特殊用途运算符（如下所述）修饰的表执行 BigQuery 查询。可以使用英文逗号查询这些数据源的组合，英文逗号是 BigQuery 中的 UNION ALL 运算符。

引用表

引用表时，必须同时指定 datasetId 和 tableId；project_name 为可选datasetIddatasetIddatasetId。如果未指定 project_name，则 BigQuery 会默认为当前项目project_name。如果项目名称包含短划线，则必须用括号括住整个表引用。

示例

[my-dashed-project:dataset1.tableName]

通过在表名称后添加空格后跟标识符，可以为表赋予别名。您可以选择在 tableId 和别名之间添加 AS 关键字来提高可读性tableId。

引用表中的列时，可以使用简单列名，也可以将别名（如果指定了别名）用作列名的前缀，或将 datasetId 和 tableId 作为前缀（只要未指定 project_name）datasetIddatasetIddatasetId。 project_name 不能包含在列前缀中，因为字段名称中不允许使用冒号字符project_name。

示例

此示例引用未设置表前缀的列。

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare];

此示例将 datasetId 和 tableId 用作列名的前缀datasetIddatasetId。注意，此示例不得添加 project_nameproject_name。只有在数据集属于当前默认项目的情况下，这种方法才有效。

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

此示例将表别名用作列名的前缀。

#legacySQL
SELECT
  t.word
FROM
  [bigquery-public-data:samples.shakespeare] AS t;

使用子查询

子查询是前后带有括号的嵌套 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 BY clause. */

不支持按浮点值和双精度值分组，因为未针对这些类型明确定义相等函数。
由于系统是互动式的，因此如果查询会生成大量组，就可能会失败。使用 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 子句不能包含任何函数；它只能接受数字常量。

查询语法

上文详细介绍了 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 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。

语法

运算符	说明	示例
+	加	`SELECT 6 + (5 - 1);` 返回：10
-	减	`SELECT 6 - (4 + 1);` 返回：1
*	乘	`SELECT 6 * (5 - 1);` 返回：24
/	除	`SELECT 6 / (2 + 2);` 返回：1.5
%	取模	`SELECT 6 % (2 + 2);` 返回：2

按位函数

按位函数在各个位级别上执行运算，并且需要数值参数。如需详细了解按位函数，请参阅按位运算。

有关另外三个按位函数（BIT_AND、BIT_OR 和 BIT_XOR）的说明，请参阅聚合函数。

语法

运算符	说明	示例
&	按位 AND	`SELECT (1 + 3) & 1` 返回：0
\|	按位 OR	`SELECT 24 \| 12` 返回：28
^	按位 XOR	`SELECT 1 ^ 0` 返回：1
<<	按位左移	`SELECT 1 << (2 + 2)` 返回：16
>>	按位右移	`SELECT (6 + 2) >> 2` 返回：2
~	按位非	`SELECT ~2` 返回：-3
`BIT_COUNT(<numeric_expr>)`	返回 `<numeric_expr>` 中设置的位数。	`SELECT BIT_COUNT(29);` 返回：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 != 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()`	如果 `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。date_format_strdate_format_str 可以包含与日期相关的标点符号（例如 date_format_str/ 和 -date_format_str）以及 C++ 中的 strftime 函数接受的特殊字符（例如，%ddate_format_str 表示一个月中的某天）。

如果您打算按时间间隔对查询数据进行分组（例如，获取特定月份的所有数据），请使用 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()`	以字节形式返回 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)

以字节形式返回 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。

expr OR expr

如果一个或两个表达式为 true，则返回 true。
如果两个表达式都为 false，则返回 false。
如果两个表达式都为 NULL 或一个表达式为 false 且另一个表达式为 NULL，则返回 NULL。

NOT expr

如果表达式为 false，则返回 true。
如果表达式为 true，则返回 false。
如果表达式为 NULL，则返回 NULL。

可以将 NOT 作为否定运算符与其他函数一起使用。例如 NOT IN(expr1, expr2) 或 IS NOT NULL。

数学函数

数学函数采用数字参数且返回数字结果。每个参数可以是数字文本或查询返回的数值。如果数学函数计算出未定义的结果，则运算返回 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_strorig_strorig_strorig_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。str2str2str2str2

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。如果省略 str2str2，LTRIM 将移除 str1 左侧的空格str2。否则，LTRIM 将从 str1 左侧移除 str2 中的任何字符（区分大小写）str2str2。

示例：

SELECT LTRIM("Say hello", "yaS") 会返回 " hello"。

SELECT LTRIM("Say hello", " ySa") 会返回 "hello"。

REPLACE('str1', 'str2', 'str3')

用 str3 替换 str1 中 str2 的所有实例str2str2str2。

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 的尾随空格str2str2。否则，RTRIM 将从 str1 右侧移除 str2 中的任何字符（区分大小写）str2str2。

示例：

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 转换为字节格式。如需将字节转换为 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>)

将字节输入 bin_data 转换为 base64 编码的字符串。例如：

#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;

如需将 base64 编码的字符串转换为字节，请使用FROM_BASE64()

高级示例

使用条件将结果分桶为类别

以下查询使用 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;
```

旧版 SQL 函数和运算符

支持的函数和运算符

查询语法

SELECT 子句

示例

聚合函数的 WITHIN 修饰符

示例

FROM 子句

引用表

示例

示例

使用子查询

英文逗号为 UNION ALL

表通配符函数

FLATTEN 运算符

JOIN 运算符

JOIN 类型

EACH 修饰符

半联接和反联接

示例

WHERE 子句

示例

OMIT RECORD IF 子句

示例

GROUP BY 子句

示例

EACH 修饰符

ROLLUP 函数

示例

示例

HAVING 子句

示例

ORDER BY 子句

LIMIT 子句

查询语法

聚合函数

语法

TOP() 函数

TOP() 示例

高级示例

算术运算符

语法

按位函数

语法

类型转换函数

语法

比较函数

语法

日期和时间函数

语法

高级示例

IP 函数

语法

语法

JSON 函数

语法

逻辑运算符

语法

数学函数

语法

高级示例

正则表达式函数

语法

高级示例

字符串函数

语法

表通配符函数

语法

网址函数

语法

高级示例

窗口函数

语法

其他函数

语法

高级示例

英文逗号为 `UNION ALL`

`EACH` 修饰符

`ROLLUP` 函数