使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

旧版 SQL 函数和运算符

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

支持的函数和运算符

大多数 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 的值介于 expr2expr3 之间(含边界值),则返回 true
expr IS NULL 如果 expr 为 NULL,则返回 true
expr IN() 如果 exprexpr1expr2 或括号中的任何值匹配,则返回 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 格式返回日期字符串
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.232620: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 BYHAVINGORDER BY 子句引用,但不能由 FROMWHEREOMIT 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 ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

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

引用表

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

示例
[my-dashed-project:dataset1.tableName]

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

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

示例

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

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

此示例将 datasetId 和 tableId 用作列名的前缀。注意,此示例不得添加 project_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

与 Google 标准 SQL 不同,旧版 SQL 将英文逗号用作 UNION ALL 运算符,而不是 CROSS JOIN 运算符。这是一种演变而来的旧行为,因为 BigQuery 过去不支持 CROSS JOIN,而 BigQuery 用户经常需要编写 UNION ALL 查询。在 Google 标准 SQL 中,执行联合的查询特别冗长。将英文逗号用作联合运算符可以更高效地编写此类查询。例如,此查询可用于对多天的日志运行单个查询。

#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] OUTERCROSS 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 子句生成的记录。 可以通过布尔值 ANDOR 子句连接多个条件,可选择用括号 () 括起来将其分组。不需要在相应的 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,则省略记录,但如果表达式返回 falsenull,则保留记录。其次,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>, 错误,建议您切换到 Google 标准 SQL。如需了解如何将 OMIT...IF 语句迁移到 Google 标准 SQL,请参阅迁移到 Google 标准 SQL

示例

再次参考用于 WITHIN 修饰符的示例,OMIT RECORD IF 可用于实现与该示例中 WITHINHAVING 相同的功能。

#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 BYORDER 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 BYORDER 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_expr1numeric_expr2 算出的值计算总体协方差。
COVAR_SAMP(numeric_expr1, numeric_expr2)
针对通过 numeric_expr1numeric_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 个 bucketsbuckets 的默认值为 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_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_ANDBIT_ORBIT_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)
以双精度类型的值返回 exprexpr 可以是类似 '45.78' 的字符串,但对于非数字值,该函数返回 NULL
HEX_STRING(numeric_expr)
以十六进制字符串形式返回 numeric_expr
INTEGER(expr)
expr 转换为 64 位整数。
  • 如果 expr 是不会对应到整数值的字符串,则返回 NULL。
  • 如果 expr 是时间戳,则返回从 Unix Epoch 开始所经过的微秒数。
STRING(numeric_expr)
以字符串形式返回 numeric_expr

比较函数

比较函数基于以下类型的比较返回 truefalse

  • 两个表达式的比较。
  • 表达式或表达式集与特定条件(例如在指定列表中、为 NULL,或者是非默认可选值)的比较。

下面列出的某些函数返回的值不是 truefalse,它们返回基于比较运算的值。

您可以使用数字或字符串表达式作为比较函数的参数 (字符串常量必须用单引号或双引号括起来)。表达式可以是查询提取的文本或值。比较函数最常用作 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 的值介于 expr2expr3 之间(含边界值),则返回 true
expr IS NULL 如果 expr 为 NULL,则返回 true
expr IN() 如果 exprexpr1expr2 或括号中的任何值匹配,则返回 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, ...)
如果 exprexpr1expr2 或括号中的任何值匹配,则返回 trueIN 关键字是 (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_defaultNVL 函数是 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 格式返回日期字符串
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 值包括 YEARMONTHDAYHOURMINUTESECOND。如果 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 日,则此函数返回同一年