Aidez-moi s'il vous plaît à générer la requête suivante. Dis que j'ai une table client et une table de commande.
Table client table de commande Je veux trouver les clients qui ont fait des commandes sur trois mois successifs. (Requête utilisant SQL Server 2005 et 2008 est autorisée). La sortie souhaitée est la suivante:
4 Réponses :
EDIT: strong> s'est débarrassé ou le max () sur (partition par ...) code> comme cela semblait tuer la performance. ;WITH cte AS (
SELECT CustID ,
OrderDate,
DATEPART(YEAR, OrderDate)*12 + DATEPART(MONTH, OrderDate) AS YM
FROM Orders
),
cte1 AS (
SELECT CustID ,
OrderDate,
YM,
YM - DENSE_RANK() OVER (PARTITION BY CustID ORDER BY YM) AS G
FROM cte
),
cte2 As
(
SELECT CustID ,
MIN(OrderDate) AS Mn,
MAX(OrderDate) AS Mx
FROM cte1
GROUP BY CustID, G
HAVING MAX(YM)-MIN(YM) >=2
)
SELECT c.CustName, o.OrderDate, YEAR(o.OrderDate) AS YEAR
FROM Customers AS c INNER JOIN
Orders AS o ON c.CustID = o.CustID
INNER JOIN cte2 c2 ON c2.CustID = o.CustID and o.OrderDate between Mn and Mx
order by c.CustName, o.OrderDate
Doit utiliser dense_rank, ou quatre ventes + à l'intérieur de trois mois seront négligés.
Martin, utilisant mes données de test ci-dessous, votre requête renvoie des paires de commandes de deux mois à part, où MX-Mn> = 2, mais comptent (*) = 2 au lieu de> = 3.
@EmtuCifor - pense que cela devrait être réparé maintenant. J'avais besoin de partition par Custadid, g pas seulement g . Merci de me le faire savoir!
Martin, j'aime votre solution pour son élégance, mais à ma surprise, ma solution "intéressante curiosité" fonctionne réellement mieux. Je pense qu'il y a une leçon ici, ce que les fonctions de classement ont un coût parfois très important! Et aussi, que quelque peu contrairement aux attentes, les jointures croisées peuvent être des moyens très efficaces de résoudre les problèmes de regroupement / classement. Maintenant, si nous avions des fonctions de fenêtres à la traîne et en direct, ils gagneraient peut-être tout!
@Empifiifor the max () / min () sur (partition par ...) semblait être le principal coupable. Se débarrasser de cela améliore les choses un peu.
Cela améliore les choses un peu plus que quelque peu! Maintenant, votre requête est le gagnant clair.
Vous allez ici:
select distinct
CustName
,year(OrderDate) [Year]
,OrderDate
from
(
select
o2.OrderDate [prev]
,o1.OrderDate [curr]
,o3.OrderDate [next]
,c.CustName
from [order] o1
join [order] o2 on o1.CustId = o2.CustId and datediff(mm, o2.OrderDate, o1.OrderDate) = 1
join [order] o3 on o1.CustId = o3.CustId and o2.OrderId <> o3.OrderId and datediff(mm, o3.OrderDate, o1.OrderDate) = -1
join Customer c on c.CustId = o1.CustId
) t
unpivot
(
OrderDate for [DateName] in ([prev], [curr], [next])
)
unpvt
order by CustName, OrderDate
AVERTISSEMENT: Cette requête est extrêmement inefficace. :)
Denis, je suis désolé de signaler que cette requête ne renvoie pas les résultats corrects lorsqu'il y a deux commandes par le même client le même jour.
@Emtucifor, je sais! Mais nous ne savons pas ce que @Cshaarpy a besoin! :)
Voici ma version. Je présentais vraiment cela comme une simple curiosité, pour montrer une autre façon de penser au problème. Il s'est avéré plus utile que cela, car il a permis de mieux que même la solution de «îles groupées» de Martin Smith. Cependant, une fois qu'il s'est débarrassé de certaines fonctions de fenêtres agrégées trop coûteuses et a fait des agrégats réels à la place, sa requête commença à frapper des fesses.
Solution 1: Strong> Courses de 3 mois ou plus, fait en vérifiant 1 mois devant et derrière et en utilisant un joint semi-rejoindre contre cela. p> solution 2: strong> modèles exacts de 3 mois. S'il s'agit d'une exécution de 4 mois ou plus, les valeurs sont exclues. Cela se fait en vérifiant 2 mois à venir et deux mois de retard (essentiellement à la recherche du motif N, Y, Y, Y, N). P> Query CPU Reads Duration
Martin 1 2297 299412 2348
Martin 2 625 285 809
Denis 3641 401 3855
Erik 1855 94727 2077
Ne me fais pas ajouter deux autres jointures à ma solution, c'est déjà trois dimensions. : P
Terminé. J'ai laissé les statistiques sur votre ancienne version en juste pour montrer que toutes les opérations de fonctionnement de fenêtres ne sont pas si grandes. Utilisé sans discernement, ils peuvent faire mal à la performance.
Voici ma prise.
Quelle sortie souhaitez-vous si la ligne 113, 13-août 2007, 1 'est ajoutée à la table de commande? Un bloc de sortie pour AA avec 4 lignes ou deux blocs de sortie, chacun contenant 3 rangées? Si vous préférez, est-ce «strictement trois mois à la fois» ou «trois mois ou plus à la fois».
Désolé pour le retard, je préfère exactement trois mois
Voulez-vous dire qu'une chaîne de 4 mois retournerait 6 rangées, une série de mois 1, 2, 3 et un autre ensemble avec le mois 2, 3, 4 ou simplement pour exclure toutes les chaînes de commandes qui ne sont pas exactement 3 mois?
Supprimer
distinct de ma requête et vous obtiendrez toutes sortes de combinaisons de groupes de trois mois. Dis, si vous avez1234 , vous obtiendrez123 ,134 ,234 .Pouvez-vous fournir des données d'échantillons et les résultats souhaités pour le cas qu'il y a plus de 3 mois contigu et plus d'une commande par mois dans cette gamme?