标准 SQL 查询语法

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

SQL 语法

query_statement:
    [ WITH with_query_name AS ( query_expr ) [, ...] ]
query_expr query_expr: { select | ( query_expr ) | query_expr set_op query_expr } [ ORDER BY expression [{ ASC | DESC }] [, ...] ] [ LIMIT count [ OFFSET skip_rows ] ] select: SELECT [{ ALL | DISTINCT }] { [ expression. ]* [ EXCEPT ( column_name [, ...] ) ]
[ REPLACE ( expression [ AS ] column_name [, ...] ) ]
| expression [ [ AS ] alias ] } [, ...] [ FROM from_item [, ...] ] [ WHERE bool_expression ] [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ] [ HAVING bool_expression ] [ WINDOW window_name AS ( window_definition ) [, ...] ] set_op: UNION { ALL | DISTINCT } | INTERSECT DISTINCT | EXCEPT DISTINCT from_item: { table_name [ [ AS ] alias ] [ FOR SYSTEM TIME AS OF timestamp_expression ] | join | ( query_expr ) [ [ AS ] alias ] | field_path | { UNNEST( array_expression ) | UNNEST( array_path ) | array_path } [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] | with_query_name [ [ AS ] alias ] } join: from_item [ join_type ] JOIN from_item [ { ON bool_expression | USING ( join_column [, ...] ) } ] join_type: { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

批注:

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

SELECT 列表

语法:

SELECT  [{ 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)的数据类型的单个值。

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

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 修饰符

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

SELECT DISTINCT

SELECT DISTINCT 语句会丢弃重复行并仅返回剩余行。SELECT DISTINCT 不能返回以下类型的列:

  • STRUCT
  • ARRAY

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 * EXCEPT 不会排除没有名称的列。

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 * REPLACE 不会替换没有名称的列。

SELECT ALL

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

别名

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

分析函数

与分析函数相关的子句记录在其他文档中。

FROM 子句

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

语法

from_item: {
    table_name [ [ AS ] alias ] [ FOR SYSTEM TIME AS OF timestamp_expression ]  |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    field_path |
    { UNNEST( array_expression ) | UNNEST( array_path ) | array_path }
        [ [ AS ] alias ] [ WITH OFFSET [ [ AS ] alias ] ] |
    with_query_name [ [ AS ] alias ]
}

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 OFFROM 子句中的源表不得为以下中的任何一种:

  • 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);

join

请参阅下面的 JOIN 类型

select

( select ) [ [ 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 封装路径,或使用完全限定的路径。

UNNEST

UNNEST 运算符传入一个 ARRAY,并返回一个表,ARRAY 中的每个元素占一行。还可以在 FROM 子句之外将 UNNESTIN 运算符搭配使用。

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

  • STRUCT

UNNEST 会破坏输入 ARRAY 中元素的顺序。请使用可选的 WITH OFFSET 子句返回具有数组元素索引的第二列(见下文)。

对于 STRUCT 的输入 ARRAYUNNEST 将对每个 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}     |
+---+-----+--------------+

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

示例:

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

在隐式解除嵌套中,array_path 必须解析为数组,而 UNNEST 关键字是可选的。

示例:

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

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

UNNEST 按如下方式处理 NULL:

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

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

示例:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

请参阅 Arrays topic,了解使用 UNNEST 的更多方法,包括构建、展平和过滤。

with_query_name

WITH 子句(请参阅 WITH 子句)中的查询名称与临时表的名称相似,您可以在 FROM 子句中的任何位置引用这些名称。在下面的示例中,subQ1subQ2with_query_names

示例:

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

除非限定表名,例如 dataset.Rosterproject.dataset.Roster,否则 WITH 子句会在查询期间隐藏任何具有相同名称的永久表。

子查询

子查询是出现在另一个语句内的查询,写在括号内。这些查询也称为“子 SELECT”或“嵌套 SELECT”。整个 SELECT 语法在子查询中都有效。

子查询有两种类型:

  • 表达式子查询,只要表达式有效,就可以在查询中使用。表达式子查询返回一个值。
  • 表子查询,只能在 FROM 子句中使用。外部查询将子查询的结果视为表。

请注意,这两种类型的子查询都必须用括号括起来。

示例:

SELECT AVG ( PointsScored )
FROM
( SELECT PointsScored
  FROM Stats
  WHERE SchoolID = 77 )

表子查询也可以具有别名。

示例:

SELECT r.LastName
FROM
( SELECT * FROM Roster) AS r;

别名

请参阅别名,了解 FROM 子句别名的语法和可见性。

JOIN 类型

语法

join:
    from_item [ join_type ] JOIN from_item
    [ ON bool_expression | USING ( join_column [, ...] ) ]

join_type:
    { INNER | CROSS | FULL [OUTER] | LEFT [OUTER] | RIGHT [OUTER] }

JOIN 子句合并两个 from_item,以便 SELECT 子句可以将它们作为一个源进行查询。join_typeONUSING 子句(“连接条件”)指定如何组合和舍弃两个 from_item 中的行来形成单个源。

所有 JOIN 子句都需要 join_type

除非以下有一个条件为 true,否则 JOIN 子句需要连接条件:

  • join_typeCROSS
  • 一个或两个 from_item 不是表,例如 array_pathfield_path

[INNER] JOIN

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

CROSS JOIN

CROSS JOIN 返回两个 from_item 的笛卡尔积。换句话说,它将保留两个 from_item 中的所有行,并将第一个 from_item 的每一行与第二个 from_item 的每一行组合。

逗号交叉连接

CROSS JOIN 可以显式写出(参见上文),也可通过用逗号分隔 from_item 来隐式写出。

隐式“逗号交叉连接”的示例:

SELECT * FROM Roster, TeamMascot;

以下内容等效于显式交叉连接:

SELECT * FROM Roster CROSS JOIN TeamMascot;

逗号交叉连接不能写在括号内。

无效 - 括号内的逗号交叉连接:

SELECT * FROM t CROSS JOIN (Roster, TeamMascot);  // INVALID.

请参阅 JOIN 序列,详细了解逗号交叉连接在一系列 JOIN 中的行为。

FULL [OUTER] JOIN

FULL OUTER JOIN(简写为 FULL JOIN)可返回两个 from_item 中满足连接条件的所有行的所有字段。

FULL 表示即使两个 from_item 中的所有行不符合连接条件也都会被返回

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

LEFT [OUTER] JOIN

即使右侧 from_item 没有行满足连接谓词,两个 from_itemLEFT OUTER JOIN(简写为 LEFT JOIN)结果也将始终保留 JOIN 子句中左侧 from_item 的所有行。

LEFT 表示返回左侧 from_item 中的所有行;如果左侧 from_item 中的给定行没有连接到右侧 from_item 中的任何行,则该行将对右侧 from_item 中的所有列返回 NULL。如果右侧 from_item 有行没有连接到左侧 from_item 中的任何行,则舍弃这些行。

RIGHT [OUTER] JOIN

RIGHT OUTER JOIN(简写为 RIGHT JOIN)的结果类似,与 LEFT OUTER JOIN 的结果对称。

ON 子句

ON 子句包含一个 bool_expression。如果 bool_expression 返回 TRUE,则组合的行(连接两行的结果)符合连接条件。

示例:

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

USING 子句

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

在大多数情况下,使用 USING 关键字的语句等效于使用 ON 关键字。例如,语句:

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
USING (LastName);

等效于:

SELECT FirstName
FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

使用 USING 的查询结果与在使用 SELECT * 时使用 ON 的查询不同。为便于说明,请参考以下查询:

SELECT * FROM Roster INNER JOIN PlayerStats
USING (LastName);

此语句返回 RosterPlayerStats 中的行,其中 Roster.LastNamePlayerStats.LastName 相同。结果包括一个 LastName 列。

相比之下,请参考以下查询:

SELECT * FROM Roster INNER JOIN PlayerStats
ON Roster.LastName = PlayerStats.LastName;

此语句返回 RosterPlayerStats 中的行,其中 Roster.LastNamePlayerStats.LastName 相同。结果包括两个 LastName 列;一个来自 Roster,一个来自 PlayerStats

JOIN 序列

FROM 子句可以在序列中包含多个 JOIN 子句。

示例:

SELECT * FROM a LEFT JOIN b ON TRUE LEFT JOIN c ON TRUE;

其中 abc 为任意 from_item。虽然 JOIN 是按从左到右的顺序绑定的,但可以通过插入括号,按其他顺序对它们进行分组。

请参考以下查询:A(无括号)和 B(有括号)互相等效,但与 C 不等效。粗体FULL JOIN 优先绑定。

A.

SELECT * FROM Roster FULL JOIN TeamMascot USING (SchoolID)
FULL JOIN PlayerStats USING (LastName);

B.

SELECT * FROM ( (Roster FULL JOIN TeamMascot USING (SchoolID))
FULL JOIN PlayerStats USING (LastName));

C.

SELECT * FROM (Roster FULL JOIN (TeamMascot FULL JOIN PlayerStats USING
(LastName)) USING (SchoolID)) ;

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

示例:

SELECT * FROM a JOIN b ON TRUE, b JOIN c ON TRUE;

上面的查询等效于

SELECT * FROM ((a JOIN b ON TRUE) CROSS JOIN b) JOIN c ON TRUE);

逗号连接后不能有 RIGHT JOINFULL JOIN

无效 - 逗号交叉连接后的 RIGHT JOIN

SELECT * FROM Roster, TeamMascot RIGHT JOIN PlayerStats ON TRUE;  // INVALID.

WHERE 子句

语法

WHERE bool_expression

WHERE 子句根据 bool_expression 计算每一行,以此来过滤行,并舍弃所有未返回 TRUE 的行(即返回 FALSE 或 NULL 的行)。

示例:

SELECT * FROM Roster
WHERE SchoolID = 52;

bool_expression 可以包含多个子条件。

示例:

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

不能在 SELECT 子句中引用 WHERE 列表的列别名。

INNER JOIN 中的表达式具有 WHERE 子句中的等效表达式。例如,使用 INNER JOINON 的查询具有使用 CROSS JOINWHERE 的等效表达式。

示例 - 此查询:

SELECT * FROM Roster INNER JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

等效于:

SELECT * 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 BYexpression 的数据类型必须是可分组的

示例:

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 子句与 WHERE 子句类似:它会过滤掉在根据 bool_expression 进行计算后未返回 TRUE 的行。

WHERE 子句一样,bool_expression 可以是任何返回布尔值的表达式,并且可以包含多个子条件。

HAVING 子句与 WHERE 子句的不同之处在于:

  • HAVING 子句要求查询中存在 GROUP BY 或聚合。
  • HAVING 子句出现在 GROUP BY 和聚合之后,ORDER BY 之前。这意味着结果集中的每个聚合行都会计算 HAVING 子句一次。这与 WHERE 子句不同,该字句在 GROUP BY 和聚合之前计算。

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 }] [, ...]

ORDER BY 子句将某个列或表达式指定为结果集的排序标准。如果 ORDER BY 子句不存在,则查询结果的顺序未定义。默认的排序方向是 ASC,即按 expression 值的升序对结果排序。DESC 则为按降序对结果排序。允许 FROM 子句或 SELECT 列表中的列别名。如果查询包含 SELECT 子句中的别名,则这些别名会覆盖相应 FROM 子句中的名称。

可以按多列进行排序。在下面的示例中,结果集首先按 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
ORDER BY LastName;
SELECT SUM(PointsScored), LastName
FROM PlayerStats
ORDER BY 2;

集合运算符

语法

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

集合运算符将两个或更多输入查询的结果合并到一个结果集中。必须指定 ALLDISTINCT;如果指定 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 ALLUNION 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 运算符返回左侧输入查询中不存在于右侧输入查询中的行。

LIMIT 子句和 OFFSET 子句

语法

LIMIT count [ OFFSET skip_rows ]

LIMIT 指定 INT64 类型的非负 count,返回的行数不会超过 countLIMIT 0 返回 0 行。如果存在集合运算,则将在集合运算求值后应用 LIMIT

OFFSET 指定 INT64 类型的非负 skip_rows,并且只考虑表中具有该偏移量的行。

这些子句只接受字面量或参数值。

除非在 LIMIT 之后使用这些运算符,否则 OFFSETORDER BY 返回的行未指定。

WITH 子句

WITH 子句包含一个或多个已命名的子查询,每次后续 SELECT 语句引用它们时都会执行这些子查询。任何子句或子查询都可以引用 WITH 子句中定义的子查询。这包括集合运算符(例如 UNION)任一侧的任何 SELECT 语句。

WITH 子句主要用于提高可读性,因为 BigQuery 不会具体化 WITH 子句中的查询结果。如果查询出现在多个 WITH 子句中,则它将在每个子句中执行。

示例:

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 子句,其中不太理想的替代方法是编写嵌套表子查询。如果 WITH 子句包含多个子查询,则子查询名称不能重复。

BigQuery 在表子查询、表达式子查询等子查询中支持 WITH 子句。

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

以下是 WITH 子句的范围限定规则:

  • 别名的范围被限定:WITH 子句中引入的别名仅在同一 WITH 子句中的后续子查询中以及 WITH 子句下的查询中可见。
  • 虽然在同一个 WITH 子句中引入的别名必须是唯一的,但在同一查询中的多个 WITH 子句中可以使用相同的别名。本地别名会覆盖本地别名可见的任何位置的任何外部别名。
  • WITH 子句中有别名的子查询始终不会关联。查询外部的任何列都不可见。外部唯一可见的名称是早先在同一个 WITH 子句中引入的其他 WITH 别名。

以下是在 WITH 子查询中使用别名的语句示例:

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.

BigQuery 不支持 WITH RECURSIVE

别名

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

显式别名语法

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

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

示例:

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

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

示例:

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

显式别名可见性

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

FROM 子句别名

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

示例:

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

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 BYORDER BYHAVING 三个子句只能引用以下值:

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

GROUP BYORDER 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;

有歧义的别名

如果名称有歧义,BigQuery 会报错,说明该名称可以解析为不止一个唯一对象。

示例:

此查询包含表之间冲突的列名称,因为 SingersSongs 都有一个名为 SingerID 的列:

SELECT SingerID
FROM Singers, Songs;

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

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

FROM 子句列名称和 GROUP BY 中的 SELECT 列表别名之间的歧义:

SELECT UPPER(LastName) AS LastName
FROM Singers
GROUP BY LastName;

上面的查询有歧义,会产生错误,因为 GROUP BY 子句中的 LastName 可以引用 Singers 中的原始列 LastName,或者它可以引用值为 UPPER(LastName) 的别名 AS LastName

相同的歧义规则适用于路径表达式。请参考以下查询,其中 table 具有列 xy,列 z 的类型为 STRUCT 并且具有字段 vwx

示例:

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

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

如果名称既是列名又是 SELECT 列表别名,只要名称解析为相同的基础对象,那么名称在 GROUP BYORDER BYHAVING 中都没有歧义。

示例:

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

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

隐式别名

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) 不具有隐式别名。

附录 A:示例与样本数据

示例表

以下三个表包含有关运动员、他们的学校以及他们在赛季中得分的样本数据。这些表将用于说明不同查询子句的行为。

表 Roster:

LastName SchoolID
Adams 50
Buchanan 52
Coolidge 52
Davis 51
Eisenhower 77

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

表 PlayerStats:

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

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

表 TeamMascot:

SchoolId Mascot
50 Jaguars
51 Knights
52 Lakers
53 Mustangs

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

JOIN 类型

1) [INNER] JOIN

示例:

SELECT * FROM Roster JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

结果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights

2) CROSS JOIN

示例:

SELECT * FROM Roster CROSS JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

结果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Adams 50 51 Knights
Adams 50 52 Lakers
Adams 50 53 Mustangs
Buchanan 52 50 Jaguars
Buchanan 52 51 Knights
Buchanan 52 52 Lakers
Buchanan 52 53 Mustangs
Coolidge 52 50 Jaguars
Coolidge 52 51 Knights
Coolidge 52 52 Lakers
Coolidge 52 53 Mustangs
Davis 51 50 Jaguars
Davis 51 51 Knights
Davis 51 52 Lakers
Davis 51 53 Mustangs
Eisenhower 77 50 Jaguars
Eisenhower 77 51 Knights
Eisenhower 77 52 Lakers
Eisenhower 77 53 Mustangs

3) FULL [OUTER] JOIN

示例:

SELECT * FROM Roster FULL JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;
LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL
NULL NULL 53 Mustangs

4) LEFT [OUTER] JOIN

示例:

SELECT * FROM Roster LEFT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

结果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Buchanan 52 52 Lakers
Coolidge 52 52 Lakers
Davis 51 51 Knights
Eisenhower 77 NULL NULL

5) RIGHT [OUTER] JOIN

示例:

SELECT * FROM Roster RIGHT JOIN TeamMascot
ON Roster.SchoolID = TeamMascot.SchoolID;

结果:

LastName Roster.SchoolId TeamMascot.SchoolId Mascot
Adams 50 50 Jaguars
Davis 51 51 Knights
Coolidge 52 52 Lakers
Buchanan 52 52 Lakers
NULL NULL 53 Mustangs

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
Mustangs 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)
此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面