基于时间戳将两个sqlite表拼凑在一起(带容差)

Question

给定来自两个不同现实生活来源的两个sqlite信息表(带有时间戳)。我正试图根据最接近的时间戳将TABLE_A和TABLE_B缝合在一起。我希望在某种程度上做到这一点，以便以后的查询(联接)"快速"。

我的总体想法是两个步骤：

1) 查找两个表中都存在的时间戳，并使用该时间戳更新引用列(TABLE_a.ref_timestamp)。

2) 如果TABLE_B中不存在TABLE_A.timestamp，则查找下一个最接近的时间，并将该参考时间写入TABLE_A.ref_timestamp

• 这两个表都有一个unix epoch时间戳作为列

我要做的是(在伪SQLite中)：

1) 使用TABLE_A.timestamp更新TABLE_A.ref_timestamp，其中timestamp=？如果时间戳存在于TABLE_B.timestamp 中

• 显然这不起作用，因为"IF"在SQLite中不存在

2)更新TABLE_A.ref_timestamp其中时间戳=？WITH(从表_B中选择，其中日期时间介于？+公差和？-公差顺序由ABS(？-日期时间)限制1)

• 这里的想法是用BETWEEN做一个粗略的切割，并在该范围内选择最接近的一个。如果没有发现任何内容，NULL也可以
• 由于缺少"WITH"，这将不起作用
• 公差将作为配置给出

我的解决方案要么不起作用，要么一直受到语法错误的困扰，我想知道SO中是否有人遇到过这种情况并有一些见解。

编辑示例：

TABLE_A:
(timestamp,ref_timestamp)
123.0,NULL
124.0,NULL
125.0,NULL
8000.0,NULL
TABLE_B
(timestamp)
122.5
124.0
125.0

运行"zipling"TABLE_A后将包含以下内容：

TABLE_A:
123.0,122.5
124.0,124.0
125.0,125.0
8000.0,NULL

感谢CL提供的解决方案。ABS函数中缺少一个"table_b."：

UPDATE table_a
SET ref_timestamp = (SELECT timestamp
FROM (SELECT timestamp,
abs(table_b.timestamp - table_a.timestamp) AS diff
FROM table_b
WHERE timestamp BETWEEN table_a.timestamp - 42 AND table_a.timestamp + 42)
ORDER BY diff
LIMIT 1);

Answer 1

WITH自SQLite 3.8.3起可用。

无论如何，这可以通过使用正确的SQL语法和相关的子查询来完成：

UPDATE table_a
SET ref_timestamp = (SELECT timestamp
FROM (SELECT timestamp,
abs(timestamp - table_a.timestamp) AS diff
FROM table_b
WHERE timestamp BETWEEN table_a.timestamp - 42 AND table_a.timestamp + 42)
ORDER BY diff
LIMIT 1);