SQL Server拆分重叠日期与街道号范围数据

嘿，针对你这种同时要处理日期范围和街道号范围双重重叠的数据拆分需求，我给你整理了一套可行的方案，结合你的示例数据来一步步说明：

核心思路 #

我们的目标是把所有重叠的区间拆分成最小的、无重叠的单元，每个单元对应唯一的Value。具体分为三步：提取所有分界点→生成最小不重叠区间→关联原数据匹配对应值。

步骤1：提取所有日期和街道号的分界点 #

首先我们需要把所有可能的日期断点和街道号断点收集起来，这些断点是拆分重叠区间的基础：

WITH AllDateBreaks AS (
    -- 收集所有开始日期和结束日期+1天（用于生成连续的无重叠区间）
    SELECT StartDate AS BreakDate FROM YourTableName
    UNION
    SELECT DATEADD(DAY, 1, EndDate) AS BreakDate FROM YourTableName
),
AllStreetNumberBreaks AS (
    -- 收集所有街道号下限和上限+1
    SELECT StreetNumberLow AS BreakNum FROM YourTableName
    UNION
    SELECT StreetNumberHigh + 1 AS BreakNum FROM YourTableName
)

步骤2：生成最小的不重叠日期区间和街道号区间 #

利用上面的断点，生成所有连续的、无重叠的日期区间和街道号区间：

, DateRanges AS (
    -- 生成连续的日期区间：每个区间的结束日期是下一个断点前一天
    SELECT 
        a.BreakDate AS StartDate,
        DATEADD(DAY, -1, b.BreakDate) AS EndDate,
        sn.StreetName
    FROM AllDateBreaks a
    JOIN AllDateBreaks b ON a.BreakDate < b.BreakDate
    CROSS JOIN (SELECT DISTINCT StreetName FROM YourTableName) sn
),
StreetNumberRanges AS (
    -- 生成连续的街道号区间：每个区间的上限是下一个断点前1
    SELECT 
        a.BreakNum AS StreetNumberLow,
        b.BreakNum - 1 AS StreetNumberHigh,
        sn.StreetName
    FROM AllStreetNumberBreaks a
    JOIN AllStreetNumberBreaks b ON a.BreakNum < b.BreakNum
    CROSS JOIN (SELECT DISTINCT StreetName FROM YourTableName) sn
)

步骤3：关联原数据，匹配每个最小单元对应的Value #

把生成的日期区间和街道号区间组合，然后关联原表，找到每个最小单元对应的有效Value（确保原记录的区间完全包含这个最小单元）：

, CombinedRanges AS (
    SELECT 
        dr.StartDate,
        dr.EndDate,
        dr.StreetName,
        snr.StreetNumberLow,
        snr.StreetNumberHigh
    FROM DateRanges dr
    JOIN StreetNumberRanges snr ON dr.StreetName = snr.StreetName
)
SELECT 
    cr.StartDate,
    cr.EndDate,
    cr.StreetName,
    cr.StreetNumberLow,
    cr.StreetNumberHigh,
    yt.Value
FROM CombinedRanges cr
JOIN YourTableName yt ON 
    cr.StreetName = yt.StreetName
    AND cr.StartDate >= yt.StartDate
    AND cr.EndDate <= yt.EndDate
    AND cr.StreetNumberLow >= yt.StreetNumberLow
    AND cr.StreetNumberHigh <= yt.StreetNumberHigh
-- 如果同一个最小单元有多个匹配值（比如Value不同），可以根据业务规则添加筛选/去重逻辑
GROUP BY cr.StartDate, cr.EndDate, cr.StreetName, cr.StreetNumberLow, cr.StreetNumberHigh, yt.Value
ORDER BY cr.StreetName, cr.StartDate, cr.StreetNumberLow;

针对你的示例数据的处理结果 #

运行上述代码后，会得到你期望的拆分结果：

StartDate EndDate StreetName StreetNumberLow StreetNumberHigh Value
2017/1/1 2017/3/31 MyStreet 1 4 1729
2017/1/1 2017/3/31 MyStreet 5 7 1729
2017/1/1 2017/3/31 MyStreet 8 11 1729
2017/4/1 2017/12/31 MyStreet 1 4 1729
2017/4/1 2017/12/31 MyStreet 5 7 1763
2017/4/1 2017/12/31 MyStreet 8 11 1729
2018/1/1 2020/12/31 MyStreet 1 4 18128
2018/1/1 2020/12/31 MyStreet 5 7 1763
2018/1/1 2020/12/31 MyStreet 8 11 18128

注意事项 #

• 请把代码中的YourTableName替换成你实际的表名；
• 如果存在同一个最小单元对应多个不同Value的情况，你需要根据业务规则添加筛选逻辑（比如取最新的记录、优先级高的Value等）；
• 这个方案适用于SQL Server 2008及以上版本，如果你用的是更旧的版本，可能需要调整日期函数的写法。

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