Исполняемые доки SQLite: Оконные функции

Q: Чем RANK отличается от DENSE_RANK в SQLite?

Обе функции присваивают ранг по ORDER BY, но по-разному обрабатывают одинаковые значения. RANK() оставляет «дыры» после совпадений: если две строки делят первое место, следующая получит ранг 3. DENSERANK() идёт без пропусков - следующая строка будет с рангом 2. Берите DENSERANK(), когда нужна сплошная нумерация, и RANK(), когда разрыв в номерах несёт смысл.

Q: Как посчитать накопительный итог в SQLite?

Используйте SUM(column) OVER (ORDER BY ...) вместе с фреймом окна. По умолчанию ORDER BY внутри OVER подразумевает фрейм RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - это и даёт накопительную сумму. Добавьте PARTITION BY, если нужно сбрасывать итог в начале каждой группы.

Оконная функция добавляет колонку, не схлопывая строки

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

Синтаксис всегда один и тот же: функция, а после неё OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

Колонка total_all показывает общую сумму по всем строкам и повторяет её в каждой записи. Исходные строки при этом остаются нетронутыми. Сравните с SELECT SUM(amount) FROM sales - число получится то же, но в ответе будет всего одна строка. А оконные функции дают вам сразу оба представления.

PARTITION BY: агрегация внутри групп

Пустой OVER () считает агрегат по всей таблице. Если добавить PARTITION BY, агрегат будет считаться внутри групп - почти как GROUP BY, только без схлопывания строк.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

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

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

Семейство ранжирующих функций нумерует строки в соответствии с ORDER BY внутри OVER. Три варианта отличаются тем, как они обрабатывают одинаковые значения.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Если посмотреть на результат:

ROW_NUMBER() всегда даёт уникальный номер - при равных значениях порядок определяется произвольно. Используйте эту функцию, когда нужен стабильный сквозной номер для каждой строки.
RANK() присваивает одинаковый ранг строкам с равными значениями, но затем пропускает следующие номера. Если два игрока делят 1-е место, дальше идёт сразу 3-е.
DENSE_RANK() тоже даёт одинаковый ранг при совпадениях, но номера не пропускает - следующим будет 2-е место.

Чтобы получить «топ-N в каждой группе», совместите ранжирование с PARTITION BY и отфильтруйте результат во внешнем запросе - напрямую в WHERE оконные функции использовать нельзя:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Топ-2 сотрудника по зарплате в каждом регионе.

LAG и LEAD: заглядываем в соседние строки

LAG(col) возвращает значение col из предыдущей строки окна, а LEAD(col) - из следующей. Обе функции отлично подходят для задач, где нужно сравнить значение с тем, что было раньше или будет дальше.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

У первой строки yesterday будет NULL - до неё ничего нет. Можно подставить значение по умолчанию: запись LAG(celsius, 1, celsius) OVER (ORDER BY day) подставит сегодняшнее значение, если предыдущей строки не существует.

LEAD работает зеркально. Если скомбинировать обе функции с PARTITION BY, получится последовательность по каждой группе - например, сравнение продаж текущего месяца с предыдущим в рамках каждого региона.

Накопительная сумма через оконные рамки

Добавьте ORDER BY внутрь OVER - и агрегатные функции вроде SUM, AVG, COUNT начнут считаться нарастающим итогом:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Здесь стоит обратить внимание на две вещи:

SUM(amount) OVER (ORDER BY day) - это накопительная сумма. Когда вы пишете ORDER BY без явного указания рамки, по умолчанию берётся диапазон «от начала окна до текущей строки».
Во втором столбце рамка задана явно: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Это скользящее окно из трёх строк - то есть скользящее среднее.

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

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - накопительный итог (та самая рамка по умолчанию).
ROWS BETWEEN N PRECEDING AND CURRENT ROW - окно из последних N строк.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - вся партиция целиком.

ROWS отсчитывает физические строки. Есть ещё RANGE - он группирует по значению и пригождается, когда в столбце из ORDER BY встречаются одинаковые значения и вы хотите, чтобы они считались за один шаг.

FIRST_VALUE, LAST_VALUE и NTILE

Ещё несколько оконных функций, которые полезно знать:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

FIRST_VALUE и LAST_VALUE возвращают первое или последнее значение в рамке. С LAST_VALUE будьте внимательны к рамке: по умолчанию она заканчивается на CURRENT ROW, поэтому, чтобы получить реальное последнее значение секции, обычно нужно явно указать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
NTILE(n) делит строки на n примерно равных групп - удобно для квартилей, перцентилей и разбиений в духе A/B-тестов.

Именованные окна через WINDOW

Когда несколько столбцов используют один и тот же OVER (...), повторять его становится утомительно. SQLite позволяет один раз задать окну имя и переиспользовать его:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Тот же запрос, только без визуального шума. Блок WINDOW ставится после WHERE/GROUP BY/HAVING и перед ORDER BY.

Оконные функции vs GROUP BY

Обе конструкции связаны с агрегацией, но решают разные задачи:

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

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

Пара подводных камней

WHERE не видит оконные функции. Фильтрация выполняется раньше, чем считаются окна. Оберните запрос в подзапрос или CTE и фильтруйте уже на внешнем уровне.
Неявные рамки кусаются. SUM(x) OVER (ORDER BY y) даёт накопительную сумму, потому что рамка по умолчанию - RANGE UNBOUNDED PRECEDING. Если нужна сумма по всей партиции, пишите OVER (PARTITION BY ...) без ORDER BY либо задавайте рамку явно.
LAST_VALUE удивляет всех с первого раза. При стандартной рамке, заканчивающейся текущей строкой, функция возвращает текущее значение, а не последнее в партиции. Рамку нужно переопределить.
Оконные функции работают только в SQLite 3.25+ (релиз 2018 года). В любой более-менее свежей сборке они есть, но в некоторых встраиваемых окружениях версии бывают древнее.

Дальше: генерируемые колонки

Оконные функции - это вычисления на этапе запроса. Следующая страница про вычисления на этапе хранения: генерируемые колонки, значение которых задаётся выражением и пересчитывается автоматически при изменении исходных данных.

Часто задаваемые вопросы

Что такое оконные функции в SQLite?

Оконные функции считают значение по набору строк, связанных с текущей строкой, но при этом не схлопывают результат, как это делает GROUP BY. К функциям вроде ROW_NUMBER(), RANK(), SUM() или LAG() дописывается клауза OVER (...), которая и задаёт само окно. Все исходные строки остаются в выдаче - просто появляется ещё одна вычисляемая колонка.

Чем RANK отличается от DENSE_RANK в SQLite?

Обе функции присваивают ранг по ORDER BY, но по-разному обрабатывают одинаковые значения. RANK() оставляет «дыры» после совпадений: если две строки делят первое место, следующая получит ранг 3. DENSE_RANK() идёт без пропусков - следующая строка будет с рангом 2. Берите DENSE_RANK(), когда нужна сплошная нумерация, и RANK(), когда разрыв в номерах несёт смысл.

Как посчитать накопительный итог в SQLite?

Используйте SUM(column) OVER (ORDER BY ...) вместе с фреймом окна. По умолчанию ORDER BY внутри OVER подразумевает фрейм RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - это и даёт накопительную сумму. Добавьте PARTITION BY, если нужно сбрасывать итог в начале каждой группы.

Связанные концепции

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