Как рассчитать количество дней товара на остатке в 1С и оптимизировать запрос?

← На главную

В процессе анализа эффективности складских запасов и расчета оборачиваемости товаров перед разработчиками и аналитиками 1С часто возникает классическая задача: определить точное количество дней, в течение которых конкретный товар фактически находился на складе (то есть его остаток был строго больше нуля) за выбранный период времени.

На первый взгляд задача кажется тривиальной. Однако при попытке решить её «в лоб» через виртуальные таблицы остатков и оборотов с посуточной детализацией СУБД начинает испытывать колоссальные нагрузки. В этой статье мы подробно разберем исходный вариант запроса, предложенный на одном из профессиональных форумов, проанализируем причины его крайне медленной работы (когда выполнение на относительно небольшой базе занимает 15 и более секунд), а также рассмотрим современные и эффективные альтернативные методы решения этой задачи.

Разбор исходного запроса: как работает базовый алгоритм

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

Посмотрим на текст исходного запроса:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ПартииТоваровНаСкладахОстаткиИОбороты.Номенклатура КАК Номенклатура,
    ПартииТоваровНаСкладахОстаткиИОбороты.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    МАКСИМУМ(ВЫБОР
            КОГДА ПартииТоваровНаСкладахОстаткиИОбороты.Период = НАЧАЛОПЕРИОДА(&КонецПериода, ДЕНЬ)
                ТОГДА ВЫБОР
                        КОГДА ПартииТоваровНаСкладахОстаткиИОбороты.КоличествоКонечныйОстатокПредыдущий > 0
                            ТОГДА ВЫБОР
                                    КОГДА ПартииТоваровНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток <= 0
                                        ТОГДА РАЗНОСТЬДАТ(ПартииТоваровНаСкладахОстаткиИОбороты.ПредыдущийПериод, ПартииТоваровНаСкладахОстаткиИОбороты.Период, ДЕНЬ)
                                    ИНАЧЕ РАЗНОСТЬДАТ(ПартииТоваровНаСкладахОстаткиИОбороты.ПредыдущийПериод, &КонецПериода, ДЕНЬ) + 1
                                КОНЕЦ
                        ИНАЧЕ ВЫБОР
                                КОГДА ПартииТоваровНаСкладахОстаткиИОбороты.КоличествоКонечныйОстаток > 0
                                    ТОГДА РАЗНОСТЬДАТ(ПартииТоваровНаСкладахОстаткиИОбороты.Период, &КонецПериода, ДЕНЬ) + 1
                                ИНАЧЕ 0
                            КОНЕЦ
                    КОНЕЦ
            ИНАЧЕ ВЫБОР
                    КОГДА ПартииТоваровНаСкладахОстаткиИОбороты.КоличествоКонечныйОстатокПредыдущий > 0
                        ТОГДА РАЗНОСТЬДАТ(ПартииТоваровНаСкладахОстаткиИОбороты.ПредыдущийПериод, ПартииТоваровНаСкладахОстаткиИОбороты.Период, ДЕНЬ)
                    ИНАЧЕ 0
                КОНЕЦ
        КОНЕЦ) КАК КоличествоДнейНаСкладе
ИЗ
    (ВЫБРАТЬ
        ТаблицаПредыдущиеПериоды.Номенклатура КАК Номенклатура,
        ТаблицаПредыдущиеПериоды.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
        ТаблицаПредыдущиеПериоды.Склад КАК Склад,
        ТаблицаПредыдущиеПериоды.Период КАК Период,
        ТаблицаПредыдущиеПериоды.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
        ТаблицаПредыдущиеПериоды.ПредыдущийПериод КАК ПредыдущийПериод,
        ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстатокПредыдущий
    ИЗ
        (ВЫБРАТЬ
            ПартииТоваровНаСкладахОстаткиИОбороты1.Номенклатура КАК Номенклатура,
            ПартииТоваровНаСкладахОстаткиИОбороты1.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
            ПартииТоваровНаСкладахОстаткиИОбороты1.Склад КАК Склад,
            ПартииТоваровНаСкладахОстаткиИОбороты1.Период КАК Период,
            ПартииТоваровНаСкладахОстаткиИОбороты1.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
            МАКСИМУМ(ПартииТоваровНаСкладахОстаткиИОбороты2.Период) КАК ПредыдущийПериод
        ИЗ
            РегистрНакопления.ПартииТоваровНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ДокументОприходования.Организация = &Организация) КАК ПартииТоваровНаСкладахОстаткиИОбороты1
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ДокументОприходования.Организация = &Организация) КАК ПартииТоваровНаСкладахОстаткиИОбороты2
            ПО (ИСТИНА)
                И ПартииТоваровНаСкладахОстаткиИОбороты1.Номенклатура = ПартииТоваровНаСкладахОстаткиИОбороты2.Номенклатура
                И ПартииТоваровНаСкладахОстаткиИОбороты1.ХарактеристикаНоменклатуры = ПартииТоваровНаСкладахОстаткиИОбороты2.ХарактеристикаНоменклатуры
                И ПартииТоваровНаСкладахОстаткиИОбороты1.Склад = ПартииТоваровНаСкладахОстаткиИОбороты2.Склад
                И ПартииТоваровНаСкладахОстаткиИОбороты1.Период > ПартииТоваровНаСкладахОстаткиИОбороты2.Период
        
        СГРУППИРОВАТЬ ПО
            ПартииТоваровНаСкладахОстаткиИОбороты1.Номенклатура,
            ПартииТоваровНаСкладахОстаткиИОбороты1.ХарактеристикаНоменклатуры,
            ПартииТоваровНаСкладахОстаткиИОбороты1.Склад,
            ПартииТоваровНаСкладахОстаткиИОбороты1.Период,
            ПартииТоваровНаСкладахОстаткиИОбороты1.КоличествоКонечныйОстаток) КАК ТаблицаПредыдущиеПериоды
            ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПартииТоваровНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ДокументОприходования.Организация = &Организация) КАК ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие
            ПО (ИСТИНА)
                И ТаблицаПредыдущиеПериоды.Номенклатура = ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие.Номенклатура
                И ТаблицаПредыдущиеПериоды.ХарактеристикаНоменклатуры = ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие.ХарактеристикаНоменклатуры
                И ТаблицаПредыдущиеПериоды.Склад = ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие.Склад
                И ТаблицаПредыдущиеПериоды.ПредыдущийПериод = ПартииТоваровНаСкладахОстаткиИОборотыПредыдущие.Период) КАК ПартииТоваровНаСкладахОстаткиИОбороты
 СГРУППИРОВАТЬ ПО
    ПартииТоваровНаСкладахОстаткиИОбороты.Номенклатура,
    ПартииТоваровНаСкладахОстаткиИОбороты.ХарактеристикаНоменклатуры

Давайте разберем по шагам логику этого запроса. Вложенная таблица ТаблицаПредыдущиеПериоды строится путем левого соединения виртуальной таблицы остатков и оборотов ПартииТоваровНаСкладах.ОстаткиИОбороты самой с собой. Условием соединения выступает неравенство дат: Период1 > Период2. Сгруппировав результат и выбрав максимум по второму периоду, автор находит дату предыдущего движения товара. Затем к полученной таблице повторно присоединяется та же таблица остатков и оборотов, чтобы вытащить значение остатка на найденную предыдущую дату.

На верхнем уровне запроса через каскад условий ВЫБОР...КОГДА вычисляется разница в днях между текущим периодом изменения остатка и предыдущим, если остаток в этот промежуток времени был положительным. Если текущий период является последним днем анализируемого интервала, происходит дополнительный расчет до конечной границы периода.

Почему этот запрос работает медленно? Анализ узких мест

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

Множественное обращение к тяжелой виртуальной таблице: Запрос обращается к виртуальной таблице ОстаткиИОбороты с периодичностью «День» трижды. Виртуальные таблицы сами по себе являются расчетными и создают нагрузку, а их многократный вызов удваивает и утраивает дисковые операции СУБД.
Соединение по условию неравенства (>): СУБД крайне неэффективно обрабатывают левые соединения, в которых условие связи содержит операторы сравнения, отличные от равенства (в данном случае Период1 > Период2). Это приводит к возникновению декартова произведения (Cartesian Product) для групп записей по номенклатуре и складу. Временные таблицы разрастаются до гигантских размеров, перегружая оперативную память сервера.
Отсутствие индексации промежуточных данных: Поскольку соединения происходят непосредственно между подзапросами «на лету», СУБД не может построить эффективный план выполнения и использовать индексы для связи таблиц.

Методы оптимизации: как ускорить расчет дней на остатке

Проанализируем современные и эффективные подходы, которые позволяют решить данную аналитическую задачу без ущерба для производительности системы.

Вариант 1. Метод «от обратного»: расчет интервалов отсутствия товара

Один из самых неочевидных, но при этом наиболее эффективных способов оптимизации — расчет времени, когда товара не было на складе, вместо подсчета дней его наличия.

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

Алгоритм работы метода следующий:

С помощью простого линейного запроса мы выбираем только те периоды, в которых конечный остаток товара стал равен нулю (произошло полное списание).
Находим дату следующего поступления этого товара (когда остаток снова стал больше нуля). Для этого можно использовать временные таблицы с индексацией по полям Номенклатура, Склад и Период.
Вычисляем разницу между датой обнуления и датой нового поступления — это и есть «интервал отсутствия» товара в днях.
Суммируем все интервалы отсутствия по каждой позиции номенклатуры.
Вычитаем полученную сумму из общего количества календарных дней анализируемого периода. Разница и будет являться искомым количеством дней нахождения товара на остатке.

Такой подход избавляет СУБД от необходимости обрабатывать миллионы строк с положительными остатками и сводит задачу к анализу единичных случаев «вымывания» товара, что ускоряет выполнение запроса в десятки раз.

Вариант 2. Использование производственного календаря для точного расчета

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

Для решения этой задачи мы можем использовать связь с производственным календарем. Разберем по шагам, как построить такой запрос:

Сначала мы формируем временную таблицу со всеми рабочими днями из регистра сведений РегламентированныйПроизводственныйКалендарь за анализируемый период.
Получаем таблицу остатков товаров на даты изменения остатков.
Соединяем таблицу календаря с таблицей движений по условию «дата календаря больше или равна дате движения». Группируя по дате календаря, мы заполняем каждый рабочий день актуальным остатком товара на этот день (метод разворачивания периодов).
На последнем шаге мы просто подсчитываем количество дней в получившейся таблице, где остаток был строго больше нуля.

Этот способ гарантирует абсолютную точность расчета, исключает нерабочие дни и легко обрабатывает ситуации, когда по товару не было никаких движений в течение длительного времени, но начальный остаток был положительным.

Вариант 3. Использование конструкции ИТОГИ ПЕРИОДАМИ в языке запросов

Если расчет не является частью сложного пакетного запроса, а подготавливается для вывода в отчет, платформа 1С предоставляет мощный встроенный механизм — расчет итогов по периодам.

Конструкция ИТОГИ ... ПЕРИОДАМИ(ДЕНЬ, &НачалоПериода, &КонецПериода) заставляет платформу автоматически сгенерировать виртуальные записи на каждый день для каждой номенклатуры, даже если в этот день по ней не регистрировалось никаких хозяйственных операций. Пример построения такого запроса выглядит следующим образом:


ВЫБРАТЬ
    ПартииТоваровОстатки.Номенклатура КАК Номенклатура,
    ПартииТоваровОстатки.Склад КАК Склад,
    ПартииТоваровОстатки.Период КАК Период,
    ПартииТоваровОстатки.КоличествоКонечныйОстаток КАК Остаток
ИЗ
    РегистрНакопления.ПартииТоваровНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК ПартииТоваровОстатки
ИТОГИ
    СУММА(Остаток)
ПО
    Номенклатура,
    Склад,
    Период ПЕРИОДАМИ(ДЕНЬ, &НачалоПериода, &КонецПериода)

При обходе результатов такого запроса в коде на встроенном языке (через выборку с типом обхода ОбходРезультатаЗапроса.ПоГруппировкам) мы можем просто в цикле проверять значение остатка на каждый день и инкрементировать счетчик дней, если остаток больше нуля. Этот метод работает крайне быстро, поскольку всю работу по интерполяции остатков на пустые даты берет на себя платформа 1С на уровне ядра.

Вариант 4. Реализация через Систему компоновки данных (СКД)

В СКД данная задача решается наиболее изящно и без написания громоздкого кода. Для этого мы создаем два набора данных:

Набор данных - запрос (Календарь): Быстрый запрос, выбирающий все последовательные даты периода (например, из регистра курсов валют или производственного календаря).
Набор данных - запрос (Остатки): Запрос к таблице остатков и оборотов, возвращающий только реальные движения.

Вкладка «Связи наборов данных» позволяет связать эти наборы по полям номенклатуры и периода. При этом для связи по периоду мы указываем условие связи и ставим флаг «Начальное значение», что заставляет СКД автоматически транслировать остатки на все промежуточные дни, где движений не было. В пользовательских полях отчета останется лишь прописать простую формулу: Выбор Когда Остаток > 0 Тогда 1 Иначе 0 Конец и рассчитать ресурс как сумму по этому полю.

Вариант 5. Взвешенный расчет наличия (учет «дробных» дней)

При анализе доступности товара в розничной торговле стандартный посуточный метод («остаток на конец дня > 0») часто приводит к искажению данных. Рассмотрим ситуацию: товар привезли утром, а к 12:00 полностью распродали. Остаток на конец дня равен нулю. При классическом подходе этот день запишется как «0 дней на складе», хотя фактически полдня товар продавался и приносил прибыль.

Для решения этой проблемы применяется взвешенный подход к расчету коэффициента наличия:

Если остаток на начало дня > 0 и на конец дня > 0 — день засчитывается как 1.0 (товар был доступен всё время).
Если на начало дня остаток был > 0, а на конец дня стал равен 0 (или наоборот) — день засчитывается как 0.5 (товар присутствовал на полках часть дня).
Если и на начало, и на конец дня остаток равен 0 — день засчитывается как 0.0.

Этот расчет не требует сложных соединений «период с периодом» и легко реализуется простым линейным запросом по таблице ОстаткиИОбороты с периодичностью «День», оценивающим поля КоличествоНачальныйОстаток и КоличествоКонечныйОстаток:


ВЫБРАТЬ
    Остатки.Номенклатура КАК Номенклатура,
    СУММА(ВЫБОР
            КОГДА Остатки.КоличествоНачальныйОстаток > 0 И Остатки.КоличествоКонечныйОстаток > 0
                ТОГДА 1
            КОГДА Остатки.КоличествоНачальныйОстаток > 0 ИЛИ Остатки.КоличествоКонечныйОстаток > 0
                ТОГДА 0.5
            ИНАЧЕ 0
        КОНЕЦ) КАК КоличествоДнейНаличия
ИЗ
    РегистрНакопления.ПартииТоваровНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , ) КАК Остатки
ГРУППИРОВАТЬ ПО
    Остатки.Номенклатура

Этот запрос выполняется мгновенно даже на огромных объемах данных, так как не содержит тяжелых соединений, а точность получаемой аналитики значительно превосходит стандартные методы расчета.

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