Coding Interview Prep · Урок

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

Создавайте текущий итог с помощью SUM OVER и упорядоченной рамки.

«Накопительные суммы с оконными рамками» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Вопрос о накопительном итоге

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

До появления оконных функций кандидаты решали такую задачу с помощью медленного самообъединения или коррелированного подзапроса. Современный ожидаемый ответ — SUM(...) OVER (ORDER BY ...). Знание варианта с оконной рамкой показывает, что Вы понимаете современный SQL, появившийся примерно после 2012 года.

Структура упорядоченной оконной суммы

Накопительный итог — это обычная агрегатная функция, превращённая в оконную. Вы сохраняете SUM(amount), но добавляете предложение OVER с ORDER BY.

Именно ORDER BY внутри OVER делает результат накопительным: он указывает SQL накапливать строки в заданной последовательности. Без ORDER BY функция SUM вычисляла бы сумму всего раздела для каждой строки, вместо того чтобы постепенно увеличиваться.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Почему ORDER BY задаёт рамку

Вот деталь, о которой интервьюеры любят спрашивать: когда Вы добавляете ORDER BY к оконной агрегатной функции, SQL применяет рамку по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Именно эта рамка по умолчанию создаёт накопительный итог: в него входят все строки от начала раздела до текущей строки включительно. Если Вы понимаете это правило, то понимаете, почему накопительная сумма «просто работает».

Явное указание рамки

Рамку можно записать явно. Эти два запроса возвращают одинаковый результат, но явная запись показывает интервьюеру, что Вы понимаете, что происходит внутри.

Для накопительного итога безопаснее всего явно указывать ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поскольку эта запись считает физические строки и позволяет избежать неожиданностей из-за группировки значений в RANGE (это будет рассмотрено в следующем уроке).

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Пример: ежедневные продажи

Представьте четыре дня продаж: понедельник — 100, вторник — 50, среда — 200, четверг — 75. Накопительный итог увеличивается слева направо.

Понедельник: 100
Вторник: 100 + 50 = 150
Среда: 150 + 200 = 350
Четверг: 350 + 75 = 425

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

Сброс для каждой группы с помощью PARTITION BY

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

Полезная мысленная модель: PARTITION BY разделяет строки на независимые группы, а ORDER BY и рамка применяются отдельно внутри каждой группы.

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

Подводный камень одинаковых значений

Если две строки имеют одинаковое значение ORDER BY (например, две продажи совершены в один день), рамка RANGE по умолчанию считает их равными строками и присваивает им одинаковый накопительный итог, включающий обе суммы.

Если нужен строго последовательный прирост по строкам даже при одинаковых значениях, переключитесь на рамку ROWS и добавьте в ORDER BY уникальный дополнительный ключ сортировки, например sale_date, id. Интервьюеры специально добавляют повторяющиеся даты, чтобы проверить, заметите ли Вы эту особенность.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

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

Накопительная логика не ограничивается функцией SUM. Любая агрегатная функция может работать как оконная, поэтому можно построить накопительное количество, накопительное среднее или накопительный максимум.

Накопительное количество заказов — распространённая метрика для панели мониторинга: сколько заказов мы приняли на данный момент по состоянию на каждый день?

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

Старый способ: коррелированный подзапрос

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

Он работает, но имеет сложность O(n в квадрате): для каждой строки таблица просматривается заново. Упомяните это, чтобы показать, что Вы понимаете, почему оконные функции пришли ему на смену.

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

Фильтрация и результат оконной функции

Частый дополнительный вопрос: «Покажите только те дни, когда накопительный итог превысил 1000». Нельзя помещать оконную функцию в WHERE, потому что рамка вычисляется после выполнения WHERE.

Решение — вычислить накопительный итог в CTE или подзапросе, а затем отфильтровать результат внешнего запроса. Это то же правило обёртки, которое применяется ко всем оконным функциям.

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

Что важно сказать на собеседовании

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

SUM OVER (ORDER BY ...) — накопительная форма.
Добавление ORDER BY создаёт рамку по умолчанию от UNBOUNDED PRECEDING до CURRENT ROW.
Используйте PARTITION BY, чтобы начинать подсчёт заново для каждой группы.
Добавьте уникальный дополнительный ключ сортировки и рамку ROWS, чтобы избежать подводного камня с одинаковыми значениями.
Оберните запрос в CTE, чтобы фильтровать результат.

Быстрая проверка

Проверьте, насколько хорошо Вы понимаете рамку по умолчанию.

Итоги: накопительные суммы

Накопительный итог — это упорядоченная оконная агрегатная функция. SUM(amount) OVER (ORDER BY sale_date) накапливает строки от начала раздела до текущей строки благодаря неявной рамке от UNBOUNDED PRECEDING до CURRENT ROW.

Начинайте подсчёт заново для каждой группы с помощью PARTITION BY, добавляйте дополнительный ключ сортировки и рамку ROWS для обработки повторяющихся значений сортировки, а когда нужно фильтровать по накопительному значению, оборачивайте запрос в CTE. Далее мы подробно разберём различие между ROWS и RANGE, на которое этот урок лишь намекнул.

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

Урок «Накопительные суммы с оконными рамками» бесплатный?

Да — полный текст урока «Накопительные суммы с оконными рамками» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Накопительные суммы с оконными рамками»?

Создавайте текущий итог с помощью SUM OVER и упорядоченной рамки. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Накопительные суммы с оконными рамками»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Накопительные суммы с оконными рамками
Рамки ROWS и RANGE
Скользящие средние в скользящем окне
Накопительное распределение и доля от общего

← Назад к Coding Interview Prep