无法优化基于attempt表的会话合并查询求助

Got it, let's work through this problem together. First, let's align on the core requirements: we have an attempt table tracking students' activity attempts. Consecutive attempts with gaps <20 minutes belong to the same session; gaps >20 minutes count as rest periods. We need to generate a combined list of sessions and rests, each with start time, end time (per your definition: session end = next session start), and duration.

First, let's assume a typical structure for your attempt table (adjust if your schema differs):

CREATE TABLE attempt (
    student_id INT,
    attempt_time DATETIME,
    -- Optional: activity_id or other context columns
);

Step-by-Step SQL Solution #

We'll use CTEs (Common Table Expressions) to break this into manageable chunks:

WITH ranked_attempts AS (
    SELECT 
        student_id,
        attempt_time,
        -- Calculate minutes since the last attempt for the same student
        TIMESTAMPDIFF(MINUTE, LAG(attempt_time) OVER (PARTITION BY student_id ORDER BY attempt_time), attempt_time) AS time_since_last,
        -- Flag if this attempt starts a new session: first attempt OR gap >20 mins
        CASE 
            WHEN LAG(attempt_time) OVER (PARTITION BY student_id ORDER BY attempt_time) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(MINUTE, LAG(attempt_time) OVER (PARTITION BY student_id ORDER BY attempt_time), attempt_time) > 20 THEN 1
            ELSE 0
        END AS is_new_session
    FROM attempt
),
session_groups AS (
    SELECT 
        student_id,
        attempt_time,
        -- Assign a unique session ID by summing new session flags (runs consecutively)
        SUM(is_new_session) OVER (PARTITION BY student_id ORDER BY attempt_time) AS session_id
    FROM ranked_attempts
),
session_summary AS (
    SELECT 
        student_id,
        session_id,
        MIN(attempt_time) AS session_start, -- First attempt in the session
        MAX(attempt_time) AS session_last_attempt -- Last attempt in the session
    FROM session_groups
    GROUP BY student_id, session_id
),
session_with_next AS (
    SELECT 
        s.student_id,
        s.session_id,
        s.session_start,
        -- Use next session's start as current session's end (per your definition)
        -- If no next session, use the session's last attempt time
        COALESCE(LEAD(s.session_start) OVER (PARTITION BY s.student_id ORDER BY s.session_id), s.session_last_attempt) AS session_end,
        s.session_last_attempt,
        -- Two duration options: adjust based on your needs
        TIMESTAMPDIFF(MINUTE, s.session_start, s.session_last_attempt) AS actual_session_duration, -- Time within the session
        TIMESTAMPDIFF(MINUTE, s.session_start, COALESCE(LEAD(s.session_start) OVER (PARTITION BY s.student_id ORDER BY s.session_id), s.session_last_attempt)) AS session_duration_per_definition -- Time until next session
    FROM session_summary s
),
rest_periods AS (
    SELECT 
        student_id,
        'Rest' AS period_type,
        session_last_attempt AS rest_start,
        LEAD(session_start) OVER (PARTITION BY student_id ORDER BY session_id) AS rest_end,
        TIMESTAMPDIFF(MINUTE, session_last_attempt, LEAD(session_start) OVER (PARTITION BY student_id ORDER BY session_id)) AS rest_duration
    FROM session_summary s
    -- Only include rests that have a subsequent session
    WHERE LEAD(session_start) OVER (PARTITION BY student_id ORDER BY session_id) IS NOT NULL
),
combined_periods AS (
    -- Add session records to the combined list
    SELECT 
        student_id,
        'Session' AS period_type,
        session_start AS period_start,
        session_end AS period_end,
        session_duration_per_definition AS duration_minutes
    FROM session_with_next
    UNION ALL
    -- Add rest records to the combined list
    SELECT 
        student_id,
        period_type,
        rest_start AS period_start,
        rest_end AS period_end,
        rest_duration AS duration_minutes
    FROM rest_periods
)
-- Final sorted list of sessions and rests
SELECT 
    student_id,
    period_type,
    period_start,
    period_end,
    duration_minutes
FROM combined_periods
ORDER BY student_id, period_start;

Key Notes & Adjustments #

• Student Grouping: The query partitions by student_id because sessions are student-specific. If you don't need to segment by student, remove all PARTITION BY student_id clauses.
• Duration Options: I included two duration calculations:
  • actual_session_duration: Time from the first to last attempt in the session (pure session activity time)
  • session_duration_per_definition: Time from session start to the next session's start (matches your end time definition)
    Pick the one that fits your use case.
• Last Session Handling: The final session's end time uses its own last attempt time (since there's no next session). If you prefer to leave this null or handle it differently, adjust the COALESCE logic.

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