基于虚拟Seq列及连续日期，查找各列1/连续1的最小/最大日期范围

这个问题属于**孤岛与间隙（Islands and Gaps）**的经典场景——我们需要为每个动物、每个Seq列，找出所有连续1的时间段的最小和最大日期。下面用SQL来实现这个需求，我会一步步拆解逻辑：

步骤1：把宽表转成便于处理的长表 #

原来的表格是"宽表"格式（每个Seq单独一列），我们先把它转成"长表"，让每条记录对应一个动物、一个日期、一个Seq类型和它的值，这样后续处理连续区间会更方便：

WITH unpivoted_data AS (
    SELECT 
        Animal,
        Calendar_Date,
        Seq_Type,
        Seq_Value
    FROM 
        your_table_name  -- 替换成你的实际表名
    UNPIVOT (
        Seq_Value FOR Seq_Type IN (SeqA, SeqB, SeqC, SeqD, SeqE)
    ) AS unpvt
    WHERE Seq_Value = 1  -- 只保留值为1的有效记录
),

步骤2：标记连续的Seq区间 #

接下来要给每个动物+Seq类型的连续1记录分组。这里用日期差分组法：计算每个日期与该组（Animal+Seq_Type）内最小日期的差值，再计算每个日期的行号差值，两者的差相同的就是连续的区间：

grouped_islands AS (
    SELECT 
        Animal,
        Seq_Type,
        Calendar_Date,
        -- 生成分组标识：连续日期的记录会有相同的group_id
        DATEADD(day, -ROW_NUMBER() OVER (PARTITION BY Animal, Seq_Type ORDER BY Calendar_Date), Calendar_Date) AS group_id
    FROM 
        unpivoted_data
)

步骤3：聚合每个区间的最小/最大日期 #

最后按动物、Seq类型和分组标识聚合，就能得到每个连续1区间的起止日期：

SELECT 
    Animal,
    Seq_Type,
    MIN(Calendar_Date) AS Min_Calendar_Date,
    MAX(Calendar_Date) AS Max_Calendar_Date
FROM 
    grouped_islands
GROUP BY 
    Animal,
    Seq_Type,
    group_id
ORDER BY 
    Animal,
    Seq_Type,
    Min_Calendar_Date;

针对你提供的示例数据，运行结果如下： #

补充适配说明 #

如果你的SQL方言不支持UNPIVOT（比如部分版本的MySQL），可以用UNION ALL手动转换长表：

SELECT Animal, Calendar_Date, 'SeqA' AS Seq_Type, SeqA AS Seq_Value FROM your_table_name
UNION ALL
SELECT Animal, Calendar_Date, 'SeqB' AS Seq_Type, SeqB AS Seq_Value FROM your_table_name
UNION ALL
SELECT Animal, Calendar_Date, 'SeqC' AS Seq_Type, SeqC AS Seq_Value FROM your_table_name
UNION ALL
SELECT Animal, Calendar_Date, 'SeqD' AS Seq_Type, SeqD AS Seq_Value FROM your_table_name
UNION ALL
SELECT Animal, Calendar_Date, 'SeqE' AS Seq_Type, SeqE AS Seq_Value FROM your_table_name
WHERE Seq_Value = 1

这个方法既能处理单个1的孤立记录（比如Cat的SeqD仅一天），也能完美覆盖连续多天的1的区间。

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