Le groupage et les requêtes portants sur des sous ensembles constituent souvent la partie la plus difficile à comprendre du SQL... C'est pourquoi nous y consacrerons un long article. Enfin, nous verrons différents opérateurs ensemblistes pour manipuler les tables à la manière des patates de Venn.
NOTA : La structure de la base de données exemple, ainsi qu'une version des principales bases utilisées sont disponibles dans la page "La base de données exemple"
1. La clause GROUP BY
Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
La clause BROUP BY est nécessaire dès que l'on utilise des fonctions de calculs statistiques avec des données brutes. Cette clause groupe les lignes sélectionnées en se basant sur la valeur de colonnes spécifiées pour chaque ligne et renvoie une seule ligne par groupe. On peut la comparer à une opération de découpage de sous ensemble un peut à la manière des "niveaux de rupture" lorsque l'on réalise des états imprimés.
Cherchons à compter le nombre de chambre par étage de notre hôtel :
Exemple 1
SELECTCOUNT(CHB_ID) AS NOMBRE, CHB_ETAGE
FROM T_CHAMBRE
La requête, telle que présentée ci dessus n'est pas calculable. En effet comment décider si le comptage doit se faire pour chaque chambre ou pour un groupe de chambre et notamment les groupes formés par chacun des étages ?
Si la requête se faisait pour chaque chambre, le résultat serait :
En revanche, un regroupement par étage, donne tout son sens à la requête :
NOMBRE CHB_ETAGE
----------- ---------
8 1er
8 2e
4 RDC
Pour réaliser un tel regroupement il faut introduire une clause de groupage dans la requête. Pour cela SQL fournit le clause GROUP BY :
Exemple 1 bis
SELECTCOUNT(*) AS NOMBRE, CHB_ETAGE
FROM T_CHAMBRE
GROUP BY CHB_ETAGE
NOTA :
La présence de la clause GROUP BY est nécessaire dès que la clause de sélection, ou le filtre WHERE, ou encore les jointures comportent simultanément des calculs d'agrégation et la présence de colonnes de table hors de calculs d'agrégation.
De plus, outes les colonnes représentées hors des calculs d'agrégation doivent figurer dans la clause GROUP BY.
La plupart du temps, le moteur de requête vous avertira d'un probable incohérence de calcul des agrégats à l'aide d'un message d'erreur (avant exécution de la requête), du genre : "La colonne ... est incorrecte dans la liste de sélection parce qu'elle n'est pas contenue dans une fonction d'agrégation et qu'il n'y a pas de clause GROUP BY" (SQL Server).
Cherchons maintenant à compter le couchage de chaque étage :
Exemple 2
SELECTSUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM T_CHAMBRE
GROUP BY CHB_ETAGE
NOMBRE CHB_ETAGE
----------- ---------
23 1er
22 2e
9 RDC
Un peu plus compliqué, cherchons à savoir quel a été le nombre de nuitées pour chaque chmabre au cours de l'année 1999 (une nuitée étant une personne passant une nuit dans une chambre. Si deux personnes occupent la même chambre cela fait deux nuitées) :
Exemple 3
SELECTSUM(CHB_PLN_CLI_NB_PERS), C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI P
ON C.CHB_ID = P.CHB_ID
WHERE PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY C.CHB_ID
Partant de là, nous pouvons rechercher le taux d'occupation de chaque chambre dans cette période. Le nombre maximal de nuitées étant le nombre de couchage de chaque chambre multiplié par toutes les dates de l'année, ce calcul s'obtient par :
Exemple 4
SELECTSUM(CHB_COUCHAGE) AS MAX_OCCUPATION, CHB_ID
FROM T_CHAMBRE C
CROSS JOIN T_PLANNING P
WHERE PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY C.CHB_ID
Il ne suffit plus que de "raccorder" les requêtes des exemples 3 et 4 :
Exemple 5
SELECT (CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT)) * 100
AS TAUX_OCCUPATION_POURCENT,
C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
CROSS JOIN T_PLANNING P
WHERE P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY C.CHB_ID
Nous allons voir, maintenant que la clause GROUP BY est souvent utilisée avec la clause HAVING...
2. La clause HAVING
Vous trouverez des compléments d'information sur le sujet aux pages 153 à 154 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
La clause HAVING agit comme le filtre WHERE, mais permet de filtrer non plus les données, mais les opérations résultant des regroupements, c'est à dire très généralement toute expression de filtre devant introduire un calcul d'agrégation.
Pour tenter de comprendre l'utilité de la clause having, nous allons procéder par un exemple simple : recherchons un étage de l'hôtel capable de coucher au moins 20 personnes. Partant de notre exemple 2, nous serions tenter d'écrire :
Exemple 6
SELECTSUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM T_CHAMBRE
WHERESUM(CHB_COUCHAGE) >= 20
GROUP BY CHB_ETAGE
Mais cette requête va imanquablement provoquer une erreur avant exécution du fait de la clause WHERE. En effet, souvenons nous que le filtre WHERE agit sur les données des tables et permet de filtrer ligne après ligne. Or le filtrage ne porte plus sur la notion de lignes, mais sur une notion de sous ensemble de la table. En d'autre termes, le filtre, ici, doit porter sur chacun des groupes. C'est pourquoi SQL introduit le filtre HAVING qui porte, non pas sur les données, mais sur les calculs résultants des regroupements.
En l'occurence, dans notre exemple, nous devons déporter le filtre WHERE dans la clause HAVING et l'opération deviendra possible :
Exemple 6 bis
SELECTSUM(CHB_COUCHAGE) AS NOMBRE, CHB_ETAGE
FROM T_CHAMBRE
GROUP BY CHB_ETAGE
HAVINGSUM(CHB_COUCHAGE) >= 20
NOMBRE CHB_ETAGE
----------- ---------
23 1er
22 2e
Autre exemple : Partant de la requête vue à l'exemple 6, essayons de ne retenir que les chambres occupées à plus de 2/3, soit 66.666666... % ?
Une première tentative consisterait à écrire :
Exemple 7
SELECT (CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT)) * 100 AS
TAUX_OCCUPATION_POURCENT, C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
CROSS JOIN T_PLANNING P
WHERE P.PLN_JOUR BETWEEN '1999-01-01' AND '1999-12-31'
GROUP BY C.CHB_ID
HAVING CAST(SUM(CHB_PLN_CLI_NB_PERS) AS FLOAT) /
CAST(SUM(CHB_COUCHAGE) AS FLOAT) >= 2.0/3.0
Un autre exemple intéressant est de rechercher quelles sont les chambres qui ont été innocupées plus de 10 jours au cours du mois de janvier 2000...
Pour calculer le nombre de jour ou les chambres ont été occupées au cours de janvier 2000, il suffit de faire :
Exemple 8
SELECTCOUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE EXTRACT(MONTH FROM P.PLN_JOUR) = 1
AND EXTRACT(YEAR FROM P.PLN_JOUR) = 2000
GROUP BY C.CHB_ID
Mais nous voulons le complément de cette colonne NOMBRE avec le nombre de jours du mois de janvier 2000, ce qui représente 31 jours. La requête devient donc :
Exemple 9
SELECT 31 - COUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE EXTRACT(MONTH FROM P.PLN_JOUR) = 1
AND EXTRACT(YEAR FROM P.PLN_JOUR) = 2000
GROUP BY C.CHB_ID
Dès lors il ne suffit plus que de filtrer le calcul : 31 - COUNT(C.CHB_ID) pour ne retenir que les valeurs supérieures à 10. Comme cette expression contient une fonction d'agrégation, ce qui suppose un groupage, il est nécessaire d'utiliser le filtre WHERE :
Exemple 10
SELECT 31 - COUNT(C.CHB_ID) AS NOMBRE, C.CHB_ID
FROM T_CHAMBRE C
JOIN TJ_CHB_PLN_CLI CPC
ON C.CHB_ID = CPC.CHB_ID
WHERE EXTRACT(MONTH FROM CPC.PLN_JOUR) = 1
AND EXTRACT(YEAR FROM CPC.PLN_JOUR) = 2000
GROUP BY C.CHB_ID
HAVING 31 - COUNT(C.CHB_ID) > 10
Pour dernier exemple, nous allons nous placer dans un contexte plus proche de la réalité. Voici notre directeur qui souhaite inciter les clients avec lesquels il a peu travaillé à venir plus souvent. L'idée lui prend d'offrir un bon de réduction de 15% sur l'ensemble des prestations hôtelières pour tous les clients ayant eut un chiffre d'affaire inférieur à 15 000 F HT au cours de l'année 2000. Une première approche consisterais par exemple a rechercher toutes les chambres libres entre les deux dates :
Exemple 11
SELECT F.CLI_ID,
-- le calcul est le suivant : -- (quantité * montant * remise en %) - remise en francs-- les colonnes "remises" pouvant avoir des valeurs NULL-- il faut les remplacer par un zéro à l'aide de la fonction COALESCESUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM T_FACTURE F
INNERJOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
WHERE EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP BY C.CLI_ID
HAVINGSUM(LIF_QTE * LIF_MONTANT * ((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000
Les clients 1, 37, 39, 40, 52, 61, 87 étant bien les personnes visées par la promotion.
Mais poussons notre raisonnement... Imaginons une personne ayant été cliente en 1999 et pas en 2000. Pour cela rajoutons les lignes suivantes dans notre jeu de données :
L'exécution de la requête de l'exemple 10, nous délivre le même résultat, alors que nous aimerions récupérer le client 101... Quelle erreur avons nous commise ? Le fait est que nous avons à nouveau fait l'hypothèse du monde clos ! (voir à la page Le SQL de A à Z, 3eme partie, le paragraphe 2.4) Il faut impérativement remonter jusqu'à la table des clients et réaliser des jointures externes sur les tables des factures et des lignes de facture :
Exemple 12
SELECT C.CLI_ID,
SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0))
AS CA
FROM T_CLIENT C
LEFTOUTERJOIN T_FACTURE F
ON C.CLI_ID = F.CLI_ID
LEFTOUTERJOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
WHERE EXTRACT(YEAR FROM F.FAC_DATE) = 2000
GROUP BY C.CLI_ID
HAVINGSUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000
Or et pour aussi bizarre que cela puisse paraître, cette requête ne nous donne toujours pas le client 101... Pourquoi ? Il persiste en fait deux raisons à cela :
nous avons supposé que le calcul du CA retournait une valeur...
nous avons filtré sur l'année 2000, après avoir effectué la jointure.
Pour contrer le cas n°1, nous ne devons pas oublier que les NULL se propagent dans les calculs et que le marqueur NULL ne peut être comparée à aucune valeur. Il faut donc réaliser le fitre HAVING en retenant l'expression si elle vaut moins de 15000 mais aussi si elle vaut NULL.
Pour contrer le cas n°2, et c'est beaucoup plus subtil, il faut inclure dans la condition de jointure entre les tables T_CLIENT et T_FACTURE l'expression située dans la clause WHERE.
La requête ainsi corrigée, devient :
Exemple 13
SELECT C.CLI_ID,
SUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) AS CA
FROM T_CLIENT C
LEFTOUTERJOIN T_FACTURE F
ON C.CLI_ID = F.CLI_ID
AND EXTRACT(YEAR FROM F.FAC_DATE) = 2000
LEFTOUTERJOIN T_LIGNE_FACTURE L
ON F.FAC_ID = L.FAC_ID
GROUP BY C.CLI_ID
HAVINGSUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) < 15000
ORSUM(LIF_QTE * LIF_MONTANT *
((1 - COALESCE(LIF_REMISE_POURCENT/100, 0)))
- COALESCE(LIF_REMISE_MONTANT, 0)) ISNULL
Nous pourrions ainsi passer encore plusieurs cas en revue tant le mélange des clauses WHERE et HAVING associées aux jointures externes peut devenir passionnant, mais ce site n'a pas pour but de vous poser des pièges, mais plutôt de vous faire toucher du doigt la problématique de ce type de requête et le logique ensembliste sous jacente.
Dans la suite du site et notamment lorsque vous aborderez les sous requêtes, de nouveaux exemples utilisant la clause HAVING et le groupage, sont présentés.
3. Les opérateurs ensemblistes
Vous trouverez des compléments d'information sur le sujet aux pages 180 à 187 de l'ouvrage "SQL", collection "La Référence", Campus Press éditeur.
C'est une des parties les plus simples de l'ordre SELECT. A la fois par sa syntaxe mais aussi par sa compréhension facile. Il s'agit, ni plus ni moins que de réaliser des opérations sur les ensembles représentés par des tables ou des extraits de table. Les opérations ensemblistes du SQL sont l'union, l'intersection et le différence.
3.1. L'UNION
Pour faire une union, il suffit de disposer de deux ensembles de données compatibles et d'utiliser le mot clef UNION. La syntaxe est alors :
SELECT ...
UNION
SELECT ...
Bien entendu il est indispensable que les deux ordres SELECT :
produisent un même nombre de colonnes
que les types de données de chaque paires ordonnées de colonnes soient de même type (ou d'un type équivalent)
3.2. L'INTERSECTION
La démarche est la même pour faire une intersection, que pour le mécanisme de l'union. La syntaxe utilisant le mot clef INTERSECT :
SELECT ...
INTERSECT
SELECT ...
Avec les mêmes contraintes d'ordre syntaxique.
3.3. LA DIFFÉRENCE
La différence de deux ensembles s'obtient de la même manière, en utilisant le mot clef EXCEPT :
SELECT ...
EXCEPT
SELECT ...
3.4. Considérations diverses sur les opérations ensemblistes du SQL
3.4.1. Le mot clef ALL peut qualifier l'opérateur ensembliste
ALL récupérera tous les doublons des ensembles considérés. ATTENTION : l'opérateur par défaut opère le dédoublonnage contrairement à la clause SELECT de l'ordre SELECT. Ainsi lorsque l'on va utiliser une telle construction pour des opérations comptable récupérant des sommes financières en utilisant l'opérateur UNION, on devra impérativement utiliser le mot clef ALL sinon les sommes identiques disparaîtrons.
3.4.2. On peut restreindre l'intersection à certaines colonnes en utilisant le filtre CORRESPONDING BY
Ainsi on pourra récupérer dans le résultat plus de colonnes que celles qui opèrent la véritable intersection.
3.4.3. Les opérateurs UNION et INTERSECT sont réflexifs, mais EXCEPT ne l'est pas
Ainsi l'ordre des requêtes de sélection n'a pas d'importance pour les opérations d'union et d'intersection, mais pour la différence cet ordre est essentiel et ne donne pas le même résultat en cas de permutation (c'est la propre de la soustraction et de la division).
3.4.4. On peut utiliser la clause ORDER BY
La clause ORDER BY doit être la clause finale, car il ne peut y en avoir qu'une.
3.4.5. Le nom des colonnes du résultat est celui du premier des ordres SELECT composant la globalité de la requête
Autrement dit si les noms des colonnes sont différents entre les divers ordres SELECT s'enchainant avec des opérateurs ensemblistes, ceux qui ont prépondérance sont ceux des colonnes de la première requête SELECT.
3.4.6. Mis à part l'union, les opérations ensemblistes d'intersection et de différence peuvent être construites à partir de requêtes de base
Exemples :
L'intersection peut être établie par :
SELECT t1.col1, t1.col2 FROM t1
INTERSECT
SELECT t2.col1, t2.col2 FROM t2
SELECTDISTINCT t1.col1, t1.col2
FROM t1
INNERJOIN t2
ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
SELECTDISTINCT t1.col1, t1.col2
FROM t1
WHEREEXISTS (SELECT *
FROM t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)
La différence peut être établie par :
SELECT t1.col1, t1.col2 FROM t1
EXCEPT
SELECT t2.col1, t2.col2 FROM t2
SELECTDISTINCT t1.col1, t1.col2
FROM t1
WHERENOTEXISTS (SELECT *
FROM t2
WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)
SELECTDISTINCT t1.col1, t1.col2
FROM t1
LEFTOUTERJOIN t2
ON t1.col1 = t2.col1 AND t1.col2 = t2.col2
GROUP BY t1.col1, t1.col2
HAVINGCOUNT(t2.*) = 0
-- t2.* étant n'importe quelle colonne de t2
3.5. Quelques exemples
Pour présenter des exemples significatifs, voici le jeu de test que nous allons utiliser :
Voici symbolisé par un dessin, le principe de l'union tel que décrit dans les deux requêtes précédentes :
union de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE
3.5.2. Exemple de DIFFÉRENCE
La différence de l'ensemble des noms des objets par rapport à celui des noms de machine, nous donnera :
Exemple 16
SELECT OBJ_NOM
FROM T_OBJET
EXCEPT
SELECT MAC_NOM
FROM T_MACHINE
OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE
Elle peut aussi être exprimée en l'absence d'opérateur EXCEPT, par :
Exemple 17
SELECT OBJ_NOM
FROM T_OBJET
WHERE OBJ_NOM NOTIN (SELECT MAC_NOM
FROM T_MACHINE)
OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE
Ou encore :
Exemple 18
SELECT O.OBJ_NOM
FROM T_OBJET O
LEFTOUTERJOIN T_MACHINE M
ON O.OBJ_NOM = M.MAC_NOM
GROUP BY O.OBJ_NOM
HAVINGCOUNT(M.MAC_NOM) = 0
OBJ_NOM
----------------
ASSIETTE
LIVRE
TABLE
Voici le diagramme de Venn correspondant à la différence telle que les requêtes que nous venons, l'exprime :
différence entre les noms des objets et ceux des machines
3.5.3. Exemple d'INTERSECTION
Quand à l'intersection, celle-ci n'est pas plus difficile :
Exemple 19
SELECT OBJ_NOM
FROM T_OBJET
INTERSECT
SELECT MAC_NOM
FROM T_MACHINE
OBJ_NOM
----------------
MOTO
PERCEUSE
Que l'on peut exprimer par l'équivalence logique :
Exemple 20
SELECT OBJ_NOM
FROM T_OBJET O
JOIN T_MACHINE M
ON O.OBJ_NOM = M.MAC_NOM
OBJ_NOM
----------------
MOTO
PERCEUSE
Ou encore :
Exemple 21
SELECT OBJ_NOM
FROM T_OBJET O
WHERE OBJ_NOM IN (SELECT MAC_NOM
FROM T_MACHINE)
OBJ_NOM
----------------
MOTO
PERCEUSE
Et par un dessin reprennant les célèbres patates, le principe de l'intersection tel qu'exprimé décrit dans les requêtes ci dessus :
intersection de l'ensemble des noms (OBJ_NOM + MAC_NOM) des tables OBJET et MACHINE
Un petit dernier exemple sera consacré à l'intersection avec correspondance. Pour cela nous avons besoin de modifier nos deux tables (ou au moins l'une d'entre elles) de façon a ce que les noms des colonnes des tables soient identiques. Par exemple, notre nouveau jeu de données pourrait être :
SELECT *
FROM T_OBJET
INTERSECT CORRESPONDING
SELECT *
FROM T_MACHINE
NOM PRIX
---------------- ----------
MOTO 43 528
ORDINATEUR 7 000
Le mot clef CORRESPONDING se comporte un peu à la manière du NATURAL JOIN et de son mot clef USING, c'est à dire qu'en le précisant, SQL va chercher la correpondance des colonnes par leur position, nom et type. On peut en outre préciser sur quelles colonnes la correspondance va porter, à l'aide du mot clef BY :
Exemple 23
SELECT *
FROM T_OBJET
INTERSECT CORRESPONDING BY (NOM)
SELECT *
FROM T_MACHINE
NOM
----------------
MOTO
ORDINATEUR
PERCEUSE
A noter que l'usage du mot clef CORRESPONDING [BY (liste de colonne)] est accepté dans l'intersection comme dans l'union. Cependant, rare sont les moteurs SQL a l'accepter !
4. Résumé
Voici les différences entre les moteurs des bases de données :
