函式的最佳做法

本文說明如何最佳化使用 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 才能完成全域運算。

請改用 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：

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

最佳化 UDF

最佳做法：使用 SQL UDF 進行簡單計算，因為查詢最佳化工具可以對 SQL UDF 定義套用最佳化。如果 SQL UDF 無法支援複雜的計算，請使用 JavaScript 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;