ORDER BY

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL

Michel Semaan

Data Scientist

Numéros de ligne

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     |
| ...  | ...                        | ...     | ...   |

ORDER BY

ORDER BY dans OVER trie les lignes liées à la ligne actuelle
- Exemple : Le classement par année dans l'ordre décroissant dans la clause OVER de la fonction ROW_NUMBER attribuera la valeur 1 aux lignes de l'année la plus récente

Tri par année dans l'ordre décroissant

Requête

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

Résultat

| Year | Event         | Country | Row_N |
|------|---------------|---------|-------|
| 2012 | Wg 96 KG      | IRI     | 1     |
| 2012 | 4X100M Medley | USA     | 2     |
| 2012 | Wg 84 KG      | RUS     | 3     |
| ...  | ...           | ...     | ...   |
| 2008 | 50M Freestyle | BRA     | 637   |
| 2008 | 96 - 120KG    | CUB     | 638   |
| ...  | ...           | ...     | ...   |

Tri par plusieurs colonnes

Requête

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

Résultat

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | + 100KG | FRA     | 1     |
| 2012 | + 67 KG | SRB     | 2     |
| 2012 | + 78KG  | CUB     | 3     |
| ...  | ...     | ...     | ...   |

Commander à l'intérieur et à l'extérieur OVER

Requête

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

Résultat

| Year | Event   | Country | Row_N |
|------|---------|---------|-------|
| 2012 | 1500M   | ALG     | 36    |
| 2000 | 1500M   | ALG     | 1998  |
| 1996 | 1500M   | ALG     | 2662  |
| ...  | ...     | ...     | ...   |

ORDER BY à l’intérieur de OVER prend effet avant le ORDER BY en dehors de OVER

Champion en titre

Un champion en titre est un champion qui a remporté les compétitions de l'année précédente et de l'année en cours
Les champions de l'année précédente et de l'année en cours doivent figurer dans la même ligne (dans deux colonnes différentes)

LAG

LAG(column, n) OVER (...) renvoie la valeur decolumn à la ligne située n lignes avant la ligne actuelle
- LAG(column, 1) OVER (...) renvoie la valeur de la ligne précédente

Champions actuels

Requête

SELECT
  Year, Country AS Champion
FROM Summer_Medals
WHERE
  Year IN (1996, 2000, 2004, 2008, 2012)
  AND Gender = 'Men' AND Medal = 'Gold'
  AND Event = 'Discus Throw';

Résultat

| Year | Champion |
|------|----------|
| 1996 | GER      |
| 2000 | LTU      |
| 2004 | LTU      |
| 2008 | EST      |
| 2012 | GER      |

Champions actuels et précédents

Requête

WITH Discus_Gold AS (
  SELECT
    Year, Country AS Champion
  FROM Summer_Medals
  WHERE
    Year IN (1996, 2000, 2004, 2008, 2012)
    AND Gender = 'Men' AND Medal = 'Gold'
    AND Event = 'Discus Throw')

SELECT
  Year, Champion,
  LAG(Champion, 1) OVER
    (ORDER BY Year ASC) AS Last_Champion
FROM Discus_Gold
ORDER BY Year ASC;

Résultat

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

Passons à la pratique !

Statistiques récapitulatives et fonctions de fenêtrage PostgreSQL