如何在Node.js的Sequelize中创建新表统计用户房间首末次访问时间

1. 创建目标表 #

先创建存储用户房间访问时段的表，包含用户ID、房间ID、首次访问时间、末次访问时间，同时通过外键关联原用户表和房间表保证数据完整性：

CREATE TABLE user_room_visit (
    userId INT,
    roomId INT,
    first_seen BIGINT,
    last_seen BIGINT,
    PRIMARY KEY (userId, roomId),
    FOREIGN KEY (userId) REFERENCES users(id),
    FOREIGN KEY (roomId) REFERENCES rooms(id)
);

2. 统计并插入数据 #

核心逻辑是识别用户切换房间的时间，将其作为前一房间的末次访问时间，下面提供两种适配不同数据库的实现方式：

方式一：使用窗口函数（推荐，支持MySQL 8.0+、PostgreSQL等）

利用LEAD窗口函数获取用户下一次访问的房间和时间，精准判断房间切换节点：

WITH ranked_chat AS (
    -- 给每个用户的聊天记录按时间排序，获取下一条记录的时间和房间ID
    SELECT 
        userId,
        roomId,
        first_seen,
        LEAD(first_seen) OVER (PARTITION BY userId ORDER BY first_seen) AS next_seen,
        LEAD(roomId) OVER (PARTITION BY userId ORDER BY first_seen) AS next_roomId
    FROM chat_data
),
room_visit_temp AS (
    -- 计算每个房间的首次访问时间，以及切换房间时的末次时间
    SELECT 
        userId,
        roomId,
        MIN(first_seen) AS first_seen,
        CASE 
            -- 下一个房间不同，或是最后一条记录，取下一次访问时间作为末次时间
            WHEN next_roomId != roomId OR next_roomId IS NULL THEN next_seen
            ELSE NULL
        END AS last_seen
    FROM ranked_chat
    GROUP BY userId, roomId, next_seen, next_roomId
)
-- 合并同一用户同一房间的记录，插入目标表
INSERT INTO user_room_visit (userId, roomId, first_seen, last_seen)
SELECT 
    userId,
    roomId,
    MIN(first_seen),
    MAX(last_seen)
FROM room_visit_temp
GROUP BY userId, roomId;

方式二：使用关联查询（兼容老版本数据库）

如果你的数据库不支持窗口函数，可通过自关联找到用户切换房间的最早时间：

INSERT INTO user_room_visit (userId, roomId, first_seen, last_seen)
SELECT 
    cd1.userId,
    cd1.roomId,
    MIN(cd1.first_seen) AS first_seen,
    -- 找到当前房间之后，用户第一次切换到其他房间的时间
    MIN(cd2.first_seen) AS last_seen
FROM chat_data cd1
LEFT JOIN chat_data cd2 
    ON cd1.userId = cd2.userId 
    AND cd2.first_seen > cd1.first_seen
    AND cd2.roomId != cd1.roomId
GROUP BY cd1.userId, cd1.roomId;

结果验证 #

针对你提供的测试数据，执行后user_room_visit表会生成以下记录：

（注：房间2的last_seen为NULL是因为没有后续切换房间的记录，你可根据需求调整为当前时间戳或其他默认值）

内容的提问来源于stack exchange，提问作者Ders Çalışma zamanı