如何用单条MySQL查询汇总连续行的用户房间停留时长

I have a MySQL table for tracking multi-user movement trajectories inside a building, which includes the fields user_id (user ID), location (room), and time (timestamp). The table data is as follows:

user_id location time
------- -------- -------------------
1       room1    2018-05-18 03:20:00
1       room1    2018-05-18 03:21:15
1       room2    2018-05-18 03:22:07
2       room1    2018-05-18 03:24:20
2       room1    2018-05-18 03:27:55
2       room1    2018-05-18 03:29:09
1       room2    2018-05-18 03:32:37
1       room1    2018-05-18 03:34:41
1       room1    2018-05-18 03:39:28

I need to summarize each user's stay duration in each room, expecting the result in the following format:

user_id location duration(s)
------- -------- -----------
1       room1    75
2       room1    289
1       room2    630
1       room1    287

Is it possible to achieve this with a single SQL query?

Absolutely! This is a classic sessionization problem, and you can solve it with a single MySQL query using window functions (available in MySQL 8.0+). The key is to group consecutive rows where a user stays in the same room, then calculate the total duration of each group. Here's the query:

WITH user_stay_sessions AS (
    SELECT
        user_id,
        location,
        time,
        -- Generate a unique session ID for each continuous stay in the same room
        SUM(CASE WHEN prev_location != location OR prev_location IS NULL THEN 1 ELSE 0 END)
            OVER (PARTITION BY user_id ORDER BY time) AS session_id
    FROM (
        SELECT
            user_id,
            location,
            time,
            -- Fetch the user's previous location to detect room changes
            LAG(location) OVER (PARTITION BY user_id ORDER BY time) AS prev_location
        FROM movement_trajectories
    ) AS lagged_records
)
SELECT
    user_id,
    location,
    -- Calculate total stay duration in seconds for each session
    TIMESTAMPDIFF(SECOND, MIN(time), MAX(time)) AS `duration(s)`
FROM user_stay_sessions
GROUP BY user_id, location, session_id
ORDER BY MIN(time);

Let me break down how this works step by step:

1. Inner subquery (lagged_records): Uses the LAG() window function to grab the user's previous room location, ordered by timestamp. This lets us spot when a user moves to a new room.
2. CTE (user_stay_sessions): Creates a session_id by incrementing a counter every time the user's location changes (or for their first entry). This groups all consecutive rows where the user stays in the same room into one session.
3. Final aggregation: Groups by user_id, location, and session_id to compute the total duration of each continuous stay. TIMESTAMPDIFF(SECOND, MIN(time), MAX(time)) calculates the time between the first and last entry of the session in seconds.

If you're on a pre-8.0 MySQL version (no window functions), you can still do this with user-defined variables, but the window function approach is far cleaner and more efficient. Running this query against your sample data will produce exactly the output format you want.

Content sourced from Stack Exchange, question author: Firman R