分组内条件行编号：识别容忍≤2个连续零的Dummy值1序列并计算分组起止日期

实现方案 #

前置说明 #

以下方案适用于支持窗口函数的SQL引擎（Hive/Spark SQL/PostgreSQL/MySQL8.0及以上版本均可直接使用），默认原始表命名为original_table，字段定义如下：

• ID：主体唯一标识
• Dummy：0/1标记字段
• Date：标准日期类型的时间字段

完整SQL实现 #

WITH 
-- 步骤1：滑动窗口计算，标记有效记录
tmp_flag AS (
    SELECT 
        ID,
        Dummy,
        Date,
        -- 取当前行+前后各2行的Dummy值求和，空值补0，和≥3即为有效记录
        COALESCE(LAG(Dummy, 2) OVER (PARTITION BY ID ORDER BY Date), 0) +
        COALESCE(LAG(Dummy, 1) OVER (PARTITION BY ID ORDER BY Date), 0) +
        Dummy +
        COALESCE(LEAD(Dummy, 1) OVER (PARTITION BY ID ORDER BY Date), 0) +
        COALESCE(LEAD(Dummy, 2) OVER (PARTITION BY ID ORDER BY Date), 0) AS window_sum
    FROM original_table
),
tmp_valid AS (
    SELECT 
        ID,
        Date,
        CASE WHEN window_sum >= 3 THEN 1 ELSE 0 END AS is_valid
    FROM tmp_flag
),
-- 步骤2：对连续有效序列分组
tmp_group AS (
    SELECT 
        ID,
        Date,
        -- 全局行号-有效记录内部行号，差值相同的记录属于同一个连续序列
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) -
        ROW_NUMBER() OVER (PARTITION BY ID, is_valid ORDER BY Date) AS group_diff
    FROM tmp_valid
    WHERE is_valid = 1 -- 仅保留有效记录
)
-- 最终输出每个序列的编号和起止日期
SELECT 
    ID,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(Date)) AS group_id,
    MIN(Date) AS start_date,
    MAX(Date) AS end_date
FROM tmp_group
GROUP BY ID, group_diff
ORDER BY ID, start_date;

逻辑补充说明 #

• 如果你已经单独完成了第一步的有效记录筛选，可直接将第一步输出结果替换tmp_valid逻辑，跳过tmp_flag计算即可
• 差值法的核心逻辑是：连续的有效记录全局行号和有效内部行号同步增长，差值保持一致，一旦序列中断差值就会发生变化，自动完成分组
• 若需要保留序列内的所有明细记录，只需在tmp_group层对(ID, group_diff)生成统一组ID，无需最后做聚合操作

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