Это пятая, заключительная статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

Разберемся на примерах. Здесь возьмем другую табличку — expenses. Она показывает доходы и расходы одного из сотрудников (пусть это будет Марина) за 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;
• и так далее.

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

Было

Стало

Столбец roll_avg показывает скользящее среднее по расходам за три месяца (текущий, предыдущий и следующий). Теперь хорошо видно, что расходы стабильно растут.

Как перейти от «было» к «стало»?

Отсортируем таблицу по месяцам:

select
  year, month, expense,
  null as roll_avg
from expenses
order by year, month;

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

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать среднее по предыдущему, текущему и следующему значению из столбца expense:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно на каждом шаге сдвигается вниз, скользит — так и получается скользящее среднее. Чтобы описать на SQL, придется вспомнить концепцию фреймов, с которой мы познакомились в статье о смещении:

1. Окно состоит из одной или нескольких секций (в нашем случае секция одна — все записи expenses).
2. Внутри секции записи упорядочены по конкретным столбцам (order by year, month).
3. У каждой записи свой фрейм.

Фрейм на каждом шаге охватывает три записи — текущую, предыдущую и следующую:

Вот как записать это на SQL:

window w as (
  order by year, month
  rows between 1 preceding and 1 following
)

С order by все понятно, а вторая строчка — это как раз определение фрейма: «выбрать строки от 1 предыдущей до 1 следующей». На следующем шаге разберемся с фреймами подробно, а пока закончим с нашим запросом.

Считаем среднее по расходам — это функция avg():

avg(expense) over w

Добавим округление и сведем все вместе:

select
  year, month, expense,
  round(avg(expense) over w) as roll_avg
from expenses
window w as (
  order by year, month
  rows between 1 preceding and 1 following
)
order by year, month;

┌──────┬───────┬─────────┬──────────┐
│ year │ month │ expense │ roll_avg │
├──────┼───────┼─────────┼──────────┤
│ 2020 │ 1     │ 82      │ 79.0     │
│ 2020 │ 2     │ 75      │ 87.0     │
│ 2020 │ 3     │ 104     │ 91.0     │
│ 2020 │ 4     │ 94      │ 99.0     │
│ 2020 │ 5     │ 99      │ 99.0     │
│ 2020 │ 6     │ 105     │ 100.0    │
│ 2020 │ 7     │ 95      │ 103.0    │
│ 2020 │ 8     │ 110     │ 103.0    │
│ 2020 │ 9     │ 104     │ 107.0    │
└──────┴───────┴─────────┴──────────┘

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

Фрейм

В общем случае определение фрейма выглядит так:

rows between X preceding and Y following

Где X — количество строк перед текущей, а Y — количество строк после текущей:

Если указать вместо X или Y значение unbounded — это значит «граница секции»:

Если указать вместо X preceding или Y following значение current row — это значит «текущая запись»:

Фрейм никогда не выходит за границы секции, если столкнулся с ней — обрезается:

Вообще, у фреймов намного больше возможностей, но мы ограничимся этими. Подробности разберем на курсе.

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

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

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

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

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

t_income показывает доходы нарастающим итогом, t_expense — расходы, а t_profit — прибыль.

t_profit = t_income - t_expense

Как рассчитать кумулятивные показатели?

Отсортируем таблицу по месяцам:

select
  year, month, income, expense,
  null as t_income,
  null as t_expense,
  null as t_profit
from expenses
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 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     │          │           │          │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать суммарные показатели от начала таблицы до текущей строки:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно на каждом шаге охватывает строки от начала таблицы до текущей записи. Мы уже значем, как сформулировать подходящий фрейм:

window w as (
  order by year, month
  rows between unbounded preceding and current row
)

Считаем сумму по доходам и расходам — это функция sum():

sum(income) over w as t_income,
sum(expense) over w as t_expense,

Прибыль считаем как разницу между доходами и расходами:

(sum(income) over w) - (sum(expense) over w) as t_profit

Все вместе:

select
  year, month, income, expense,
  sum(income) over w as t_income,
  sum(expense) over w as t_expense,
  (sum(income) over w) - (sum(expense) over w) as t_profit
from expenses
window w as (
  order by year, month
  rows between unbounded preceding and current row
)
order by year, month;

┌──────┬───────┬────────┬─────────┬──────────┬───────────┬──────────┐
│ year │ month │ income │ expense │ t_income │ t_expense │ t_profit │
├──────┼───────┼────────┼─────────┼──────────┼───────────┼──────────┤
│ 2020 │ 1     │ 94     │ 82      │ 94       │ 82        │ 12       │
│ 2020 │ 2     │ 94     │ 75      │ 188      │ 157       │ 31       │
│ 2020 │ 3     │ 94     │ 104     │ 282      │ 261       │ 21       │
│ 2020 │ 4     │ 100    │ 94      │ 382      │ 355       │ 27       │
│ 2020 │ 5     │ 100    │ 99      │ 482      │ 454       │ 28       │
│ 2020 │ 6     │ 100    │ 105     │ 582      │ 559       │ 23       │
│ 2020 │ 7     │ 100    │ 95      │ 682      │ 654       │ 28       │
│ 2020 │ 8     │ 100    │ 110     │ 782      │ 764       │ 18       │
│ 2020 │ 9     │ 104    │ 104     │ 886      │ 868       │ 18       │
└──────┴───────┴────────┴─────────┴──────────┴───────────┴──────────┘

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

Функции агрегации

Скользящие агрегаты используют те же самые функции, что и агрегаты обычные:

• min() и max()
• count(), avg() и sum()

Разница только в наличии фрейма у скользящих агрегатов.

⌘ ⌘ ⌘

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

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

Теперь попробуйте применить «окошки» на практике!

Если хотите узнать больше об оконных функциях или потренироваться — записывайтесь на курс 🚀

Если вы давно и хорошо знаете питон, некоторые моменты в нем могут напрягать. Например, необходимость начинать каждый новый проект с создания виртуального окружения и установки пачки тулов вроде black, pylint и pytest. Или отсутствующий рефакторинг и частые ошибки из-за динамической типизации. Или убоговатая асинхронность и костыль в виде GIL.

Если у вас так — возможно, пора освоить второй язык. Логично выбрать вариант без питонячих недостатков: статически типизированный, с готовым тулингом, продуманной асинхронностью и параллелизмом.

Давайте посмотрим, кто из топа StackOverflow подходят на роль второго языка.

JavaScript. Динамический язык, который во всем хуже питона. Единственный плюс — только с ним нативно работают браузеры. Если мечтаете о фронтенде — хороший выбор, если нет — с негодованием отметаем.

Bash/Shell/PowerShell. Моя любимая тройка write-only языков! Легко написать что угодно, уже через неделю невозможно понять, что написал. Нет.

C#. Интересный вариант. Новее питона, отличная стандартная библиотека, статически типизирован, все в порядке с асинхронностью. Начинался как очень простой, но за 20 лет превратился в один из самых фичастых языков. Кросс-платформенный, несмотря на виндовые корни.

PHP. Динамический язык с тяжелым наследием, который в свежих версиях решили превратить в Java образца 2005 года. Я даже не знаю, что может быть хуже.

TypeScript. А это JavaScript, который решили превратить в современную Java. Классическая дырявая абстракция, джаваскрипт протекает из всех щелей. Статически типизирован, куча наворотов. Стандартная библиотека отсутствует. Фронтендеры на него молятся, но у питониста вряд ли вызовет что-то помимо отвращения.

C++. В рейтинге мозговыносящих языков точно занял бы первое место. Старый язык, в который запихнули все что только можно. Гарантирую, писать на C++ вам будет физически больно.

C. Самый старый, но относительно простой язык. Статические типы, очень низкоуровневый, скудная стандартная библиотека. В 1991 году был бы хорошим выбором. Зато быстрый, да.

Go. Интересный вариант. Новый, отличная стандартная библиотека, статически типизирован, классная асинхронность. Одним из основных принципов считает простоту, так что не превратился в фича-монстра, в отличие от C#. Можно делать как низкоуровневые штуки, так и бизнес-приложения.

Kotlin. Интересный вариант. Котлин — это Java, если бы ее изобрели в 2010 году. Статический, много фич. Работает поверх JVM (виртуальная машина джавы), можно использовать любые джава-либы.

И несколько нишевых языков с преданным сообществом:

Rust. Современная альтернатива C++. Отличный вариант для низкоуровневых штук, плохо подходит для остального. Много фич, тяжело освоить. Хорошая стандартная библиотека.

Clojure. Современный LISP. Язык, в котором простота возведена в абсолют — всё есть данные. Акцент на неизменяемых данных, продуманная стандартная библиотека. Работает поверх JVM.

Итого, из топ-10 мне кажутся интересными C#, Go и Kotlin. Лично я выбрал Go, потому что устал от фичастых языков и наслоений абстракций. Хочу простоты.

Если вам тоже интересно попробовать — присоединяйтесь ко мне на бесплатном курсе:

Go на практике

Одиннадцать лет назад, весной 2010 года, Adobe Flash был на пике популярности. Adobe построил вокруг этого поделия аж целую платформу, с прицелом на мобильные устройства и встраиваемое ПО.

Флеш был так успешен, что Microsoft еще в 2007 году выкатила конкурента — аналогичную хреновину под названием Silverlight. Борьба намечалась нешуточная.

Apple, владея стремительно набирающей популярность iOS, не могла остаться в стороне. И анонсировала собственную альтернативу флешу — iSlate. Аналогичный шаг сделал и Google. В следующие 10 лет развернулась кровавая битва флешеподобных плагинов, а обычный веб (HTML и JS) остались совсем не у дел.

А, нет. Вычеркните последний абзац. Ничего этого не было, и вот почему.

29 апреля 2010 года Стив Джобс опубликовал открытое письмо под названием «Thoughts on Flash», в котором разгромил флеш и поддержал HTML5. В течение двух следующих лет Adobe Flash и Microsoft Silverlight были уничтожены. Технически они продолжали существовать еще довольно долго, но и разработчики, и сами производители признали, что это тупик.

HTML5, CSS и JS получили такой мощный пинок, что до сих пор не могут остановиться (вот кому мы обязаны мириадами глючных фич веба и армией js-фреймворков). При всех недостатках, веб действительно оказался лучшим выбором, потому что не принадлежал одному монополисту.

Заботился ли Джобс о процветании Apple, когда писал свое письмо? Конечно. Он честно сказал, что благодаря отмиранию флеша Apple продаст больше устройств. Но он также думал и о разработчиках, и о пользователях. Джобс понимал, что ждет нас всех, если индустрия пойдет по пути конкурирующих плагинов вместо развития общей платформы — веба.

Джобс не был ангелом, это уж точно. Но у него было видение, и эмпатия, и смелость. Не будь этого письма в 2010 году — не было бы веба, каким мы его знаем сегодня.

В 2020 году Apple тихо удалила открытое письмо Джобса с сайта компании. Конечно, зачем эти слова про открытый веб — у нынешнего Apple совсем другие приоритеты. Да и у других техногигантов тоже. Очень жаль.

Thoughts on Flash

Это четвертая статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

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

┌────┬──────────┬────────┬────────────┬────────┐
│ 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    │
└────┴──────────┴────────┴────────────┴────────┘

• сумма по секции,
• фильтрация,
• описание окна,
• функции агрегации.

Все запросы можно повторять в песочнице.

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

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

Было

Стало

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

Как перейти от «было» к «стало»?

Отсортируем таблицу по департаментам:

select
  name, department, salary,
  null as fund,
  null as perc
from employees
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┬──────┐
│   name   │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья    │ hr         │ 70     │      │      │
│ Борис    │ hr         │ 78     │      │      │
│ Елена    │ it         │ 84     │      │      │
│ Ксения   │ it         │ 90     │      │      │
│ Леонид   │ it         │ 104    │      │      │
│ Марина   │ it         │ 104    │      │      │
│ Иван     │ it         │ 120    │      │      │
│ Вероника │ sales      │ 96     │      │      │
│ Григорий │ sales      │ 96     │      │      │
│ Анна     │ sales      │ 100    │      │      │
└──────────┴────────────┴────────┴──────┴──────┘

Для удобства я добавил сортировку по зарплате и идентификатору. Но, как вы увидите дальше, в данном случае можно обойтись и без них.

Теперь пройдем от первой строчки до последней. На каждом шаге будем считать:

• fund — сумму зарплат по департаменту в целом (она одинакова для всех сотрудников департамента);
• perc — долю зарплаты сотрудника от этой суммы.

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно состоит из секций по департаментам. При этом порядок записей в секции неважен: мы считаем сумму значений salary, а она не зависит от порядка.

window w as (
  partition by department
)

Для расчета fund подойдет обычная функция sum() — ее можно использовать поверх окна. А perc посчитаем как salary / fund:

select
  name, department, salary,
  sum(salary) over w as fund,
  round(salary * 100.0 / sum(salary) over w) as perc
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┬──────┐
│   name   │ department │ salary │ fund │ perc │
├──────────┼────────────┼────────┼──────┼──────┤
│ Дарья    │ hr         │ 70     │ 148  │ 47.0 │
│ Борис    │ hr         │ 78     │ 148  │ 53.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Елена    │ it         │ 84     │ 502  │ 17.0 │
│ Ксения   │ it         │ 90     │ 502  │ 18.0 │
│ Леонид   │ it         │ 104    │ 502  │ 21.0 │
│ Марина   │ it         │ 104    │ 502  │ 21.0 │
│ Иван     │ it         │ 120    │ 502  │ 24.0 │
├──────────┼────────────┼────────┼──────┼──────┤
│ Вероника │ sales      │ 96     │ 292  │ 33.0 │
│ Григорий │ sales      │ 96     │ 292  │ 33.0 │
│ Анна     │ sales      │ 100    │ 292  │ 34.0 │
└──────────┴────────────┴────────┴──────┴──────┘

Функция sum() работает без неожиданностей — считает сумму значений по всей секции, которой принадлежит текущая строка.

Фильтрация и порядок выполнения

Вернемся к запросу, который считал фонд оплаты труда по департаменту:

select
  name, department, salary,
  sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬──────┐
│   name   │ department │ salary │ fund │
├──────────┼────────────┼────────┼──────┤
│ Дарья    │ hr         │ 70     │ 148  │
│ Борис    │ hr         │ 78     │ 148  │
│ Елена    │ it         │ 84     │ 502  │
│ Ксения   │ it         │ 90     │ 502  │
│ Леонид   │ it         │ 104    │ 502  │
│ Марина   │ it         │ 104    │ 502  │
│ Иван     │ it         │ 120    │ 502  │
│ Вероника │ sales      │ 96     │ 292  │
│ Григорий │ sales      │ 96     │ 292  │
│ Анна     │ sales      │ 100    │ 292  │
└──────────┴────────────┴────────┴──────┘

Допустим, мы хотим оставить в отчете только самарских сотрудников. Добавим фильтр:

select
  name, salary,
  sum(salary) over w as fund
from employees
where city = 'Самара'
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 188  │
│ Леонид   │ 104    │ 188  │
│ Григорий │ 96     │ 96   │
└──────────┴────────┴──────┘

Фильтр сработал. Вот только значения fund отличаются от ожидаемых:

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 502  │
│ Леонид   │ 104    │ 502  │
│ Григорий │ 96     │ 292  │
└──────────┴────────┴──────┘

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 188  │
│ Леонид   │ 104    │ 188  │
│ Григорий │ 96     │ 96   │
└──────────┴────────┴──────┘

Все дело в порядке выполнения операций. Вот в какой последовательности действует движок, когда выполняет запрос:

1. Взять нужные таблицы (from) и соединить их при необходимости (join).
2. Отфильтровать строки (where).
3. Сгруппировать строки (group by).
4. Отфильтровать результат группировки (having).
5. Взять конкретные столбцы из результата (select).
6. Рассчитать значения оконных функций (function() over window).
7. Отсортировать то, что получилось (order by).

Таким образом, окна отрабатывают предпоследним шагом, уже после фильтрации и группировки результатов. Поэтому в нашем запросе fund отражает не сумму всех зарплат по департаменту, а сумму только по самарским сотрудникам.

Решение — использовать подзапрос с окном и фильтровать его в основном запросе:

with emp as (
  select
    name, city, salary,
    sum(salary) over w as fund
  from employees
  window w as (partition by department)
  order by department, salary, id
)
select name, salary, fund
from emp
where city = 'Самара';

┌──────────┬────────┬──────┐
│   name   │ salary │ fund │
├──────────┼────────┼──────┤
│ Дарья    │ 70     │ 148  │
│ Борис    │ 78     │ 148  │
│ Елена    │ 84     │ 502  │
│ Леонид   │ 104    │ 502  │
│ Григорий │ 96     │ 292  │
└──────────┴────────┴──────┘

Описание окна

До сих пор мы описывали окно в блоке window и ссылались на него в выражении over:

select
  name, department, salary,
  count(*) over w as emp_count,
  sum(salary) over w as fund
from employees
window w as (partition by department)
order by department, salary, id;

┌──────────┬────────────┬────────┬───────────┬──────┐
│   name   │ department │ salary │ emp_count │ fund │
├──────────┼────────────┼────────┼───────────┼──────┤
│ Дарья    │ hr         │ 70     │ 2         │ 148  │
│ Борис    │ hr         │ 78     │ 2         │ 148  │
│ Елена    │ it         │ 84     │ 5         │ 502  │
│ Ксения   │ it         │ 90     │ 5         │ 502  │
│ Леонид   │ it         │ 104    │ 5         │ 502  │
│ Марина   │ it         │ 104    │ 5         │ 502  │
│ Иван     │ it         │ 120    │ 5         │ 502  │
│ Вероника │ sales      │ 96     │ 3         │ 292  │
│ Григорий │ sales      │ 96     │ 3         │ 292  │
│ Анна     │ sales      │ 100    │ 3         │ 292  │
└──────────┴────────────┴────────┴───────────┴──────┘

Это не единственный способ. SQL разрешает вообще не использовать window и описывать окно прямо внутри over:

select
  name, department, salary,
  count(*) over (partition by department) as emp_count,
  sum(salary) over (partition by department) as fund
from employees
order by department, salary, id;

Мне больше нравится вариант с window — его легче читать, и можно явно переиспользовать окно. Но в документации часто встречается определение окна внутри over, поэтому не удивляйтесь, когда увидите его.

Кстати, определение окна может быть пустым:

select
  name, department, salary,
  count(*) over () as emp_count,
  sum(salary) over () as fund
from employees
order by department, salary, id;

Такое окно включает все строки, так что emp_count покажет общее количество сотрудников, а fund — общий фонд оплаты труда по всем записям employees.

Функции агрегации

⌘ ⌘ ⌘

Мы разобрались, как считать фиксированные агрегаты в окнах. В следующей части займемся скользящими агрегатами!

Чтобы закрепить знания на практике — записывайтесь на курс 🚀

Я вечно забываю синтаксис линуксовых утилит вроде grep, sed и find. Есть команда man, которая показывает документацию по утилите, но для меня она совершенно бесполезна — никогда не получается найти то, что нужно (возможно, это только я такой тупой).

Поэтому был бесконечно счастлив, когда на днях нашел приятную альтернативу от Игоря Чубина — «шпаргалки» с конкретными примерами. Чтобы их включить, достаточно добавить такую функцию в .bashrc:

function cht() { curl "cht.sh/$1"; }

И вызвать шпаргалку по конкретной команде:

$ cht grep
$ cht sed
$ cht tr

Есть консольный клиент и много всяких наворотов, подробности в репозитории автора.

Подписывайтесь на @nalgeon в твитере, чтобы не пропустить новые заметки 🚀

Это третья статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

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

Мы будем сравнить сотрудников из таблички employees:

┌────┬──────────┬────────┬────────────┬────────┐
│ 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    │
└────┴──────────┴────────┴────────────┴────────┘

• сравнение с предыдущим,
• диапазон,
• окно, секция и фрейм,
• функции смещения.

Все запросы можно повторять в песочнице.

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

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

Было

Стало

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

Как перейти от «было» к «стало»?

Сначала отсортируем таблицу по возрастанию зарплаты:

select
  name, department, salary,
  null as prev
from employees
order by salary, id;

┌──────────┬────────────┬────────┬──────┐
│   name   │ department │ salary │ prev │
├──────────┼────────────┼────────┼──────┤
│ Дарья    │ hr         │ 70     │      │
│ Борис    │ hr         │ 78     │      │
│ Елена    │ it         │ 84     │      │
│ Ксения   │ it         │ 90     │      │
│ Вероника │ sales      │ 96     │      │
│ Григорий │ sales      │ 96     │      │
│ Анна     │ sales      │ 100    │      │
│ Леонид   │ it         │ 104    │      │
│ Марина   │ it         │ 104    │      │
│ Иван     │ it         │ 120    │      │
└──────────┴────────────┴────────┴──────┘

Теперь пройдем от первой строчки до последней, на каждом шаге «подтягивая» зарплату предыдущего сотрудника:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Видно, что окно в данном случае охватывает текущую и предыдущую запись. Оно сдвигается вниз на каждом шаге, скользит. Это логичная трактовка происходящего, и задать скользящее окно в SQL можно. Но у таких окон более сложный синтаксис, поэтому отложим их до статьи о скользящих агрегатах.

Вместо этого возьмем более простое и знакомое нам окно — все записи, упорядоченные по возрастанию salary:

window w as (order by salary)

А чтобы на каждом шаге подтягивать зарплату предыдущего сотрудника, будем использовать оконную функцию lag():

lag(salary, 1) over w

Функция lag() возвращает значение из указанного столбца, отстоящее от текущего на указанное количество записей назад. В нашем случае — salary от предыдущей записи.

Добавим окно и оконную функцию в исходный запрос:

select
  id, name, department, salary,
  lag(salary, 1) over w as prev
from employees
window w as (order by salary)
order by salary, id;

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

Столбец prev показывает зарплату предыдущего сотрудника. Осталось посчитать разницу между prev и salary в процентах:

select
  name, department, salary,
  round(
    (salary - lag(salary, 1) over w)*100.0 / salary
  ) as diff
from employees
window w as (order by salary)
order by salary, id;

┌──────────┬────────────┬────────┬──────┐
│   name   │ department │ salary │ diff │
├──────────┼────────────┼────────┼──────┤
│ Дарья    │ hr         │ 70     │      │
│ Борис    │ hr         │ 78     │ 10.0 │
│ Елена    │ it         │ 84     │ 7.0  │
│ Ксения   │ it         │ 90     │ 7.0  │
│ Вероника │ sales      │ 96     │ 6.0  │
│ Григорий │ sales      │ 96     │ 0.0  │
│ Анна     │ sales      │ 100    │ 4.0  │
│ Леонид   │ it         │ 104    │ 4.0  │
│ Марина   │ it         │ 104    │ 0.0  │
│ Иван     │ it         │ 120    │ 13.0 │
└──────────┴────────────┴────────┴──────┘

Здесь мы заменили prev → lag(salary, 1) over w. Конструкцию вида function_name(...) over window_name движок заменяет на конкретное значение, которое вернула функция. Так что оконную функцию можно вызывать прямо внутри вычислений, и вы не раз встретите такие запросы в документации и примерах.

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

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

Было

Стало

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

Как перейти от «было» к «стало»?

Сначала отсортируем таблицу по департаментам, а внутри департамента — по возрастанию зарплаты:

select
  name, department, salary,
  null as low,
  null as high
from employees
order by department, salary, id;

┌──────────┬────────────┬────────┬─────┬──────┐
│   name   │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья    │ hr         │ 70     │     │      │
│ Борис    │ hr         │ 78     │     │      │
│ Елена    │ it         │ 84     │     │      │
│ Ксения   │ it         │ 90     │     │      │
│ Леонид   │ it         │ 104    │     │      │
│ Марина   │ it         │ 104    │     │      │
│ Иван     │ it         │ 120    │     │      │
│ Вероника │ sales      │ 96     │     │      │
│ Григорий │ sales      │ 96     │     │      │
│ Анна     │ sales      │ 100    │     │      │
└──────────┴────────────┴────────┴─────┴──────┘

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

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Одной гифкой:

Окно состоит из трех секций. Секция на каждом шаге охватывает весь департамент сотрудника. Записи при этом упорядочены по возрастанию зарплаты внутри департамента, чтобы минимальная и максимальная зарплаты всегда находились на границах секции:

window w as (
  partition by department
  order by salary
)

Хотелось бы воспользоваться функциями lag() и lead(), чтобы получить диапазон зарплат в отделе. Но они заглядывают на фиксированное количество записей назад или вперед. Нам же требуется нечто другое:

• low — зарплата первого сотрудника, входящего в секцию окна;
• high — зарплата последнего сотрудника, входящего в секцию.

К счастью, есть оконные функции ровно для этого:

first_value(salary) over w as low,
last_value(salary) over w as high

Добавим окно и оконную функцию в исходный запрос:

select
  name, department, salary,
  first_value(salary) over w as low,
  last_value(salary) over w as high
from employees
window w as (
  partition by department
  order by salary
)
order by department, salary, id;

┌──────────┬────────────┬────────┬─────┬──────┐
│   name   │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья    │ hr         │ 70     │ 70  │ 70   │
│ Борис    │ hr         │ 78     │ 70  │ 78   │
├──────────┼────────────┼────────┼─────┼──────┤
│ Елена    │ it         │ 84     │ 84  │ 84   │
│ Ксения   │ it         │ 90     │ 84  │ 90   │
│ Леонид   │ it         │ 104    │ 84  │ 104  │
│ Марина   │ it         │ 104    │ 84  │ 104  │
│ Иван     │ it         │ 120    │ 84  │ 120  │
├──────────┼────────────┼────────┼─────┼──────┤
│ Вероника │ sales      │ 96     │ 96  │ 96   │
│ Григорий │ sales      │ 96     │ 96  │ 96   │
│ Анна     │ sales      │ 100    │ 96  │ 100  │
└──────────┴────────────┴────────┴─────┴──────┘

low рассчитался корректно, а вот с high какая-то ерунда. Вместо того, чтобы равняться максимальной зарплате департамента, он меняется от сотрудника к сотруднику. Что ж, давайте разбираться.

Окно, секция, фрейм

До сих пор все было логично:

• есть окно, которое состоит из одной или нескольких секций;
• внутри секции записи упорядочены по конкретному столбцу.

На предыдущем шаге мы разделили окно на три секции — по департаментам, и упорядочили записи внутри секций по зарплате:

window w as (
  partition by department
  order by salary
)

Допустим, движок выполняет запрос, и текущая запись — Леонид из it-отдела. Мы ожидаем, что first_value() вернет первую запись it-секции (salary = 84), а last_value() — последнюю (salary = 120):

Вместо этого last_value() возвращает salary = 104:

Ожидание

Реальность

Дело в том, что функции first_value() и last_value() работают не просто с секцией окна. Они работают с фреймом внутри секции:

Фрейм находится в той же секции, где текущая запись (Леонид):

• начало фрейма = начало секции (Елена);
• конец фрейма = последняя запись со значением salary, равным текущей записи (Марина).

Секция фиксирована, фрейм же зависит от текущей записи и постоянно меняется:

Секция

Фрейм

first_value() возвращает первую строчку фрейма, а не секции. Но поскольку начало фрейма совпадает с началом секции, функция отрабатывает как мы ожидали.

last_value() возвращает последнюю строчку фрейма, а не секции. Именно поэтому в нашем запросе она вернула не максимальную зарплату для каждого отдела, а какую-то ерунду.

Чтобы last_value() работала как мы ожидаем, придется «прибить» границы фрейма к границам секции. Тогда для каждой секции фрейм будет в точности совпадать с ней:

Зачем так сложно-то? 🤦 Если возникла такая реакция — прекрасно вас понимаю. От фреймов есть польза, но зачем авторы стандарта SQL сделали такое неочевидное поведение по умолчанию — я не знаю. Остается только понять и простить.

Подытожим принцип, по которому работают first_value() и last_value():

1. Есть окно, которое состоит из одной или нескольких секций (partition by department).
2. Внутри секции записи упорядочены по конкретному столбцу (order by salary).
3. У каждой записи в секции свой фрейм. По умолчанию начало фрейма совпадает с началом секции, а конец для каждой записи свой.
4. Конец фрейма можно приклеить к концу секции, чтобы фрейм в точности совпадал с секцией.
5. Функция first_value() возвращает значение из первой строки фрейма.
6. Функция last_value() возвращает значение из последней строки фрейма.

Теперь разберемся, как прибить фрейм к окну — и закончим с запросом по диапазону зарплат в департаментах.

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

Возьмем наше окно:

window w as (
  partition by department
  order by salary
)

И настроим его, чтобы фрейм в точности совпадал с секцией (департаментом):

window w as (
  partition by department
  order by salary
  rows between unbounded preceding and unbounded following
)

Не будем сейчас разбирать конструкцию rows between — ее время придет в статье про скользящие агрегаты. Важно, что благодаря ей фрейм совпадает с секцией, а значит last_value() вернет максимальную зарплату по департаменту:

select
  name, department, salary,
  first_value(salary) over w as low,
  last_value(salary) over w as high
from employees
window w as (
  partition by department
  order by salary
  rows between unbounded preceding and unbounded following
)
order by department, salary, id;

┌──────────┬────────────┬────────┬─────┬──────┐
│   name   │ department │ salary │ low │ high │
├──────────┼────────────┼────────┼─────┼──────┤
│ Дарья    │ hr         │ 70     │ 70  │ 78   │
│ Борис    │ hr         │ 78     │ 70  │ 78   │
├──────────┼────────────┼────────┼─────┼──────┤
│ Елена    │ it         │ 84     │ 84  │ 120  │
│ Ксения   │ it         │ 90     │ 84  │ 120  │
│ Леонид   │ it         │ 104    │ 84  │ 120  │
│ Марина   │ it         │ 104    │ 84  │ 120  │
│ Иван     │ it         │ 120    │ 84  │ 120  │
├──────────┼────────────┼────────┼─────┼──────┤
│ Вероника │ sales      │ 96     │ 96  │ 100  │
│ Григорий │ sales      │ 96     │ 96  │ 100  │
│ Анна     │ sales      │ 100    │ 96  │ 100  │
└──────────┴────────────┴────────┴─────┴──────┘

Теперь движок заполняет low и high так же, как мы делали это вручную.

Функции смещения

lag() и lead() действуют относительно текущей строки, заглядывая вперед или назад на указанное количество строк.

first_value(), last_value() и nth_value() действуют относительно границ фрейма, выбирая указанную строку в пределах фрейма.

Чтобы границы фрейма совпадали с границами секции (или всего окна, если секция одна) — используют конструкцию rows between unbounded preceding and unbounded following в определении окна.

⌘ ⌘ ⌘

Мы разобрались, как сравнивать строки с соседями и границами окна. В следующей части займемся агрегацией данных!

Чтобы закрепить знания на практике — записывайтесь на курс 🚀

На днях я наткнулся на идеальное руководство по инфографике и визуализации данных:

• основано на работах Эдварда Тафти и Стивена Фью;
• подробное, но не слишком многословное (150 страниц);
• практичное и с кучей примеров (197 иллюстраций).

Книга рассказывает, как строить понятные отчеты и дашборды, на основе которых люди смогут принимать решения:

1. Как сформулировать и донести сообщение.
2. Как выбрать подходящую диаграмму.
3. Как должен выглядеть каждый элемент диаграммы.
4. Как избежать визуального мусора и повысить информационную плотность.
5. Как не ввести никого в заблуждение и облегчить восприятие информации.

Хотя само руководство отличное, его авторы — IBCS Association — приняли несколько странных решений:

1. Назвали книгу максимально непонятно и далеко от настоящей темы — «International Business Communication Standards».
2. Разместили на сайте в таком виде, что нормально читать невозможно.

К счастью, IBCS опубликовали руководство под открытой лицензий Creative Commons. Так что с помощью пригоршни питона и такой-то матери я подготовил нормальную веб-версию, EPUB and PDF.

Авторы вложили в руководство много труда, а результат мне очень понравился. Так что предлагаю вам тоже оценить!

Помню, лет десять назад американские СМИ захватила идея, что «каждый должен научиться программировать». Повсеместно открывались буткампы из серии «от нуля до сеньор-разработчика за 10 дней», и даже президент США делал вид, что учится писать на джаваскрипте.

Я не уверен, что программирование нужно прямо так уж всем. И точно не стоит всем бросаться учить джаваскрипт: для автоматизации повседневной работы есть более подходящие языки.

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

Если вы согласны, и чувствуете, что навыки работы с данными стоило бы подтянуть — дальше я расскажу про три курса и один инструмент. Инструментом я пользуюсь каждый день, а курсы проходил лично (один даже сделал сам). Авторы не платили за рекомендации. Хотя, естественно, если вы выберете мой курс — я на этом заработаю.

SQL — это «родной» язык работы с данными. В принципе, можно прожить и без него: в базовом варианте достаточно понимать основы статистики и знать Excel на более глубоком уровне, чем «рисую таблички в рамочке». В этом поможет курс Алексея Куличевского «Данные для бизнеса». Хотя небольшой порции SQL и там не избежать.

Если интересна продуктовая аналитика — курс Анатолия Макаревича SQL Habit. Типа GoPractice, только бодрее и на голом SQL, без Amplitude. Проводит от самых основ до серьезной работы.

Если уже знаете основы SQL и хотите научиться применять его в повседневных задачах — мой курс «SQLite для аналитики». Научитесь загружать и выгружать данные, «чистить» проблемы, находить связи и анализировать показатели, применять аналитические функции и работать с JSON.

А инструмент — Redash. Это веб-интерфейс к любым базам данных, в нем строят отчеты и собирают дашборды. Простая и дешевая замена замороченным BI-инструментам. Из этой же серии есть Metabase и Superset — их многие хвалят, но я не пробовал.

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

Это вторая статья из серии Оконные функции в картинках. Рекомендую не просто читать, а проходить курс — с ним знания превратятся в навыки.

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

Будем работать с игрушечной таблицей employees, вот такой:

┌────┬──────────┬────────┬────────────┬────────┐
│ 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    │
└────┴──────────┴────────┴────────────┴────────┘

• рейтинг зарплат,
• рейтинг зарплат по департаментам,
• группы по зарплате,
• функции ранжирования.

Все запросы можно повторять в песочнице.

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

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

Было

Стало

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

Как перейти от «было» к «стало»?

Сначала отсортируем таблицу по убыванию зарплаты:

select
  null as rank,
  name, department, salary
from employees
order by salary desc, id;

┌──────┬──────────┬────────────┬────────┐
│ rank │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│      │ Иван     │ it         │ 120    │
│      │ Леонид   │ it         │ 104    │
│      │ Марина   │ it         │ 104    │
│      │ Анна     │ sales      │ 100    │
│      │ Вероника │ sales      │ 96     │
│      │ Григорий │ sales      │ 96     │
│      │ Ксения   │ it         │ 90     │
│      │ Елена    │ it         │ 84     │
│      │ Борис    │ hr         │ 78     │
│      │ Дарья    │ hr         │ 70     │
└──────┴──────────┴────────────┴────────┘

Теперь пройдем от первой строчки до последней и проставим ранг каждой записи. Начнем с 1 и будем увеличивать ранг каждый раз, когда значение salary меньше, чем у предыдущей записи:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца salary, выделенные синей рамкой. Назовем эти значения окном.

Попробуем описать содержимое окна словами:

• Это значения столбца salary.
• Они упорядочены от большего значения к меньшему.

Сформулируем то же самое на SQL:

window w as (order by salary desc)

• window — ключевое слово, которое показывает, что дальше будет определение окна;
• w — название окна (может быть любым);
• (order by salary desc) — описание окна («значения столбца salary, упорядоченные по убыванию»).

Задача — посчитать ранг по окну w. На SQL это записывается как dense_rank() over w.

dense_rank() — это оконная функция, которая считает ранг по указанному окну. Логика dense_rank() такая же, как была у нас при ручном подсчете — начать с 1 и увеличивать ранг каждый раз, когда очередное значение из окна отличается от предыдущего.

Добавим окно и оконную функцию в исходный запрос:

select
  dense_rank() over w as rank,
  name, department, salary
from employees
window w as (order by salary desc)
order by rank, id;

┌──────┬──────────┬────────────┬────────┐
│ rank │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1    │ Иван     │ it         │ 120    │
│ 2    │ Леонид   │ it         │ 104    │
│ 2    │ Марина   │ it         │ 104    │
│ 3    │ Анна     │ sales      │ 100    │
│ 4    │ Вероника │ sales      │ 96     │
│ 4    │ Григорий │ sales      │ 96     │
│ 5    │ Ксения   │ it         │ 90     │
│ 6    │ Елена    │ it         │ 84     │
│ 7    │ Борис    │ hr         │ 78     │
│ 8    │ Дарья    │ hr         │ 70     │
└──────┴──────────┴────────────┴────────┘

Вот как движок выполняет такой запрос:

1. Берет таблицу, указанную в from.
2. Выбирает из нее все записи.
3. Для каждой записи рассчитывает значение dense_rank() с помощью окна w.
4. Сортирует результат как указано в order by.

Вот как отрабатывает шаг 3, на котором назначается ранг:

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

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

Было

Стало

Как перейти от «было» к «стало»?

Сначала отсортируем таблицу по департаментам, а внутри департамента — по убыванию зарплаты:

select
  null as rank,
  name, department, salary
from employees
order by department, salary desc, id;

┌──────┬──────────┬────────────┬────────┐
│ rank │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│      │ Борис    │ hr         │ 78     │
│      │ Дарья    │ hr         │ 70     │
│      │ Иван     │ it         │ 120    │
│      │ Леонид   │ it         │ 104    │
│      │ Марина   │ it         │ 104    │
│      │ Ксения   │ it         │ 90     │
│      │ Елена    │ it         │ 84     │
│      │ Анна     │ sales      │ 100    │
│      │ Вероника │ sales      │ 96     │
│      │ Григорий │ sales      │ 96     │
└──────┴──────────┴────────────┴────────┘

Теперь пройдем от первой строчки до последней и проставим ранг каждой записи. Начнем с 1 и будем увеличивать ранг каждый раз, когда значение salary меньше, чем у предыдущей записи. При переходе от департамента к департаменту будем сбрасывать ранг обратно на 1:

1️⃣

2️⃣

3️⃣

4️⃣

5️⃣

и так далее...

Чтобы проставить ранг, достаточно на каждом шаге смотреть только на значения из столбца salary, выделенные синей рамкой. Это и есть окно в данном случае.

Видно, что окно меняется в зависимости от того, к какому департаменту относится текущая запись. Опишем словами:

• Окно разбито на несколько независимых секций — по одной на департамент.
• Внутри секции записи упорядочены по убыванию зарплаты.

Сформулируем то же самое на SQL:

window w as (
  partition by department
  order by salary desc
)

• partition by department указывает, как следует разбить окно на секции;
• order by salary desc задает сортировку внутри секции.

Функция расчета ранга остается прежней — dense_rank().

Добавим окно и оконную функцию в исходный запрос:

select
  dense_rank() over w as rank,
  name, department, salary
from employees
window w as (
  partition by department
  order by salary desc
)
order by department, rank, id;

┌──────┬──────────┬────────────┬────────┐
│ rank │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1    │ Борис    │ hr         │ 78     │
│ 2    │ Дарья    │ hr         │ 70     │
├──────┼──────────┼────────────┼────────┤
│ 1    │ Иван     │ it         │ 120    │
│ 2    │ Леонид   │ it         │ 104    │
│ 2    │ Марина   │ it         │ 104    │
│ 3    │ Ксения   │ it         │ 90     │
│ 4    │ Елена    │ it         │ 84     │
├──────┼──────────┼────────────┼────────┤
│ 1    │ Анна     │ sales      │ 100    │
│ 2    │ Вероника │ sales      │ 96     │
│ 2    │ Григорий │ sales      │ 96     │
└──────┴──────────┴────────────┴────────┘

Вот как движок рассчитывает ранг для каждой записи:

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

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

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

Было

Стало

Как перейти от «было» к «стало»?

Сначала отсортируем таблицу по убыванию зарплаты:

select
  null as tile,
  name, department, salary
from employees
order by salary desc, id;

┌──────┬──────────┬────────────┬────────┐
│ tile │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│      │ Иван     │ it         │ 120    │
│      │ Леонид   │ it         │ 104    │
│      │ Марина   │ it         │ 104    │
│      │ Анна     │ sales      │ 100    │
│      │ Вероника │ sales      │ 96     │
│      │ Григорий │ sales      │ 96     │
│      │ Ксения   │ it         │ 90     │
│      │ Елена    │ it         │ 84     │
│      │ Борис    │ hr         │ 78     │
│      │ Дарья    │ hr         │ 70     │
└──────┴──────────┴────────────┴────────┘

Всего 10 записей, 3 группы — значит, две группы по 3 записи и одна 4 записи. Например, так:

┌──────┬──────────┬────────────┬────────┐
│ tile │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│      │ Иван     │ it         │ 120    │
│      │ Леонид   │ it         │ 104    │
│      │ Марина   │ it         │ 104    │
│      │ Анна     │ sales      │ 100    │
├──────┼──────────┼────────────┼────────┤
│      │ Вероника │ sales      │ 96     │
│      │ Григорий │ sales      │ 96     │
│      │ Ксения   │ it         │ 90     │
├──────┼──────────┼────────────┼────────┤
│      │ Елена    │ it         │ 84     │
│      │ Борис    │ hr         │ 78     │
│      │ Дарья    │ hr         │ 70     │
└──────┴──────────┴────────────┴────────┘

Чтобы провести границы между группами, придется анализировать все зарплаты, отсортированные по убыванию. Поэтому окно будет таким:

window w as (order by salary desc)

А вот функция потребуется другая — ntile(n), где n — количество групп. В нашем случае:

select
  ntile(3) over w as tile,
  name, department, salary
from employees
window w as (order by salary desc)
order by salary desc, id;

┌──────┬──────────┬────────────┬────────┐
│ tile │   name   │ department │ salary │
├──────┼──────────┼────────────┼────────┤
│ 1    │ Иван     │ it         │ 120    │
│ 1    │ Леонид   │ it         │ 104    │
│ 1    │ Марина   │ it         │ 104    │
│ 1    │ Анна     │ sales      │ 100    │
├──────┼──────────┼────────────┼────────┤
│ 2    │ Вероника │ sales      │ 96     │
│ 2    │ Григорий │ sales      │ 96     │
│ 2    │ Ксения   │ it         │ 90     │
├──────┼──────────┼────────────┼────────┤
│ 3    │ Елена    │ it         │ 84     │
│ 3    │ Борис    │ hr         │ 78     │
│ 3    │ Дарья    │ hr         │ 70     │
└──────┴──────────┴────────────┴────────┘

ntile(n) разбивает все записи на n групп и возвращает номер группы для каждой записи. Если общее количество записей (10 в нашем случае) не делится на размер группы (3), то первые группы будут крупнее последних.

Функции ранжирования

dense_rank() и ntile() мы уже разобрали.

row_number() нумерует строки в порядке, указанном в order by. Никаких неожиданностей.

rank() похож на dense_rank(), a разницу проще всего показать на примере.

select
  ••• over w as rank,
  name, salary
from employees
window w as (order by salary desc)
order by rank, id;

В одном случае вместо ••• укажем dense_rank(), а в другом — rank():

┌──────┬──────────┬────────┐
│ rank │   name   │ salary │
├──────┼──────────┼────────┤
│ 1    │ Иван     │ 120    │
│ 2    │ Леонид   │ 104    │
│ 2    │ Марина   │ 104    │
│ 3    │ Анна     │ 100    │
│ 4    │ Вероника │ 96     │
│ 4    │ Григорий │ 96     │
│ 5    │ Ксения   │ 90     │
│ 6    │ Елена    │ 84     │
│ 7    │ Борис    │ 78     │
│ 8    │ Дарья    │ 70     │
└──────┴──────────┴────────┘

┌──────┬──────────┬────────┐
│ rank │   name   │ salary │
├──────┼──────────┼────────┤
│ 1    │ Иван     │ 120    │
│ 2    │ Леонид   │ 104    │
│ 2    │ Марина   │ 104    │
│ 4    │ Анна     │ 100    │
│ 5    │ Вероника │ 96     │
│ 5    │ Григорий │ 96     │
│ 7    │ Ксения   │ 90     │
│ 8    │ Елена    │ 84     │
│ 9    │ Борис    │ 78     │
│ 10   │ Дарья    │ 70     │
└──────┴──────────┴────────┘

dense_rank() назначает Анне третье место, а rank() — четвертое, потому что второе-третье уже заняты Леонидом и Мариной. Аналогично с Ксенией после Вероники и Григория. Вот и вся разница.

⌘ ⌘ ⌘

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

Чтобы закрепить знания на практике — записывайтесь на курс 🚀

О замечательной библиотеке для всех, кто не фанатеет от html-верстки.

awsm.css на гитхабе

Описание и примеры

Поддержать подкаст + бонусы

Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции».

Если вкратце — оконные функции помогают делать классные аналитические отчеты без участия «экселя». Хотите посчитать процент продаж по месяцам от общих продаж за год? Оконные функции. Разделить маркетинговые каналы на эффективные и неэффективные? Оконные функции. Выбрать топ-10 клиентов по каждому сегменту? Тоже они.

Я прочитал несколько десятков статей «для начинающих», которые объясняли, что такое оконные функции. Все они страдали от одной из двух проблем:

1. Все понятно, но описан 1% возможностей «окошек».
2. Написано так сложно, что если бы я уже не знал предмет обсуждения — ничего бы не понял.

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

План такой:

1. Зачем нужны оконные функции (эта статья).
2. Ранжирование
3. Смещение
4. Агрегация
5. Скользящие агрегаты

Я рекомендую не просто читать статьи, а сразу проходить курс. В нем много практических задачек, а только с ними абстрактные знания превратятся в навыки.

⌘ ⌘ ⌘

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

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

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

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

На примере нашей таблички сотрудников:

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

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

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

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

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

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

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

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

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

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

Что ж, этим зарплату больше не повышать! (или наоборот, повысить? 🤔)

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

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

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

На примере нашей таблички сотрудников:

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

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

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

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

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

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

3. Агрегация

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

На примере нашей таблички сотрудников:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Резюме

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

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

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

Чтобы закрепить знания на практике — записывайтесь на курс 🚀

Как лучше всего возвращать информацию об ошибках в API.

How do you know what's gone wrong when your API request fails?

RFC 7807. Problem Details for HTTP APIs

Библиотеки для разных языков

HTTP Status Codes

Поддержать подкаст + бонусы