标准 SQL 中的查询语法

查询语句可扫描一个或多个表或表达式，并返回计算的结果行。本主题描述 BigQuery 中 SQL 查询的语法。

SQL 语法

query_statement:
    query_expr

query_expr:
    [ WITH cte[, ...] ]
    { select | ( query_expr ) | set_operation }
    [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
    [ LIMIT count [ OFFSET skip_rows ] ]

select:
    SELECT [ AS { STRUCT | VALUE } ] [{ ALL | DISTINCT }]
        { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
            [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
        | expression [ [ AS ] alias ] } [, ...]
    [ FROM from_clause[, ...] ]
    [ WHERE bool_expression ]
    [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]
    [ HAVING bool_expression ]
    [ QUALIFY bool_expression ]
    [ WINDOW window_clause ]

表示法规则

方括号“[ ]”表示可选的子句。
圆括号“( )”表示文本括号。
竖线“|”表示逻辑“或”(OR) 。
大括号“{ }”括起一组选项。
方括号内后跟省略号的逗号“[, …]”表示可以在逗号分隔列表中重复其前面的项目。

示例表

下表用于说明本参考文档中不同查询子句的行为。

Roster 表

Roster 表列出了运动员姓氏 (LastName) 以及分配给其学校的唯一 ID (SchoolID)。如下所示：

+-----------------------+
| LastName   | SchoolID |
+-----------------------+
| Adams      | 50       |
| Buchanan   | 52       |
| Coolidge   | 52       |
| Davis      | 51       |
| Eisenhower | 77       |
+-----------------------+

您可以使用此 WITH 子句为本参考文档中的示例模拟临时表名称：

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

PlayerStats 表

PlayerStats 表列出了运动员姓氏 (LastName)、分配给给定比赛中的竞争对手的唯一 ID (OpponentID)，以及运动员在该比赛中的得分 (PointsScored)。

+----------------------------------------+
| LastName   | OpponentID | PointsScored |
+----------------------------------------+
| Adams      | 51         | 3            |
| Buchanan   | 77         | 0            |
| Coolidge   | 77         | 1            |
| Adams      | 52         | 4            |
| Buchanan   | 50         | 13           |
+----------------------------------------+

您可以使用此 WITH 子句为本参考文档中的示例模拟临时表名称：

WITH PlayerStats AS
 (SELECT 'Adams' as LastName, 51 as OpponentID, 3 as PointsScored UNION ALL
  SELECT 'Buchanan', 77, 0 UNION ALL
  SELECT 'Coolidge', 77, 1 UNION ALL
  SELECT 'Adams', 52, 4 UNION ALL
  SELECT 'Buchanan', 50, 13)
SELECT * FROM PlayerStats

TeamMascot 表

TeamMascot 表列出了唯一学校 ID (SchoolID) 以及相应学校的吉祥物 (Mascot)。

+---------------------+
| SchoolID | Mascot   |
+---------------------+
| 50       | Jaguars  |
| 51       | Knights  |
| 52       | Lakers   |
| 53       | Mustangs |
+---------------------+

您可以使用此 WITH 子句为本参考文档中的示例模拟临时表名称：

WITH TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')
SELECT * FROM TeamMascot

SELECT 列表

SELECT [ AS { typename | STRUCT | VALUE } ] [{ ALL | DISTINCT }]
    { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
        [ REPLACE ( expression [ AS ] column_name [, ...] ) ]
    | expression [ [ AS ] alias ] } [, ...]

SELECT 列表定义查询将返回的列。SELECT 列表中的表达式可以引用该列表相应的 FROM 子句的任何 from_item 中的列。

SELECT 列表中的每一项可以是以下其中之一：

*
expression
expression.*

SELECT *

SELECT * 通常称为查询星号，会对执行完整查询后可见的每个列生成一个输出列。

SELECT * FROM (SELECT "apple" AS fruit, "carrot" AS vegetable);

+-------+-----------+
| fruit | vegetable |
+-------+-----------+
| apple | carrot    |
+-------+-----------+

SELECT `expression`

SELECT 列表中的项目可以是表达式。这些表达式的计算结果为单个值，并且生成一个输出列，其中具有可选的显式 alias。

如果表达式没有显式别名，则在可能的情况下，其将根据隐式别名的规则接收隐式别名。否则，该列是匿名的，您无法在查询的其他位置通过名称引用该列。

SELECT `expression.*`

SELECT 列表中的项也可以采用 expression.* 的形式。这会为每个列或 expression 的顶级字段生成一个输出列。表达式必须是表别名或者其计算结果是带有字段的数据类型（如 STRUCT）的单个值。

以下查询为表 groceries 中的每一列生成一个输出列，别名为 g。

WITH groceries AS
  (SELECT "milk" AS dairy,
   "eggs" AS protein,
   "bread" AS grain)
SELECT g.*
FROM groceries AS g;

+-------+---------+-------+
| dairy | protein | grain |
+-------+---------+-------+
| milk  | eggs    | bread |
+-------+---------+-------+

更多示例：

WITH locations AS
  (SELECT STRUCT("Seattle" AS city, "Washington" AS state) AS location
  UNION ALL
  SELECT STRUCT("Phoenix" AS city, "Arizona" AS state) AS location)
SELECT l.location.*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
| Phoenix | Arizona    |
+---------+------------+

WITH locations AS
  (SELECT ARRAY<STRUCT<city STRING, state STRING>>[("Seattle", "Washington"),
    ("Phoenix", "Arizona")] AS location)
SELECT l.LOCATION[offset(0)].*
FROM locations l;

+---------+------------+
| city    | state      |
+---------+------------+
| Seattle | Washington |
+---------+------------+

* 运算符的修饰符

SELECT * EXCEPT

SELECT * EXCEPT 语句指定要从结果中排除的一个或多个列的名称。输出中将忽略所有匹配的列名称。

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id)
FROM orders;

+-----------+----------+
| item_name | quantity |
+-----------+----------+
| sprocket  | 200      |
+-----------+----------+

SELECT * REPLACE

SELECT * REPLACE 语句指定一个或多个 expression AS identifier 子句。每个标识符必须与 SELECT * 语句中的列名匹配。在输出列列表中，该 REPLACE 子句中的表达式将替换与 REPLACE 子句中的标识符匹配的列。

SELECT * REPLACE 语句不会更改列的名称或顺序。但是该语句可以更改值和值类型。

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

重复行处理

您可以修改从 SELECT 查询返回的结果，如下所示。

SELECT DISTINCT

SELECT DISTINCT 语句会丢弃重复行并仅返回剩余行。SELECT DISTINCT 无法返回以下类型的列：

STRUCT
ARRAY

SELECT ALL

SELECT ALL 语句返回包括重复行在内的所有行。SELECT ALL 是 SELECT 的默认行为。

值表

在 BigQuery 中，值表是一个表，其中行类型是单个值。在常规表中，每行由列组成，每列都有名称和类型。在值表中，行类型只是一个值，并且没有列名。

注意：在 BigQuery 中，查询只能返回类型为 STRUCT 的值表。

如果需要使用恰好具有一列的查询，可以改用值表查询。例如，标量子查询和数组子查询（请参阅子查询）通常需要单列查询，但在 BigQuery 中，它们还允许使用值表查询。

如果查询使用 SELECT AS，则会使用以下语法之一生成值表：

SELECT AS STRUCT

SELECT AS STRUCT expr [[AS] struct_field_name1] [,...]

此语法会生成一个具有 STRUCT 行类型的值表，其中 STRUCT 字段名称和类型与 SELECT 列表中生成的列名称和类型相匹配。

例如：

SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)

可以在标量或数组子查询中使用 SELECT AS STRUCT 生成将多个值组合在一起的单个 STRUCT 类型。此语法通常不允许标量和数组子查询（请参阅子查询）返回多个列，但可以返回 STRUCT 类型的单个列。

SELECT AS VALUE

SELECT AS VALUE 从任何只生成一列的 SELECT 列表生成一个值表。输出将是一个值表，而不是生成具有一列的输出表（可能具有名称），其中行类型只是在一个 SELECT 列中生成的值类型。该列所具有的任何别名都将在值表中被舍弃。

例如：

SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyz

上面的查询会生成一个行类型为 STRUCT<a int64, b int64> 的表。

别名

请参阅使用别名，了解 SELECT 列表别名的语法和可见性。

FROM 子句

FROM from_clause[, ...]

from_clause:
    from_item
    [ { pivot_operator | unpivot_operator } ]
    [ tablesample_operator ]

from_item:
    {
      table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ]
      | { join_operation | ( join_operation ) }
      | ( query_expr ) [ as_alias ]
      | field_path
      | unnest_operator
      | cte_name [ as_alias ]
    }

as_alias:
    [ AS ] alias

FROM 子句表示要从中检索行的一个或多个表，并指定如何将这些行联接在一起来生成单个行流，以便在查询的其余部分进行处理。

table_name

现有表的名称（可限定）。

SELECT * FROM Roster;
SELECT * FROM dataset.Roster;
SELECT * FROM project.dataset.Roster;

FOR SYSTEM_TIME AS OF

FOR SYSTEM_TIME AS OF 引用历史版本的表定义和 timestamp_expression 时的当前行。

限制：

包含 FOR SYSTEM_TIME AS OF 的 FROM 子句中的源表不能是以下任何一种：

ARRAY 扫描，包括展平数组或 UNNEST 运算符的输出。
由 WITH 子句定义的通用表表达式。

timestamp_expression 必须是常量表达式。它不能包含以下内容：

子查询。
相关引用（对在 SELECT 列表等更高级别的查询语句中出现的表中各列的引用）。
用户定义的函数 (UDF)。

timestamp_expression 的值不能属于以下范围：

在当前时间戳之后（将来）。
往前超过当前时间戳七 (7) 天。

单个查询语句不能在多个时间点（包括当前时间）引用一个表。也就是说，查询可以在同一时间戳多次引用一个表，但不能引用当前版本和历史版本，或两个不同的历史版本。

示例：

以下查询返回表在过去一个小时内的历史版本。

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

以下查询返回表在一个绝对时间点的历史版本。

SELECT *
FROM t
  FOR SYSTEM_TIME AS OF '2017-01-01 10:00:00-07:00';

以下查询返回错误，因为 timestamp_expression 包含对所包含查询中的列的相关引用。

SELECT *
FROM t1
WHERE t1.a IN (SELECT t2.a
               FROM t2 FOR SYSTEM_TIME AS OF t1.timestamp_column);

以下操作演示如何访问在替换表之前表的历史版本。

DECLARE before_replace_timestamp TIMESTAMP;

-- Create table books.
CREATE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Get current timestamp before table replacement.
SET before_replace_timestamp = CURRENT_TIMESTAMP();

-- Replace table with different schema(title and release_date).
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, DATE '1603-01-01' release_date;

-- This query returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF before_replace_timestamp;

以下操作演示如何访问 DML 作业之前表的历史版本。

DECLARE JOB_START_TIMESTAMP TIMESTAMP;

-- Create table books.
CREATE OR REPLACE TABLE books AS
SELECT 'Hamlet' title, 'William Shakespeare' author;

-- Insert two rows into the books.
INSERT books (title, author)
VALUES('The Great Gatsby', 'F. Scott Fizgerald'),
      ('War and Peace', 'Leo Tolstoy');

SELECT * FROM books;

SET JOB_START_TIMESTAMP = (
  SELECT start_time
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
  WHERE job_type="QUERY"
    AND statement_type="INSERT"
  ORDER BY start_time DESC
  LIMIT 1
 );

-- This query only returns Hamlet, William Shakespeare as result.
SELECT * FROM books FOR SYSTEM_TIME AS OF JOB_START_TIMESTAMP;

join_operation

请参阅 JOIN 运算。

query_expr

( query_expr ) [ [ AS ] alias ] 是表子查询。

field_path

在 FROM 子句中，field_path 是解析为数据类型中的某字段的任意路径。field_path 可以任意深入嵌套的数据结构。

有效的 field_path 值的一些示例包括：

SELECT * FROM T1 t1, t1.array_column;

SELECT * FROM T1 t1, t1.struct_column.array_field;

SELECT (SELECT ARRAY_AGG(c) FROM t1.array_column c) FROM T1 t1;

SELECT a.struct_field1 FROM T1 t1, t1.array_of_structs a;

SELECT (SELECT STRING_AGG(a.struct_field1) FROM t1.array_of_structs a) FROM T1 t1;

FROM 子句中的字段路径必须以数组字段结尾。另外，字段路径在路径末尾之前不能包含数组。例如，路径 array_column.some_array.some_array_field 无效，因为它在路径末尾前包含数组。

unnest_operator

请参阅 UNNEST 运算符。

cte_name

WITH 子句中的通用表表达式 (CTE) 充当临时表，可在 FROM 子句中的任何位置引用。在下面的示例中，subQ1 和 subQ2 为 CTE。

例如：

WITH
  subQ1 AS (SELECT * FROM Roster WHERE SchoolID = 52),
  subQ2 AS (SELECT SchoolID FROM subQ1)
SELECT DISTINCT * FROM subQ2;

除非您像下面那样限定表名，否则 WITH 子句会在查询期间隐藏具有相同名称的任何永久表：

dataset.Roster 或 project.dataset.Roster。

UNNEST 运算符

unnest_operator:
    {
      UNNEST( array_expression )
      | UNNEST( array_path )
      | array_path
    }
    [ as_alias ]
    [ WITH OFFSET [ as_alias ] ]

UNNEST 运算符接受 ARRAY，并返回一个表，ARRAY 中的每个元素占一行。您还可以在 FROM 子句外部将 UNNEST 与 IN 运算符搭配使用。

对于大多数元素类型的输入 ARRAY，UNNEST 的输出通常有一列。此列具有一个可选 alias，您可以使用它在查询中的其他位置引用该列。具有这些元素类型的 ARRAYS 可返回多个列：

STRUCT

UNNEST 会破坏输入 ARRAY 中元素的顺序。请使用可选的 WITH OFFSET 子句返回另一个包含数组元素索引的列（见下文）。

如需了解多种使用 UNNEST 的方式（包括构造、展平和过滤），请参阅 Working with arrays。

UNNEST 和 STRUCT

对于 STRUCT 的输入 ARRAY，UNNEST 将对每个 STRUCT 返回一行，STRUCT 中的每个字段单独占一列。每列的别名是相应 STRUCT 字段的名称。

例如：

SELECT *
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')]);

+---+-----+
| x | y   |
+---+-----+
| 3 | bar |
| 1 | foo |
+---+-----+

由于 UNNEST 运算符返回一个值表，因此您可以使用别名 UNNEST 定义可在查询中的其他位置引用的范围变量。如果在 SELECT 列表中引用范围变量，则查询将返回包含输入表中原始 STRUCT 的所有字段的 STRUCT。

例如：

SELECT *, struct_value
FROM UNNEST(ARRAY<STRUCT<x INT64, y STRING>>[(1, 'foo'), (3, 'bar')])
       AS struct_value;

+---+-----+--------------+
| x | y   | struct_value |
+---+-----+--------------+
| 3 | bar | {3, bar}     |
| 1 | foo | {1, foo}     |
+---+-----+--------------+

显式和隐式 UNNEST

ARRAY 解除嵌套可以是显式的也可以是隐式的。在显式解除嵌套中，array_expression 必须返回 ARRAY 值，但不需要解析为 ARRAY，并且 UNNEST 关键字是必需的。

例如：

SELECT * FROM UNNEST ([1, 2, 3]);

在隐式解除嵌套中，array_path 必须解析为 ARRAY，且 UNNEST 关键字是可选的。

例如：

SELECT x
FROM mytable AS t,
  t.struct_typed_column.array_typed_field1 AS x;

在这种情况下，array_path 可以任意深入数据结构中，但最后一个字段必须为 ARRAY 类型。表达式中前面的字段不能为 ARRAY 类型，因为无法从 ARRAY 中提取命名字段。

UNNEST 和 NULL

UNNEST 按如下方式处理 NULL：

NULL 和空数组会生成零行。
包含 NULL 的数组会生成包含 NULL 值的行。

可选的 WITH OFFSET 子句会返回一个单独的列，其中包含 UNNEST 运算为每一行生成的“偏移量”值（即从零开始计数）。该列具有一个可选的 alias；默认别名为 offset。

例如：

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

PIVOT 运算符

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

PIVOT 运算符通过聚合将行旋转为列。PIVOT 是 FROM 子句的一部分。

PIVOT 可用于修改任何表表达式。
不允许将 PIVOT 与 FOR SYSTEM_TIME AS OF 结合使用，但用户可以将 PIVOT 与本身使用 FOR SYSTEM_TIME AS OF 的子查询输入结合使用。
WITH OFFSET 子句不允许出现在 PIVOT 运算符之前。

概念性示例：

-- Before PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

-- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns:
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple   | 77 | 0  | 25 | 2  |
| Kale    | 51 | 23 | 45 | 3  |
+---------+----+----+----+----+

定义

顶层定义：

from_item：要对其执行行转列操作的表或子查询。from_item 必须遵循这些规则。
pivot_operator：要对 from_item 执行的行转列操作。
alias：要在查询中用于某个项的别名。

pivot_operator 定义：

aggregate_function_call：聚合函数调用，用于聚合所有输入行，以使 input_column 与 pivot_column 中的特定值匹配。与不同 pivot_column 值对应的每个聚合会在输出中生成不同的列。创建聚合函数调用时请遵循这些规则。
input_column：根据这些规则接受列并检索该列的行值。
pivot_column：要为每个聚合函数调用创建的行转列列。如果未提供别名，则创建默认别名。行转列列值类型必须与 input_column 中的值类型匹配，以便比较值。pivot_column 中的值可能与 input_column 中的值不匹配。必须是常量，并遵循这些规则。

规则

传递给 PIVOT 的 from_item 的规则：

from_item 可以包含任何表或子查询结果。
from_item 不能生成值表。
from_item 不能是使用 SELECT AS STRUCT 的子查询。

aggregate_function_call 的规则：

必须是聚合函数。例如 SUM。
您可以引用传递给 PIVOT 的表中的列以及相关列，但不能访问 PIVOT 子句本身定义的列。
如果提供了传递给 PIVOT 的表的别名，则可以通过别名访问该表。
您只能使用接受一个参数的聚合函数。
除了 COUNT 之外，您只能使用忽略 NULL 输入的聚合函数。
如果您使用 COUNT，则可以使用 * 作为参数。

input_column 的规则：

可以访问输入表的列以及相关列，但不能访问 PIVOT 子句本身定义的列。
根据输入表中的每一行进行计算；禁止使用聚合和分析函数调用。
非确定性是可接受的。
类型必须可分组。
如果提供了输入表的别名，则可以通过别名访问输入表。

pivot_column 的规则：

pivot_column 必须是常量。
不支持已命名的常量（例如变量）。
不支持查询参数。
如果需要已命名常量或查询参数的名称，请使用别名明确指定。
在特殊情况下，不同的 pivot_column 可能具有相同的默认列名称。例如，一个输入列可能同时包含 NULL 值和字符串字面量 "NULL"。在这种情况下，将创建多个同名的行转列列。为避免这种情况，请为行转列列名称使用别名。
如果 pivot_column 未指定别名，则列名称的构造方式如下：

从	到	示例
NULL	NULL	输入：NULL 输出：“NULL”
INT64 NUMERIC BIGNUMERIC	字符串格式的数字，遵循以下规则：正数以 `_` 开头。负数以 `minus_` 开头。小数点替换为 `_point_`。	输入：1 输出：_1 输入：-1 输出：minus_1 输入：1.0 输出：_1_point_0
BOOL	`TRUE` 或 `FALSE`。	输入：TRUE 输出：TRUE 输入：FALSE 输出：FALSE
STRING	字符串值。	输入："PlayerName" 输出：PlayerName
DATE	采用 `_YYYY_MM_DD` 格式的日期。	输入：DATE '2013-11-25' 输出：_2013_11_25
ENUM	枚举常量的名称。	输入：COLOR.RED 输出：RED
STRUCT	通过计算每个字段的 `pivot_column` 名称并将结果用下划线连接起来形成的字符串。适用以下规则：如果该字段名称为：`<field_name>_<pivot_column_name_for_field_name>`。如果该字段未命名：`<pivot_column_name_for_field_name>`。通过以递归方式在此表中应用规则来确定 `<pivot_column_name_for_field_name>`。如果任何 `STRUCT` 字段没有可用的规则，则整个行转列列未命名。由于从 `IN` 列表值隐式强制转换为 `<value-expression>` 类型，字段名称必须存在于 `input_column` 中，以影响数据透视列。	输入：STRUCT("one", "two") 输出：one_two 输入：STRUCT("one" AS a, "two" AS b) 输出：one_a_two_b
所有其他数据类型	不受支持。您必须提供别名。

示例

以下示例引用名为 Produce 的表，如下所示：

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter UNION ALL
  SELECT 'Kale', 23, 'Q2' UNION ALL
  SELECT 'Kale', 45, 'Q3' UNION ALL
  SELECT 'Kale', 3, 'Q4' UNION ALL
  SELECT 'Apple', 77, 'Q1' UNION ALL
  SELECT 'Apple', 0, 'Q2' UNION ALL
  SELECT 'Apple', 25, 'Q3' UNION ALL
  SELECT 'Apple', 2, 'Q4')
SELECT * FROM Produce

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

使用 PIVOT 运算符，quarter 列中的行将转换为下列新列：Q1、Q2、Q3、Q4。

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Apple   | 77 | 0  | 25 | 2  |
| Kale    | 51 | 23 | 45 | 3  |
+---------+----+----+----+----+

SELECT * FROM
  (SELECT sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

+-----+----+----+----+
| Q1  | Q2 | Q3 | Q4 |
+-----+----+----+----+
| 128 | 23 | 70 | 5  |
+-----+----+----+----+

您只能返回所选列：

SELECT * FROM
  (SELECT * FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))

+---------+----+----+----+
| product | Q1 | Q2 | Q3 |
+---------+----+----+----+
| Apple   | 77 | 0  | 25 |
| Kale    | 51 | 23 | 45 |
+---------+----+----+----+

SELECT * FROM
  (SELECT sales, quarter FROM Produce)
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3'))

+-----+----+----+
| Q1  | Q2 | Q3 |
+-----+----+----+
| 128 | 23 | 70 |
+-----+----+----+

UNPIVOT 运算符

FROM from_item[, ...] unpivot_operator

unpivot_operator:
    UNPIVOT [ { INCLUDE NULLS | EXCLUDE NULLS } ] (
        { single_column_unpivot | multi_column_unpivot }
    ) [unpivot_alias]

single_column_unpivot:
    values_column
    FOR name_column
    IN (columns_to_unpivot)

multi_column_unpivot:
    values_column_set
    FOR name_column
    IN (column_sets_to_unpivot)

values_column_set:
    (values_column[, ...])

columns_to_unpivot:
    unpivot_column [row_value_alias][, ...]

column_sets_to_unpivot:
    (unpivot_column [row_value_alias][, ...])

unpivot_alias and row_value_alias:
    [AS] alias

UNPIVOT 运算符将列旋转为行。UNPIVOT 是 FROM 子句的一部分。

UNPIVOT 可用于修改任何表表达式。
不允许将 UNPIVOT 与 FOR SYSTEM_TIME AS OF 结合使用，但用户可以将 UNPIVOT 与本身使用 FOR SYSTEM_TIME AS OF 的子查询输入结合使用。
WITH OFFSET 子句不允许出现在 UNPIVOT 运算符之前。
PIVOT 聚合不能通过 UNPIVOT 撤消。

概念性示例：

-- Before UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

-- After UNPIVOT is used to rotate Q1, Q2, Q3, Q4 into sales and quarter columns:
+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

定义

顶层定义：

from_item：要对其执行行转列操作的表或子查询。from_item 必须遵循这些规则。
unpivot_operator：要对 from_item 执行的行转列操作。

unpivot_operator 定义：

INCLUDE NULLS：将具有 NULL 值的行添加到结果中。
EXCLUDE NULLS：不将具有 NULL 值的行添加到结果中。默认情况下，UNPIVOT 会排除具有 NULL 值的行。
single_column_unpivot：将列转换为一个 values_column 和一个 name_column。
multi_column_unpivot：将列转换为多个 values_column 和一个 name_column。
unpivot_alias：UNPIVOT 操作结果的别名。此别名可以在查询中的其他位置引用。

single_column_unpivot 定义：

values_column：包含 columns_to_unpivot 中的行值的列。创建值列时，请遵循这些规则。
name_column：包含 columns_to_unpivot 中的列名称的列。创建名称列时，请遵循这些规则。
columns_to_unpivot：from_item 中用于填充 values_column 和 name_column 的列。在创建列转行列时，请遵循这些规则。
- row_value_alias：为 name_column 中的列显示的列的可选别名。如果未指定此标志，则使用列名称的字符串值。创建行值别名时，请遵循这些规则。

multi_column_unpivot 定义：

values_column_set：一组列，包含 columns_to_unpivot 中的行值。创建值列时，请遵循这些规则。
name_column：一组列，包含 columns_to_unpivot 中的列名称。创建名称列时，请遵循这些规则。
column_sets_to_unpivot：要对其执行列转行操作的 from_item 中的列。在创建列转行列时，请遵循这些规则。
- row_value_alias：为 name_column 中的列集显示的列集的可选别名。如果未指定此标志，则使用列集的字符串值，并用下划线 (_) 分隔字符串中的每一列。例如，(col1, col2) 输出 col1_col2。创建行值别名时，请遵循这些规则。

规则

传递给 UNPIVOT 的 from_item 的规则：

from_item 可以包含任何表或子查询结果。
from_item 不能生成值表。
from_item 中的重复列不能在 UNPIVOT 子句中引用。

unpivot_operator 的规则：

不允许使用表达式。
不允许使用限定名称。例如，mytable.mycolumn 是不允许的。
如果 UNPIVOT 结果具有重复的列名称：
- 是否允许 SELECT *？
- SELECT values_column 会导致歧义。

values_column 的规则：

此名称不能是 name_column 或 unpivot_column 的名称。
可以与 from_item 中的列名称相同。

name_column 的规则：

此名称不能是 values_column 或 unpivot_column 的名称。
可以与 from_item 中的列名称相同。

unpivot_column 的规则：

必须是 from_item 中的列名。
不能引用重复的 from_item 列名称。
列集中的所有列都必须具有等效的数据类型。
- 数据类型无法强制转换为常见超类型。
- 如果数据类型完全匹配（例如具有不同字段名称的结构体），则第一个输入的数据类型是输出的数据类型。
同一列集中不能出现相同名称。例如，(emp1, emp1) 会导致错误。
不同列集中可以有相同的名称。例如，(emp1, emp2), (emp1, emp3) 是有效的。

row_value_alias 的规则：

可以是 STRING 或 INT64 字面量。
所有 row_value_alias 子句的数据类型必须相同。
如果值为 INT64，则必须为每个 unpivot_column 指定 row_value_alias。

示例

以下示例引用名为 Produce 的表，如下所示：

WITH Produce AS (
  SELECT 'Kale' as product, 51 as Q1, 23 as Q2, 45 as Q3, 3 as Q4 UNION ALL
  SELECT 'Apple', 77, 0, 25, 2)
SELECT * FROM Produce

+---------+----+----+----+----+
| product | Q1 | Q2 | Q3 | Q4 |
+---------+----+----+----+----+
| Kale    | 51 | 23 | 45 | 3  |
| Apple   | 77 | 0  | 25 | 2  |
+---------+----+----+----+----+

使用 UNPIVOT 运算符，Q1、Q2、Q3 和 Q4 列将被旋转。现在，这些列的值会填充名为 Sales 的新列，这些列的名称会填充名为 Quarter 的新列。这是一个单列列转行操作。

SELECT * FROM Produce
UNPIVOT(sales FOR quarter IN (Q1, Q2, Q3, Q4))

+---------+-------+---------+
| product | sales | quarter |
+---------+-------+---------+
| Kale    | 51    | Q1      |
| Kale    | 23    | Q2      |
| Kale    | 45    | Q3      |
| Kale    | 3     | Q4      |
| Apple   | 77    | Q1      |
| Apple   | 0     | Q2      |
| Apple   | 25    | Q3      |
| Apple   | 2     | Q4      |
+---------+-------+---------+

在此示例中，我们对四个季度执行 UNPIVOT 操作，使其合并为两个半年。这是一个多列列转行操作。

SELECT * FROM Produce
UNPIVOT(
  (first_half_sales, second_half_sales)
  FOR semesters
  IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))

+---------+------------------+-------------------+------------+
| product | first_half_sales | second_half_sales | semesters  |
+---------+------------------+-------------------+------------+
| Kale    | 51               | 23                | semester_1 |
| Kale    | 45               | 3                 | semester_2 |
| Apple   | 77               | 0                 | semester_1 |
| Apple   | 25               | 2                 | semester_2 |
+---------+------------------+-------------------+------------+

TABLESAMPLE 运算符

TABLESAMPLE SYSTEM ( percent PERCENT )

说明

您可以使用 TABLESAMPLE 运算符选择数据集的随机样本。在处理包含大量数据的表并且不需要精确答案时，此运算符非常有用。

采样会返回各种记录，同时避免了与扫描和处理整个表相关的费用。每次执行查询都可能返回不同的结果，因为每次执行都会处理独立计算的样本。BigQuery 不会缓存包含 TABLESAMPLE 子句的查询的结果。

将 percent 替换为您要包含在结果中的数据集百分比。该值必须在 0 到 100 之间。该值可以是字面量值或查询参数。不能是变量。

如需了解详情，请参阅表采样。

示例

以下查询选择表中约 10% 的数据：

SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)

JOIN 运算

join_operation:
    { cross_join_operation | condition_join_operation }

cross_join_operation:
    from_item cross_join_operator from_item

condition_join_operation:
    from_item condition_join_operator from_item join_condition

cross_join_operator:
    { CROSS JOIN | , }

condition_join_operator:
    {
      [INNER] JOIN
      | FULL [OUTER] JOIN
      | LEFT [OUTER] JOIN
      | RIGHT [OUTER] JOIN
    }

join_condition:
    { on_clause | using_clause }

on_clause:
    ON bool_expression

using_clause:
    USING ( join_column [, ...] )

JOIN 运算合并两个 from_item，以便 SELECT 子句可以将它们作为一个源进行查询。join_type 和 ON 或 USING 子句（“联接条件”）指定如何组合和舍弃两个 from_item 中的行来形成单个源。

[内]连接

INNER JOIN（简写为 JOIN）有效地计算两个 from_item 的笛卡尔积，并舍弃所有不符合联接条件的行。“有效”表示可以在没有实际计算笛卡尔积的情况下实现 INNER JOIN。

FROM A INNER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | k |     | 2 | b | 2 | k |
| 2 | b |     | 3 | m |     | 3 | c | 3 | m |
| 3 | c |     | 3 | n |     | 3 | c | 3 | n |
| 3 | d |     | 4 | p |     | 3 | d | 3 | m |
+-------+     +-------+     | 3 | d | 3 | n |
                            +---------------+

FROM A INNER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +-----------+
| x | y |  *  | x | z |  =  | x | y | z |
+-------+     +-------+     +-----------+
| 1 | a |     | 2 | k |     | 2 | b | k |
| 2 | b |     | 3 | m |     | 3 | c | m |
| 3 | c |     | 3 | n |     | 3 | c | n |
| 3 | d |     | 4 | p |     | 3 | d | m |
+-------+     +-------+     | 3 | d | n |
                            +-----------+

示例

此查询对 Roster 和 TeamMascot 表执行 INNER JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

CROSS JOIN

CROSS JOIN 返回两个 from_item 的笛卡尔积。换句话说，它将第一个 from_item 的每一行与第二个 from_item 的每一行组合。

如果两个 from_item 的行是独立的，则结果有 M * N 行，其中给定的 M 行在一个 from_item 中，而 N 行在另一个中。请注意，这适用于所有 from_item 具有零行的情况。

在 FROM 子句中，CROSS JOIN 可以编写如下：

FROM A CROSS JOIN B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

您可以使用相互关联的交叉联接将 ARRAY 转换或展平为一组行。如需了解详情，请参阅将数组中的元素转换为表中的行。

示例

此查询对 Roster 和 TeamMascot 表执行 CROSS JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

逗号交叉联接 (,)

CROSS JOIN 可以通过逗号隐式写出。这称为逗号交叉联接。

FROM 子句中的逗号交叉联接如下所示：

FROM A, B

Table A       Table B       Result
+-------+     +-------+     +---------------+
| w | x |  *  | y | z |  =  | w | x | y | z |
+-------+     +-------+     +---------------+
| 1 | a |     | 2 | c |     | 1 | a | 2 | c |
| 2 | b |     | 3 | d |     | 1 | a | 3 | d |
+-------+     +-------+     | 2 | b | 2 | c |
                            | 2 | b | 3 | d |
                            +---------------+

逗号交叉联接不能写在括号内。如需了解详情，请参阅序列中的联接操作。

FROM (A, B)  // INVALID

您可以使用相互关联的逗号交叉联接将 ARRAY 转换或展平为一组行。如需了解详情，请参阅将数组中的元素转换为表中的行。

示例

此查询对 Roster 和 TeamMascot 表执行逗号交叉联接。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster, TeamMascot;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Adams      | Knights      |
| Adams      | Lakers       |
| Adams      | Mustangs     |
| Buchanan   | Jaguars      |
| Buchanan   | Knights      |
| Buchanan   | Lakers       |
| Buchanan   | Mustangs     |
| ...                       |
+---------------------------+

FULL [OUTER] JOIN

FULL OUTER JOIN（简写为 FULL JOIN）返回两个 from_item 中满足联接条件的所有行的所有字段。

FULL 表示即使并非两个 from_item 中的所有行都符合联接条件，也全部返回。

OUTER 表示如果一个 from_item 中的给定行没有联接到其他 from_item 中的任何行，则该行将对另一个 from_item 中的所有列返回 NULL。

FROM A FULL OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A FULL OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

示例

此查询对 Roster 和 TeamMascot 表执行 FULL JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster FULL JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
| NULL       | Mustangs     |
+---------------------------+

LEFT [OUTER] JOIN

两个 from_item 的 LEFT OUTER JOIN（简写为 LEFT JOIN）结果将始终保留 JOIN 运算中左侧 from_item 的所有行，即使右侧 from_item 没有满足联接谓词的行也是如此。

LEFT 表示返回左侧 from_item 中的所有行；如果左侧 from_item 中的给定行没有联接到右侧 from_item 中的任何行，则该行将对右侧 from_item 中的所有列返回 NULL。右侧 from_item 中没有联接到左侧 from_item 的任何行都将被舍弃。

FROM A LEFT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | 2    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | 3    | n    |
+-------+     +-------+     | 3    | d    | 3    | m    |
                            | 3    | d    | 3    | n    |
                            +---------------------------+

FROM A LEFT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 1    | a    | NULL |
| 2 | b |     | 3 | m |     | 2    | b    | k    |
| 3 | c |     | 3 | n |     | 3    | c    | m    |
| 3 | d |     | 4 | p |     | 3    | c    | n    |
+-------+     +-------+     | 3    | d    | m    |
                            | 3    | d    | n    |
                            +--------------------+

示例

此查询对 Roster 和 TeamMascot 表执行 LEFT JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster LEFT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| Eisenhower | NULL         |
+---------------------------+

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN（简写为 RIGHT JOIN）的结果与 LEFT OUTER JOIN 类似，互为对称。

FROM A RIGHT OUTER JOIN B ON A.w = B.y

Table A       Table B       Result
+-------+     +-------+     +---------------------------+
| w | x |  *  | y | z |  =  | w    | x    | y    | z    |
+-------+     +-------+     +---------------------------+
| 1 | a |     | 2 | k |     | 2    | b    | 2    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | 3    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | 3    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | 3    | m    |
+-------+     +-------+     | 3    | d    | 3    | n    |
                            | NULL | NULL | 4    | p    |
                            +---------------------------+

FROM A RIGHT OUTER JOIN B USING (x)

Table A       Table B       Result
+-------+     +-------+     +--------------------+
| x | y |  *  | x | z |  =  | x    | y    | z    |
+-------+     +-------+     +--------------------+
| 1 | a |     | 2 | k |     | 2    | b    | k    |
| 2 | b |     | 3 | m |     | 3    | c    | m    |
| 3 | c |     | 3 | n |     | 3    | c    | n    |
| 3 | d |     | 4 | p |     | 3    | d    | m    |
+-------+     +-------+     | 3    | d    | n    |
                            | 4    | NULL | p    |
                            +--------------------+

示例

此查询对 Roster 和 TeamMascot 表执行 RIGHT JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster RIGHT JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
| NULL       | Mustangs     |
+---------------------------+

ON 子句

如果 ON 联接条件返回 TRUE，则组合的行（联接两行的结果）符合该联接条件。

FROM A JOIN B ON A.x = B.x

Table A   Table B   Result (A.x, B.x)
+---+     +---+     +-------+
| x |  *  | x |  =  | x | x |
+---+     +---+     +-------+
| 1 |     | 2 |     | 2 | 2 |
| 2 |     | 3 |     | 3 | 3 |
| 3 |     | 4 |     +-------+
+---+     +---+

示例

此查询对 Roster 和 TeamMascot 表执行 INNER JOIN。

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

+---------------------------+
| LastName   | Mascot       |
+---------------------------+
| Adams      | Jaguars      |
| Buchanan   | Lakers       |
| Coolidge   | Lakers       |
| Davis      | Knights      |
+---------------------------+

USING 子句

USING 子句需要一个列列表，其中包含出现在这两个输入表中的一个或多个列。该子句对这些列执行相等比较，如果相等比较返回 TRUE，则行满足联接条件。

FROM A JOIN B USING (x)

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

示例

此查询对 Roster 和 TeamMascot 表执行 INNER JOIN。

此语句返回 Roster 和 TeamMascot 中 Roster.SchooldID 与 TeamMascot.SchooldID 相同的行。结果包括一个 SchooldID 列。

SELECT * FROM Roster INNER JOIN TeamMascot USING (SchoolID);

+----------------------------------------+
| SchoolID   | LastName   | Mascot       |
+----------------------------------------+
| 50         | Adams      | Jaguars      |
| 52         | Buchanan   | Lakers       |
| 52         | Coolidge   | Lakers       |
| 51         | Davis      | Knights      |
+----------------------------------------+

ON 和 USING 的等效情况

ON 和 USING 关键字并不等效，但它们是类似的。ON 返回多列，USING 返回一列。

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

虽然 ON 和 USING 不等效，但如果您指定要返回的列，它们可以返回相同的结果。

SELECT x FROM A JOIN B USING (x);
SELECT A.x FROM A JOIN B ON A.x = B.x;

Table A   Table B   Result
+---+     +---+     +---+
| x |  *  | x |  =  | x |
+---+     +---+     +---+
| 1 |     | 2 |     | 2 |
| 2 |     | 3 |     | 3 |
| 3 |     | 4 |     +---+
+---+     +---+

序列中的联接操作

FROM 子句可以在一个序列中包含多个 JOIN 运算。JOIN 按从左到右的顺序绑定。例如：

FROM A JOIN B USING (x) JOIN C USING (x)

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

您还可以插入括号来对 JOIN 分组：

FROM ( (A JOIN B USING (x)) JOIN C USING (x) )

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2                  = return value

通过括号，您可以将 JOIN 分组，从而使它们按不同的顺序绑定：

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

-- B JOIN C USING (x)       = result_1
-- A JOIN result_1          = result_2
-- result_2                 = return value

FROM 子句可以有多个联接。如果 FROM 子句中没有逗号交叉联接，则联接不需要括号，但括号可增强可读性：

FROM A JOIN B JOIN C JOIN D USING (w) ON B.x = C.y ON A.z = B.x

如果子句包含逗号交叉联接，则必须使用括号：

FROM A, B JOIN C JOIN D ON C.x = D.y ON B.z = C.x    // INVALID

FROM A, B JOIN (C JOIN D ON C.x = D.y) ON B.z = C.x  // VALID

当逗号交叉联接出现在具有一系列 JOIN 的查询中时，它们会像其他 JOIN 类型一样按从左到右的顺序进行分组：

FROM A JOIN B USING (x) JOIN C USING (x), D

-- A JOIN B USING (x)        = result_1
-- result_1 JOIN C USING (x) = result_2
-- result_2 CROSS JOIN D     = return value

除非逗号交叉联接带括号，否则它后面不能有 RIGHT JOIN 或 FULL JOIN：

FROM A, B RIGHT JOIN C ON TRUE // INVALID

FROM A, B FULL JOIN C ON TRUE  // INVALID

FROM A, B JOIN C ON TRUE       // VALID

FROM A, (B RIGHT JOIN C ON TRUE) // VALID

FROM A, (B FULL JOIN C ON TRUE)  // VALID

相互关联的联接操作

当右侧的 from_item 包含对左侧 from_item 引入的至少一个范围变量或列名称的引用时，联接操作便是相互关联的联接。

在相互关联的联接操作中，右侧 from_item 的行由左侧 from_item 的行确定。因此，RIGHT OUTER 和 FULL OUTER 联接无法相互关联，因为在左侧 from_item 没有行的情况下无法确定右侧 from_item 的行。

所有相互关联的联接操作都必须引用右侧 from_item 的数组。

以下概念性示例展示了一个包含相互关联的子查询的相互关联的联接操作：

FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C

左侧 from_item：A
右侧 from_item：UNNEST(...) AS C
相互关联的子查询：(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)

这是另一个展示相互关联的联接操作的概念性示例。array_of_IDs 属于左侧 from_item，但在右侧 from_item 中进行引用。

FROM A JOIN UNNEST(A.array_of_IDs) AS C

UNNEST 运算符可以是显式运算符或隐式运算符。以下两者都被允许：

FROM A JOIN UNNEST(A.array_of_IDs) AS IDs

FROM A JOIN A.array_of_IDs AS IDs

在相互关联的联接操作中，系统会根据左侧 from_item 的每个不同行来重新计算右侧 from_item。在以下概念性示例中，相互关联的联接操作首先会计算 A 和 B，然后计算 A 和 C：

FROM
  A
  JOIN
  UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
  ON A.Name = C.Name

示例

以下示例展示了使用 Roster 和 PlayerStats 表的相互关联联接：

SELECT *
FROM
  Roster
JOIN
  UNNEST(
    ARRAY(
      SELECT AS STRUCT *
      FROM PlayerStats
      WHERE PlayerStats.OpponentID = Roster.SchoolID
    )) AS PlayerMatches
  ON PlayerMatches.LastName = 'Buchanan'

+------------+----------+----------+------------+--------------+
| LastName   | SchoolID | LastName | OpponentID | PointsScored |
+------------+----------+----------+------------+--------------+
| Adams      | 50       | Buchanan | 50         | 13           |
| Eisenhower | 77       | Buchanan | 77         | 0            |
+------------+----------+----------+------------+--------------+

WHERE 子句

WHERE bool_expression

WHERE 子句过滤 FROM 子句的结果。

只包含 bool_expression 计算结果为 TRUE 的行。bool_expression 计算结果为 NULL 或 FALSE 的行将被舍弃。

包含 WHERE 子句的查询的计算通常按以下顺序完成：

FROM
WHERE
GROUP BY 和聚合
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

WHERE 子句只能引用通过 FROM 子句提供的列；不能引用 SELECT 列表别名。

示例

此查询将返回 Roster 表中 SchoolID 列值为 52 的所有行：

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression 可以包含多个子条件：

SELECT * FROM Roster
WHERE STARTS_WITH(LastName, "Mc") OR STARTS_WITH(LastName, "Mac");

INNER JOIN 中的表达式具有 WHERE 子句中的等效表达式。例如，使用 INNER JOIN 和 ON 的查询具有使用 CROSS JOIN 和 WHERE 的等效表达式。例如，以下两个查询是等效的：

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

SELECT Roster.LastName, TeamMascot.Mascot
FROM Roster CROSS JOIN TeamMascot
WHERE Roster.SchoolID = TeamMascot.SchoolID;

GROUP BY 子句

GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }

GROUP BY 子句将表中在 GROUP BY 子句具有相同 expression 值的行合并。对于源表中具有相同 expression 值的多个行，GROUP BY 子句会生成一个组合行。当 SELECT 列表中存在聚合函数或要消除输出中的冗余时，通常使用 GROUP BY。expression 的数据类型必须是可分组的。

例如：

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName;

GROUP BY 子句可以引用 SELECT 列表中的表达式名称。GROUP BY 子句还允许使用整数值对 SELECT 列表中的表达式进行序号引用。1 是指在 SELECT 列表中的第一个表达式，2 是指第二个表达式，以此类推。表达式列表可以组合序号和表达式名称。

例如：

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY LastName, FirstName;

上面的查询等效于：

SELECT SUM(PointsScored), LastName, FirstName
FROM PlayerStats
GROUP BY 2, FirstName;

GROUP BY 子句也可能引用别名。如果查询包含 SELECT 子句中的别名，则这些别名会替换相应 FROM 子句中的名称。

例如：

SELECT SUM(PointsScored), LastName as last_name
FROM PlayerStats
GROUP BY last_name;

GROUP BY ROLLUP 返回对 ROLLUP 列表中表达式前缀进行 GROUP BY 操作的结果，每个结果称为分组集。对于 ROLLUP 列表 (a, b, c)，分组集为 (a, b, c)、(a, b)、(a)、()。在为特定分组集计算 GROUP BY 的结果时，GROUP BY ROLLUP 将不在分组集中的表达式视为具有 NULL 值。如下所示的 SELECT 语句：

SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);

使用汇总列表 (a, b)。结果将包括对分组集 (a, b)、(a) 和包括所有行的 () 进行 GROUP BY 操作的结果。这将返回以下行：

SELECT NULL, NULL, SUM(c) FROM Input               UNION ALL
SELECT a,    NULL, SUM(c) FROM Input GROUP BY a    UNION ALL
SELECT a,    b,    SUM(c) FROM Input GROUP BY a, b;

这允许计算对分组集的聚合，这些分组集由 ROLLUP 列表中的表达式和该列表的前缀定义。

例如：

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(day);

上面的查询除了输出所有天数的累积总数之外（如 NULL 日所示），还对每一天输出一行：

+------+-------+
| day  | total |
+------+-------+
| NULL | 39.77 |
|    1 | 23.54 |
|    2 |  9.99 |
|    3 |  6.24 |
+------+-------+

例如：

WITH Sales AS (
  SELECT 123 AS sku, 1 AS day, 9.99 AS price UNION ALL
  SELECT 123, 1, 8.99 UNION ALL
  SELECT 456, 1, 4.56 UNION ALL
  SELECT 123, 2, 9.99 UNION ALL
  SELECT 789, 3, 1.00 UNION ALL
  SELECT 456, 3, 4.25 UNION ALL
  SELECT 789, 3, 0.99
)
SELECT
  sku,
  day,
  SUM(price) AS total
FROM Sales
GROUP BY ROLLUP(sku, day)
ORDER BY sku, day;

上面的查询返回按以下分组集分组的行：

sku 和 day
sku（day 为 NULL）
空分组集（day 和 sku 为 NULL）

这些分组集的总和对应每个不同的 sku-day 组合的总和、所有天数中每个 sku 的总和以及所有总和：

+------+------+-------+
| sku  | day  | total |
+------+------+-------+
| NULL | NULL | 39.77 |
|  123 | NULL | 28.97 |
|  123 |    1 | 18.98 |
|  123 |    2 |  9.99 |
|  456 | NULL |  8.81 |
|  456 |    1 |  4.56 |
|  456 |    3 |  4.25 |
|  789 |    3 |  1.99 |
|  789 | NULL |  1.99 |
+------+------+-------+

HAVING 子句

HAVING bool_expression

HAVING 子句过滤由 GROUP BY 或聚合生成的结果。查询中必须存在 GROUP BY 或聚合。如果存在聚合，结果集中的每个聚合行都会计算 HAVING 子句一次。

只包含 bool_expression 计算结果为 TRUE 的行。bool_expression 计算结果为 NULL 或 FALSE 的行将被舍弃。

包含 HAVING 子句的查询的计算通常按以下顺序完成：

FROM
WHERE
GROUP BY 和聚合
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

HAVING 子句可以引用通过 FROM 子句提供的列以及 SELECT 列表别名。HAVING 子句中引用的表达式必须出现在 GROUP BY 子句中，或者它们必须是聚合函数的结果：

SELECT LastName
FROM Roster
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

如果查询包含 SELECT 子句中的别名，则这些别名会替换 FROM 子句中的名称。

SELECT LastName, SUM(PointsScored) AS ps
FROM Roster
GROUP BY LastName
HAVING ps > 0;

强制聚合

聚合不必存在于 HAVING 子句本身中，但聚合必须至少以下列一种形式存在：

`SELECT` 列表中的聚合函数。

SELECT LastName, SUM(PointsScored) AS total
FROM PlayerStats
GROUP BY LastName
HAVING total > 15;

HAVING 子句中的聚合函数。

SELECT LastName
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

`SELECT` 列表和 `HAVING` 子句中的聚合。

当聚合函数同时存在于 SELECT 列表和 HAVING 子句中时，聚合函数及其引用的列不需要相同。在下面的示例中，COUNT() 和 SUM() 这两个聚合函数是不同的，也使用不同的列。

SELECT LastName, COUNT(*)
FROM PlayerStats
GROUP BY LastName
HAVING SUM(PointsScored) > 15;

ORDER BY 子句

ORDER BY expression
  [{ ASC | DESC }]
  [{ NULLS FIRST | NULLS LAST }]
  [, ...]

ORDER BY 子句将某个列或表达式指定为结果集的排序标准。如果 ORDER BY 子句不存在，则查询结果的顺序未定义。允许 FROM 子句或 SELECT 列表中的列别名。如果查询包含 SELECT 子句中的别名，则这些别名会替换相应 FROM 子句中的名称。expression 的数据类型必须是可排序的。

可选子句

NULLS FIRST | NULLS LAST：
- NULLS FIRST：在非 null 值之前对 null 值进行排序。
- NULLS LAST：在非 null 值之后对 null 值进行排序。
ASC | DESC：按 expression 值的升序或降序顺序对结果进行排序。ASC 为默认值。如果未使用 NULLS FIRST 或 NULLS LAST 指定 null 排序，则：
- 如果排序顺序为升序，系统会默认应用 NULLS FIRST。
- 如果排序顺序为降序，系统会默认应用 NULLS LAST。

示例

使用默认排序顺序（升序）。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 1    | true  |
| 9    | true  |
+------+-------+

使用默认排序顺序（升序），但最后返回 null 值。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x NULLS LAST;
+------+-------+
| x    | y     |
+------+-------+
| 1    | true  |
| 9    | true  |
| NULL | false |
+------+-------+

使用降序排序顺序。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC;
+------+-------+
| x    | y     |
+------+-------+
| 9    | true  |
| 1    | true  |
| NULL | false |
+------+-------+

使用降序排序顺序，但先返回 null 值。

SELECT x, y
FROM (SELECT 1 AS x, true AS y UNION ALL
      SELECT 9, true UNION ALL
      SELECT NULL, false)
ORDER BY x DESC NULLS FIRST;
+------+-------+
| x    | y     |
+------+-------+
| NULL | false |
| 9    | true  |
| 1    | true  |
+------+-------+

可以按多列进行排序。在下面的示例中，结果集首先按 SchoolID 排序，然后按 LastName 排序：

SELECT LastName, PointsScored, OpponentID
FROM PlayerStats
ORDER BY SchoolID, LastName;

对值进行排序时，以下规则适用：

NULL：在 ORDER BY 子句的上下文中，NULL 是可能的最小值；也就是说，NULL 在 ASC 排序方向中出现在最前位置，在 DESC 排序方向中出现在最后位置。
浮点数据类型：请参阅浮点语义，了解排序和分组。

将 ORDER BY 子句与集合运算符一起使用时，该子句将应用于整个查询的结果集，而不仅仅应用于最近的 SELECT 语句。因此，使用括号显示 ORDER BY 的范围会很有帮助（但不是必需的）。

以下不带括号的查询：

SELECT * FROM Roster
UNION ALL
SELECT * FROM TeamMascot
ORDER BY SchoolID;

等效于以下带括号的查询：

( SELECT * FROM Roster
  UNION ALL
  SELECT * FROM TeamMascot )
ORDER BY SchoolID;

但不等效于以下查询，其中 ORDER BY 子句仅应用于第二个 SELECT 语句：

SELECT * FROM Roster
UNION ALL
( SELECT * FROM TeamMascot
  ORDER BY SchoolID );

您还可以在 ORDER BY 子句中使用整数字面量作为列引用。整数字面量成为 SELECT 列表中的序号（例如，从 1 开始计数）。

示例 - 以下两个查询是等效的：

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY LastName
ORDER BY LastName;

SELECT SUM(PointsScored), LastName
FROM PlayerStats
GROUP BY 2
ORDER BY 2;

QUALIFY 子句

QUALIFY bool_expression

QUALIFY 子句过滤分析函数的结果。分析函数必须存在于 QUALIFY 子句或 SELECT 列表中。

只包含 bool_expression 计算结果为 TRUE 的行。bool_expression 计算结果为 NULL 或 FALSE 的行将被舍弃。

包含 QUALIFY 子句的查询的计算通常按以下顺序完成：

FROM
WHERE
GROUP BY 和聚合
HAVING
WINDOW
QUALIFY
DISTINCT
ORDER BY
LIMIT

限制

QUALIFY 子句存在实现限制，它必须至少与以下一个子句一起使用：

WHERE
GROUP BY
HAVING

示例

以下查询会返回 Produce 表中最热门的蔬菜及其排名。

SELECT
  item,
  RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3

+---------+------+
| item    | rank |
+---------+------+
| kale    | 1    |
| lettuce | 2    |
| cabbage | 3    |
+---------+------+

您无需在 SELECT 列表中添加分析函数即可使用 QUALIFY。以下查询返回 Produce 表中最受欢迎的蔬菜。

SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3

+---------+
| item    |
+---------+
| kale    |
| lettuce |
| cabbage |
+---------+

WINDOW 子句

WINDOW named_window_expression [, ...]

named_window_expression:
  named_window AS { named_window | ( [ window_specification ] ) }

WINDOW 子句定义了一系列命名窗口。命名窗口表示表中要使用分析函数的一组行。命名窗口可通过窗口规范进行定义，也可以引用其他命名窗口。如果引用了另一命名窗口，则引用窗口的定义必须在引用窗口之前定义。

示例

一些示例引用了名为 Produce 的表。它们会返回相同的结果。请注意使用不同窗口组合的不同方式，并在分析函数的 OVER 子句中使用。

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
  PARTITION BY category
  ORDER BY purchases
  ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (d) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
  d AS (c)

SELECT item, purchases, category, LAST_VALUE(item)
  OVER (c ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
  a AS (PARTITION BY category),
  b AS (a ORDER BY purchases),
  c AS b

集合运算符

set_operation:
  query_expr set_operator query_expr

set_operator:
  UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT

集合运算符将两个或更多输入查询的结果合并到一个结果集中。必须指定 ALL 或 DISTINCT；如果指定 ALL，将保留所有行。如果指定 DISTINCT，将舍弃重复的行。

如果给定行 R 在第一个输入查询中正好出现 m 次，在第二个输入查询中出现 n 次（m >= 0，n >= 0）：

对于 UNION ALL，R 将在结果中正好出现 m + n 次。
对于 UNION DISTINCT，先计算 UNION，再计算 DISTINCT，因此 R 恰好出现一次。
对于 INTERSECT DISTINCT，先计算上述结果，再计算 DISTINCT。
对于 EXCEPT DISTINCT，如果 m > 0 且 n = 0，则行 R 在输出中出现一次。
如果输入查询超过两个，则上述运算会进行泛化，并且输出结果与输入从左到右递增组合的情形相同。

以下规则适用：

对于 UNION ALL 以外的集合运算，所有列类型都必须支持相等比较。
运算符两侧的输入查询都必须返回相同数量的列。
运算符根据列在各自 SELECT 列表中的位置对每个输入查询返回的列进行配对。也就是说，第一个输入查询中的第一列与第二个输入查询中的第一列配对。
结果集始终使用第一个输入查询中的列名称。
结果集始终在相应列中使用输入类型的超类型，因此配对列也必须具有相同的数据类型或通用超类型。
必须使用括号分隔不同的集合运算；因此，集合运算（如 UNION ALL 和 UNION DISTINCT）是不同的。如果语句只重复相同的集合运算，则不需要括号。

示例：

query1 UNION ALL (query2 UNION DISTINCT query3)
query1 UNION ALL query2 UNION ALL query3

无效：

query1 UNION ALL query2 UNION DISTINCT query3
query1 UNION ALL query2 INTERSECT ALL query3;  // INVALID.

UNION

UNION 运算符将每个查询的结果集中的列进行配对并以垂直方式连接这些列，以此来合并两个或更多输入查询的结果集。

INTERSECT

INTERSECT 运算符返回在左侧和右侧输入查询的结果集中都能找到的行。与 EXCEPT 不同，输入查询的定位（INTERSECT 运算符的左侧和右侧）无关紧要。

EXCEPT

EXCEPT 运算符返回左侧输入查询中不存在于右侧输入查询中的行。

例如：

SELECT * FROM UNNEST(ARRAY<int64>[1, 2, 3]) AS number
EXCEPT DISTINCT SELECT 1;

+--------+
| number |
+--------+
| 2      |
| 3      |
+--------+

LIMIT 和 OFFSET 子句

LIMIT count [ OFFSET skip_rows ]

LIMIT 指定 INT64 类型的非负 count，返回的行数不会超过 count。LIMIT 0 返回 0 行。

如果存在集合运算，则将在集合运算求值后应用 LIMIT。

OFFSET 指定在应用 LIMIT 之前要跳过的非负行数。skip_rows 的类型为 INT64。

这些子句只接受字面量或参数值。除非在 ORDER BY 之后使用这些运算符，否则 LIMIT 和 OFFSET 返回的行并不明确。

示例：

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 2

+---------+
| letter  |
+---------+
| a       |
| b       |
+---------+

SELECT *
FROM UNNEST(ARRAY<STRING>['a', 'b', 'c', 'd', 'e']) AS letter
ORDER BY letter ASC LIMIT 3 OFFSET 1

+---------+
| letter  |
+---------+
| b       |
| c       |
| d       |
+---------+

WITH 子句

WITH cte[, ...]

WITH 子句包含一个或多个常用的表表达式 (CTE)。CTE 充当临时表，您可以在单个查询表达式中引用该表。每个 CTE 都会将子查询的结果绑定到一个表名，您可以在同一查询表达式的其他位置使用该表名，但适用相关规则。

BigQuery 不会具体化 WITH 子句中的非递归 CTE 的结果。如果在查询中的多个位置引用非递归 CTE，则每次引用都执行一次该 CTE。含有非递归 CTE 的 WITH 子句主要用于提高可读性。

CTE

cte:
    cte_name AS ( query_expr )

通用表表达式 (CTE) 包含一个子查询和一个与该 CTE 关联的名称。

CTE 不能引用自身。
CTE 可以通过包含 WITH 子句的查询表达式来引用，但适用规则。

示例

在此示例中，WITH 子句定义了在相关 set 操作中引用的两个 CTE，其中每个 set 操作的输入查询表达式都引用了一个 CTE：

WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2

您可以将更复杂的查询分解为 WITH 子句和 WITH SELECT 语句，而不是编写嵌套表子查询。例如：

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2)

WITH q1 AS (my_query)
SELECT *
FROM
  (WITH q2 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q3 AS (SELECT * FROM q1),  # q1 resolves to my_query
        q1 AS (SELECT * FROM q1),  # q1 (in the query) resolves to my_query
        q4 AS (SELECT * FROM q1)   # q1 resolves to the WITH subquery on the previous line.
    SELECT * FROM q1)              # q1 resolves to the third inner WITH subquery.

CTE 规则和限制条件

通用表表达式 (CTE) 可以在包含 WITH 子句的查询表达式中引用。

下面是使用 CTE 时需要考虑的一些常规规则和限制条件：

同一 WITH 子句中的每个 CTE 必须具有唯一的名称。
在 WITH 子句中定义的 CTE 仅对在其后定义的同一 WITH 子句中的其他 CTE 可见。
本地 CTE 会覆盖外部同名的 CTE 或表。
子查询上的 CTE 可能不会引用外部查询中的相关列。

CTE 可见性

WITH 子句中常见表表达式 (CTE) 之间的引用可以向后进行，但不能向前进行。

当您在两个 WITH 子句中引用自己的 CTE 或相互引用它们时，就会发生这种情况。假设 A 是子句中的第一个 CTE，B 是子句中的第二个 CTE：

A 引用 A：无效
A 引用 B：无效
B 引用 A：有效
A 引用 B，B 引用 A：无效（不允许循环引用）

这会导致错误。A 不能引用自身，因为不支持自引用：

WITH
  A AS (SELECT 1 AS n UNION ALL (SELECT n + 1 FROM A WHERE n < 3))
SELECT * FROM A

-- Error

这会导致错误。A 不能引用 B，因为 CTE 之间的引用可以向后进行，但不能向前进行：

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT 1 AS n)
SELECT * FROM B

-- Error

B 可以引用 A，因为 CTE 之间的引用可以向后进行：

WITH
  A AS (SELECT 1 AS n),
  B AS (SELECT * FROM A)
SELECT * FROM B

+---+
| n |
+---+
| 1 |
+---+

这会导致错误。A 和 B 相互引用，这会产生循环引用：

WITH
  A AS (SELECT * FROM B),
  B AS (SELECT * FROM A)
SELECT * FROM B

-- Error

使用别名

别名是为查询中存在的表、列或表达式提供的临时名称。您可以在 SELECT 列表或 FROM 子句中引入显式别名，或者 BigQuery 将推断某些表达式的隐式别名。既没有显式别名也没有隐式别名的表达式是匿名的，查询无法通过名称引用它们。

显式别名

您可以在 FROM 子句或 SELECT 列表中引入显式别名。

在 FROM 子句中，可以使用 [AS] alias 为任何项（包括表、数组、子查询和 UNNEST 子句）引入显式别名。AS 关键字是可选的。

例如：

SELECT s.FirstName, s2.SongName
FROM Singers AS s, (SELECT * FROM Songs) AS s2;

可以使用 [AS] alias 为 SELECT 列表中的任何表达式引入显式别名。AS 关键字是可选的。

例如：

SELECT s.FirstName AS name, LOWER(s.FirstName) AS lname
FROM Singers s;

隐式别名

在 SELECT 列表中，如果存在没有显式别名的表达式，则 BigQuery 会根据以下规则分配隐式别名。在 SELECT 列表中可以有多个具有相同别名的列。

对于标识符，别名就是标识符。例如，SELECT abc 表示 AS abc。
对于路径表达式，别名是路径中的最后一个标识符。例如，SELECT abc.def.ghi 表示 AS ghi。
对于使用“点”成员字段访问运算符的字段访问，别名是字段名称。例如，SELECT (struct_function()).fname 表示 AS fname。

在所有其他情况下，不存在隐式别名，因此列是匿名的，不能通过名称引用。系统仍将返回该列的数据，显示的查询结果可能会有为该列生成的标签，但标签不能像别名一样使用。

在 FROM 子句中，from_item 不需要具有别名。以下规则适用：

如果存在没有显式别名的表达式，则 BigQuery 会在以下情况分配隐式别名：

对于标识符，别名就是标识符。例如，FROM abc 表示 AS abc。
对于路径表达式，别名是路径中的最后一个标识符。例如，FROM abc.def.ghi 表示 AS ghi。
使用 WITH OFFSET 生成的列具有隐式别名 offset。

表子查询不具有隐式别名。
FROM UNNEST(x) 不具有隐式别名。

别名可见性

在查询中引入显式别名后，在查询的其他位置引用该别名存在限制。这些对别名可见性的限制是 BigQuery 的名称范围限定规则引起的。

FROM 子句中的可见性

BigQuery 从左到右处理 FROM 子句中的别名，并且别名仅对 FROM 子句中的后续路径表达式可见。

例如：

假设 Singers 表有 ARRAY 类型的 Concerts 列。

SELECT FirstName
FROM Singers AS s, s.Concerts;

无效：

SELECT FirstName
FROM s.Concerts, Singers AS s;  // INVALID.

FROM 子句别名对同一 FROM 子句中的子查询不可见。FROM 子句中的子查询不能在同一 FROM 子句中包含对其他表的相关引用。

无效：

SELECT FirstName
FROM Singers AS s, (SELECT (2020 - ReleaseDate) FROM s)  // INVALID.

您可以在查询中的任何位置使用 FROM 中某个表的任何列名作为别名，无论是否具有表名称的资格。

例如：

SELECT FirstName, s.ReleaseDate
FROM Singers s WHERE ReleaseDate = 1975;

如果 FROM 子句包含显式别名，则必须在查询的其余部分使用显式别名而不是隐式别名（请参阅隐式别名）。如果出现自联接等情况，在查询处理期间多次扫描同一个表时，表别名可用于简化表达或消除歧义。

例如：

SELECT * FROM Singers as s, Songs as s2
ORDER BY s.LastName

无效 - ORDER BY 不使用表别名：

SELECT * FROM Singers as s, Songs as s2
ORDER BY Singers.LastName;  // INVALID.

SELECT 列表中的可见性

SELECT 列表中的别名仅对以下子句可见：

GROUP BY 子句
ORDER BY 子句
HAVING 子句

例如：

SELECT LastName AS last, SingerID
FROM Singers
ORDER BY last;

GROUP BY、ORDER BY、HAVING 子句中的可见性

GROUP BY、ORDER BY 和 HAVING 三个子句只能引用以下值：

FROM 子句中的表及其任何列。
SELECT 列表中的别名。

GROUP BY 和 ORDER BY 还可以引用第三个组：

整型字面量，其引用 SELECT 列表中的项。整数 1 表示 SELECT 列表中的第一项，2 表示第二项等。

例如：

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY 1
ORDER BY 2 DESC;

上述查询等效于以下查询：

SELECT SingerID AS sid, COUNT(Songid) AS s2id
FROM Songs
GROUP BY sid
ORDER BY s2id DESC;

别名重复

系统允许使用包含同一名称的多个显式或隐式别名的 SELECT 列表或子查询，只要查询中的其他位置没有引用该别名即可（因为引用可能会有歧义）。

如果顶级 SELECT 列表包含重复的列名称，并且未指定目标表，则系统会自动为所有重复的列（第一个重复的列除外）重命名，以使其名称具有唯一性。重命名的列会显示在查询结果中。

例如：

SELECT 1 AS a, 2 AS a;

+---+-----+
| a | a_1 |
+---+-----+
| 1 | 2   |
+---+-----+

系统不支持在表或视图定义中使用重复的列名称。以下语句将会失败，因为其查询中包含重复的列名称：

CREATE TABLE my_dataset.my_table AS (SELECT 1 AS a, 2 AS a);

CREATE VIEW my_dataset.my_view AS (SELECT 1 AS a, 2 AS a);

有歧义的别名

如果访问名称有歧义，则 BigQuery 会报错，这意味着该名称可以解析为查询或表架构（包括目标表的架构）中的不止一个唯一对象。

示例：

此查询包含表之间冲突的列名称，因为 Singers 和 Songs 都有一个名为 SingerID 的列：

SELECT SingerID
FROM Singers, Songs;

此查询包含在 GROUP BY 子句中有歧义的别名，因为它们在 SELECT 列表中重复：

SELECT FirstName AS name, LastName AS name,
FROM Singers
GROUP BY name;

此查询包含在 SELECT 列表和 FROM 子句中有歧义的别名，因为它们的名称相同：假设 table 包含 x 列、y 列和 z 列。z 是 STRUCT 类型，具有字段 v、w、x。

例如：

SELECT x, z AS T
FROM table AS T
GROUP BY T.x;

别名 T 有歧义，会产生错误，因为 GROUP BY 子句中的 T.x 可能引用 table.x 或 table.z.x。

如果某名称既是列名又是 SELECT 列表别名，那么只要该名称解析为相同的基础对象，就不会在 GROUP BY、ORDER BY 或 HAVING 中产生歧义。

例如：

SELECT LastName, BirthYear AS BirthYear
FROM Singers
GROUP BY BirthYear;

别名 BirthYear 不会有歧义，因为它解析为相同的基础列 Singers.BirthYear。

范围变量

在 BigQuery 中，范围变量是 FROM 子句中的表表达式别名。有时，范围变量称为 table alias。范围变量允许您引用正在从表表达式中扫描的行。表表达式表示 FROM 子句中返回表的项。此表达式可以表示的常用项包括表、值表、子查询、联接和带圆括号的联接。

通常，范围变量提供对表表达式行的引用。范围变量可用于限定列引用并明确标识相关表，例如 range_variable.column_1。

在没有指定列后缀的情况下直接引用范围变量时，表表达式的结果是相关表的行类型。值表具有显式行类型，因此对于与值表相关的范围变量，结果类型是值表的行类型。其他表没有显式行类型，对于这些表，范围变量类型是一个动态定义的 STRUCT，其中包含表中的所有列。

示例

在这些示例中，WITH 子句用于模拟名为 Grid 的临时表。此表包含 x 和 y 列。名为 Coordinate 的范围变量指的是系统扫描表时的当前行。Coordinate 可用于访问该行中的整行或整列。

以下示例从范围变量 Coordinate 中选择列 x，这实际上从表 Grid 中选择列 x。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.x FROM Grid AS Coordinate;

+---+
| x |
+---+
| 1 |
+---+

以下示例从范围变量 Coordinate 中选择所有列，这实际上从表 Grid 中选择所有列。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate.* FROM Grid AS Coordinate;

+---+---+
| x | y |
+---+---+
| 1 | 2 |
+---+---+

以下示例选择范围变量 Coordinate，它是对表 Grid 中的行的引用。由于 Grid 不是值表，因此 Coordinate 的结果类型为 STRUCT，其中包含 Grid 中的所有列。

WITH Grid AS (SELECT 1 x, 2 y)
SELECT Coordinate FROM Grid AS Coordinate;

+--------------+
| Coordinate   |
+--------------+
| {x: 1, y: 2} |
+--------------+

附录 A：样本数据示例

这些示例包括对 Roster、TeamMascot、PlayerStats 表执行查询的语句。

GROUP BY 子句

例如：

SELECT LastName, SUM(PointsScored)
FROM PlayerStats
GROUP BY LastName;

LastName	SUM
Adams	7
Buchanan	13
Coolidge	1

UNION

UNION 运算符将每个 SELECT 语句的结果集中的列进行配对并以垂直方式连接这些列，以此来合并两个或更多 SELECT 语句的结果集。

例如：

SELECT Mascot AS X, SchoolID AS Y
FROM TeamMascot
UNION ALL
SELECT LastName, PointsScored
FROM PlayerStats;

结果：

X	Y
Jaguars	50
Knights	51
Lakers	52
Mustangs	53
Adams	3
Buchanan	0
Coolidge	1
Adams	4
Buchanan	13

INTERSECT

此查询将返回 Roster 和 PlayerStats 中存在的姓氏。

SELECT LastName
FROM Roster
INTERSECT DISTINCT
SELECT LastName
FROM PlayerStats;

结果：

LastName
Adams
Coolidge
Buchanan

EXCEPT

下面的查询返回 Roster 中存在而 PlayerStats 中不存在的姓氏。

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

结果：

LastName
Eisenhower
Davis

颠倒 SELECT 语句的顺序将返回 PlayerStats 中存在而 Roster 中不存在的姓氏：

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

结果：

(empty)

标准 SQL 中的查询语法

SQL 语法

示例表

Roster 表

PlayerStats 表

TeamMascot 表

SELECT 列表

SELECT *

SELECT expression

SELECT expression.*

* 运算符的修饰符

SELECT * EXCEPT

SELECT * REPLACE

重复行处理

SELECT DISTINCT

SELECT ALL

值表

SELECT AS STRUCT

SELECT AS VALUE

别名

FROM 子句

pivot_operator

unpivot_operator

tablesample_operator

table_name

FOR SYSTEM_TIME AS OF

join_operation

query_expr

field_path

unnest_operator

cte_name

UNNEST 运算符

UNNEST 和 STRUCT

显式和隐式 UNNEST

UNNEST 和 NULL

PIVOT 运算符

UNPIVOT 运算符

TABLESAMPLE 运算符

JOIN 运算

[内]连接

CROSS JOIN

逗号交叉联接 (,)

FULL [OUTER] JOIN

LEFT [OUTER] JOIN

RIGHT [OUTER] JOIN

ON 子句

USING 子句

ON 和 USING 的等效情况

序列中的联接操作

相互关联的联接操作

WHERE 子句

GROUP BY 子句

HAVING 子句

强制聚合

SELECT 列表中的聚合函数。

HAVING 子句中的聚合函数。

SELECT 列表和 HAVING 子句中的聚合。

ORDER BY 子句

QUALIFY 子句

WINDOW 子句

集合运算符

UNION

INTERSECT

EXCEPT

LIMIT 和 OFFSET 子句

WITH 子句

CTE

示例

CTE 规则和限制条件

CTE 可见性

使用别名

显式别名

隐式别名

别名可见性

FROM 子句中的可见性

SELECT 列表中的可见性

GROUP BY、ORDER BY、HAVING 子句中的可见性

别名重复

有歧义的别名

范围变量

SELECT `expression`

SELECT `expression.*`

`SELECT` 列表中的聚合函数。

`SELECT` 列表和 `HAVING` 子句中的聚合。