Оконные функции SQL: OVER, PARTITION BY, LAG и ROW_NUMBER на примерах

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

Если вы только начинаете, сначала пригодится обзор языка SQL. Без оконных функций такие задачи решают через GROUP BY и подзапросы, мы разбирали их в статье о функции COUNT и группировке.

Все примеры ниже мы выполнили на одной таблице в PostgreSQL 16, MySQL 8.0 и SQLite 3.51. Цифры в выводе у трёх баз совпали, формат показан как в psql.

CheckroiПодборка курсов по SQL421 курс • 40 школСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→

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

Возьмём таблицу sales: три менеджера, выручка по месяцам в тысячах рублей. У Веры нет апреля, а у Анны в феврале и марте одинаковая сумма. Оба момента пригодятся в примерах.

CREATE TABLE sales (
  manager VARCHAR(20),
  month   VARCHAR(7),
  amount  INT
);

INSERT INTO sales (manager, month, amount) VALUES
  ('Анна',  '2026-01', 120), ('Анна',  '2026-02', 150), ('Анна',  '2026-03', 150), ('Анна',  '2026-04', 90),
  ('Борис', '2026-01', 200), ('Борис', '2026-02', 180), ('Борис', '2026-03', 210), ('Борис', '2026-04', 240),
  ('Вера',  '2026-01', 80),  ('Вера',  '2026-02', 80),  ('Вера',  '2026-03', 130);

Обычная группировка отдаёт по строке на менеджера, отдельные месяцы пропадают:

SELECT manager, SUM(amount) AS total
FROM sales
GROUP BY manager;

 manager | total
---------+-------
 Анна    |   510
 Борис   |   830
 Вера    |   290

Та же сумма через OVER остаётся рядом с каждой строкой. Все 11 продаж на месте, у каждой видно итог её менеджера:

SELECT manager, month, amount,
       SUM(amount) OVER (PARTITION BY manager) AS total
FROM sales
ORDER BY manager, month;

 manager |  month  | amount | total
---------+---------+--------+-------
 Анна    | 2026-01 |    120 |   510
 Анна    | 2026-02 |    150 |   510
 Анна    | 2026-03 |    150 |   510
 Анна    | 2026-04 |     90 |   510
 Борис   | 2026-01 |    200 |   830
 ...
 Вера    | 2026-03 |    130 |   290

Отсюда главный практический выигрыш: строку можно сравнить с группой без подзапроса и JOIN. Например, долю каждой продажи в общей выручке. Пустые скобки OVER () означают «окно из всех строк таблицы»:

SELECT manager, month, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS share_pct
FROM sales
ORDER BY share_pct DESC
LIMIT 3;

 manager |  month  | amount | share_pct
---------+---------+--------+-----------
 Борис   | 2026-04 |    240 |      14.7
 Борис   | 2026-03 |    210 |      12.9
 Борис   | 2026-01 |    200 |      12.3

Апрельские 240 тысяч Бориса дали 14,7 % от общих 1 630 тысяч.

Синтаксис OVER: из чего состоит окно

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

функция(аргументы) OVER (
    PARTITION BY столбец      -- на какие группы делить строки
    ORDER BY столбец          -- в каком порядке идти внутри группы
    ROWS BETWEEN ... AND ...  -- какие строки брать в расчёт (рамка)
)

• PARTITION BY делит строки на разделы, и функция считается внутри каждого раздела заново. Без него раздел один, вся выборка.
• ORDER BY внутри OVER задаёт порядок строк в разделе. Он нужен ранжирующим функциям и LAG/LEAD, а у SUM и AVG превращает итог по группе в нарастающий.
• Рамка (ROWS, RANGE, в PostgreSQL и SQLite ещё GROUPS) сужает раздел до строк вокруг текущей. О ней ниже отдельный раздел, потому что значение по умолчанию часто удивляет.

Теперь о том, когда база считает окно. Во введении в оконные функции PostgreSQL сказано, что они видят строки, которые уже прошли через WHERE, GROUP BY и HAVING, и логически выполняются после этих предложений. Документация MySQL добавляет, что окна считаются раньше ORDER BY и LIMIT всего запроса.

Отсюда правило: строка, которую отрезал WHERE, в окно не попадёт, а фильтровать по результату оконной функции в том же запросе нельзя. В том же введении это сказано прямо: в WHERE, GROUP BY и HAVING оконные функции запрещены. Как это обойти, покажем в разделе с примерами.

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

Список в PostgreSQL, MySQL и SQLite почти одинаковый. Удобнее делить функции по задаче:

Полный перечень с типами аргументов есть в справке «Оконные функции» для PostgreSQL 16. Любая агрегатная функция становится оконной, если дописать к ней OVER, отдельного синтаксиса для неё нет.

Примеры оконных функций SQL на одной таблице

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

Нумерация и рейтинг: ROW_NUMBER, RANK, DENSE_RANK

Три функции отличаются только тем, как они обходятся с равными значениями. У Анны в феврале и марте по 150, у Веры в январе и феврале по 80:

SELECT manager, month, amount,
       ROW_NUMBER() OVER (PARTITION BY manager ORDER BY amount DESC, month) AS rn,
       RANK()       OVER (PARTITION BY manager ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY manager ORDER BY amount DESC) AS drnk
FROM sales
WHERE manager IN ('Анна', 'Вера')
ORDER BY manager, rn;

 manager |  month  | amount | rn | rnk | drnk
---------+---------+--------+----+-----+------
 Анна    | 2026-02 |    150 |  1 |   1 |    1
 Анна    | 2026-03 |    150 |  2 |   1 |    1
 Анна    | 2026-01 |    120 |  3 |   3 |    2
 Анна    | 2026-04 |     90 |  4 |   4 |    3
 Вера    | 2026-03 |    130 |  1 |   1 |    1
 Вера    | 2026-01 |     80 |  2 |   2 |    2
 Вера    | 2026-02 |     80 |  3 |   2 |    2

• ROW_NUMBER всегда даёт разные номера, даже равным суммам.
• RANK ставит равным одно место и пропускает следующее: после двух первых у Анны сразу третье.
• DENSE_RANK пропусков не делает: после двух первых идёт второе место.

Второй ключ month в ORDER BY у ROW_NUMBER нужен, чтобы номера не менялись между запусками. У строк с равной суммой нет заданного порядка, и без второго ключа номер 1 получит февраль или март на усмотрение базы. Если нужны просто уникальные значения без нумерации, хватит DISTINCT.

Нарастающий итог

Добавьте ORDER BY внутрь OVER, и сумма по менеджеру превратится в сумму с начала года по текущий месяц:

SELECT manager, month, amount,
       SUM(amount) OVER (PARTITION BY manager ORDER BY month) AS running_total
FROM sales
WHERE manager = 'Анна'
ORDER BY month;

 manager |  month  | amount | running_total
---------+---------+--------+---------------
 Анна    | 2026-01 |    120 |           120
 Анна    | 2026-02 |    150 |           270
 Анна    | 2026-03 |    150 |           420
 Анна    | 2026-04 |     90 |           510

Здесь месяцы не повторяются, поэтому итог растёт ровно по строке. Когда в ORDER BY есть повторы, результат другой, и это главная ловушка рамки по умолчанию.

LAG и LEAD: сравнение с прошлым месяцем

LAG берёт значение из предыдущей строки окна, LEAD из следующей. Так прирост к прошлому месяцу считается одной строкой SQL, без соединения таблицы с самой собой:

SELECT manager, month, amount,
       LAG(amount)  OVER (PARTITION BY manager ORDER BY month) AS prev_amount,
       amount - LAG(amount) OVER (PARTITION BY manager ORDER BY month) AS diff,
       LEAD(amount) OVER (PARTITION BY manager ORDER BY month) AS next_amount
FROM sales
WHERE manager = 'Борис'
ORDER BY month;

 manager |  month  | amount | prev_amount | diff | next_amount
---------+---------+--------+-------------+------+-------------
 Борис   | 2026-01 |    200 |             |      |         180
 Борис   | 2026-02 |    180 |         200 |  -20 |         210
 Борис   | 2026-03 |    210 |         180 |   30 |         240
 Борис   | 2026-04 |    240 |         210 |   30 |

У января нет предыдущей строки, у апреля нет следующей, и там стоит NULL. psql показывает его пустой ячейкой, MySQL пишет NULL словом. У функции есть ещё два аргумента: сдвиг (по умолчанию 1) и значение на случай, когда строки нет (по умолчанию NULL). Так описывают LAG и справочник функций PostgreSQL, и справка Microsoft:

SELECT month, amount,
       LAG(amount, 1, 0) OVER (ORDER BY month) AS prev_amount
FROM sales
WHERE manager = 'Вера'
ORDER BY month;

  month  | amount | prev_amount
---------+--------+-------------
 2026-01 |     80 |           0
 2026-02 |     80 |          80
 2026-03 |    130 |          80

LAG смотрит на соседнюю строку, а не на прошлый календарный месяц. Если месяц пропущен, функция возьмёт строку перед ним. Для отчётов «месяц к месяцу» пропуски сначала заполняют, например, через календарную таблицу.

Скользящее среднее за три месяца

Рамка ROWS BETWEEN 2 PRECEDING AND CURRENT ROW берёт текущую строку и две перед ней:

SELECT month, amount,
       ROUND(AVG(amount) OVER (ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3m
FROM sales
WHERE manager = 'Борис'
ORDER BY month;

  month  | amount | avg_3m
---------+--------+--------
 2026-01 |    200 |  200.0
 2026-02 |    180 |  190.0
 2026-03 |    210 |  196.7
 2026-04 |    240 |  210.0

В январе в рамке одна строка, в феврале две, полное окно из трёх месяцев появляется только с марта. Первые значения скользящего среднего поэтому сравнивать с остальными нельзя.

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

Лучший месяц каждого менеджера

Задача «первая строка в каждой группе» встречается на собеседованиях постоянно. Первая мысль, отфильтровать по ROW_NUMBER прямо в WHERE, не работает. Все три базы отказываются выполнять такой запрос:

SELECT manager, month, amount
FROM sales
WHERE ROW_NUMBER() OVER (PARTITION BY manager ORDER BY amount DESC) = 1;

-- PostgreSQL 16:
ERROR:  window functions are not allowed in WHERE
-- MySQL 8.0:
ERROR 3593 (HY000): You cannot use the window function 'row_number' in this context.
-- SQLite 3.51:
misuse of window function ROW_NUMBER()

Решение: посчитать номер во вложенном запросе или CTE, а отфильтровать снаружи. Тот же приём показывает и документация PostgreSQL:

SELECT manager, month, amount
FROM (
  SELECT manager, month, amount,
         ROW_NUMBER() OVER (PARTITION BY manager ORDER BY amount DESC, month) AS rn
  FROM sales
) AS t
WHERE rn = 1
ORDER BY manager;

 manager |  month  | amount
---------+---------+--------
 Анна    | 2026-02 |    150
 Борис   | 2026-04 |    240
 Вера    | 2026-03 |    130

Нужны три лучших месяца, пишите rn <= 3. Хотите оставить все месяцы с рекордной суммой, если их несколько, замените ROW_NUMBER на RANK. Другие частые задачи с интервью собраны в разборе собеседования на аналитика данных.

Рамка окна: ROWS и RANGE

Если в OVER есть ORDER BY, а рамку вы не задали, база подставляет свою. В PostgreSQL и MySQL правило одно: рамка идёт от начала раздела до текущей строки плюс все строки с таким же значением ORDER BY. Это описано в учебной главе PostgreSQL об окнах и в разделе справки MySQL о рамке окна. Без ORDER BY в рамку входит весь раздел.

Посмотрите, что это делает с нарастающим итогом, когда сортировка идёт по сумме, а у Анны две продажи по 150:

SELECT month, amount,
       SUM(amount) OVER (ORDER BY amount) AS sum_range,
       SUM(amount) OVER (ORDER BY amount
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_rows
FROM sales
WHERE manager = 'Анна'
ORDER BY amount, month;

  month  | amount | sum_range | sum_rows
---------+--------+-----------+----------
 2026-04 |     90 |        90 |       90
 2026-01 |    120 |       210 |      210
 2026-02 |    150 |       510 |      360
 2026-03 |    150 |       510 |      510

• RANGE (так работает умолчание) считает обе строки по 150 одной ступенью, и у февраля сразу 510.
• ROWS идёт по физическим строкам, и февраль получает свои 360.

Вторая ловушка того же умолчания, LAST_VALUE. Кажется, что функция вернёт последнее значение раздела. На деле рамка кончается текущей строкой, и вы получаете сумму текущего месяца:

SELECT month, amount,
       LAST_VALUE(amount) OVER (ORDER BY month) AS last_default,
       LAST_VALUE(amount) OVER (ORDER BY month
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM sales
WHERE manager = 'Анна'
ORDER BY month;

  month  | amount | last_default | last_full
---------+--------+--------------+-----------
 2026-01 |    120 |          120 |        90
 2026-02 |    150 |          150 |        90
 2026-03 |    150 |          150 |        90
 2026-04 |     90 |           90 |        90

Документация PostgreSQL прямо пишет, что с рамкой по умолчанию результаты LAST_VALUE «могут быть не очень полезны», и советует задавать рамку явно. Совет: там, где важен точный итог, пишите ROWS BETWEEN сами и не полагайтесь на умолчание.

Именованное окно WINDOW

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

SELECT month, amount,
       MIN(amount) OVER w AS min_so_far,
       MAX(amount) OVER w AS max_so_far
FROM sales
WHERE manager = 'Борис'
WINDOW w AS (ORDER BY month)
ORDER BY month;

  month  | amount | min_so_far | max_so_far
---------+--------+------------+------------
 2026-01 |    200 |        200 |        200
 2026-02 |    180 |        180 |        200
 2026-03 |    210 |        180 |        210
 2026-04 |    240 |        180 |        240

В PostgreSQL, MySQL и SQLite запрос выполнился без правок. В Microsoft SQL Server предложение WINDOW появилось только в версии 2022 и, по справке Microsoft, требует уровня совместимости базы 160 или выше. На старых серверах окно придётся повторять в каждом OVER.

Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в канал→

В каких СУБД работают оконные функции

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

Страницы dev.mysql.com с российского адреса иногда отвечают ошибкой доступа. Тогда то же правило про рамку по умолчанию можно прочитать в документации PostgreSQL на postgrespro.ru, она открывается без ограничений.

Частые ошибки и что с ними делать

1. Фильтр по оконной функции в WHERE. Ошибка выше, «window functions are not allowed in WHERE». Решение: вложенный запрос или CTE, фильтр снаружи. HAVING тоже не подойдёт, он выполняется раньше окна.
2. ROW_NUMBER без ORDER BY. В документации MySQL сказано, что без сортировки строки раздела не упорядочены и нумерация недетерминирована. Запрос сегодня и завтра вернёт разные «первые» строки.
3. Повторы в ORDER BY. Две строки с одной суммой получают номера в случайном порядке, а нарастающий итог прыгает ступенькой. Добавьте второй ключ сортировки, чаще всего дату или id.
4. SUM с ORDER BY внутри OVER вместо итога по группе. По документации PostgreSQL, агрегат с ORDER BY и рамкой по умолчанию считает «бегущую сумму». Нужен итог по всему разделу, уберите ORDER BY из OVER.
5. Сортировка результата через OVER. ORDER BY внутри окна упорядочивает строки только для расчёта. Порядок вывода задаёт обычный ORDER BY в конце запроса, без него строки придут как попало.

Писать и проверять такие запросы удобно в графическом клиенте, мы показывали, как подключить DBeaver к PostgreSQL. Оконные функции входят в базовый набор SQL-аналитика: на них строят когортные отчёты, расчёт удержания клиентов и сравнение периодов.

Где освоить SQL системно

Одна статья закрывает синтаксис, а уверенность приходит после сотни запросов к живым данным. На курсах по SQL оконные функции идут после JOIN и подзапросов, вместе с CTE и разбором плана выполнения. Если вы строите карьеру вокруг баз данных, посмотрите и план, как стать SQL-разработчиком.

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

Курс	Школа	Стоимость со скидкой	В рассрочку	Длитель­ность	Обзор курса от Checkroi
Нейросети для рабочих задач Перейти на сайт курса	Skillbox	31 290 ₽	-	1 месяц	Обзор курса
SQL для анализа данных Перейти на сайт курса	Skillbox	52 353 ₽	6203 ₽/мес.	2 месяца	Обзор курса
SQL-разработчик: тариф PRO Перейти на сайт курса	Эдюсон	89 900 ₽	7491 ₽/мес.	2 месяца	Обзор курса
SQL-разработчик: тариф Базовый Перейти на сайт курса	Эдюсон	79 900 ₽	3329 ₽/мес.	3 месяца	Обзор курса
SQL с нуля	Skypro	134 640 ₽	5500 ₽/мес.	12 месяцев	Обзор курса

• 

  Нейросети для рабочих задач

  Skillbox31 290 ₽1 месяц
  Перейти на сайт курсаОбзор
• 

  SQL для анализа данных

  Skillbox52 353 ₽6203 ₽/мес2 месяца
  Перейти на сайт курсаОбзор
• 

  SQL-разработчик: тариф PRO

  Эдюсон89 900 ₽7491 ₽/мес2 месяца
  Перейти на сайт курсаОбзор
• 

  SQL-разработчик: тариф Базовый

  Эдюсон79 900 ₽3329 ₽/мес3 месяца
  Перейти на сайт курсаОбзор
• 

  SQL с нуля

  Skypro134 640 ₽5500 ₽/мес12 месяцев
  Обзор

Больше программ — в полном каталоге курсов по SQL