Trouver la rangée associée à un Min / Max, sans boucle interne

cela va gérer les produits qui ont des dates en double:

 DECLARE @Orders table (ProductId int ,CustomerId int ,Date datetime ) INSERT INTO @Orders VALUES (1,1,'20090701') INSERT INTO @Orders VALUES (2,1,'20090703') INSERT INTO @Orders VALUES (3,1,'20090702') INSERT INTO @Orders VALUES (1,2,'20090704') INSERT INTO @Orders VALUES (4,2,'20090701') INSERT INTO @Orders VALUES (1,3,'20090706') INSERT INTO @Orders VALUES (2,3,'20090704') INSERT INTO @Orders VALUES (4,3,'20090702') INSERT INTO @Orders VALUES (5,5,'20090703') --duplicate dates for product #5 INSERT INTO @Orders VALUES (5,1,'20090703') --duplicate dates for product #5 INSERT INTO @Orders VALUES (5,5,'20090703') --duplicate dates for product #5 ;WITH MinOrders AS (SELECT o.ProductId, o.CustomerId, o.Date ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue FROM @Orders o INNER JOIN (SELECT ProductId ,MIN(Date) MinDate FROM @Orders GROUP BY ProductId ) dt ON o.ProductId=dt.ProductId AND o.Date=dt.MinDate ) SELECT m.ProductId, m.CustomerId, m.Date FROM MinOrders m WHERE m.RankValue=1 ORDER BY m.ProductId, m.CustomerId 

cela returnnera les mêmes résultats, utilisez juste la même déclaration et les insertions que le code ci-dessus:

 ;WITH MinOrders AS (SELECT o.ProductId, o.CustomerId, o.Date ,row_number() over(partition by o.ProductId order by o.ProductId,o.CustomerId) AS RankValue FROM @Orders o ) SELECT m.ProductId, m.CustomerId, m.Date FROM MinOrders m WHERE m.RankValue=1 ORDER BY m.ProductId, m.CustomerId 

Vous pouvez essayer chaque version pour voir laquelle fonctionnera plus vite …

 declare @Orders table ( ProductId int, CustomerId int, Date datetime ) insert into @Orders values (1,1,'20090701') insert into @Orders values (2,1,'20090703') insert into @Orders values (3,1,'20090702') insert into @Orders values (1,2,'20090704') insert into @Orders values (4,2,'20090701') insert into @Orders values (1,3,'20090706') insert into @Orders values (2,3,'20090704') insert into @Orders values (4,3,'20090702') insert into @Orders values (5,5,'20090703') select O.* from @Orders O inner join ( select ProductId, MIN(Date) MinDate from @Orders group by ProductId ) FO on FO.ProductId = O.ProductId and FO.MinDate = O.Date 

Le plan de requête estimé pour cela est inutile car je me moque de lui avec des variables de table, mais la jointure interne anonyme doit être optimisée sur une sous-sélection.

Dans SQL Server 2005+ :

 SELECT oo.* FROM ( SELECT DISTINCT ProductId FROM Orders ) od CROSS APPLY ( SELECT TOP 1 ProductID, Date, CustomerID FROM Orders oi WHERE oi.ProductID = od.ProductID ORDER BY Date DESC ) oo 

Nominalement, le plan de la requête contient des Nested Loops .

Toutefois, la boucle externe utilisera une Index Scan avec Stream Aggregate , et la boucle interne contiendra une Index Seek pour le ProductID avec un Top .

En fait, la seconde opération est presque gratuite, puisque la page d'index utilisée dans la boucle interne résidera probablement dans le cache car elle vient d'être utilisée pour la boucle externe.

Voici le résultat du test sur 1,000,000 lignes (avec 100 DISTINCT ProductID ):

 SQL Server parse and comstack time: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 100) Table 'Orders'. Scan count 103, logical reads 6020, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 234 ms, elapsed time = 125 ms. 

, alors que ceci est le résultat d'une simple requête SELECT DISTINCT :

 SELECT od.* FROM ( SELECT DISTINCT ProductId FROM Orders ) od 

Et les statistics:

 SQL Server parse and comstack time: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 100) Table 'Orders'. Scan count 3, logical reads 5648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 250 ms, elapsed time = 125 ms. 

Comme nous pouvons le voir, la performance est la même, et le CROSS APPLY prend que 400 logical reads supplémentaires logical reads (qui ne seront probablement jamais physical ).

Ne voyez pas comment il est possible d'améliorer cette requête.

Aussi le bénéfice de cette requête est qu'elle se met bien en parallèle. Vous remarquerez peut-être que le time CPU est deux fois plus elapsed time : c'est dû à la parallélisation de mon ancien Core Duo .

Un CPU 4-core compléterait cette requête dans la moitié de ce time.

Les solutions utilisant des fonctions de window ne sont pas parallélisées:

 SELECT od.* FROM ( SELECT ProductId, Date, CustomerID, ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Date DESC) AS rn FROM Orders ) od WHERE rn = 1 

et voici les statistics:

 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 100) Table 'Orders'. Scan count 1, logical reads 5123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 406 ms, elapsed time = 415 ms. 

Je ne vois pas un moyen de le faire bien sans faire une sous-requête ou une fonction de fenêtrage (comme row_number, rank) car le max ne ressemble qu'à une seule colonne.

Cependant, vous pouvez le faire pas bien.

 SELECT productid, min(date), cast( subssortingng( min(convert(varchar(23),date,21) + cast(customerid as varchar(20))) , 24, 44) as int) customerid from orders group by productid 

Cela ne fonctionne que si votre identifiant client a less de 20 numbers.

EDIT: groupe par clause ajoutée

 SELECT o1.productid, o1.date, o1.customerid FROM Orders o1 JOIN (select productid, min(date) as orderDate from Orders group by productid ) firstOrder ON o1.productid = firstOrder.productid 

C'est le meilleur que je peux find pour être honnête, je ne sais pas quelles sont les caractéristiques de performance de cette requête. Si ce n'est pas bon, je suggérerais probablement d'exécuter deux requêtes pour get l'information que vous voulez.

Est-ce que IX_Orders est sortingé par ProductId, puis CutomerId, puis Date ou est-ce ProductId, puis Date, CustomerId? Si c'est le premier, changez-le en dernier.

En d'autres termes, n'utilisez pas ceci:

 create index IX_Orders on Orders (ProductId, CustomerId, Date) 

Utilisez ceci à la place:

 create index IX_Orders on Orders (ProductId, Date, CustomerId) 

Alors si vous faites:

 SELECT o1.* FROM [Order] o1 JOIN ( SELECT ProductID, Min(Date) as Date FROM [Order] GROUP BY ProductID ) o2 ON o1.ProductID = o2.ProductID AND o1.Date = o2.Date ORDER BY ProductID 

Vous vous retrouvez avec un seul scan d'index sur IX_Orders, mais si deux clients peuvent order le même produit en même time, vous pouvez get plusieurs lignes pour chaque produit. Vous pouvez passer à côté de cela en utilisant la requête suivante, mais elle est less efficace que la première:

 WITH cte AS ( SELECT ProductID, CustomerID, Date, ROW_NUMBER() OVER(PARTITION BY ProductID ORDER BY Date ASC) AS row FROM [Order] ) SELECT ProductID, CustomerId, Date FROM cte WHERE row = 1 ORDER BY ProductID