解决 BigQuery 相关子查询和 UDF 限制：实用指南

BigQuery UDF 优化：解决相关子查询问题

使用标准 SQL 并优化 BigQuery UDF 数组处理的解决方案

CREATE OR REPLACE FUNCTION my.gcp.optimized_function(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays)) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(ip_start_date, ip_end_date, INTERVAL 1 DAY)) AS cal_date
  WHERE cal_date NOT IN (SELECT cal_date FROM UNNEST(holiday_array.holidays))
),
SELECT working_day
FROM working_days));

使用子查询连接处理 BigQuery UDF 关联错误

使用 LEFT JOIN 并处理数组数据以最大程度地减少子查询问题的解决方案

CREATE OR REPLACE FUNCTION my.gcp.function_v2(ip_start_date TIMESTAMP, ip_end_date TIMESTAMP)
RETURNS NUMERIC AS ((
WITH temp_date AS (
  SELECT
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_end_date)
      ELSE DATE(ip_start_date)
    END AS ip_date_01,
    CASE
      WHEN ip_start_date > ip_end_date THEN DATE(ip_start_date)
      ELSE DATE(ip_end_date)
    END AS ip_date_02
),
holiday_array AS (
  SELECT ARRAY_AGG(STRUCT(DATE(cal_date) AS cal_date, holiday_flag)) AS holidays
  FROM dataset.staff_time
),
working_days AS (
  SELECT
    CASE
      WHEN DATE(ip_start_date) <> DATE(ip_end_date) THEN
        SUM(CASE
          WHEN ot.cal_date IS  AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7) THEN 1
          ELSE 0
        END)
      ELSE 
    END AS working_day
  FROM UNNEST(GENERATE_DATE_ARRAY(SAFE_CAST(ip_start_date AS DATE),
  DATE_SUB(SAFE_CAST(ip_end_date AS DATE), INTERVAL 1 DAY), INTERVAL 1 DAY)) AS cal_date
  LEFT JOIN holiday_array ot
  ON cal_date = ot.cal_date
  WHERE ot.cal_date IS 
    AND EXTRACT(DAYOFWEEK FROM cal_date) NOT IN (1, 7)
),
SELECT working_day
FROM working_days));

有关 BigQuery UDF 和相关子查询的常见问题

1. 如何避免 BigQuery 中的相关子查询错误？
2. 为了避免相关子查询错误，请尝试重构您的查询以使用 ARRAY_AGG() 和 UNNEST() 函数或预聚合数据以减少 UDF 内部联接的需要。
3. 为什么我的 BigQuery UDF 在引用外部表时速度很慢？
4. BigQuery UDF 在重复引用外部表时会变慢，尤其是在相关子查询中。要解决此问题，请将关键数据存储在临时表中或使用缓存机制来减少查询开销。
5. 的作用是什么 SAFE_CAST() 在 BigQuery UDF 中？
6. 这 SAFE_CAST() 函数通过安全地转换值并在转换失败时返回 来确保无效的日期格式或数据类型不会导致查询失败。
7. 如何优化 UDF 以处理日期范围和假期？
8. 使用类似的函数 GENERATE_DATE_ARRAY() 处理日期范围和 EXTRACT() 从计算中过滤掉周末或节假日。这些可确保 UDF 中工作日的精确处理。
9. 我可以将 BigQuery UDF 用于大型数据集吗？
10. 是的，但是您需要仔细优化您的查询。尽量减少引用外部表的次数并使用高效的数组函数，例如 ARRAY_AGG() 来处理复杂的数据结构。

关于优化 BigQuery UDF 的最终想法

相关子查询是在 BigQuery 中开发函数时的主要限制之一。通过利用预聚合数据、数组操作和智能数据处理等替代方法，可以缓解这些限制，从而提高查询性能。

优化查询设计并最大程度地减少 UDF 中对外部表的引用可以显着减少错误和速度减慢。对于处理大型数据集的开发人员来说，应用这些技术将提高 BigQuery 中的报告效率并减少执行问题。