Зачем нужны оконные функции
Это фрагмент моего курса Оконные функции 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:
- Ранжирование (всевозможные рейтинги).
- Сравнение со смещением (соседние элементы и границы).
- Агрегация (сумма и среднее).
- Скользящие агрегаты (сумма и среднее в динамике).
Конечно, это не исчерпывающий список. Но, надеюсь, теперь понятно, как пригодятся оконные функции в аналитике данных. На следующем уроке разберемся, что такое «окно», как работает оконная функция, и научимся ранжировать записи.
Записаться на курс или купить книгу (скоро)
★ Подписывайтесь на новые заметки.