Developpez.com

Télécharger gratuitement le magazine des développeurs, le bimestriel des développeurs avec une sélection des meilleurs tutoriels

L'agrégation de données
Un tutoriel de Lyche

Le , par Lyche, Expert confirmé
Fonction de totaux et sous-totaux


Présentation

Les bases de données de Business Intelligence sont confrontées à des problématiques récurrentes de calculs, de statistiques, ou de diverses agrégations de données. Beaucoup s’imaginent que faire des sommes et autres moyennes de notes n’est qu’appliquer de simples formules mathématiques. Or, lorsqu’il s’agit de bases de données, ces calculs prennent une autre dimension, dont la complexité dépend des règles de gestion et du besoin client.

Les Agrégations

Les agrégations, malgré ce nom un peu effrayant, sont de simples calculs mathématiques. Le tableau, ci-dessous, présente les fonctions d’agrégation les plus communes.

Fonction Sert à
AVG() Calculer la moyenne des valeurs agrégées
COUNT() Calculer le nombre de lignes
MAX() Récupérer la valeur maximum d'une colonne
MIN() Récupérer la valeur minimum d'une colonne
SUM() Calculer la somme des valeurs de la colonne
Cas pratique
Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
;WITH ListUsers AS ( 
SELECT * 
  FROM ( VALUES( 1, 'Georges', 74 ), 
               ( 2, 'Pierre' , 17 ), 
               ( 3, 'Bernard', 63 ), 
               ( 4, 'John'   , 23 ) ) AS Users( Id, Nom, Age ) ) 
  
SELECT SUM( Age ) AS [Somme_Age] 
     , AVG( Age ) AS [Moyenne_Age] 
     , MIN( Age ) AS [Age_Minimum] 
     , MAX( Age ) AS [Age_Maximum] 
     , COUNT( * ) AS [Nombre_Users] 
  FROM ListUsers
Cet exemple utilise un jeu de données assez simple : une liste de personnes avec leur âge, sur lequel sont faits les différents calculs requis. En premier, la somme des âges, ensuite la moyenne d’âge, puis le plus jeune, le plus vieux et enfin le nombre de lignes dans notre jeu de données.
GROUP BY

Ces fonctionnalités ne sont pas difficiles à comprendre. Ce qui peut compliquer la tâche, c’est la difficulté d’associer un certain nombre de colonnes à des calculs spécifiques. Ce que l’on nommera le groupement de données, (GROUP BY en langage SQL)
Cette fonctionnalité de la norme SQL permet de définir des éléments communs de regroupement de l’information.
Avant de rentrer dans le texte un peu théorique, pas toujours clair et parfois difficile à comprendre, un petit cas pratique.
Cas pratique

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
  
CREATE TABLE dbo.TB_DEPARTEMENTS ( 
  ID_DEPARTEMENT  INT PRIMARY KEY 
, NOM_DEPARTEMENT VARCHAR( 50 ) ); 
GO 
  
CREATE TABLE dbo.TB_EMPLOYES ( 
  ID_EMPLOYE      INT PRIMARY KEY 
, NOM_EMPLOYE     VARCHAR( 50 ) NOT NULL 
, AGE_EMPLOYE     INT 
, DEPARTEMENT_ID  INT 
, SALAIRE_EMPLOYE MONEY ); 
GO 
  
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 1, 'Administration'       ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 2, 'Ressources Humaines'  ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 3, 'Service Informatique' ); 
INSERT INTO dbo.TB_DEPARTEMENTS 
VALUES( 4, 'Comptabilité'         ); 
  
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 1, 'Georges', 74, 4, 2480.3 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 2, 'Pierre' , 17, 3, 1387.2 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 3, 'Bernard', 63, 1, 3499.8 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 4, 'John'   , 23, 3, 1876.9 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 5, 'Jérome' , 45, 2, 2286.6 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 6, 'Lina', 30, 3, 2230.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 7, 'Marie', 26, 3, 1980.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 8, 'Virginie', 37, 3, 2730.4 ); 
INSERT INTO dbo.TB_EMPLOYES 
VALUES( 9, 'Hélène', 33, 3, 2430.4 );
Les commandes ci-dessus créent 2 tables employés/départements liées par l’id_departement.
Nous sommes ici dans le cadre d’une entreprise qui va vouloir faire quelques statistiques sur ses employés.
1 – Faire la somme des salaires par département.

Code sql : Sélectionner tout
1
2
3
4
5
SELECT NOM_DEPARTEMENT 
     , SUM( SALAIRE_EMPLOYE ) AS TOTAL_SALAIRE 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.TB_DEPARTEMENTS D ON E.DEPARTEMENT_ID = D.ID_DEPARTEMENT 
 GROUP BY NOM_DEPARTEMENT
Vous pourrez remarquer l’emploi de la fonction GROUP BY, associée à la colonne NOM_DEPARTEMENT.
Vous constaterez que la valeur de la colonne TOTAL_SALAIRE est égale à la somme totale des salaires saisis pour la liste des employés.
Explication

Au niveau du SGBD, la commande lui ordonne de sommer la colonne salaire et de faire ce calcul par nom de département (donc, de regrouper les informations identiques en une seule ligne).
Nous nous retrouvons donc avec nos 4 départements et leur budget mensuel respectifs.
Nous pourrions faire exactement les mêmes calculs que dans la première partie de notre billet.
2 – Calculer la moyenne des salaires, le salaire minimum et maximum, ainsi que le nombre d’employés par département


Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
SELECT NOM_DEPARTEMENT 
     , SUM( SALAIRE_EMPLOYE ) AS TOTAL_SALAIRE 
     , AVG( SALAIRE_EMPLOYE ) AS MOYENNE_SALAIRE 
     , MAX( SALAIRE_EMPLOYE ) AS SALAIRE_MAXIMUM 
     , MIN( SALAIRE_EMPLOYE ) AS SALAIRE_MINIMUM 
     , COUNT( * )             AS TOTAL_EMPLOYE 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.TB_DEPARTEMENTS D ON E.DEPARTEMENT_ID = D.ID_DEPARTEMENT 
 GROUP BY NOM_DEPARTEMENT
Ici, en étudiant, on constate certains écarts, notamment au niveau du service informatique là où il y a plusieurs employés.

  • La requête permet ici de calculer plusieurs types d’informations basées sur le NOM_DEPARTEMENT comme critère commun.


Important :
Il m’est souvent arrivé d’avoir des demandes concernant des requêtes « fausses » à cause d’une mauvaise gestion du GROUP BY. Pour faire au plus simple quant à sa pratique, il faut savoir que toute colonne se trouvant dans un select et n’appartenant pas à une fonction d’agrégation, doit se retrouver dans la clause du GROUP BY.
Ce qui est le plus difficile, c’est l’identification des champs du select hors agrégat. Il est parfois nécessaire de retourner la question posée (ou la RG) afin d’être le plus précis possible sur les champs.

  • À noter qu’il est possible d’agréger des données sur le résultat d’une fonction.

Exemple

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
  
CREATE TABLE dbo.INTERVENTION_CLIENT ( 
  ID_INTERVENTION   INT PRIMARY KEY 
, DATE_INTERVENTION Date 
, NOM_CLIENT        VARCHAR( 50 ) 
, EMPLOYE_ID        INT 
, FACTURATION       Money ) 
  
  
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  1, '2014-01-02', 'Client 1', 2, 500 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  2, '2014-01-03', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  3, '2014-01-06', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  4, '2014-01-07', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  5, '2014-01-08', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  6, '2014-01-09', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  7, '2014-01-10', 'Client 1', 2, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  8, '2014-01-03', 'Client 1', 4, 400 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES(  9, '2014-01-04', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 10, '2014-01-06', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 11, '2014-01-07', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 12, '2014-01-08', 'Client 1', 4, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 13, '2014-01-03', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 14, '2014-01-04', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 15, '2014-01-06', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 16, '2014-01-07', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 17, '2014-01-08', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 18, '2014-01-09', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 19, '2014-01-10', 'Client 2', 6, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 20, '2014-01-06', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 21, '2014-01-07', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 22, '2014-01-08', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 23, '2014-01-09', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 24, '2014-01-10', 'Client 3', 7, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 25, '2014-01-06', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 26, '2014-01-07', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 27, '2014-01-08', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 28, '2014-01-09', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 29, '2014-01-10', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 30, '2014-01-03', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 31, '2014-01-06', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 32, '2014-01-07', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 33, '2014-01-08', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 34, '2014-01-09', 'Client 4', 9, 650 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 35, '2014-01-10', 'Client 4', 9, 650 );

Nous créons ici un ensemble de lignes d’intervention de nos collaborateurs chez les clients. Nous souhaitons avoir des informations sur les mois de travail.

Quelle est la somme des facturations de nos collaborateurs pour les mois écoulés, sans tenir compte du lieu de l’intervention ?


Code sql : Sélectionner tout
1
2
3
4
5
6
SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE, MONTH( DATE_INTERVENTION );

Nous avons pris en compte 3 éléments de la demande.

  • Mois d’Intervention
  • Collaborateur
  • Somme des facturations.


  • Ces 3 informations permettent d’identifier le groupement nécessaire ainsi que l’agrégation souhaitée.

Nous nous retrouvons avec la fonction MONTH(), qui permet de ressortir le mois d’une date, dans le select. Contrairement au ORDER BY qui peut fonctionner avec la position du champ dans le select, GROUP BY nécessite de reproduire exactement les mêmes commandes que lors du SELECT.

  • Pour faciliter l’écriture d’un GROUP BY, une CTE, une table Dérivée ou une l’utilisation d’une vue, sont de bonnes solutions.


La clause HAVING

Maintenant que nous savons faire un groupement de données afin de l’agréger, nous allons voir comment exploiter le résultat de notre groupement et comment limiter les résultats à nos attentes.
Nous l’avons vu plus haut, il est possible de calculer des sommes d’interventions d’une personne, maintenant, imaginons que les consultants envoyés en missions ont un coût pour l’entreprise. Ce coût, c’est le salaire.
A partir de ces deux informations que sont le salaire et les interventions, nous pouvons en déduire quel consultant est « rentable » ou non, et pour ce faire, il nous est possible de faire des comparaisons entre la valeur d’un champ (ou le résultat d’une requête)

A partir de notre jeu de données, nous allons faire un test.

  • Nous savons que nous avons pu facturer nos prestataires à différents clients au cours du mois. Cependant, est-ce que ces personnes nous rapportent suffisamment par rapport à leur salaire, ou serait-il nécessaire de leur trouver plus de mission. Ou bien faut-il facturer plus les clients ?

Nous connaissons le salaire des employés, et nous venons de calculer la valeur de leurs différentes interventions.
Cependant, qu’en est-il de ce qu’ils rapportent à l’entreprise ?


Nous allons utiliser la fonction HAVING. Elle doit nous permettre de comparer le résultat d’un groupement à une valeur fixe.

Pour ce faire, reprenons notre précédente requête et ajoutons la clause.
Code sql : Sélectionner tout
1
2
3
4
5
6
7
SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE, SALAIRE_EMPLOYE, MONTH( DATE_INTERVENTION ) 
 HAVING SUM( FACTURATION ) > SALAIRE_EMPLOYE;
Vous pourrez constater que le groupement devient quelque peu différent. En effet, l’ajout du SALAIRE_EMPLOYE dans le groupement devient obligatoire si l’on souhaite le comparer. Pourquoi ?
Comme expliqué plus haut, toute colonne utilisée dans la requête et n’étant pas soumise à un agrégat doit obligatoirement se trouver dans le groupement.
J’ai besoin de comparer le SALAIRE à la somme des facturations. Il me faut donc définir le SALAIRE comme une donnée non agrégée.


Le résultat est sans appel ! Il manque John à l’appel des personnes « rentables ». Qu’à cela ne tienne, on facturera de la même façon que les autres

Maintenant que l’on a vu les fonctionnalités de base des groupements, nous allons voir quelques fonctionnalités avancées qui devraient vous faciliter la vie pour les rapports.

GROUP BY ROLLUP

Nous l’avons vu, GROUP BY est une fonction assez simple qui agrège les données sur des critères spécifiques mais ne donne pas de détails ni ne fait de sommes particulières.

Nous allons compléter notre jeu de données avec des interventions sur un nouveau mois.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 36, '2014-02-03', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 37, '2014-02-04', 'Client 1', 2, 500 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 38, '2014-02-05', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 39, '2014-02-06', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 40, '2014-02-07', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 41, '2014-02-10', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 42, '2014-02-11', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 43, '2014-02-12', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 44, '2014-02-13', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 45, '2014-02-14', 'Client 1', 2, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 46, '2014-02-03', 'Client 1', 4, 400 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 47, '2014-02-04', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 48, '2014-02-05', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 49, '2014-02-06', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 50, '2014-02-07', 'Client 1', 4, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 51, '2014-02-03', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 52, '2014-02-04', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 53, '2014-02-05', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 54, '2014-02-06', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 55, '2014-02-07', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 56, '2014-02-10', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 57, '2014-02-19', 'Client 2', 6, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 58, '2014-02-06', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 59, '2014-02-07', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 60, '2014-02-10', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 61, '2014-02-11', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 62, '2014-02-12', 'Client 3', 7, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 63, '2014-02-06', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 64, '2014-02-07', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 65, '2014-02-10', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 66, '2014-02-11', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 67, '2014-02-12', 'Client 3', 8, 700 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 68, '2014-02-03', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 69, '2014-02-06', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 70, '2014-02-07', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 71, '2014-02-10', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 72, '2014-02-11', 'Client 4', 9, 750 ); 
INSERT INTO dbo.INTERVENTION_CLIENT 
VALUES( 73, '2014-02-12', 'Client 4', 9, 750 );

Nous voici donc avec un jeu de données pour janvier et février. Si l’on refait la requête précédente, nous aurons de nouvelles lignes dans le tableau.


Nous observons bien les 2 mois et les noms des employés associés à chaque mois d’intervention.

Ce total n’est pas assez précis ! Il me faut le calcul annuel par employé ET un total annuel de l’entreprise !

Le GROUP BY est, sans conteste, quelque chose de courant dans les bases de données. Mais il a ses limites. La question, au-dessus, va nous poser un problème.
Comment pouvons nous agréger à différents niveau dans une même requête ?

La réponse est « Impossible en une requête avec un simple GROUP BY. Pour réaliser la demande, il nous faut 3 requêtes que l’on va unir avec la clause UNION.

La première, on l’a vu au-dessus, calcule au mois. La seconde (ci-dessous) calcule à l’employé.
Code sql : Sélectionner tout
1
2
3
4
5
6
SELECT NOM_EMPLOYE 
     , 'Total'            AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION ) AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY NOM_EMPLOYE;

Cette requête calcul le total des interventions par utilisateur et devrait être unie à la précédente afin de restituer en un seul jeu de données le total par utilisateur.
Enfin, une troisième requête est nécessaire pour calculer le total global des interventions.

Code sql : Sélectionner tout
1
2
3
4
5
SELECT 'Total'            AS NOM_EMPLOYE 
     , NULL               AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION ) AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID;

Ce tableau, représente l’union des 3 requêtes

En faisant un tri sur le nom puis le mois d’intervention, il est possible d’obtenir un tableau plus cohérent. Cependant, nous pouvons le constater, Virginie est dans l’ordre alphabétique et se retrouve après le total. Pour contourner ce problème, il nous faudrait rajouter une colonne avec un chiffre définis ce qui rajouterais encore du code !

Maintenant Le ROLLUP !

Comment cela fonctionne ? Et bien comme un GROUP BY nous citons une série de colonnes à regrouper.
Dans notre cas, toujours le mois et le consultant.

Code sql : Sélectionner tout
1
2
3
4
5
6
SELECT NOM_EMPLOYE 
     , MONTH( DATE_INTERVENTION ) AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY ROLLUP (NOM_EMPLOYE, MONTH( DATE_INTERVENTION ));
Vous constaterez la forme de l’écriture. Nous sommes toujours dans un GROUP BY à ceci près que nous précisons la fonctionnalité ROLLUP et que nous plaçons entre () les colonnes du groupement. Pourquoi ? Simplement parce que cette fonction nous permet, en une requête, d’afficher plusieurs groupements différents, tous séparés par une « , ». D’où le besoin des parenthèses pour permettre au SGBD de ne pas s’emmêler les pinceaux.

Le résultat est immédiat (ou presque tout dépend des volumes et de l'optimisation de votre base!)

Le SGBD a déjà calculé les sous-niveaux. Un total mensuel, un total par employé et un total !

Que fait concrètement ROLLUP ?

Et bien, pour chaque colonne du groupement en partant de la droite il va calculer les regroupements à tous les niveaux. Dans notre cas, il calcule le groupement par PERSONNE/ANNEE puis, simplement par PERSONNE et pour finir sans groupement.
Il rassemble nos 3 requêtes précédentes pour lesquelles j’ai dû faire une manipulation pour supprimer les colonnes en trop dans les groupements.

Comme vous pouvez le constater, les regroupements supérieurs n’ont pas de valeur. Il nous suffira simplement de mettre des fonctions ISNULL afin de placer les valeurs que l’on souhaite.

Code sql : Sélectionner tout
1
2
3
4
5
6
7
8
SELECT ISNULL( NOM_EMPLOYE, 'Total Annuel' ) AS NOM_EMPLOYE 
     , CASE WHEN NOM_EMPLOYE IS NULL AND MONTH( DATE_INTERVENTION ) IS NULL 
            THEN NULL 
            ELSE ISNULL( CAST( MONTH( DATE_INTERVENTION ) AS VARCHAR ), 'Total Mensuel' ) END AS [MOIS_INTERVENTION] 
     , SUM( FACTURATION )         AS [SOMME_FACTUREE] 
  FROM dbo.TB_EMPLOYES E 
       INNER JOIN dbo.INTERVENTION_CLIENT I ON E.ID_EMPLOYE = I.EMPLOYE_ID 
 GROUP BY ROLLUP (NOM_EMPLOYE, MONTH( DATE_INTERVENTION ));


Et voilà, comment en une requête on peut ressortir un tableau complet et juste !


Vous avez aimé cette actualité ? Alors partagez-la avec vos amis en cliquant sur les boutons ci-dessous :


 Poster un commentaire

Avatar de ylarvor ylarvor - Membre expérimenté https://www.developpez.com
le 27/04/2016 à 18:50
Belle rédaction, maitrise du logiciel, présentation claire. Bonne continuation.
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 28/04/2016 à 10:21
Citation Envoyé par ylarvor;bt2685
Belle rédaction, maitrise du logiciel, présentation claire. Bonne continuation.

Merci, de votre soutiens, d'autres vont venir pour compléter certains termes parfois "trop techniques" et qui nécessitent des explications.

Cordialement,
Avatar de jimbolion jimbolion - Expert éminent https://www.developpez.com
le 28/04/2016 à 11:19
Bravo, un rappel efficace des fonctions d'agrégations qui saura ravir les débutants comme les plus chevronnés.
Hâte de lire les nouveaux billets

Jimbolion
Avatar de yuva.iuva yuva.iuva - Membre à l'essai https://www.developpez.com
le 28/04/2016 à 16:36
Super, c'est avec grande joie que je découvre la fonction le groupby rollup
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 28/04/2016 à 17:59
Citation Envoyé par yuva.iuva;bt2689
Super, c'est avec grande joie que je découvre la fonction le groupby rollup

Au plaisir de rendre service.

Cordialement,
Avatar de fozzeuh fozzeuh - Membre confirmé https://www.developpez.com
le 06/05/2016 à 14:08
Super billet merci Lyche.
Avatar de juvamine juvamine - Membre confirmé https://www.developpez.com
le 08/06/2016 à 8:34
Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

Joli petit tuto !
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 08/06/2016 à 15:03
Citation Envoyé par juvamine;bt2828
Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

Joli petit tuto !

Merci poto ^_^
Avatar de WebPac WebPac - Membre confirmé https://www.developpez.com
le 25/07/2016 à 13:41
Salut,

cet article est très instructif.
Par contre, est-ce que la clause GROUP BY ROLL UP est supportée par beaucoup de base de données ?
Je présume que c'est le cas de SQL Server et Oracle, mais est-ce le cas pour des bases telles que MySQL, PostgreSQL ou Firebird ?

Merci.
Avatar de Lyche Lyche - Expert confirmé https://www.developpez.com
le 29/07/2016 à 11:20
Citation Envoyé par WebPac;bt2960
Salut,

cet article est très instructif.
Par contre, est-ce que la clause GROUP BY ROLL UP est supportée par beaucoup de base de données ?
Je présume que c'est le cas de SQL Server et Oracle, mais est-ce le cas pour des bases telles que MySQL, PostgreSQL ou Firebird ?

Merci.

Bonjour,

Très bonne question en effet. Il se trouve que MySQL et PGSQL implément tout ou partie de ces fonctionnalités Firebird lui n'en a implémenté aucune.

MySQL a implémenté un ROLLUP à sa façon en précisant la clause WITH ROLLUP après la déclaration du groupement
Exemple:
Code sql : Sélectionner tout
1
2
  
GROUP BY A, B, C WITH ROLLUP
PG SQL a quant-à lui implémenté les 3 groupements GROUPING SET, CUBE, ROLLUP, l'écriture est conforme à la norme SQL
Il est a noter que Sybase aussi a implémenté les 3 fonctionnalités.

Cordialement,
Offres d'emploi IT
Architecte et intégrateur scade/simulink H/F
Safran - Ile de France - Vélizy-Villacoublay (78140)
Ingénieur H/F
Safran - Ile de France - Moissy-Cramayel (77550)
Consultant sap finance/controlling H/F
Safran - Ile de France - Vélizy-Villacoublay (78140)

Voir plus d'offres Voir la carte des offres IT
Contacter le responsable de la rubrique SQL-Server