Средний остаток в SQL: почему два снимка нельзя просто усреднить

На начало понедельника на складе сто единиц товара, со вторника остаётся десять. Следующая выгрузка сделана в пятницу. Среднее двух снимков равно 55, хотя почти весь период запас держался около десяти. Если использовать 55 в расчёте оборачиваемости или потребности в площади, частота выгрузок начнёт влиять на бизнес-метрику.

У значения есть срок действия

Снимок описывает состояние с момента записи до следующего изменения. Для среднего за период нужно умножить каждый остаток на длительность его действия, сложить произведения и разделить на длительность всего периода. Получаются условные «штуко-дни», которые после деления снова становятся штуками. Два снимка не обязаны иметь одинаковый вес.

Рассмотрим окно с 1 октября 00:00 до 5 октября 00:00. Оно длится четыре дня, правая граница не включается. Сто единиц лежит один день, десять — три дня. Среднее составляет (100 × 1 + 10 × 3) / 4 = 32,5. Это средний фактически занятый запас за выбранное время, а не типичное значение снимка.

Превратить снимки в интервалы

LEAD берёт время следующей записи. Для последнего снимка задаём конец отчётного окна. Разность julianday даёт длительность в днях, включая доли суток. В примере каждая дата совпадает с полуночью и арифметика получается прозрачной. Запрос также возвращает covered_days: без проверки покрытия красивое среднее может быть рассчитано только по части окна.

Пример выполняется в SQLite; функции и синтаксис ниже относятся к этому диалекту.

WITH snapshots(ts,qty) AS (VALUES
 ('2026-10-01 00:00:00',100),('2026-10-02 00:00:00',10)),
intervals AS (
 SELECT ts,qty,LEAD(ts,1,'2026-10-05 00:00:00') OVER(ORDER BY ts) AS next_ts
 FROM snapshots
)
SELECT SUM(qty*(julianday(next_ts)-julianday(ts)))/4.0 AS avg_qty,
 SUM(julianday(next_ts)-julianday(ts)) AS covered_days
FROM intervals;

Ответ — средний остаток 32,5 и покрытие 4 дня. Значение 55 означало бы, что каждый снимок действовал одинаковое время. Если добавить ещё сто одинаковых выгрузок во вторник, временное среднее останется прежним. Обычное AVG строк приблизится к десяти. Это хороший тест на случайное влияние частоты сбора данных.

Граница начала требует отдельного внимания

В рабочей истории первый снимок обычно сделан раньше начала отчёта. Нужно найти последнее известное состояние на начало окна и перенести его значение в стартовую точку. Затем добавить изменения внутри окна и обрезать последний интервал концом. Если просто отфильтровать историю датами месяца, первый интервал пропадёт: система будет считать, будто до первой выгрузки товара не существовало.

Если состояния до начала нет, неизвестный интервал нельзя безусловно заполнить нулём. Покажите долю покрытого времени и определите правило допуска отчёта. Для склада без остатков ноль допустим, когда это подтверждённое состояние. Для оборванного интеграционного канала он означает потерю наблюдения и требует восстановления данных.

Какие записи можно считать состоянием

Снимки и движения — разные входы. Если источник содержит приходы и расходы, сначала восстановите накопительный остаток с начальным балансом. Взвешивать непосредственно величины движений бессмысленно: поступление двадцати единиц не означает, что на складе двадцать. Аналогично для денежных остатков сначала нужен баланс, а затем среднее по времени.

На одну товарную позицию, склад и время должна приходиться одна действующая запись. Две корректировки с одинаковым временем требуют версии или номера последовательности. Просто добавить id в ORDER BY недостаточно, если более ранняя версия должна быть полностью заменена: её следует исключить до построения интервалов. Для нескольких складов LEAD разделяют через PARTITION BY.

При почасовых данных согласуйте часовой пояс. Средний запас за календарный день и за ровно 24 часа могут отличаться в зоне с переводом часов. В денежном отчёте также важно, какой баланс нужен: бухгалтерский, доступный или с учётом резервов. Временное взвешивание исправляет веса, но не выбирает правильный смысл исходного остатка.

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

Следующий шаг

Дальнейшая работа — собрать начальное состояние из истории и рассчитать интервалы для многих товаров. В платном «Продвинутом SQL» есть исторические значения, календарные ряды и оконные функции. Практика на 109 проверяемых задачах помогает освоить переход от отдельных снимков к воспроизводимой витрине. Откройте программу курса.