Learning Platform

Оконные функции в SQL: PARTITION BY, ORDER BY и фреймы

7 мин чтения
sqlwindow-functionsanalyticspostgresqldata-engineeringdatabases

Главная мысль: окно считает рядом, а не вместо строки

Самое важное про оконные функции укладывается в одну фразу: оконная функция добавляет к строке новое значение, но саму строку не убирает. Это и есть всё отличие от GROUP BY. Когда вы пишете GROUP BY, движок схлопывает множество строк в одну: было десять продаж по региону — стала одна строка с суммой. Когда вы пишете оконную функцию, движок берёт те же десять строк, для каждой считает что-то по соседям и возвращает все десять, дописав к каждой ещё одну колонку.

Поэтому ментальная модель такая: GROUP BY — это «свернуть и потерять детали», окно — это «посчитать агрегат сбоку, оставив детали на месте». Если вам когда-нибудь хотелось в одном запросе показать и конкретную продажу, и долю этой продажи в сумме по региону — без двух запросов и без джойна с подзапросом — это ровно та задача, ради которой окна и придуманы.

Синтаксически окно — это любая функция с ключевым словом OVER (...) после неё. Всё, что внутри скобок OVER, описывает, какие строки считать соседями для текущей строки. Пустые скобки OVER () означают «соседи — это вообще все строки результата».

SELECT
  region,
  amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total,
  amount::numeric / SUM(amount) OVER (PARTITION BY region) AS share
FROM sales;

Здесь region_total повторяется в каждой строке региона — потому что строки не схлопнулись. А share — доля конкретной продажи в сумме по её региону, посчитать которую без окна обычно означало бы отдельный подзапрос.

PARTITION BY: режем результат на независимые корзины

PARTITION BY делит набор строк на группы, и внутри каждой группы окно считается независимо. Если в OVER нет PARTITION BY, вся выборка — одна большая партиция.

Ключевое отличие от GROUP BY: PARTITION BY нарезает строки на корзины только для расчёта окна, но не уменьшает число строк на выходе. Граница партиции — это «забор»: функция не видит строки из других партиций. SUM(amount) OVER (PARTITION BY region) — это сумма внутри своего региона; пересечь границу региона она не может.

Думайте о PARTITION BY как о GROUP BY, который оставляет детали. Те же поля группировки, та же логика «считаем по каждой корзине» — но результат пришивается к каждой исходной строке, а не вместо них.

Можно перечислить несколько полей: PARTITION BY region, channel создаст отдельную корзину на каждую пару «регион + канал», и окно будет считаться независимо в каждой такой паре. Это полезно держать в голове как контраст с GROUP BY: набор полей выглядит одинаково, но смысл результата разный — GROUP BY отдаст по одной строке на пару, окно отдаст все строки, дописав агрегат.

Важная деталь про производительность: чтобы посчитать окно с PARTITION BY и ORDER BY, движку нужно физически разложить строки по партициям и отсортировать каждую. В PostgreSQL это видно в плане запроса как узел WindowAgg, которому почти всегда предшествует Sort. Если несколько окон в одном SELECT используют одинаковое OVER, движок переиспользует одну сортировку на все — поэтому держать окна согласованными по PARTITION BY и ORDER BY выгодно не только для читаемости.

ORDER BY внутри OVER: задаём порядок и неявный фрейм

Дальше начинается самое тонкое. ORDER BY внутри OVER (...) — это не та сортировка, что в конце запроса. Внешний ORDER BY упорядочивает вывод. ORDER BY внутри окна задаёт порядок, в котором строки накапливаются для расчёта, и именно он включает накопительное поведение.

Сравните два выражения:

  • SUM(amount) OVER (PARTITION BY region) — сумма по всей партиции, одинаковая во всех строках.
  • SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) — нарастающий итог: в каждой строке сумма от начала партиции до текущей строки включительно.

Почему так? Потому что у ORDER BY внутри окна есть скрытый эффект: при наличии ORDER BY и при отсутствии явного фрейма СУБД подставляет фрейм по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. То есть «от начала партиции до текущей строки». Уберите ORDER BY — фреймом станет вся партиция. Именно эта неявная подмена фрейма и превращает обычный SUM в running total, и именно поэтому она так часто удивляет новичков.

Ранжирование: ROW_NUMBER, RANK, DENSE_RANK

Ранжирующие функции требуют ORDER BY внутри окна — им нужно знать, по чему расставлять места. Они отличаются тем, как обходятся с одинаковыми значениями (ничьими).

ФункцияЧто делаетНичьи (равные значения)Пропуски в нумерации
ROW_NUMBER()Сквозной номер строки в окнеРазные номера (порядок произвольный)Нет
RANK()Спортивный рангОдин и тот же рангДа (после ничьей номер прыгает)
DENSE_RANK()Плотный рангОдин и тот же рангНет
NTILE(n)Раскладывает строки на n примерно равных группПо границам бакетовНе применимо
PERCENT_RANK()Относительный ранг от 0 до 1ОдинаковыйНе применимо

Разница RANK и DENSE_RANK на примере оценок 100, 100, 90: RANK даст 1, 1, 3 (двойка пропущена, потому что две строки заняли первое и «второе» место), DENSE_RANK даст 1, 1, 2 (плотно, без дырки). ROW_NUMBER даст 1, 2, 3 всегда, даже при равных значениях — поэтому он подходит, когда нужен гарантированно уникальный номер, например для дедупликации: оставить «по одной строке на ключ» — это ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY ...) и затем фильтр WHERE rn = 1.

Какой из трёх брать — решает задача. Нужен честный спортивный рейтинг с разрывами при ничьих — RANK. Нужны «уровни» без дырок (топ-1, топ-2, топ-3 как категории) — DENSE_RANK. Нужен технический уникальный идентификатор позиции — ROW_NUMBER. Путаница между ними — одна из самых частых причин расхождений в аналитических отчётах, когда в данных есть дубли по ключу сортировки.

Классический рецепт «топ-N в каждой группе» строится именно на ROW_NUMBER:

SELECT region, product, amount
FROM (
  SELECT
    region,
    product,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY region
      ORDER BY amount DESC
    ) AS rn
  FROM sales
) ranked
WHERE rn <= 3;

Внутри подзапроса каждой строке проставляется её место внутри региона (PARTITION BY region) по убыванию суммы. Внешний WHERE rn <= 3 оставляет три лучшие продажи в каждом регионе. Обратите внимание: фильтровать по rn приходится снаружи — оконные функции нельзя использовать в WHERE того же уровня, потому что они вычисляются логически после WHERE (об этом ниже).

Frame clause: ROWS против RANGE

frame clause — это и есть ответ на вопрос «какие именно соседи участвуют в расчёте для текущей строки». Он имеет смысл только когда есть ORDER BY внутри окна, и пишется как ROWS BETWEEN ... AND ... или RANGE BETWEEN ... AND ....

Границы фрейма:

  • UNBOUNDED PRECEDING — от самого начала партиции.
  • N PRECEDING — на N строк (или значений) назад.
  • CURRENT ROW — текущая строка.
  • N FOLLOWING — на N вперёд.
  • UNBOUNDED FOLLOWING — до конца партиции.

Принципиальная и самая частая ловушка — разница между ROWS и RANGE:

  • ROWS считает физические строки: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это ровно три строки (две предыдущие плюс текущая), что бы в них ни лежало.
  • RANGE считает по значениям колонки сортировки: все строки, чьё значение ORDER BY попадает в диапазон. При этом строки с одинаковым значением сортировки трактуются как одна группа (peer-группа) и попадают во фрейм целиком.

Отсюда коварство фрейма по умолчанию. Когда вы пишете SUM(...) OVER (ORDER BY sale_date) без явного фрейма, действует RANGE ... CURRENT ROW. Если в один день несколько продаж, RANGE включит в running total все продажи этого дня сразу — и в строках одного дня вы увидите одинаковую накопленную сумму, а не пошаговый рост. Чтобы получить честный построчный нарастающий итог, почти всегда нужен явный ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

SELECT
  sale_date,
  amount,
  -- построчный нарастающий итог: ровно «всё до текущей строки»
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  -- скользящее среднее по последним трём строкам
  AVG(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM sales;

Добавление id во вторичный ключ сортировки делает порядок детерминированным: без него строки с одинаковой датой могут идти в произвольном порядке, и нарастающий итог станет невоспроизводимым между запусками.

LAG и LEAD: заглянуть в соседнюю строку

LAG и LEAD — это окна, которые не агрегируют, а берут значение из другой строки относительно текущей в порядке ORDER BY. LAG(x, n) смотрит на n строк назад, LEAD(x, n) — на n строк вперёд. По умолчанию n = 1. Третий аргумент — значение по умолчанию, когда заглядывать некуда (край партиции): LAG(amount, 1, 0).

Это рабочая лошадка аналитики «период к периоду»: разница с предыдущим днём, рост выручки, длительность между событиями пользователя. Без окон такое считается через self-join по сдвинутому ключу — медленно и многословно; LAG делает то же одной колонкой.

SELECT
  sale_date,
  amount,
  LAG(amount) OVER (ORDER BY sale_date)               AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY sale_date)       AS delta,
  LEAD(amount) OVER (ORDER BY sale_date)               AS next_amount
FROM sales;

В самой первой строке prev_amount будет NULL — назад смотреть некуда; в последней NULL будет у next_amount. Поэтому delta в первой строке тоже NULL. Если для арифметики нужен ноль вместо NULL, задайте значение по умолчанию третьим аргументом или оберните в COALESCE.

Рядом стоят FIRST_VALUE, LAST_VALUE и NTH_VALUE — они берут не соседа по смещению, а конкретную строку внутри фрейма. Тут снова всплывает фрейм: LAST_VALUE(x) OVER (ORDER BY d) с фреймом по умолчанию вернёт текущую строку, а не последнюю в партиции, потому что фрейм заканчивается на CURRENT ROW. Чтобы получить настоящее последнее значение партиции, нужен явный ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Порядок выполнения: почему окно нельзя в WHERE

Чтобы не ловить ошибки «column rn does not exist» в WHERE, держите в голове логический порядок шагов SQL: FROM и джойны, затем WHERE, затем GROUP BY и HAVING, затем оконные функции (на этапе SELECT), и только потом внешний ORDER BY и LIMIT.

Из этого следуют два практических вывода. Первый: окна видят строки уже после WHERE и GROUP BY — отфильтрованные строки в окно не попадут, а строки уже агрегированные GROUP BY можно окнить поверх агрегата. Второй: раз окна вычисляются на этапе SELECT, обратиться к их результату в WHERE того же запроса нельзя — он ещё не существует. Поэтому фильтрация по результату окна (топ-N, «строки с рангом 1») всегда выносится во внешний подзапрос или CTE, как в примере выше.

Куда это ведёт дальше

Оконные функции — это тот навык, после которого огромный класс задач, раньше требовавших подзапросов и self-join’ов, схлопывается в одну читаемую колонку: рейтинги, доли, нарастающие итоги, разницы период-к-периоду, дедупликация, сессионизация событий. А чтобы понимать, почему running total с RANGE ведёт себя иначе, чем с ROWS, полезно один раз увидеть, как планировщик материализует партиции и сортирует их перед оконным проходом — это уже территория внутренностей движка.

Разобраться по-настоящему помогает практика прямо в браузере. На бесплатном курсе SQL Fundamentals оконные функции, фреймы и ранжирование разобраны по шагам, с интерактивной песочницей на pglite — пишете запросы и сразу видите результат, ничего не устанавливая. Курс полностью бесплатный.

Если вам интересна не только сама команда, но и то, как СУБД исполняет окна, и где это встаёт в реальные пайплайны, посмотрите весь каталог направления Data Engineering: от SQL до планировщиков, форматов хранения и движков обработки.

CTA

Возьмите оконные функции руками, а не глазами: бесплатный курс SQL Fundamentals с песочницей в браузере — PARTITION BY, ORDER BY, фреймы и ранжирование на живых данных, без установки и без оплаты.

Ещё в направлении · Data Engineering

Все материалы направления →