Saya punya tabel seperti ini:

CREATE TABLE mytable (
  id INT(10) auto_increment PRIMARY KEY,
  from DATE(10) NOT NULL,
  before DATE(10) NULL,
  reference_id INT(10) NOT NULL,
)

Jadi ada baris yang mereferensikan tabel lain (dengan reference_id). Referensi tersebut memiliki rentang tanggal (from/before) yang berlaku. Untuk setiap reference_id mungkin ada banyak entri yang biasanya tidak memiliki celah:

id | from       | before     | reference_id
-------------------------------------------
1  | 2019-03-01 | 2019-03-05 | 5
5  | 2019-03-05 | 2019-03-09 | 5
8  | 2019-03-09 | NULL       | 5

(Mungkin ada entri untuk reference_id lainnya di antara mereka.) Sebuah entri dimulai dari yang sebelumnya berakhir. Sekarang saya ingin menemukan semua entri yang memiliki celah di antara mereka, di mana from lebih lambat dari before sebelumnya. Misalnya (ubah baris 2, kolom from):

id | from       | before     | reference_id
-------------------------------------------
1  | 2019-03-01 | 2019-03-05 | 5
5  | 2019-03-06 | 2019-03-09 | 5
8  | 2019-03-09 | NULL       | 5

from baris 2 lebih lambat satu hari dari before baris 1, itu selisih. Masalahnya: untuk baris 3 dan 1 hal yang sama benar, tetapi mereka tidak boleh dianggap sebagai hasil karena mereka memiliki baris lain di antara mereka.

Apa yang saya dapatkan adalah ini:

SELECT *
FROM mytable mt1
INNER JOIN mytable mt2 ON mt1.reference_id = mt2.reference_id AND mt1.id != mt2.id
WHERE mt1.before IS NOT NULL
  AND mt1.from < mt2.from
  AND DATE_ADD(mt1.before, INTERVAL 1 DAY) = mt2.from
  AND NOT EXISTS(SELECT * FROM mytable mt3 WHERE mt3.id BETWEEN mt1.id AND mt2.id)

Namun, ini (EXISTS) sangat lambat. Apakah ada cara yang lebih baik untuk melakukan ini?

[ sunting ]Permintaan baru saja selesai dan saya tidak mendapatkan hasil apa pun meskipun saya sangat mengharapkannya. Jadi bukan hanya lambat tapi juga salah.[/edit]


Rencana eksekusi:

1,PRIMARY,mt1,ALL,"mytable_48d78c2b,mytable_261384ee,mytable_849034da",,,,3313021,Using where
1,PRIMARY,mt2,ref,"mytable_48d78c2b,mytable_849034da",mytable_849034da,4,db.mt1.reference_id,1,Using index condition; Using where
2,DEPENDENT SUBQUERY,mt3,index,PRIMARY,mytable_48d78c2b,3,,3313021,Using where; Using index
1
Tim-Erwin 22 Maret 2019, 12:02

1 menjawab

Jawaban Terbaik

(Perhatikan bahwa from adalah pilihan yang sangat buruk untuk nama kolom karena ini adalah kata kunci yang dicadangkan. Tapi yah, itu valid, jadi begitulah.)

Dengan asumsi tidak ada tumpang tindih, Anda dapat menggunakan fungsi lag untuk mencari before dari baris sebelumnya: lag(before, 1) over (partition by reference_id order by before) as previous_before. Dari sana, jika mudah untuk memeriksa apakah ada celah, jika from > previous_before

select
    `from`, before, `reference_id`
  , `from` > lag(before, 1) over (partition by reference_id order by before) as has_gap
from mytable

Apa yang lag lakukan adalah mencari nilai before dari baris sebelumnya, yang sebelumnya ditentukan oleh klausa order by dari fungsi jendela. Jika from dan sebelumnya identik, tidak ada celah.

Kueri ini memberi Anda baris dengan celah di depannya, Anda dapat menggunakan fungsi lead dengan cara yang sama untuk mendapatkan baris dengan celah setelahnya.

Perhatikan bahwa MariaDB memiliki fungsi jendela sejak 10.2.2 saja.

2
Guillaume 22 Maret 2019, 21:08