User action 연속일수 구하기

학생 연속 출석일수 구하기

제가 학생 3명이 있는 학급을 관리하고 있는 선생님이라고 가정해보겠습니다.

출석을 열심히 한 학생에게 상을 주고 싶은데, 특정 기간동안 많이 출석한 학생보다 연속적으로 오래 출석한 학생에게 상을 주고 싶습니다.

아래 데이터는 학생 3명의 2020-12-01 ~ 2020-12-05 5일간의 출석기록입니다.
DB에 아래 데이터가 있다고 가정하고 SQL을 이용해서 학생별로 연속출석일수를 구해보겠습니다.

Table: Student

student_code	date	status
A	2020-12-01	1
A	2020-12-02	0
A	2020-12-03	1
A	2020-12-04	0
A	2020-12-05	1
B	2020-12-01	1
B	2020-12-02	1
B	2020-12-03	1
B	2020-12-04	0
B	2002-12-05	0
C	2020-12-01	1
C	2020-12-02	1
C	2020-12-03	0
C	2020-12-04	1
C	2020-12-05	1

with student_attendance as (
    SELECT
        student_code
        , date
        , status
        , row_number() over(partition by student_code order by date) s_rownum
        , row_number() over(partition by student_code, status order by date) s_t_rownum
    FROM student
), student_consecutive_days as (
    SELECT
        student_code
        , date
        , status
        , (rn1 - rn2) as grp
        , min(date) as start_date
        , max(date) as end_date
        , count(*) as consecutive_days
    FROM student_attendance
    GROUP BY student_code, status, grp
    ORDER BY student_code, start_date
)

SELECT
	student_code
	, max(consecutive_days) # 가장 길었던 연속일수
FROM student_consecutive_days
WHERE status = 1 # 출석했던 구간 중
GROUP BY student_code

SQL 설명

student_attendance 라는 가상 table을 만듭니다. 기존 data에서 출석여부별 연속기간을 구하기 위해 row nuber를 붙이는데
여기서 student_code를 partition으로 하는 row number와 (student_code, status) 를 partition을 하는 row number 두개가 필요합니다.

Why?

첫 번째 row number는 학생마다 날짜순서대로 붙게됩니다. 두 번째 row number는 status가 추가적으로 partiton 되어있기 때문에
학생의 날짜, 출석여부 별로 row number가 붙게됩니다. 즉, 연속적으로 출석하지 않은 시점에 row number 1과 row number 2의 차이가 발생하게 됩니다. 그러면 이 차이가 똑같은 구간은 똑같은 상태가 지속되고 있음을 나타내게 됩니다.

학생의 출석 상태별 연속되는 구간 Table을 만들고 난 후, 마지막으로 출석상태인 구간만 filtering 하여 max 연속일수를 구하면
학생별로 기간 내 가장 길었던 연속일수를 구할 수 있습니다.