Зачем нужны оконные функции

Это фрагмент моего курса Оконные функции SQL с пошаговым изложением, наглядными иллюстрациями и практическими упражнениями.

Если вкратце — оконные функции помогают делать классные аналитические отчеты без участия «экселя» и pandas.

Проще всего объяснять на конкретных примерах. Будем работать с игрушечной таблицей сотрудников, вот такой:

┌────┬──────────┬────────┬────────────┬────────┐
│ id │   name   │  city  │ department │ salary │
├────┼──────────┼────────┼────────────┼────────┤
│ 11 │ Дарья    │ Самара │ hr         │ 70     │
│ 12 │ Борис    │ Самара │ hr         │ 78     │
│ 21 │ Елена    │ Самара │ it         │ 84     │
│ 22 │ Ксения   │ Москва │ it         │ 90     │
│ 23 │ Леонид   │ Самара │ it         │ 104    │
│ 24 │ Марина   │ Москва │ it         │ 104    │
│ 25 │ Иван     │ Москва │ it         │ 120    │
│ 31 │ Вероника │ Москва │ sales      │ 96     │
│ 32 │ Григорий │ Самара │ sales      │ 96     │
│ 33 │ Анна     │ Москва │ sales      │ 100    │
└────┴──────────┴────────┴────────────┴────────┘

Рассмотрим некоторые задачи, которые удобно решать с помощью «окошек» в SQL. Как именно их решать — разберемся на следующем уроке. Пока просто оценим возможности.

Если вам не терпится попрактироваться — этот урок можно пропустить перейти к следующему.

Ранжирование

Ранжирование — это всевозможные рейтинги, начиная от призеров чемпионата мира по плаванию и заканчивая Forbes 500.

Мы будем ранжировать сотрудников.

Общий рейтинг зарплат

Составим рейтинг сотрудников по размеру заработной платы:

Столбец rank показывает позицию сотрудника в рейтинге.

Видно, что у некоторых коллег одинаковая зарплата (Леонид и Марина, Вероника и Григорий) — поэтому они получили один и тот же ранг.

Рейтинг зарплат по департаментам

Тот же рейтинг, только не для всей компании, а по каждому департаменту в отдельности:

Столбец rank показывает позицию сотрудника в рейтинге конкретного департамента.

Группы по зарплате

Разобьем сотрудников на три группы в зависимости от размера зарплаты:

• высокооплачиваемые,
• средние,
• низкооплачиваемые.

Столбец tile показывает, к какой группе относится каждый сотрудник.

Самые «дорогие» коллеги

Найдем самых высокооплачиваемых людей по каждому департаменту:

Что ж, этим зарплату больше не повышать.

Сравнение со смещением

Сравнение со смещением — это когда мы смотрим, в чем разница между соседними значениями. Например, сравниваем страны, которые занимают 5 и 6 место в мировом рейтинге ВВП — сильно ли отличаются? А если сравнить 1 и 6 место?

Сюда же попадают задачи, в которых мы сравниваем значение из набора с границами набора. Например, есть 100 лучших теннисисток мира. Мария Саккари занимает в рейтинге 10 место. Как ее показатели соотносятся со спортсменкой, которая занимает первое место? А с той, кто занимает последнее?

Мы будем сравнивать сотрудников.

Разница по зарплате с предыдущим

Упорядочим сотрудников по возрастанию зарплаты и проверим, велик ли разрыв между соседями:

Столбец diff показывает, на сколько процентов зарплата сотрудника отличается от предыдущего коллеги. Видно, что больших разрывов нет. Самые крупные — между Дарьей и Борисом (11%) и Мариной и Иваном (15%).

Диапазон зарплат в департаменте

Посмотрим, как зарплата сотрудника соотносится с минимальной и максимальной зарплатой в его департаменте:

Для каждого сотрудника столбец low показывает минимальную зарплату родного департамента, а столбец high — максимальную. Видно, что разброс значений в HR и продажах невелик, а у айтишников — значительный.

Агрегация

Агрегация — это когда мы считаем суммарные или средние показатели. Например, среднюю зарплату по каждому региону или количество золотых медалей у каждой страны в зачете Олимпийских игр.

Мы будем агрегировать зарплаты сотрудников.

Сравнение с фондом оплаты труда

У каждого департамента есть фонд оплаты труда — денежная сумма, которая ежемесячно уходит на выплату зарплат сотрудникам. Посмотрим, какой процент от этого фонда составляет зарплата каждого сотрудника:

Столбец fund показывает фонд оплаты труда отдела, а perc — долю зарплаты сотрудника от этого фонда. Видно, что в HR и продажах все более-менее ровно, а у айтишников есть заметный разброс зарплат.

Сравнение со средней зарплатой

Интересно, велик ли разброс зарплат в департаментах. Проверим — посчитаем отклонение зарплаты каждого сотрудника от средней по департаменту:

Результат подтверждает предыдущие наблюдения: у айтишников зарплаты колеблются от -16% до +20% от среднего, а у остальных департаментов отклонение в пределах 5%.

4. Скользящие агрегаты

Скользящие агрегаты — это те же сумма и среднее. Только рассчитывают их не по всем элементам набора, а более хитрым способом.

Поясню на примере. Здесь возьмем другую таблицу — с доходами и расходами компании за 9 месяцев 2020 года:

┌──────┬───────┬────────┬─────────┐
│ year │ month │ income │ expense │
├──────┼───────┼────────┼─────────┤
│ 2020 │ 1     │ 94     │ 82      │
│ 2020 │ 2     │ 94     │ 75      │
│ 2020 │ 3     │ 94     │ 104     │
│ 2020 │ 4     │ 100    │ 94      │
│ 2020 │ 5     │ 100    │ 99      │
│ 2020 │ 6     │ 100    │ 105     │
│ 2020 │ 7     │ 100    │ 95      │
│ 2020 │ 8     │ 100    │ 110     │
│ 2020 │ 9     │ 104    │ 104     │
└──────┴───────┴────────┴─────────┘

Скользящее среднее по расходам

Судя по данным, доходы растут: 94 в январе → 104 в сентябре. А вот растут ли расходы? Сходу сложно сказать, месяц на месяц не приходится. Чтобы сгладить эти скачки, используют «скользящее среднее» — для каждого месяца рассчитывают средний расход с учетом предыдущего и следующего месяца. Например:

• скользящее среднее за февраль = (январь + февраль + март) / 3;
• за март = (февраль + март + апрель) / 3;
• за апрель = (март + апрель + май) / 3;
• и так далее.

Рассчитаем скользящее среднее по всем месяцам:

Теперь хорошо видно, что расходы стабильно растут.

Прибыль нарастающим итогом

Благодаря скользящему среднему, мы выяснили, что растут и доходы, и расходы. А как они соотносятся друг с другом? Хочется понять, находится ли компания «в плюсе» или «в минусе» с учетом всех заработанных и потраченных денег.

Причем важно понимать не на конец года, а на каждый месяц. Потому что если по итогам года все ОК, а в июне компания ушла в минус — это потенциальная проблема (такую ситуацию называют «кассовым разрывом»).

Поэтому посчитаем доходы и расходы по месяцам нарастающим итогом (кумулятивно):

• кумулятивный доход за январь = январь;
• за февраль = январь + февраль;
• за март = январь + февраль + март;
• за апрель = январь + февраль + март + апрель;
• и так далее.

Теперь видно, что дела у компании идут неплохо. В некоторых месяцах расходы превышают доходы, но благодаря накопленной «денежной подушке» кассового разрыва не происходит.

Резюме

Вот задачи, которые непринужденно решаются с помощью оконных функций в SQL:

• Ранжирование (всевозможные рейтинги).
• Сравнение со смещением (соседние элементы и границы).
• Агрегация (сумма и среднее).
• Скользящие агрегаты (сумма и среднее в динамике).

Конечно, это не исчерпывающий список. Но, надеюсь, теперь понятно, как пригодятся оконные функции в аналитике данных. На следующем уроке разберемся, что такое «окно», как работает оконная функция, и научимся ранжировать записи.

Записаться на курс  или купить книгу (скоро)