自定义加入条件。左加入 ID 和最近的小于日期。SQL 或 Python 解决方案

我正在尝试加入 2 个表。表 1 包含 Id、日期列和值列。表 2 还有 Id、日期列和不同的值列。

我需要通过相同的记录 ID 和表 1 中最接近的小于表 2 中日期的日期将表 1(如左图)与表 2(右图)连接起来。因此,连接将首先匹配 ID,然后匹配它将为表 2 中的日期查找表 1 中最接近的小于日期。因此,如果表 1 中没有小于表 2 的相应 ID 的日期,则不会添加表 2 的值,因为ID。

我想显示表 1 中的所有记录,并且只显示表 2 中的值列。因此,如果表 1 中有 100,000 行,那么我希望最终的联接表也有 100,000 行。

这是我希望实现的简化版本和示例。在简化示例中,表 2 中只有 1 个值列,但我有 15 个。

下面是创建虚拟表的 SQL 代码:

drop table if exists table1;

drop table if exists table2;


create table table1 (id text, date date, val int);

create table table2 (id text, date date, val int);


insert into table1 (id, date, val)

values ('sn1', '2010/01/26', 10),

       ('sn1', '2010/01/25', 9),

       ('sn1', '2010/01/21', 8),

       ('sn2', '2010/01/23', 9),

       ('sn2', '2010/01/22', 7),

       ('sn1', '2010/01/19', 10);

insert into table1 (id, date, val) values ('sn2', '2010/01/18', 11);

select * from table1 order by 1,2;


insert into table2 (id, date, val) values ('sn1', '2010/01/26', 20);

insert into table2 (id, date, val) values ('sn2', '2010/01/23', 99);

insert into table2 (id, date, val) values ('sn2', '2010/01/17', 50);

insert into table2 (id, date, val) values ('sn2', '2010/01/21', 60);

insert into table2 (id, date, val) values ('sn1', '2010/01/20', 0);

select * from table2 order by 1,2;


下面显示了所需的结果。它具有表 1 中的所有列和行以及表 2 中的最后一列。


+------+------------+------+------+

| id   | date       | val1 | val2 |

+------+------------+------+------+

| sn1  | 2010-01-19 |   10 |    0 |

| sn1  | 2010-01-21 |    8 |      |

| sn1  | 2010-01-25 |    9 |      |

| sn1  | 2010-01-26 |   10 |   20 |

| sn2  | 2010-01-18 |   11 |   60 |

| sn2  | 2010-01-22 |    7 |      |

| sn2  | 2010-01-23 |    9 |   99 |

+------+------------+------+------+

任何帮助将不胜感激,无论是 SQL(首选)还是 Python。


Helenr
浏览 122回答 1
1回答

潇湘沐

SELECT t1.id, t1.`date`, t1.val val1, t2.val val2FROM table1 t1LEFT JOIN table2 t2 ON t2.`date` >= t1.`date`                    AND t2.id = t1.id                   AND NOT EXISTS (SELECT NULL                                   FROM table1 t3                                   WHERE t3.`date` > t1.`date`                                     AND t2.`date` >= t3.`date`                                     AND t3.id = t1.id)ORDER BY id, `date`;或者WITH cte1 AS (SELECT id, `date`, val val1, NULL val2         FROM table1        UNION ALL         SELECT id, `date`, NULL val1, val val2         FROM table2),cte2 AS (SELECT id,                 `date`,                 val1,                 CASE WHEN val2 IS NULL                     THEN LEAD(val2) OVER (PARTITION BY id ORDER BY `date`, val2)                     ELSE val2                     END val2         FROM cte1)SELECT *FROM cte2WHERE val1 IS NOT NULLORDER BY id, `date`;小提琴
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

Python