函式的最佳做法

本文件說明如何最佳化使用 SQL 函式的查詢。

最佳化字串比較

最佳做法：盡可能使用 LIKE 而非 REGEXP_CONTAINS。

在 BigQuery 中，您可以使用 REGEXP_CONTAINS 函式或 LIKE 運算子來比較字串。REGEXP_CONTAINS 提供更多功能，但執行時間較慢。使用 LIKE 而非 REGEXP_CONTAINS 會更快速，特別是如果您不需要 REGEXP_CONTAINS 提供的規則運算式完整功能 (例如萬用字元比對)，更是如此。

請參考下列 REGEXP_CONTAINS 函式的使用方式：

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

您可以按照下列方式最佳化這項查詢：

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

改善匯總函式

最佳做法：如果您的應用實例支援的話，請使用近似匯總函式。

如果您使用的 SQL 匯總函式有等同的近似函式，近似函式可提供更快的查詢效能。例如，請改用 APPROX_COUNT_DISTINCT，而非 COUNT(DISTINCT)。詳情請參閱近似匯總函式。

您也可以使用 HyperLogLog++ 函式來計算近似值 (包括自訂近似匯總)。詳情請參閱 GoogleSQL 參考資料中的 HyperLogLog++ 函式。

請參考下列 COUNT 函式的使用方式：

SELECT
  dim1,
  COUNT(DISTINCT dim2)
FROM
  `dataset.table`
GROUP BY 1;

您可以按照下列方式最佳化這項查詢：

SELECT
  dim1,
  APPROX_COUNT_DISTINCT(dim2)
FROM
  `dataset.table`
GROUP BY 1;

最佳化分位數函式

最佳做法：盡可能使用 APPROX_QUANTILE 而非 NTILE。

如果單一區隔中 ORDER BY 的元素過多，導致資料量增加，執行包含 NTILE 函式的查詢可能會失敗，並顯示 Resources exceeded 錯誤。分析時間區間並未分割，因此 NTILE 運算需要全域 ORDER BY，才能由單一 worker/運算單元處理資料表中的所有資料列。

請改用 APPROX_QUANTILES。這項函式不需要為資料表中的所有資料列建立全域 ORDER BY，因此可讓查詢更有效率地執行。

請參考下列 NTILE 函式的使用方式：

SELECT
  individual_id,
  NTILE(nbuckets) OVER (ORDER BY sales desc) AS sales_third
FROM
  `dataset.table`;

您可以按照下列方式最佳化這項查詢：

WITH QuantInfo AS (
  SELECT
    o, qval
  FROM UNNEST((
     SELECT APPROX_QUANTILES(sales, nbuckets)
     FROM `dataset.table`
    )) AS qval
  WITH offset o
  WHERE o > 0
)
SELECT
  individual_id,
  (SELECT
     (nbuckets + 1) - MIN(o)
   FROM QuantInfo
   WHERE sales <= QuantInfo.qval
  ) AS sales_third
FROM `dataset.table`;

經過最佳化的版本會傳回與原始查詢相似但不完全相同的結果，因為 APPROX_QUANTILES：

  1. 提供近似匯總。
  2. 以不同方式放置餘數值 (資料列數除以值區後的餘數)。

最佳化 UDF

最佳做法：請使用 SQL UDF 進行簡單計算，因為查詢最佳化工具可將最佳化套用至 SQL UDF 定義。使用 JavaScript UDF 執行 SQL UDF 不支援的複雜計算。

呼叫 JavaScript UDF 需要例項化子程序。直接啟動這項程序並執行 UDF 會影響查詢成效。如果可行，請改用原生 (SQL) UDF。

永久 UDF

建議您在集中式 BigQuery 資料集中建立持久的使用者定義 SQL 和 JavaScript 函式，以便在各項查詢和邏輯檢視中叫用，而非每次在程式碼中建立及叫用 UDF。在共用資料集內建立全組織的商業邏輯程式庫，有助於提升效能並減少資源用量。

以下範例說明如何在查詢中叫用臨時 UDF：

CREATE TEMP FUNCTION addFourAndDivide(x INT64, y INT64) AS ((x + 4) / y);

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, addFourAndDivide(val, 2) AS result
FROM numbers;

您可以將暫時性 UDF 替換為永久性 UDF，藉此最佳化這項查詢：

WITH numbers AS
  (SELECT 1 as val
  UNION ALL
  SELECT 3 as val
  UNION ALL
  SELECT 4 as val
  UNION ALL
  SELECT 5 as val)
SELECT val, `your_project.your_dataset.addFourAndDivide`(val, 2) AS result
FROM numbers;