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

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

Все запросы ниже прогнаны в SQLite 3.50.4 через модуль sqlite3 из Python 3.14.3 на Windows 10. Оконные функции появились в SQLite 3.25.0 (сентябрь 2018 года), а режим GROUPS, предложение EXCLUDE и сцепление окон добавили в 3.28.0. В более старой сборке запросы с OVER не заработают, поэтому версию проверьте запросом SELECT sqlite_version();.

Таблица для примеров

Учебная таблица продаж содержит категорию товара, дату продажи и сумму. Запустите этот блок первым, все остальные примеры работают с ней. Как устроен первичный ключ в CREATE TABLE, подробно разобрано в статье о первичном и внешнем ключе в SQL.

CREATE TABLE sales (
    id        INTEGER PRIMARY KEY,
    category  TEXT    NOT NULL,
    sale_date TEXT    NOT NULL,
    amount    INTEGER NOT NULL
);

INSERT INTO sales (id, category, sale_date, amount) VALUES
    (1,  'A', '2026-09-01', 500),
    (2,  'B', '2026-09-01', 300),
    (3,  'A', '2026-09-02', 700),
    (4,  'C', '2026-09-02', 700),
    (5,  'B', '2026-09-03', 300),
    (6,  'A', '2026-09-03', 200),
    (7,  'C', '2026-09-04', 400),
    (8,  'B', '2026-09-04', 900),
    (9,  'A', '2026-09-05', 700),
    (10, 'C', '2026-09-05', 100);

В данных нарочно есть повторы: продажи по 700 встречаются трижды, по 300 дважды, и на каждый день приходится по две записи.

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

GROUP BY сворачивает группу в одну строку, и детали пропадают. Оконная функция вычисляет тот же агрегат, но приписывает его к каждой исходной строке. Сравните запросы к одной таблице, с группировкой и без неё:

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

SELECT id, category, amount,
       COUNT(*)    OVER (PARTITION BY category) AS sales_count,
       SUM(amount) OVER (PARTITION BY category) AS category_total,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY category), 1) AS share
FROM sales
ORDER BY category, id;

SELECT category, SUM(amount) AS total,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS place
FROM sales
GROUP BY category
ORDER BY place;

Результат первого запроса, по строке на категорию:

Второй запрос вернул все десять строк таблицы:

Третий:

Суммы во втором запросе те же, что в первом, но каждая стоит рядом со своей продажей, и долю продажи в выручке категории можно получить в той же строке. Без оконной функции для этого нужно делать подзапрос с GROUP BY и соединять его с таблицей через JOIN. Третий пример показывает другое правило: обычные агрегатные функции выполняются раньше оконных функций. RANK получает уже готовые суммы по группам, поэтому агрегат можно передать оконной функции аргументом, а наоборот нельзя.

Синтаксис оконных функций SQL

Общий вид вызова, в квадратных скобках необязательные части:

функция(аргументы) OVER (
    [PARTITION BY столбцы]
    [ORDER BY столбцы [ASC | DESC]]
    [ROWS | RANGE | GROUPS BETWEEN начало AND конец]
)

Пустые скобки OVER () означают одно окно на весь результат запроса: SUM(amount) OVER () вернёт 4800 в каждой строке нашей таблицы, это общая сумма всех продаж.

PARTITION BY

PARTITION BY делит строки результата на группы по значениям указанных столбцов, и функция вычисляется внутри каждой группы отдельно. Без PARTITION BY весь результат запроса считается одной группой. В примере выше PARTITION BY category дал три окна: A, B и C.

ORDER BY внутри OVER

ORDER BY внутри окна задаёт, в каком порядке функция проходит строки группы. Порядок вывода он не меняет: за него отвечает ORDER BY в конце SELECT, и окно может сортировать строки иначе, чем итоговый результат. Для ROW_NUMBER, RANK, LAG и LEAD без порядка результат теряет смысл. Если не указать сортировку в окне, все строки группы становятся равноправными, и RANK с DENSE_RANK вернут 1 для каждой строки.

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

Рамка окна (frame) выбирает из группы диапазон строк относительно текущей. ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW берёт текущую строку и две перед ней. RANGE отбирает строки по значению столбца сортировки, и строки с равным значением попадают в рамку вместе. GROUPS считает группы равных строк. Если сортировка в окне есть, а рамка не указана, используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: от начала группы до текущей строки вместе со всеми строками, равными ей по сортировке. Это значение по умолчанию в SQLite, PostgreSQL и SQL Server. С ним в нарастающем итоге равные по сортировке строки получают одну сумму, а LAST_VALUE не видит строк после текущей, кроме равных ей.

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

Если несколько функций в одном SELECT используют одно окно, его описывают один раз в предложении WINDOW после HAVING и перед ORDER BY: WINDOW w AS (PARTITION BY category ORDER BY sale_date), а в функции пишут OVER w. Пример есть ниже, у FIRST_VALUE.

Порядок выполнения

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

Функции ранжирования: ROW_NUMBER, RANK и DENSE_RANK

Основные функции ранжирования нумеруют строки в порядке ORDER BY и расходятся только на повторах. ROW_NUMBER присваивает каждой строке уникальный порядковый номер, даже если значения одинаковые. RANK даёт строкам с одинаковыми значениями одинаковый ранг и пропускает следующие номера. DENSE_RANK тоже присваивает одинаковый ранг, но без пропусков.

SELECT id, category, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS row_num,
       RANK()       OVER (ORDER BY amount DESC)     AS rank_gaps,
       DENSE_RANK() OVER (ORDER BY amount DESC)     AS rank_dense
FROM sales
ORDER BY amount DESC, id;

SELECT category, id, amount, pos
FROM (
    SELECT id, category, amount,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC, id) AS pos
    FROM sales
) AS ranked
WHERE pos <= 2
ORDER BY category, pos;

Второй запрос оставляет по две самые крупные продажи в каждой категории. Первый вернул такую таблицу:

Продажи по 700 получили ранг 2 у обеих функций. Следующий ранг у RANK равен 5, потому что ранг равен номеру первой строки в группе равных. DENSE_RANK ставит 3, следующий номер группы. RANK удобен для мест в рейтинге, где после трёх участников на втором месте следующий получает ранг 5. DENSE_RANK подходит, когда нужны уровни значений без пропусков.

В ROW_NUMBER мы добавили в сортировку второй столбец id. Строкам с равными суммами ROW_NUMBER раздаёт номера в неопределённом порядке, и без второго ключа любая продажа на 700 может оказаться второй. Для отчётов и постраничного вывода мы бы всегда добавляли такой ключ.

Остальные функции ранжирования в SQLite:

Топ-N в каждой группе

Частая задача: две самые крупные продажи в каждой категории. Первая попытка обычно такая: WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) <= 2. SQLite её не выполнит:

sqlite3.OperationalError: misuse of window function ROW_NUMBER()

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

В категории B две продажи по 300, и в топ попала только одна, с меньшим id. Если вместо ROW_NUMBER поставить DENSE_RANK, условие pos <= 2 вернёт все продажи с двумя наибольшими суммами: в категории B это 900 и обе продажи по 300, в категории A две по 700 и одна на 500. Ещё одна задача на самый дорогой товар в категории разобрана в подборке задач по SQL с решениями.

LAG и LEAD: значение из предыдущей и следующей строки

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

SELECT category, sale_date, amount,
       LAG(amount) OVER (PARTITION BY category ORDER BY sale_date) AS prev_amount,
       amount - LAG(amount) OVER (PARTITION BY category ORDER BY sale_date) AS diff,
       LEAD(sale_date) OVER (PARTITION BY category ORDER BY sale_date) AS next_date
FROM sales
ORDER BY category, sale_date;

У первой строки каждой категории предыдущей строки нет, и LAG вернул NULL, а вместе с ним NULL получилась и разница. У последней строки нет следующей, поэтому next_date пустой. Полная форма LAG(amount, 1, 0) принимает смещение и значение по умолчанию: с ней у первой продажи категории A вместо NULL стоит 0, и разница amount - LAG(amount, 1, 0) в этой строке равна 500. Заменять NULL нулём стоит не всегда: разница «500 минус ноль» выглядит как прирост на 500, хотя сравнивать было не с чем. Рамку окна функции LAG и LEAD не учитывают, им нужны только разбиение на группы и сортировка.

Нарастающий итог и скользящее среднее

Агрегатные функции SUM, AVG, COUNT, MIN и MAX становятся оконными, если добавить к ним OVER. С сортировкой внутри окна SUM даёт нарастающий итог, а AVG с рамкой ROWS даёт скользящее среднее.

SELECT id, sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS total_range,
       SUM(amount) OVER (ORDER BY sale_date, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_rows,
       ROUND(AVG(amount) OVER (ORDER BY sale_date, id
                               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg
FROM sales
ORDER BY sale_date, id;

Столбец total_range посчитан с рамкой по умолчанию, то есть RANGE. Обе продажи 1 сентября равны по sale_date, поэтому попадают в рамку вместе, и уже в первой строке стоит 800. Это итог на конец дня, а не на момент продажи. Столбец total_rows с явным ROWS растёт по одной строке: 500, 800, 1500. Если нужен итог по строкам, а в столбце сортировки бывают повторы, пишите ROWS явно и добавляйте в ORDER BY уникальный столбец.

moving_avg усредняет текущую строку и две предыдущие, то есть три последние продажи независимо от дат. В первых двух строках предыдущих строк меньше двух, и среднее значение берётся по тем, что есть: 500.0, потом (500 + 300) / 2 = 400.0. Для среднего по календарным дням нужен RANGE со смещением по числовому значению даты: AVG(amount) OVER (ORDER BY julianday(sale_date) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) на 3 сентября даёт 450.0, среднее шести продаж с 1 по 3 сентября. В ORDER BY такого окна может быть только одно выражение. С ORDER BY sale_date, id SQLite отвечает ошибкой RANGE with offset PRECEDING/FOLLOWING requires one ORDER BY expression.

FIRST_VALUE и LAST_VALUE: ловушка рамки по умолчанию

FIRST_VALUE возвращает первое значение в рамке окна, LAST_VALUE последнее. В отличие от функций ранжирования и смещения, эти функции рамку учитывают, а рамка по умолчанию заканчивается на текущей строке. В запросе ниже окно описано один раз через WINDOW, а для третьего столбца к нему дописаны границы:

SELECT category, sale_date, amount,
       FIRST_VALUE(amount) OVER w AS first_amount,
       LAST_VALUE(amount)  OVER w AS last_wrong,
       LAST_VALUE(amount)  OVER (w ROWS BETWEEN UNBOUNDED PRECEDING
                                      AND UNBOUNDED FOLLOWING) AS last_amount
FROM sales
WINDOW w AS (PARTITION BY category ORDER BY sale_date)
ORDER BY category, sale_date;

last_wrong просто повторяет amount: внутри категории даты не повторяются, поэтому последняя строка рамки по умолчанию и есть текущая строка. Последнюю продажу в категории даёт только рамка до UNBOUNDED FOLLOWING. Запись OVER (w ROWS ...) называется сцеплением окон: новое окно берёт из w PARTITION BY и ORDER BY, а границы добавляет свои. Своего PARTITION BY у такого окна быть не может, а у базового окна w не должно быть рамки.

Оконные функции в PostgreSQL, MySQL и SQL Server

Разработчики SQLite брали за образец документацию PostgreSQL и прогоняли многие тесты оконных функций против PostgreSQL.

В SQLite DISTINCT в окне падает с ошибкой DISTINCT is not supported for window functions, это мы проверили на COUNT(DISTINCT category) OVER ().

На больших таблицах оконная функция сортирует строки каждой группы. В SQL Server такому запросу может помочь некластеризованный индекс, в котором сначала идут столбцы PARTITION BY, затем столбцы ORDER BY в том же порядке. В SQLite мы проверили это через EXPLAIN QUERY PLAN для SUM(amount) OVER (PARTITION BY category ORDER BY sale_date): без индекса в плане есть строка USE TEMP B-TREE FOR ORDER BY, а после CREATE INDEX idx_sales_category_date ON sales (category, sale_date) план меняется на SCAN sales USING INDEX idx_sales_category_date, и отдельной сортировки нет. Как читать план и когда индекс мешает, разобрано в статье про индексы в SQL.

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

Можно ли использовать оконные функции в WHERE?

Нет. Оконные функции разрешены только в списке SELECT и в ORDER BY, в WHERE, GROUP BY и HAVING их использовать нельзя. На попытку в WHERE SQLite отвечает ошибкой misuse of window function ROW_NUMBER(). Вычислите значение в подзапросе и фильтруйте во внешнем запросе, как в примере с топ-N.

Чем PARTITION BY отличается от GROUP BY?

GROUP BY оставляет одну строку на группу. PARTITION BY только делит строки на окна для вычисления, и в результате остаются все исходные строки, к каждой из которых добавлено значение по её окну.

Чем ROWS отличается от RANGE?

ROWS отсчитывает физические строки от текущей. RANGE берёт строки по значению столбца сортировки, и все строки с тем же значением попадают в рамку вместе с текущей. На нашей таблице нарастающий итог с RANGE в первой строке равен 800, с ROWS 500.

Можно ли использовать несколько оконных функций в одном запросе?

Да, и у каждой может быть своё окно: в примере с ранжированием в одном SELECT стоят ROW_NUMBER, RANK и DENSE_RANK, а в примере с нарастающим итогом окна у SUM и AVG разные. Одинаковые окна удобно вынести в предложение WINDOW.

Почему LAST_VALUE возвращает значение текущей строки?

Рамка по умолчанию заканчивается на текущей строке, точнее на последней из строк, равных ей по сортировке. Если равных нет, последней оказывается сама текущая строка. Укажите ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.