SQL多表关联需求：将activity表每行匹配window表中符合时间范围的最早窗口行

我来帮你解决这个问题，你的核心需求是把activity表的每一行匹配到window表中时间戳落在窗口区间内且起始时间最早的唯一一行。先分析下你当前查询的问题，再给出两种可行的解决方案。

你的查询为什么不对？ #

你当前的JOIN条件只匹配了ISBN，但子查询返回的是符合条件窗口的ISBN（而不是唯一的窗口行），这会导致所有同ISBN的窗口都和activity行进行关联——比如你的例子里每个activity行都会和4个window行连接，最终得到2×4=8行结果，这显然不符合预期。

解决方案1：使用窗口函数（推荐） #

我们可以先把所有符合条件的activity和window关联对找出来，然后用ROW_NUMBER()窗口函数对每个activity行的匹配窗口按start排序，只保留排序第一的（也就是起始时间最早的窗口）：

SELECT 
    w.start, 
    w.end, 
    w.isbn, 
    a.timestamp
FROM test.activity a
JOIN (
    SELECT 
        w_inner.*,
        a_inner.timestamp AS activity_ts,
        -- 按ISBN和activity时间戳分组，窗口start从小到大排序
        ROW_NUMBER() OVER (
            PARTITION BY w_inner.isbn, a_inner.timestamp 
            ORDER BY w_inner.start ASC
        ) AS rn
    FROM test.window w_inner
    JOIN test.activity a_inner 
        ON w_inner.isbn = a_inner.isbn 
        AND a_inner.timestamp BETWEEN w_inner.start AND w_inner.end
) w 
    ON w.isbn = a.isbn 
    AND w.activity_ts = a.timestamp 
    AND w.rn = 1;

解决方案2：使用子查询获取最小起始窗口 #

另一种思路是，对每个activity行，先找到符合条件的窗口中最小的start值，再关联到对应的窗口行：

SELECT 
    w.start, 
    w.end, 
    w.isbn, 
    a.timestamp
FROM test.activity a
JOIN test.window w 
    ON w.isbn = a.isbn 
    -- 确保时间戳落在窗口区间内
    AND a.timestamp BETWEEN w.start AND w.end
    -- 匹配起始时间最小的窗口
    AND w.start = (
        SELECT MIN(w2.start)
        FROM test.window w2
        WHERE w2.isbn = a.isbn
            AND a.timestamp BETWEEN w2.start AND w2.end
    );

验证结果 #

这两种方案都会返回你期望的结果：

两种方案各有优劣：窗口函数的方式在数据量较大时通常性能更稳定；子查询的方式逻辑更直观，适合理解基础关联逻辑。

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