PostgreSQL车辆端点事件分组排名：闲置时长计算需求实现

问题描述 #

需要统计车辆在端点的闲置时长，规则是把到达事件（is_arrival_activity = TRUE）和对应的离开事件归为一组，通过每组的最早/最晚时间计算闲置时长。之前用DENSE_RANK()窗口函数没能得到预期的分组效果，求可行的SQL实现方案。

表结构 #

CREATE TABLE endpoint_event (
    event_id INT PRIMARY KEY,
    vehicle_id INT,
    event_timestamp TIMESTAMP,
    is_arrival_activity BOOLEAN
);

测试数据 #

INSERT INTO endpoint_event VALUES
(1, 101, '2024-05-01 08:00:00', TRUE),  -- 车辆101到达
(2, 101, '2024-05-01 08:15:00', FALSE), -- 车辆101离开
(3, 101, '2024-05-01 09:00:00', TRUE),  -- 车辆101再次到达
(4, 101, '2024-05-01 09:30:00', FALSE), -- 车辆101再次离开
(5, 102, '2024-05-01 10:00:00', TRUE),  -- 车辆102到达
(6, 102, '2024-05-01 10:45:00', FALSE); -- 车辆102离开

预期输出 #

尝试的SQL（未达预期） #

SELECT
    vehicle_id,
    MIN(event_timestamp) AS arrival_time,
    MAX(event_timestamp) AS departure_time,
    TIMESTAMPDIFF(MINUTE, MIN(event_timestamp), MAX(event_timestamp)) AS idle_duration
FROM (
    SELECT
        *,
        DENSE_RANK() OVER (PARTITION BY vehicle_id ORDER BY event_timestamp) AS rnk
    FROM endpoint_event
) t
GROUP BY vehicle_id, rnk
ORDER BY vehicle_id, arrival_time;

问题分析 #

DENSE_RANK()会给每一条事件单独生成排名，导致到达和离开事件被拆成不同分组，没法实现“到达-离开”的配对。这种场景需要用累计计数的方式给到达事件分组，让同一轮的到达、离开事件共享同一个分组ID。

正确的SQL实现 #

核心逻辑是：对每个车辆，累计统计到达事件的数量，每出现一次到达事件，分组ID就加1，后续的离开事件会继承这个ID，直到下一次到达出现。

WITH grouped_events AS (
    SELECT
        vehicle_id,
        event_timestamp,
        is_arrival_activity,
        -- 累计到达事件数，作为分组标识
        SUM(CASE WHEN is_arrival_activity THEN 1 ELSE 0 END) 
            OVER (PARTITION BY vehicle_id ORDER BY event_timestamp) AS group_id
    FROM endpoint_event
)
SELECT
    vehicle_id,
    -- 提取组内的到达时间（到达事件在前，取最小值）
    MIN(CASE WHEN is_arrival_activity THEN event_timestamp END) AS arrival_time,
    -- 提取组内的离开时间（离开事件在后，取最大值）
    MAX(CASE WHEN NOT is_arrival_activity THEN event_timestamp END) AS departure_time,
    -- 计算闲置时长（单位：分钟）
    TIMESTAMPDIFF(MINUTE,
        MIN(CASE WHEN is_arrival_activity THEN event_timestamp END),
        MAX(CASE WHEN NOT is_arrival_activity THEN event_timestamp END)
    ) AS idle_duration_minutes
FROM grouped_events
GROUP BY vehicle_id, group_id
-- 过滤未配对的事件（比如只有到达没有离开的情况）
HAVING arrival_time IS NOT NULL AND departure_time IS NOT NULL
ORDER BY vehicle_id, arrival_time;

逻辑说明 #

1. 窗口函数SUM(CASE...) OVER()会按车辆分组、时间排序，每遇到一个到达事件就给分组ID加1，确保同一轮的到达和离开事件属于同一个分组。
2. 分组后通过MIN()和MAX()分别提取到达、离开时间，再用TIMESTAMPDIFF计算时长。
3. 最后通过HAVING子句过滤掉未配对的无效事件（可选，根据业务需求调整）。

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