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 可见性
CBA爆发激烈冲突!爱德华兹故意绊倒黄荣奇,吴羽佳为队友强出头CBA常规赛第35轮继续进行,经过四节较量,江苏男篮以9991战胜同曦男篮,结束12连败,值得注意的是,此前同曦正是在江苏身上结束了自己超长的27连败,可以说是难兄难弟相互送温暖,
追梦效应!昨日和追梦同时在场的15分钟里库里得41分反之仅得6分直播吧3月16日讯昨日NBA常规赛,勇士主场126112战胜奇才。此役追梦格林迎来复出。追梦替补出场20分钟,投篮4中2,三分3中1,罚球2中1,得到6分7篮板6助攻1抢断,出现3
为保家卫国!洛马琴科放弃了与小坎博索斯冠军争霸赛洛马琴科与现役WBC(特许冠军)WBAIBFWBO轻量级拳王小乔治坎博索斯的冠军赛原计划今年6月打响。由于俄乌冲突爆发,洛马琴科为了守护家园,加入了乌克兰的特种部队防御营,目前正在
北交所加快培育上市后备军近日,全国股转公司新修订的全国中小企业股份转让系统分层管理办法及配套指南正式发布。新办法在新三板进层时间安排上做了重大调整,由此前每年4月30日启动定期调入,改为上半年2月至6月逐
窦骁这豪门姑爷的位置稳了吗?豪门大瓜一向走在八卦前沿,若有点风吹草动,狗仔们立即拿起16倍镜反复侦查。也不知道从哪儿吹来的风说窦骁准备跟赌王之女何超莲完婚,原因是女方已经怀孕并且窦骁还准备10亿的聘礼要将女方
中三角,机会来了长江中游城市群,这个概念,提出来应该超过十年了!那时候,还俗称中三角。今天,这个方案,终于经批准发布了。内容几十条,前后看完,我理解三句话定位,方向,路径。1。定位很长一段话,但核
35mm焦段镜头优势有多大?努比亚Z40Pro用真实拍照体验告诉你春节过后的1个多月,多款手机扎堆发布,但能够勾引用户购买欲望的手机却少得可怜。在陷入技术创新匮乏的泥潭后,从性能,到拍照,再到快充,手机同质化太严重了。不过,努比亚Z40Pro的发
春吃护生草,明目强骨,老少皆宜!2种做法一定要知道3月插杨柳,吃荠菜荠菜被称为报春菜,其清香可口风味独特。苏东坡曾赞之天然之珍,虽小甘于五味,而有味外之美。但其实,荠菜除了味道鲜美,营养价值也不低1hr人称护春草,味美营养好荠菜分
深棕色的服装还挺有气质,就是有点显老,学蒋勤勤搭配不老气棕色系的服装不知不觉就火了很久了,既有满满的高级范,还有复古典雅的效果。比黑色的更有特点,还没有白色这么清纯。不过深棕色的服装选不对比黑色的还要显老。不仅穿不同的颜色服装能让棕色的
你知道什么是知性吗?30的女人如何穿出知性风女人,二十岁的时候,身材和长相都达到了巅峰。一旦过了30岁之后,只要能做到对自己的形象进行精细化管理,依旧可以活出韵味优雅有格调。此时,在穿衣风格上,也会发生不小的变化,今天就为大
牛仔裤和皮鞋这样搭配,太绝了这次介绍经典男士休闲风单品牛仔裤与给人美好成熟印象的皮鞋搭配的技巧哦!牛仔裤和皮鞋的搭配技巧王道是避免鞋子太正式或太考究没有特别的规矩,但是休闲装搭配牛仔裤,选择一款对皮鞋来说不太