Как сравнить покупку со средним чеком, увидеть рост продаж и составить рейтинг? Объясняем оконные функции SQL на понятных примерах, без программирования.
Зачем смотреть на соседние строки
Представьте небольшой книжный магазин. В таблице продаж есть дата, покупатель и сумма каждой покупки. Хозяин видит, что сегодня кто-то потратил 2 000 рублей. Но это много или мало? Чтобы ответить, одной строки недостаточно: нужно сравнение с другими покупками.
Можно отдельно посчитать средний чек и держать его в голове. А можно показать рядом с каждой покупкой показатель, с которым её удобно сравнить. Для подобных задач в SQL существуют оконные функции. SQL — язык, с помощью которого обращаются к данным в базе. Чтобы понять идею окна, писать запросы пока не нужно.
Что здесь называют окном
Окно — это набор строк, которые участвуют в расчёте для выбранной строки. Например, покупки того же клиента или продажи за определённый период. При этом отдельные записи остаются видны: рядом с ними появляются дополнительные результаты.
Обычный итог по клиенту может оставить одну строку с общей суммой его покупок. Оконный расчёт позволяет сохранить каждую покупку и показать рядом общую сумму этого клиента. Это различие описано во введении PostgreSQL.
Одна покупка на фоне остальных
Возьмём условный пример. Анна купила книги на 1 000, 2 000 и 3 000 рублей. Её средний чек — 2 000 рублей. Если поставить этот ориентир рядом с каждой покупкой, сразу видно, какая была ниже среднего, какая совпала с ним, а какая оказалась выше.
Теперь добавим Бориса с другой историей покупок. Сравнивать Анну со средним чеком Бориса обычно незачем. Поэтому сначала нужно решить, чьи покупки относятся к одной группе. В нашем примере это покупки одного человека. В другой задаче группой станет магазин, город или категория книг.
Рейтинг, движение во времени и накопленный итог
Оконные функции помогают решать несколько разных задач: нумеровать записи, определять места в рейтинге, обращаться к предыдущему значению. Доступные варианты перечислены в справочнике PostgreSQL. Подходящий расчёт выбирают под вопрос, а не под красивое название функции.
- Рейтинг. Какие книги принесли магазину больше всего выручки в своей категории?
- Сравнение по времени. Как продажи вторника отличаются от продаж понедельника?
- Накопленный итог. Сколько магазин заработал с начала месяца к каждому дню?
Последний пример легко представить как копилку. В понедельник в ней 5 000 рублей, во вторник добавились 3 000. Итог за вторник — 3 000, а накопленный к этому дню — 8 000. Это два полезных ответа на разные вопросы.
Почему одинаковые числа могут дать разные места
Допустим, две книги принесли одинаковую выручку. В рейтинге можно присвоить им одно место. Но если задача — выбрать ровно три книги для витрины, придётся договориться, как поступать при равенстве: например, учитывать ещё число проданных экземпляров.
Сам по себе расчёт не знает правил магазина. До работы с таблицей полезно записать: что сравниваем, внутри какой группы, в каком порядке и что делать с равными результатами. Особенно это важно для сравнения с предыдущей записью: предыдущая по дате и предыдущая по сумме покупки — разные вещи.
Когда это полезно человеку, который только знакомится с данными
Оконные функции позволяют ставить более точные вопросы к привычным отчётам. Вместо «сколько продали» появляется «как изменились продажи», вместо «какой чек» — «как он выглядит на фоне остальных».
Начать можно с обычной таблицы и устного описания нужного результата. Выберите понятный сюжет: расходы семьи, прочитанные книги или посещения спортзала. Решите, что хотите сравнить. Когда смысл расчёта ясен, техническую запись изучать легче. А если нужен только один общий итог, отдельный оконный расчёт может вообще не понадобиться.
Куда двигаться дальше
Если вам интересно самостоятельно разбираться в отчётах и задавать вопросы к таблицам, посмотрите программу курса «SQL инженер». Сопоставьте темы и требования с тем, что уже умеете: понимание задачи пригодится раньше, чем сложные конструкции языка.
