В практике разработки на платформе «1С:Предприятие» (особенно в крупных ERP-системах, таких как ERP 2.4, Комплексная автоматизация или Управление торговлей) часто возникает задача оценки стоимости последних поступивших партий товара. Классический пример: необходимо выяснить, по какой стоимости были закуплены последние 100 кг определенной номенклатуры (например, картофеля). Трудность заключается в том, что товар поставляется в разное время, разными партиями, и эти 100 кг могут «размазаться» по нескольким документам поступления.
В этой статье мы подробно проанализируем различные подходы к решению этой задачи, разберем, почему классическое решение «в лоб» через один запрос может обрушить производительность базы данных, и рассмотрим наиболее оптимальные алгоритмы с точки зрения производительности СУБД.
---
Первое решение, которое часто приходит в голову начинающим разработчикам, — это использование механизма нарастающего итога внутри одного запроса. Нам предлагают связать таблицу приходов саму с собой по условию неравенства периодов (чтобы для каждой строки рассчитать сумму всех предыдущих поступлений) и отобрать строки, где накопленная сумма еще не превысила заданный порог в 100 кг.
Давайте рассмотрим типичный пример такого запроса:
ВЫБРАТЬ ПЕРВЫЕ 1
ОстаткиТоваров.Период КАК Период,
ОстаткиТоваров.Номенклатура КАК Номенклатура,
СУММА(ОстаткиТоваров2.Количество) КАК НакопленноеКоличество
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров2
ПО ОстаткиТоваров.Период <= ОстаткиТоваров2.Период
И ОстаткиТоваров2.Активность
И ОстаткиТоваров2.Количество > 0
И ОстаткиТоваров2.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
И ОстаткиТоваров2.Номенклатура = &Номенклатура
ГДЕ
ОстаткиТоваров.Активность
И ОстаткиТоваров.Количество > 0
И ОстаткиТоваров.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
И ОстаткиТоваров.Номенклатура = &Номенклатура
СГРУППИРОВАТЬ ПО
ОстаткиТоваров.Период,
ОстаткиТоваров.Номенклатура
ИМЕЮЩИЕ
СУММА(ОстаткиТоваров2.Количество) >= &НужныйОбъем
УПОРЯДОЧИТЬ ПО
Период УБЫВ
В чем критическая опасность этого подхода?
Язык запросов 1С (SDBL) преобразует этот код в SQL-запрос. СУБД будет вынуждена выполнять нелинейное соединение таблиц по условию неравенства ОстаткиТоваров.Период <= ОстаткиТоваров2.Период. Это приводит к квадратичной сложности алгоритма — O(N²).
Если по выбранной номенклатуре в базе данных содержится 100 записей приходов, СУБД обработает порядка 5 000 строк. Но если перед нами крупное предприятие, где по позиции ведется активное движение и накоплено 10 000 записей прихода, количество сопоставляемых строк вырастет до 50 000 000! В результате СУБД либо зависнет по таймауту, либо полностью заблокирует таблицы регистра, парализовав работу остальных пользователей.
Кроме того, в платформе 1С на уровне СУБД не поддерживаются нативные оконные функции (такие как SUM(...) OVER(...)), которые в стандартном SQL позволяют решать подобные задачи эффективно. Поэтому использовать чистый запрос с соединением «самого с собой» на реальных базах данных ERP категорически не рекомендуется.
---
Самым надежным, быстрым и отказоустойчивым способом решения этой задачи в 1С является комбинация простого, быстрого запроса к СУБД и последующей постобработки данных на стороне сервера 1С в цикле. Мы выбираем только нужные записи, упорядоченные с конца (метод LIFO), а затем в коде накапливаем сумму до тех пор, пока не наберем 100 кг.
Разберем этот алгоритм по шагам:
Посмотрим на пример программной реализации:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Закупки.Период КАК Период,
| Закупки.Регистратор КАК ДокументПрихода,
| Закупки.Количество КАК Количество,
| Закупки.Сумма КАК Сумма
|ИЗ
| РегистрНакопления.Закупки КАК Закупки
|ГДЕ
| Закупки.Номенклатура = &Номенклатура
| И Закупки.Активность
| И Закупки.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияНакопления.Приход)
|УПОРЯДОЧИТЬ ПО
| Период УБЫВ,
| ДокументПрихода УБЫВ";
Запрос.УстановитьПараметр("Номенклатура", ВыбраннаяНоменклатура);
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
НужныйОбъем = 100; // Нам нужно набрать последние 100 кг
НакопленныйОбъем = 0;
ОбщаяСтоимость = 0;
Пока Выборка.Следующий() Цикл
ОсталосьНабрать = НужныйОбъем - НакопленныйОбъем;
// Если в текущей партии больше товара, чем нам осталось набрать
Если Выборка.Количество >= ОсталосьНабрать Тогда
ЦенаЗаЕдиницу = Выборка.Сумма / Выборка.Количество;
ОбщаяСтоимость = ОбщаяСтоимость + (ОсталосьНабрать * ЦенаЗаЕдиницу);
НакопленныйОбъем = НакопленныйОбъем + ОсталосьНабрать;
Прервать; // Цель достигнута, выходим из цикла
Иначе
// Забираем всю партию целиком
ОбщаяСтоимость = ОбщаяСтоимость + Выборка.Сумма;
НакопленныйОбъем = НакопленныйОбъем + Выборка.Количество;
КонецЕсли;
КонецЦикла;
Если НакопленныйОбъем < НужныйОбъем Тогда
Сообщить("Внимание! Всего удалось собрать только " + НакопленныйОбъем + " кг товара.");
КонецЕсли;
Сообщить("Стоимость последних " + НакопленныйОбъем + " кг составляет: " + ОбщаяСтоимость);
Преимущества гибридного метода:
Номенклатура и Период, что обеспечивает моментальное выполнение первичной выборки.---
Если товаров много (целый список), и выполнять гибридный метод в цикле для каждой позиции не хочется, можно использовать итерационный подход выполнения запросов. Мы стремимся не читать всю историю движений по базе, а ограничить выборку разумными временными рамками.
Проанализируем стратегию последовательных приближений:
Такой каскадный метод позволяет минимизировать объем данных, пересылаемых между СУБД и сервером 1С, сохраняя высокую скорость работы системы даже при расчете по большому списку номенклатуры.
---
Если результатом расчета должен стать аналитический отчет, то тяжелые вычисления на стороне СУБД можно полностью обойти с помощью возможностей СКД. Система компоновки данных умеет рассчитывать нарастающий итог в оперативной памяти сервера приложений 1С чрезвычайно эффективно.
Рассмотрим порядок настройки СКД для нашей задачи:
ВычислитьВыражение("Сумма(Количество)", "Номенклатура", , "Первая", "Текущая").
Эта функция последовательно суммирует количество по группировке «Номенклатура» сверху вниз.
ПриКомпоновкеРезультата мы обходим полученный процессор вывода и прекращаем вывод строк по конкретной номенклатуре, как только ресурс нарастающего итога превысил значение 100.---
Если логика вашей задачи требует выполнения расчетов исключительно внутри пакетного запроса (например, когда результат нарастающего итога должен сразу передаваться в последующие пакеты сложного расчета), обязательно придерживайтесь следующих правил оптимизации:
ВТ_Приходы для последующего соединения, обязательно укажите в блоке индексации поля Номенклатура и Период. Без индексов соединение по неравенству превратится в кошмар для планировщика SQL Server.Для большинства практических задач в ERP-системах на платформе 1С наиболее эффективным, простым в поддержке и производительным является Гибридный метод (Решение 1). Он гарантирует предсказуемое время работы алгоритма и исключает появление избыточных блокировок в базе данных. Использование сложных конструкций нарастающего итога непосредственно в языке запросов оправдано лишь в редких специфических задачах и требует от разработчика высочайшей квалификации в оптимизации SQL-запросов.