Оконные функции SQL: четыре задачи с решениями в PostgreSQL

Создайте восемь строк, которые можно проверить вручную

Выполните этот код один раз в сессии PostgreSQL, а следующие запросы запускайте в той же сессии. Временная таблица исчезнет после её завершения. Для повторного создания в текущей сессии сначала выполните DROP TABLE practice_orders. Все суммы здесь положительные, у каждого заказа один клиент. Совпадающие суммы и даты, а также пропущенные дни добавлены специально.

До оконных вычислений найдите контрольные суммы: клиент a потратил 550, клиент b — 360. Держите их рядом с результатами: так легко заметить забытый PARTITION BY. В реальном отчёте отдельно уточните, учитываются ли возвраты, отменённые заказы и пересчёт валют. В этом упражнении восемь строк составляют полную историю заказов, подходящих под условия задачи.

CREATE TEMP TABLE practice_orders (
  order_id integer PRIMARY KEY,
  customer text NOT NULL,
  ordered_on date NOT NULL,
  amount numeric(10, 2) NOT NULL
);

INSERT INTO practice_orders VALUES
  (1, 'a', '2026-09-01', 100),
  (2, 'a', '2026-09-02', 200),
  (3, 'a', '2026-09-02', 200),
  (4, 'a', '2026-09-05', 50),
  (5, 'b', '2026-09-01', 80),
  (6, 'b', '2026-09-03', 120),
  (7, 'b', '2026-09-04', 120),
  (8, 'b', '2026-09-07', 40);

SELECT * FROM practice_orders
ORDER BY customer, ordered_on, order_id;

Сначала определите, что означает одна строка результата

В нашем отчёте сохраняется одна строка на заказ. Группировка по клиенту свернула бы его историю в одну строку; оконная сумма позволяет показать рядом заказ и общий расход клиента. PARTITION BY определяет, какие заказы влияют друг на друга. ORDER BY внутри OVER задаёт последовательность вычисления. Финальный ORDER BY задаёт порядок вывода. Это три отдельных решения.

Сформулируйте требование обычной фразой: для каждого заказа вычислить показатель по заказам того же клиента. Затем решите, нужна ли вся история, её упорядоченное начало или несколько соседних строк. Для слов «последний», «крупнейший» и «предыдущий» уточняйте поведение при совпадениях. Уникальный order_id делает последовательность воспроизводимой, но не должен незаметно менять смысл равенства в бизнес-задаче.

• PostgreSQL: window functions tutorial

Задача 1: выбрать ровно два заказа каждого клиента

Первый запрос сравнивает три правила ранжирования. Для клиента a порядок идентификаторов: 2, 3, 1, 4. Значения position: 1, 2, 3, 4; competition_rank: 1, 1, 3, 4; value_rank: 1, 1, 2, 3. У клиента b получается тот же рисунок рангов для заказов 6, 7, 5, 8. Равные суммы сохраняют одинаковый ранг в RANK и DENSE_RANK: соответствующие окна сортируются только по amount.

Второй запрос отвечает на требование «ровно два» через ROW_NUMBER с дополнительной сортировкой по order_id. Ожидаемый результат: a — заказы 2 и 3 по 200; b — заказы 6 и 7 по 120. Если нужны две крупнейшие различные суммы, рассчитайте DENSE_RANK по amount и оставьте ранг не больше двух: добавятся заказы 1 и 5. При RANK <= 2 они не попадут в ответ, поскольку вторая различная сумма имеет ранг три.

Не добавляйте order_id во все окна автоматически. Он подходит для устойчивой последовательности, но в окнах RANK и DENSE_RANK сделает строки различными по ключу сортировки и уничтожит совпадения сумм. Сначала объясните, какое правило соответствует требованию продукта, и только потом показывайте запрос.

SELECT customer, order_id, amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer ORDER BY amount DESC, order_id
  ) AS position,
  RANK() OVER (
    PARTITION BY customer ORDER BY amount DESC
  ) AS competition_rank,
  DENSE_RANK() OVER (
    PARTITION BY customer ORDER BY amount DESC
  ) AS value_rank
FROM practice_orders
ORDER BY customer, amount DESC, order_id;

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer ORDER BY amount DESC, order_id
  ) AS position
  FROM practice_orders
)
SELECT customer, order_id, amount
FROM numbered
WHERE position <= 2
ORDER BY customer, position;

• PostgreSQL: window function reference
• PostgreSQL: SELECT processing and filtering

Задача 2: накопительный расход и доля в общей сумме

Здесь нужны два разных окна. running_amount следует хронологии заказов клиента, а customer_total охватывает весь раздел. Знаменатель для доли должен оставаться равным 550 или 360 во всех строках соответствующего клиента. Явная рамка ROWS увеличивает накопительную сумму по одному заказу, поэтому два заказа от 2 сентября учитываются последовательно.

Для заказов 1–4 ожидаются накопительные суммы 100, 300, 500, 550; для заказов 5–8 — 80, 200, 320, 360. Доли для a: 18.18, 36.36, 36.36, 9.09 процента; для b: 22.22, 33.33, 33.33, 11.11. Из-за округления итоговые проценты не обязаны складываться ровно в 100. NULLIF защищает знаменатель, если в новой версии задачи допустима история с нулевой итоговой суммой.

Теперь уберите order_id и явную рамку из накопительного окна. При сортировке только по ordered_on стандартная рамка включает строки с той же датой: оба заказа клиента a от 2 сентября покажут 500. Для накопительного отчёта по датам это может подходить, но наша задача требует движения по отдельным заказам. До эксперимента запишите ожидаемую разницу.

SELECT customer, order_id,
  SUM(amount) OVER (
    PARTITION BY customer ORDER BY ordered_on, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount,
  SUM(amount) OVER (PARTITION BY customer) AS customer_total,
  ROUND(100.0 * amount / NULLIF(
    SUM(amount) OVER (PARTITION BY customer), 0
  ), 2) AS percentage
FROM practice_orders
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window expression and frame syntax

Задача 3: сравнить заказ с предыдущим

LAG получает значение из более ранней строки заданной последовательности. Здесь «предыдущий» означает предыдущий заказ этого клиента, а order_id разрешает совпадения дат. Это не означает «вчерашний». У клиента a нет заказов 3 и 4 сентября, но заказ от 5 сентября всё равно сравнивается с заказом 3 от 2 сентября.

Ожидаемые изменения для заказов 1–4: NULL, 100, 0, -150; для заказов 5–8: NULL, 40, 0, -80. NULL в первой строке означает отсутствие истории. Замена на ноль создала бы предположение о предыдущем заказе с нулевой суммой и поменяла смысл показателя. CTE даёт предыдущей сумме имя previous_amount, чтобы вычитание легче читалось и проверялось.

Чтобы показать только уменьшения, добавьте WHERE amount < previous_amount во внешний запрос: останутся заказы 4 и 8. Фильтр малых сумм внутри compared удалил бы строки до поиска предшественника и ответил на другой вопрос. При отладке проверяйте не только разность, но и идентификатор предыдущего заказа.

WITH compared AS (
  SELECT customer, order_id, ordered_on, amount,
    LAG(amount) OVER (
      PARTITION BY customer ORDER BY ordered_on, order_id
    ) AS previous_amount
  FROM practice_orders
)
SELECT customer, order_id, previous_amount,
  amount - previous_amount AS change
FROM compared
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window function reference
• PostgreSQL: SELECT processing and filtering

Задача 4: усреднить текущий и предыдущий заказ

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW включает не более двух строк в упорядоченном разделе клиента. Для первого заказа доступна только его собственная строка. Ожидаемые средние для заказов 1–4: 100.00, 150.00, 200.00, 125.00; для заказов 5–8: 80.00, 100.00, 120.00, 80.00.

Называйте этот показатель средним по двум заказам. Название «среднее за два дня» вводит в заблуждение: последняя пара клиента b охватывает 4 и 7 сентября. Для календарного отчёта нужно определить смысл пропущенных дней; часто перед окном потребуется дневная агрегация или таблица календаря. Также решите, должны ли несколько заказов одного дня иметь отдельный вес или сначала объединяться.

SELECT customer, order_id,
  ROUND(AVG(amount) OVER (
    PARTITION BY customer ORDER BY ordered_on, order_id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ), 2) AS two_order_average
FROM practice_orders
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window expression and frame syntax

Ошибки, из-за которых правдоподобный отчёт оказывается неверным

Успешно выполненный запрос может отвечать не на тот бизнес-вопрос. При изменениях держите под рукой набор из восьми строк и проверяйте каждую строку результата, а не только итоговую сумму. На больших данных начните с истории одного клиента, где есть совпадение и пропущенный день. Такие случаи выявляют предположения, незаметные на обычных строках.

• Фильтр position в WHERE того же SELECT: сначала рассчитайте окно в CTE или подзапросе, затем примените условие снаружи.
• Фильтрация входа без обсуждения охвата: ранжировать сентябрьские заказы и выбрать сентябрьских победителей из всей истории — разные задачи.
• Надежда на финальный ORDER BY для разрешения совпадений окна: устойчивый порядок нужен и внутри соответствующего OVER.
• Соединение заказа с несколькими товарными позициями до суммирования: проверьте количество входных строк и вернитесь к одной строке на заказ, если задача требует именно этого.

• PostgreSQL: SELECT processing and filtering

Упражнение на 20 минут с проверяемым ответом

Не копируя готовые запросы, выведите последний заказ каждого клиента, предыдущую сумму и итог за всю историю. Рассчитайте все три показателя по полной истории, прежде чем выбирать последнюю строку. Для LAG нужна прямая хронология, для маркера последнего заказа — обратная, для общей суммы — раздел без сортировки. Объясните, почему порядок у функций отличается.

В ответе должны быть a: заказ 4, сумма 50, предыдущая сумма 200, итог 550; b: заказ 8, сумма 40, предыдущая сумма 120, итог 360. Если предыдущая сумма у обоих победителей NULL, вероятно, вы выбрали последние строки до расчёта LAG. В конце добавьте ещё один заказ на последнюю дату и сформулируйте правило для совпадения до выполнения запроса.

После практики превратите пропущенное решение в вопрос для самопроверки: когда фильтрация меняет предшественника, почему одинаковым рангам нужен другой ORDER BY или какие строки входят в рамку? Повторите вопрос в RecallDeck, затем снова решите задачу по требованию. Умение назвать функцию — лишь начало умения ею пользоваться.

Коротко

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