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

Question

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

我需要将表1（如左图所示）与表2（如右图所示）通过相同的记录Id和表1中最接近的小于表2中日期的日期连接起来。因此，联接将首先匹配ID，然后在表1中查找表2中最接近的小于日期的日期。因此，如果表1中没有日期小于表2对应ID的日期，那么表2的值将不会被添加到该ID中

我想显示表1中的所有记录，只显示表2中的值列。所以，如果我在表1中有100000行，那么我希望我的最终联接表也有100000行

下面是我希望实现的一个简化版本和示例。在简化的示例中，表2中只有1个value列，但我有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

多谢各位

Answer 1

SELECT t1.id, t1.`date`, t1.val val1, t2.val val2
FROM table1 t1
LEFT 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 cte2
WHERE val1 IS NOT NULL
ORDER BY id, `date`;

fiddle