Introductie

PostgreSQL Samenvattingsstatistieken en vensterfuncties

Michel Semaan

Data Scientist

Motivatie

USA totaal en cumulatief totaal gouden medailles Zomerspelen sinds 2004

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

Status titelverdediger 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 windowfuncties
Ophalen, ranken en pagineren
Aggregaat-windowfuncties en frames
Verder dan windowfuncties

Zomerspelen-dataset

Elke rij is een medaille op de Zomerspelen

Kolommen

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

Windowfuncties

Voer een bewerking uit over een set rijen die gerelateerd zijn aan de huidige rij
Lijkt op GROUP BY-aggregaten, maar alle rijen blijven in de output

Toepassingen

Waarden ophalen uit vorige of volgende rijen (bijv. de vorige rij)
- Titelverdediger bepalen
- Groei over tijd berekenen
Ordinale rangen toekennen (1e, 2e, enz.) op basis van positie in een gesorteerde lijst
Cumulatieve totals, 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 windowfunctie

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