Finden Sie effizient den letzten Zeitstempel innerhalb eines bestimmten Zeitbereichs

Question

Das Problem liegt bei einem hypothetischen Online-Shop; ich werde die Daten für mein spezifisches Problem vereinfachen.

Angenommen, es gibt zwei Tabellen: Eine enthält das Registrierungsdatum jedes Kunden

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

, die zweite speichert alle Online-Besuche des Kunden

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

Für jeden Benutzer möchte ich den letzten Tag ermitteln, an dem der Benutzer den Online-Shop innerhalb von 7 Tagen nach der Registrierung zuletzt besucht hat, auf die effizienteste Weise. Wenn der Benutzer den Shop seit der Registrierung nicht besucht hat, sollte ich NULL als Datum des letzten Besuchs innerhalb von 7 Tagen nach der Registrierung zurückgeben.

Selbstverständlich kann ich LEFT JOIN Kundenzugriff über Kunden-ID ermöglichen und das Zugriffsdatum auf zwischen Registrierungstagen und plus sieben filtern und schließlich das maximale Datum für jeden Kunden erhalten. Allerdings führt die anfängliche Verknüpfung zu einer riesigen Tabelle, und ich möchte diesen rechenintensiven Vorgang vermeiden.

Vielen Dank für deine Gedanken!

Answer 1

这可能是最有效的：

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;

索引：

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

您对“计算成本高昂的操作”的担忧：

• 获取行的成本比表达式求值的成本更高。
• 我推荐的访问索引是“覆盖”，因此它只查看索引。