Oracle SQL查询：合并连续任务为项目按完成天数升序输出起止日期

原SQL思路问题分析 #

原思路只筛选出了每个新项目的起始行，没有对属于同一项目的连续任务做分组聚合，且存在冗余操作：START_DATE本身是DATE类型，不需要再用to_date做转换，多次转换反而可能引发日期格式异常。

正确解法（连续序列孤岛问题标准实现） #

这类连续数据合并属于经典的「孤岛问题」，通过窗口函数标记分组即可实现，完整SQL如下：

SELECT 
    MIN(start_date) AS project_start_date,
    MAX(end_date) AS project_end_date,
    MAX(end_date) - MIN(start_date) AS consume_days
FROM (
    SELECT 
        task_id,
        start_date,
        end_date,
        -- 对分组标记累加，得到同项目的分组ID
        SUM(group_flag) OVER(ORDER BY start_date) AS group_id
    FROM (
        SELECT 
            task_id,
            start_date,
            end_date,
            -- 上一个任务的结束日期不等于当前任务开始日期则标记为1，代表新项目开始
            CASE WHEN LAG(end_date) OVER(ORDER BY start_date) = start_date THEN 0 ELSE 1 END AS group_flag
        FROM hr.project
    ) t1
) t2
GROUP BY group_id
ORDER BY consume_days ASC, project_start_date ASC;

逻辑说明 #

1. 最内层子查询用LAG窗口函数取上一行的end_date，和当前行start_date比较，新项目起始行标记为1，同项目后续行标记为0。
2. 第二层子查询对标记做累加求和，同一个项目内的所有行累加结果相同，作为分组ID。
3. 外层按分组ID聚合，取最小开始日期、最大结束日期计算项目周期，最后按要求排序。

运行结果 #

完全符合规则要求。

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