Introduction

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL

Michel Semaan

Data Scientist

Motivation

Total et total cumulatif des médailles d’or des États-Unis 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 agrégées et cadres
Au-delà des fonctions de fenêtrage

Ensemble de données sur les Jeux olympiques d'été

Chaque ligne représente une médaille décernée lors des Jeux olympiques d'été

Colonnes

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

Fonctions de fenêtrage

Effectuer une opération sur un ensemble de lignes qui sont liées d'une manière ou d'une autre à la ligne actuelle
Semblable aux fonctions d'agrégation GROUP BY, mais toutes les lignes sont conservées dans le résultat

Utilisations

Récupération de valeurs à partir des lignes précédentes ou suivantes (par exemple, récupération de la valeur de la ligne précédente)
- Détermination du statut de champion en titre
- Calculer la croissance au fil du temps
Attribuer des rangs ordinaux (1er, 2e, etc.) aux lignes en fonction de la position de leurs valeurs dans une liste triée
Totaux cumulés, 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     |
| ...  | ...                        | ...     |

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 !

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL