oracle 中是否有用于 python pd.merge_asof 的等效日期函式？請看下面的例子：
表A1
ID Date
1 12/02/2020
2 11/23/2019
3 09/09/2021
3 10/12/2021
表A2
ID Date
3 09/12/2021
對于 ID = 3，A2 表中的日期為 09/12/2021。當我嘗試將此 ID 和日期與 A1 匹配時，只有 ID 匹配。所以我嘗試添加一個邏輯來獲得以下輸出（因為它是最近的日期）。
輸出
ID Date ID2 Date2
3 09/09/2021 3 09/12/2021
uj5u.com熱心網友回復：
你可以做這樣的事情（加入 聚合）：
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
我只包含id在輸出中一次 - 因為你加入了 by id，所以顯示兩次是沒有意義的。
uj5u.com熱心網友回復：
沒有這樣的功能（至少，據我所知），所以一種選擇是對日期之間的差異進行排名并獲取排名最高的行。像這樣的東西：
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>
uj5u.com熱心網友回復：
從 Oracle 12 開始，您可以使用LATERAL JOINwith 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
其中，對于樣本資料：
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;
輸出：
ID 日期 日期2 3 2021-09-09 00:00:00 2021-09-12 00:00:00
如果您有多個相同的行ID，A2那么它們將與最近的行匹配：
例如，之后：
INSERT INTO a2 (id, "DATE") VALUES (3, DATE '2025-01-01');
然后上面的查詢將輸出：
ID 日期 日期2 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<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/369923.html
下一篇：使用變數時缺少右括號錯誤