Úvod

PostgreSQL Summary Stats and Window Functions

Michel Semaan

Data Scientist

Motivace

Celkový a průběžný počet zlatých medailí USA na letních olympijských hrách od roku 2004

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

Status obhájce titulu v hodu diskem

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

Osnova kurzu

Úvod do okenních funkcí
Načítání, řazení a stránkování
Agregační okenní funkce a rámce
Nad rámec okenních funkcí

Datová sada letních olympijských her

Každý řádek představuje medaili udělenou na letních olympijských hrách

Sloupce

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

Okenní funkce

Provádí operaci přes sadu řádků vztahujících se k aktuálnímu řádku
Podobné agregačním funkcím GROUP BY, ale všechny řádky zůstávají ve výstupu

Využití

Načítání hodnot z předchozích nebo následujících řádků (např. hodnota předchozího řádku)
- Určení statusu obhájce titulu
- Výpočet růstu v čase
Přiřazení pořadí (1., 2., atd.) řádkům podle pozice jejich hodnot v seřazeném seznamu
Průběžné součty, klouzavé průměry

Čísla řádků

Dotaz

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

Výsledek

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

Funkce ROW_NUMBER

Dotaz

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

Výsledek

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

Anatomie okenní funkce

Dotaz

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

Pojďme si procvičit!

PostgreSQL Summary Stats and Window Functions