使用SQL递归查询匹配人员移动记录与事件发生位置的求助

SQL实现事件位置匹配方案 #

核心逻辑 #

该需求无需使用递归查询，通过构造移动位置的有效时间区间即可完成匹配：每个人员的某条移动记录对应的位置，生效时间为本次移动时间，失效时间为下一次移动时间，事件发生时间落在哪个生效区间内，对应的位置就是事件发生时的人员位置。

可直接运行的SQL示例（支持MySQL 8.0+/PostgreSQL/SQL Server等主流支持窗口函数的数据库） #

WITH movement_valid_range AS (
    SELECT
        person_id, -- 请替换为你的movement表中实际的人员ID字段名
        location, -- 请替换为你的movement表中实际的地点字段名
        move_time AS valid_start,
        -- 取同人员下一次移动时间为当前位置失效时间，无下一次则默认到9999-12-31覆盖后续所有事件
        LEAD(move_time, 1, '9999-12-31') OVER (PARTITION BY person_id ORDER BY move_time) AS valid_end
    FROM movement
)
SELECT
    e.event_id, -- 请替换为你的event表中实际的事件ID字段名
    e.person_id,
    e.event_time,
    m.location AS event_location
FROM event e
LEFT JOIN movement_valid_range m
    ON e.person_id = m.person_id
    AND e.event_time >= m.valid_start
    AND e.event_time < m.valid_end;

补充说明 #

1. 你添加的movement rank字段如果是按人员分组、按移动时间升序排序得到的序号，可直接用该字段代替窗口函数中的排序逻辑，降低计算开销
2. 如果你的数据库版本不支持窗口函数，可通过自连接+子查询的方式实现相同的时间区间构造逻辑
3. 若需处理事件发生在人员第一次移动之前的场景，可自行在关联逻辑中补充默认位置规则

参考表结构与输出示例

• movement表结构：
• event表结构：
• 最终输出效果参考：

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