PostgreSQL分组重复项：基于RTLS历史数据计算用户房间停留时长

Got it, let's work through this problem together. Since you already pointed out using the LAG() window function, that's exactly the right tool for the job—we need to compare each position record to the previous one to calculate how long a user stayed in each room.

First, let's start with a reasonable assumption for your historical_movements table structure (adjust field names if yours differ):

CREATE TABLE historical_movements (
    movement_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    room_id VARCHAR(50) NOT NULL, -- e.g., room number or name
    event_timestamp TIMESTAMPTZ NOT NULL -- Timestamp of the position update
);

This table stores position change events (since your RTLS doesn't collect continuous data, only when a user moves to a new room).

Step-by-Step Query Solution #

Here's a complete query that uses LAG() to track previous positions, calculates individual stay intervals, then aggregates total duration per user and room:

WITH movement_with_prev AS (
    SELECT
        user_id,
        room_id,
        event_timestamp,
        -- Get the previous room and timestamp for the same user (ordered by time)
        LAG(room_id) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_room_id,
        LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_event_timestamp
    FROM historical_movements
),
stay_intervals AS (
    -- Calculate duration for completed stays (when user moved to a new room)
    SELECT
        user_id,
        prev_room_id AS room_id,
        prev_event_timestamp AS stay_start,
        event_timestamp AS stay_end,
        event_timestamp - prev_event_timestamp AS duration
    FROM movement_with_prev
    WHERE prev_room_id IS NOT NULL -- Skip the first record (no prior position)
      AND prev_room_id != room_id -- Only process when room changed
    UNION ALL
    -- Handle the last known position (user is still in the room)
    SELECT
        user_id,
        room_id,
        event_timestamp AS stay_start,
        NOW() AS stay_end, -- Use current time as end; replace with a fixed cutoff if needed
        NOW() - event_timestamp AS duration
    FROM (
        SELECT
            user_id,
            room_id,
            event_timestamp,
            ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp DESC) AS rn
        FROM historical_movements
    ) latest_movements
    WHERE rn = 1
)
-- Aggregate total stay time per user and room
SELECT
    user_id,
    room_id,
    SUM(duration) AS total_stay_duration,
    COUNT(*) AS stay_count -- Optional: number of times the user stayed in the room
FROM stay_intervals
GROUP BY user_id, room_id
ORDER BY user_id, total_stay_duration DESC;

Breakdown of the Query #

1. movement_with_prev CTE:

   • Uses LAG() window function partitioned by user_id (so we only compare records for the same user) and ordered by event_timestamp (to get the chronological previous record).
   • This adds columns for the user's prior room and the timestamp of that prior position.

2. stay_intervals CTE:

   • First section: Calculates duration for stays that ended (when the user moved to a new room). We use the previous room as the stay room, the previous timestamp as start time, and the current record's timestamp as end time.
   • Second section: Handles the user's last known position—if there's no subsequent move record, we calculate the stay from the last timestamp to the current time (replace NOW() with a fixed cutoff time if you don't want real-time calculations).

3. Final Aggregation:

   • Groups by user_id and room_id to sum up all stay durations for each user-room pair. The optional stay_count shows how many times the user entered that room.

Important Notes #

• Deduplicate Redundant Records: If your RTLS occasionally reports the same room for a user multiple times in a row (no actual movement), add a deduplication step first to avoid incorrect duration calculations:

  WITH deduplicated_movements AS (
      SELECT DISTINCT ON(user_id, room_id, DATE_TRUNC('second', event_timestamp))
          user_id,
          room_id,
          event_timestamp
      FROM historical_movements
      ORDER BY user_id, room_id, event_timestamp
  ),
  movement_with_prev AS (
      SELECT ... FROM deduplicated_movements ...
  )
  

• Time Zone Handling: Use TIMESTAMPTZ (time zone-aware timestamps) to avoid issues with daylight saving or cross-timezone deployments.
• Custom End Time: If you don't want to use current time for the last stay, replace NOW() with a specific timestamp (e.g., '2024-05-20 23:59:59'::TIMESTAMPTZ).

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