BigQuery UDF关联子查询报错：参考列表超5条时失效

要做的事是用BigQuery写一个带UDF的查询，从餐厅表里返回相似门店，想靠UDF简化新restaurantid的数据生成工作。但参考列表的记录数超过5条就报错，2-3条的时候能正常跑。
报错信息：Query error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN

原脚本分三个阶段：

• 创建核心参考列表trial_stores_stg1（用来避免UDF返回相同ID，实现批量触发UDF）
• 定义接收4个参数的临时UDFFindSimilarStore，用来查找相似门店
• 通过SELECT语句给参考列表里的所有记录调用UDF

UDF里面嵌套了关联子查询——比如引用外部表trial_stores_stg1，还有通过var_restaurantid关联主查询的子查询。当调用UDF的记录变多，BigQuery没法把这些关联子查询转成非关联的形式，执行计划优化不了，就触发报错了。

放弃UDF，改用JOIN+窗口函数的方式把逻辑整合到单查询中，既能避开关联子查询的问题，批量处理效率也更高：

1. 预计算所有目标门店的平均订单数
2. 预计算所有候选相似门店的平均订单数（排除参考列表和门店自身）
3. 通过JOIN关联目标门店和候选门店，计算相似度
4. 用窗口函数ROW_NUMBER()为每个目标门店筛选相似度最高的门店

-- 1. 创建核心参考列表
DROP TABLE IF EXISTS `trial_stores_stg1`;
CREATE TABLE `trial_stores_stg1` AS (
  SELECT '' AS restaurantid, '' AS country, '' AS launch_date, "Test" AS chain
  UNION ALL SELECT "1", "CA", "2022-04-10", "Test"
  UNION ALL SELECT "2", "CA", "2022-04-10", "Test"
  UNION ALL SELECT "3", "UK", "2022-04-10", "Test"
);

-- 2. 预计算目标门店的平均订单数
WITH target_restaurants AS (
  SELECT
    ts.restaurantid,
    ts.country,
    ts.chain,
    DATE(ts.launch_date) AS launch_date,
    AVG(ds.nr_of_orders) AS target_avg_orders
  FROM `trial_stores_stg1` ts
  JOIN `dummydata.fact_restaurant_daily_snapshot` ds 
    ON ds.restaurantid = ts.restaurantid
  JOIN `dummydata.dim_restaurant` dr 
    ON dr.restaurantid = ts.restaurantid
  GROUP BY ts.restaurantid, ts.country, ts.chain, DATE(ts.launch_date)
),

-- 3. 预计算候选相似门店的平均订单数（排除参考列表和自身）
candidate_stores AS (
  SELECT
    dr.restaurantid AS candidate_id,
    dr.country,
    dr.chain,
    AVG(ds.nr_of_orders) AS candidate_avg_orders
  FROM `dummydata.fact_restaurant_daily_snapshot` ds
  JOIN `dummydata.dim_restaurant` dr 
    ON dr.restaurantid = ds.restaurantid
  WHERE dr.restaurantid NOT IN (SELECT restaurantid FROM `trial_stores_stg1`)
  GROUP BY dr.restaurantid, dr.country, dr.chain
),

-- 4. 关联目标和候选门店，计算相似度并筛选最优
similarity_matches AS (
  SELECT
    tr.restaurantid,
    tr.country,
    cs.candidate_id AS similar_restaurantid,
    -- 计算相似度（相对误差）
    ABS(cs.candidate_avg_orders - tr.target_avg_orders) / tr.target_avg_orders AS similarity_score,
    -- 为每个目标门店按相似度排序
    ROW_NUMBER() OVER (
      PARTITION BY tr.restaurantid 
      ORDER BY ABS(cs.candidate_avg_orders - tr.target_avg_orders) / tr.target_avg_orders ASC
    ) AS rn
  FROM target_restaurants tr
  JOIN candidate_stores cs
    ON cs.country = tr.country
    AND cs.chain = tr.chain
  -- 匹配候选门店的时间范围（和原UDF逻辑一致）
  JOIN `dummydata.fact_restaurant_daily_snapshot` ds_candidate
    ON ds_candidate.restaurantid = cs.candidate_id
    AND ds_candidate.snapshot_dt BETWEEN DATE_SUB(tr.launch_date, INTERVAL 3 MONTH) AND tr.launch_date
)

-- 5. 生成最终结果表
DROP TABLE IF EXISTS `trial_stores`;
CREATE TABLE `trial_stores` AS
SELECT
  restaurantid,
  country,
  similar_restaurantid
FROM similarity_matches
WHERE rn = 1;

• 如果需要保留部分复用性，可以把相似度计算逻辑封装成纯计算型SQL函数，确保函数内部没有关联子查询，只做数值运算
• 若参考列表trial_stores_stg1会动态更新，可将其作为CTE嵌入查询，避免重复创建表
• 针对大数据量场景，可通过按country/chain分区或聚类优化表结构，提升查询效率

内容的提问来源于stack exchange，提问作者Moose