ORDER BY

PostgreSQL Samenvattingsstatistieken en vensterfuncties

Michel Semaan

Data Scientist

Rijnummers

Query

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

Resultaat*

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

Maak kennis met ORDER BY

ORDER BY in OVER sorteert de rijen die bij de huidige rij horen
- Voorbeeld: sorteren op jaar aflopend in de OVER-clausule van ROW_NUMBER geeft 1 aan de meest recente jaren

Sorteren op Year in aflopende volgorde

Query

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

Resultaat

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

Sorteren op meerdere kolommen

Query

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

Resultaat

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

Sorteren in en buiten OVER

Query

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;

Resultaat

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

ORDER BY binnen OVER wordt toegepast vóór ORDER BY buiten OVER

Regerend kampioen

Een regerend kampioen heeft zowel het vorige als het huidige jaar gewonnen
Vorige en huidige kampioen moeten in dezelfde rij staan (twee kolommen)

Maak kennis met LAG

LAG(column, n) OVER (...) geeft de waarde van column terug n rijen vóór de huidige rij
- LAG(column, 1) OVER (...) geeft de waarde van de vorige rij

Huidige kampioenen

Query

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

Resultaat

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

Huidige en vorige kampioenen

Query

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;

Resultaat

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

Laten we oefenen!

PostgreSQL Samenvattingsstatistieken en vensterfuncties