dans la leçon d’aujourd’hui, vous allez apprendre à regrouper les résultats renvoyés par vos requêtes à l’aide de la clause SQL GROUP BY.
Les objectifs de la leçon d’aujourd’hui sont les suivants:
- Apprenez à regrouper les résultats en utilisant GROUP BY
- utilisez les fonctions agrégées pour effectuer des calculs
- comprenez comment filtrer les groupes en utilisant la clause HAVING
Important! Veuillez suivre et faire les exemples dans votre base de données. Si vous ne l’avez pas déjà fait, inscrivez-vous à mon Guide pour commencer avec SQL Server., Vous obtenez des instructions sur la façon d’installer les outils gratuits et exemple de base de données.
clause SQL GROUP BY
la Clause SQL GROUP BY est utilisée pour afficher une ligne à travers les valeurs de colonne spécifiées. Il est généralement utilisé en conjonction avec des fonctions d’agrégation telles que SUM ou Count pour résumer les valeurs. Dans SQL, les groupes sont des combinaisons uniques de champs. Plutôt que de renvoyer chaque ligne d’une table, lorsque les valeurs sont regroupées, seules les combinaisons uniques sont renvoyées.
la Clause GROUP BY est ajoutée à L’instruction SQL après la Clause WHERE., Voici un exemple où nous listons SalesOrderID, à l’exclusion des quantités supérieures à 100.
Il ya un couple de choses à noter. Tout d’abord, les colonnes que nous voulons résumer sont répertoriées, séparées par des virgules, dans la clause GROUP BY. Deuxièmement, cette même liste de colonnes doivent être énumérés dans l’instruction select, sinon l’instruction échoue.,
lorsque cette instruction est exécutée, toutes les lignes filtrées ne sont pas renvoyées. Seules les combinaisons uniques de SalesOrderID sont incluses dans le résultat. Cette instruction est très similaire à
Mais il y a une différence fondamentale., Le modificateur DISTINCT s’arrête à la sortie d’une combinaison unique de lignes, alors que, avec L’instruction GROUP BY, nous pouvons calculer des valeurs basées sur les lignes filtrées sous-jacentes pour chaque combinaison unique.
en d’autres termes, en utilisant notre exemple, avec le groupe BY, Nous pouvons calculer le nombre ou OrderDetails par commande comme suit:
COUNT est un exemple d’une fonction d’agrégation, ce sont ce qui donne vraiment au groupe par déclaration sa valeur spéciale.
SQL Aggregate Functions
certaines fonctions, telles que SUM, sont utilisées pour effectuer des calculs sur un groupe de lignes. Dans la plupart des cas, ces fonctions fonctionnent sur un groupe de valeurs définies à l’aide de la clause GROUP BY., Lorsqu’il n’y a pas de clause GROUP BY, il est généralement entendu que la fonction aggregate s’applique à tous les résultats filtrés.
Quelques-uns des les plus courantes des fonctions d’agrégation suivants:
| AVG(expression) | Calculer la moyenne de l’expression. |
| COUNT(expression) | compter les occurrences de valeurs non nulles renvoyées par l’expression. |
| COUNT(*) | Compte toutes les lignes dans la table spécifiée., |
| MIN(expression) | trouve la valeur d’expression minimale. |
| MAX(expression) | trouve la valeur d’expression maximale. |
| SUM(expression) | Calculer la somme de l’expression. |
Ces fonctions peuvent être utilisées en conjonction avec la clause GROUP BY. Seuls, ils fonctionnent sur toute la table; cependant, lorsqu’ils sont utilisés avec GROUP BY, leurs calculs sont « réinitialisés” chaque fois que le regroupement change., De cette manière, ils agissent comme des sous-totaux.
fonction D’agrégation Simple
lorsque vous utilisez la fonction d’agrégation, vous pouvez calculer le résultat sur toutes les valeurs ou sur des valeurs distinctes. Par exemple, pour compter tous les enregistrements SalesOrderDetails, nous pouvons utiliser l’expression:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
cela entraîne un nombre de 121317 renvoyés.
pour compter les différents ordres composant les détails, nous utiliserions ce qui suit:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
le nombre est maintenant de 31465.,
SQL GROUP BY et fonctions D’agrégation
pour agréger signifie Faire tout à partir de parties individuelles. Les fonctions d’agrégation sont des fonctions qui travaillent sur plus d’une ligne pour retourner un résultat.
AVG ET SUM
la fonction SUM totalise les valeurs retournées, de la même manière AVG calcule la moyenne.
voyons si nous pouvons calculer le montant total de la commande à partir des OrderDetails., D’après les leçons précédentes, nous savons comment calculer le montant total pour chaque détail comme:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
puisque nous pouvons appliquer la fonction d’agrégation aux expressions, nous pouvons configurer un regroupement sur OrderID pour calculer le prix total par commande comme
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
Nous pouvons même Trier par le total pour aae732dfbc »>
de la même manière, nous pouvons calculer le détail moyen de la commande en utilisant AVG. Pourquoi essayez-vous ci-dessous! Si vous êtes bloqué, basculez la réponse.
Pour les curieux, car une moyenne est calculée comme la somme de l’échantillon divisé par le nombre d’échantillon, puis à l’aide d’AVG dans la déclaration ci-dessus est le même que:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Nous avons couvert beaucoup dans cette section., Voici quelques points clés à retenir:
- Une fonction d’agrégation peut évaluer une expression telle que SUM(A + B)
- Vous devriez alias les fonctions d’agrégation, donc les noms de colonne sont significatifs
- lorsque vous travaillez avec des fonctions d’agrégation et GROUP BY, il est parfois plus facile de penser aux détails d’abord, c’est-à-dire écrire une simple instruction SELECT, inspecter les résultats, puis ajouter des éléments fantaisistes. Je couvre cela dans Comment écrire de meilleures requêtes.
SQL COUNT
la fonction COUNT est utilisée lorsque vous avez besoin de savoir combien d’enregistrements existent dans une table ou dans un groupe., COUNT(*) comptera tous les enregistrements du regroupement; tandis que COUNT(expression) compte tous les enregistrements où le résultat de l’expression n’est pas null. Vous pouvez également utiliser Distinct with COUNT pour trouver le nombre de valeurs uniques au sein d’un groupe.
Pour trouver le nombre de SalesOrderDetail Lignes par commande
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Pour trouver le nombre de commandes par produit
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN et MAX
l’Utilisation MIN et MAX pour trouver la plus petite et la plus grande des valeurs, respectivement, à l’intérieur d’un tableau ou d’un groupe.,
par exemple, pour trouver les quantités de produits les plus petites et les plus grandes commandées dans une commande, essayez
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Vous pouvez également trouver la valeur MIN ou MAX d’un calcul. Ici, nous trouvons le montant de produit le plus élevé commandé dans un produit:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
Clause HAVING
la clause HAVING SQL est utilisée pour filtrer les groupes en fonction des résultats des fonctions d’agrégation. Cela permet de résoudre des problèmes tels que sélectionnez toutes les commandes qui ont plus de deux lignes de détail.,
cet exemple ressemble
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
Si nous voulions trouver toutes les commandes de plus de $1000 nous écrire
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
Notez que nous pouvons utiliser l’alias TotalPrice dans la clause ORDER BY, mais ensuite avoir une clause de a à utiliser l’expression.
pour marteler la maison ayant, je veux montrer un dernier exemple. Ici, vous verrez que L’instruction HAVING inclut une fonction d’agrégation qui ne figure pas dans la liste SELECT.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
dans la requête ci-dessus, nous obtenons le prix total des commandes pour lesquelles le montant SalesOrderDetail moyen est supérieur à 500,00$.
déclaration finale sur HAVING
bien qu’ils remplissent une fonction similaire, il existe une distinction clé entre la clause WHERE et HAVING. La clause WHERE pour filtrer les enregistrements individuels; tandis que la clause HAVING filtre sur les groupes.
pour le garder droit dans ma tête, j’aime penser à la clause WHERE qui fait son travail avant que les groupements aient lieu, puis à la clause HAVING qui prend le relais après la formation des groupes.,
Exercices
Il est important de pratiquer! Utilisez l’exemple de base de données pour répondre à ces questions.
- Les RH veulent un rapport sur le nombre d’employés actifs par titre d’emploi. Quel SQL utiliseriez-vous?
- afficher la quantité minimale, maximale et moyenne commandée pour chaque produit dans SalesOrderDetails.
- énumérez tous les titres d’emploi des employés et le nombre d’employés dont le nombre moyen d’heures de congé de maladie est inférieur ou égal à quarante.
- pour un titre de poste renvoyé dans #3 ci-dessus, le nombre est-il le même pour le résultat de la réponse #1 au titre de poste correspondant?
les Réponses sont Ici!,
Leave a Reply