求构建基于位置与日期动态分段的单SQL查询（含3种场景）

针对按Group_Name分组，处理Begin_Position、End_Position与Date的分段重叠场景，以下是单条SQL实现方案，通过收集所有分界点生成分段，再匹配对应最新日期：

实现思路 #

1. 提取每个分组内所有的Begin_Position和End_Position作为分界点
2. 对分界点排序，生成连续的有效分段区间
3. 关联原数据，找到每个分段区间对应的所有覆盖记录，取最新的Date

完整SQL代码 #

假设原表名为segment_data，包含字段Group_Name, Begin_Position, End_Position, Date：

WITH all_boundaries AS (
    -- 收集分组内所有起止位置作为分界点
    SELECT Group_Name, Begin_Position AS boundary FROM segment_data
    UNION
    SELECT Group_Name, End_Position AS boundary FROM segment_data
),
sorted_boundaries AS (
    -- 对每个分组的分界点排序并添加序号
    SELECT 
        Group_Name,
        boundary,
        ROW_NUMBER() OVER (PARTITION BY Group_Name ORDER BY boundary) AS rn
    FROM all_boundaries
),
segment_intervals AS (
    -- 生成连续的分段区间，过滤无效区间
    SELECT
        s1.Group_Name,
        s1.boundary AS new_begin,
        s2.boundary AS new_end
    FROM sorted_boundaries s1
    JOIN sorted_boundaries s2 
        ON s1.Group_Name = s2.Group_Name 
        AND s1.rn = s2.rn - 1
    WHERE s1.boundary < s2.boundary
),
segment_latest_date AS (
    -- 匹配每个分段对应的最新Date
    SELECT
        si.Group_Name,
        si.new_begin AS Begin_Position,
        si.new_end AS End_Position,
        MAX(sd.Date) AS latest_date
    FROM segment_intervals si
    JOIN segment_data sd 
        ON si.Group_Name = sd.Group_Name
        AND sd.Begin_Position <= si.new_begin
        AND sd.End_Position >= si.new_end
    GROUP BY si.Group_Name, si.new_begin, si.new_end
)
SELECT * FROM segment_latest_date;

场景处理说明 #

• 分段起始部分重叠：比如分组内有(1,5,'2023-01-01')和(3,7,'2023-01-02')，分界点为1、3、5、7，生成(1,3)、(3,5)、(5,7)三个分段，分别对应最新日期2023-01-01、2023-01-02、2023-01-02
• 分段末尾部分重叠：比如分组内有(1,7,'2023-01-01')和(3,5,'2023-01-02')，生成分段(1,3)、(3,5)、(5,7)，对应最新日期2023-01-01、2023-01-02、2023-01-01
• 完全重叠：比如分组内有(1,5,'2023-01-01')和(1,5,'2023-01-02')，分界点为1、5，生成单一分段(1,5)，对应最新日期2023-01-02

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