Introduktion

PostgreSQL – Sammanfattande statistik och fönsterfunktioner

Michel Semaan

Data Scientist

Motivation

USA:s totala och löpande antal guldmedaljer i sommar-OS sedan 2004

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

Försvarande mästare i diskuskastning

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

Kursöversikt

Introduktion till fönsterfunktioner
Hämtning, rangordning och paginering
Aggregerade fönsterfunktioner och ramar
Bortom fönsterfunktioner

Datamängden sommar-OS

Varje rad representerar en medalj tilldelad vid sommar-OS

Kolumner

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

Fönsterfunktioner

Utför en beräkning över en uppsättning rader som är relaterade till den aktuella raden
Liknar aggregatfunktioner med GROUP BY, men alla rader behålls i resultatet

Användningsområden

Hämta värden från föregående eller efterföljande rader (t.ex. föregående rads värde)
- Avgöra status som försvarande mästare
- Beräkna tillväxt över tid
Tilldela rangordning (1:a, 2:a, etc.) baserat på värdenas position i en sorterad lista
Löpande summor, glidande medelvärden

Radnummer

Fråga

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

Resultat

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

Introducerar ROW_NUMBER

Fråga

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

Resultat

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

En fönsterfunktions uppbyggnad

Fråga

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

Nu kör vi en övning!

PostgreSQL – Sammanfattande statistik och fönsterfunktioner