Introduction

Fonctions de synthèse et de fenêtre dans PostgreSQL

Michel Semaan

Data Scientist

Motivation

Total américain et cumul des médailles d'or aux Jeux olympiques d'été depuis 2004

| Year | Medals | Medals_RT |
|------|--------|-----------|
| 2004 | 116    | 116       |
| 2008 | 125    | 241       |
| 2012 | 147    | 388       |

Statut de champion en titre au lancer du disque

| Year | Champion | Last_Champion | Reigning_Champion |
|------|----------|---------------|-------------------|
| 1996 | GER      | null          | false             |
| 2000 | LTU      | GER           | false             |
| 2004 | LTU      | LTU           | true              |
| 2008 | EST      | LTU           | false             |
| 2012 | GER      | EST           | false             |

Plan du cours

Introduction aux fonctions de fenêtrage
Récupération, classement et pagination
Fonctions de fenêtrage d'agrégats et trames
Au‑delà des fonctions de fenêtrage

Ensemble de données des Jeux d'été

Chaque ligne représente une médaille remise aux Jeux olympiques d'été

Colonnes

Year, City
Sport, Discipline, Event
Athlete, Country, Gender
Medal

Fonctions de fenêtrage

Effectuer une opération sur un groupe de lignes liées à la ligne courante
Semblable aux fonctions d'agrégat GROUP BY, mais toutes les lignes restent en sortie

Usages

Récupérer des valeurs des lignes précédentes ou suivantes (p. ex. la valeur de la ligne précédente)
- Déterminer le statut de champion en titre
- Calculer la croissance dans le temps
Attribuer des rangs ordinaux (1er, 2e, etc.) selon la position dans une liste triée
Totaux cumulatifs, moyennes mobiles

Numéros de ligne

Requête

SELECT
  Year, Event, Country
FROM Summer_Medals
WHERE
  Medal = 'Gold';

Résultat

| Year | Event                      | Country |
|------|----------------------------|---------|
| 1896 | 100M Freestyle             | HUN     |
| 1896 | 100M Freestyle For Sailors | GRE     |
| 1896 | 1200M Freestyle            | HUN     |
| ...  | ...                        | ...     |

Découvrir ROW_NUMBER

Requête

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER () AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

Résultat

| Year | Event                      | Country | Row_N |
|------|----------------------------|---------|-------|
| 1896 | 100M Freestyle             | HUN     | 1     |
| 1896 | 100M Freestyle For Sailors | GRE     | 2     |
| 1896 | 1200M Freestyle            | HUN     | 3     |
| ...  | ...                        | ...     | ...   |

Anatomie d'une fonction de fenêtrage

Requête

SELECT
  Year, Event, Country,
  ROW_NUMBER() OVER () AS Row_N
FROM Summer_Medals
WHERE
  Medal = 'Gold';

FUNCTION_NAME() OVER (...)
- ORDER BY
- PARTITION BY
- ROWS/RANGE PRECEDING/FOLLOWING/UNBOUNDED

Passons à la pratique !

Fonctions de synthèse et de fenêtre dans PostgreSQL