PostgreSQL按范围筛选重叠行：仅保留最高优先级投标

可以通过递归CTE实现这个需求，核心思路是从最高排名的投标开始，逐步筛选出后续所有与已保留路段无重叠的最高排名记录，最终得到符合要求的结果集。

具体实现步骤 #

假设你的原始表名为bid_segments，包含字段bid_id（投标ID）、start_ch_km（起始链距）、end_ch_km（终止链距）、bid_price（投标价格），已经通过numrange(start_ch_km, end_ch_km)生成了路段范围字段segment_range。

1. 生成带排名的基础数据集
   先创建CTE给所有投标按价格降序排名：

   WITH ranked_bids AS (
       SELECT
           bid_id,
           segment_range,
           bid_price,
           ROW_NUMBER() OVER (ORDER BY bid_price DESC) AS bid_rank
       FROM bid_segments
   ),
   

2. 递归筛选不重叠投标
   递归CTE分为锚点和迭代两部分：

   • 锚点：直接选取排名第1的投标作为起始保留项
   • 迭代：每次从剩余未被保留的投标中，筛选出与所有已保留路段都不重叠的最高排名记录，加入结果集

   recursive_selected AS (
       -- 锚点：取排名第一的投标
       SELECT bid_id, segment_range, bid_rank
       FROM ranked_bids
       WHERE bid_rank = 1
       UNION ALL
       -- 递归步骤：筛选后续不重叠的最高排名投标
       SELECT
           rb.bid_id,
           rb.segment_range,
           rb.bid_rank
       FROM ranked_bids rb
       -- 只处理比已保留记录排名更低的投标
       WHERE rb.bid_rank > (SELECT MAX(bid_rank) FROM recursive_selected)
       -- 确保当前投标与所有已保留路段无重叠
       AND NOT EXISTS (
           SELECT 1
           FROM recursive_selected rs_check
           WHERE rb.segment_range && rs_check.segment_range
       )
       -- 仅取当前符合条件的最高排名
       AND rb.bid_rank = (
           SELECT MIN(rb_inner.bid_rank)
           FROM ranked_bids rb_inner
           WHERE rb_inner.bid_rank > (SELECT MAX(bid_rank) FROM recursive_selected)
           AND NOT EXISTS (
               SELECT 1
               FROM recursive_selected rs_inner_check
               WHERE rb_inner.segment_range && rs_inner_check.segment_range
           )
       )
   )
   

3. 查询最终结果
   从递归CTE中取出所有记录，就是你需要的不重叠高优先级投标：

   SELECT * FROM recursive_selected ORDER BY bid_rank;
   

关键逻辑说明 #

• 使用PostgreSQL内置的&&操作符判断两个numrange是否重叠
• 递归过程中每次仅选取当前剩余投标里的最高排名且不重叠记录，严格保证高优先级优先保留
• 通过NOT EXISTS子句彻底排除所有与已保留路段重叠的投标

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