SQL技术问询：计算排除重叠时段的总聚合周数

核心思路 #

要合并同一number+name维度下的重叠/连续日期范围，同时忽略daycount=0的行，最高效的方式是用窗口函数标记日期组再按组聚合。这种方法比自连接更适合处理数千行的数据量，逻辑也更清晰。

分步实现SQL #

1. 过滤无效行并排序

先筛掉daycount=0的记录，再按number、name、fromdate排序，为后续分组做准备。

2. 标记日期分组

用LAG()窗口函数获取上一行的todate，判断当前行的fromdate是否超出上一行的日期范围（包括连续情况：当前fromdate = 上一行todate + 1天），如果超出则开启新分组，否则归为同一组。

3. 合并日期区间并计算周数

按分组聚合得到每个合并后的日期区间，再计算每个区间的有效天数，最后转换为周数并求和。

完整SQL代码 #

-- 先创建CTE处理日期分组
WITH filtered_data AS (
    SELECT 
        number,
        name,
        fromdate,
        todate
    FROM #RW
    WHERE daycount <> 0  -- 忽略daycount=0的行
),
grouped_dates AS (
    SELECT 
        *,
        -- 标记分组：当前行fromdate > 上一行todate + 1天则新建分组
        SUM(CASE WHEN fromdate > DATEADD(day, 1, LAG(todate) OVER (PARTITION BY number, name ORDER BY fromdate)) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY number, name ORDER BY fromdate) AS date_group
    FROM filtered_data
)
-- 合并日期区间并计算总周数
SELECT 
    number,
    name,
    MIN(fromdate) AS merged_fromdate,
    MAX(todate) AS merged_todate,
    -- 计算每个区间的天数，再转为周数（可根据需求替换ROUND为FLOOR/CEILING）
    ROUND(DATEDIFF(day, MIN(fromdate), MAX(todate)) / 7.0, 2) AS interval_weeks,
    -- 总周数：当前number+name下所有区间周数之和
    SUM(ROUND(DATEDIFF(day, MIN(fromdate), MAX(todate)) / 7.0, 2)) OVER (PARTITION BY number, name) AS total_weeks
FROM grouped_dates
GROUP BY number, name, date_group
ORDER BY number, name, merged_fromdate;

代码说明 #

• filtered_data：过滤掉无效行，只保留有意义的日期记录。
• grouped_dates：通过LAG()获取上一行结束日期，用SUM()累积分组标记，把重叠/连续的日期归为同一组。
• 最终聚合：按number、name、date_group分组，得到合并后的日期区间，同时计算每个区间的周数和总周数。

为什么之前的自连接方法行不通？ #

自连接只能找出两两重叠的行，但无法直接将多个连续重叠的行合并成一个完整区间；而且对于数千行的数据，自连接会产生大量冗余数据，性能极低，也难以处理日期连续（上一行结束日和当前行开始日相邻）的情况。

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