Beam ZetaSQL 查詢語法

查詢語句掃描一個或多個表格、串流或表達式，並傳回計算結果的列。

SQL 語法

query_statement:
    query_expr

query_expr:
    [ WITH with_query_name AS ( 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 [ [ AS ] alias ] } [, ...]
    [ FROM from_item ]
    [ WHERE bool_expression ]
    [ GROUP BY expression [, ...] ]
    [ HAVING bool_expression ]

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

from_item: {
    table_name [ [ AS ] alias ] |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    with_query_name [ [ AS ] alias ]
}
table_name:
    identifier [ . identifier ...]

join:
    from_item [ join_type ] JOIN from_item
    ON bool_expression

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

符號表示法

• 方括號 "[ ]" 表示可選子句。
• 括號 "( )" 表示字面括號。
• 垂直線 "|" 表示邏輯 OR。
• 大括號 "{ }" 包含一組選項。
• 方括號內的逗號後接省略號 "[, ... ]" 表示前面的項目可以以逗號分隔的清單重複。

SELECT 清單

語法

SELECT  [ ALL ]
    { * | expression [ [ AS ] alias ] } [, ...]

SELECT 清單定義查詢將傳回的欄位。SELECT 清單中的表達式可以參考其對應 FROM 子句中任何 from_item 中的欄位。

SELECT 清單中的每個項目都是以下之一

• *
• 表達式

SELECT *

SELECT *，通常稱為select star，會針對執行完整查詢後可見的每個欄位產生一個輸出欄位。

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

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

SELECT expression

注意：在頂層 SELECT 中，您必須使用明確選取的欄位名稱，或者如果您使用表達式，則必須使用明確的別名。

SELECT 清單中的項目可以是表達式。這些表達式會評估為單一值並產生一個輸出欄位，並帶有可選的明確 alias。

如果表達式沒有明確的別名，它會根據隱含別名的規則接收隱含別名（如果可能）。否則，該欄位是匿名的，您無法在查詢的其他地方以名稱參考它。

SELECT 修飾符

您可以修改從 SELECT 查詢傳回的結果，如下所示。

SELECT ALL

SELECT ALL 語句會傳回所有列，包括重複的列。SELECT ALL 是 SELECT 的預設行為。

別名

請參閱別名，以取得 SELECT 清單別名的語法和可見性資訊。

FROM 子句

FROM 子句表示要從中擷取列的表格或串流，並指定如何將這些列聯結在一起，以產生單一列串流，以便在查詢的其餘部分中處理。

語法

from_item: {
    table_name [ [ AS ] alias ] |
    join |
    ( query_expr ) [ [ AS ] alias ] |
    with_query_name [ [ AS ] alias ]
}

table_name

Beam SQL 可查詢的資料來源的完整 SQL 名稱，由使用 Standard SQL 詞彙結構的以點分隔的識別符號清單指定。您必須使用反引號來括住包含非字母、數字或底線的字元的識別符號。

SELECT * FROM bigquery.table.`my-project`.baseball.roster;
SELECT * FROM pubsub.topic.`my-project`.incoming_events;

join

請參閱下面的 JOIN 型別。

select

( select ) [ [ AS ] alias ] 是一個表格子查詢。

with_query_name

WITH 子句中的查詢名稱（請參閱WITH 子句）的作用就像臨時表格的名稱，您可以在 FROM 子句中的任何位置參考它。在下面的範例中，subQ1 和 subQ2 是 with_query_names。

範例

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

WITH 子句會在查詢期間隱藏任何同名的永久表格，除非您限定表格名稱，例如

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

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

JOIN 子句會合併兩個 from_item，以便 SELECT 子句可以將它們作為一個來源查詢。join_type 和 ON 子句（「聯結條件」）指定如何組合和捨棄兩個 from_item 的列，以形成單一來源。

所有 JOIN 子句都需要 join_type。

[INNER] JOIN

INNER JOIN，或簡稱為 JOIN，會有效地計算兩個 from_item 的笛卡爾積，並捨棄所有不符合聯結條件的列。「有效地」表示可以實作 INNER 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，LEFT OUTER JOIN（或簡稱為 LEFT JOIN）的結果始終會保留 JOIN 子句中左側 from_item 的所有列，即使右側 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;

JOIN 序列

FROM 子句可以依序包含多個 JOIN 子句。

範例

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

其中 a、b 和 c 是任何 from_item。JOIN 會從左到右繫結，但您可以插入括號以不同的順序對它們進行分組。

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 清單的欄位別名。

GROUP BY 子句

語法

GROUP BY expression [, ...]

GROUP BY 子句會將資料表中的列，依據 GROUP BY 子句中 expression 的非相異值分組。對於來源資料表中具有 expression 非相異值的多個列，GROUP BY 子句會產生一個合併的單一列。當 SELECT 清單中存在彙總函式，或為了消除輸出中的冗餘時，通常會使用 GROUP BY。expression 的資料類型必須是可分組的。

範例

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

GROUP BY 子句可以參照 SELECT 清單中的運算式名稱。GROUP BY 子句也允許使用整數值來參照 SELECT 清單中運算式的序數。1 表示 SELECT 清單中的第一個運算式，2 表示第二個，依此類推。運算式清單可以結合序數和運算式名稱。

範例

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

上述查詢等效於

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

GROUP BY 子句也可以參照別名。如果查詢在 SELECT 子句中包含別名，這些別名會覆蓋對應 FROM 子句中的名稱。

範例

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

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;

LIMIT 子句和 OFFSET 子句

語法

[ ORDER BY expression [{ASC | DESC}] [,...] ] LIMIT count [ OFFSET skip_rows ]

ORDER BY 子句指定欄位或運算式作為結果集的排序條件。如果不存在 ORDER BY 子句，則查詢結果的順序未定義。預設排序方向為 ASC，它會以 expression 值的遞增順序排序結果。DESC 會以遞減順序排序結果。允許使用來自 FROM 子句或 SELECT 清單的欄位別名。如果查詢在 SELECT 子句中包含別名，這些別名會覆蓋對應 FROM 子句中的名稱。

可以依多個欄位排序。

排序值時適用下列規則

• NULL：在 ORDER BY 子句的上下文中，NULL 是最小值；也就是說，NULL 會在 ASC 排序中先出現，在 DESC 排序中最後出現。

LIMIT 指定 INT64 類型的非負 count，且最多只會傳回 count 列。LIMIT 0 會傳回 0 列。如果有集合運算，則會在評估集合運算之後套用 LIMIT。

OFFSET 指定 INT64 類型的非負 skip_rows，並且只會考慮該偏移量之後的資料列。

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

除非這些運算子在 ORDER BY 之後使用，否則 LIMIT 和 OFFSET 傳回的資料列是不明確的。

WITH 子句

WITH 子句會將一或多個具名子查詢的結果繫結到暫存資料表名稱。每個引入的資料表名稱在同一個查詢運算式中後續的 SELECT 運算式中都可見。這包括下列類型的 SELECT 運算式

• 後續 WITH 繫結中的任何 SELECT 運算式
• 集合運算子（例如 UNION）兩側查詢運算式中的頂層 SELECT 運算式
• 同一個查詢運算式內子查詢中的 SELECT 運算式

範例

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

以下是 WITH 子句的範圍規則

• 別名的範圍是讓 WITH 子句中引入的別名僅在同一個 WITH 子句中稍後的子查詢，以及在 WITH 子句下的查詢中可見。
• 在同一個 WITH 子句中引入的別名必須是唯一的，但同一個別名可以在同一個查詢的多個 WITH 子句中使用。局部別名會覆蓋局部別名可見的任何位置的任何外部別名。

Beam SQL 不支援 WITH RECURSIVE。

別名

別名是給予查詢中存在的資料表、欄位或運算式的暫時名稱。您可以在 SELECT 清單或 FROM 子句中引入明確的別名。

明確別名語法

您可以在 FROM 子句或 SELECT 清單中引入明確的別名。

在 FROM 子句中，您可以使用 [AS] alias 為任何項目（包括資料表、陣列和子查詢）引入明確的別名。AS 關鍵字是選用的。

範例

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

您可以使用 [AS] alias 為 SELECT 清單中的任何運算式引入明確的別名。AS 關鍵字是選用的。

範例

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

明確別名可見性

在查詢中引入明確的別名之後，您可以在查詢中的其他位置參考該別名，但會有一些限制。這些別名可見性的限制是 Beam SQL 名稱範圍規則的結果。

FROM 子句別名

Beam SQL 會從左到右處理 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;

SELECT 清單別名

SELECT 清單中的別名僅對下列子句可見

• GROUP BY 子句
• ORDER BY 子句
• HAVING 子句

範例

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

GROUP BY、ORDER BY 和 HAVING 子句中的明確別名

這三個子句（GROUP BY、ORDER BY 和 HAVING）只能參照下列值

• FROM 子句中的資料表及其任何欄位。
• SELECT 清單中的別名。

GROUP BY 和 ORDER BY 也可以參照第三組

• 整數常值，其參照 SELECT 清單中的項目。整數 1 參照 SELECT 清單中的第一個項目，2 參照第二個項目，依此類推。

範例

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

上述查詢等效於

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

不明確的別名

如果名稱不明確，也就是說，它可以解析為多個唯一物件，Beam SQL 會提供錯誤。

範例

此查詢包含資料表之間衝突的欄位名稱，因為 Singers 和 Songs 都有一個名為 SingerID 的欄位

SELECT SingerID
FROM Singers, Songs;

此查詢包含在 GROUP BY 子句中不明確的別名，因為它們在 SELECT 清單中重複

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

GROUP BY 中 FROM 子句欄位名稱和 SELECT 清單別名之間的歧義

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

上述查詢是不明確的，並且會產生錯誤，因為 GROUP BY 子句中的 LastName 可能參照 Singers 中的原始欄位 LastName，也可能參照別名 AS LastName，其值為 UPPER(LastName)。

相同的歧義規則適用於路徑運算式。請考量以下查詢，其中 table 具有欄位 x 和 y，且欄位 z 的類型為 STRUCT，並具有欄位 v、w 和 x。

範例

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

別名 T 不明確，並會產生錯誤，因為 GROUP BY 子句中的 T.x 可以參照 table.x 或 table.z.x。

只要名稱解析為相同的底層物件，則當名稱既是欄位名稱又是 SELECT 清單別名時，該名稱在 GROUP BY、ORDER BY 或 HAVING 中就不會不明確。

範例

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

別名 BirthYear 並不明確，因為它解析為相同的底層欄位 Singers.BirthYear。

附錄 A：使用範例資料的範例

範例表格

以下三個資料表包含關於運動員、他們的學校以及他們在賽季中得分的樣本資料。這些資料表將用來說明不同查詢子句的行為。

資料表 Roster

Roster 資料表包含球員姓名 (LastName) 和分配給他們學校的唯一 ID (SchoolID) 的清單。

資料表 PlayerStats

PointsScored

PlayerStats 資料表包含球員姓名 (LastName) 和他們在特定比賽中對手 (OpponentID) 的唯一 ID，以及運動員在該比賽中獲得的分數 (PointsScored) 的清單。

Mustangs

JOIN 型別

TeamMascot 資料表包含唯一的學校 ID (SchoolID) 和該學校的吉祥物 (Mascot) 的清單。

範例

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

1) [INNER] JOIN

TeamMascot.SchoolId

範例

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

NULL

範例

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

1) [INNER] JOIN

3) LEFT [OUTER] JOIN

範例

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

1) [INNER] JOIN

GROUP BY 子句

範例

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

集合運算子

SUM

UNION 運算子會透過配對每個 SELECT 陳述式結果集中的欄位，並以垂直方式串連它們，來組合兩個或更多 SELECT 陳述式的結果集。

範例

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

1) [INNER] JOIN

INTERSECT

此查詢會傳回同時存在於 Roster 和 PlayerStats 中的姓氏。

SELECT LastName
FROM Roster
INTERSECT ALL
SELECT LastName
FROM PlayerStats;

1) [INNER] JOIN

EXCEPT

以下查詢會傳回 Roster 中不存在於 PlayerStats 中的姓氏。

SELECT LastName
FROM Roster
EXCEPT DISTINCT
SELECT LastName
FROM PlayerStats;

1) [INNER] JOIN

反轉 SELECT 語句的順序將返回 PlayerStats 中不存在於 Roster 中的姓氏

SELECT LastName
FROM PlayerStats
EXCEPT DISTINCT
SELECT LastName
FROM Roster;

1) [INNER] JOIN

(empty)