基于事件时序/时间戳关联数据表的SQL实现求助

需求回顾 #

我们需要关联Login和Create_Page表，为每个Login记录找到**同一个用户下、时间晚于它、且最接近（时间差最小）**的Create Page记录，同时要求两者时间差不超过10小时。

核心思路 #

1. 筛选有效关联对：先保留UserID一致、Create Page时间晚于Login、且时间差≤10小时的记录，排除不符合条件的无效匹配。
2. 锁定最近匹配项：使用窗口函数ROW_NUMBER()，按每个Login记录分组，对候选的Create Page按时间差从小到大排序，取排序后的第一条（即最接近的匹配）。

SQL语句（以MySQL为例） #

SELECT UserID, XYZ, ABC
FROM (
    SELECT
        l.UserID,
        cp.XYZ,
        l.ABC,
        -- 按用户+Login时间分组，按时间差升序分配行号
        ROW_NUMBER() OVER (
            PARTITION BY l.UserID, l.Timestamp
            ORDER BY TIMESTAMPDIFF(MINUTE, l.Timestamp, cp.Timestamp) ASC
        ) AS rn
    FROM Login l
    JOIN Create_Page cp 
        ON l.UserID = cp.UserID
        AND cp.Timestamp > l.Timestamp
        -- 过滤时间差超过10小时的记录
        AND TIMESTAMPDIFF(HOUR, l.Timestamp, cp.Timestamp) <= 10
) AS temp
-- 仅保留每个Login对应的最近Create Page
WHERE rn = 1;

语句细节解释 #

• 关联过滤：在JOIN阶段直接过滤掉不符合条件的记录，减少后续计算量，避免无效数据干扰结果。
• 窗口函数ROW_NUMBER()：
  • PARTITION BY l.UserID, l.Timestamp：将数据按用户和Login时间拆分成独立分组，每个分组对应一条Login记录。
  • ORDER BY TIMESTAMPDIFF(MINUTE, ...) ASC：在每个分组内，按Create Page与Login的时间差从小到大排序，时间差最小的记录会被标记为行号1。
• 外层筛选：只保留行号为1的记录，最终得到每个Login对应的最近符合条件的Create Page。

其他数据库适配调整 #

• PostgreSQL：替换时间差计算函数为：

  EXTRACT(EPOCH FROM (cp.Timestamp - l.Timestamp)) / 60 -- 计算分钟级时间差
  

• SQL Server：替换时间差计算函数为：

  DATEDIFF(MINUTE, l.Timestamp, cp.Timestamp)
  

结果验证 #

运行上述语句后，得到的结果与你提供的期望结果一致（仅顺序可通过ORDER BY调整）：

UserID | XYZ | ABC 
1      | xe  | ad 
1      | xh  | ab 
2      | xc  | af 
2      | xb  | ag 

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