MySQL 5.6中如何计算分组数据的时间差

Got it, let's work through calculating the time difference between each paired START and END event for your session_id groups. Looking at your data, some sessions have multiple cycles of START → END, so we need to make sure we pair each start event with its corresponding end correctly.

First, let's recap your source data for clarity:

Solution Using Window Functions (Works in PostgreSQL, MySQL 8+, SQL Server, etc.) #

We'll use a common table expression (CTE) to group each START-END pair together, then calculate the time difference for each group.

WITH session_event_groups AS (
    SELECT
        session_id,
        event,
        created,
        -- Assign a unique group number to each START-END pair
        SUM(CASE WHEN event = 'START' THEN 1 ELSE 0 END) OVER (
            PARTITION BY session_id
            ORDER BY created
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS pair_group
    FROM your_table_name  -- Replace with your actual table name
)
SELECT
    session_id,
    pair_group,
    -- Calculate time difference between END and START
    MAX(CASE WHEN event = 'END' THEN created END) - MAX(CASE WHEN event = 'START' THEN created END) AS session_duration,
    -- Optional: Get duration in minutes for readability (adjust unit as needed)
    TIMESTAMPDIFF(MINUTE, 
                  MAX(CASE WHEN event = 'START' THEN created END), 
                  MAX(CASE WHEN event = 'END' THEN created END)
                 ) AS duration_minutes
FROM session_event_groups
GROUP BY session_id, pair_group
ORDER BY session_id, pair_group;

How This Works: #

1. CTE (session_event_groups): The SUM window function increments a counter every time it hits a START event for a session. This creates a pair_group number that ties each START to all subsequent events until the next START (including its matching END).
2. Main Query: We group by session_id and pair_group, then use MAX to pull the START and END timestamps for each group. Subtracting these gives the duration of that session cycle.

Sample Output: #

Note: NULL values appear for sessions where a START doesn't have a matching END yet (like session 101's second start and session 103's second start).

Handling Edge Cases #

• If you want to exclude unpaired events, add a HAVING clause to the main query:

  HAVING MAX(CASE WHEN event = 'END' THEN created END) IS NOT NULL
  

• For databases that don't support window functions (older MySQL versions), you can use correlated subqueries to find the next END for each START, but window functions are much more efficient.

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