递归CTE超限问题：咨询高效验证体重数据有效性的方案

需求说明 #

需验证体重数据表的有效性，表包含User_ID、Activity_ID、Activity_DT、Weight、Weight_Source五列，计算列Valid_Weight规则如下：

• 每个用户的首行Valid_Weight标记为1
• 当前体重与最近一条Valid_Weight为1的体重相比，变化幅度≤5%则标记为1，否则为0；仅能与有效历史行对比

原递归CTE的问题 #

尝试用递归CTE实现时触发max_recursion_rows限制，核心原因是递归逻辑存在无限循环：递归部分的JOIN条件仅限制vw.Activity_DT < comb.Activity_DT且vw.Valid_Weight=1，会导致同一有效行关联所有后续行，重复生成数据，最终超出递归次数限制。

高效实现方案 #

采用窗口函数+累积分组的方式，无需递归，通过标记有效区间的起始点，跟踪每个用户的最近有效体重值：

步骤说明 #

1. 按User_ID和Activity_DT对数据排序，确保按时间顺序处理
2. 用累积SUM标记有效区间：每次遇到有效行（首行或符合5%变化规则）时，区间编号递增，无效行继承当前区间编号
3. 基于区间编号，用LAST_VALUE获取当前区间的基准有效体重，再判断当前行是否有效

完整SQL代码 #

WITH sorted_data AS (
    SELECT 
        User_ID,
        Activity_ID,
        Activity_DT,
        Weight,
        Weight_Source,
        -- 按用户和时间排序，生成行号用于识别首行
        ROW_NUMBER() OVER(PARTITION BY User_ID ORDER BY Activity_DT) AS rn
    FROM etl.rdm_weight_validation
),
valid_groups AS (
    SELECT 
        *,
        -- 累积计算有效组：首行或当前体重与上一个有效组的基准体重差异≤5%则新建组
        SUM(CASE 
                WHEN rn = 1 THEN 1
                WHEN ABS(Weight - LAST_VALUE(CASE WHEN valid_flag = 1 THEN Weight END IGNORE NULLS) OVER(PARTITION BY User_ID ORDER BY Activity_DT)) 
                     / LAST_VALUE(CASE WHEN valid_flag = 1 THEN Weight END IGNORE NULLS) OVER(PARTITION BY User_ID ORDER BY Activity_DT) <= 0.05 THEN 1
                ELSE 0
            END) OVER(PARTITION BY User_ID ORDER BY Activity_DT) AS group_id,
        -- 临时标记首行为有效
        CASE WHEN rn = 1 THEN 1 ELSE 0 END AS valid_flag
    FROM sorted_data
),
final_valid AS (
    SELECT 
        User_ID,
        Activity_ID,
        Activity_DT,
        Weight,
        Weight_Source,
        -- 根据组内的基准体重判断当前行是否有效
        CASE 
            WHEN ABS(Weight - FIRST_VALUE(Weight) OVER(PARTITION BY User_ID, group_id ORDER BY Activity_DT)) 
                 / FIRST_VALUE(Weight) OVER(PARTITION BY User_ID, group_id ORDER BY Activity_DT) <= 0.05 THEN 1
            ELSE 0
        END AS Valid_Weight
    FROM valid_groups
)
SELECT 
    Activity_ID,
    User_ID,
    Activity_DT,
    Weight,
    Weight_Source,
    Valid_Weight
FROM final_valid
ORDER BY User_ID, Activity_DT;

代码说明 #

• sorted_data：对每个用户的数据按时间排序，生成行号用于识别首行
• valid_groups：通过累积SUM生成有效组编号，每次遇到符合条件的有效行时，组号递增，确保每个组的基准是该组的第一个有效体重
• final_valid：基于组内的基准体重（每组首行的体重），判断当前行是否符合5%的变化幅度，生成最终的Valid_Weight

替代简化方案（适用于支持LAG条件窗口的数据库） #

如果数据库支持LAG的IGNORE NULLS参数，可以进一步简化：

WITH sorted_data AS (
    SELECT 
        User_ID,
        Activity_ID,
        Activity_DT,
        Weight,
        Weight_Source,
        ROW_NUMBER() OVER(PARTITION BY User_ID ORDER BY Activity_DT) AS rn
    FROM etl.rdm_weight_validation
),
valid_calc AS (
    SELECT 
        *,
        -- 跟踪最近的有效体重：如果当前行有效，则用自身体重，否则继承上一个有效体重
        LAST_VALUE(CASE WHEN Valid_Weight = 1 THEN Weight END IGNORE NULLS) OVER(PARTITION BY User_ID ORDER BY Activity_DT) AS last_valid_weight,
        CASE 
            WHEN rn = 1 THEN 1
            WHEN ABS(Weight - LAST_VALUE(CASE WHEN Valid_Weight = 1 THEN Weight END IGNORE NULLS) OVER(PARTITION BY User_ID ORDER BY Activity_DT)) 
                 / LAST_VALUE(CASE WHEN Valid_Weight = 1 THEN Weight END IGNORE NULLS) OVER(PARTITION BY User_ID ORDER BY Activity_DT) <= 0.05 THEN 1
            ELSE 0
        END AS Valid_Weight
    FROM sorted_data
)
SELECT 
    Activity_ID,
    User_ID,
    Activity_DT,
    Weight,
    Weight_Source,
    Valid_Weight
FROM valid_calc
ORDER BY User_ID, Activity_DT;

结果示例 #

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