Les regroupements de données

Extraction de données groupées

Les requêtes examinées jusqu’à maintenant produisent des lignes qui sont généralement une correspondance une pour une avec les lignes d’une table de la clause FROM

Nous allons maintenant examiner comment il est possible d’extraire d’une table, ou d’une jointure, des informations sur les concepts latents dans ces tables.

Le concept

Considérons la table Clients.

On peut y voir, virtuellement du moins, des groupes de clients qui ont la même ville ou la même catégorie.

On pourrait dire que certains clients habitent la même ville, ou appartiennent à la même catégorie.

SELECT numero, nom, prenom, ville, categorie
FROM Clients;

Résultat :

Clients
numero	nom	prenom	ville	categorie
1	Lebreton	Éléonore	Meylan	A1
2	Vasseur	Georges	Grenoble	B2
3	Da Costa	Léon	Paris	C3
4	Rey	Michel	Vizille	B1
5	Weiss	Dominique	Lille	B2
6	Berlioz	Diane	Paris
7	Lefevre	Jean	Lyon	C1
9	Barda	Jérôme	Paris	C1
8	Jacques	Chaoui	Paris	C1

 

Le regroupement de données, réalisé par la clause GROUP BY, permet de mettre en évidence ces groupes et d’extraire des informations sur eux.

GROUP BY

Par exemple, on souhaite obtenir le nombre de clients par ville, ainsi que la valeur moyenne de leur compte.

SELECT
  ville,
  COUNT(*) AS 'Nombre de clients',
  AVG(compte) AS 'Moyenne des comptes'
FROM Clients
GROUP BY ville ;

Résultat :

Moyennes des clients par ville
ville	Nombre de clients	Moyenne des comptes
Grenoble	1	1903
Lille	1	0
Lyon	1	-1000
Meylan	1	10000
Paris	4	29461
Vizille	1	3251

HAVING

Des conditions de sélection peuvent être imposées aux groupes sélectionnés.

Elles sont exprimées dans une clause HAVING pour éviter toute confusion avec la clause WHERE

Par exemple, si on reprend la requête précédente, on peut ajouter une condition pour ne retenir que les villes avec au moins 3 clients.

SELECT
  ville,
  COUNT(*) AS 'Nombre de clients',
  AVG(compte) AS 'Moyenne des comptes'
FROM Clients
GROUP BY ville
HAVING COUNT(*) >= 3 ;

Résultat :

Moyennes des clients par ville qui ont au moins 3 clients
ville	Nombre de clients	Moyenne des comptes
Paris	4	29461

Remarque

La condition exprimée par la clause HAVING peut porter sur les éléments cités dans la clause SELECT, mais aussi sur toute autre fonction d’agrégation calculable sur chaque groupe.

Exemples

Exemple 1

Donner la liste des clients ayant commandé au moins 2 fois le produit numéro 1 (le produit numéro 1 est le T-shirt)

Réalisons cette requête par étapes :

1. On sélectionne les lignes de la table Commandes en les regroupant par client :

SELECT numeroClient , COUNT(*)
FROM Commandes
GROUP BY numeroClient;

Résultat :

Commandes regroupées par client
numeroClient	COUNT(*)
1	2
3	1
4	2
5	2

Exemple 1

2. On ne retient que les groupes d’un moins 2 commandes :

SELECT numeroClient , COUNT(*)
FROM Commandes
GROUP BY numeroClient
HAVING COUNT(*) >= 2;

Résultat :

Commandes regroupées par client ayant au moins 2 commandes
numeroClient	COUNT(*)
1	2
4	2
5	2

Exemple 1

3. Finalement, on ne considère, avant groupement, que les commandes spécifiant le produit numéro 1 :

SELECT numeroClient , COUNT(*)
FROM Commandes
WHERE numero IN (SELECT numeroCommande FROM Details WHERE numeroProduit = 1 )
GROUP BY numeroClient
HAVING COUNT(*) >= 2;

Résultat :

Commandes regroupées par client ayant au moins 2 commandes du produit numéro 1
numeroClient	COUNT(*)
4	2
5	2

Exemple 1

4. Pour aller plus loin, on pourrait effectuer une jointure supplémentaire pour sélectionner le produit non pas par son numéro, mais par son nom :

SELECT numeroClient, COUNT(*)
FROM Commandes
WHERE numero IN (SELECT numeroCommande FROM Details, Produits P WHERE Details.numeroProduit = P.numero AND P.nom = 'T-shirt' )
GROUP BY numeroClient
HAVING COUNT(*) >= 2;

Résultat :

Commandes regroupées par client ayant au moins 2 commandes du produit T-Shirt
numeroClient	COUNT(*)
4	2
5	2

Exemple 1

Syntaxes alternatives

On pourrait aussi formuler cette requête en utilisant des jointures explicites :

SELECT P.nom, numeroClient , COUNT(*)
FROM Commandes C, Details D, Produits P
WHERE C.numero = D.numeroCommande
AND D.numeroProduit = P.numero
AND P.nom = 'T-shirt'
GROUP BY numeroClient
HAVING COUNT(*) >= 2;

Résultat :

Commandes regroupées par client ayant au moins 2 commandes du produit T-Shirt
nom	numeroClient	COUNT(*)
T-shirt	4	2
T-shirt	5	2

Exemple 1

Et pour terminer, une version avec la syntaxe JOIN :

SELECT numeroClient , COUNT(*)
FROM Commandes C
JOIN Details D ON C.numero = D.numeroCommande
JOIN Produits P ON D.numeroProduit = P.numero
WHERE P.nom = 'T-shirt'
GROUP BY numeroClient
HAVING COUNT(*) >= 2;

Résultat :

Commandes regroupées par client ayant au moins 2 commandes du produit T-Shirt
numeroClient	COUNT(*)
4	2
5	2

Exemple 2

Exemple 2

On souhaite obtenir la quantité totale commandée du produit numéro 1, toutes commandes confondues :

SELECT numeroProduit, SUM(quantite)
FROM Details
WHERE numeroProduit = 1
GROUP BY numeroProduit;

Résultat :

Quantité totale commandée du produit numéro 1
numeroProduit	SUM(quantite)
1	27

Exemple 3

Pour chaque client de Lille, donner le montant total de ses commandes

SELECT numeroClient, SUM(quantite * prix)
FROM Commandes C, Details D, Produits P, Clients CL
WHERE C.numero = D.numeroCommande
AND D.numeroProduit = P.numero
AND C.numeroClient = CL.numero
AND CL.ville = 'Lille'
GROUP BY numeroClient;

Résultat :

Commandes de Lille
numeroClient	SUM(quantite * prix)
5	480

Exemple 4

Donner la quantité qui reste en stock, si on déduit les quantités commandées

SELECT
numero, nom,
stock - ( SELECT SUM(quantite)
          FROM Details, Produits
          WHERE numeroProduit = Produits.numero) AS 'Quantité restante'
FROM Produits
GROUP BY numero;

Résultat :

Quantité restante en stock
numero	nom	Quantité restante
1	T-shirt	-9
2	Pantalon	11
3	Chaussures	61
4	Casquette	-29
5	Sac à dos	-34

Multi-colonnes

1. Le critère de groupement peut inclure plusieurs noms de colonne
2. L’ordre des colonnes est indifférent

Donner pour chaque ville le montant total commandé

SELECT ville, SUM(quantite * prix) AS 'montant'
FROM Clients CL, Commandes C, Details D, Produits P
WHERE CL.numero = C.numeroClient
AND C.numero = D.numeroCommande
AND D.numeroProduit = P.numero
GROUP BY ville;

Résultat :

Montant commandé par ville
ville	montant
Lille	480
Meylan	350
Paris	495
Vizille	260

Donner pour chaque ville et produit le montant total commandé

SELECT ville, P.numero AS 'produit', SUM(quantite * prix) AS 'montant'
FROM Clients CL, Commandes C, Details D, Produits P
WHERE CL.numero = C.numeroClient
AND C.numero = D.numeroCommande
AND D.numeroProduit = P.numero
GROUP BY ville, P.numero;

Résultat :

Montant commandé par ville et produit
ville	produit	montant
Lille	1	300
Lille	2	60
Lille	3	120
Meylan	1	75
Meylan	2	60
Meylan	3	120
Meylan	4	15
Meylan	5	80
Paris	1	100
Paris	2	60
Paris	3	240
Paris	4	15
Paris	5	80
Vizille	1	200
Vizille	2	60

Multi-colonnes

Syntaxe JOIN

SELECT ville, P.numero AS 'produit', SUM(quantite * prix) AS 'montant'
FROM Clients CL
JOIN Commandes C ON CL.numero = C.numeroClient
JOIN Details D ON C.numero = D.numeroCommande
JOIN Produits P ON D.numeroProduit = P.numero
GROUP BY ville, P.numero;

Calculs

Le critère de groupement peut aussi inclure une expression de calcul quelconque

Donner la liste des clients en fonction de la première lettre de leur catégorie

SELECT substr(categorie, 1, 1) AS 'Catégorie', COUNT(*) AS 'Nombre de clients'
FROM Clients
GROUP BY substr(categorie, 1, 1);

Résultat :

Clients par catégorie
Catégorie	Nombre de clients
	1
A	1
B	3
C	4

Regrouper les clients selon leurs valeurs de COMPTE par intervalle de 1.000

SELECT 'de' , abs(compte/1000)*1000 AS MIN, 'à' , abs(compte/1000)*1000 + 999 AS MAX, COUNT(*) AS N
FROM Clients
GROUP BY abs(compte/1000);

Résultat :

valeurs de COMPTE par intervalle de 1.000
'de'	MIN	'à'	MAX	N
de	0	à	999	1
de	1000	à	1999	1
de	1500	à	2499	1
de	1750	à	2749	1
de	1903	à	2902	1
de	2000	à	2999	1
de	3251	à	4250	1
de	10000	à	10999	1
de	123094	à	124093	1

Jointures

L’extraction de données groupées est à définir avec précaution avec des jointures

Donner pour chaque ville, la somme des comptes des clients et le nombre de commandes.

On serait tenté d’écrire :

SELECT ville, SUM(compte), COUNT(*)
FROM Clients C, Commandes M
WHERE C.numero = M.numeroClient
GROUP BY ville;

Résultat :

somme des comptes des clients et le nombre de commandes
ville	SUM(compte)	COUNT(*)
Lille	0	2
Meylan	20000	2
Paris	-1500	1
Vizille	6502	2

Ce résultat, en apparence correct, est pourtant erroné (indépendamment du fait que les clients sans commande ne sont pas repris) :

• Le résultat de la jointure n’est pas des clients mais des commandes
  • Rappel : le résultat d’une jointure représente des entités de la table contenant la clé étrangère
• En particulier, le compte d’un client est compté autant de fois qu’il a passé une commande
• Le calcul de la somme des comptes s’effectue donc sur des ensembles de commandes et non des clients
• Pour répondre correctement à la question il faudra procéder en deux étapes

Omission

Il est possible d’éviter la clause GROUP BY lorsque le concept latent dans une table est explicitement représenté par une autre table, et que le regroupement ne sert qu’à la sélection

Donner les produits dont plus de 2 unités ont été commandées en 2024

SELECT numero, nom, stock
FROM Produits
WHERE numero IN (SELECT numeroProduit FROM Details WHERE quantite > 2)
GROUP BY numero;

Résultat :

Clients par intervalle de compte
numero	nom	stock
1	T-shirt	30

est équivalente à

SELECT numero, nom, stock
FROM Produits
WHERE numero IN (SELECT numeroProduit FROM Details WHERE quantite > 2);

Résultat :

Clients par intervalle de compte
numero	nom	stock
1	T-shirt	30

Conclusion

• Les regroupements de données permettent d’extraire des informations sur des concepts latents dans les tables.
• Les clauses GROUP BY et HAVING permettent de réaliser ces regroupements.
• Les jointures doivent être utilisées avec précaution dans les requêtes de regroupement.
• Il est possible d’omettre la clause GROUP BY lorsque le concept latent est explicitement représenté par une autre table.