如何在MySQL中计算数据集里最长的连续会话段

Hey there, let's figure out how to calculate the longest stretch of consecutive sessions in your MySQL dataset. Based on your columns respondent_id, day_session, and daydiff, here's a straightforward, step-by-step solution:

Core Idea #

Consecutive sessions are those where each next session is exactly 1 day after the previous one (marked by daydiff = 1). We need to group these consecutive blocks, count how many days each block lasts, then pick the longest one.

Full SQL Solution #

-- First, group sessions into consecutive streaks
WITH session_streaks AS (
    SELECT
        respondent_id,
        day_session,
        daydiff,
        -- Create a unique group ID for each consecutive streak
        SUM(CASE WHEN daydiff != 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY respondent_id 
            ORDER BY day_session ASC
        ) AS streak_group
    FROM your_table_name
),
-- Calculate the length of each streak
streak_metrics AS (
    SELECT
        respondent_id,
        streak_group,
        COUNT(*) AS streak_length,
        MIN(day_session) AS streak_start_date,
        MAX(day_session) AS streak_end_date
    FROM session_streaks
    GROUP BY respondent_id, streak_group
)
-- Fetch the longest streak(s)
SELECT
    respondent_id,
    streak_length,
    streak_start_date,
    streak_end_date
FROM streak_metrics
WHERE streak_length = (SELECT MAX(streak_length) FROM streak_metrics);

Breakdown of the Query #

1. session_streaks CTE:
   We use a running total (SUM() OVER()) to split sessions into groups. Every time we hit a session where daydiff ≠ 1 (meaning there's a gap between this session and the last), we increment the group ID. This groups all consecutive sessions together.

2. streak_metrics CTE:
   For each streak group, we count how many days it lasts (streak_length) and capture the start/end dates of the streak for context.

3. Final Selection:
   We filter to show only the streaks that have the maximum length across all respondents. If you want the longest streak per respondent, you can add a RANK() window function here instead of the global max filter.

Example Output (Using Your Sample Data) #

For your sample rows:

respondent_id day_session daydiff
nmo876 11/19/2017 0
nmo876 11/20/2017 1
nmo876 11/21/2017 1
nmo876 11/23/2017 2
nmo876 11/24/2017 1

The query would return:

This is the longest consecutive streak in the sample (3 days of back-to-back sessions).

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