![]()
La requête
de regroupement est un important outil d'analyse et de synthèse.
Pour cette raison, nous lui consacrons un chapitre entier. Le terme
"Requête de regroupement" est le plus courant ,
mais on rencontre aussi "Requête d'agrégation", qui est synonyme.
Les requêtes de regroupement sont
très utilisées dans l'analyse des résultats comptables et financiers. Comme
nous le verrons dans le chapitre suivant, elles sont aussi utilisées pour le
comptage et l'élimination des doublons.
La notion de regroupement déborde
le cadre des seules requêtes. Nous la retrouverons également dans les états et
les formulaires.
Comme pour les autres chapitres de
ce tutoriel (ou tutorial, ou cours en ligne), nous utilisons le SGBD Access
comme support pratique.
Créons une table intitulée "Résultats",
contenant le chiffre d'affaires journalier d'une entreprise possédant trois
agences. Le champ "Date" est du type "Date/Heure", le champ
"Agence" du type texte (10 caractères), et le champ CA (Chiffre
d'Affaires) du type monétaire (format euro). Introduisons quelques valeurs dans
la table, qui prend alors l'aspect représenté ci-dessous.
|
Date |
Agence |
CA |
|
06/01/2003 |
Nord |
927,02 € |
|
06/01/2003 |
Sud |
1 098,46 € |
|
06/01/2003 |
Est |
561,29 € |
|
07/01/2003 |
Nord |
1 385,55 € |
|
07/01/2003 |
Est |
681,09 € |
|
07/01/2003 |
Sud |
1 401,56 € |
Pour juger les performances de
l'entreprise, ces données brutes sont malcommodes. Un décideur a besoin du
chiffre d'affaires non seulement au jour le jour, mais aussi à la semaine, au mois
et pour l'exercice annuel. Il le veut toutes agences confondues pour juger des
performances de l'entreprise. Il le veut aussi agence par agence, pour juger
des performances de ces dernières (le responsable de l'agence Est va prendre un
savon). Et il ne veut pas être obligé de sortir sa calculette pour regrouper
les chiffres qui l'intéressent ; le regroupement doit être effectué par le
SGBD.
Pour l'exemple très simple que
nous avons choisi, deux regroupements du chiffre d'affaires sont
possibles :
·
par date, en sommant les CA des trois agences, de manière à obtenir
le CA quotidien de l'entreprise. Dans ce cas, la notion d'agence
s'efface ;
·
par agence, en sommant les CA de chaque agence sur l'ensemble des dates
mentionnées dans la table. Dans ce cas, la notion de date s'efface.
Quand peut-on envisager
d'effectuer un regroupement dans une table ?
·
Quand il existe un champ possédant des doublons. Dans
l'exemple ci-dessus, il serait impossible de regrouper par date si chaque
valeur de la date n'apparaissait qu'une seule fois. De même, il serait
impossible d'envisager le regroupement par agence, si le nom de chaque agence
n'apparaissait pas de manière répétée.
Quelle opération peut-on
envisager quand on effectue un regroupement ? La nature de cette opération
dépend du type des données à regrouper :
·
des données numériques ou monétaires se prêtent à des
opérations arithmétiques (somme, moyenne, minimum, maximum), statistiques
(variance et écart-type), voire mathématiques. Tout dépend des possibilités
offertes par le SGBD ;
·
des données de type texte se prêtent au classement et au
comptage (la concaténation n'est pas prévue).
Nous voyons tout de suite qu'une
requête de regroupement met en jeu le plus souvent deux colonnes :
·
une colonne sur laquelle s'effectue le regroupement (elle doit contenir des
doublons). On peut effectuer le regroupement sur plusieurs colonnes lorsqu'il
existe des doublons s'étendant sur plusieurs colonnes ;
·
une colonne sur laquelle s'effectue une opération (somme, ou moyenne, ou etc.).
La mise au point d'une requête de
regroupement peut s'avérer délicate, et il faut garder en mémoire les
observations suivantes :
·
Regroupement. Le SGBD permet d'effectuer le
regroupement sur plusieurs colonnes, mais la probabilité pour qu'il existe des
doublons (sur plusieurs colonnes) diminue très vite avec le nombre de ces
dernières. Dans beaucoup de cas rencontrés en pratique, on effectue le
regroupement sur une colonne seulement ;
·
Opérations. On peut envisager d'effectuer des
opérations sur plusieurs colonnes, si elles s'y prêtent. Dans l'exemple
ci-dessus, le CA pourrait être ventilé sur deux colonnes (l'une pour les biens,
l'autre sur les services, par exemple), que nous pourrions sommer séparément ;
·
Requête multi-table. Une requête de regroupement peut
impliquer plusieurs tables liées par des relations, mais il est alors beaucoup
plus facile de commettre des erreurs de conception. Il est donc prudent
d'utiliser d'abord une requête de sélection pour regrouper dans une même table
les données dont on a besoin, avant d'appliquer la requête de regroupement,
même si cela risque d'augmenter un peu le temps d'exécution.
Il résulte de ces considérations qu'une
requête de regroupement met généralement en jeu un nombre très restreint de
champs. En fait, il est fortement conseillé de commencer la mise au point d'une
requête de regroupement sur deux colonnes seulement, et que ces deux
colonnes appartiennent à la même table (ou à la même feuille de
données).
Nous allons créer le premier
regroupement envisagé au paragraphe précédent (calcul du CA quotidien de
l'entreprise). Pour ne pas nous tromper, nous allons opérer de manière méthodique.
Première étape. Elle consiste à ouvrir la fenêtre de définition
d'une requête, et à y introduire la table sur laquelle on veut effectuer
l'opération de regroupement (ici "Résultats").
Seconde étape. Elle consiste à introduire dans la grille le champ
sur lequel s'effectue le regroupement. Comme nous cherchons à calculer des
CA quotidiens, ce champ ne peut être que la date. Nous
introduisons donc le champ "Date" dans la grille de création de la
requête.
Troisième étape. Il faut signifier au SGBD que la requête implique
un regroupement sur le champ "Date". Pour ce faire, nous
cliquons sur l'icône
"Totaux". Une nouvelle
ligne, baptisée "Opération :", apparaît dans la grille de
définition de la requête, entre "Table :" et
"Tri :" (figures ci-dessous). La valeur par défaut, pour le
champ "Date", est justement "Regroupement" (si cette
valeur n'apparaît pas, nous cliquons sur la ligne et nous sélectionnons
"Regroupement" dans la liste déroulante). Ainsi, le regroupement sera
effectué sur la date.
Quatrième étape. Il faut maintenant introduire le champ sur lequel
s'effectue l'opération liée au regroupement. Dans le présent exemple,
l'opération consiste à sommer les CA de chaque agence. Nous introduisons donc
le champ "CA" dans la grille.
Cinquième étape. Il faut indiquer au SGBD à quelle opération
il doit procéder sur le champ "CA". Nous cliquons sur la ligne
"Opération :", nous utilisons la liste déroulante pour remplacer
"Regroupement" (qui s'est inscrit par défaut) par "Somme".
La requête se présente ainsi comme
le montre la figure ci-dessous à gauche.

Quand nous basculons en mode feuille
de données, nous obtenons le résultat représenté sur la figure ci-dessous (à
gauche). Nous vérifions que le SGBD a bien calculé, pour chaque date, la somme
des chiffres d'affaires des trois agences.

De la même façon, nous pouvons
regrouper le chiffre d'affaires par agence. La requête et son résultat sont
représentés dans les deux figures ci-dessus (à droite). Cette fois le SGBD a
calculé, pour chaque agence, la somme des chiffres d'affaires quotidiens, pour
les deux dates figurant dans la table.
Remarque 1 : l'icône
fonctionne
comme un commutateur. Si nous cliquons dessus alors que la ligne
"Opération :" est présente, celle-ci disparaît, et vice versa.
Remarque 2 : le nom du champ
("SommeDeCA") a été attribué par le SGBD,
mais on peut le changer à volonté dans la grille de création de la requête.
Pour l'appeler "CA agence", par exemple, il faut remplacer
"CA" sur la ligne "Champ :" par "CA
agence: CA".
Dans le paragraphe précédent, nous
avons créé des requêtes impliquant un regroupement sur une colonne donnée, ce
qui nous a permis de calculer le chiffre d'affaires par date (pour chacune des
trois agences) ou par agence (pour chacune des deux dates). Mais nous pouvons
également avoir besoin du chiffre d'affaire total, pour toutes les agences et
toutes les dates de la table. Pour ce faire, nous créons de nouveau une requête
de regroupement, mais sans déclarer sur quel champ nous regroupons,
comme le montre la figure ci-dessous à gauche. Vous noterez que nous avons
utilisé la syntaxe qui nous permet d'imposer le nom du champ (CA_total).

Le résultat figure ci-dessus à
droite ; vous pourrez vérifier qu'il est bien exact. Bien entendu, vous pouvez
utiliser d'autres fonctions que la somme dans ces opérations effectuées
verticalement. L'étude de ces fonctions fait l'objet du paragraphe suivant.
Ainsi vous pouvez effectuer des
opérations sur les colonnes d'une table, comme vous le feriez dans un tableur.
La différence avec un tableur -- outre la syntaxe -- provient du fait que le
résultat ne peut pas être enregistré dans la table. Il apparaît
systématiquement dans une nouvelle "feuille de données" volatile, à
moins que vous ne demandiez que la requête crée une nouvelle table.
La fonction "Somme" (qui ne s'applique qu'aux données
numériques et monétaires) n'est pas la seule qui puisse être utilisée lors du
regroupement. Dans Access, on trouve également les fonctions suivantes :
·
Moyenne : calcule la moyenne. S'applique
uniquement aux données numériques ou monétaires ;
·
Min : retient seulement la valeur la plus basse.
S'applique aussi au texte (classement alphabétique) ;
·
Max : retient seulement la valeur la plus haute.
S'applique aussi au texte (classement alphabétique) ;
·
ÉcartType : calcule l'écart type. S'applique
uniquement aux données numériques ou monétaires ;
·
Var : calcule la variance. S'applique uniquement aux
données numériques ou monétaires ;
·
Premier : retient la première valeur
rencontrée (en parcourant la table du haut en bas). S'applique aussi au
texte ;
·
Dernier : retient la dernière valeur
rencontrée (en parcourant la table du haut en bas). S'applique aussi au
texte ;
·
Compte : compte le nombre de doublons dans
le regroupement. Nous dédierons un chapitre particulier à l'étude de cette
fonction, qui s'applique à tous les types de données.
La liste des fonctions utilisables
lors du regroupement varie d'un SGBD à l'autre. La somme, la moyenne et le
comptage sont présents dans tous les SGBD.
Si nous essayons de faire opérer
une fonction sur un type de données incompatible, le SGBD Access affiche le
message d'erreur suivant : "Type de données incompatible dans
l'expression du critère". Et qu'en termes galants ces choses-là sont
dites ! Évidemment, il ne s'agit pas d'un critère, mais d'une fonction.
On trouvera ci-dessous des
exemples illustrant l'application de ces différentes fonctions, à l'exception
de l'écart type et de la variance, qui n'auraient guère de sens vu le petit
nombre de données (trois par regroupement) contenues dans la table "Résultats"
qui nous sert d'exemple.
|
Date |
MoyenneDeCA |
|
Agence |
MoyenneDeCA |
|
06/01/2003 |
862,26 € |
Est |
621,19 € |
|
|
07/01/2003 |
1 156,07 € |
Nord |
1 156,29 € |
|
|
|
Sud |
1 250,01 € |
||
Une requête de regroupement peut
être filtrée avant et /ou filtrée après le regroupement. Il faut
indiquer au SGBD dans quel cas on entend se trouver.
Le filtrage après
regroupement. Ce filtrage ne pose
pas de problème particulier, puisque les champs sur lesquels nous pouvons
opérer sont présents dans la grille de définition de la requête, et que cette
dernière comporte une ligne "Critères :". Nous y inscrivons les
critères de filtrage, en respectant les règles de syntaxe propre au SGBD, comme
nous avons appris à le faire au chapitre précédent.
Dans la colonne "CA",
par exemple, le critère :
>2000
limitera l'affichage aux regroupements conduisant à des
chiffres d'affaires supérieurs à 2.000 euros. Dans la colonne
"Agence", le critère :
Comme "Nord"
limitera le calcul du chiffre d'affaire cumulé par agence à
la seule agence Nord. Dans la colonne "Date", le critère :
<#08/01/2003#
limitera le calcul du chiffre d'affaire cumulé par date aux
jours précédant le 8 janvier 2003.
Le filtrage
avant regroupement. Pour filtrer une
requête avant regroupement, nous procédons ainsi :
·
nous introduisons le champ dans la grille ;
·
nous cliquons sur la ligne "Opération :", où
"Regroupement" s'est inscrit par défaut ;
·
nous sélectionnons "Où" tout en bas de la liste déroulante.
Nous constatons alors que la coche disparaît de la case qui se trouve sur la
ligne "Afficher :". Attention ! si nous oublions d'inscrire
"où" sur la ligne "Opération :", la requête ne
fonctionnera pas ;
·
nous introduisons le(s) critère(s) de filtrage sur la ligne
"Critères :".
Avant de filtrer une requête de
regroupement, il faut bien réfléchir au résultat que l'on veut obtenir.
Car un même critère, appliqué au même champ, ne donnera pas le même résultat
suivant qu'il opère avant ou après le regroupement. Pour le
montrer, nous avons créé deux exemples dans lesquels le filtre consiste à ne
conserver un CA que s'il est supérieur à 1000 euros, et où le regroupement
s'effectue sur l'agence.
·
Premier exemple (figures de gauche). Nous
appliquons le filtre aux CA regroupés par agence, c'est à dire que nous
ne les affichons que s'ils dépassent 1000 euros ;
·
Deuxième exemple (figures de droite). Nous
appliquons le filtre aux CA quotidiens, et nous ne les prenons en compte
que s'ils dépassent 1000 euros.
Les requêtes et leurs résultats
sont représentés sur les figures ci-dessous.


Si le critère porte sur le champ
de regroupement, l'application du filtre avant ou après le regroupement donne
le même résultat. Faites l'expérience !
La requête de regroupement est un
outil fort utile. Mais, pour l'utiliser correctement, il faut prendre les
précautions suivantes :
·
pour effectuer des opérations sur les colonnes, il ne faut
pas préciser sur quel champ s'opère le regroupement ;
·
il faut appliquer le regroupement à un seul champ, ou à
un très petit nombre de champs, sinon il n'y a plus de regroupement
possible et la requête, bien évidemment, ne regroupe plus rien
·
il faut, avant d'appliquer un filtre, décider s'il doit agir
avant ou après le regroupement. Le résultat, en effet, ne sera pas le même dans
les deux cas, sauf si le filtre est appliqué au champ de regroupement.
Le regroupement avec comptage, et son
application au traitement des doublons, sont abordés au chapitre suivant.