在 PostgreSQL 中高效地按最接近日期合并两个数据集

Question

我会尝试将两个时间分辨率不同的表格合并到它们最近的日期。

这些表格如下：

表格1：

id    | date    | device  | value1
----------------------------------
1     | 10:22   | 13      | 0.53
2     | 10:24   | 13      | 0.67
3     | 10:25   | 14      | 0.83
4     | 10:25   | 13      | 0.32

表2：

id    | date    | device  | value2
----------------------------------
22    | 10:18   | 13      | 0.77
23    | 10:21   | 14      | 0.53
24    | 10:23   | 13      | 0.67
25    | 10:28   | 14      | 0.83
26    | 10:31   | 13      | 0.23

我希望将这些表格与第一个表格合并。因此，我想将value2添加到Table1中，其中对于每个设备，最新的value2出现。

结果：

id    | date    | device  | value1 | value2
-------------------------------------------
1     | 10:22   | 13      | 0.53   | 0.77
2     | 10:24   | 13      | 0.67   | 0.67
3     | 10:25   | 14      | 0.83   | 0.53
4     | 10:25   | 13      | 0.32   | 0.67

我有大约20-30个设备，Table2中有数千行数据（=m），Table1中有数百万条数据（=n）。

我可以按日期对所有表进行排序（O(n*logn)），将它们写入文本文件，像合并一样遍历Table1，同时从Table2中拉取数据，直到数据更新为止（我需要管理每个设备的约20-30个指向最新数据的指针，但不超过这个范围）。在合并后，我可以将其重新上传到数据库。然后复杂度是O(n * log(n))用于排序和O(n+m)用于遍历表格。

但是最好还是在数据库中完成。但是我能够实现的最佳查询的复杂度为O(n^2)：

SELECT DISTINCT ON (Table1.id)
       Table1.id, Table1.date, Table1.device, Table1.value1, Table2.value2
FROM Table1, Table2
WHERE Table1.date > Table2.date and Table1.device = Table2.device
ORDER BY Table1.id, Table1.date-Table2.date;

我需要处理的数据量非常大，这个方法很慢，有更好的方法吗？或者只能使用已下载的数据进行处理？

- hunyadym

---

可能是重复的问题：SQL查询以最接近的时间戳为基础连接两个表 - Kromster

Answer 1

您的查询可以重写为：

SELECT DISTINCT ON (t1.id)
       t1.id, t1.date, t1.device, t1.value1, t2.value2
FROM   table1 t1
JOIN   table2 t2 USING (device)
WHERE  t1.date > t2.date
ORDER  BY t1.id, <b>t2.date DESC</b>;

不需要为每一行组合计算日期差（这样耗费大且不sargable），只需从每个集合中选择具有最大t2.date的行。建议使用索引支持。 DISTINCT ON的详细信息：

• 选择每个GROUP BY组中的第一行？

这可能还不够快。根据您的数据分布，您需要进行松散索引扫描，可以通过相关子查询（如Gordon的查询）或更现代、多功能的JOIN LATERAL来模拟。

SELECT t1.id, t1.date, t1.device, t1.value1, t2.value2
FROM   table1 t1
LEFT   JOIN LATERAL (
   SELECT value2
   FROM   table2
   WHERE  device = t1.device
   AND    date   < t1.date
   ORDER  BY date DESC
   LIMIT  1
   ) t2 ON TRUE;

LEFT JOIN避免了在t2中找不到匹配项时丢失行。详情：

• 优化GROUP BY查询以检索每个用户的最新行

但是，由于您在"Table2中有数千行，在Table1中有数百万行"，因此仍然不太快。

两个想法可能更快，但也更复杂：

1.UNION ALL加上窗口函数

将Table1和Table2组合在一个UNION ALL查询中，并在派生表上运行窗口函数。这通过Postgres 9.4或更高版本中的"移动聚合支持"得到增强。

SELECT id, date, device, value1, value2
FROM  (
   SELECT id, date, device, value1
        , min(value2) OVER (PARTITION BY device, grp) AS value2
   FROM  (
      SELECT *
           , count(value2) OVER (PARTITION BY device ORDER BY date) AS grp
      FROM  (
         SELECT id, date, device, value1, NULL::numeric AS value2 
         FROM   table1

         UNION  ALL
         SELECT id, date, device, NULL::numeric AS value1, value2
         FROM   table2
         ) s1
      ) s2
   ) s3
WHERE  value1 IS NOT NULL
ORDER  BY date, id;

需要测试是否能够竞争。足够的work_mem可以在内存中进行排序。

db<>fiddle here 包含三个查询。
_{旧版 sqlfiddle}

2. PL/pgSQL函数

为Table2中每个设备创建游标，在循环遍历Table1时，选择相应设备游标的值，并前进直到cursor.date > t1.date，并保留前一行的value2。与此处的获胜实现类似:

• 窗口函数或公共表达式：在范围内计算前面的行数

可能是最快的方法，但需要编写更多的代码。

- Erwin Brandstetter

---

你真是干得漂亮！你应该得到最佳答案。 - AliWieckowicz

Answer 2

因为表1要小得多，所以使用相关子查询可能更有效：

select t1.*,
       (select t2.value2
        from table2 t2
        where t2.device = t.device and t2.date <= t1.date
        order by t2.date desc
        limit 1
       ) as value2
from table1 t1;

为了提高性能，还需在table2(device, date, value2)上创建索引。

- Gordon Linoff

---

抱歉，我写错了：Table2是较小的表格，Table1是较大的表格，因此Table2中的值将在结果表中出现多次（如示例所示）。我已经相应地编辑了这个问题。 - hunyadym

@hunyadym...相同的想法应该成立。table1的索引应该很容易适合内存，而table2中的每一行将需要扫描一下这个索引。 - Gordon Linoff

太棒了，我试过了，速度快多了。谢谢！ - hunyadym