SQL实现时间戳模糊JOIN：60分钟内匹配并选最近时间

现有数据表 #

表a（降水量数据） #

表b（地点时间数据） #

关联规则 #

• 表b的每一行需与表a中时间戳差值在60分钟以内的行关联，无符合条件的匹配则丢弃该行
• 若表b某行可匹配表a多行，仅保留时间戳最接近的那一行

预期输出结果 #

SQL实现示例 #

以下以PostgreSQL语法为例，通过窗口函数筛选最接近的匹配行：

WITH ranked_matches AS (
    SELECT
        a.timestamp,
        b.loc,
        a.precipitation,
        -- 计算时间差（分钟）
        ABS(EXTRACT(EPOCH FROM a.timestamp - b.timestamp) / 60) AS time_diff_mins,
        -- 按表b时间分组，按时间差排序取第一行
        ROW_NUMBER() OVER (PARTITION BY b.timestamp ORDER BY ABS(a.timestamp - b.timestamp)) AS rank
    FROM 表a a
    JOIN 表b b 
        ON ABS(EXTRACT(EPOCH FROM a.timestamp - b.timestamp) / 60) <= 60
)
SELECT timestamp, loc, precipitation
FROM ranked_matches
WHERE rank = 1;

代码说明 #

1. 先通过JOIN筛选出两张表中时间差在60分钟内的所有可能匹配
2. 使用ROW_NUMBER()窗口函数，以表b的时间戳为分组依据，按时间差绝对值从小到大排序
3. 最后筛选出每组中排名第一的记录，即为表b每行对应的最接近匹配行

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