根据附近的时间戳联接两个表 [英] Join two tables based on nearby timestamps

问题描述

表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是第二步.

I want to join these two tables (using left join) with nearby timestamps. table1 is first step and table2 is second step in production process.

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

In my desired table dateee(from table1) and timee(from table2) should be very nearby. I want to correlate sn1 and id based on the nearby timestamp.

推荐答案

附近"非常模糊.
要加入table2所在的table2，将来在dateee的10秒内:

"Nearby" is rather fuzzy.
To join table2 where timee lies within 10 seconds of dateee in the future:

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

• LEFT JOIN保留结果中table1中的行，即使在table2中不匹配也是如此.

  • The LEFT JOIN keeps rows from table1 in the result, even without match in table2.

    可以有多个匹配项，因此基表中的每一行都可以以不同的组合多次返回.

    There can be multiple matches, so that each row from the base tables can be returned multiple times in various combinations.

    使用下一个较高的时间戳将table1中的每一行连接到该行. 恰好在table1上每行结果中有一行:

    To join each rows in table1 to the row with with the next higher timestamp. Exactly one row in the result per row on 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)上的索引对于性能至关重要.

    An index on (timee) is essential for performance.

    这篇关于根据附近的时间戳联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！