Оконные функции SQL: синтаксис и примеры для аналитика

Оконная функция SQL — это функция, которая считает значение по набору связанных строк (окну) и записывает результат в каждую строку, сохраняя их все в выдаче. Окно задаёт конструкция OVER: PARTITION BY делит строки на группы, ORDER BY задаёт порядок, ROWS или RANGE ограничивают рамку. Так одним запросом считают нарастающий итог, ранги, долю в группе и рост к прошлому месяцу.

Как работает оконная функция в SQL?

Оконная функция считает значение по строкам окна и возвращает его рядом с исходными колонками. Число строк в результате не меняется.

Общий синтаксис:

функция(аргументы) OVER (
    PARTITION BY колонка_группы
    ORDER BY колонка_порядка
    ROWS BETWEEN начало AND конец
)

Порядок выполнения запроса: FROM, WHERE, GROUP BY, HAVING, потом оконные функции, потом ORDER BY и LIMIT. Если одно окно повторяется, его объявляют один раз через WINDOW w AS (...) и пишут OVER w.

На какой таблице решаем задачи?

Все примеры ниже идут на учебной таблице sales: 10 продаж за январь–апрель 2026 года, четыре клиента, четыре менеджера, два региона.

SELECT * FROM sales ORDER BY id

Каждый запрос и результат мы прогнали в SQLite 3.51 и DuckDB 1.4: выдача совпала. Запросы с DATE_TRUNC выполнятся и в PostgreSQL; для SQLite мы заменили его на strftime('%Y-%m-01', sale_date).

Чем оконная функция отличается от GROUP BY?

GROUP BY возвращает одну строку на группу: Москва 445 000, СПб 415 000. Оконная функция добавляет тот же итог к каждой исходной строке.

SELECT id, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY region, id

Долю строки в группе через GROUP BY считают в два шага: агрегат в подзапросе и JOIN к таблице. Окно делает это в одном SELECT. Такие расчёты встречаются в задачах по юнит-экономике и при разборе финансовых KPI.

Какие бывают оконные функции SQL?

Функции делят на три группы: ранжирующие, агрегатные в окне и функции смещения.

Разница ранжирующих функций видна на продажах 4 и 6: у обеих сумма 60 000.

SELECT id, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM sales
WHERE amount <= 95000
ORDER BY amount DESC, id

ROW_NUMBER дал продажам 4 и 6 номера 4 и 5. RANK дал обеим 4 и перешёл к 6. DENSE_RANK дал обеим 4 и перешёл к 5. Колонку id в ORDER BY добавили, чтобы порядок равных сумм был одинаковым при каждом запуске.

Задачи по SQL с оконными функциями: примеры с решениями

Под каждым запросом — его результат на таблице sales.

Задача 1. Нарастающий итог выручки по месяцам

Сначала CTE сворачивает продажи до месяцев, потом SUM с ORDER BY копит сумму.

WITH m AS (
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(amount) AS revenue
    FROM sales
    GROUP BY 1
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM m
ORDER BY month

Задача 2. Доля продажи в выручке клиента

Окно по клиенту без ORDER BY отдаёт итог клиента в каждую строку. Множитель 100.0 переводит деление в дробное.

SELECT client, sale_date, amount,
       SUM(amount) OVER (PARTITION BY client) AS client_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY client), 1) AS share_pct
FROM sales
ORDER BY client, sale_date

Задача 3. Ранг менеджеров по продажам в регионе

GROUP BY считает сумму менеджера, RANK раздаёт места внутри региона.

WITH t AS (
    SELECT region, manager, SUM(amount) AS total
    FROM sales
    GROUP BY region, manager
)
SELECT region, manager, total,
       RANK() OVER (PARTITION BY region ORDER BY total DESC) AS place
FROM t
ORDER BY region, place

Задача 4. Рост выручки к предыдущему месяцу через LAG

LAG(revenue) берёт выручку прошлого месяца. У января прошлого месяца нет, поэтому там NULL.

WITH m AS (
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(amount) AS revenue
    FROM sales
    GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM m
ORDER BY month

Нулевой делитель защищают через NULLIF(..., 0).

Задача 5. Скользящее среднее за 3 месяца

Рамка ROWS BETWEEN 2 PRECEDING AND CURRENT ROW берёт текущий месяц и два предыдущих.

WITH m AS (
    SELECT DATE_TRUNC('month', sale_date) AS month,
           SUM(amount) AS revenue
    FROM sales
    GROUP BY 1
)
SELECT month, revenue,
       CAST(ROUND(AVG(revenue) OVER w, 0) AS INTEGER) AS ma_3m,
       COUNT(*) OVER w AS months_in_window
FROM m
WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY month

В январе и феврале в окне один и два месяца. Колонка months_in_window показывает это, неполные окна отбрасывают условием months_in_window = 3 во внешнем запросе.

Задача 6. Первая покупка клиента

ROW_NUMBER нумерует покупки клиента по дате, внешний запрос оставляет номер 1.

WITH t AS (
    SELECT client, sale_date, manager, amount,
           ROW_NUMBER() OVER (PARTITION BY client ORDER BY sale_date) AS rn
    FROM sales
)
SELECT client, sale_date, manager, amount
FROM t
WHERE rn = 1
ORDER BY client

Если дата первой покупки нужна рядом с каждой продажей, используйте FIRST_VALUE(sale_date) OVER (PARTITION BY client ORDER BY sale_date). По ней считают когорты.

Такие задачи дают на собеседованиях, SQL входит в навыки бизнес-аналитика.

Чем отличаются оконные функции в PostgreSQL, MySQL и ClickHouse?

Синтаксис OVER одинаков во всех трёх СУБД. Различаются функции дат и фильтр по окну.

• PostgreSQL. Оконные функции есть с версии 8.4. Месяц получают через DATE_TRUNC('month', sale_date). Деление целого на целое отбрасывает дробную часть: 5 / 2 даёт 2.
• MySQL 8.0+. DATE_TRUNC в справочнике функций дат нет, месяц получают через DATE_FORMAT(sale_date, '%Y-%m-01'). QUALIFY в документации MySQL 8.0 не описан, фильтр по окну пишут через CTE.
• ClickHouse. Поддерживает ROWS и RANGE, по умолчанию берёт рамку RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Есть QUALIFY: он фильтрует строки после расчёта оконных функций. Помимо LAG и LEAD есть lagInFrame и leadInFrame, которые учитывают рамку окна.
• DuckDB. Мы проверили QUALIFY: он заменяет CTE из задачи 6.

Какие ошибки чаще всего делают в оконных функциях?

Три ошибки дают неверный результат или падение запроса.

LAST_VALUE без рамки окна

С ORDER BY рамка по умолчанию заканчивается на текущей строке. LAST_VALUE возвращает значение текущей строки.

SELECT client, sale_date, amount,
       LAST_VALUE(amount) OVER (PARTITION BY client ORDER BY sale_date) AS last_amount
FROM sales
WHERE client = 'Альфа'
ORDER BY sale_date

Рамку до конца группы задают явно:

SELECT client, sale_date, amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY client ORDER BY sale_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_amount
FROM sales
WHERE client = 'Альфа'
ORDER BY sale_date

То же правило действует для NTH_VALUE.

Фильтр по оконной функции в WHERE

WHERE выполняется раньше оконных функций, поэтому такой запрос падает:

SELECT client, sale_date
FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY client ORDER BY sale_date) = 1

DuckDB отвечает «Binder Error: WHERE clause cannot contain window functions!», SQLite — «misuse of window function ROW_NUMBER()». Фильтр выносят в CTE, как в задаче 6, или в QUALIFY.

Нарастающий итог по колонке с повторами

Без явной рамки окно работает как RANGE: строки с одинаковым значением ORDER BY считаются вместе. ROWS считает строки по одной.

SELECT id, amount,
       SUM(amount) OVER (ORDER BY amount
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_rows,
       SUM(amount) OVER (ORDER BY amount
           RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_range
FROM sales
WHERE amount <= 80000
ORDER BY amount, id

У продажи 4 sum_range сразу включает и продажу 6 с той же суммой. Для построчного итога пишите ROWS и добавляйте в ORDER BY уникальную колонку.

Частые вопросы

Другие материалы по аналитике собраны в разделе Аналитика.