日期格式下的Gaps and Islands问题：合并连续日期区间

针对你需要将同ma_id下的连续日期合并为起止区间、日期中断时生成新行的需求，以下是适配纯DATE类型字段的解决方案：

正确SQL语句 #

SELECT
    ma_id,
    MIN(act_date) AS start_date,
    MAX(act_date) AS end_date
FROM (
    SELECT
        ma_id,
        act_date,
        -- 生成连续日期区间的分组标识：遇到日期中断或组内首行时，分组号+1
        SUM(CASE 
            WHEN prev_date IS NULL OR DATEDIFF(act_date, prev_date) > 1 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY ma_id ORDER BY act_date) AS grp
    FROM (
        SELECT
            ma_id,
            act_date,
            -- 获取同ma_id分组中，当前行的上一行日期
            LAG(act_date) OVER (PARTITION BY ma_id ORDER BY act_date) AS prev_date
        FROM XXXX.XXXX
        WHERE ma_id IN (1,2)
    ) t1
) t2
GROUP BY ma_id, grp
ORDER BY ma_id, start_date;

逻辑说明 #

1. 内层t1：利用LAG()窗口函数，为每个ma_id分组内的每一行获取上一行的日期prev_date，用于判断日期是否连续。
2. 中间层t2：通过累加求和生成分组标识grp：当prev_date为空（组内第一行），或者当前日期与prev_date的间隔超过1天时，判定为新的区间，分组号加1；否则沿用之前的分组号。
3. 外层查询：按ma_id和grp分组，取每个分组的最小日期作为区间起始、最大日期作为区间结束，得到最终的连续日期区间结果。

原SQL的问题分析 #

你之前的SQL无法适配DATE格式的核心问题：

• 不必要地在PARTITION BY中加入了date_format(act_date,'%d-%m-%Y')，这会把每个日期单独拆分为一个分组，完全破坏了连续日期的合并逻辑。
• 判断分组的条件prev_act_date >= act_date逻辑错误，应该通过日期差值判断是否中断，而非比较日期大小。
• 最终GROUP BY保留了date_format的字段，导致无法合并连续日期。

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