Trouvez efficacement le dernier horodatage dans une plage de temps donnée

Question

Le problème concerne une hypothétique boutique en ligne ; je vais simplifier les données pour mon problème spécifique.

Supposons qu'il y ait deux tables : l'une contient la date d'inscription de chaque client

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

, le second enregistre toutes les visites en ligne du client

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

Pour chaque utilisateur, je souhaite connaître le dernier jour où l'utilisateur a visité la boutique en ligne dans les 7 jours suivant l'inscription, de la manière la plus efficace. Si l'utilisateur n'a pas visité le magasin depuis son inscription, je dois renvoyer NULL comme date de dernière visite dans les 7 jours suivant l'inscription.

Bien sûr, je peux LEFT JOIN accéder aux clients par ID client et filtrer la date d'accès à entre les jours d'inscription et plus sept, et enfin obtenir la date maximale pour chaque client. Cependant, la jointure initiale donne lieu à une table énorme et je souhaite éviter cette opération coûteuse en termes de calcul.

Merci beaucoup pour vos pensées !

Answer 1

C'est probablement le plus efficace :

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;

Indice :

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

Vos inquiétudes concernant les « opérations coûteuses en calcul » :

• Obtenir des lignes coûte plus cher que évaluer des expressions.
• Le 访问index que je recommande est "overlay", il ne regarde donc que l'index.