使用SQL或Pandas统计学生指定周期内连续缺勤天数

需求：统计每位学生从今日起回溯至过去某一日期（比如t-30）的连续缺勤天数。现有表class_absenses，字段包括：id、student_id、present（0=缺勤，1=出勤）、date。需要返回student_id与对应连续缺勤天数的列表，优先用SQL实现，若无法实现则用Pandas处理。

示例输入 #

id      student_id      present      date
0       1               0            4-28-2023
1       1               0            4-27-2023
2       1               1            4-26-2023
3       2               0            4-28-2023
4       2               1            4-27-2023
5       2               0            4-26-2023
6       3               1            4-28-2023
7       3               0            4-27-2023
8       3               0            4-26-2023

示例输出 #

student_id     ConsecutiveAbsense
1              2
2              1
3              0

SQL 实现方案 #

逻辑说明 #

按学生分组筛选指定时间范围的记录，从最新日期向前统计连续present=0的天数，遇到present=1则停止计数。

SQL 代码（MySQL） #

WITH filtered_data AS (
    SELECT 
        student_id,
        present,
        date,
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY date DESC) AS rn
    FROM class_absenses
    WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE()
),
consecutive_groups AS (
    SELECT 
        student_id,
        rn,
        SUM(CASE WHEN present = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY student_id ORDER BY rn) AS group_id
    FROM filtered_data
)
SELECT 
    student_id,
    COALESCE(MAX(CASE WHEN group_id = 0 THEN rn ELSE 0 END), 0) AS ConsecutiveAbsense
FROM consecutive_groups
GROUP BY student_id
ORDER BY student_id;

代码解释 #

1. filtered_data：筛选近30天记录，按学生分组后按日期倒序编号，最新日期对应rn=1。
2. consecutive_groups：通过累加present=1的次数生成分组ID，连续缺勤记录会被归为group_id=0，遇到出勤后分组ID递增。
3. 最终统计每个学生group_id=0的最大rn值，即从最新日期开始的连续缺勤天数；若最新日期为出勤，结果为0。

Pandas 实现方案 #

逻辑说明 #

将数据导入DataFrame后，按学生分组并按日期倒序排序，遍历每组记录统计连续present=0的天数，遇到present=1则终止计数。

Pandas 代码 #

import pandas as pd
from datetime import datetime, timedelta

# 模拟数据（实际可从数据库导入）
df = pd.DataFrame({
    'id': [0,1,2,3,4,5,6,7,8],
    'student_id': [1,1,1,2,2,2,3,3,3],
    'present': [0,0,1,0,1,0,1,0,0],
    'date': pd.to_datetime(['4-28-2023','4-27-2023','4-26-2023','4-28-2023','4-27-2023','4-26-2023','4-28-2023','4-27-2023','4-26-2023'])
})

# 筛选近30天数据
end_date = datetime.today()
start_date = end_date - timedelta(days=30)
filtered_df = df[(df['date'] >= start_date) & (df['date'] <= end_date)]

# 统计单学生连续缺勤天数
def count_absences(group):
    sorted_group = group.sort_values('date', ascending=False)
    count = 0
    for _, row in sorted_group.iterrows():
        if row['present'] == 0:
            count += 1
        else:
            break
    return count

# 分组计算并整理结果
result = filtered_df.groupby('student_id').apply(count_absences).reset_index(name='ConsecutiveAbsense')
print(result)

代码解释 #

1. 转换日期格式并筛选近30天记录。
2. 定义计数函数：对每组倒序遍历，统计连续缺勤天数，遇到出勤立即停止。
3. 按学生分组应用函数，得到最终统计结果。

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