如何用SQL实现带过期机制的违规累计求和及惩戒次数统计？

要实现需求，我们需要分两步：首先为每条违规记录生成对应的惩戒行动，然后统计指定时间段内各惩戒行动的次数。核心逻辑是针对每个学生的每一条违规，计算其过去30天内（不含当前违规日）的有效违规次数，以此映射到D0/D1/D2惩戒档。

1. 生成带惩戒行动的违规记录表 #

使用子查询计算每条记录对应的历史有效违规次数，再拼接成惩戒行动标识：

SELECT
    student_id,
    infraction_type,
    day,
    CONCAT('D', 
        (SELECT COUNT(*) 
         FROM infractions i2 
         WHERE i2.student_id = i.student_id 
           AND i2.day >= i.day - 30  -- 30天有效期判断
           AND i2.day < i.day        -- 排除当前违规本身
        )
    ) AS disciplinary_action_gen
FROM infractions
ORDER BY student_id, day;

逻辑说明： #

• 子查询针对当前违规记录，统计同一学生在当前违规日之前、且在30天有效期内的违规次数
• 次数为0对应D0，1对应D1，2对应D2，完全匹配示例中的惩戒规则

2. 统计指定时间段内的惩戒行动次数 #

通过CTE（公共表表达式）复用上述带惩戒行动的数据集，再筛选指定时间段并分组统计：

WITH infraction_with_discipline AS (
    SELECT
        i.student_id,
        i.infraction_type,
        i.day,
        CONCAT('D', 
            (SELECT COUNT(*) 
             FROM infractions i2 
             WHERE i2.student_id = i.student_id 
               AND i2.day >= i.day - 30 
               AND i2.day < i.day)
        ) AS disciplinary_action_gen
    FROM infractions i
)
SELECT
    disciplinary_action_gen AS disciplinary_action,
    COUNT(*) AS count
FROM infraction_with_discipline
-- 筛选第99天过去100天内的记录：day范围为99-100= -1 到99
WHERE day BETWEEN 99 - 100 AND 99
GROUP BY disciplinary_action_gen
ORDER BY disciplinary_action_gen;

自定义时间段说明： #

只需修改WHERE子句中的99 - 100和99即可适配不同统计需求，例如要在第X天统计过去Y天的记录，替换为day BETWEEN X - Y AND X。

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