В практике разработки на платформе 1С:Предприятие часто возникает задача анализа оборачиваемости запасов. Одним из ключевых показателей в таких расчетах является количество дней, в течение которых товар фактически присутствовал на складе (то есть его остаток был строго больше нуля). Начинающие разработчики часто решают эту задачу «в лоб» — запускают цикл по дням анализируемого периода и внутри цикла выполняют запрос к виртуальной таблице остатков. Такой подход крайне неэффективен, так как порождает сотни и тысячи обращений к СУБД, что приводит к критическому падению производительности системы.
В рамках данной статьи мы подробно разберем, почему выполнение запросов в цикле является грубейшей ошибкой, проанализируем нерабочий код, а также рассмотрим несколько эффективных способов решения этой задачи с помощью одного-единственного запроса.
Рассмотрим пример кода, который часто можно встретить у начинающих программистов (аналогичный код привел один из участников обсуждения в Сообщении 12):
ДнейВыборки = Окр((Объект.ДатаПродажОкончание - Объект.ДатаПродажНачало) / (60 * 60 * 24), 0);
Для Каждого Стр Из Объект.Товары Цикл
ДнейНаСкладе = 0;
Для СчДней = 0 По ДнейВыборки Цикл
ЗапросОстатокСклада = Новый Запрос;
ЗапросОстатокСклада.Текст =
"ВЫБРАТЬ
| ОстаткиНоменклатурыОстатки.Номенклатура,
| ОстаткиНоменклатурыОстатки.КоличествоОстаток
|ИЗ
| РегистрНакопления.ОстаткиНоменклатуры.Остатки(&Период, ) КАК ОстаткиНоменклатурыОстатки
|ГДЕ
| ОстаткиНоменклатурыОстатки.Номенклатура = &Номенклатура
| И ОстаткиНоменклатурыОстатки.Склад = &Склад";
ЗапросОстатокСклада.УстановитьПараметр("Склад", Стр.Склад);
ЗапросОстатокСклада.УстановитьПараметр("Номенклатура", Стр.Номенклатура);
ЗапросОстатокСклада.УстановитьПараметр("Период", Объект.ДатаПродажНачало + СчДней * 60 * 60 * 24);
ИтогЗапросОстаток = ЗапросОстатокСклада.Выполнить().Выгрузить();
Если ИтогЗапросОстаток.Количество() > 0 Тогда
Если ИтогЗапросОстаток[0].КоличествоОстаток > 0 Тогда
ДнейНаСкладе = ДнейНаСкладе + 1;
КонецЕсли;
КонецЕсли;
КонецЦикла;
Стр.ДнейНаСкладе = ДнейНаСкладе;
КонецЦикла;
Давайте выясним основные причины, почему данный код является нерабочим или крайне неоптимальным:
Новый Запрос и компиляция его текста на каждом шаге цикла расходуют колоссальное количество ресурсов процессора и оперативной памяти.Остатки просто не вернет строку для этой номенклатуры. Метод Количество() вернет 0, и день не будет засчитан, даже если товар физически находился на складе, но по нему не было движений в этот конкретный день.СчДней * 60 * 60 * 24 не учитывает переходы на летнее/зимнее время (в некоторых старых системах) или может смещать точку времени на границу суток, из-за чего остатки за день могут быть получены некорректно.Для того чтобы решить эту задачу правильно, быстро и без использования циклов в коде 1С, мы должны перенести всю логику вычислений на сторону СУБД с помощью языка запросов.
Самый простой и логичный способ решить задачу одним запросом — использовать виртуальную таблицу РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты. Главная хитрость здесь заключается в правильной настройке периодичности и параметров дополнения периодов.
По умолчанию виртуальная таблица возвращает данные только за те дни, в которых были совершены реальные движения (приход или расход). Если товар лежал без движения неделю, то без специальных настроек эта неделя просто «выпадет» из результата запроса. Чтобы этого избежать, мы должны использовать параметр дополнения периодов.
Рассмотрим пример запроса, который решает эту задачу:
ВЫБРАТЬ
ОстаткиИОбороты.Номенклатура КАК Номенклатура,
ОстаткиИОбороты.Склад КАК Склад,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(ОстаткиИОбороты.Период, ДЕНЬ)) КАК ДнейВНаличии
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
День,
ДвиженияИГраницыПериода,
Номенклатура В (&МассивНоменклатуры)
И Склад = &Склад
) КАК ОстаткиИОбороты
ГДЕ
ОстаткиИОбороты.КоличествоКонечныйОстаток > 0
СГРУППИРОВАТЬ ПО
ОстаткиИОбороты.Номенклатура,
ОстаткиИОбороты.Склад
Разберем по шагам, как работает этот запрос:
ОстаткиИОбороты. Третьим параметром мы передаем периодичность День. Это заставляет систему разбить весь период на посуточные интервалы.ДвиженияИГраницыПериода. Это критически важно! Данная настройка указывает платформе 1С автоматически «дорисовать» (дополнить) строки для тех дней, в которые по товару не было никаких движений, но остаток на складе существовал.ГДЕ мы накладываем условие ОстаткиИОбороты.КоличествоКонечныйОстаток > 0. Таким образом, в выборку попадут только те дни, на конец которых товар реально числился на складе.ВЫБРАТЬ мы агрегируем результат с помощью функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...), подсчитывая уникальное количество дней, удовлетворяющих нашему условию.Хотя первый метод прост в реализации, при работе с огромными базами данных (сотни тысяч товаров, анализируемый период в несколько лет) дополнение периодов по каждому дню может сильно замедлить выполнение запроса. В СУБД передается огромный массив строк, который затем группируется.
В таких случаях применяется более производительный метод — расчет интервалов отсутствия товара. Мы определяем периоды, когда остаток товара был равен нулю, вычисляем их общую длительность в днях с помощью функции РАЗНОСТЬДАТ, а затем вычитаем полученный результат из общего количества дней в анализируемом периоде.
Проанализируем алгоритм реализации этого метода:
РАЗНОСТЬДАТ(ДатаНачалаОтсутствия, ДатаОкончанияОтсутствия, ДЕНЬ).Этот метод работает значительно быстрее на базах данных с высокой плотностью записей, поскольку СУБД оперирует только реальными точками изменения остатков, избегая генерации миллионов виртуальных строк для «пустых» дней дополнения.
Что делать, если по каким-то причинам виртуальная таблица с дополнением периодов не решает задачу (например, требуется связать остатки со сложным производственным календарем компании, исключив праздничные и выходные дни, когда продажи не велись)?
Для этого в запросе динамически генерируют непрерывную шкалу дат за весь анализируемый период, а затем соединяют её левым соединением с остатками товаров. Сгенерировать шкалу дат без использования реальных таблиц БД можно с помощью декартова произведения временных таблиц (метод разрядов).
Посмотрим на пример реализации генератора дат в запросе:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ Цифры
ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, т1.Цифра + т2.Цифра * 10 + т3.Цифра * 100) КАК ПериодДень
ПОМЕСТИТЬ СеткаДат
ИЗ
Цифры КАК т1
CROSS JOIN Цифры КАК т2
CROSS JOIN Цифры КАК т3
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, т1.Цифра + t2.Цифра * 10 + т3.Цифра * 100) <= &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СеткаДат.ПериодДень КАК ПериодДень,
Товары.Номенклатура КАК Номенклатура,
ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
ПОМЕСТИТЬ ОстаткиПоВсемДатам
ИЗ
СеткаДат КАК СеткаДат
CROSS JOIN (ВЫБРАТЬ РАЗЛИЧНЫЕ Номенклатура ИЗ РегистрНакопления.ТоварыНаСкладах ГДЕ Склад = &Склад) КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&НачалоПериода, Склад = &Склад) КАК Остатки
ПО СеткаДат.ПериодДень = НАЧАЛОПЕРИОДА(Остатки.Период, ДЕНЬ)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ОстаткиПоВсемДатам.Номенклатура,
КОЛИЧЕСТВО(ВЫБОР КОГДА ОстаткиПоВсемДатам.Остаток > 0 ТОГДА 1 КОНЕЦ) КАК ДнейВНаличии
ИЗ
ОстаткиПоВсемДатам КАК ОстаткиПоВсемДатам
СГРУППИРОВАТЬ ПО
ОстаткиПоВсемДатам.Номенклатура
Этот метод гарантирует абсолютную точность и позволяет накладывать любые дополнительные календари (например, соединяя таблицу СеткаДат с производственным календарем компании перед расчетом остатков).
При проектировании алгоритмов расчета дней в наличии обязательно нужно учитывать специфику движений товаров внутри одного дня (как справедливо заметил автор Сообщения 10):
КоличествоКонечныйОстаток > 0), но и факт прихода или оборота за день: КоличествоКонечныйОстаток > 0 ИЛИ КоличествоПриход > 0.Остаток > 0.Использование предложенных подходов позволит вам полностью отказаться от тяжелых циклов в кодовой базе 1С, переложить рутинные вычисления на оптимизированный движок СУБД и значительно ускорить работу ваших отчетов и обработок.