Saya mencari solusi yang lebih baik untuk masalah yang cukup umum dengan tabel temporal.

Katakan kita punya

table_a (some_value int, date_from date, date_to date)

Dan serangkaian tabel serupa table_b, table_c, ...

Sistem yang sebenarnya adalah sistem SDM yang melacak orang, kontrak, penugasan, gaji, ... semuanya diberi tanggal dengan cara yang dijelaskan di atas.

Saya perlu menggabungkan tabel seperti itu (mis. bayangkan mendapatkan semua baris dengan some_value yang sama) dan mengembalikan periode saat penggabungan tersebut valid (yaitu semua baris tumpang tindih selama periode tersebut).

Dengan dua tabel itu mudah (abaikan nilai NULL untuk saat ini)

select a.some_value, greatest(a.date_from, b.date_from) date_from, least(a.date_to, b.date_to) date_to
from table_a a join table_b b on a.some_value = b.some_value
where a.date_from < b.date_to and b.date_from < a.date_to

Ini menjadi lebih sulit secara kuadrat dengan lebih banyak tabel, karena dengan tiga tabel (A, B, C) Anda perlu memeriksa tumpang tindih antara A dan B, B dan C, C dan A. dengan N tabel ini tumbuh sebagai N^2.

Jadi saya telah menulis fungsi pipelined pl/sql (sebut saja dated_join) yang memberikan dua interval ia mengembalikan satu baris dengan periode yang tumpang tindih atau tidak sama sekali jika tidak tumpang tindih

Jadi saya bisa memiliki untuk tiga meja

select a.some_value, period_b.date_from, period_b.date_to
from table_a a
join table_b b on a.some_value = b.some_value
join table(dated_join(a.date_from, a.date_to, b.date_from, b.date_to)) period_a
join table_c c on a.some_value = c.some_value
join table(dated_join(period_a.date_from, period_a.date_to, c.date_from, c.date_to)) period_b

Skala ini linier ke nilai N, karena setiap periode hanya bergabung dengan yang sebelumnya dan meneruskan periode yang tumpang tindih.

Pertanyaan: apakah mungkin membuat strategi ini bekerja dengan OUTER JOIN? Saya tidak dapat menemukan solusi yang setengah layak.

Apakah ada sesuatu dalam ekstensi temporal SQL:2011 yang akan membantu dengan ini?

Terima kasih atas bantuan Anda

0
user103716 8 Januari 2021, 19:43

2 jawaban

Jawaban Terbaik

Jika Anda ingin menggabungkan beberapa tabel sehingga semuanya tumpang tindih dengan periode yang sama, Anda dapat menggunakan GREATEST dan LEAST:

SELECT t1.date_from AS t1_from,
       t2.date_from AS t2_from,
       t3.date_from AS t3_from,
       t4.date_from AS t4_from,
       t1.date_to   AS t1_to,
       t2.date_to   AS t2_to,
       t3.date_to   AS t3_to,
       t4.date_to   AS t4_to
FROM   table1 t1
       INNER JOIN table2 t2
       ON (   t1.date_to   > t2.date_from
          AND t1.date_from < t2.date_to )
       INNER JOIN table3 t3
       ON (   LEAST( t1.date_to, t2.date_to )        > t3.date_from
          AND GREATEST( t1.date_from, t2.date_from ) < t3.date_to )
       INNER JOIN table4 t4
       ON (   LEAST( t1.date_to, t2.date_to, t3.date_to )          > t4.date_from
          AND GREATEST( t1.date_from, t2.date_from, t3.date_from ) < t4.date_to );

Yang, untuk data sampel:

CREATE TABLE table1 ( date_from, date_to ) AS
SELECT DATE '2020-01-01', DATE '2020-01-10' FROM DUAL UNION ALL
SELECT DATE '2020-02-10', DATE '2020-02-15' FROM DUAL UNION ALL
SELECT DATE '2020-03-15', DATE '2020-03-18' FROM DUAL;

CREATE TABLE table2 ( date_from, date_to ) AS
SELECT DATE '2020-01-05', DATE '2020-01-15' FROM DUAL UNION ALL
SELECT DATE '2020-02-09', DATE '2020-02-16' FROM DUAL UNION ALL
SELECT DATE '2020-03-16', DATE '2020-03-18' FROM DUAL;

CREATE TABLE table3 ( date_from, date_to ) AS
SELECT DATE '2020-01-01', DATE '2020-01-02' FROM DUAL UNION ALL
SELECT DATE '2020-01-09', DATE '2020-01-16' FROM DUAL UNION ALL
SELECT DATE '2020-02-08', DATE '2020-02-17' FROM DUAL UNION ALL
SELECT DATE '2020-03-15', DATE '2020-03-17' FROM DUAL;

CREATE TABLE table4 ( date_from, date_to ) AS
SELECT DATE '2020-01-02', DATE '2020-01-12' FROM DUAL UNION ALL
SELECT DATE '2020-02-08', DATE '2020-02-17' FROM DUAL UNION ALL
SELECT DATE '2020-03-16', DATE '2020-03-19' FROM DUAL;

Keluaran:

T1_FROM   | T2_FROM   | T3_FROM   | T4_FROM   | T1_TO     | T2_TO     | T3_TO     | T4_TO    
:-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :--------
15-MAR-20 | 16-MAR-20 | 15-MAR-20 | 16-MAR-20 | 18-MAR-20 | 18-MAR-20 | 17-MAR-20 | 19-MAR-20
10-FEB-20 | 09-FEB-20 | 08-FEB-20 | 08-FEB-20 | 15-FEB-20 | 16-FEB-20 | 17-FEB-20 | 17-FEB-20
01-JAN-20 | 05-JAN-20 | 09-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 16-JAN-20 | 12-JAN-20

Dan, jika Anda menginginkannya agar ada tumpang tindih dengan bagian mana pun dari rentang, tukar GREATEST dan LEAST:

SELECT t1.date_from AS t1_from,
       t2.date_from AS t2_from,
       t3.date_from AS t3_from,
       t4.date_from AS t4_from,
       t1.date_to   AS t1_to,
       t2.date_to   AS t2_to,
       t3.date_to   AS t3_to,
       t4.date_to   AS t4_to
FROM   table1 t1
       INNER JOIN table2 t2
       ON (   t1.date_to   > t2.date_from
          AND t1.date_from < t2.date_to )
       INNER JOIN table3 t3
       ON (   GREATEST( t1.date_to, t2.date_to )  > t3.date_from
          AND LEAST( t1.date_from, t2.date_from ) < t3.date_to )
       INNER JOIN table4 t4
       ON (   GREATEST( t1.date_to, t2.date_to, t3.date_to )    > t4.date_from
          AND LEAST( t1.date_from, t2.date_from, t3.date_from ) < t4.date_to );

Keluaran mana:

T1_FROM   | T2_FROM   | T3_FROM   | T4_FROM   | T1_TO     | T2_TO     | T3_TO     | T4_TO    
:-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :-------- | :--------
15-MAR-20 | 16-MAR-20 | 15-MAR-20 | 16-MAR-20 | 18-MAR-20 | 18-MAR-20 | 17-MAR-20 | 19-MAR-20
10-FEB-20 | 09-FEB-20 | 08-FEB-20 | 08-FEB-20 | 15-FEB-20 | 16-FEB-20 | 17-FEB-20 | 17-FEB-20
01-JAN-20 | 05-JAN-20 | 01-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 02-JAN-20 | 12-JAN-20
01-JAN-20 | 05-JAN-20 | 09-JAN-20 | 02-JAN-20 | 10-JAN-20 | 15-JAN-20 | 16-JAN-20 | 12-JAN-20

db<>fiddle di sini

2
MT0 8 Januari 2021, 19:30

Jika Anda ingin semua nilai dari tabel yang tumpang tindih dengan jangka waktu, maka Anda sepertinya ingin:

select *
from table_a a left join
     table_b b
     on b.date_from < a.date_to and
        b.date_to > a.date_from left join
     table_c c
     on c.date_from < a.date_to and
        c.date_to > a.date_from
where a.value = ?

Saya tidak melihat kerumitan "kuadrat". Kondisi join yang sama persis digunakan untuk setiap tabel baru.

1
Gordon Linoff 8 Januari 2021, 16:49