PostgreSQL如何为拼车表生成trip_id 实现同司机同行程乘客分组

实现思路 #

要同时满足「2小时时间窗口」和「单行程最多3名乘客」两个分组规则，采用递归CTE逐行判断分组边界的方案最准确，不会出现边界判定错误的问题。

完整查询SQL #

WITH RECURSIVE ranked_rides AS (
    -- 按司机分组、上车时间排序，给每个行程生成连续序号
    SELECT 
        id,
        driver_id,
        passenger_id,
        meeting_at,
        ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY meeting_at ASC) AS rn
    FROM carpooling_proofs
),
trip_groups AS (
    -- 递归初始节点：每个司机的第一条行程作为首个行程组的起点
    SELECT 
        id,
        driver_id,
        passenger_id,
        meeting_at,
        rn,
        id AS trip_id,
        meeting_at AS trip_start_time,
        1 AS passenger_count
    FROM ranked_rides
    WHERE rn = 1

    UNION ALL

    -- 递归处理后续行程，判断是否可加入上一个行程组
    SELECT 
        r.id,
        r.driver_id,
        r.passenger_id,
        r.meeting_at,
        r.rn,
        CASE 
            WHEN r.meeting_at <= t.trip_start_time + INTERVAL '2 hours' AND t.passenger_count < 3
            THEN t.trip_id
            ELSE r.id
        END AS trip_id,
        CASE 
            WHEN r.meeting_at <= t.trip_start_time + INTERVAL '2 hours' AND t.passenger_count < 3
            THEN t.trip_start_time
            ELSE r.meeting_at
        END AS trip_start_time,
        CASE 
            WHEN r.meeting_at <= t.trip_start_time + INTERVAL '2 hours' AND t.passenger_count < 3
            THEN t.passenger_count + 1
            ELSE 1
        END AS passenger_count
    FROM ranked_rides r
    JOIN trip_groups t ON r.driver_id = t.driver_id AND r.rn = t.rn + 1
)
-- 输出最终结果
SELECT 
    id,
    driver_id,
    passenger_id,
    meeting_at,
    trip_id
FROM trip_groups
ORDER BY driver_id, meeting_at ASC;

补充说明 #

如果需要将trip_id持久化存储到原表，可以先执行新增字段语句：

ALTER TABLE carpooling_proofs ADD COLUMN trip_id UUID;

再配合UPDATE语句，用上述查询的结果更新对应行的trip_id值即可。

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