使用SQL递归CTE清理重叠范围数据的技术求助

需求分析 #

需要从raw_BIN表中筛选出数据最新且无范围重叠的记录，核心逻辑是：

• 同一起始范围的记录，保留created_on最新的
• 不同起始的范围，如果旧范围被新范围完全覆盖，则剔除旧范围
• 最终结果按range_from升序排列

正确SQL实现（以Snowflake为例） #

WITH ranked AS (
    -- 第一步：按范围起始升序、创建时间降序排序，确保同起始的最新记录排在前面
    SELECT 
        range_from, 
        range_end, 
        created_on,
        ROW_NUMBER() OVER (ORDER BY range_from, created_on DESC) AS rn
    FROM raw_BIN
),
recursive_clean AS (
    -- 递归初始值：取排序后的第一条记录
    SELECT 
        range_from, 
        range_end, 
        created_on,
        rn,
        range_end AS current_max_end -- 跟踪当前已保留范围的最大结束值
    FROM ranked
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续每条记录
    SELECT 
        r.range_from, 
        r.range_end, 
        r.created_on,
        r.rn,
        -- 更新当前最大结束值：如果当前记录的起始在已保留范围内，则取两者结束值的最大值；否则保留当前记录的结束值
        CASE 
            WHEN r.range_from <= rc.current_max_end THEN GREATEST(rc.current_max_end, r.range_end)
            ELSE r.range_end
        END AS current_max_end
    FROM recursive_clean rc
    JOIN ranked r ON r.rn = rc.rn + 1
    -- 只保留未被已保留范围完全覆盖的记录
    WHERE NOT (r.range_from >= rc.current_max_end AND r.range_end <= rc.current_max_end)
)
-- 最终筛选出无重叠的有效记录
SELECT DISTINCT
    range_from,
    range_end,
    created_on
FROM recursive_clean
-- 保留那些起始值大于前一个保留范围最大结束值的记录（或第一条记录）
QUALIFY 
    rn = 1 
    OR range_from > LAG(current_max_end) OVER (ORDER BY rn)
ORDER BY range_from;

代码执行逻辑说明 #

1. ranked CTE：先对所有记录排序，确保同起始范围的最新记录优先，同时按范围起始从小到大排列，方便后续递归处理。
2. recursive_clean CTE：
   • 初始行取排序后的第一条记录，初始化当前最大范围结束值。
   • 递归连接下一条记录，判断当前记录是否被已保留的范围完全覆盖：如果是则跳过；否则更新当前最大范围结束值并保留该记录。
3. 最终筛选：通过QUALIFY子句剔除重叠的记录，只保留真正无重叠的有效行。

你原有代码的问题 #

1. 递归终止逻辑错误：你的WHERE v.bin_from < b.bin_end会导致递归在遇到第一条不满足条件的记录时直接停止，无法继续处理后续行。
2. 未处理“保留最新记录”的逻辑：没有对同起始范围的记录按created_on排序取最新，导致旧记录可能被保留。
3. 未跟踪当前范围的最大结束值：无法判断后续记录是否被已保留范围覆盖，也就无法正确剔除重叠行。

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