Введение

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: сводная статистика и оконные функции