Dernière ligne par client : égalités et plans efficaces

La dernière commande de chaque client semble demander un simple MAX. MAX trouve le dernier horodatage, mais pas une ligne complète unique en cas d'égalité. Une jointure sur cette date peut ramener plusieurs commandes. Des MAX indépendants sur d'autres colonnes peuvent même construire une combinaison qui n'a jamais existé.

Définir la règle de sélection

L'exemple trie OccurredAt puis OrderId en ordre décroissant. Le second critère unique départage les dates identiques et produit une seule ligne lorsqu'il existe des commandes. Une identité supérieure n'est ici qu'un départage, pas une preuve de chronologie métier ou d'ordre des commits.

CREATE TABLE #Customers(CustomerId int PRIMARY KEY);
INSERT #Customers VALUES(1),(2),(3);
CREATE TABLE #Orders(OrderId int PRIMARY KEY, CustomerId int NOT NULL,
 OccurredAt datetime2(0) NOT NULL, Amount decimal(10,2) NOT NULL);
INSERT #Orders VALUES(11,1,'20230101',10),(12,1,'20230101',20),
(13,2,'20230102',30);
CREATE INDEX IX_Latest ON #Orders(CustomerId,OccurredAt DESC,OrderId DESC)
INCLUDE(Amount);

;WITH ranked AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS rn FROM #Orders)
SELECT c.CustomerId,r.OrderId,r.OccurredAt,r.Amount
FROM #Customers AS c LEFT JOIN ranked AS r
ON r.CustomerId=c.CustomerId AND r.rn=1
ORDER BY c.CustomerId;

Le client 1 possède deux commandes au même instant : OrderId 12 gagne selon la règle. Le client 2 en possède une et le client 3 aucune. LEFT JOIN conserve ce dernier avec des champs NULL. La condition rn = 1 doit rester dans la jointure ; placée dans WHERE, elle supprimerait le client sans commande.

Pour retourner toutes les commandes également récentes, utilisez RANK ou DENSE_RANK sur l'horodatage seul et acceptez plusieurs lignes. Ajouter OrderId à cet ordre supprimerait l'égalité métier. Choisissez entre un représentant et tous les événements ex aequo avant toute optimisation.

Comparer les parcours

ROW_NUMBER convient à une sélection large de clients, car SQL Server peut traiter ensemble beaucoup de commandes. OUTER APPLY exprime une recherche par client pouvant s'arrêter au premier résultat avec un index adapté.

SELECT c.CustomerId,o.OrderId,o.OccurredAt,o.Amount
FROM #Customers AS c
OUTER APPLY
(SELECT TOP(1) OrderId,OccurredAt,Amount FROM #Orders AS o
 WHERE o.CustomerId=c.CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS o
ORDER BY c.CustomerId;

Aucune formulation n'est toujours plus rapide. Quelques clients peuvent favoriser des recherches ciblées, tandis qu'un rapport presque exhaustif peut bénéficier d'un traitement global. L'optimiseur transforme les plans : examinez exécution réelle, lectures logiques et estimations au lieu de déduire le parcours du texte SQL.

L'index commence par CustomerId puis suit l'ordre décroissant demandé ; Amount est inclus pour couvrir la lecture. Cela peut éviter un tri ou une recherche supplémentaire. Chaque colonne ajoutée coûte cependant de l'espace et des écritures. Un index manquant ou mal ordonné peut provoquer des scans répétés dans un plan APPLY.

Vérifier les nuances métier

La dernière commande réussie diffère de la dernière commande si elle est réussie. Filtrer le succès avant le classement choisit le dernier succès. Classer toutes les commandes puis filtrer peut ne rien retourner lorsque la plus récente a échoué. Ces deux lectures sont plausibles mais différentes.

Un rapport à une date donnée doit placer sa borne dans les candidats. Filtrez OccurredAt strictement avant la borne exclusive avant de choisir le gagnant. Si plusieurs instructions doivent partager une même vue, définissez l'isolation : des lectures indépendantes peuvent observer des modifications entre étapes.

Testez absence de commandes, égalités, client très volumineux, nombreux petits clients et les deux interprétations du statut. Contrôlez le nombre de clients conservés et l'appartenance de chaque Amount à l'OrderId retourné. Comparez les plans avec les mêmes paramètres et la même règle fonctionnelle. Une requête plus rapide sélectionnant une autre commande n'améliore pas le rapport demandé.

Références techniques: Microsoft Learn: ROW_NUMBER · Microsoft Learn: TOP.