SQL - sous-Requête en Fonction d'Agrégation

Je suis en utilisant la base de données northwind pour actualiser mes compétences SQL, par la création de plus ou moins de requêtes complexes. Malheureusement, je ne pouvais pas trouver une solution pour mon dernier cas d'utilisation:
"Obtenir la somme de cinq grands ordres pour chaque catégorie dans l'année 1997."

Les tables concernées sont:

Orders(OrderId, OrderDate)
Order Details(OrderId, ProductId, Quantity, UnitPrice)
Products(ProductId, CategoryId)
Categories(CategoryId, CategoryName)

J'ai essayé la requête suivante

SELECT c.CategoryName, SUM(
  (SELECT TOP 5 od2.UnitPrice*od2.Quantity 
   FROM [Order Details] od2, Products p2
   WHERE od2.ProductID = p2.ProductID
   AND c.CategoryID = p2.CategoryID
   ORDER BY 1 DESC))
FROM [Order Details] od, Products p, Categories c, Orders o 
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName

Bien... Il s'est avéré que les sous-requêtes ne sont pas autorisés dans les fonctions d'agrégation. J'ai lu d'autres posts sur ce problème, mais ne pouvait pas trouver un solution pour mon cas d'utilisation spécifiques. J'espère que vous pourrez m'aider...

La version de SGBD que vous utilisez? Aussi votre question n'est pas correcte parce que l'ordre peut appartenir à plus d'une catégorie.
Je suis l'aide de MSSQL 2012. Si vous vous référez à la sous-requête, il y a une clause where - WHERE c.categoryID = p2.CategoryID - filtre pour une catégorie seulement.

OriginalL'auteur Thomas | 2013-05-01

  1. 22

    Les sous-requêtes ne sont généralement pas autorisés dans les fonctions d'agrégation. Au lieu de cela, déplacer le total à l'intérieur de la sous-requête. Dans ce cas, vous aurez besoin d'un niveau supplémentaire de sous-requête en raison de la top 5:

    SELECT c.CategoryName,
  (select sum(val)
   from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val
         FROM [Order Details] od2, Products p2
         WHERE od2.ProductID = p2.ProductID
         AND c.CategoryID = p2.CategoryID
         ORDER BY 1 DESC
        ) t
  )
FROM [Order Details] od, Products p, Categories c, Orders o 
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName, c.CategoryId
    Merci ça fait la job! Juste pour info... le code de catégorie doit être regroupés. La dernière ligne devrait ressembler à GROUP BY c.CategoryName, c.CategoryID. Mais merci pour votre temps, que la requête a l'air vraiment bizarre pour moi. Besoin de l'analyser en profondeur maintenant... 🙂

    OriginalL'auteur Gordon Linoff

  2. 4

    Utilisation CTE avec ROW_NUMBER fonction de classement à la place excessive de la sous-requête. 

     ;WITH cte AS
 (
  SELECT c.CategoryName, od2.UnitPrice, od2.Quantity,
         ROW_NUMBER() OVER(PARTITION BY c.CategoryName ORDER BY od2.UnitPrice * od2.Quantity DESC) AS rn
  FROM [Order Details] od JOIN Products p ON od.ProductID = p.ProductID
                          JOIN Categories c ON p.CategoryID = c.CategoryID
                          JOIN Orders o ON od.OrderID = o.OrderID
  WHERE o.OrderDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101'), 0)
    AND o.OrderDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101')+1, 0)
  )
  SELECT CategoryName, SUM(UnitPrice * Quantity) AS val
  FROM cte
  WHERE rn < 6
  GROUP BY CategoryName

    OriginalL'auteur Aleksandr Fedorenko

  3. 3

    Son certainement une sous-requête problème ici, c'est un excellent article sur ce (écrit à l'origine pour l'Accès, mais la syntaxe est identique), également orderdate = 1997 donnera la date de commande pour 1 janv 1997 " - vous besoin d'datepart(année, date de commande) = 1997, une fois que vous avez l' (jusqu'à cinq) de lignes renvoyées pour chaque catégorie, vous pouvez alors encapsuler les lignes retournées et agréger entre eux

    Merci pour le lien. Vous avez raison, j'ai édité mon post. Il convient de l'ofc YEAR(o.OrderDate) = '1997'
    Pourriez-vous fournir un exemple pour "encapsuler les lignes retournées et de les agréger"?
    SÉLECTIONNEZ x.Un "x".B, x.C, SUM(x.d) D DE ( pour être valable, Toute instruction sql select contenant les colonnes a, b , c, d, e) x GROUPE x.d
    C'est également une sous-requête, cependant, sa très simple et dans le top 5 regroupées par Catégorie sous-requête est plus avancé et puissant à utiliser. Je ne veux pas mélanger à l'aide de la même descriptif nown. De plus en plus important pour vous d'être conscient de la puissance des sous-requêtes.

    OriginalL'auteur Ian P

  4. 0

    J'ai couru dans un problème similaire avec un Accès à la sous-requête où les enregistrements ont été triées par date. Lorsque j'ai utilisé le "Dernier" fonction d'agrégation, j'ai trouvé il est passé par toutes les sous-requêtes et récupéré la dernière ligne de données contre l'Accès de la table, et pas la requête triée comme prévu. Bien que je puisse avoir réécrit la requête à utiliser la fonction d'agrégation dans le premier jeu de parenthèses (comme suggéré auparavant) je l'ai trouvé plus facile d'enregistrer les résultats de la requête sous forme de tableau dans la base de données triées dans l'ordre que je voulais et ensuite utiliser le "Dernier" fonction d'agrégation pour récupérer les valeurs que je voulais. Je vais exécuter une requête de mise à jour dans le futur pour garder les résultats actuels. Pas efficace, mais efficace.

    OriginalL'auteur Peter Cleary