Introductie

PostgreSQL samenvattingsstatistieken en vensterfuncties

Michel Semaan

Data Scientist

Motivatie

Totaal en voortschrijdend totaal van Amerikaanse gouden medailles op de Zomerspelen sinds 2004

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

Status regerend kampioen discuswerpen

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

Cursusoverzicht

Introductie tot vensterfuncties
Ophalen, rangschikken en pagineren
Samenvattende vensterfuncties en frames
Verder dan vensterfuncties

Gegevensset Zomerspelen

Elke rij staat voor een uitgereikte medaille op de Zomerspelen

Kolommen

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

Vensterfuncties

Voer een bewerking uit over een set rijen die op een of andere manier bij de huidige rij horen
Lijkt op GROUP BY-aggregaties, maar alle rijen blijven in de output

Toepassingen

Waarden ophalen uit vorige of volgende rijen (bijv. de waarde van de vorige rij)
- Regerend-kampioenstatus bepalen
- Groei in de tijd berekenen
Ordinale rangen toekennen (1e, 2e, enz.) op basis van positie in een gesorteerde lijst
Voortschrijdende totalen, voortschrijdende gemiddelden

Rijnummers

Query

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

Resultaat

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

Maak kennis met ROW_NUMBER

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

Anatomie van een vensterfunctie

Query

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

Laten we oefenen!

PostgreSQL samenvattingsstatistieken en vensterfuncties