使用可重複使用的資料欄別名

BigQuery 中的 GoogleSQL 支援可重複使用的資料欄別名。可重複使用的資料欄別名可讓您在 SQL 查詢的 SELECT 子句中定義運算式的別名，並立即在同一個 SELECT 清單或同一個查詢區塊的 WHERE 子句中參照該別名。

舉例來說，下列查詢說明如何根據計算結果 (例如 gross_price) 篩選結果。您可以直接在 WHERE 子句中參照別名 gross_price，而不必在 WHERE 子句中重複計算或使用子查詢：

SELECT
  net_price * 1.23 as gross_price
FROM
  product
WHERE
  gross_price < 10.0

可重複使用的資料欄別名可提供以下功能：

• 簡化 SQL 語法：您可以編寫簡潔、由左至右的計算，不必重複複雜的運算式，或將查詢包裝在子查詢中。
• 提升可讀性：分析師和開發人員可以宣告運算式一次，然後立即重複使用。使查詢更容易閱讀、偵錯和維護。

啟用或停用可重複使用的資料欄別名

重要事項： 啟用可重複使用的資料欄別名前，請先評估對現有工作負載的影響。資料欄別名會變更查詢的名稱解析順序。

根據預設，可重複使用的資料欄別名會停用。如要使用資料欄別名，BigQuery 管理員必須使用下列其中一種資料定義語言 (DDL) 陳述式，將 reusable_column_aliases_mode 選項設為 on，啟用這項功能：

• 如要在組織層級啟用資料欄別名，請使用 ALTER ORGANIZATION SET OPTIONS 陳述式。

• 如要在專案層級啟用資料欄別名，請使用 ALTER PROJECT SET OPTIONS 陳述式。

如要設定這個選項，您必須具備 BigQuery 管理員 (roles/bigquery.admin) 角色，或專案的 bigquery.projects.update 權限，或組織的 bigquery.organizations.update 權限。如要瞭解設定選項所需的權限，請參閱「管理設定」頁面的「必要權限」一節。

如要停用可重複使用的資料欄別名，請將 reusable_column_aliases_mode 設為 off。 如果將值設為 NULL，系統會使用預設值，並停用這項功能。

範例

如要在組織層級啟用可重複使用的資料欄別名，請輸入下列 DDL 陳述式：

ALTER ORGANIZATION
SET OPTIONS (
  `region-<code><var>REGION</var></code>.reusable_column_aliases_mode` = "on"
);

將 REGION 替換為您的區域。

如要在專案層級啟用可重複使用的資料欄別名，請輸入下列 DDL 陳述式：

ALTER PROJECT <code><var>PROJECT_ID</var></code>
SET OPTIONS (
  `region-<code><var>REGION</var></code>.reusable_column_aliases_mode` = "on"
);

更改下列內容：

• REGION：您所在的區域。
• PROJECT_ID：您的專案 ID。

確認是否已啟用或停用資料欄別名

您可以查詢下列資訊結構定義檢視表，確認專案或組織層級是否已啟用可重複使用的資料欄別名：

• PROJECT_OPTIONS
• ORGANIZATION_OPTIONS
• EFFECTIVE_PROJECT_OPTIONS

如要進一步瞭解如何使用資訊結構定義檢視區塊，請參閱「INFORMATION_SCHEMA簡介」。

如何使用可重複使用的資料欄別名

您可以在 WHERE 子句或 SELECT 子句中使用別名 (指派給運算式的 ID)：

• WHERE 子句：根據 SELECT 清單中定義的計算資料欄進行篩選。
• SELECT 子句：使用計算別名在相同投影清單中定義另一個別名，或判斷查詢傳回的資料。

範例

這個範例說明如何使用可重複使用的資料欄別名，根據您先前在 SELECT 清單中定義的其他計算來計算值。這項查詢也會顯示您和如何在 WHERE 子句中參照別名：

SELECT
  net_price * 1.23 as gross_price,
  gross_price * 0.5 as discounted_gross_price
FROM
  product
WHERE
  discounted_gross_price < 10.0;

查詢名稱解析順序的行為變更 (語意漂移)

ID 名稱可以做為資料表中的實體資料欄，也可以做為您建立的別名，因此請考量查詢的名稱解析順序。BigQuery 會按照下列順序解析名稱：

1. 資料欄名稱：資料表中的現有資料欄一律具有最高優先順序。
2. 別名：查詢中定義的別名。
3. 外部查詢資料欄：父項查詢中的資料欄。
4. 函式引數：如果是在使用者定義函式 (UDF) 或資料表值函式 (TVF) 中執行。
5. 指令碼變數：在指令碼中定義的變數。

根據這個順序，如果資料表有名為 gross_price 的資料欄，而您建立了名為 gross_price 的別名，查詢會使用資料欄值，而非別名。

也就是說，如果外部查詢資料欄、函式引數或指令碼變數的名稱與您在 SELECT 子句中定義的別名相同，BigQuery 會使用「別名」值進行計算，而非變數或引數。

名稱解析順序運作方式範例

以下範例說明在命名衝突時，名稱解析順序如何與可重複使用的資料欄別名搭配運作。

與外部查詢資料欄衝突

如未啟用可重複使用的資料欄別名，則在 SELECT 子句中定義的別名，如果與外部查詢中的資料欄名稱相同，則會使用外部查詢資料欄。啟用可重複使用的資料欄別名後，別名會在查詢範圍內「遮蔽」外部查詢資料欄。

在以下範例中，巢狀查詢使用別名，並可存取外部查詢中的資料欄：

SELECT
  name,
  net_price,
  category_id, -- table product has category_id column
  (SELECT 100 AS category_id -- alias defined with the same name
     FROM category c
     WHERE
       id = category_id  -- Now the alias category_id is used here, not the outer query column
  ) as X
FROM
  product;

如果未啟用可重複使用的資料欄別名，子查詢會使用外部查詢中 product 資料表內的 category_id 資料欄。因此，category_id 值會因資料列而異。如果啟用可重複使用的資料欄別名，查詢會對所有資料列使用 100 的別名值。

與函式引數衝突

如果查詢是在 user-defined function (UDF) 內執行，則在該查詢中定義的別名優先於函式的引數。

在下列範例中，calculate_metric 函式有名為 val 的輸入引數：

CREATE TEMP FUNCTION calculate_metric(val INT64) AS ((
  SELECT AS STRUCT
    5 AS val,       -- Alias defined as constant 5
    val + 10        -- Reference to 'val'
));

-- Calling the function with argument 100
SELECT calculate_metric(100);

如果未啟用可重複使用的資料欄別名，查詢會使用傳遞至函式的引數值 (100)，並將結果計算為 110 (100 + 10)。如果啟用可重複使用的資料欄別名，查詢會使用 SELECT 清單 (5) 中定義的別名，並將結果計算為 15 (5 + 10)。

與指令碼變數衝突

如果未啟用可重複使用的資料欄別名，且 SELECT 子句中定義的別名與指令碼變數同名，系統會使用指令碼變數。如果啟用可重複使用的資料欄別名，別名會遮蔽查詢範圍內的變數。

以下範例宣告名為 gross_price 的變數，預設值為 10.0。查詢也會計算資料欄，並將其別名設為 gross_price：

DECLARE gross_price NUMERIC DEFAULT 10.0;

SELECT
  net_price * 1.23 AS gross_price,
  -- Usage of 'gross_price' here determines the behavior
  gross_price * 0.5 AS discounted_gross_price
FROM
  product;

如未啟用可重複使用的資料欄別名，查詢會使用 10.0 的變數值。結果是 discounted_gross_price 值為 5.0，這是所有資料列的常數。計算結果為 10.0 * 0.5 = 5.0。

如果啟用可重複使用的資料欄別名，查詢會使用 net_price * 1.23 的別名值。結果是每列不同的 discounted_gross_price 值。計算結果為 (net_price * 1.23) * 0.5。

最佳做法

使用可重複使用的資料欄別名時，請參考下列最佳做法：

• 啟用可重複使用的資料欄別名前，請先評估對現有工作負載的影響。
• 為避免發生非預期行為或無聲的行為變化 (語意漂移)，請勿使用指令碼變數、函式引數或外部查詢中的資料欄名稱做為別名。
• 如果您要從支援別名語意的來源方言 (例如 Teradata 或 Snowflake) 遷移 SQL，請先按照 SQL 翻譯說明操作，再開始遷移。

疑難排解欄別名問題

以下範例說明如何排解可重複使用資料欄別名的常見問題。

問題：查詢結果不如預期，或別名似乎遭到忽略。

解決方法：確認名稱解析順序。如果資料表包含與別名同名的資料欄，系統會優先使用資料欄名稱，請為別名指定不重複的名稱，以免發生衝突。

錯誤：Column not found。

解決方法：請確認您未使用向前參照。您只能參照在使用前定義的別名。系統會由左至右評估。

錯誤：Column name is ambiguous。

解決方法：請確認別名在同一個 SELECT 清單中只定義一次，再進行參照。

限制

可重複使用的資料欄別名有下列限制：

• 如果查詢包含分組或匯總，則 WHERE 子句或匯總函式的引數中，不得使用未匯總運算式 (分組資料欄或分組資料欄的投影) 的別名。在下列查詢中，別名 b 不當參照包含 a 的運算式，而 是分組資料欄：

  SELECT a+1 as b, sum(b) -- sum(b) is invalid.
  FROM t
  WHERE b > 5         -- Referencing b under WHERE clause is not allowed.
  GROUP BY a
  

• 直接或間接參照匯總或視窗函式的別名，無法用於 WHERE 子句，或做為其他匯總函式的引數。在下列查詢中，別名 x 不當參照匯總函式：

  SELECT sum(a) AS x, sum(x) -- sum(x) is invalid.
  FROM t
  WHERE x > 0          -- Referencing x under WHERE clause is not allowed.
  

• 您無法將視窗函式套用至直接或間接參照其他視窗函式的別名。在下列查詢中，別名 x 是指無效的視窗函式：

  SELECT count(*) over() AS x, sum(x) over() -- sum(x) over() is invalid.
  FROM t
  

• 管道語法不支援可重複使用的資料欄別名。

• Graph Query Language (GQL) 不支援可重複使用的資料欄別名。