Вступ

Підсумкова статистика та віконні функції в PostgreSQL

Michel Semaan

Data Scientist

Мотивація

Сума та наростаючий підсумок золотих медалей США на літніх Олімпіадах з 2004 року

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

Статус чинного чемпіона у метанні диска

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

Опис курсу

Вступ до віконних функцій
Вибірка, ранжування та розбиття на сторінки
Агрегатні віконні функції та фрейми
Поза межами віконних функцій

Набір даних про Літні Олімпіади

Кожен рядок — це медаль, здобута на Літніх Олімпійських іграх

Стовпці

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

Віконні функції

Виконують операцію над набором рядків, пов'язаних із поточним рядком
Подібні до агрегатів GROUP BY, але всі рядки залишаються у виводі

Застосування

Отримання значень із попередніх чи наступних рядків (наприклад, значення попереднього рядка)
- Визначення статусу чинного чемпіона
- Обчислення зростання з часом
Присвоєння порядкових рангів (1‑й, 2‑й, тощо) за позицією у відсортованому списку
Наростаючі підсумки, ковзні середні

Номери рядків

Запит

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

Результат

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

Знайомство з ROW_NUMBER

Запит

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

Результат

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

Будова віконної функції

Запит

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

Давайте потренуємось!

Підсумкова статистика та віконні функції в PostgreSQL