如何在SQL中实现状态变更时重置的连续缺勤计数

要实现按人员分组的连续缺勤计数，核心是先识别连续的缺勤段，再在每个段内递增计数，遇到未缺勤的行则重置计数。以下是具体的SQL实现方案：

假设你的表名为employee_absences，字段包括：

• person_id（人员ID）
• month_date（月份日期，需确保按时间排序）
• is_absent（是否缺勤，值为'Yes'/'No'）

完整SQL代码 #

WITH absence_groups AS (
    SELECT 
        person_id,
        month_date,
        is_absent,
        -- 为每个连续缺勤段生成分组ID：遇到未缺勤时分组号+1
        SUM(CASE WHEN is_absent = 'No' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY person_id ORDER BY month_date) AS group_id
    FROM employee_absences
)
SELECT
    CASE 
        WHEN is_absent = 'Yes' THEN 
            ROW_NUMBER() OVER (PARTITION BY person_id, group_id ORDER BY month_date)::VARCHAR
        ELSE '-' 
    END AS "连续缺勤月数",
    is_absent AS "是否缺勤",
    month_date AS "月份日期",
    person_id AS "人员ID"
FROM absence_groups
ORDER BY person_id, month_date;

逻辑说明 #

1. 生成缺勤分组：
   使用SUM() OVER()窗口函数，按person_id分区、month_date排序。每当遇到is_absent='No'时，累加1生成新的group_id，这样连续的缺勤行（'Yes'）会被分到同一个group_id下，未缺勤的行则触发分组重置。

2. 计算连续缺勤数：
   在每个person_id + group_id的分组内，用ROW_NUMBER()对缺勤行（'Yes'）生成递增序号；未缺勤行直接显示'-'。

为什么单纯用ROW_NUMBER() PARTITION BY person_id不行？ #

单纯按person_id分区的ROW_NUMBER()会对所有行连续计数，无法识别“未缺勤重置”的逻辑，必须先通过分组标识拆分出独立的连续缺勤段，再在段内计数。

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