Oracle SQL - left join record with closest datetime-CodePudding

I have 2 tables.

table1:

item	end time
1	2022-11-23 08:12:00
1	2022-11-23 09:12:00
2	2022-11-22 13:12:00
3	2022-11-22 14:12:00

table2:

item	value	last_dt
1	11	2022-11-23 09:12:00
1	12	2022-11-23 08:30:00
1	13	2022-11-24 08:30:00
2	21	2022-11-22 13:12:00
3	31	2022-11-22 14:12:00
3	32	2022-11-22 14:30:00

i would like to left join table1 to table2 by comparing the table1's end_time with table2's last_dt.

below is the expected result.

item	end time	value
1	2022-11-23 08:12:00	12
1	2022-11-23 09:12:00	11
2	2022-11-22 13:12:00	21
3	2022-11-22 14:12:00	31

CodePudding user response：

First of all.

Too hard to work with under-link examples.

-- ms-sql-syntax
create table table1(item# int, end_time datetime);
insert table1 select 1, '2022-11-23T08:12:00';
insert table1 select 2, '2022-11-22T13:12:00';
insert table1 select 3, '2022-11-22T14:12:00';

create table table2 (item# int, value int, end_time datetime);
insert table2 select 1, 11, '2022-11-23T09:12:00';
insert table2 select 1, 12, '2022-11-23T08:30:00';
insert table2 select 1, 13, '2022-11-24T08:30:00';
insert table2 select 2, 21, '2022-11-22T13:12:00';
insert table2 select 3, 31, '2022-11-22T14:12:00';
insert table2 select 3, 32, '2022-11-22T14:30:00';

expected result

item# end_time            value
1     2022-11-23 08:12:00 12
2     2022-11-22 13:12:00 21
3     2022-11-22 14:12:00 31

Second

You do not need "left join". You need "outer apply" or "cross apply" https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#cross-apply-join

It should be something like this:

-- it should be oracle syntax. not sure
SELECT
    t1.item#, t1.end_time, t2.value
FROM table1 AS t1
CROSS APPLY (
    SELECT value
    FROM table2 AS t2ca
    WHERE rownum = 1
    ORDER BY ABS(@DATEDIFF ('SS', t2ca.end_time, t1.end_time))
) AS t2

-- ms-sql-syntax. exactly
SELECT
    t1.item#, t1.end_time, t2.value
FROM table1 AS t1
CROSS APPLY (
    SELECT top 1 value
    FROM table2 AS t2ca
    ORDER BY ABS(DATEDIFF (second, t2ca.end_time, t1.end_time))
) AS t2

CodePudding user response：

You may use lateral join with fetch first row only to select the closest value per row. But it effectively will perform a nested loop (which would be fast in case of index on item, last_dt and small table1).

select *
from table1
  left join lateral (
    select value
    from table2
    where table2.item = table1.item
    order by abs(table2.last_dt - table1.end_time) asc
    fetch first row only
  ) val
  on 1 = 1

Alternatively you may use first aggregate function and order by time difference. It would work in old Oracle versions (at least from 10g) also.

select
  table1.item,
  table1.end_time,
  max(table2.value) keep(dense_rank first order by abs(table2.last_dt - table1.end_time)) as value
from table1
  left join table2
  on table2.item = table1.item
group by
  table1.item,
  table1.end_time

For your sample data both will return this result:

ITEM	END_TIME	VALUE
1	2022-11-23 08:12:00	12
2	2022-11-22 13:12:00	21
3	2022-11-22 14:12:00	31

db<>fiddle