sql - 根据附近的时间戳连接两个表

Question

表 1(1422 行)

 sn1     |       dateee        | shift | linee
---------+---------------------+-------+-------
 8419404 | 2015-02-27 09:45:50 | D     |     2
 8419383 | 2015-02-27 09:46:10 | D     |     2
 8419410 | 2015-02-27 09:46:40 | D     |     2
 8419385 | 2015-02-27 09:50:40 | D     |     2
 8419412 | 2015-02-27 09:50:50 | D     |     2
 8419390 | 2015-02-27 09:51:30 | D     |     2
 8419414 | 2015-02-27 09:52:00 | D     |     2
 8419387 | 2015-02-27 09:52:20 | D     |     2
 8419416 | 2015-02-27 09:52:50 | D     |     2
 8419394 | 2015-02-27 09:57:10 | D     |     2

表 2(824 行)

id    | id2 |        timee
------+-----+---------------------
 1302 |     | 2015-02-27 09:46:11
 1303 |     | 2015-02-27 09:46:36
 1304 |     | 2015-02-27 09:50:37
 1305 |     | 2015-02-27 09:51:06
 1306 |     | 2015-02-27 09:51:31
 1307 |     | 2015-02-27 09:51:55
 1308 |     | 2015-02-27 09:52:20
 1309 |     | 2015-02-27 09:52:45
 1310 |     | 2015-02-27 09:57:05

我想用附近的时间戳连接这两个表(使用左连接)。table1 是生产过程中的第一步，table2 是第二步。

在我想要的表中，dateee(from table1) 和 timee(from table2) 应该就在附近。我想根据附近的时间戳关联 sn1 和 id。

Answer 1

“附近”比较模糊。
要加入 table2，其中 timee 位于 dateee future 10 秒内:

SELECT *
FROM   table1 t1
LEFT   JOIN table2 t2 ON t2.timee BETWEEN t1.dateee
                                      AND t1.dateee + interval '10 sec';

• LEFT JOIN 将 table1 中的行保留在结果中，即使 table2 中没有匹配项也是如此。

• 可以有多个匹配项，因此基表中的每一行都可以以各种组合多次返回。

备选

将 table1 中的每一行连接到具有下一个更高时间戳的行。 恰好table1 上每行结果中的一行:

SELECT *
FROM   table1 t1
LEFT   JOIN LATERAL (
   SELECT *
   FROM   table2 t2
   WHERE  t2.timee >= t1.dateee
   ORDER  BY t2.timee
   LIMIT  1
   ) ON TRUE;

(timee) 上的索引对于性能至关重要。