I have two DB's that look like this:
Table 1
ID | time1 |
---|---|
1 | 21.3.2000, 16:00 |
1 | 21.3.2000, 17:00 |
... | ... |
2 | 21.3.2000, 16:00 |
... | ... |
Table 2
ID | time2 |
---|---|
1 | 21.3.2000, 16:30 |
1 | 21.3.2000, 18:00 |
... | ... |
2 | 21.3.2000, 15:30 |
... | ... |
where for each ID
I have several dates time1
and time2
.
For each time1
I want the closest time2
which is also not greater than time1
.
I tried that but even building the carthesian product like this:
SELECT h."ID", h."time", m."time"
FROM Table1 as h, Table2 as m
WHERE h."ID" = m."ID"
loads for a very long time because of the huge amount of data, is there any way I can improve the speed and get the same result?
CodePudding user response:
You mentioned that time2 could be greater than or equal to time1... that simplifies the logic. I would try a lateral join:
SELECT *
FROM table1
LEFT JOIN LATERAL (
SELECT *
FROM table2
WHERE table2.ID = table1.ID AND table2.time2 >= table1.time1
ORDER BY table2.time2
LIMIT 1
) AS table2_new ON true