Is there any equivalent date function in oracle for python pd.merge_asof? Please see the following example:

Table A1

ID      Date
1       12/02/2020
2       11/23/2019
3       09/09/2021
3       10/12/2021

Table A2

ID      Date
3       09/12/2021

For ID = 3, the date is 09/12/2021 in A2 table. When I try to match this ID and date with A1, only ID is matching. So I try to add a logic to get the following output (since it is the closest date).

Output

ID       Date        ID2      Date2
3     09/09/2021      3     09/12/2021

CodePudding user response：

You could do something like this (join aggregate):

with
  t1 (id, datum) as (
    select 1, date '2020-12-02' from dual union all
    select 2, date '2019-11-23' from dual union all
    select 3, date '2021-09-09' from dual union all
    select 3, date '2021-10-12' from dual
  )
, t2 (id, datum) as (
    select 3, date '2021-09-12' from dual
  )
select t2.id, 
       min(t1.datum) keep (dense_rank first
                 order by abs(t1.datum - t2.datum)) as date_1,
       t2.datum as date_2
from   t2 left outer join t1 on t1.id = t2.id
group  by t2.id, t2.datum
;

        ID DATE_1     DATE_2    
---------- ---------- ----------
         3 09-09-2021 12-09-2021

I only included id in the output once - since you join by id, it makes no sense to show it twice.

CodePudding user response：

There's no such function (at least, as far as I can tell), so one option is to rank differences between dates and fetch the highest ranked row. Something like this:

SQL> with
  2  -- sample data
  3  t1 (id, datum) as
  4    (select 1, date '2020-12-02' from dual union all
  5     select 2, date '2019-11-23' from dual union all
  6     select 3, date '2021-09-09' from dual union all
  7     select 3, date '2021-10-12' from dual
  8    ),
  9  t2 (id, datum) as
 10    (select 3, date '2021-09-12' from dual),
 11  --
 12  temp as
 13    -- rank difference of dates in ascending order
 14    (select b.id b_id, b.datum b_datum, a.id a_id, a.datum a_datum,
 15       rank() over (partition by a.id order by abs(b.datum - a.datum) asc) rnk
 16     from t1 a join t2 b on a.id = b.id
 17    )
 18  -- value you want is ranked the "highest"
 19  select a_id, a_datum,
 20         b_id, b_datum
 21  from temp
 22  where rnk = 1;

      A_ID A_DATUM          B_ID B_DATUM
---------- ---------- ---------- ----------
         3 09/09/2021          3 09/12/2021

SQL>

CodePudding user response：

From Oracle 12, you can use a LATERAL JOIN with FETCH FIRST ROW ONLY:

SELECT a1.*,
       a2."DATE" AS date2
FROM   a2
       CROSS JOIN LATERAL (
         SELECT a1.*
         FROM   a1
         WHERE  a1.id = a2.id
         ORDER BY ABS(a1."DATE" - a2."DATE")
         FETCH FIRST ROW ONLY
       ) a1

Which, for the sample data:

CREATE TABLE A1 (ID, "DATE") AS
SELECT 1, DATE '2020-12-02' FROM DUAL UNION ALL
SELECT 2, DATE '2019-11-23' FROM DUAL UNION ALL
SELECT 3, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 3, DATE '2021-10-12' FROM DUAL;

CREATE TABLE A2 (ID, "DATE") AS
SELECT 3, DATE '2021-09-12' FROM DUAL;

Outputs:

ID	DATE	DATE2
3	2021-09-09 00:00:00	2021-09-12 00:00:00

If you have multiple rows for the same ID in A2 then they will each be matched with the closest row:

For example, after:

INSERT INTO a2 (id, "DATE") VALUES (3, DATE '2025-01-01');

Then the query above would output:

ID	DATE	DATE2
3	2021-09-09 00:00:00	2021-09-12 00:00:00
3	2021-10-12 00:00:00	2025-01-01 00:00:00

db<>fiddle here