Voir le flux RSS

Blog de Lyche

[Actualité] L’agrégation de données

Note : 6 votes pour une moyenne de 4,83.
par , 26/04/2016 à 14h59 (2100 Affichages)

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 - Visualiser dans une fenêtre à part
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
Nom : Exemple1.jpg
Affichages : 2994
Taille : 9,2 Ko
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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
Nom : Exemple2.jpg
Affichages : 2966
Taille : 16,3 Ko
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 - Visualiser dans une fenêtre à part
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

Nom : Exemple3.jpg
Affichages : 2987
Taille : 24,9 Ko
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 - Visualiser dans une fenêtre à part
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 - Visualiser dans une fenêtre à part
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 );

Nom : Exemple4.jpg
Affichages : 2970
Taille : 21,0 Ko

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 - Visualiser dans une fenêtre à part
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.

Nom : Exemple5.jpg
Affichages : 2963
Taille : 18,9 Ko

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 - Visualiser dans une fenêtre à part
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.

Nom : Exemple6.jpg
Affichages : 2989
Taille : 35,2 Ko

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 - Visualiser dans une fenêtre à part
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;

Nom : Exemple7.jpg
Affichages : 2959
Taille : 22,8 Ko

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 - Visualiser dans une fenêtre à part
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;

Nom : Exemple8.jpg
Affichages : 2958
Taille : 9,1 Ko

Ce tableau, représente l’union des 3 requêtes
Nom : Exemple9.jpg
Affichages : 2986
Taille : 54,2 Ko

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 - Visualiser dans une fenêtre à part
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!)
Nom : Exemple10.jpg
Affichages : 2994
Taille : 55,6 Ko

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 - Visualiser dans une fenêtre à part
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 ));

Nom : Exemple11.jpg
Affichages : 2979
Taille : 58,5 Ko


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

Envoyer le billet « L’agrégation de données » dans le blog Viadeo Envoyer le billet « L’agrégation de données » dans le blog Twitter Envoyer le billet « L’agrégation de données » dans le blog Google Envoyer le billet « L’agrégation de données » dans le blog Facebook Envoyer le billet « L’agrégation de données » dans le blog Digg Envoyer le billet « L’agrégation de données » dans le blog Delicious Envoyer le billet « L’agrégation de données » dans le blog MySpace Envoyer le billet « L’agrégation de données » dans le blog Yahoo

Mis à jour 10/08/2016 à 23h20 par Lyche

Tags: sql, sql server, t-sql
Catégories
Sans catégorie

Commentaires

  1. Avatar de ylarvor
    • |
    • permalink
    Belle rédaction, maitrise du logiciel, présentation claire. Bonne continuation.
  2. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par ylarvor
    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,
  3. Avatar de jimbolion
    • |
    • permalink
    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
  4. Avatar de yuva.iuva
    • |
    • permalink
    Super, c'est avec grande joie que je découvre la fonction le groupby rollup
  5. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par yuva.iuva
    Super, c'est avec grande joie que je découvre la fonction le groupby rollup
    Au plaisir de rendre service.

    Cordialement,
  6. Avatar de fozzeuh
    • |
    • permalink
    Super billet merci Lyche.
  7. Avatar de juvamine
    • |
    • permalink
    Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

    Joli petit tuto !
  8. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par juvamine
    Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

    Joli petit tuto !
    Merci poto ^_^
  9. Avatar de WebPac
    • |
    • permalink
    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.
  10. Avatar de Lyche
    • |
    • permalink
    Citation Envoyé par WebPac
    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 - Visualiser dans une fenêtre à part
    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,
  11. Avatar de MaitrePylos
    • |
    • permalink
    Pour PostgreSQL il faut la version 9.4 minimum.
    Je viens de faire des essais et avec quelques modification syntaxique on arrive à un résultats (différents dans les montants, mais je suppose, qu'on utilise pas les même valeurs ).

    Voici ce que j'ai modifié pour faire fonctionner la dernière requête dans PostgreSQL 9.5

    Create table et Insertion :

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    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
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
     
     
    CREATE TABLE tb_departements ( 
      id_departement  INT PRIMARY KEY 
    , nom_departement VARCHAR( 50 ) ); 
     
     
    CREATE TABLE tb_employes ( 
      id_employe      INT PRIMARY KEY 
    , nom_employe     VARCHAR( 50 ) NOT NULL 
    , age_employe     INT 
    , departement_id  INT 
    , salaire_employe MONEY ); 
     
     
    INSERT INTO tb_departements 
    VALUES( 1, 'Administration'       ); 
    INSERT INTO tb_departements 
    VALUES( 2, 'Ressources Humaines'  ); 
    INSERT INTO tb_departements 
    VALUES( 3, 'Service Informatique' ); 
    INSERT INTO tb_departements 
    VALUES( 4, 'Comptabilité'         ); 
     
    INSERT INTO tb_employes 
    VALUES( 1, 'Georges', 74, 4, 2480.3 ); 
    INSERT INTO tb_employes 
    VALUES( 2, 'Pierre' , 17, 3, 1387.2 ); 
    INSERT INTO tb_employes 
    VALUES( 3, 'Bernard', 63, 1, 3499.8 ); 
    INSERT INTO tb_employes 
    VALUES( 4, 'John'   , 23, 3, 1876.9 ); 
    INSERT INTO tb_employes 
    VALUES( 5, 'Jérome' , 45, 2, 2286.6 ); 
    INSERT INTO tb_employes 
    VALUES( 6, 'Lina', 30, 3, 2230.4 ); 
    INSERT INTO tb_employes 
    VALUES( 7, 'Marie', 26, 3, 1980.4 ); 
    INSERT INTO tb_employes 
    VALUES( 8, 'Virginie', 37, 3, 2730.4 ); 
    INSERT INTO tb_employes 
    VALUES( 9, 'Hélène', 33, 3, 2430.4 );
     
     
     
    CREATE TABLE intervention_client ( 
      id_intervention   INT PRIMARY KEY 
    , date_intervention Date 
    , nom_client        VARCHAR( 50 ) 
    , employe_id        INT 
    , facturation       Money ) 
     
     
    INSERT INTO intervention_client 
    VALUES(  1, '2014-01-02', 'Client 1', 2, 500 ); 
    INSERT INTO intervention_client 
    VALUES(  2, '2014-01-03', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  3, '2014-01-06', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  4, '2014-01-07', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  5, '2014-01-08', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  6, '2014-01-09', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  7, '2014-01-10', 'Client 1', 2, 650 ); 
    INSERT INTO intervention_client 
    VALUES(  8, '2014-01-03', 'Client 1', 4, 400 ); 
    INSERT INTO intervention_client 
    VALUES(  9, '2014-01-04', 'Client 1', 4, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 10, '2014-01-06', 'Client 1', 4, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 11, '2014-01-07', 'Client 1', 4, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 12, '2014-01-08', 'Client 1', 4, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 13, '2014-01-03', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 14, '2014-01-04', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 15, '2014-01-06', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 16, '2014-01-07', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 17, '2014-01-08', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 18, '2014-01-09', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 19, '2014-01-10', 'Client 2', 6, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 20, '2014-01-06', 'Client 3', 7, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 21, '2014-01-07', 'Client 3', 7, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 22, '2014-01-08', 'Client 3', 7, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 23, '2014-01-09', 'Client 3', 7, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 24, '2014-01-10', 'Client 3', 7, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 25, '2014-01-06', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 26, '2014-01-07', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 27, '2014-01-08', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 28, '2014-01-09', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 29, '2014-01-10', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 30, '2014-01-03', 'Client 4', 9, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 31, '2014-01-06', 'Client 4', 9, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 32, '2014-01-07', 'Client 4', 9, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 33, '2014-01-08', 'Client 4', 9, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 34, '2014-01-09', 'Client 4', 9, 650 ); 
    INSERT INTO intervention_client 
    VALUES( 35, '2014-01-10', 'Client 4', 9, 650 );
     
     
    INSERT INTO intervention_client 
    VALUES( 36, '2014-02-03', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 37, '2014-02-04', 'Client 1', 2, 500 ); 
    INSERT INTO intervention_client 
    VALUES( 38, '2014-02-05', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 39, '2014-02-06', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 40, '2014-02-07', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 41, '2014-02-10', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 42, '2014-02-11', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 43, '2014-02-12', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 44, '2014-02-13', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 45, '2014-02-14', 'Client 1', 2, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 46, '2014-02-03', 'Client 1', 4, 400 ); 
    INSERT INTO intervention_client 
    VALUES( 47, '2014-02-04', 'Client 1', 4, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 48, '2014-02-05', 'Client 1', 4, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 49, '2014-02-06', 'Client 1', 4, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 50, '2014-02-07', 'Client 1', 4, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 51, '2014-02-03', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 52, '2014-02-04', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 53, '2014-02-05', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 54, '2014-02-06', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 55, '2014-02-07', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 56, '2014-02-10', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 57, '2014-02-19', 'Client 2', 6, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 58, '2014-02-06', 'Client 3', 7, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 59, '2014-02-07', 'Client 3', 7, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 60, '2014-02-10', 'Client 3', 7, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 61, '2014-02-11', 'Client 3', 7, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 62, '2014-02-12', 'Client 3', 7, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 63, '2014-02-06', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 64, '2014-02-07', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 65, '2014-02-10', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 66, '2014-02-11', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 67, '2014-02-12', 'Client 3', 8, 700 ); 
    INSERT INTO intervention_client 
    VALUES( 68, '2014-02-03', 'Client 4', 9, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 69, '2014-02-06', 'Client 4', 9, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 70, '2014-02-07', 'Client 4', 9, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 71, '2014-02-10', 'Client 4', 9, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 72, '2014-02-11', 'Client 4', 9, 750 ); 
    INSERT INTO intervention_client 
    VALUES( 73, '2014-02-12', 'Client 4', 9, 750 );


    Requêtes :

    Code sql : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    SELECT COALESCE( nom_employe, 'Total Annuel' ) AS nom_employe 
         , CASE WHEN nom_employe IS NULL AND EXTRACT(MONTH FROM date_intervention ) IS NULL 
                THEN NULL 
                ELSE COALESCE( CAST( EXTRACT(MONTH FROM date_intervention ) AS VARCHAR ), 'Total Mensuel' ) END AS "MOIS_INTERVENTION" 
         , SUM( facturation )         AS "SOMME_FACTUREE" 
      FROM tb_employes e 
           INNER JOIN intervention_client i ON e.id_employe = i.employe_id 
     GROUP BY ROLLUP (nom_employe, EXTRACT(MONTH FROM date_intervention ));
  12. Avatar de Lyche
    • |
    • permalink
    très intéressant, merci pour ton retour