Peut effectuer un traitement de groupe généralisé des valeurs de champ. Cela se fait à l'aide de fonctions d'agrégation. Les fonctions d'agrégation produisent une valeur unique pour un groupe de tables entier. SQL fournit les fonctions d'agrégation suivantes :

• COMPTER– compte le nombre de lignes du tableau avec des valeurs non NULL du champ spécifié en argument.
• SOMME– calcule la somme arithmétique de toutes les valeurs sélectionnées pour un champ donné.
• MOYENNE– fait la moyenne de toutes les valeurs sélectionnées de ce champ.
• MAXIMUM– affiche la plus grande valeur de toutes les valeurs sélectionnées pour ce champ.
• MINIMUM– affiche la plus petite valeur de toutes les valeurs sélectionnées pour ce champ.

  Utilisation des fonctions d'agrégation

  Les fonctions d'agrégation sont utilisées de la même manière que les noms de champs dans la clause SELECT d'une requête, à une exception près : elles prennent les noms de champs comme argument. Seuls les champs numériques peuvent être utilisés avec SOMME Et MOYENNE. AVEC COMPTER, MAXIMUM, Et MINIMUM Les champs numériques et caractères peuvent être utilisés. Lorsqu'il est utilisé avec des champs de caractères MAXIMUM Et MINIMUM les traduira en équivalent ASCII. Cela signifie que MINIMUM choisira le premier, et MAXIMUM dernière valeur par ordre alphabétique.

  Pour trouver le montant total des ventes dans le tableau des ventes, nous devons écrire la requête suivante :

  SELECT SUM(SSum) FROM Vend

  En conséquence nous obtenons :

  Cette requête comptait le nombre de valeurs non vides dans le champ SNum de la table Sells. Si on réécrit la requête comme suit :

  SELECT COUNT(SDate) FROM Vend

  En conséquence nous obtenons :

  COMPTE DE SDate

  4

  Différents résultats de requête lors du calcul de ce qui semble être la même chose sont obtenus car l'une des valeurs du champ SDate est vide ( NUL). Soyez prudent lorsque vous utilisez de telles requêtes.

Comment puis-je connaître le nombre de modèles de PC produits par un fournisseur particulier ? Comment déterminer le prix moyen d’ordinateurs ayant les mêmes caractéristiques techniques ? Il est possible de répondre à ces questions et à bien d’autres liées à certaines informations statistiques en utilisant fonctions finales (agrégatives). La norme fournit les fonctions d'agrégation suivantes :

Toutes ces fonctions renvoient une valeur unique. Parallèlement, les fonctions COMPTE, MINIMUM Et MAXIMUM applicable à tout type de données, tandis que SOMME Et MOYENNE sont utilisés uniquement pour les champs numériques. Différence entre la fonction COMPTER(*) Et COMPTER(<имя поля>) est que le second ne prend pas en compte les valeurs NULL lors du calcul.

Exemple. Trouvez le prix minimum et maximum pour les ordinateurs personnels :

Exemple. Recherchez le nombre d'ordinateurs disponibles produits par le fabricant A :

Exemple. Si l'on s'intéresse au nombre de modèles différents produits par le fabricant A, alors la requête peut être formulée comme suit (en utilisant le fait que dans la table Produit chaque modèle est enregistré une fois) :

Exemple. Recherchez le nombre de modèles différents disponibles produits par le fabricant A. La requête est similaire à la précédente, dans laquelle il fallait déterminer le nombre total de modèles produits par le fabricant A. Ici, vous devez également trouver le nombre de modèles différents dans la table PC (c'est-à-dire celles disponibles à la vente).

Pour garantir que seules des valeurs uniques sont utilisées lors de l'obtention d'indicateurs statistiques, lorsque argument des fonctions d'agrégation peut être utilisé Paramètre DISTINCT. Un autre paramètre TOUS est la valeur par défaut et suppose que toutes les valeurs renvoyées dans la colonne sont comptées. Opérateur,

Si nous avons besoin d’obtenir le nombre de modèles de PC produits tout le monde fabricant, vous devrez utiliser Clause GROUPE PAR, suivant syntaxiquement OÙ clauses.

Clause GROUPE PAR

Clause GROUPE PAR utilisé pour définir des groupes de chaînes de sortie pouvant être appliquées à fonctions d'agrégation (COUNT, MIN, MAX, AVG et SUM). Si cette clause est manquante et que des fonctions d'agrégation sont utilisées, alors toutes les colonnes dont les noms sont mentionnés dans SÉLECTIONNER, devrait être inclus dans fonctions d'agrégation, et ces fonctions seront appliquées à l'ensemble des lignes qui satisfont au prédicat de requête. Sinon, toutes les colonnes de la liste SELECT non inclus dans les fonctions d'agrégation, il faut préciser dans la clause GROUP BY. En conséquence, toutes les lignes de requête de sortie sont divisées en groupes caractérisés par les mêmes combinaisons de valeurs dans ces colonnes.
Après cela, des fonctions d'agrégation seront appliquées à chaque groupe. Veuillez noter que pour GROUP BY, toutes les valeurs NULL sont traitées comme égales, c'est-à-dire lors du regroupement par un champ contenant des valeurs NULL, toutes ces lignes tomberont dans un seul groupe. Si s'il y a une clause GROUP BY , dans la clause SELECT pas de fonctions d'agrégation
, la requête renverra simplement une ligne de chaque groupe. Cette fonctionnalité, ainsi que le mot clé DISTINCT, peuvent être utilisés pour éliminer les lignes en double dans un jeu de résultats.

Regardons un exemple simple : À PARTIR DU PC Modèle GROUPE PAR ;

Dans cette demande, pour chaque modèle de PC, leur nombre et leur coût moyen sont déterminés. Toutes les lignes avec la même valeur de modèle forment un groupe et la sortie de SELECT calcule le nombre de valeurs et les valeurs de prix moyennes pour chaque groupe. Le résultat de la requête sera le tableau suivant :

modèle	Qté_modèle	Prix_moy.
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Si le SELECT avait une colonne de date, il serait alors possible de calculer ces indicateurs pour chaque date spécifique. Pour ce faire, vous devez ajouter la date en tant que colonne de regroupement, puis les fonctions d'agrégation seront calculées pour chaque combinaison de valeurs (modèle-date).

Il existe plusieurs spécifiques règles pour exécuter des fonctions d'agrégation:

• Si à la suite de la demande aucune ligne reçue(ou plus d'une ligne pour un groupe donné), alors il n'y a aucune donnée source pour calculer l'une des fonctions d'agrégation. Dans ce cas, le résultat des fonctions COUNT sera nul et le résultat de toutes les autres fonctions sera NULL.
• Argument fonction d'agrégation ne peut pas lui-même contenir des fonctions d'agrégation(fonction à partir de la fonction). Ceux. dans une requête, il est impossible, par exemple, d'obtenir le maximum de valeurs moyennes.
• Le résultat de l'exécution de la fonction COUNT est entier(ENTIER). D'autres fonctions d'agrégation héritent des types de données des valeurs qu'elles traitent.
• Si la fonction SOMME produit un résultat supérieur à la valeur maximale du type de données utilisé, erreur.

Ainsi, si la demande ne contient pas Clause GROUPE PAR, Que fonctions d'agrégation inclus dans Clause SELECT, sont exécutés sur toutes les lignes de requête résultantes. Si la demande contient Clause GROUPE PAR, chaque ensemble de lignes qui a les mêmes valeurs d'une colonne ou d'un groupe de colonnes spécifié dans Clause GROUPE PAR, constitue un groupe, et fonctions d'agrégation sont effectués pour chaque groupe séparément.

AVOIR une offre

Après cela, des fonctions d'agrégation seront appliquées à chaque groupe. Veuillez noter que pour GROUP BY, toutes les valeurs NULL sont traitées comme égales, c'est-à-dire lors du regroupement par un champ contenant des valeurs NULL, toutes ces lignes tomberont dans un seul groupe. Clause OÙ définit un prédicat pour filtrer les lignes, puis AVOIR une offre s'applique après le regroupement pour définir un prédicat similaire qui filtre les groupes par valeurs fonctions d'agrégation. Cette clause est nécessaire pour valider les valeurs obtenues en utilisant fonction d'agrégation pas à partir de lignes individuelles de la source d'enregistrement définie dans Clause DE, et de groupes de telles lignes. Un tel contrôle ne peut donc pas être contenu dans Clause OÙ.

Pour résumer les informations contenues dans la base de données, SQL fournit des fonctions d'agrégation. Une fonction d'agrégation prend une colonne entière de données comme argument et renvoie une valeur unique qui résume cette colonne d'une manière spécifique.

Par exemple, la fonction d'agrégation AVG() prend une colonne de nombres comme argument et calcule leur moyenne.

Pour calculer le revenu moyen par habitant d'un résident de Zelenograd, vous avez besoin de la requête suivante :

SÉLECTIONNEZ 'REVENU MOYEN PAR HABITANT=', AVG(SUMD)

SQL dispose de six fonctions d'agrégation qui fournissent différents types d'informations récapitulatives (Figure 1) :

– SUM() calcule la somme de toutes les valeurs contenues dans une colonne ;

– AVG() calcule la moyenne des valeurs contenues dans la colonne ;

– MIN() trouve la plus petite de toutes les valeurs contenues dans la colonne ;

– MAX() trouve la plus grande parmi toutes les valeurs contenues dans la colonne ;

– COUNT() compte le nombre de valeurs contenues dans une colonne ;

– COUNT(*) compte le nombre de lignes dans le tableau des résultats de la requête.

L'argument d'une fonction d'agrégation peut être un simple nom de colonne, comme dans l'exemple précédent, ou une expression, comme dans la requête suivante, qui spécifie le calcul de la taxe moyenne par habitant :

SÉLECTIONNER MOYENNE(SOMME*0,13)

Cette requête crée une colonne temporaire contenant les valeurs (SUMD*0,13) pour chaque ligne de la table PERSON, puis calcule la moyenne de la colonne temporaire.

Le montant du revenu de tous les résidents de Zelenograd peut être calculé à l'aide de la fonction agrégée SUM :

SELECT SUM(SUMD) DE LA PERSONNE

Une fonction d'agrégation peut également être utilisée pour calculer des totaux à partir d'un tableau de résultats obtenu en joignant plusieurs tableaux sources. Par exemple, vous pouvez calculer le montant total des revenus que les résidents ont reçus d'une source appelée « Bourse » :

SÉLECTIONNER SOMME (ARGENT)

DU PROFIT, HAVE_D

OÙ PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE=’Bourse’

Les fonctions d'agrégation MIN() et MAX() vous permettent de trouver respectivement les valeurs les plus petites et les plus grandes d'un tableau. La colonne peut contenir des valeurs numériques ou de chaîne, ou des valeurs de date ou d'heure.

Par exemple, vous pouvez définir :

(a) le revenu total le plus faible perçu par les résidents et l'impôt à payer le plus élevé :

SÉLECTIONNER MIN(SOMME), MAX(SOMME*0,13)

b) les dates de naissance du résident le plus âgé et le plus jeune :

SÉLECTIONNER MIN(RDATE), MAX(RDATE)

c) les noms, prénoms et patronymes des tout premier et dernier résidents de la liste, classés par ordre alphabétique :

SÉLECTIONNER MIN(FIO), MAX(FIO)

Lorsque vous utilisez ces fonctions d'agrégation, vous devez vous rappeler que les données numériques sont comparées à l'aide de règles arithmétiques, que les dates sont comparées de manière séquentielle (les valeurs de date antérieures sont considérées comme plus petites que les valeurs ultérieures) et que les intervalles de temps sont comparés en fonction de leur durée.

Lors de l'utilisation des fonctions MIN() et MAX() avec des données de chaîne, le résultat de la comparaison de deux chaînes dépend de la table de codage de caractères utilisée.

La fonction d'agrégation COUNT() compte le nombre de valeurs dans une colonne de n'importe quel type :

(a) combien y a-t-il d'appartements dans le 1er microquartier ?

SELECT COUNT(ADR) FROM FLAT OÙ ADR LIKE "%, 1_ _-%"

(b) combien de résidents ont des sources de revenus ?

SELECT COUNT(DISTINCT NOM) FROM HAVE_D

(c) combien de sources de revenus les résidents utilisent-ils ?

SELECT COUNT(DISTINCT ID) FROM HAVE_D (Le mot-clé DISTINCT précise que les valeurs non dupliquées dans la colonne sont comptées).

La fonction d'agrégation spéciale COUNT(*) compte les lignes du tableau de résultats, pas les valeurs des données :

(a) combien y a-t-il d'appartements dans le 2ème microquartier ?

SELECT COUNT(*) FROM FLAT OÙ ADR LIKE "%, 2__-%"

(b) de combien de sources de revenus Ivan Ivanovitch Ivanov dispose-t-il ?

SELECT COUNT(*) FROM PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovitch" AND PERSON.NOM=HAVE_D.NOM

(c) combien de résidents vivent dans un appartement à une certaine adresse ?

SELECT COUNT(*) FROM PERSON WHERE ADR="Zelenograd, 1001-45"

Une façon de comprendre comment les requêtes récapitulatives avec des fonctions d’agrégation sont exécutées est de considérer l’exécution de la requête comme divisée en deux parties. Tout d’abord, nous déterminons comment la requête fonctionnerait sans fonctions d’agrégation, renvoyant plusieurs lignes de résultats. Les fonctions d'agrégation sont ensuite appliquées aux résultats de la requête, renvoyant une seule ligne résultante.

Par exemple, considérons la requête complexe suivante : recherchez le revenu total moyen par habitant, la somme du revenu total des résidents et le rendement moyen de la source en pourcentage du revenu total du résident. L'opérateur donne la réponse

SELECT AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID

Sans fonctions d'agrégation, la requête ressemblerait à ceci :

SELECT SUMD, SUMD, MONEY/SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID

et renverrait une ligne de résultats pour chaque résident et source de revenu spécifique. Les fonctions d'agrégation utilisent les colonnes du tableau des résultats de la requête pour produire un tableau à une seule ligne avec les résultats récapitulatifs.

Vous pouvez spécifier une fonction d'agrégation dans la ligne de colonne renvoyée au lieu de n'importe quel nom de colonne. Par exemple, il peut faire partie d'une expression qui ajoute ou soustrait les valeurs de deux fonctions d'agrégation :

SÉLECTIONNER MAX(SUMD)-MIN(SUMD) DE LA PERSONNE

Cependant, une fonction d'agrégation ne peut pas être un argument pour une autre fonction d'agrégation, c'est-à-dire Les fonctions d'agrégation imbriquées sont interdites.

De plus, la liste des colonnes renvoyées ne peut pas utiliser simultanément des fonctions d'agrégation et des noms de colonnes normaux, car cela n'a aucun sens, par exemple :

SELECT FIO, SUM(SUMD) DE PERSONNE

Ici, le premier élément de la liste demande au SGBD de créer un tableau composé de plusieurs lignes et contenant une ligne pour chaque résident. Le deuxième élément de la liste demande au SGBD d'obtenir une seule valeur de résultat, qui est la somme des valeurs de la colonne SUMD. Ces deux instructions se contredisent, entraînant une erreur.

Ce qui précède ne s'applique pas aux cas de traitement de sous-requêtes et de requêtes avec regroupement.

Clause GROUPE PAR(instructions SELECT) vous permet de regrouper des données (lignes) par la valeur d'une colonne ou de plusieurs colonnes ou expressions. Le résultat sera un ensemble de lignes récapitulatives.

Chaque colonne de la liste de sélection doit apparaître dans la clause GROUP BY, à l'exception des colonnes de constantes et d'opérandes de fonction d'agrégation.

Vous pouvez regrouper un tableau selon n'importe quelle combinaison de ses colonnes.

Fonctions d'agrégation sont utilisés pour obtenir une seule valeur totale à partir d’un groupe de lignes. Toutes les fonctions d'agrégation effectuent des calculs sur un seul argument, qui peut être une colonne ou une expression. Le résultat de tout calcul de fonction d'agrégation est une valeur constante affichée dans une colonne de résultat distincte.

Les fonctions d'agrégation sont spécifiées dans la liste de colonnes d'une instruction SELECT, qui peut également contenir une clause GROUP BY. S'il n'y a pas de clause GROUP BY dans l'instruction SELECT et que la liste des colonnes de sélection contient au moins une fonction d'agrégation, elle ne doit pas contenir de colonnes simples. D'un autre côté, la liste de sélection de colonnes peut contenir des noms de colonnes qui ne sont pas des arguments de la fonction d'agrégation si ces colonnes sont des arguments de la clause GROUP BY.

Si la requête contient une clause WHERE, les fonctions d'agrégation calculent la valeur des résultats de la sélection.

Fonctions d'agrégation MIN et MAX calculez respectivement la plus petite et la plus grande valeur de la colonne. Les arguments peuvent être des nombres, des chaînes et des dates. Toutes les valeurs NULL sont supprimées avant le calcul (c'est-à-dire qu'elles ne sont pas prises en compte).

Fonction d'agrégation SOMME calcule la somme totale des valeurs d'une colonne. Les arguments ne peuvent être que des nombres. L'utilisation du paramètre DISTINCT élimine toutes les valeurs en double dans une colonne avant d'appliquer la fonction SUM. De même, toutes les valeurs NULL sont supprimées avant d'appliquer cette fonction d'agrégation.

Fonction d'agrégation AVG renvoie la moyenne de toutes les valeurs d'une colonne. Les arguments ne peuvent également être que des nombres et toutes les valeurs NULL sont supprimées avant évaluation.

Fonction d'agrégation COUNT a deux formes différentes :

• COUNT(col_name) - compte le nombre de valeurs dans la colonne col_name, les valeurs NULL ne sont pas prises en compte
• COUNT(*) - compte le nombre de lignes dans le tableau, les valeurs NULL sont également prises en compte

Si la requête utilise le mot-clé DISTINCT, toutes les valeurs en double dans la colonne sont supprimées avant d'utiliser la fonction COUNT.

COUNT_BIG fonction similaire à la fonction COUNT. La seule différence entre eux est le type de résultat qu'ils renvoient : la fonction COUNT_BIG renvoie toujours des valeurs BIGINT, tandis que la fonction COUNT renvoie des valeurs de données INTEGER.

DANS AVOIR une offre définit une condition qui s'applique à un groupe de lignes. Elle a la même signification pour les groupes de lignes que la clause WHERE pour le contenu de la table correspondante (WHERE s'applique avant le regroupement, HAVING après).

La leçon couvrira le sujet du renommage par SQL d'une colonne (champs) en utilisant le mot de service AS ; Le sujet des fonctions d'agrégation dans SQL est également abordé. Des exemples spécifiques de demandes seront discutés

Les noms de colonnes dans les requêtes peuvent être renommés. Cela rend les résultats plus lisibles.

En SQL, renommer les champs implique d'utiliser Mot-clé AS, qui est utilisé pour renommer les noms de champs dans les jeux de résultats

Syntaxe:

SÉLECTIONNER<имя поля>COMME<псевдоним>DEPUIS...

Regardons un exemple de renommage en SQL :

Exemple de base de données « Institut » : Affichez les noms des enseignants et leurs salaires, pour les enseignants dont le salaire est inférieur à 15 000, renommez le champ zarplata en "salaire_faible"

✍Solution :

Renommer les colonnes en SQL est souvent nécessaire lors du calcul des valeurs associées à plusieurs champs tableaux. Regardons un exemple :

Exemple de base de données « Institut » : Depuis la table des enseignants, affichez le champ nom et calculez le montant du salaire et de la prime en nommant le champ "salaire_bonus"

✍Solution :

1 2	SELECT nom, (zarplata+ prime) AS zarplata_premia FROM enseignants ;

SELECT nom, (zarplata+premia) AS zarplata_premia FROM enseignants ;

Résultat:

Fonctions d'agrégation en SQL

Pour obtenir des valeurs totales et évaluer des expressions, des fonctions d'agrégation en SQL sont utilisées :

Toutes les fonctions d'agrégation renvoient une valeur unique.

Les fonctions COUNT, MIN et MAX s'appliquent à tout type de données.

Les fonctions SUM et AVG sont utilisées uniquement pour les champs numériques.
Il existe une différence entre les fonctions COUNT(*) et COUNT() : la seconde ne prend pas en compte les valeurs NULL lors du calcul.

Important: Lorsque vous travaillez avec des fonctions d'agrégation en SQL, un mot de fonction est utilisé COMME

Exemple de base de données « Institut » : Obtenez la valeur du salaire le plus élevé parmi les enseignants, affichez le résultat sous forme "salaire_maximum"

✍Solution :

SELECT MAX (zarplata) AS max_salary FROM enseignants ;

SELECT MAX(zarplata) AS max_salary FROM enseignants ;

Résultats:

Examinons un exemple plus complexe d'utilisation de fonctions d'agrégation dans SQL.

✍Solution :

Clause GROUP BY en SQL

L'opérateur group by dans SQL est généralement utilisé en conjonction avec des fonctions d'agrégation.

Les fonctions d'agrégation sont exécutées sur toutes les lignes de requête résultantes. Si la requête contient une clause GROUP BY, chaque ensemble de lignes spécifié dans la clause GROUP BY constitue un groupe et les fonctions d'agrégation sont exécutées. pour chaque groupe séparément.

Regardons un exemple avec la table des leçons :

Exemple:

Important: Ainsi, en utilisant GROUP BY, toutes les lignes de sortie de requête sont divisées en groupes caractérisés par les mêmes combinaisons de valeurs dans ces colonnes (c'est-à-dire que les fonctions d'agrégation sont exécutées sur chaque groupe séparément).

Il convient de considérer que lors du regroupement selon un champ contenant des valeurs NULL, tous ces enregistrements tomberont dans un seul groupe.

Pour différents types d'imprimantes, déterminez leur coût moyen et leur quantité (c'est-à-dire séparément pour le laser, le jet d'encre et la matrice). Utilisez des fonctions d'agrégation. Le résultat devrait ressembler à ceci :

Avoir une instruction SQL

La clause HAVING dans SQL est nécessaire pour vérifier les valeurs, qui sont obtenus à l'aide de la fonction d'agrégation après regroupement(après avoir utilisé GROUP BY). Un tel chèque ne peut pas être contenu dans une clause WHERE.

Exemple: Magasin informatique DB. Calculez le prix moyen des ordinateurs dotés de la même vitesse de processeur. Effectuez les calculs uniquement pour les groupes dont le prix moyen est inférieur à 30 000.