Apprendre à contruire les agrégations de données dans une base de données de BI,

Un tutoriel de Lyche

Le 23 juillet 2016, par Lyche

0PARTAGES

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 !

Une erreur dans cette actualité ? Signalez-nous-la !

Votre nom : Votre e-mail :

Décrivez l'erreur que vous souhaitez porter à notre connaissance :

Identifiez-vous

Créer un compte

Apprendre à contruire les agrégations de données dans une base de données de BI,

Un tutoriel de Lyche

Identifiant
Mot de passe

Mot de passe oublié ?

Identifiez-vous

Créer un compte

Apprendre à contruire les agrégations de données dans une base de données de BI, Un tutoriel de Lyche

Apprendre à contruire les agrégations de données dans une base de données de BI,

Un tutoriel de Lyche