学生连续缺勤5天，节假日除外

Question

我正在使用代码点火器和考勤表，如下所示：

attendance ID   timestamp   student_id     status
1           01-01-20        1           P
2           01-01-20        2           P
3           02-01-20        1           P
4           02-01-20        2           A
5           03-01-20        1           P
6           03-01-20        2           A
7           04-01-20        1           H
8           04-01-20        2           H
9           05-01-20        1           P
10          05-01-20        2           A

我的目标是获得从今天起过去 3 个月内连续缺勤 1 天的学生证，不包括中间的假期，如上表中所示，学生证 2 应该是连续缺勤 3 天的学生证，不包括 1 月 4 日的假期。

我正在使用带有mysql的Apache/2.4.23(Win32(OpenSSL/1.0.2h PHP/5.6.28。我已经能够连续缺席 3 次，但是当中间有假期时，我无法在那里找到工作。这是我现有的代码：

SELECT *,
CASE
WHEN (@studentID  = student_id) THEN @absentRun := IF(status = A, 0, @absentRun + 1)
WHEN (@studentID := student_id) THEN @absentRun := IF(status = A, @absentRun + 1, 0)
END AS absentRun
FROM attendance
Where timestamp BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE() AND year='2019-2020'
ORDER BY student_id, timestamp;

我真的很感激有人能快速回答来帮助我解决这个问题。我真的希望有一个解决方案，因为我是第一次在这里发帖寻求帮助。提前谢谢。

Answer 1

我理解这是差距和岛屿问题的变体。这是使用row_number()(在MySQL 8.0中可用(解决它的一种方法 - 行号之间的差异为您提供了每条记录所属的组。

select 
student_id,
min(timestamp) timestamp_start,
max(timestramp) timestamp_end
from (
select 
t.*, 
row_number() over(partition by student_id order by timestamp) rn1,
row_number() over(partition by student_id, status order by timestamp) rn2
from mytable t
) t
where status = 2
group by student_id, rn1 - rn2
having count(*) >= 5

这将为每个学生至少连续缺勤 5 天的连续记录提供一条记录。作为奖励，该查询还显示每个连胜的开始和结束日期。