如何用Recursive CTE清理BigQuery中每日多状态的重复事件数据

核心思路 #

由于当日的正确状态依赖次日的最终确定状态，可以采用逆向递归推导的方式：从状态唯一的最后一天开始，依次向前推导每一天的正确状态——当某一天存在多个状态时，保留与次日最终状态一致的那个状态。这种方式规避了BigQuery递归CTE对分析函数、GROUP BY的限制。

实现SQL #

WITH sample_data AS (
  SELECT DATE('2024-11-01') date, 'active' status UNION ALL
  SELECT DATE('2024-11-01'), 'inactive' UNION ALL
  SELECT DATE('2024-11-02'), 'inactive' UNION ALL
  SELECT DATE('2024-11-03'), 'active' UNION ALL
  SELECT DATE('2024-11-03'), 'inactive' UNION ALL
  SELECT DATE('2024-11-04'), 'active' UNION ALL
  SELECT DATE('2024-11-04'), 'inactive' UNION ALL
  SELECT DATE('2024-11-05'), 'active'
),
date_info AS (
  SELECT 
    date,
    status,
    -- 逆向排序，获取原顺序的"次日"日期
    LAG(date) OVER (ORDER BY date DESC) next_day,
    -- 标记当日是否有重复状态
    COUNT(*) OVER (PARTITION BY date) status_count
  FROM sample_data
),
recursive_dedup AS (
  -- 初始成员：最后一天的唯一状态
  SELECT 
    date, 
    status AS final_status
  FROM date_info
  WHERE date = (SELECT MAX(date) FROM sample_data)
  
  UNION ALL
  
  -- 递归成员：基于次日的确定状态，筛选当日的正确状态
  SELECT 
    di.date, 
    di.status AS final_status
  FROM recursive_dedup rd
  JOIN date_info di ON di.next_day = rd.date
  WHERE di.status = rd.final_status
)
-- 按日期正序输出最终结果
SELECT date, final_status AS status
FROM recursive_dedup
ORDER BY date;

代码说明 #

1. sample_data：模拟原始的多状态重复数据。
2. date_info：计算每个日期的状态重复次数，并通过逆向LAG函数获取原顺序的次日日期，为后续递归连接做准备。
3. recursive_dedup：
   • 初始部分直接取最后一天的唯一状态（无重复，无需筛选）。
   • 递归部分通过连接已确定的次日状态，筛选出当日与次日状态一致的行，自动完成去重。
4. 最终按日期正序输出，得到每日唯一的状态结果。

执行结果 #

运行后将得到符合需求的去重数据：

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