GoogleSQL

　　查询语法query_statement:     query_expr  query_expr:     [ WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...] ]     { select | ( query_expr ) | set_operation }     [ ORDER BY expression [{ ASC | DESC }] [, ...] ]     [ LIMIT count [ OFFSET skip_rows ] ]  select:     SELECT         [ { ALL | DISTINCT } ]         [ AS { STRUCT | VALUE } ]         select_list     [ FROM from_clause[, ...] ]     [ WHERE bool_expression ]     [ GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) } ]     [ HAVING bool_expression ]     [ QUALIFY bool_expression ]     [ WINDOW window_clause ]
　　表示法规则 方括号 [ ] 表示可选子句。 圆括号 ( ) 表示文本括号。 竖线 | 表示逻辑 OR。 大括号 { } 括起一组可选项。 方括号内后跟省略号的逗号 [, ... ] 表示可以在逗号分隔列表中重复其前面的项。
　　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 PlayerStatsSELECT语句SELECT     [ { ALL | DISTINCT } ]     [ AS { STRUCT | VALUE } ]    select_list  select_list:     { select_all | select_expression } [, ...]  select_all:     [ expression. ]*     [ EXCEPT ( column_name [, ...] ) ]     [ REPLACE ( expression [ AS ] column_name [, ...] ) ]  select_expression:     expression [ [ AS ] alias ]SELECT *
　　SELECT * 通常称为查询星号，会对执行完整查询后可见的每个列生成一个输出列。SELECT * FROM (SELECT ＂apple＂ AS fruit, ＂carrot＂ AS vegetable);  +-------+-----------+ | fruit | vegetable | +-------+-----------+ | apple | carrot    | +-------+-----------+SELECT expression.*
　　SELECT 列表中的项也可以采用 expression.* 的形式。这会为每个列或 expression 的顶级字段生成一个输出列。表达式必须是表别名或者其计算结果是带有字段的数据类型（如 STRUCT）的单个值。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>[(＂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 * REPLACEWITH 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 AS STRUCTSELECT AS STRUCT expr [[AS] struct_field_name1] [,...]
　　此语法会生成一个行类型为 STRUCT 的值表，其中 STRUCT 字段名称和类型与 SELECT 列表中生成的列名称和类型相匹配。SELECT ARRAY(SELECT AS STRUCT 1 a, 2 b)SELECT AS VALUE
　　SELECT AS VALUE 从任何只生成一列的 SELECT 列表生成一个值表。输出将是一个值表，而不是生成具有一列的输出表（可能具有名称），其中行类型只是在一个 SELECT 列中生成的值类型。该列所具有的任何别名都将在值表中被舍弃。SELECT AS VALUE STRUCT(1 AS a, 2 AS b) xyzFROM子句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 ] aliasFOR SYSTEM_TIME AS OF-- 返回表在过去一个小时内的历史版本 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;-- 查询返回错误，因为 DML 对表的当前版本以及一天前的表的历史版本进行操作  INSERT INTO t1 SELECT * FROM t1   FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);UNNEST运算符
　　UNNEST 运算符接受数组，并返回一个表，数组中的每个元素占一行。您还可以在 FROM 子句外部将 UNNEST 与 IN 运算符搭配使用。unnest_operator:     {       UNNEST( array_expression )       | UNNEST( array_path )       | array_path     }     [ as_alias ]     [ WITH OFFSET [ as_alias ] ]  as_alias:     [AS] aliasUNNEST和结构体
　　对于结构体的输入数组，UNNEST 会为每个结构体返回一行，并在结构体中为每个字段返回单独的列。每列的别名是相应结构体字段的名称。SELECT * FROM UNNEST(   ARRAY<     STRUCT<       x INT64,       y STRING,       z STRUCT>>[         (1, ＂foo＂, (10, 11)),         (3, ＂bar＂, (20, 21))]);  +---+-----+----------+ | x | y   | z        | +---+-----+----------+ | 1 | foo | {10, 11} | | 3 | bar | {20, 21} | +---+-----+----------+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_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;UNNEST和NULLULL 和空数组会生成零行。 包含 NULL 的数组会生成包含 NULL 值的行。 UNNEST和WITH OFFSET
　　可选的 WITH OFFSET 子句会返回一个包含偏移量值的单独列，其中 UNNEST 运算生成的每一行会从零开始计数。该列具有一个可选的别名；如果未使用可选别名，则默认列名称为 offset。SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;  +---------+--------+ | numbers | offset | +---------+--------+ | 10      | 0      | | 20      | 1      | | 30      | 2      | +---------+--------+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 | year | +---------+-------+---------+------| | Kale    | 51    | Q1      | 2020 | | Kale    | 23    | Q2      | 2020 | | Kale    | 45    | Q3      | 2020 | | Kale    | 3     | Q4      | 2020 | | Kale    | 70    | Q1      | 2021 | | Kale    | 85    | Q2      | 2021 | | Apple   | 77    | Q1      | 2020 | | Apple   | 0     | Q2      | 2020 | | Apple   | 1     | Q1      | 2021 | +---------+-------+---------+------+  -- After PIVOT is used to rotate sales and quarter into Q1, Q2, Q3, Q4 columns: +---------+------+----+------+------+------+ | product | year | Q1 | Q2   | Q3   | Q4   | +---------+------+----+------+------+------+ | Apple   | 2020 | 77 | 0    | NULL | NULL | | Apple   | 2021 | 1  | NULL | NULL | NULL | | Kale    | 2020 | 51 | 23   | 45   | 3    | | Kale    | 2021 | 70 | 85   | NULL | NULL | +---------+------+----+------+------+------+-- 聚合函数 SUM 按除了 pivot_column 以外的所有未聚合列（product 和 year）进行隐式分组。  SELECT * FROM   Produce   PIVOT(SUM(sales) FOR quarter IN (＂Q1＂, ＂Q2＂, ＂Q3＂, ＂Q4＂))  +---------+------+----+------+------+------+ | product | year | Q1 | Q2   | Q3   | Q4   | +---------+------+----+------+------+------+ | Apple   | 2020 | 77 | 0    | NULL | NULL | | Apple   | 2021 | 1  | NULL | NULL | NULL | | Kale    | 2020 | 51 | 23   | 45   | 3    | | Kale    | 2021 | 70 | 85   | NULL | NULL | +---------+------+----+------+------+------+-- 如果您未添加 year，则 SUM 只会按 product 进行分组。  SELECT * FROM   (SELECT product, sales, quarter FROM Produce)   PIVOT(SUM(sales) FOR quarter IN (＂Q1＂, ＂Q2＂, ＂Q3＂, ＂Q4＂))  +---------+-----+-----+------+------+ | product | Q1  | Q2  | Q3   | Q4   | +---------+-----+-----+------+------+ | Apple   | 78  | 0   | NULL | NULL | | Kale    | 121 | 108 | 45   | 3    | +---------+-----+-----+------+------+SELECT * FROM   (SELECT product, sales, quarter FROM Produce)   PIVOT(SUM(sales) FOR quarter IN (＂Q1＂, ＂Q2＂, ＂Q3＂)) +---------+-----+-----+------+ | product | Q1  | Q2  | Q3   | +---------+-----+-----+------+ | Apple   | 78  | 0   | NULL | | Kale    | 121 | 108 | 45   | +---------+-----+-----+------+SELECT * FROM   (SELECT product, sales, quarter FROM Produce)   PIVOT(SUM(sales) FOR quarter IN (＂Q1＂, ＂Q2＂, ＂Q3＂)) +---------+-----+-----+------+ | product | Q1  | Q2  | Q3   | +---------+-----+-----+------+ | Apple   | 78  | 0   | NULL | | Kale    | 121 | 108 | 45   | +---------+-----+-----+------+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      | +---------+-------+---------+-- 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运算符-- 选择表中约 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 [, ...] )[INNER] JOINFROM A INNER JOIN B ON A.w = B.y
　　FROM A INNER JOIN B USING (x)
　　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 JOINFROM A CROSS JOIN B
　　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     | | ...                       | +---------------------------+逗号交叉联接 (,)FROM A, B
　　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] JOINFROM A FULL OUTER JOIN B ON A.w = B.y
　　FROM A FULL OUTER JOIN B USING (x)
　　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] JOINFROM A LEFT OUTER JOIN B ON A.w = B.y
　　FROM A LEFT OUTER JOIN B USING (x)
　　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] JOINFROM A RIGHT OUTER JOIN B ON A.w = B.y
　　FROM A RIGHT OUTER JOIN B USING (x)
　　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子句FROM A JOIN B ON A.x = B.x
　　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子句FROM A JOIN B USING (x)
　　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)
　　-- 虽然 ON 和 USING 不等效，但如果您指定要返回的列，它们可以返回相同的结果。  SELECT x FROM A JOIN B USING (x); SELECT A.x FROM A JOIN B ON A.x = B.x;
　　序列中的联接操作-- 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  // 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 JOIN C ON TRUE       // VALID  FROM A, (B RIGHT JOIN C ON TRUE) // VALID  FROM A, (B FULL JOIN C ON TRUE)  // VALID相互关联的联接操作FROM A JOIN UNNEST(ARRAY(SELECT AS STRUCT * FROM B WHERE A.ID = B.ID)) AS C
　　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            | +------------+----------+----------+------------+--------------+-- 相关 LEFT JOIN 的常见模式是在右侧执行 UNNEST 运算，并引用左侧输入引入的某个列的数组。对于该数组为空或 NULL 的行，UNNEST 运算在右侧的输入上不生成行。在这种情况下，系统会创建右侧输入对应列中具有 NULL 条目的行，以便与左侧输入中的行进行联接。  SELECT A.name, item, ARRAY_LENGTH(A.items) item_count_for_name FROM   UNNEST(     [       STRUCT(         ＂first＂ AS name,         [1, 2, 3, 4] AS items),       STRUCT(         ＂second＂ AS name,         [] AS items)]) AS A LEFT JOIN   A.items AS item;  +--------+------+---------------------+ | name   | item | item_count_for_name | +--------+------+---------------------+ | first  | 1    | 4                   | | first  | 2    | 4                   | | first  | 3    | 4                   | | first  | 4    | 4                   | | second | NULL | 0                   | +--------+------+---------------------+-- 对于相关 CROSS JOIN，当右侧的输入对于左侧的某个行为空时，则从结果中删除最后一行  SELECT A.name, item FROM   UNNEST(     [       STRUCT(         ＂first＂ AS name,         [1, 2, 3, 4] AS items),       STRUCT(         ＂second＂ AS name,         [] AS items)]) AS A CROSS JOIN   A.items AS item;  +-------+------+ | name  | item | +-------+------+ | first | 1    | | first | 2    | | first | 3    | | first | 4    | +-------+------+WHERE子句WHERE bool_expression-- bool_expression 可以包含多个子条件  SELECT * FROM Roster WHERE STARTS_WITH(LastName, ＂Mc＂) OR STARTS_WITH(LastName, ＂Mac＂);GROUP BY子句GROUP BY { expression [, ...] | ROLLUP ( expression [, ...] ) }SELECT SUM(PointsScored), LastName, FirstName FROM PlayerStats GROUP BY LastName, FirstName;SELECT SUM(PointsScored), LastName, FirstName FROM PlayerStats GROUP BY 2, FirstName;SELECT SUM(PointsScored), LastName as last_name FROM PlayerStats GROUP BY last_name;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;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);
　　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;
　　HAVING子句HAVING bool_expressionSELECT列表中的聚合函数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 LastName, COUNT(*) FROM PlayerStats GROUP BY LastName HAVING SUM(PointsScored) > 15;ORDER BY子句ORDER BY expression   [{ ASC | DESC }]   [{ NULLS FIRST | NULLS LAST }]   [, ...]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 | +------+-------+QUALIFY子句
　　QUALIFY 子句过滤窗口函数的结果。QUALIFY 子句或 SELECT 列表中必须存在窗口函数。QUALIFY bool_expressionSELECT   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 item FROM Produce WHERE Produce.category = ＂vegetable＂ QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3  +---------+ | item    | +---------+ | kale    | | lettuce | | cabbage | +---------+WINDOW子句
　　WINDOW 子句定义了一系列命名窗口。命名窗口表示表中要使用窗口函数的一组行。命名窗口可通过窗口规范进行定义，也可以引用其他命名窗口。如果引用了另一命名窗口，则引用窗口的定义必须在引用窗口之前定义。WINDOW named_window_expression [, ...]  named_window_expression:   named_window AS { named_window | ( [ window_specification ] ) }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对于 UNION ALL，R 将在结果中正好出现 m + n 次。 对于 UNION DISTINCT，先计算 UNION，再计算 DISTINCT，因此 R 恰好出现一次。 对于 INTERSECT DISTINCT，先计算上述结果，再计算 DISTINCT。 对于 EXCEPT DISTINCT，如果 m > 0 且 n = 0，则行 R 在输出中出现一次。 如果输入查询超过两个，则上述运算会进行泛化，并且输出结果与输入从左到右递增组合的情形相同。 query1 UNION ALL (query2 UNION DISTINCT query3) query1 UNION ALL query2 UNION ALL query3
　　》》》UNION
　　UNION 运算符将每个查询的结果集中的列进行配对并以垂直方式连接这些列，以此来合并两个或更多输入查询的结果集。
　　》》》INTERSECT
　　INTERSECT 运算符返回在左侧和右侧输入查询的结果集中都能找到的行。与 EXCEPT 不同，输入查询的定位（INTERSECT 运算符的左侧和右侧）无关紧要。
　　》》》EXCEPT
　　EXCEPT 运算符返回左侧输入查询中不存在于右侧输入查询中的行。SELECT * FROM UNNEST(ARRAY[1, 2, 3]) AS number EXCEPT DISTINCT SELECT 1;  +--------+ | number | +--------+ | 2      | | 3      | +--------+LIMIT和OFFSET子句LIMIT 指定 INT64 类型的非负 count，返回的行数不会超过 count。LIMIT 0 返回 0 行。
　　如果存在集合运算，则将在集合运算求值后应用 LIMIT。
　　OFFSET 指定在应用 LIMIT 之前要跳过的非负行数。skip_rows 的类型为 INT64。LIMIT count [ OFFSET skip_rows ]SELECT * FROM UNNEST(ARRAY[＂a＂, ＂b＂, ＂c＂, ＂d＂, ＂e＂]) AS letter ORDER BY letter ASC LIMIT 2  +---------+ | letter  | +---------+ | a       | | b       | +---------+SELECT * FROM UNNEST(ARRAY[＂a＂, ＂b＂, ＂c＂, ＂d＂, ＂e＂]) AS letter ORDER BY letter ASC LIMIT 3 OFFSET 1  +---------+ | letter  | +---------+ | b       | | c       | | d       | +---------+WITH子句
　　WITH 子句包含一个或多个常用的表表达式 (CTE)。CTE 充当临时表，您可以在单个查询表达式中引用该表。WITH [ RECURSIVE ] { non_recursive_cte | recursive_cte }[, ...]RECURSIVE关键字在 WITH 子句中启用递归。如果此关键字不存在，则只能包含非递归通用表表达式 (CTE)。如果存在此关键字，则您可以同时使用递归和非递归 CTE。 在 WITH 子句中更改 CTE 可见性。如果此关键字不存在，则 CTE 只会向 WITH 子句中定义在它后面的 CTE 显示。如果此关键字存在，则 CTE 会向定义了它的 WITH 子句中的所有 CTE 显示。 非递归 CTE非递归 CTE 不能引用自身。 非递归 CTE 可以通过包含 WITH 子句的查询表达式进行引用，但会应用规则。 non_recursive_cte:     cte_name AS ( query_expr )WITH subQ1 AS (SELECT SchoolID FROM Roster),      subQ2 AS (SELECT OpponentID FROM PlayerStats) SELECT * FROM subQ1 UNION ALL SELECT * FROM subQ2WITH 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 会引用其自身。 递归 CTE 可在包含 WITH 子句的查询表达式中进行引用，但会应用规则。 在 WITH 子句中定义递归 CTE 时，必须存在 RECURSIVE 关键字。 base_term：运行递归联合操作的第一次迭代。此术语必须遵循基本术语规则。 union_operator：UNION 运算符返回来自基本术语和递归术语的并集的行。借助 UNION ALL，迭代 N 中生成的每一行都会成为最终 CTE 结果和迭代 N+1 的输入的一部分。如果迭代没有生成要进入下一次迭代的行，则迭代会停止。 recursive_term：运行其余迭代。它必须包含对递归 CTE 的一个自引用（递归引用）。只有该术语可以包含自引用。该术语必须遵循递归术语规则。 recursive_cte:     cte_name AS ( recursive_union_operation )  recursive_union_operation:     base_term union_operator recursive_term  base_term:     query_expr  recursive_term:     query_expr  union_operator:     UNION ALLWITH RECURSIVE   T1 AS ( (SELECT 1 AS n) UNION ALL (SELECT n + 1 AS n FROM T1 WHERE n < 3) ) SELECT n FROM T1  +---+ | n | +---+ | 2 | | 1 | | 3 | +---+WITH RECURSIVE   T1 AS (     (SELECT 1 AS n) UNION ALL     (SELECT n + 2 FROM T1 WHERE n < 4)) SELECT * FROM T1 ORDER BY n  +---+ | n | +---+ | 1 | | 3 | | 5 | +---+-- 只要每个递归的周期长度为 1，同一递归 CTE 中有多个子查询则可以接受。递归条目也可以依赖于非递归条目，反之亦然。  WITH RECURSIVE   T0 AS (SELECT 1 AS n),   T1 AS ((SELECT * FROM T0) UNION ALL (SELECT n + 1 FROM T1 WHERE n < 4)),   T2 AS ((SELECT 1 AS n) UNION ALL (SELECT n + 1 FROM T2 WHERE n < 4)),   T3 AS (SELECT * FROM T1 INNER JOIN T2 USING (n)) SELECT * FROM T3 ORDER BY n  +---+ | n | +---+ | 1 | | 2 | | 3 | | 4 | +---+-- 只要聚合函数未在所定义的表中聚合，便可以在子查询中进行调用：  WITH RECURSIVE   T0 AS (SELECT * FROM UNNEST ([60, 20, 30])),   T1 AS ((SELECT 1 AS n) UNION ALL (SELECT n + (SELECT COUNT(*) FROM T0) FROM T1 WHERE n < 4)) SELECT * FROM T1 ORDER BY n  +---+ | n | +---+ | 1 | | 4 | +---+
　　CTE 规则和限制条件
　　CTE 可见性