Cari cap waktu terakhir dengan cekap dalam julat masa tertentu

Question

Masalahnya ialah dengan kedai dalam talian hipotesis; Saya akan memudahkan data untuk masalah khusus saya.

Andaikan terdapat dua jadual: satu mengandungi tarikh pendaftaran setiap pelanggan

CREATE TABLE customers (
    customerID int,
    register DATE,
);

, yang kedua menyimpan semua lawatan dalam talian pelanggan

CREATE TABLE visits (
    customerID int,
    visit DATE,
);

Untuk setiap pengguna, saya ingin mencari hari terakhir pengguna melawat kedai dalam talian dalam masa 7 hari selepas pendaftaran, dengan cara yang paling berkesan. Jika pengguna tidak mengunjungi kedai sejak pendaftaran, saya harus mengembalikan NULL sebagai tarikh lawatan terakhir dalam tempoh 7 hari selepas pendaftaran.

Sudah tentu saya boleh TINGGAL SERTAI pelanggan mengakses dengan ID pelanggan dan menapis tarikh akses ​​hingga antara hari pendaftaran dan tambah tujuh, dan akhirnya dapatkan maksimum untuk setiap tarikh Walau bagaimanapun, gabungan awal menghasilkan jadual yang besar dan saya ingin mengelakkan operasi pengiraan yang mahal ini. Terima kasih banyak atas pemikiran anda!

Answer 1

Ini mungkin yang paling berkesan:

SELECT  c.customerID, c.register,
        ( SELECT MAX(v.visit)
            FROM visits AS v
            WHERE v.customerID = c.customerID
              AND v.visit < c.register + INTERVAL 7 DAY
        ) AS last_date
    FROM customers AS c;

Indeks:

customers:  PRIMARY KEY(customerId)  -- is this what  you have?
visits:     INDEX(customerID, visit)  -- or perhaps PRIMARY KEY

Kebimbangan anda tentang "operasi yang mahal secara pengiraan":

• Mendapatkan baris adalah lebih mahal daripada menilai ungkapan.
• Indeks 访问 yang saya cadangkan ialah "overlay", jadi ia hanya melihat pada indeks.