3617. 查找具有螺旋学习模式的学生

# Write your MySQL query statement below
WITH
    ranked_sessions AS (
        SELECT
            ss.student_id,
            ss.session_id,
            ss.session_date,
            ss.subject,
            ss.hours_studied,
            DATEDIFF(
                ss.session_date,
                LAG(ss.session_date) OVER (
                    PARTITION BY ss.student_id
                    ORDER BY ss.session_date, ss.session_id
                )
            ) AS date_diff,
            ROW_NUMBER() OVER (
                PARTITION BY ss.student_id
                ORDER BY ss.session_date, ss.session_id
            ) AS session_index
        FROM study_sessions ss
    ),
    contiguous_students AS (
        SELECT student_id
        FROM ranked_sessions
        GROUP BY student_id
        HAVING
            SUM(
                CASE
                    WHEN date_diff > 2 THEN 1
                    ELSE 0
                END
            ) = 0
    ),
    student_stats AS (
        SELECT
            r.student_id,
            COUNT(*) AS session_count,
            COUNT(DISTINCT r.subject) AS cycle_length,
            SUM(r.hours_studied) AS total_hours
        FROM
            ranked_sessions r
            JOIN contiguous_students c ON c.student_id = r.student_id
        GROUP BY r.student_id
        HAVING
            cycle_length >= 3
            AND session_count >= cycle_length * 2
            AND session_count % cycle_length = 0
    )
SELECT
    s.student_id,
    s.student_name,
    s.major,
    st.cycle_length,
    st.total_hours AS total_study_hours
FROM
    student_stats st
    JOIN students s ON s.student_id = st.student_id
WHERE
    NOT EXISTS (
        SELECT 1
        FROM
            ranked_sessions cur
            JOIN ranked_sessions cyc
                ON cyc.student_id = cur.student_id
                AND cyc.session_index = (cur.session_index - 1) % st.cycle_length + 1
        WHERE cur.student_id = st.student_id AND NOT (cur.subject <=> cyc.subject)
    )
ORDER BY st.cycle_length DESC, st.total_hours DESC;

import pandas as pd


def find_study_spiral_pattern(
    students: pd.DataFrame, study_sessions: pd.DataFrame
) -> pd.DataFrame:
    study_sessions = study_sessions.copy()
    study_sessions["session_date"] = pd.to_datetime(study_sessions["session_date"])

    result = []

    for student_id, group in study_sessions.groupby("student_id"):
        group = group.sort_values(["session_date", "session_id"]).reset_index(drop=True)
        # One gap longer than two days breaks the whole record. A later fragment
        # is not a separate spiral.
        if len(group) >= 2:
            gaps = group["session_date"].diff().dt.days.iloc[1:]
            if (gaps > 2).any():
                continue
        _check_pattern(student_id, [row for _, row in group.iterrows()], result)

    df_result = pd.DataFrame(
        result, columns=["student_id", "cycle_length", "total_study_hours"]
    )

    if df_result.empty:
        return pd.DataFrame(
            columns=[
                "student_id",
                "student_name",
                "major",
                "cycle_length",
                "total_study_hours",
            ]
        )

    df_result = df_result.merge(students, on="student_id")
    df_result = df_result[
        ["student_id", "student_name", "major", "cycle_length", "total_study_hours"]
    ]

    return df_result.sort_values(
        by=["cycle_length", "total_study_hours"], ascending=[False, False]
    ).reset_index(drop=True)


def _check_pattern(student_id, sessions, result):
    subjects = [row["subject"] for row in sessions]
    hours = sum(row["hours_studied"] for row in sessions)

    n = len(subjects)
    # Cycle length is the number of distinct subjects, not an arbitrary divisor.
    cycle_len = len(set(subjects))
    if cycle_len < 3 or n < cycle_len * 2 or n % cycle_len != 0:
        return

    first_cycle = subjects[:cycle_len]
    if subjects != first_cycle * (n // cycle_len):
        return

    result.append(
        {
            "student_id": student_id,
            "cycle_length": cycle_len,
            "total_study_hours": hours,
        }
    )