PostgreSQL按活动日统计人员连续缺勤天数实现方案咨询

实现逻辑 #

• 第一步：按人员+日期聚合，判定单日有效状态
  先对每个用户的单日签到记录做聚合，只要当日存在1条Present记录，当日标记为出席，仅当全日所有记录都是Absent时标记为有效缺勤日，过滤掉不符合的日期。
• 第二步：连续缺勤区间分组
  用PostgreSQL原生窗口函数实现连续区间判定，不需要依赖会话变量，逻辑更稳定。对每个用户按活动日期排序后，给缺勤记录生成连续分组标识，同组内的日期即为连续的缺勤活动日。
• 第三步：计算连续缺勤天数并关联原始记录
  对每个连续缺勤分组计算累计天数，再关联回原始签到表，得到每条记录对应的连续缺勤计数，可直接筛选大于指定阈值x的记录。

可运行SQL示例 #

-- 自定义连续缺勤阈值min_consec_days，示例设置为3
WITH params AS (SELECT 3 AS min_consec_days),
-- 步骤1：聚合计算每个用户单日的有效签到状态
daily_status AS (
    SELECT
        Name,
        DATE(EventDateTime) AS event_date,
        BOOL_AND(Mark = 'Absent') AS is_full_absent
    FROM attendance
    GROUP BY Name, DATE(EventDateTime)
),
-- 步骤2：生成连续缺勤的分组标识
absent_groups AS (
    SELECT
        Name,
        event_date,
        is_full_absent,
        -- 核心逻辑：连续的缺勤日会被划分到同一个组ID下
        SUM(CASE WHEN is_full_absent THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Name ORDER BY event_date) AS absent_group_id
    FROM daily_status
),
-- 步骤3：计算每个缺勤组的连续缺勤天数
consec_calc AS (
    SELECT
        Name,
        event_date,
        absent_group_id,
        COUNT(*) OVER (PARTITION BY Name, absent_group_id ORDER BY event_date) AS ConsecCount
    FROM absent_groups
    WHERE is_full_absent = TRUE
)
-- 关联原始表输出最终结果，筛选符合阈值的记录
SELECT
    a.Name,
    a.EventDateTime,
    a.Mark,
    c.ConsecCount
FROM attendance a
JOIN consec_calc c 
    ON a.Name = c.Name 
    AND DATE(a.EventDateTime) = c.event_date
JOIN params p ON c.ConsecCount >= p.min_consec_days
ORDER BY a.Name, a.EventDateTime;

性能优化建议 #

• 建联合索引CREATE INDEX idx_att_name_date ON attendance (Name, DATE(EventDateTime))，可大幅提升分组、排序的执行效率，适配百万级以上数据量。
• 若仅需定期生成报表，可新增日期分区对历史数据做增量计算，不需要每次全表扫描。

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