Всем привет! Я — практикующий исследователь данных, и на этом канале делюсь тем, что реально работает в IT. Никакой сухой теории, только личный опыт, рабочие инструменты и грабли, на которые я уже наступил за вас. Рад, что вы здесь!
Честно, когда я впервые столкнулся с оконными функциями, мне казалось, что это какая-то непостижимая штука, мозг прям сопротивлялся их использовать, но это длилась до тех пор, пока мне просто стало необходимо их брать на вооружение. Но на деле оказалось всё куда проще. Если подойти к ним как к инструменту «посмотреть на свои данные через маленькое окошко», то они становятся суперполезными для аналитики.
Оконные функции — это как сидеть на балконе и смотреть на двор. Ты видишь весь двор, но можешь фокусироваться на каждом доме по отдельности и одновременно считать, сколько людей живет в каждом доме, кто пришёл первым, кто последним и так далее (так себе сравнение наверно, но мне прям нравится😄).
Почему тяжело обходиться без оконных функций
Обычные агрегатные функции вроде SUM() или AVG() в SQL дают одно число для всей таблицы или группы. А оконные функции позволяют считать эти агрегаты для каждой строки, учитывая соседей и порядок.
Пример простой аналогии:
- У тебя есть список всех заказов в магазине.
- Ты хочешь знать не только общий доход, но и доход каждого клиента относительно всех заказов, а также кто сделал первый заказ, кто второй и так далее.
Оконные функции позволяют сделать это без мучительных джойнов и подзапросов.
Основные концепции и простые примеры
1. PARTITION BY: делим данные на группы
Представим, что у нас есть таблица продаж по магазинам:
Если мы хотим посчитать суммарный доход по каждому магазину, но при этом оставить строки, мы можем использовать PARTITION BY:
SELECT
store,
revenue,
SUM(revenue) OVER (PARTITION BY store) AS total_store_revenue
FROM sales;
Результат:
Напоминалка: не путай PARTITION BY с GROUP BY. Первый сохраняет все строки, второй — сводит в одну.
2. ORDER BY и ROW_NUMBER(): нумерация внутри группы
Допустим, ты хочешь пронумеровать заказы внутри каждого магазина по сумме дохода:
SELECT
store,
revenue,
ROW_NUMBER() OVER (PARTITION BY store ORDER BY revenue DESC) AS row_num
FROM sales;
Теперь легко увидеть «топовый заказ» каждого магазина.
Mini-лайфхак: есть RANK() и DENSE_RANK(). Разница — как они нумеруют одинаковые значения. RANK() оставляет «дыры», DENSE_RANK() — нет.
3. SUM, AVG и другие агрегаты как оконные функции
Оконные функции позволяют считать скользящие суммы, средние и прочие агрегаты для каждой строки.
Пример: средний доход по магазину для каждой строки:
SELECT
store,
revenue,
AVG(revenue) OVER (PARTITION BY store) AS avg_revenue
FROM sales;
Результат:
Применение: удобно для расчета средней корзины, средней выручки по клиенту, среднего времени на сайте.
4. LAG и LEAD: заглянем в прошлое и будущее
LAG() позволяет посмотреть на значение предыдущей строки, а LEAD() — на следующую.
Пример: посмотреть разницу между заказами внутри магазина:
SELECT
store,
revenue,
LAG(revenue) OVER (PARTITION BY store ORDER BY revenue) AS prev_revenue,
revenue - LAG(revenue) OVER (PARTITION BY store ORDER BY revenue) AS diff
FROM sales;
Пояснение: NULL в первой строке нормально — это начало отсчета. Можно заменить через COALESCE() если нужно 0.
Реальные кейсы использования в аналитике
- Топовые клиенты по каждому региону — ROW_NUMBER() + PARTITION BY.
- Скользящее среднее продаж за неделю — AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
- Разница между текущим и предыдущим заказом — LAG().
- Ранжирование товаров по выручке внутри категории — RANK().
Если работать с большими данными, это реально экономит десятки подзапросов и джойнов.
Типичные грабли и как их избежать
- Не забывай ORDER BY внутри оконной функции — без него результаты могут быть хаотичными.
- Не путай агрегаты с обычным GROUP BY — оконные функции оставляют строки.
- Осторожно с производительностью на больших таблицах — индексы помогают, но всё равно могут быть «тяжелые» запросы.
- LAG и LEAD дают NULL для краевых значений — всегда проверяй и обрабатывай.
Мини-лайфхаки для аналитиков
- Используй PARTITION BY для любой группировки, не создавая лишних джойнов.
- ROWS BETWEEN позволяет делать «скользящие окна» — супер для временных рядов.
- Комбинируй ROW_NUMBER() с PARTITION BY для топ-N аналитики.
- Перед сложными запросами тестируй на маленькой выборке — быстрее дебажить.
Итоги
Оконные функции в SQL — как увеличительное стекло для твоих данных🔍. Они позволяют:
- видеть контекст каждой строки внутри группы;
- делать скользящие суммы, средние и ранжирования;
- заглядывать в прошлое и будущее;
- строить сложные аналитические отчеты без десятков джойнов.
Освоив их, ты сможешь работать с данными быстрее, чище и эффективнее. Это действительно удобно.
Я не претендую на последнюю инстанцию, я пишу о своём пути и опыте. Спасибо что дочитали до конца. Подпишитесь👇👇👇, ставьте лайки 👍🏽👍🏽👍🏽 впереди много интересных статей про навыки, инструменты, обучение, лайфхаки и пути аналитика.
