Wprowadzenie

PostgreSQL – statystyki podsumowujące i funkcje okna

Michel Semaan

Data Scientist

Motywacja

Łączna i narastająca liczba złotych medali USA na Letnich Igrzyskach Olimpijskich od 2004 r.

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

Status obrońcy tytułu w rzucie dyskiem

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

Plan kursu

Wprowadzenie do funkcji okna
Pobieranie, rangowanie i stronicowanie
Agregujące funkcje okna i ramki
Poza funkcjami okna

Zbiór danych Letnich Igrzysk Olimpijskich

Każdy wiersz reprezentuje medal przyznany na Letnich Igrzyskach Olimpijskich

Kolumny

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

Funkcje okna

Wykonują operację na zbiorze wierszy powiązanych z bieżącym wierszem
Podobne do funkcji agregujących GROUP BY, ale wszystkie wiersze pozostają w wyniku

Zastosowania

Pobieranie wartości z poprzednich lub następnych wierszy (np. wartości poprzedniego wiersza)
- Określanie statusu obrońcy tytułu
- Obliczanie wzrostu w czasie
Przypisywanie rang porządkowych (1., 2. itd.) do wierszy na podstawie ich wartości na posortowanej liście
Sumy narastające, średnie kroczące

Numery wierszy

Zapytanie

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

Wynik

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

Wprowadzenie ROW_NUMBER

Zapytanie

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

Wynik

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

Budowa funkcji okna

Zapytanie

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

Czas na ćwiczenia!

PostgreSQL – statystyki podsumowujące i funkcje okna