如何为trajectory表新增trip_id列并基于user_id和session_id赋值？

问题背景 #

现有trajectory表结构及数据如下：

CREATE TABLE trajectory(
    user_id int, 
    session_id int, 
    timestamp timestamp with time zone,
    lat double precision, 
    lon double precision
);

INSERT INTO trajectory(user_id, session_id, timestamp, lat, lon) VALUES 
(1, 25304,'2008-10-23 02:53:04+01', 39.984702, 116.318417),
(1, 25304, '2008-10-23 02:53:10+01', 39.984683, 116.31845), 
(1, 25304, '2008-10-23 02:53:15+01', 39.984686, 116.318417),
(1, 25304, '2008-10-23 02:53:20+01', 39.984688, 116.318385), 
(1, 20959,'2008-10-24 02:09:59+01', 40.008304, 116.319876),
(1, 20959,'2008-10-24 02:10:04+01', 40.008413, 116.319962), 
(1, 20959,'2008-10-24 02:10:14+01', 40.007171, 116.319458),
(2, 55305, '2008-10-23 05:53:05+01', 39.984094, 116.319236), 
(2, 55305, '2008-10-23 05:53:11+01', 39.984198, 116.319322), 
(2, 55305, '2008-10-23 05:53:21+01', 39.984224, 116.319402), 
(2, 34104, '2008-10-23 23:41:04+01', 40.013867, 116.306473),
(2, 34104, '2008-10-23 23:41:16+01', 40.013907, 116.306488);

需求：为该表新增trip_id列，要求同一用户的同一session_id对应相同的trip_id，当session_id变化时trip_id递增；需修改表结构而非仅查询数据，同时说明原表已包含timestamp列（用于确定session顺序）。

实现步骤 #

1. 新增trip_id列 #

首先执行ALTER TABLE语句添加trip_id列：

ALTER TABLE trajectory ADD COLUMN trip_id INT;

2. 计算并更新trip_id值 #

使用窗口函数DENSE_RANK()按用户分区，以每个session的最早timestamp排序，为每个session分配递增的排名，再将该排名赋值给trip_id：

WITH trip_rank AS (
    SELECT 
        user_id,
        session_id,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY MIN(timestamp)) AS rank
    FROM trajectory
    GROUP BY user_id, session_id
)
UPDATE trajectory t
SET trip_id = tr.rank
FROM trip_rank tr
WHERE t.user_id = tr.user_id AND t.session_id = tr.session_id;

说明：通过MIN(timestamp)确保同一用户的session按时间先后顺序分配trip_id，保证trip_id随session变化正确递增。

3. 可选：设置trip_id为非空约束 #

如果业务要求trip_id不允许为空，可执行以下语句修改列属性：

ALTER TABLE trajectory ALTER COLUMN trip_id SET NOT NULL;

验证结果 #

执行上述操作后，查询表数据即可得到期望的结果：

SELECT user_id, session_id, timestamp, lat, lon, trip_id FROM trajectory ORDER BY user_id, trip_id, timestamp;

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