Les requêtes avec regroupement

IV-A-1. Compte([NomDuChamp])▲

Nous avons déjà eu un aperçu de cette opération au chapitre précédent lors du comptage des commandes par client.

La ligne « Champ » au-dessus de l'opération Compte, peut comporter un champ de table ou une expression calculée renvoyant une valeur de type numérique, texte ou date.

L'important est que dans ce cas, seuls les enregistrements où la valeur du champ est non vide (non marqué à Null), sont dénombrés.

Dans l'exemple ci-dessous, nous mettons le champ [DateEnvoi] comme paramètre de la fonction Compte:

et nous constatons sans surprise que seulement six des sept commandes du client « WOLZA » ont été dénombrées à cause de la [DateEnvoi] marquée à Null lors de sa dernière commande.

Nous ne discuterons pas ici du bien-fondé ou non de ce champ « Nullable » pour modéliser le fait, par exemple, qu'une commande n'ait pas été envoyée. Il s'agit plutôt ici de constater les premiers effets de bord dus à la présence du marqueur Null sur l'évaluation des fonctions d'agrégation.
Nous n'avons pas fini d'en discuter.

IV-A-2. Compte(*)▲

Pour dénombrer tous les enregistrements, nous pouvons utiliser le caractère générique astérisque « * » comme argument de la fonction Compte. Pour cela, le QBE d'Access nous oblige à basculer la ligne « Opération » à Expression avant de saisir au clavier l'expression avec Compte(*) sur la ligne « Champ ».

NbrCommandes est l'alias(4) du champ retourné par la requête.

IV-A-3. Compte([idCommande]) ou Compte(*) ?▲

Mais alors, on prend laquelle ?

Si vous utilisez un astérisque, la fonction Compte(*) calcule le nombre total des enregistrements par client même si ceux-ci ont tous leurs champs marqués à Null. Si vous utilisez Compte([idCommande]), la fonction calcule le nombre d'enregistrements par client avec [idCommande] non Null.

La clé primaire [idCommande] de TblCommande étant non Null par principe, les deux requêtes retournent rigoureusement le même résultat.

Seulement, la documentation Access précise bien :

Count(*) s'éxecute beaucoup plus rapidement que Count([nom_de_colonne]).

Donc privilégiez l'utilisation de Compte(*) lorsque cela vous est possible.

Pour distinguer l'usage de Compte(*) et Compte([NomDuChamp]), on propose l'exercice qui suit.

On souhaite le nombre de commandes par client, ceci pour tous les clients enregistrés dans la table TblClient, y compris ceux qui, à défaut d'avoir passé une seule commande, ne manifestent aucune présence dans la table TblCommande.

On commence par une requête de sélection sur les deux tables TblClient et TblCommande avec une jointure de type « 2 » pour ramener tous les clients, y compris ceux qui ne figurent pas dans la table des commandes.

Le résultat :

Tiens ! Le client « FISSA » n'a passé aucune commande. Pour obtenir le nombre de commandes par client (et donc zéro commande pour le client « FISSA »), doit-on utiliser Compte(*) ou Compte([idCommande]) ?

Réponse :

Un Compte(*) retournera un nombre de commandes égal à un pour le client « FISSA » puisqu'il y a un enregistrement retourné.

La bonne réponse est d'effectuer un Compte([idCommande]) qui ne dénombre que les enregistrements où [idCommande] est non Null.

IV-B-1. Principe▲

On regarde maintenant du côté des lignes de commande :

Pour la partie facturation, nous avons besoin de regrouper les lignes de commande par [idCommande] et de faire la somme :

Somme([PrixUnitaire]*[QtteCommande])

Le principe de réalisation d'une telle requête n'appelle pas de commentaire particulier :

• opération de regroupement par [idCommande] ;
• opération d'agrégation Somme sur l'expression [PrixUnitaire]*[QtteCommande];
• la synthèse qui, par commande, retourne un enregistrement ([idCommande], [PrixCommande]).

Et on retrouve bien, par exemple pour [idCommande]=10248, la somme des lignes :

2 200 € = (70 € x 12) + (49 € x 10) + (174 € x 5)

IV-B-2. ...mais Access ne sait pas faire des additions !▲

On rajoute un champ [Remise] dans le détail des commandes :

Le grain de sable a été introduit dans la commande identifiée 10248 avec un champ [Remise] négligemment marqué à Null (encadré en rouge) :

Voici ce que retourne une simple requête de sélection avec les champs calculés [AvantRemise], [MontantRemise] et [ApresRemise] :

Faisons le bilan :

Le total avant remise est de 2 200 €, le montant de la remise est de 87 €.

Le montant de la commande devrait être de 2 200 € - 87 € = 2 113 €, du moins si vous le calculiez comme cela en faisant la différence entre le montant avant la remise et la remise. En fait, tout se passe comme si le champ à Null était à zéro.

Parce que si vous faites autrement en faisant la somme des montants de la colonne [ApresRemise]…

… la facture passe à 1 273 €, amputée de la totalité de la ligne de commande à cause de cette remise non renseignée.

Votre comptable sera ravi d'apprendre que :

Somme([QtteCommande] x [PrixUnitaire]) - Somme([Remise] x [QtteCommande] x [PrixUnitaire])

ne donne pas toujours le même résultat que :

Somme([QtteCommande] x [PrixUnitaire] x (1 - [Remise]))

Vous pouvez toujours rétorquer que le développeur averti aurait naturellement surchargé sa programmation à coup de :

 

Sélectionnez

Nz([remise];0)

qui remplace les marqueurs Null par la valeur numérique 0.

(Imaginez toutefois la moyenne sur le bulletin du gamin si en suivant le même conseil, le logiciel remplaçait les notes non renseignées d'un devoir par zéro !)

Si le cahier des charges de l'application précise que la remise doit être systématiquement renseignée, la solution idéale est d'interdire tout simplement la présence de l'affreux Null dans la table :

Quoi qu'il en soit, on vous a maintenant assez prévenu des effets parfois néfastes du marqueur Null.

On revient sur une requête précédente :

L'opération « Où » dans une requête avec regroupement effectue une restriction sur les enregistrements avant que le regroupement ne soit effectif.

Ici, le regroupement est donc opéré uniquement sur les enregistrements vérifiant le critère :

        Année([DateCommande])=Année(Date())

Essayons maintenant les deux requêtes suivantes visant à compter, parmi les commandes passées lors de l'année en cours, le nombre de commandes pour les [CodeClient] commençant par la lettre « A ».

Dans la première version, la requête effectue une restriction supplémentaire sur le [CodeClient]. Les enregistrements de la table des commandes dont l'année de la [dateCommande] ne correspond pas à l'année en cours et dont le [CodeClient] ne commence pas par « A » sont exclus avant d'opérer le regroupement et de retourner la synthèse :

Dans la deuxième version, on effectue comme précédemment une restriction sur la [dateCommande]. La différence est qu'ici on opère d'abord le regroupement et le comptage. Puis seulement après, on retire les synthèses dont le [CodeClient] ne commence pas par « A » :

Il est évident que dans ces versions, les deux requêtes retournent un résultat identique. Toutefois la deuxième version est déconseillée puisqu'elle oblige d'abord à opérer le comptage sur 83 sous-divisions avant finalement de n'en retenir que quatre.

Par contre, si on souhaite faire une restriction sur le nombre de commandes passées, on doit filtrer sur le résultat retourné par la fonction d'agrégation Compte.

Ainsi, si nous voulons une requête qui, pour les commandes passées lors de l'année en cours, ne retienne que les clients ayant commandé plus de cinq fois, on doit :

• retirer les enregistrements de TblCommande dont l'année de la [dateCommande] ne correspond pas à l'année en cours (opération « Où ») ;
• opérer un regroupement sur le [codeClient] (opération « Regroupement») ;
• compter tous les enregistrements pour chaque sous-division opérée (opération Compte) ;
• exclure les synthèses où le comptage donne un résultat inférieur ou égal à 5 (critère « >5 ») ;
• afficher la synthèse retournée (cases « Afficher » cochées).

Ce qui donne dans le QBE :

L'opération « Où » permet de filtrer les enregistrements avant que le regroupement ne soit effectif.

Un critère sur une opération d'agrégation n'agit qu'après regroupement et synthèse. On l'utilise pour éliminer les synthèses ne vérifiant pas le critère sur l'expression agrégée (critère sur un comptage, une somme, etc.).

À des fins comptables ou statistiques, nous pouvons être amenés à déterminer les nombres de commandes passées par nos clients, et ceci par année civile.

Le partitionnement des enregistrements de la table des commandes doit se faire de la façon suivante, par client et par année de la date de commande :

Dans le QBE, la solution vient naturellement en regroupant dans la table des commandes par commande et par année (champ calculé : Année([DateCommande]).

Dans ce cas, le couple ( [CodeClient], Année([DateCommande]) ) constitue ce qu'on appelle souvent la clé de regroupement.

Afin de compléter la requête précédente, nous voulons, en plus du [CodeClient], le nom du pays où siège la société [Pays].

Rechercher le champ [Pays] nécessite de faire participer également la table des clients dans la requête :

Il se trouve que [Pays] est « fonctionnellement dépendant » de [CodeClient].

L'objet de cet article n'étant pas le Modèle Relationnel de Données et la normalisation des tables des SGBD relationnels, nous resterons très simple au niveau du concept de dépendance fonctionnelle (DF). Si le lecteur souhaite se familiariser plus en détail, il se tournera vers l'article correspondant : Bases de données relationnelles et normalisation : de la première à la sixième forme normale, notamment au paragraphe Dépendance fonctionnelle (DF).
Si nous reprenons la table des clients, un client a un [CodeClient] unique et chaque client est en fait une société avec un nom de société [Societe].
Nous pouvons écrire l'instruction :
[CodeClient]→[Societe]
parce que pour un [CodeClient] donné, correspond exactement un nom de [Societe].
On dit alors que [Societe] est fonctionnellement dépendant de [CodeClient] ou que [CodeClient] détermine fonctionnellement [Societe].
[CodeClient] étant la clé primaire de la table, il définit fonctionnellement chaque attribut de la table des clients :
[CodeClient]→[Societe]
[CodeClient]→[Contact]
…
[CodeClient]→[Adresse]
…
[CodeClient]→[Pays]
Note : D'ailleurs, si vous avez pris le coup, vous vous rendrez compte qu'il serait naturel d'écrire aussi la dépendance :
[Contact]→[Fonction]
(Si j'identifie un contact donné, j'ai bien déterminé sa fonction, non ?)
Ne faudrait-il pas une table TblContact ? Bravo, vous venez de découvrir une des raisons pour laquelle la base de données « Les comptoirs » n'est vraiment pas un modèle en termes de normalisation. Passons…

Voyons ce que donne le partitionnement de la table des commandes par [CodeClient] et par Année([DateCommande]) si nous ajoutons simplement le champ [Pays] pour chaque enregistrement :

Nous constatons que grâce à la dépendance fonctionnelle [CodeClient]→[Pays], le partitionnement demeure inchangé si nous rajoutons [Pays] dans la clé de regroupement :

Comme nous avons aussi les dépendances fonctionnelles :

[CodeClient]→[Societe]

[CodeClient]→[Contact]

…

[CodeClient]→[Adresse]

nous pouvons généraliser dans le QBE et obtenir toutes les caractéristiques du client dans notre requête de comptage :

Cette méthode n'est toutefois pas très recommandée en termes de performance comme nous allons le voir dans le paragraphe suivant.

On pourrait dire beaucoup de choses sur l'optimisation des requêtes (cf. le Guide d'optimisation de vos applications Microsoft Access). Les conseils de bon sens comme celui de regrouper sur un nombre de champs minimal de type entier numérique et indexés (à peu près tout le contraire de la requête précédente) s'avèrent judicieux « en principe », surtout si les tables deviennent volumineuses.
Toutefois il y a de nombreux facteurs et un prototypage en règle de vos requêtes sensibles dans l'environnement de production est le meilleur moyen de conclure de l'avantage d'une requête sur telle autre.

On reprend la requête peu recommandable précédente. On rappelle que cette requête est censée ramener le nombre de commandes par client et par année.

Ramener les informations du client dans la clé de regroupement pour obtenir le résultat souhaité est certes possible grâce aux dépendances fonctionnelles :

        [CodeClient]→[Pays]

        [CodeClient]→[Societe]

        [CodeClient]→[Contact]

        [CodeClient]→[DateCommande]

mais il reste qu'une clé de regroupement sur un nombre élevé de champs est coûteuse en termes de performance. Deux méthodes permettent d'obtenir le même résultat de façon optimisée.

VI-C-1. Utilisation de l'opérateur d'agrégation « Premier »▲

Après tout, le partitionnement sur les seuls [CodeClient] et Année([DateCommande]) devraient suffire. Si on veut ramener les autres attributs du client, il suffit pour chaque sous-division de prendre les informations sur une seule des lignes de la sous-division. Pourquoi pas la première en utilisant l'opérateur d'agrégation Premier ?

Dans une table de base de données relationnelle, sélectionner le « premier » enregistrement n'a pas de sens. Les lignes de la table sont comparables aux billes d'un sac de billes. Que signifie la « première » bille ? Suivant quel critère ? D'ailleurs l'opérateur d'agrégation Premier (mot-clé : First) est propre à Access, vous ne le trouverez nulle part dans la norme SQL.
Malgré l'aberration que cet opérateur représente et le caractère aléatoire de ce qu'Access retourne lorsqu'on lui demande de retourner le « premier » enregistrement, dans notre contexte où toutes les billes regroupées sont identiques, il est tolérable de prendre la « première » trouvée (parce qu'ici, on aurait aussi bien pu lui demander la « dernière » - mot-clé : Last).

VI-C-2. Utilisation d'une sous-requête▲

L'idée est encore d'optimiser la clé de regroupement. On regroupe sur un minimum de champs, de préférence de type numérique et indexés. Le [CodeClient] et l'année de la date de commande Année([DateCommande]) feront l'affaire ici.

La requête principale reprend la sous-requête :

C'est par jointure de la table des clients et la sous-requête via le [CodeClient] que nous récupérons les attributs du client dans une requête simple de sélection :

Bien qu'elle semble « coûter » davantage à cause de la sous-requête, le fait d'optimiser la clé de regroupement améliore en principe les performances malgré l'opération de jointure supplémentaire (qui est quand même une opération sensible et en principe ultraoptimisée dans un SGBD relationnel).

Si vous souhaitez dénombrer les clients ayant commandé par année civile, la requête qui suit vous retournera un résultat faux :

Ceci parce qu'un même client ayant commandé, par exemple, trois fois dans l'année sera compté trois fois. Ce que vous vouliez en fait, c'est le nombre de clients distincts ayant commandé dans l'année.

Pour cela, Access vous oblige à passer par une sous-requête intermédiaire ne listant qu'une seule fois les clients ayant passé commande par année civile (en supprimant les doublons sur le couple ([CodeClient], [AnneeCommande]) :

On dénombre ensuite les clients en interrogeant la sous-requête :

Un classique du genre…La plaie des bases de données, les doublons.

Si la table des employés comporte deux fois les mêmes nom et prénom, il peut s'agir d'un homonyme mais il est aussi probable que ce soit la même personne saisie deux fois.

Ce genre de doublons n'est pas difficile à repérer, il suffit de mettre les champs [Nom] et [Prénom] dans la clé de regroupement et retourner les sous-divisions dénombrant au moins deux enregistrements. Par exemple :

Si vous voulez voir comment Access se débrouille avec les doublons, inspirez-vous des requêtes proposées par l'assistant :

La table TblProduit comporte un champ booléen (de type « Oui/Non ») nommé [Indisponible] :

Si on souhaite dénombrer les produits indisponibles par catégorie de produit, voici ce que l'on pourrait composer avec le QBE d'Access :

On restreint les enregistrements aux seuls produits ayant le marqueur [Indisponible] à Vrai avant d'effectuer le regroupement puis le comptage.

Sous Access, quel que soit le format d'affichage du champ booléen « Oui/Non », « Vrai/Faux » ou « Actif/Inactif », celui-ci est stocké sous une forme numérique avec :

• [Inactif]=-1 si « Vrai », « Oui » ou « Actif »
• [Inactif]=0 si « Faux », « Non » ou « Inactif »

Vous pouvez tester la requête suivante en utilisant la fonction de conversion en entier CEnt pour vous en convaincre :

Dès lors, nous pouvons aussi obtenir notre comptage en sommant les valeurs numériques sous-jacentes au champ booléen (au signe près), ce qui donne une autre version du même comptage :

Notez le signe « - » devant l'opération Somme.

Si cette astuce semble simplifier la requête dans son écriture, il vous appartient de vérifier sa pertinence du point de vue des performances sur des tables volumineuses.

Mais imaginons que l'on souhaite en plus connaître le nombre d'alertes de réapprovisionnement par catégorie de produit. Les alertes doivent être déclenchées à partir du moment où [UnitesEnStock] devient inférieur ou égal à [NiveauReappro].

On pourrait calculer un « booléen » qui vaudrait 1 si [UnitesEnStock]<=[NiveauReappro] et 0 sinon avec l'instruction VraiFaux :

VraiFaux([UnitesEnStock]<=[NiveauReappro];1;0)

On se ramène alors au cas précédent avec le champ [Indisponible]. Il ne reste plus qu'à faire la somme :

Et voilà comment avec deux opérations « Somme » obtenir deux comptages « conditionnels » suivant la disponibilité et le niveau du stock.

Cette fois nous partons d'une table (temporaire) TblCA2012 avec le chiffre d'affaires 2012 pour chaque produit :

L'objectif est d'obtenir les cinq meilleurs CA avec le rang, ceci par catégorie de produit :

Il y a plusieurs façons de résoudre le problème, comme celle inspirée des sources Access :

Classer les résultats d'une requête

Si toutefois vous êtes fâché avec le méchant SQL, la solution présentée qui suit (entièrement réalisée dans l'interface QBE) oblige à passer par une sous-requête intermédiaire :

Celle-ci, pour chaque produit, va dénombrer les produits (champ calculé [NbProd]) de la même catégorie (grâce à la jointure avec TblCA2012_1) qui ont un chiffre d'affaires supérieur (grâce au critère de l'opération « Où »).

D'après le résultat intermédiaire ci-dessus, le produit identifié 1 sera au cinquième rang de sa catégorie en termes de chiffre d'affaires puisque la sous-requête ramène quatre produits de la même catégorie ayant un chiffre d'affaires strictement supérieur.

Le premier étage de la fusée pour le calcul du rang est prêt. Vous noterez la jointure de la table TblCA2012 avec elle-même (représentée via l'alias TblCA2012_1, obtenu en ajoutant une deuxième fois TblCA2012 dans la fenêtre).

Il reste à conclure avec la requête principale de sélection :

Le rang d'un produit en termes de CA est le nombre de produits de la même catégorie ayant un CA strictement supérieur, auquel on rajoute 1.

Les meilleurs produits (avec le rang égal à 1) ne sont pas retournés par la sous-requête puisqu'aucun produit de la même catégorie n'a de CA supérieur.

D'où la nécessité d'une jointure de type « 2 » entre TblCA2012 et SousReq_CA, en plus de la fameuse fonction Nz dans le calcul du rang.

Nz([NomDuChamp]; Valeur si Null)