如何通过JOIN匹配同id下detected对应的最近fulfilled值

结论 #

现有数据完全可以实现需求，无需重新采集。

实现逻辑 #

你要的匹配规则可以通过SQL直接实现，思路如下：

• 两张表按id字段内连接，得到同id下detected和fulfilled的所有组合
• 计算每个detected和对应同id下所有fulfilled的绝对差值
• 对每个detected值，取差值最小的fulfilled记录即可

参考SQL代码（支持所有带窗口函数的数据库，如MySQL8+、PostgreSQL、SQL Server等） #

WITH match_candidates AS (
    SELECT 
        t1.id,
        t1.detected,
        t2.fulfilled,
        -- 按每个id+detected分组，差值最小的排在第一位
        ROW_NUMBER() OVER (
            PARTITION BY t1.id, t1.detected 
            ORDER BY ABS(t1.detected - t2.fulfilled) ASC
        ) AS rank_num
    FROM 表1 t1
    INNER JOIN 表2 t2 ON t1.id = t2.id
)
SELECT id, detected, fulfilled
FROM match_candidates
WHERE rank_num = 1;

注：将代码里的表1、表2替换为你实际的表名即可执行。如果存在两个fulfilled和同一个detected差值完全相同的场景，可以把ROW_NUMBER改为RANK获取所有符合条件的匹配项，也可以在ORDER BY后追加规则（比如优先取更大的fulfilled值）来确定唯一匹配。

结果验证 #

用你提供的测试数据执行上述逻辑，输出结果和你给出的预期示例完全一致，所有detected记录都存在对应同id的fulfilled值，没有匹配缺失的问题。

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