Jointure SQL: sélection des derniers enregistrements dans un un-à-plusieurs relations

Question

Supposons que j'ai une table de clients et une table des achats. Chaque achat appartient à un client. Je veux obtenir une liste de tous les clients avec leur dernier achat dans une instruction SELECT. Quelles sont les bonnes pratiques? Des conseils sur la construction d'indices?

Veuillez utiliser cette table/colonne de noms dans votre réponse:

• client: id, nom
• achat: id, customer_id, item_id, la date

Et dans des situations plus complexes, serait-il (de performance) bénéfique à dénormaliser la base de données en mettant le dernier achat dans la table client?

Si l' (achat) id est garanti pour être triés par date, les déclarations simplifiée en utilisant quelque chose comme LIMIT 1?

Answer 1

Ceci est un exemple de l' greatest-n-per-group problème qui apparaît régulièrement sur StackOverflow.

Voici comment j'ai l'habitude de recommander de le résoudre:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
WHERE p2.id IS NULL;

Explication: étant donné une rangée p1, il devrait y avoir aucune ligne n' p2 avec le même client et à une date ultérieure (ou, dans le cas de liens, plus tard id). Quand nous trouvons que c'est vrai, alors p1 est la plus récente d'achat du client.

Concernant les indices, j'aimerais créer un index composé en purchase sur les colonnes (customer_id, date, id). Qui peut permettre à la jointure externe pour être fait à l'aide d'un index de couverture. Assurez-vous de le tester sur votre plate-forme, car l'optimisation est dépendant de l'implémentation. Utiliser les fonctions de votre SGBDR pour analyser le plan d'optimisation. E. g. EXPLAIN sur MySQL.

---

Certaines personnes utilisent des sous-requêtes au lieu de la solution que je montre ci-dessus, mais j'ai trouver ma solution, il est plus facile de résoudre les égalités.

Answer 2

Vous pouvez également essayer de faire cela à l'aide d'une sous-sélection

SELECT  c.*, p.*
FROM    customer c INNER JOIN
        (
            SELECT  customer_id,
                    MAX(date) MaxDate
            FROM    purchase
            GROUP BY customer_id
        ) MaxDates ON c.id = MaxDates.customer_id INNER JOIN
        purchase p ON   MaxDates.customer_id = p.customer_id
                    AND MaxDates.MaxDate = p.date

L'sélectionnez doit adhérer sur tous les clients et leur Dernière date d'achat.

Answer 3

Une autre approche serait d'utiliser un NOT EXISTS condition dans votre condition de jointure pour tester plus tard achats:

SELECT *
FROM customer c
LEFT JOIN purchase p ON (
       c.id = p.customer_id
   AND NOT EXISTS (
     SELECT 1 FROM purchase p1
     WHERE p1.customer_id = c.id
     AND p1.id > p.id
   )
)

Answer 4

Vous n'avez pas spécifié de la base de données. Si c'est celle qui permet à des fonctions analytiques, il peut être plus rapide d'utiliser cette approche que le GROUPE PAR un(certainement plus rapide dans Oracle, probablement plus rapide à la fin des éditions de SQL Server, ne sais pas pour les autres).

La syntaxe de SQL Server serait:

SELECT c.*, p.*
FROM customer c INNER JOIN 
     (SELECT RANK() OVER (PARTITION BY customer_id ORDER BY date DESC) r, *
             FROM purchase) p
ON (c.id = p.customer_id)
WHERE p.r = 1