標準 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] }

標記法

  • 角括弧 "[ ]" 表示選擇性子句。
  • 括號 "( )" 表示文字括號。
  • 分隔號 "|" 表示邏輯上的「或」。
  • 大括號 "{ }" 用於括住一組選項。
  • 角括弧內的逗號後面加上刪節號 "[, ... ]" 表示前述項目可能會在逗號分隔清單中重複。

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 星號萬用字元」,會在執行完整查詢後,針對每個顯示的資料欄,產生一個輸出資料欄。

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

您可以修改從 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 天前更早的時間。

單一查詢陳述式不能參照多個時間點 (包括目前時間) 的單一資料表。也就是說,查詢可多次參照同一個時間戳記的資料表,但無法同時參照目前和歷史的資料表版本,或兩個不同的歷史版本。

範例:

下列查詢會傳回資料表 1 小時前的歷史版本。

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 子句之外使用 UNNEST 搭配 IN 運算子

對於大多數元素類型的輸入 ARRAYUNNEST 的輸出通常含有一個資料欄。這個單一資料欄有一個選擇性的 alias,用於在查詢中的其他位置參照該資料欄。這些元素類型的 ARRAYS 會傳回多個資料欄:

  • STRUCT

UNNEST 會刪除輸入 ARRAY 中的元素順序。使用選擇性的 WITH OFFSET 子句可傳回含有陣列元素索引的第二個資料欄 (如下所示)。

對於 STRUCTARRAY 輸入值,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}     |
+---+-----+--------------+

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 對於空值的處理方式如下:

  • 空值與空白 ARRAY 不會產生任何資料列。
  • 包含空值的 ARRAY 會產生包含空值的資料列。

選擇性的 WITH OFFSET 子句會為 UNNEST 作業產生的每個資料列傳回包含「偏移」值 (亦即從零開始計算) 的獨立資料欄。這個資料欄有選擇性的 alias;預設別名為 offset。

範例:

SELECT * FROM UNNEST ( ) WITH OFFSET AS num;

如需更多使用 UNNEST 的方法,包括建構、扁平化與篩選,請參閱 Arrays topic 說明。

with_query_name

WITH 子句 (請參閱 WITH 子句一節) 中的查詢名稱作用和臨時資料表名稱相同,您可以在 FROM 子句的任何位置參照查詢名稱。在下列範例中,subQ1subQ2 就是 with_query_names

範例:

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

WITH 會在查詢期間內隱藏所有同名的永久性資料表,除非您限定資料表名稱,例如:dataset.Rosterproject.dataset.Roster

子查詢

子查詢是顯示在其他陳述式內且使用括號括住的查詢。子查詢也稱為「子 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

JOIN 子句需要聯結條件,除非下列其中一項條件為 true:

  • 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.

如要瞭解 JSON 序列中的逗號交叉聯結行為,請參閱 JOIN 序列

FULL [OUTER] JOIN

FULL OUTER JOIN (或 FULL JOIN) 會傳回兩個 from_item 中符合聯結條件的所有資料列的所有欄位。

FULL 表示即使不符合聯結條件,仍會傳回兩個 from_item 中的「所有資料列」

OUTER 表示如果一個 from_item 中的指定資料列未與另一個 from_item 中的任何資料列聯結,該資料列會以空值傳回另一個 from_item 中的所有資料欄。

LEFT [OUTER] JOIN

兩個 from_itemLEFT OUTER JOIN (或 LEFT JOIN) 的結果會一律保留 JOIN 子句左側 from_item 的所有資料列,即使右側 from_item 中沒有資料列滿足聯結述詞仍是如此。

LEFT 表示會傳回「左側」from_item 中的所有資料列;如果左側 from_item 中的指定資料列未與「右側」from_item 中的任何資料列聯結,則這個資料列會以 NULL 傳回右側 from_item 中的所有資料欄。右側 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;

當您使用 SELECT * 時,使用 USING 的查詢結果與使用 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");

您不能在 WHERE 子句參照 SELECT 清單中的資料欄別名。

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 子句可將資料表中 expression 的值無明顯不同的資料列群組在 GROUP BY 子句中。針對來源資料表中 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);

使用了 Rollup 清單 (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 與天數
  • SKU (天數為 NULL)
  • 空白群組集 (天數與 SKU 為 NULL)

這些群組集的總和對應於每個不同 SKU-天數組合的總計、所有天數內每個 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 子句的差異在於:

  • 查詢中必須存在 GROUP BY 或匯總才能使用 HAVING 子句。
  • HAVING 子句的位置在 GROUP BY 與匯總之後、ORDER BY 之前。這意味著結果集中每個匯總的資料列都會進行一次 HAVING 字句的評估。這與在 GROUP BY 與匯總之前評估的 WHERE 子句不同。

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;

排序值的規則如下:

  • 空值:在 ORDER BY 子句的內容中,空值是最小的可能值;也就是說,以 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,而且不會傳回超過 count 個資料列。LIMIT 0 會傳回 0 個資料列。如果有集合運算,則會在評估集合運算後再套用 LIMIT

OFFSET 用於指定 INT64 類型的正整數 skip_rows,而且只會考慮資料表中來自該偏移的資料列。

這些子句只接受文字或參數值。

除非 LIMITOFFSET 使用於 ORDER 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 子句,clauses, where the less desirable alternative is writing nested table subqueries. 如果 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 資料表含有 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 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,也可能參照別名 AS LastName (值為 UPPER(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

如果 GROUP BYORDER BYHAVING 中的名稱既是資料欄名稱,也是 SELECT 清單別名,則只要將名稱解析成相同的基礎物件,就不是模稜兩可的名稱。

範例:

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)
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁