sql - 加入 2 个表并仅保留第一个最近的事件

Question

我有以下当前表格：

table_1
id | timestamp | origin | info

table_2
id | timestamp | origin | type

我的目标是为表 2 中的每一行找到表 1 中的原始事件。我只想保留第一个事件。例如：

table 1
1 | 1000 | "o1" | "i1"
2 | 2000 | "o2" | "i2"
3 | 2010 | "o2" | "i2"

table 2
1 | 1010 | "o1" | "t1"
2 | 2100 | "o2" | "t2"

我的预期结果是：

table_2.id | table_2.timestamp | table_2.origin | table_2.type | table_1.info | table_1.timestamp
1          | 1010              | "o1"           | "t1"         | "i1"         | 1000
2          | 2100              | "o2"           | "t2"         | "i2"         | 2010

目前我只是使用一个简单的连接origin，table_2.timestamp > table_1.timestamp它给了我：

table_2.id | table_2.timestamp | table_2.origin | table_2.type | table_1.info | table_1.timestamp
1          | 1010              | "o1"           | "t1"         | "i1"         | 1000
2          | 2100              | "o2"           | "t2"         | "i2"         | 2000
2          | 2100              | "o2"           | "t2"         | "i2"         | 2010

如您所见，我不想要上面的第二行，因为我只想要 table_1 中的第一个最接近的事件。

有任何想法吗？

标签： sqlpostgresqljoingreatest-n-per-group

Answer 1

一个跨数据库的解决方案是用一个相关的子查询加入和过滤：

select 
    t2.*,
    t1.info,
    t1.timestamp t1_timestamp
from 
    table_2 t2
    inner join table_1 t1
        on t1.origin = t2.origin
        and t1.timestamp = (
            select max(t11.timestamp) 
            from table_1 t11
            where t11.origin = t2.origin and t11.timestamp < t2.timestamp
        )
order by t2.id

由于您使用的是 Postgres，因此您可以使用方便的语法distinct on；这实际上可能表现更好：

select 
    distinct on(t2.id)
    t2.*,
    t1.info,
    t1.timestamp t1_timestamp
from 
    table_2 t2
    inner join table_1 t1 
        on t1.origin = t2.origin and t1.timestamp < t2.timestamp
order by t2.id, t1.timestamp desc

DB Fiddle 上的演示- 两个查询都产生：

编号 | 时间戳 | 产地 | 类型 | 信息 | t1_timestamp
-: | --------: | :----- | :--- | :--- | ------------:
 1 | 1010 | o1 | t1 | i1 | 1000
 2 | 2100 | o2 | t2 | i2 | 2010