Если ты уже умеешь писать SELECT, WHERE и JOIN, но застреваешь на задачах вроде «найди топ-3 продукта в каждой категории» или «сколько процентов прироста выручки сегодня против вчера» — добро пожаловать в мир оконных функций. Это самый часто используемый инструмент SQL-аналитика после JOIN.
Покажу всё на простой таблице заказов:
CREATE TABLE orders (
id INT PRIMARY KEY,
category VARCHAR(50),
product VARCHAR(100),
amount NUMERIC(10, 2),
created_at DATE
);
Оконные функции против GROUP BY
Главное различие в одном слове: GROUP BY схлопывает строки, оконные функции — нет.
С GROUP BY ты теряешь детализацию. Был миллион заказов — после GROUP BY category осталось 5 строк, по одной на категорию. Удобно для отчёта, но нельзя одновременно видеть и сам заказ, и средний чек по его категории.
С оконной функцией каждая строка остаётся, а рядом появляется вычисленное значение:
SELECT
id,
category,
amount,
AVG(amount) OVER (PARTITION BY category) AS category_avg
FROM orders;
Прочитай вслух: «средняя amount по окну, разбитому на части по category». Для каждого заказа рядом окажется среднее по его категории — все остальные заказы остались на месте.
OVER(): три кнопки для управления
Внутри OVER() живут три настройки. Для большинства задач нужны только первые две.
1. PARTITION BY — на что нарезать
Без PARTITION BY окном считается вся таблица. С ним — отдельная группа для каждого значения колонки:
AVG(amount) OVER ()
AVG(amount) OVER (PARTITION BY category)
2. ORDER BY — в каком порядке
Нужен когда важна позиция строки внутри окна: «первый», «второй», «соседний». Без него ROW_NUMBER бессмыслен — а с ним получается нумерация:
ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC)
3. Frame clause — необязательная третья кнопка
Frame решает «какие соседи участвуют в агрегате». Для начала запомни одно: как только в OVER() появляется ORDER BY, агрегат вроде SUM начинает считать накопительно — от начала окна до текущей строки.
SUM(amount) OVER (PARTITION BY category ORDER BY created_at)
Это самая частая ловушка новичков: добавил ORDER BY чтобы было «красиво», а агрегат превратился в running total. Запомни.
ROW_NUMBER, RANK, DENSE_RANK — три способа пронумеровать
Они отличаются тем, как обрабатывают одинаковые значения в ORDER BY.
| Функция |
На значениях 10, 10, 20 даёт |
ROW_NUMBER() |
1, 2, 3 — всегда уникальные |
RANK() |
1, 1, 3 — одинаковый ранг + разрыв |
DENSE_RANK() |
1, 1, 2 — одинаковый ранг, без разрыва |
Кейс: топ-3 продукта в каждой категории
Это, пожалуй, самая частая задача оконных функций на собеседованиях. С GROUP BY решить красиво нельзя. С ROW_NUMBER — в один проход:
WITH ranked AS (
SELECT
category,
product,
SUM(amount) AS total,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY SUM(amount) DESC
) AS rn
FROM orders
GROUP BY category, product
)
SELECT category, product, total
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;
Что здесь происходит шаг за шагом:
- Сначала группируем по
(category, product) и считаем сумму по продукту. Получили список продуктов с их выручкой.
- Внутри каждой категории нумеруем продукты по убыванию суммы. У продукта-лидера
rn = 1, у второго rn = 2 и т.д.
- Во внешнем запросе оставляем только строки с
rn <= 3.
ROW_NUMBER гарантирует строго 1, 2, 3 даже если две позиции совпали по сумме. Если хочешь «всех кто в тройке», даже когда есть ничьи на втором месте — бери RANK вместо ROW_NUMBER.
LAG и LEAD — посмотреть на соседнюю строку
LAG(col) возвращает значение col из предыдущей строки в окне, LEAD(col) — из следующей. Если соседа нет, по умолчанию NULL.
Кейс: прирост выручки день ко дню
Один из топ-3 запросов, которые ждут от аналитика на интервью. Дано — дневная выручка. Нужно — процент прироста относительно предыдущего дня.
Шаг 1. Сворачиваем заказы в дневную сумму:
WITH daily AS (
SELECT
DATE_TRUNC('day', created_at)::DATE AS day,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
)
SELECT * FROM daily ORDER BY day;
Шаг 2. Добавляем колонку с выручкой предыдущего дня:
SELECT
day,
revenue,
LAG(revenue) OVER (ORDER BY day) AS prev_revenue
FROM daily
ORDER BY day;
В первой строке prev_revenue будет NULL — это нормально, у первого дня нет соседа слева.
Шаг 3. Считаем процент:
SELECT
day,
revenue,
ROUND(
100.0 * (revenue - LAG(revenue) OVER (ORDER BY day))
/ NULLIF(LAG(revenue) OVER (ORDER BY day), 0),
2
) AS dod_growth_pct
FROM daily
ORDER BY day;
Зачем NULLIF(..., 0) — чтобы не словить деление на ноль, если в предыдущий день не было ни одного заказа.
Кумулятивная сумма
Накопительная сумма — самый частый сценарий с frame'ами. С ORDER BY без явного frame'а агрегат уже работает накопительно:
SELECT
day,
revenue,
SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue
FROM daily
ORDER BY day;
Это «выручка с начала истории до текущего дня». Если хочешь month-to-date — добавь PARTITION BY по месяцу:
SELECT
day,
revenue,
SUM(revenue) OVER (
PARTITION BY DATE_TRUNC('month', day)
ORDER BY day
) AS month_to_date
FROM daily
ORDER BY day;
Каждое первое число месяца счётчик обнуляется и начинает копиться заново.
Скользящее среднее за 7 дней
Чтобы график выручки не дёргался от выходных — частый приём в дашбордах:
SELECT
day,
revenue,
AVG(revenue) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS revenue_7d_avg
FROM daily
ORDER BY day;
Frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW означает «текущая строка и шесть предыдущих». В первые 6 дней истории окно неполное — среднее посчитается по тому, что есть.
Что дальше
Window-функции открывают целый класс задач, которые без них требовали бы вложенных подзапросов или вообще были невозможны: топ-N в группе, day-over-day метрики, кумулятивные суммы, скользящие средние, lag-фичи для машинного обучения. Если пишешь SQL для аналитики — это ежедневный инструмент.
Лучший способ освоить — порешать задачи именно на эту тему. У нас на тренажёре есть отдельная категория window-функций с пошаговыми разборами.
Если ты уже умеешь писать
SELECT,WHEREиJOIN, но застреваешь на задачах вроде «найди топ-3 продукта в каждой категории» или «сколько процентов прироста выручки сегодня против вчера» — добро пожаловать в мир оконных функций. Это самый часто используемый инструмент SQL-аналитика послеJOIN.Покажу всё на простой таблице заказов:
CREATE TABLE orders ( id INT PRIMARY KEY, category VARCHAR(50), product VARCHAR(100), amount NUMERIC(10, 2), created_at DATE );Оконные функции против GROUP BY
Главное различие в одном слове:
GROUP BYсхлопывает строки, оконные функции — нет.С
GROUP BYты теряешь детализацию. Был миллион заказов — послеGROUP BY categoryосталось 5 строк, по одной на категорию. Удобно для отчёта, но нельзя одновременно видеть и сам заказ, и средний чек по его категории.С оконной функцией каждая строка остаётся, а рядом появляется вычисленное значение:
SELECT id, category, amount, AVG(amount) OVER (PARTITION BY category) AS category_avg FROM orders;Прочитай вслух: «средняя amount по окну, разбитому на части по category». Для каждого заказа рядом окажется среднее по его категории — все остальные заказы остались на месте.
OVER(): три кнопки для управления
Внутри
OVER()живут три настройки. Для большинства задач нужны только первые две.1. PARTITION BY — на что нарезать
Без
PARTITION BYокном считается вся таблица. С ним — отдельная группа для каждого значения колонки:-- Среднее по всей таблице AVG(amount) OVER () -- Среднее по каждой категории отдельно AVG(amount) OVER (PARTITION BY category)2. ORDER BY — в каком порядке
Нужен когда важна позиция строки внутри окна: «первый», «второй», «соседний». Без него
ROW_NUMBERбессмыслен — а с ним получается нумерация:-- Заказы внутри категории по убыванию суммы: 1, 2, 3, ... ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC)3. Frame clause — необязательная третья кнопка
Frame решает «какие соседи участвуют в агрегате». Для начала запомни одно: как только в
OVER()появляетсяORDER BY, агрегат вродеSUMначинает считать накопительно — от начала окна до текущей строки.-- Накопительная сумма с начала категории SUM(amount) OVER (PARTITION BY category ORDER BY created_at)Это самая частая ловушка новичков: добавил
ORDER BYчтобы было «красиво», а агрегат превратился в running total. Запомни.ROW_NUMBER, RANK, DENSE_RANK — три способа пронумеровать
Они отличаются тем, как обрабатывают одинаковые значения в
ORDER BY.ROW_NUMBER()RANK()DENSE_RANK()Кейс: топ-3 продукта в каждой категории
Это, пожалуй, самая частая задача оконных функций на собеседованиях. С
GROUP BYрешить красиво нельзя. СROW_NUMBER— в один проход:WITH ranked AS ( SELECT category, product, SUM(amount) AS total, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY SUM(amount) DESC ) AS rn FROM orders GROUP BY category, product ) SELECT category, product, total FROM ranked WHERE rn <= 3 ORDER BY category, rn;Что здесь происходит шаг за шагом:
(category, product)и считаем сумму по продукту. Получили список продуктов с их выручкой.rn = 1, у второгоrn = 2и т.д.rn <= 3.ROW_NUMBERгарантирует строго 1, 2, 3 даже если две позиции совпали по сумме. Если хочешь «всех кто в тройке», даже когда есть ничьи на втором месте — бериRANKвместоROW_NUMBER.LAG и LEAD — посмотреть на соседнюю строку
LAG(col)возвращает значениеcolиз предыдущей строки в окне,LEAD(col)— из следующей. Если соседа нет, по умолчаниюNULL.Кейс: прирост выручки день ко дню
Один из топ-3 запросов, которые ждут от аналитика на интервью. Дано — дневная выручка. Нужно — процент прироста относительно предыдущего дня.
Шаг 1. Сворачиваем заказы в дневную сумму:
WITH daily AS ( SELECT DATE_TRUNC('day', created_at)::DATE AS day, SUM(amount) AS revenue FROM orders GROUP BY 1 ) SELECT * FROM daily ORDER BY day;Шаг 2. Добавляем колонку с выручкой предыдущего дня:
SELECT day, revenue, LAG(revenue) OVER (ORDER BY day) AS prev_revenue FROM daily ORDER BY day;В первой строке
prev_revenueбудетNULL— это нормально, у первого дня нет соседа слева.Шаг 3. Считаем процент:
SELECT day, revenue, ROUND( 100.0 * (revenue - LAG(revenue) OVER (ORDER BY day)) / NULLIF(LAG(revenue) OVER (ORDER BY day), 0), 2 ) AS dod_growth_pct FROM daily ORDER BY day;Зачем
NULLIF(..., 0)— чтобы не словить деление на ноль, если в предыдущий день не было ни одного заказа.Кумулятивная сумма
Накопительная сумма — самый частый сценарий с frame'ами. С
ORDER BYбез явного frame'а агрегат уже работает накопительно:SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS cumulative_revenue FROM daily ORDER BY day;Это «выручка с начала истории до текущего дня». Если хочешь month-to-date — добавь
PARTITION BYпо месяцу:SELECT day, revenue, SUM(revenue) OVER ( PARTITION BY DATE_TRUNC('month', day) ORDER BY day ) AS month_to_date FROM daily ORDER BY day;Каждое первое число месяца счётчик обнуляется и начинает копиться заново.
Скользящее среднее за 7 дней
Чтобы график выручки не дёргался от выходных — частый приём в дашбордах:
SELECT day, revenue, AVG(revenue) OVER ( ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS revenue_7d_avg FROM daily ORDER BY day;Frame
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWозначает «текущая строка и шесть предыдущих». В первые 6 дней истории окно неполное — среднее посчитается по тому, что есть.Что дальше
Window-функции открывают целый класс задач, которые без них требовали бы вложенных подзапросов или вообще были невозможны: топ-N в группе, day-over-day метрики, кумулятивные суммы, скользящие средние, lag-фичи для машинного обучения. Если пишешь SQL для аналитики — это ежедневный инструмент.
Лучший способ освоить — порешать задачи именно на эту тему. У нас на тренажёре есть отдельная категория window-функций с пошаговыми разборами.