mars 2025 – Gaia

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);

Si vous avez mis en œuvre le journal vous pouvez et même devez analyser les refus de connexion.
Le plus souvent c’est un mauvais mot de passe mais ca peut être aussi une attaque, ou un comportement douteux

Voici une requête simple qui permet cette analyse rapide

SELECT JOB_NAME, USER_NAME, FUNCTION, MESSAGE_ID, MESSAGE_TIMESTAMP
FROM TABLE(QSYS2.DISPLAY_JOURNAL(‘QSYS’, ‘QAUDJRN’))
WHERE MESSAGE_ID IN (‘CPF2234’, ‘CPF1107’, ‘CPF1393’)
ORDER BY MESSAGE_TIMESTAMP DESC;

Les messages traités ici
CPF2234 Tentative de connexion échouée.
CPF1107 Mot de passe incorrect.
CPF1393 Accès refusé.

Remarque :
Vous pouvez ajouter des filtres (plage horaire, autres messages de refus , etc …)
Vous devrez découper vous même la zone entry data, vous pouvez également utiliser les fonctions table QSYS2.DISPLAY_JOURNALxx spécialiser par TYPE

Plus d’information ici https://www.ibm.com/support/pages/qsys2displayjournal

Archive pour le mois : mars, 2025

Préambule

Présentation

Agréger les données avec LISTAGG

Agréger les données avec GROUP BY

Utilisation de ROLLUP

Utilisation de CUBE

Utilisation de GROUPING SETS

Tableau Croisé avec Agrégation et CASE

Avec SUM

Avec AVG

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

Utiliser SQL pour faire une analyse