Maitriser les requêtes 02

la requête de regroupement

Introduction

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.

2 - La notion de regroupement

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.

 

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).

3 - La création de la requête

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".

4 - Les opérations sur les colonnes

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.

5 - Les fonctions

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.

 

6 - Le filtrage d'une requête de regroupement

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 €.

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 :

1. nous introduisons le champ dans la grille ;
2. nous cliquons sur la ligne "Opération :", où "Regroupement" s'est inscrit par défaut ;
3. 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 ;
4. 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 !

7 - Conclusion

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.