sql - 两个表中的 SQL 内连接只匹配一次

Question

我正在努力寻找一种有效的方法来自我内部加入一个有 1m 行的表。

t1带有别名&的表 t t2：

Timestamp col1 col2

我想做一个基于时间戳和 col1 的内部连接只匹配一次：

1. 如果有很多匹配项，则从右表返回第一个匹配项
2. AND 匹配的列被标记，不能用于下一个匹配

在伪代码中，

for v in t1.col1:
   if t1.timestamp - t2.timestamp < 5 mins AND t2.mark == False AND t1.col1 = t2.col1:
       t2.mark = true <- don't know how to do this in SQL
       return t1.timestamp, t1.col1, t1.col2, t2.timestamp, t2.col2

我已经成功实现

1. 如果有很多匹配项，则从右表返回第一个匹配项

但不是 2. AND 匹配的列被标记，不能用于下一个匹配

使用inner join+ group by。

换句话说，有 t2 行在结果时间内出现了多次（期望只有一个）

样本输入数据：

Timestamp                    name price
2020-11-16 01:07:38.147501   A    1.0
2020-11-16 01:07:40.294957   A    1.2

样本输出数据：

open_time                  close_time              name open_price close_price
2020-11-16 01:07:38.147501 2020-11-16 01:53:17.690113 A 1.0         1.5

1. 在 open_time 和 close_time 列中，它们在其列中是唯一的。
2. 任何行的 open_time 和 close_time 之间没有重叠。也就是说，对于第 2 行的 open_time，它必须晚于第 1 行的 close_time。

可能这与光标有关。

---

为了简化问题，请考虑我有以下记录：

 open_time                  close_time              name open_price close_price
2020-11-16 01:07:38.147501,2020-11-16 01:53:17.690113, a, 1
2020-11-16 01:07:38.147501,2020-11-16 01:53:03.217847, a, 1.1
2020-11-16 01:07:40.294957,2020-11-16 01:53:17.690113, a, 1.2
2020-11-16 01:07:40.294957,2020-11-16 01:53:03.217847, a, 1.3

如何使所有记录的 open_time 和 close_time 之间没有重叠？

标签： sqlpostgresql

Answer 1

我将问题解释为您在两个表中有重复项并希望将它们连接起来，因此只有一个重复的行匹配。

您可以通过枚举每个表中的行来解决此问题：

select . . .
from (select t1.*,
             row_number() over (partition by col, timestamp order by timestamp) as seqnum
      from t1
     ) t1 join
     (select t2.*,
             row_number() over (partition by col, timestamp order by timestamp) as seqnum
      from t2
     ) t2
     on t1.col = t2.col and
        t1.timestamp = t2.timestamp and
        t1.seqnum = t2.seqnum