ORDER BY

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen

Michel Semaan

Data Scientist

Zeilennummern

Abfrage

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

Ergebnis*

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

Verwende ORDER BY

ORDER BY in OVER sortiert die zu der aktuellen Zeile zugehörigen Zeilen
- Beispiel: Wenn du in der OVER-Klausel von ROW_NUMBER die Zeilen in absteigender Reihenfolge nach Jahr sortierst, wird den Zeilen des aktuellsten Jahres der Wert 1 zugewiesen.

Sortieren nach Jahr in absteigender Reihenfolge

Abfrage

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

Ergebnis

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

Sortieren nach mehreren Spalten

Abfrage

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

Ergebnis

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

Sortierungen außerhalb und innerhalb OVER

Abfrage

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;

Ergebnis

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

ORDER BY innerhalb von OVER wird vor ORDER BY außerhalb von OVER berücksichtigt

Titelverteidiger

Titelverteidiger ist Sieger, der die Spiele im vorherigen Austragungsjahr und im aktuellen Jahr gewonnen hat
Gewinner vom letzten und diesem Austragungsjahr müssen in derselben Zeile stehen (in zwei verschiedenen Spalten)

Verwende LAG

LAG(column, n) OVER (...) gibt Wert von column in der Zeile n Zeilen vor aktueller Zeile zurück
- LAG(column, 1) OVER (...) gibt Wert vorheriger Zeile zurück

Aktuelle Gewinner

Abfrage

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

Ergebnis

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

Aktuelle und letzte Gewinner

Abfrage

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;

Ergebnis

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

Lass uns üben!

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen