Home > Enterprise >  SQL - Comparing dates efficiently for specific IDs
SQL - Comparing dates efficiently for specific IDs

Time:09-30

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
  • Related