函数的最佳做法

本文档介绍如何优化使用 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：

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;