BigQuery递归CTE问询：非唯一键关联与行扩展场景实现

源数据示例 #

核心需求与迭代规则 #

需基于业务规则计算conversions增量差值，生成按迭代区分的结果，迭代从region_name_baseline为NULL的行开始：

• 关联规则：cte.region_name_baseline = data.region_name
• 行扩展规则：
  • 若关联行channel为NULL，仅合并新行；
  • 若关联行channel非NULL，需生成两组新行：一组继承关联行的channel/subchannel/activity_type，另一组保留新行原有属性（示例中第二次迭代需从3行扩展为6行）
• 增量计算规则：
  1. 父行无channel分类：average_baseline_conversions为父行中早于当前行日期的conversions平均值；incremental_conversions为当前行conversions与该平均值的差值。
  2. 父行有channel分类且属于“继承集”：average_baseline_conversions为父行的父行中早于当前行日期的conversions平均值；incremental_conversions为父行incremental_conversions的平均值。
  3. 父行有channel分类且属于非继承集：average_baseline_conversions为父行中早于当前行日期的conversions平均值；incremental_conversions为当前行conversions与该平均值的差值（与规则1逻辑一致，仅父层级不同）。

当前困境 #

已掌握计算逻辑，但卡在递归Union/行扩展实现，需将9行源数据扩展为12行并填充正确属性。现有方案可行但繁琐，受BigQuery递归CTE三大限制影响：

1. 不允许子查询，非唯一键关联需先扩展再处理；
2. 不允许窗口/分析函数，需承受类笛卡尔积关联的性能损耗；
3. 不允许额外Union，需预先扩展数据。

现有方案代码 #

WITH RECURSIVE `data` AS (
  SELECT '2024-06-01' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 8 AS `conversions`
  UNION ALL
  SELECT '2024-06-02' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 6 AS `conversions`
  UNION ALL
  SELECT '2024-06-03' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 7 AS `conversions`
  UNION ALL
  SELECT '2024-06-04' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 11 AS `conversions`
  UNION ALL
  SELECT '2024-06-05' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 13 AS `conversions`
  UNION ALL
  SELECT '2024-06-06' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 13 AS `conversions`
  UNION ALL
  SELECT '2024-06-07' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 18 AS `conversions`
  UNION ALL
  SELECT '2024-06-08' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 18 AS `conversions`
  UNION ALL
  SELECT '2024-06-09' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 22 AS `conversions`
),

`cte_data` AS (
  SELECT
    *,
    TRUE AS `original`
  FROM `data`

  UNION ALL

  SELECT 
    *,
    FALSE AS `original`
  FROM `data`
  WHERE `region_name_baseline` IS NOT NULL
),

`cte` AS (
  SELECT 
    `date`,
    `region_number`,
    `region_name`,
    `channel`,
    `subchannel`,
    `activity_type`,
    `region_name_baseline`,
    `conversions`,
    NULL AS `average_baseline_conversions`,
    NULL AS `incremental_conversions`, 
    1 AS `iteration`
  FROM `cte_data`
  WHERE `region_name_baseline` IS NULL

  UNION ALL
  
  SELECT
    `cte_data`.`date`,
    `cte_data`.`region_number`,
    `cte_data`.`region_name`,
    IF(`cte_data`.`original`, `cte`.`channel`, `cte_data`.`channel`) AS `channel`,
    IF(`cte_data`.`original`, `cte`.`subchannel`, `cte_data`.`subchannel`) AS `subchannel`,
    IF(`cte_data`.`original`, `cte`.`activity_type`, `cte_data`.`activity_type`) AS `activity_type`,
    IF(`cte_data`.`original`, `cte`.`region_name_baseline`, `cte_data`.`region_name_baseline`) AS `region_name_baseline`,
    `cte_data`.`conversions`,
    NULL AS `average_baseline_conversions`,
    NULL AS `incremental_conversions`, 
    `iteration` + 1 AS `iteration`
  FROM `cte_data`
  JOIN `cte`
    ON `cte_data`.`region_name_baseline` = `cte`.`region_name`
    AND `cte_data`.`date` > `cte`.`date`
  WHERE `cte`.`channel` IS NOT NULL OR NOT `cte_data`.`original`
)

SELECT *
FROM `cte`
QUALIFY ROW_NUMBER() OVER (PARTITION BY 
  `region_number`, 
  `region_name_baseline`,
  `date`
) = 1
ORDER BY `iteration`, `region_number`, `region_name_baseline`, `date`

优化思路与方案 #

思路：拆分递归逻辑，预生成扩展分支 #

针对BigQuery递归CTE的限制，将行扩展逻辑提前到非递归阶段处理，避免在递归中做复杂分支判断：

1. 预生成分支标记：为需要扩展的行（即关联行channel非NULL的目标行）提前生成两个分支标记（branch_type：original和inherit），替代原有original字段；
2. 递归关联简化：在递归CTE中，根据分支标记直接决定是否继承父行属性，无需复杂IF判断；
3. 分步计算增量：递归完成后，统一用窗口函数计算average_baseline_conversions和incremental_conversions，避开递归中不能使用窗口函数的限制。

优化后代码 #

WITH RECURSIVE `data` AS (
  SELECT '2024-06-01' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 8 AS `conversions`
  UNION ALL
  SELECT '2024-06-02' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 6 AS `conversions`
  UNION ALL
  SELECT '2024-06-03' AS `date`, 1 AS `region_number`, 'Control' AS `region_name`, NULL AS `channel`, NULL AS `subchannel`, NULL AS `activity_type`, NULL AS `region_name_baseline`, 7 AS `conversions`
  UNION ALL
  SELECT '2024-06-04' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 11 AS `conversions`
  UNION ALL
  SELECT '2024-06-05' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 13 AS `conversions`
  UNION ALL
  SELECT '2024-06-06' AS `date`, 1 AS `region_number`, 'ITVX' AS `region_name`, 'tv' AS `channel`, 'bvod' AS `subchannel`, 'itvx' AS `activity_type`, 'Control' AS `region_name_baseline`, 13 AS `conversions`
  UNION ALL
  SELECT '2024-06-07' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 18 AS `conversions`
  UNION ALL
  SELECT '2024-06-08' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 18 AS `conversions`
  UNION ALL
  SELECT '2024-06-09' AS `date`, 1 AS `region_number`, 'ITVX + TikTok' AS `region_name`, 'paid_social' AS `channel`, 'tiktok' AS `subchannel`, NULL AS `activity_type`, 'ITVX' AS `region_name_baseline`, 22 AS `conversions`
),

-- 预生成扩展分支：为需要扩展的行生成original和inherit两个分支
`data_with_branches` AS (
  SELECT 
    *,
    'original' AS branch_type
  FROM `data`

  UNION ALL

  SELECT 
    d.*,
    'inherit' AS branch_type
  FROM `data` d
  JOIN `data` parent 
    ON d.region_name_baseline = parent.region_name
  WHERE parent.channel IS NOT NULL
),

-- 递归CTE：简化关联与属性继承逻辑
`recursive_cte` AS (
  -- 初始节点：region_name_baseline为NULL的行
  SELECT 
    date,
    region_number,
    region_name,
    channel,
    subchannel,
    activity_type,
    region_name_baseline,
    conversions,
    NULL AS parent_region_name,
    1 AS iteration,
    branch_type
  FROM `data_with_branches`
  WHERE region_name_baseline IS NULL

  UNION ALL

  -- 递归节点：根据分支类型决定是否继承父属性
  SELECT
    d.date,
    d.region_number,
    d.region_name,
    CASE WHEN d.branch_type = 'inherit' THEN r.channel ELSE d.channel END AS channel,
    CASE WHEN d.branch_type = 'inherit' THEN r.subchannel ELSE d.subchannel END AS subchannel,
    CASE WHEN d.branch_type = 'inherit' THEN r.activity_type ELSE d.activity_type END AS activity_type,
    CASE WHEN d.branch_type = 'inherit' THEN r.region_name_baseline ELSE d.region_name_baseline END AS region_name_baseline,
    d.conversions,
    r.region_name AS parent_region_name,
    r.iteration + 1 AS iteration,
    d.branch_type
  FROM `data_with_branches` d
  JOIN `recursive_cte` r
    ON d.region_name_baseline = r.region_name
    AND d.date > r.date
  -- 过滤重复分支：仅当父行有channel时，inherit分支才有效
  WHERE NOT (r.channel IS NULL AND d.branch_type = 'inherit')
),

-- 计算增量指标：递归完成后统一用窗口函数处理
`final_results` AS (
  SELECT
    *,
    -- 计算average_baseline_conversions
    CASE
      -- 场景1：父行无channel（初始节点）
      WHEN iteration = 1 THEN AVG(conversions) OVER (PARTITION BY region_number, region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      -- 场景2：继承分支，取父行的父行基准平均值
      WHEN branch_type = 'inherit' THEN AVG(parent_conversions) OVER (PARTITION BY region_number, parent_region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      -- 场景3：非继承分支，取父行基准平均值
      ELSE AVG(parent_conversions) OVER (PARTITION BY region_number, parent_region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS average_baseline_conversions,
    -- 计算incremental_conversions
    CASE
      WHEN iteration = 1 THEN conversions - AVG(conversions) OVER (PARTITION BY region_number, region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      WHEN branch_type = 'inherit' THEN AVG(parent_incremental) OVER (PARTITION BY region_number, parent_region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
      ELSE conversions - AVG(parent_conversions) OVER (PARTITION BY region_number, parent_region_name ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS incremental_conversions
  FROM (
    SELECT
      r.*,
      -- 关联父行的conversions和incremental值
      parent.conversions AS parent_conversions,
      parent.incremental_conversions AS parent_incremental
    FROM `recursive_cte` r
    LEFT JOIN `recursive_cte` parent
      ON r.parent_region_name = parent.region_name
      AND r.date > parent.date
  )
)

SELECT
  date,
  region_number,
  region_name,
  channel,
  subchannel,
  activity_type,
  region_name_baseline,
  conversions,
  average_baseline_conversions,
  incremental_conversions,