1.5  Agrégation de données

• Fonctions d’agrégation
• Groupage

1.5.1  Fonctions d’agrégation

Exemple introductif

Nous voulons connaître le nombre de lignes de table produit. Deux façons de procéder :

1. Solution moche

   SELECT * FROM PRODUIT;
   
   numprod nomprod
   1 Bocal de cornichons
   2 Tube de dentifrice
   3 Flacon de lotion anti-escarres
   4 Déodorant fraîcheur 96 heures
   
   

   On a la réponse avec le nombre de lignes sélectionnées.

2. Solution belle

   SELECT count(*) FROM PRODUIT;
   
   count(*)
   4
   
   

   La réponse est le résultat de la requête.

Définition

Une fonction d’agrégation retourne une valeur calculée sur toutes les lignes de la requête (nombre, moyenne...). Nous allons utiliser les suivantes :

• COUNT(col) : retourne le nombre de lignes dont le champ col est non NULL.
• AVG(col) : retourne la moyenne des valeurs col sur toutes les lignes dont le champ col est non NULL.
• MAX(col) : retourne la plus grande des valeurs col sur toutes les lignes dont le champ col est non NULL.
• MIN(col) : retourne la plus petite des valeurs col sur toutes les lignes dont le champ col est non NULL.
• SUM(col) : retourne la somme des valeurs col sur toutes les lignes dont le champ col est non NULL.

Exemples d’utilisation

L’exemple suivant retourne le prix du produit proposé au prix maximal.

SELECT MAX(prix)
FROM PROPOSER;

MAX(prix)
3

Il est possible de renommer la colonne MAX(prix), en utilisant le mot clé AS :

SELECT MAX(prix) AS PRIX_MAXIMAL
FROM PROPOSER;

PRIX_MAXIMAL
3

Les requêtes suivantes récupèrent le nom du fournisseur proposant l’article ’Bocal de cornichons’ au prix le moins élevé :

SELECT MIN(prix) AS PRIX_MINIMUM
FROM PROPOSER PR, PRODUIT P
WHERE PR.numprod = P.numprod
AND nomprod = 'Bocal de cornichons';

PRIX_MINIMUM
2

SELECT nomfou 
FROM FOURNISSEUR F, PROPOSER PR, PRODUIT P
WHERE F.numfou = PR.numfou
AND PR.numprod = P.numprod
AND nomprod = 'Bocal de cornichons'
AND prix = 1;

Il est possible de faire cela avec une seule requête en récupérant le prix minimum dans une requête imbriquée. Mais cela sera pour un cours ultérieur.

Compléments sur COUNT

On récupère le nombre de ligne retournées par une requête en utilisant COUNT(*). Par exemple, si on souhaite connaître le nombre de produits proposés par le fournisseur ’Bocaux Gérard’ :

SELECT COUNT(*) AS NB_PROD
FROM FOURNISSEUR F, PROPOSER P
WHERE F.numfou = P.numfou
AND nomfou = 'Bocaux Gérard';

NB_PROD
1

On aurait aussi pu saisir :

SELECT COUNT(numprod) AS NB_PROD
FROM FOURNISSEUR F, PROPOSER P
WHERE F.numfou = P.numfou
AND nomfou = 'Bocaux Gérard';

NB_PROD
1

Pour connaître le nombre de produits proposés, c’est à dire dont le numprod a une occurence dans la table PROPOSER, on procède de la façon suivante :

SELECT COUNT(DISTINCT numprod) AS NB_PRODUITS_PROPOSES
FROM PROPOSER;

NB_PRODUITS_PROPOSES
3

Le DISTINCT nous sert à éviter qu’un même produit proposé par des fournisseurs différents soit comptabilisé plusieurs fois.

1.5.2  Groupage

L’instruction GROUP BY

Les opérations d’agrégation considérées jusqu’à maintenant portent sur la totalité des lignes retournées par les requêtes, l’instruction GROUP BY permet de former des paquets à l’intérieur desquels les données seront agrégées. Cette instruction s’utilise de la manière suivante

SELECT ...
FROM ...
WHERE...
GROUP BY <liste_colonnes>
ORDER BY ...

La liste des colonnes sert de critère pour répartir les lignes dans des paquets de lignes. Si par exemple nous souhaitons afficher la liste des nombres de produits proposés par chaque fournisseur :

SELECT nomfou, COUNT(*) AS NB_PRODUITS_PROPOSES
FROM FOURNISSEUR F, PROPOSER P
WHERE F.numfou = P.numfou
GROUP BY nomfou;

nomfou NB_PRODUITS_PROPOSES
Bocaux Gérard 1
Paramédical Gisèle 3

L’instruction HAVING

Supposons que de la requête précédente, nous ne souhaitions garder que les lignes pour lesquelles la valeur NB_PRODUITS_PROPOSES est égale à 1. Ajouter une condition dans WHERE serait inutile, le filtrage occasionné par WHERE est effectué avant l’agrégation. Il nous faudrait une instruction pour n’inclure que des groupes de données répondant certains critères. L’instruction utilisée pour ce faire est HAVING. Son utilisation est la suivante :

SELECT ...
FROM ...
WHERE ...
GROUP BY...
HAVING <condition>
ORDER BY ...

Par exemple,

SELECT nomfou, COUNT(numprod) AS NB_PRODUITS_PROPOSES
FROM FOURNISSEUR F, PROPOSER P
WHERE F.numfou = P.numfou
GROUP BY nomfou
HAVING COUNT(numprod) = 1
ORDER BY nomfou DESC;

nomfou NB_PRODUITS_PROPOSES
Bocaux Gérard 1

Affichons les noms des fournisseurs qui ont livré strictement plus d’un produit différent (toutes livraisons confondues),

SELECT nomfou
FROM FOURNISSEUR F, DETAILLIVRAISON D
WHERE F.numfou = D.numfou
GROUP BY F.numfou, nomfou
HAVING count(DISTINCT numprod) > 1;

nomfou
Paramédical Gisèle