Analyse de données avec SQL

Module S70

Dalibo SCOP

25.09

5 septembre 2025

Sur ce document

Formation Module S70
Titre Analyse de données avec SQL
Révision 25.09
PDF https://dali.bo/s70_pdf
EPUB https://dali.bo/s70_epub
HTML https://dali.bo/s70_html
Slides https://dali.bo/s70_slides
TP https://dali.bo/s70_tp
TP (solutions) https://dali.bo/s70_solutions

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • Paternité
  • Pas d’utilisation commerciale
  • Partage des conditions initiales à l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

SQL pour l’analyse de données

Préambule

  • Analyser des données est facile avec PostgreSQL
    • opérations d’agrégation disponibles
    • fonctions OLAP avancées
  • Agrégation de données
  • Clause FILTER
  • Fonctions WINDOW
  • GROUPING SETS, ROLLUP, CUBE
  • WITHIN GROUPS

Objectifs

  • Écrire des requêtes encore plus complexes
  • Analyser les données en amont
    • pour ne récupérer que le résultat

Tables d’exemple

  • Table employes
  • Tables population et continents

Agrégats

  • SQL dispose de fonctions de calcul d’agrégats
  • Utilité :
    • calcul de sommes, moyennes, valeur minimale et maximale
    • nombreuses fonctions statistiques disponibles

Agrégats avec GROUP BY

  • agrégat + GROUP BY
  • Utilité
    • effectue des calculs sur des regroupements : moyenne, somme, comptage, etc.
    • regroupement selon un critère défini par la clause GROUP BY
    • exemple : calcul du salaire moyen de chaque service

GROUP BY : principe

GROUP BY : syntaxe et exemple

SELECT service,
       sum(salaire) AS salaires_par_service
  FROM employes
 GROUP BY service;
   service   | salaires_par_service
-------------+----------------------
 Courrier    |              7500.00
 Direction   |             10000.00
 Publication |              7000.00
(3 lignes)

Agrégats et ORDER BY

  • Extension propriétaire de PostgreSQL
    • ORDER BY dans la fonction d’agrégat
  • Utilité :
    • ordonner les données agrégées
    • surtout utile avec array_agg, string_agg et xmlagg

Utiliser ORDER BY avec un agrégat

SELECT service,
       string_agg(nom, ', ' ORDER BY nom) AS liste_employes
  FROM employes
 GROUP BY service;
   service   |  liste_employes
-------------+-------------------
 Courrier    | Fantasio, Lagaffe
 Direction   | Dupuis
 Publication | Lebrac, Prunelle
(3 lignes)

Créer un tableau avec un agrégat : array_agg

SELECT service,
       array_agg(nom ORDER BY nom) AS liste_employes
  FROM employes
 GROUP BY service;
   service   |   liste_employes
-------------+--------------------
 Courrier    | {Fantasio,Lagaffe}
 Direction   | {Dupuis}
 Publication | {Lebrac,Prunelle}

Clause FILTER

  • Clause FILTER
  • Utilité :
    • filtrer les données sur les agrégats
    • évite les expressions CASE complexes
  • SQL:2003

Filtrer avec CASE

  • Syntaxe fastidieuse :
SELECT count(*) AS compte_pays,
       count(CASE WHEN continent='Europe' THEN 'Oui'
                  ELSE NULL
              END) AS compte_pays_europeens
FROM   population p ;
 compte_pays | compte_pays_europeens 
-------------+-----------------------
          88 |                    34

Filtrer avec FILTER (WHERE…)

  • La même requête écrite avec la clause FILTER :
SELECT count(*) AS compte_pays,
       count(*) FILTER (WHERE continent='Europe')
                AS compte_pays_europeens
FROM population ;

Fonctions de fenêtrage

Des fonctionnalités trop peu connues

Fenêtrage et regroupement : premier exemple

SELECT matricule, salaire, service,
       SUM(salaire) OVER (PARTITION BY service)
       AS total_salaire_service
  FROM employes;
 matricule | salaire  |   service   | total_salaire_service
-----------+----------+-------------+-----------------------
 00000004  |  4500.00 | Courrier    |               7500.00
 00000020  |  3000.00 | Courrier    |               7500.00
 00000001  | 10000.00 | Direction   |              10000.00
 00000006  |  4000.00 | Publication |               7000.00
 00000040  |  3000.00 | Publication |               7000.00

Fonctions de fenêtrage : utilisation

  • Fonctions de fenêtrage avec OVER ( … )
    • travail sur des ensembles de données, regroupés et triés, indépendamment de la requête principale
  • Utilisation :
    • référence à d’autres lignes de l’ensemble de données
    • différents critères d’agrégation dans la même requête
    • fonctions de classement
  • Exemples :
    • ratios entre ligne et ensemble
    • sommes courantes, moyennes glissantes
    • évolution entre deux lignes
  • Performances

Fenêtrage et regroupement : OVER (PARTITION BY …)

SELECT …
 <agregation> OVER (PARTITION BY <colonnes>)
  FROM <liste_tables>
 WHERE <predicats>
  • NB : rien à voir avec le partitionnement d’une table

Fenêtrage et tri : OVER (ORDER BY …)

OVER (ORDER BY matricule)
  • Utilité :
    • numéroter les lignes : row_number()
    • classer des résultats : rank(), dense_rank()
    • faire appel à d’autres lignes du résultat : lead(), lag()
  • Le tri du fenêtrage est indépendant du ORDER BY des lignes

Fenêtrage et tri : row_number()

  • Pour numéroter des lignes :
SELECT row_number() OVER (ORDER BY nom),
       matricule, nom            
FROM   employes
ORDER BY matricule;
 row_number | matricule |   nom
------------+-----------+----------
          1 | 00000001  | Dupuis
          2 | 00000004  | Fantasio
          5 | 00000006  | Prunelle
          3 | 00000020  | Lagaffe
          4 | 00000040  | Lebrac
(5 lignes)

Fenêtrage et tri : numéroter des lignes sans critère

  • Pour numéroter des lignes :
SELECT row_number() OVER (),
       matricule, nom            
FROM   employes
ORDER BY nom DESC ;
 row_number | matricule |   nom
------------+-----------+----------
          3 | 00000006  | Prunelle
          5 | 00000040  | Lebrac
          4 | 00000020  | Lagaffe
          2 | 00000004  | Fantasio
          1 | 00000001  | Dupuis

Fenêtrage et tri : rang

  • Rang selon le salaire :
SELECT matricule, nom, salaire, service,
       rank() OVER (ORDER BY salaire),
       dense_rank() OVER (ORDER BY salaire) -- pas de trous
FROM   employes ;
 matricule |   nom    | salaire  |   service   | rank | dense_rank
-----------+----------+----------+-------------+------+------------
 00000020  | Lagaffe  |  3000.00 | Courrier    |    1 |          1
 00000040  | Lebrac   |  3000.00 | Publication |    1 |          1
 00000006  | Prunelle |  4000.00 | Publication |    3 |          2
 00000004  | Fantasio |  4500.00 | Courrier    |    4 |          3
 00000001  | Dupuis   | 10000.00 | Direction   |    5 |          4

Fenêtrage et tri : somme glissante (exemple)

  • Calcul d’une somme glissante :
SELECT matricule, salaire,
       SUM(salaire) OVER (ORDER BY matricule)
FROM   employes;
 matricule | salaire  |   sum
-----------+----------+----------
 00000001  | 10000.00 | 10000.00
 00000004  |  4500.00 | 14500.00
 00000006  |  4000.00 | 18500.00
 00000020  |  3000.00 | 21500.00
 00000040  |  3000.00 | 24500.00

Fenêtrage et tri : somme glissante (principe)

SUM(salaire) OVER (ORDER BY matricule)

Fenêtrage : regroupement et tri

  • On peut combiner :
    • OVER (PARTITION BY … ORDER BY …)
  • Utilité :
    • travailler sur des jeux de données ordonnés et isolés les uns des autres

Regroupement et tri : exemple

SELECT continent, pays, population,
       rank() OVER (PARTITION BY continent
                    ORDER BY population DESC)
              AS rang
  FROM population;
    continent     |       pays         | population | rang
------------------+--------------------+------------+------
 Afrique          | Nigéria            |      173.6 |    1
 Afrique          | Éthiopie           |       94.1 |    2
 Afrique          | Égypte             |       82.1 |    3
 Afrique          | Rép. dém. du Congo |       67.5 |    4
(…)
 Amérique du Nord | États-Unis         |      320.1 |    1
 Amérique du Nord | Canada             |       35.2 |    2
(…)

Regroupement et tri : principe

OVER (PARTITION BY continent
      ORDER BY population DESC)

Fonctions analytiques

  • first_value (), last_value, nth (), lag(), lead()…
  • À utiliser avec les fonctions de fenêtrage
  • Utilité :
    • faire référence à d’autres lignes du même ensemble
    • évite les auto-jointures complexes et lentes

lead() et lag() : exemple

SELECT pays, continent, population,
       lag(population) OVER (PARTITION BY continent
                             ORDER BY population DESC)
FROM   population
WHERE  continent in ('Europe','Afrique');
         pays          | continent | population |  lag
-----------------------+-----------+------------+--------
 Nigéria               | Afrique   |      173.6 |      
 Éthiopie              | Afrique   |       94.1 | 173.6
 Égypte                | Afrique   |       82.1 |  94.1
 …
 Tunisie               | Afrique   |       11.0 |  14.1
 Féd. de Russie        | Europe    |      142.8 |      
 Allemagne             | Europe    |       82.7 | 142.8
 France métropolitaine | Europe    |       64.3 |  82.7
 Royaume-Uni           | Europe    |       63.1 |  64.3
 Italie                | Europe    |       61.0 |  63.1
 …
 Malte                 | Europe    |        0.4 |   0.5
(53 lignes)

lead() et lag() : principe

lag(population) OVER (PARTITION BY continent
                      ORDER BY population DESC)

first/last/nth_value

  • first_value(colonne)
    • retourne la première valeur pour la colonne
  • last_value(colonne)
    • retourne la dernière valeur pour la colonne
  • nth_value(colonne, n)
    • retourne la n-ème valeur (en comptant à partir de 1) pour la colonne

first/last/nth_value : exemple

SELECT pays, continent, population,
       first_value(population)
           OVER (PARTITION BY continent
                 ORDER BY population DESC)
FROM   population
WHERE  continent in ('Europe','Afrique');
         pays          | continent | population | first_value 
-----------------------+-----------+------------+-------------
 Nigéria               | Afrique   |      173.6 |       173.6
 Éthiopie              | Afrique   |       94.1 |       173.6
 Égypte                | Afrique   |       82.1 |       173.6
 …
 Féd. de Russie        | Europe    |      142.8 |       142.8
 Allemagne             | Europe    |       82.7 |       142.8
 France métropolitaine | Europe    |       64.3 |       142.8
 …

Clause WINDOW

  • Pour factoriser la définition d’une fenêtre :
SELECT matricule, nom, salaire, service,
       rank() OVER w,
       dense_rank() OVER w
FROM   employes
WINDOW w AS (ORDER BY salaire) ;
  • Plusieurs fenêtres possibles

Fenêtre de travail

  • La fenêtre d’OVER (ORDER BY …) par défaut est :
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • Toutes les lignes :
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • Trois modes possibles :
    • par RANGE (par plage ; ci-dessus)
    • par ROWS (par nombre de lignes)
    • par GROUPS (selon valeurs des lignes)

Fenêtre de travail avec RANGE

  • Intervalle à bornes flou
  • Borne de départ :
    • UNBOUNDED PRECEDING: depuis le début de la partition
    • CURRENT ROW : depuis la ligne courante
  • Borne de fin :
    • UNBOUNDED FOLLOWING : jusqu’à la fin de la partition
    • CURRENT ROW : jusqu’à la ligne courante
    OVER (PARTITION BY …
      ORDER BY …
      RANGE BETWEEN UNBOUNDED PRECEDING
            AND     UNBOUNDED FOLLOWING )

Fenêtre de travail avec RANGE : exemple

SELECT pays, continent, population,
       last_value(population) OVER (
            PARTITION BY continent
            ORDER BY population DESC
            RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
            )
FROM   population
WHERE  continent in ('Europe','Afrique');
         pays          | continent | population | last_value 
-----------------------+-----------+------------+------------
 Nigéria               | Afrique   |      173.6 |       11.0
 Éthiopie              | Afrique   |       94.1 |       11.0
 Égypte                | Afrique   |       82.1 |       11.0
 Rép. dém. du Congo    | Afrique   |       67.5 |       11.0
 …
 Tunisie               | Afrique   |       11.0 |       11.0
 Féd. de Russie        | Europe    |      142.8 |        0.4
 Allemagne             | Europe    |       82.7 |        0.4
 France métropolitaine | Europe    |       64.3 |        0.4
 …
 Malte                 | Europe    |        0.4 |        0.4

Fenêtre de travail avec ROWS

  • Intervalle borné par un nombre de ligne défini avant et après la ligne courante
  • Borne de départ :
    • xxx PRECEDING : depuis les xxx valeurs devant la ligne courante
    • CURRENT ROW : depuis la ligne courante
  • Borne de fin :
    • xxx FOLLOWING : depuis les xxx valeurs derrière la ligne courante
    • CURRENT ROW : jusqu’à la ligne courante
    OVER (PARTITION BY …
     ORDER BY …
     ROWS BETWEEN 2 PRECEDING  AND  1 FOLLOWING )
  • Exemple : moyenne glissante

Fenêtre de travail avec GROUPS

  • Intervalle borné par un groupe de lignes de valeurs identiques défini avant et après la ligne courante
  • Borne de départ :
    • xxx PRECEDING : depuis les xxx groupes de valeurs identiques devant la ligne courante
    • CURRENT ROW : depuis la ligne courante ou le premier élément identique selonORDER BY
  • Borne de fin :
    • xxx FOLLOWING : depuis les xxx groupes de valeurs identiques derrière la ligne courante
    • CURRENT ROW : jusqu’à la ligne courante ou le dernier élément identique selon ORDER BY
    OVER (PARTITION BY …
          ORDER BY …
          GROUPS BETWEEN 2 PRECEDING AND 1 FOLLOWING

Définition de la fenêtre : EXCLUDE

  • Lignes à exclure de la fenêtre de données
  • EXCLUDE CURRENT ROW : exclut la ligne courante
  • EXCLUDE GROUP : exclut la ligne courante et le groupe de valeurs identiques dans l’ordre
  • EXCLUDE TIES : exclut les valeurs identiques à la ligne courante dans l’ordre mais pas la ligne courante
  • EXCLUDE NO OTHERS : pas d’exclusion (par défaut)

WITHIN GROUP

  • WITHIN GROUP
  • Utilité :
    • calcul de médianes, centiles

WITHIN GROUP : exemple

SELECT continent,
  percentile_disc(0.5)
    WITHIN GROUP (ORDER BY population) AS "mediane",
  percentile_disc(0.95)
    WITHIN GROUP (ORDER BY population) AS "95pct",
  ROUND(AVG(population), 1) AS moyenne
FROM population
GROUP BY continent ;
         continent         | mediane | 95pct  | moyenne
---------------------------+---------+--------+---------
 Afrique                   |    33.0 |  173.6 |    44.3
 Amérique du Nord          |    35.2 |  320.1 |   177.7
 Amérique latine. Caraïbes |    30.4 |  200.4 |    53.3
 Asie                      |    53.3 | 1252.1 |   179.9
 Europe                    |     9.4 |   82.7 |    21.8

Regroupement avancés

  • GROUPING SETS / ROLLUP / CUBE
  • Extension de GROUP BY
  • Utilité :
    • plusieurs agrégations différentes dans la même requête
    • moins de requêtes, meilleures performances

GROUPING SETS : données d’exemple

DROP TABLE IF EXISTS stock  ;

CREATE TABLE stock (
  piece     text,
  region    text,
  quantite  integer) ;

INSERT INTO stock
VALUES     ('ecrous', 'est',   50),
           ('ecrous', 'ouest',  0),
           ('ecrous', 'sud',   40),
           ('clous',  'est',   70),
           ('clous',  'nord',   0),
           ('vis',    'ouest', 50),
           ('vis',    'sud',   50),
           ('vis',    'nord',  60) ;

GROUPING SETS : exemple visuel

Émuler les GROUPING SETS avec GROUP BY

Sans GROUPING SET : deux GROUP BY

SELECT piece, NULL AS region, sum(quantite)
  FROM stock
  GROUP BY piece
UNION ALL
SELECT NULL, region, sum(quantite)
  FROM STOCK
  GROUP BY region;
 piece  | region | sum 
--------+--------+-----
 vis    |        | 160
 ecrous |        |  90
 clous  |        |  70
        | ouest  |  50
        | nord   |  60
        | est    | 120
        | sud    |  90

GROUPING SETS : exemple

SELECT piece,region,sum(quantite)
FROM   stock
GROUP BY GROUPING SETS (piece,region);
 piece  | region | sum
--------+--------+-----
 vis    |        | 160
 ecrous |        |  90
 clous  |        |  70
        | ouest  |  50
        | nord   |  60
        | est    | 120
        | sud    |  90

ROLLUP

  • ROLLUP
  • Utilité :
    • calcul de totaux dans la même requête

ROLLUP : exemple visuel

ROLLUP : exemple et résultat

SELECT piece, region, sum(quantite)
FROM   stock
GROUP BY ROLLUP (piece,region) ;
 piece  | region | sum 
--------+--------+-----
        |        | 320
 ecrous | ouest  |   0
 clous  | nord   |   0
 vis    | nord   |  60
 clous  | est    |  70
 vis    | sud    |  50
 ecrous | est    |  50
 ecrous | sud    |  40
 vis    | ouest  |  50
 vis    |        | 160
 ecrous |        |  90
 clous  |        |  70

CUBE

  • CUBE
  • Utilité :
    • calcul de totaux dans la même requête
    • sur toutes les clauses de regroupement

CUBE : exemple visuel

CUBE : Syntaxe

SELECT piece,region,sum(quantite)
FROM stock
GROUP BY CUBE (piece,region);
 piece  | region | sum 
--------+--------+-----
        |        | 320
 ecrous | ouest  |   0
 clous  | nord   |   0
 vis    | nord   |  60
 clous  | est    |  70
 vis    | sud    |  50
 ecrous | est    |  50
 ecrous | sud    |  40
 vis    | ouest  |  50
 vis    |        | 160
 ecrous |        |  90
 clous  |        |  70
        | ouest  |  50
        | nord   |  60
        | est    | 120
        | sud    |  90

GROUPING SETS, ROLLUP ou CUBE ?

  • CUBE peut le plus
  • mais forcément plus coûteux

Filtrer les lignes d’un certain regroupement

Pour que l’application repère les regroupements :

SELECT GROUPING(type_client,code_pays)::bit(2),
       GROUPING(type_client)::boolean AS g_type_cli,
       GROUPING(code_pays)::boolean   AS g_code_pays,
       type_client,
       code_pays,
       SUM(quantite*prix_unitaire) AS montant
       …

Affichage des tableaux croisés

  • PostgreSQL ne renvoie que des lignes
  • Dans psql : \crosstabview
  • Extension : tablefunc, fonction crosstab (text,text)

Conclusion

  • Les fonctions fenêtrées existent depuis SQL:2003
  • Ne vous limitez pas au SQL du XXᵉ siècle

Travaux pratiques

Travaux pratiques (solutions)