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

Fonction de totaux et sous-totaux

Dans ce tutoriel, nous verrons les différents agrégats implémentés dans les moteurs SQL. Nous allons apprendre dans une deuxième partie ce qu'il est possible de faire avec ces agrégats et notamment la fonction avancée d'agrégation ROLLUP.
Tous ces agrégats seront illustrés par des cas pratiques pour une meilleure compréhension.

Commentez Donner une note à l'article (5)

Article lu   fois.

L'auteur

Profil Pro

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. 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.

II. Les Agrégations

Les agrégations, malgré ce nom un peu effrayant, sont faciles à comprendre et à utiliser. 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.

III. Cas pratique

 
Sélectionnez
;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
Image non disponible

Cet exemple utilise un jeu de données assez simple : une liste de personnes avec leur âge, sur laquelle 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.

IV. 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.

V. Cas pratique

 
Sélectionnez
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 deux 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.

V-A. Faire la somme des salaires par département

 
Sélectionnez
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
Image non disponible

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.

V-B. 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 quatre départements et leur budget mensuel respectif.

Nous pourrions faire exactement les mêmes calculs que dans la première partie de notre billet.

V-C. Calculer la moyenne des salaires, le salaire minimum et maximum, ainsi que le nombre d'employés par département

 
Sélectionnez
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
Image non disponible

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.

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 colonnes du SELECT hors agrégat. Il est parfois nécessaire de retourner la question posée afin d'être le plus précis possible sur les colonnes.

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

V-C-1. Exemple

 
Sélectionnez
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.

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

 
Sélectionnez
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 );
Image non disponible

Nous avons pris en compte trois éléments de la demande :

  • mois d'intervention ;
  • collaborateur ;
  • somme des facturations.

Ces trois 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 de la colonne 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.

VI. 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.

À 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'une colonne (ou le résultat d'une requête) et le résultat d'un groupement.

À 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 missions. 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.

VII. 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.

 
Sélectionnez
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.

Image non disponible

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.

VIII. 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.

 
Sélectionnez
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.

Image non disponible

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

IX. 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.

X. Comment pouvons-nous agréger à différents niveaux 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 trois 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é.

 
Sélectionnez
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;
Image non disponible

Cette requête calcule 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.

 
Sélectionnez
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;
Image non disponible

Ce tableau représente l'union des trois requêtes :

Image non disponible

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, dans l'ordre alphabétique, « Virginie » est classée après « Total ». Pour contourner ce problème, il nous faudrait rajouter une colonne avec un chiffre défini ce qui rajouterait encore du code !

Maintenant le ROLLUP !

Comment cela fonctionne-t-il ? Eh bien comme un GROUP BY, nous citons une série de colonnes à regrouper.

Dans notre cas, toujours le mois et le consultant.

 
Sélectionnez
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 !)

Image non disponible

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

XI. Que fait concrètement ROLLUP ?

Eh 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 trois 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 de mettre des fonctions ISNULL afin de placer les valeurs que l'on souhaite.

 
Sélectionnez
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 ));
Image non disponible

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

XII. Remerciements

Nous tenons à remercier zoom61 pour la mise au gabarit et f-leb pour la relecture orthographique.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2016 Lyche. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.