, , Regroupements et Analyses avec SQL

Préambule

Cet article est librement inspiré d’une session animée par Birgitta HAUSER lors des universités de l’IBMi du 19 et 20 novembre 2024. Je remercie également Laurent CHAVANEL avec qui j’ai partagé une partie de l’analyse.

Présentation

Pour réaliser cet article, nous avons créé un fichier de données météorologiques quotidiennes de quatre villes françaises pendant cinq années (de 2020 à 2024).

Les données contenues dans le fichier CLIMAT sont :

  • La ville
  • Le jour (AAAA-MM-JJ)
  • Les précipitations en mm
  • La température minimale du jour (en °C)
  • La température maximale du jour (en °C)
  • La température moyenne du jour (en °C)
  • L’amplitude de température du jour (en °C)

Agréger les données avec LISTAGG

Cette fonction permet de rassembler dans un seul champ, les données issues de plusieurs lignes

SELECT VILLE,
       YEAR(DATEREL) Annee,
       MONTHNAME(DATEREL) Mois,
       LISTAGG(TMOY || '°C', ', ') "Températures moyennes du Mois"
    FROM CLIMAT
    WHERE YEAR(DATEREL) = 2020
          AND MONTH(DATEREL) = 1
    GROUP BY VILLE,
             YEAR(DATEREL),
             MONTHNAME(DATEREL)

Données brutes

Données avec la fonction LISTAGG

Agréger les données avec GROUP BY

Comme première analyse, on souhaite faire des statistiques annuelles pour chaque ville sur chaque année.

On utilise les fonctions :

  • SUM qui va nous permettre de faire le total des précipitations
  • MIN pour extraire la température minimale
  • MAX pour extraire la température maximale
  • AVG pour faire une moyenne (de la température ainsi que de l’amplitude des températures)

On notera que TOUTES les colonnes sans fonction d’agrégation doivent être regroupées dans un GROUP BY et nous ajoutons un ORDER BY pour classer nos données.

SELECT YEAR(DATEREL) "Année",
       VILLE,
       SUM(MMPLUIE) "Total des précipitations",
       MIN(TMIN) "Température Minimale",
       MAX(TMAX) "Température Maximale",
       CAST(AVG(TMOY) AS DEC(4, 2)) "Température Moyenne",
       CAST(AVG(TAMPLI) AS DEC(4, 2)) "Amplitude Moyenne"
    FROM CLIMAT
    GROUP BY YEAR(DATEREL),
             VILLE
    ORDER BY VILLE,
             "Année";

Utilisation de ROLLUP

Nous voulons réaliser un total des précipitations sur les cinq dernières années, pour chaque commune de notre fichier tout en conservant un total pour chaque année observée

SELECT VILLE,
       YEAR(DATEREL) "Année",
       SUM(MMPLUIE) "Total des précipitations"
    FROM CLIMAT
    GROUP BY ROLLUP (VILLE, YEAR(DATEREL))
    ORDER BY VILLE,
             "Année";

L’extension ROLLUP apportée au GROUP BY, nous permet d’avoir des sous totaux par :

  • VILLE / ANNEE
  • VILLE

Ainsi qu’un total général (ce qui, dans le cas présent n’a que peu d’intérêt, je vous l’accorde)

Autre exemple, le total des précipitations par mois pour une seule ville.

SELECT VILLE,
       YEAR(DATEREL) "Année",
       MONTH(DATEREL) Mois,
       SUM(MMPLUIE) "Total des précipitations"
    FROM GG.CLIMAT
    WHERE VILLE = 'LYON'
    GROUP BY ROLLUP (VILLE, YEAR(DATEREL), MONTH(DATEREL));

…

…

Utilisation de CUBE

Cette extension nous permet d’obtenir plusieurs type de sous-totaux dans une même extraction

SELECT VILLE,
       YEAR(DATEREL) Annee,
       MONTH(DATEREL) Mois,
       SUM(MMPLUIE) "Total des précipitations"
    FROM CLIMAT
    WHERE VILLE = 'LYON'
    GROUP BY CUBE (VILLE, YEAR(DATEREL), MONTH(DATEREL));
  • Par VILLE et ANNEE
  • Par VILLE et sur la période de mesure
  • Sur la période de mesure (valeur identique à la précédente car une seule ville sélectionnée ici)
  • Par VILLE pour chaque mois de la période sélectionnée (ou simplement pour chaque mois de la période sélectionnée)

Pour Lyon, on a, par exemple, un total de précipitations de 188.00 mm pour tous les mois de janvier ou 400.00 mm pour tous les mois de septembre entre 2020 et 2024

Utilisation de GROUPING SETS

Cette extension permet de faire des regroupements choisis. Cela permet de faire une sélection des regroupements plus fine que celle réalisée avec CUBE.

Select VILLE, Year(DATEREL) Annee, month(DATEREL) Mois,
       sum(MMPLUIE) "Total des précipitations",
       Cast(Avg(TMOY) as Dec(4, 2)) "Température Moyenne"
   From CLIMAT
   WHERE VILLE in ('LYON', 'MARSEILLE', 'PARIS')   
   Group By GROUPING SETS((VILLE, YEAR(DATEREL)), (VILLE, month(DATEREL)))
   ORDER BY VILLE, YEAR(DATEREL), month(DATEREL);

Dans cet exemple, on fait des regroupements par VILLE/ANNEES et VILLE/MOIS dans une seule extraction

Tableau Croisé avec Agrégation et CASE

Avec SUM

Select VILLE, Year(DATEREL) Annee,
    sum(case when month(DATEREL)= 1 then MMPLUIE else 0 end) as "mm Janvier",     
    sum(case when month(DATEREL)= 2 then MMPLUIE else 0 end) as "mm Février", 
    sum(case when month(DATEREL)= 3 then MMPLUIE else 0 end) as "mm Mars", 
    sum(case when month(DATEREL)= 4 then MMPLUIE else 0 end) as "mm Avril", 
    sum(case when month(DATEREL)= 5 then MMPLUIE else 0 end) as "mm Mai", 
    sum(case when month(DATEREL)= 6 then MMPLUIE else 0 end) as "mm Juin", 
    sum(case when month(DATEREL)= 7 then MMPLUIE else 0 end) as "mm Juillet", 
    sum(case when month(DATEREL)= 8 then MMPLUIE else 0 end) as "mm Aout", 
    sum(case when month(DATEREL)= 9 then MMPLUIE else 0 end) as "mm Septembre", 
    sum(case when month(DATEREL)=10 then MMPLUIE else 0 end) as "mm Octobre", 
    sum(case when month(DATEREL)=11 then MMPLUIE else 0 end) as "mm Novembre", 
    sum(case when month(DATEREL)=12 then MMPLUIE else 0 end) as "mm Décembre",
    sum(MMPLUIE) as "Total Précipitations"
    FROM CLIMAT
    Group by Ville, Year(DATEREL)
    order by Ville, Year(DATEREL);

Avec AVG

Select VILLE, Year(DATEREL) Annee,    
    cast(avg(case when month(DATEREL)= 1 then TMOY else NULL end) as Dec(4, 2)) as "°C Janvier",     
    cast(avg(case when month(DATEREL)= 2 then TMOY else NULL end) as Dec(4, 2)) as "°C Février", 
    cast(avg(case when month(DATEREL)= 3 then TMOY else NULL end) as Dec(4, 2)) as "°C Mars", 
    cast(avg(case when month(DATEREL)= 4 then TMOY else NULL end) as Dec(4, 2)) as "°C Avril", 
    cast(avg(case when month(DATEREL)= 5 then TMOY else NULL end) as Dec(4, 2)) as "°C Mai", 
    cast(avg(case when month(DATEREL)= 6 then TMOY else NULL end) as Dec(4, 2)) as "°C Juin", 
    cast(avg(case when month(DATEREL)= 7 then TMOY else NULL end) as Dec(4, 2)) as "°C Juillet", 
    cast(avg(case when month(DATEREL)= 8 then TMOY else NULL end) as Dec(4, 2)) as "°C Aout", 
    cast(avg(case when month(DATEREL)= 9 then TMOY else NULL end) as Dec(4, 2)) as "°C Septembre", 
    cast(avg(case when month(DATEREL)=10 then TMOY else NULL end) as Dec(4, 2)) as "°C Octobre", 
    cast(avg(case when month(DATEREL)=11 then TMOY else NULL end) as Dec(4, 2)) as "°C Novembre", 
    cast(avg(case when month(DATEREL)=12 then TMOY else NULL end) as Dec(4, 2)) as "°C Décembre", 
    cast(avg(TMOY) as Dec(4, 2)) as "°C Moyenne"
    FROM CLIMAT
    Group by Ville, Year(DATEREL)
    order by Ville, Year(DATEREL);

Note sur l’utilisation de SUM vs AVG dans un tableau croisé

SUM totalise par mois, tandis que AVG calcule la moyenne.

Utilisation de ELSE NULL au lieu de ELSE 0 :

  • Avec ELSE 0, la fonction AVG prend en compte les zéros, ce qui fausse la moyenne si une valeur est absente.
  • NULL est ignoré par AVG, garantissant une moyenne correcte.

Par exemple, si nous écrivons 

AVG(CASE WHEN MONTH(DATEREL)= 1 THEN TMOY ELSE 0 END)

Alors la requête va additionner les températures moyennes de janvier MAIS aussi ajouter 0 pour tous les jours qui ne sont pas en janvier, le résultat sera donc faux au regard des températures mesurées… il en sera de même pour chaque mois.

La bonne pratique, pour l’utilisation de la fonction AVG est donc :

AVG(CASE WHEN MONTH(DATEREL)= 1 THEN TMOY ELSE NULL END)

Utiliser SQL pour faire une analyse

Nous pouvons également combiner différentes fonctions de SQL pour effectuer une analyse avec un rendu facilement lisible.

Dans le cas ci-dessous nous combinons CASE à différents niveaux, avec SUM afin de voir si les précipitations annuelles de chaque ville sont au-dessus ou en dessous des moyennes connues et les classer par rapport à un niveau de 800mm (choisi arbitrairement pour l’exercice)

SELECT VILLE,
       YEAR(DATEREL) Annee,       
       CASE 
            WHEN VILLE = 'KOUROU' THEN
               CASE
                   WHEN SUM(MMPLUIE) > 2560 THEN 'Excédent'
                   ELSE 'Déficit'
               END
            WHEN VILLE = 'LYON' THEN
               CASE
                   WHEN SUM(MMPLUIE) > 830 THEN 'Excédent'
                   ELSE 'Déficit'
               END
            WHEN VILLE = 'MARSEILLE' THEN
               CASE
                   WHEN SUM(MMPLUIE) > 453 THEN 'Excédent'
                   ELSE 'Déficit'
               END
            WHEN VILLE = 'PARIS' THEN
               CASE
                   WHEN SUM(MMPLUIE) > 600 THEN 'Excédent'
                   ELSE 'Déficit'
               END
       END "NIVEAU",        
       CASE
           WHEN SUM(MMPLUIE) > 800 THEN SUM(MMPLUIE)
           ELSE 0
       END "> 800 mm",
       CASE
           WHEN SUM(MMPLUIE) <= 800 THEN SUM(MMPLUIE)
           ELSE 0
       END "<= 800 mm"
    FROM CLIMAT
    GROUP BY Ville, YEAR(DATEREL)
    ORDER BY Ville, YEAR(DATEREL);

/par