Einleitung

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen

Michel Semaan

Data Scientist

Problem

Summe und laufende Summe der Goldmedaillen der USA bei den Olympischen Sommerspielen seit 2004

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

Titelverteidiger im Diskuswurf

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

Gliederung des Kurses

Einführung in Fensterfunktionen
Abrufen, Ranken und Paging
Aggregierte Fensterfunktionen und Rahmen
Über Fensterfunktionen hinaus

Datensatz zu den Olympischen Sommerspielen

Jede Zeile steht für eine bei Olympischen Sommerspielen vergebene Medaille

Spalten

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

Fensterfunktionen

Führen Operation über mehrere Zeilen aus, die irgendwie mit aktueller Zeile zusammenhängen
Ähnlich wie die Aggregatfunktionen GROUP BY, aber alle Zeilen bleiben in der Ausgabe

Anwendungsfälle

Werte aus vorherigen oder nachfolgenden Zeilen abrufen (z. B. den Wert der vorherigen Zeile abrufen)
- Titelverteidiger bestimmen
- Berechnung des Wachstums im Laufe der Zeit
Ordnungszahlen (1., 2. usw.) den Zeilen zuweisen, je nach Position der Werte in einer sortierten Liste
Laufende Summen, gleitende Durchschnitte

Zeilennummern

Abfrage

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

Ergebnis

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

Verwende ROW_NUMBER

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

Aufbau einer Fensterfunktion

Abfrage

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

Lass uns üben!

PostgreSQL: Zusammenfassende Statistiken und Fensterfunktionen