При разработке отчетов в конфигурациях на платформе 1С:Предприятие (особенно в крупных ERP-системах) программисты часто сталкиваются с задачей расчета стоимости остатков или оборотов ТМЦ. Классический сценарий: необходимо взять количество товара из регистра накопления, получить актуальную цену из регистра сведений на определенную дату и перемножить их для получения суммы.
Сложности начинаются, когда в базе ведется учет по дополнительным разрезам — характеристикам или, как в нашем случае, вариантам исполнения номенклатуры. Логика бизнеса обычно требует следующего приоритета выбора цен:
Попытки решить эту задачу «в лоб» часто приводят к двум критическим проблемам: получению неверных (задвоенных) сумм в отчетах или к полному зависанию системы с ошибкой «Недостаточно памяти». Проанализируем причины этих ошибок и разберем наиболее эффективные и производительные способы реализации такой логики.
Выясним, почему стандартные подходы не работают и как построить оптимальный запрос.
Рассмотрим типичную ошибку, которую совершают многие разработчики при попытке связать таблицы остатков и цен. Они используют оператор ИЛИ в условии левого соединения:
ПО СтоимостьТМЦОстаткиИОбороты.Номенклатура = ЦеныНоменклатурыСрезПоследних.Номенклатура
И (СтоимостьТМЦОстаткиИОбороты.ВариантИсполнения = ЦеныНоменклатурыСрезПоследних.ВариантИсполнения
ИЛИ ЦеныНоменклатурыСрезПоследних.ВариантИсполнения = ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка))
Проанализируем две ключевые проблемы этого кода:
1. Катастрофическое падение производительности СУБД (Зависание запроса)
Когда оптимизатор СУБД (будь то MS SQL Server или PostgreSQL) видит оператор ИЛИ в секции соединения ПО (ON), он чаще всего не может построить эффективный план выполнения запроса с использованием индексов. Вместо быстрого поиска по индексу (Index Seek) СУБД переходит к полному сканированию таблиц (Table Scan) или начинает использовать алгоритм вложенных циклов (Nested Loops) для каждой строки. На реальной базе данных с сотнями тысяч записей в регистрах СтоимостьТМЦ и ЦеныНоменклатуры это приводит к колоссальному потреблению ресурсов процессора и оперативной памяти сервера приложений. Результат — аварийное завершение процесса или зависание системы.
2. Эффект «задвоения» количества и сумм
Представим ситуацию: на складе лежит товар «Шкаф двухстворчатый» с вариантом исполнения «Красный» в количестве 5 штук. В регистре сведений ЦеныНоменклатуры для этого шкафа зафиксированы две действующие цены:
При левом соединении по условию с оператором ИЛИ одна строка остатка (5 штук шкафов) найдет соответствие сразу в двух строках таблицы цен. СУБД вернет в результирующую выборку две строки для одного и того же товара. При последующем группировании и расчете итогов с использованием агрегатной функции СУММА() количество товара превратится из 5 в 10 штук, а итоговая стоимость исказится.
Разберем по шагам, как решить эту задачу правильно, используя лучшие практики программирования в среде 1С.
Самый надежный и понятный способ обойтись без оператора ИЛИ и гарантировать отсутствие дублирования записей — это использование двух независимых левых соединений к виртуальной таблице цен в одном запросе. При этом каждое соединение будет отвечать за свой уровень приоритета цены.
Посмотрим на пример реализации этого метода во временной таблице ВТ_СтоимостьТМЦ:
ВЫБРАТЬ
СтоимостьТМЦОстаткиИОбороты.Фирма КАК Фирма,
СтоимостьТМЦОстаткиИОбороты.Номенклатура КАК Номенклатура,
СтоимостьТМЦОстаткиИОбороты.ВариантИсполнения КАК ВариантИсполнения,
ЕСТЬNULL(ЦеныСВариантом.ТипЦен, &ТипЦен) КАК ТипЦен,
// Логика приоритета: если есть индивидуальная цена, берем её.
// Если её нет — берем базовую. Если нет базовой — подставляем 0.
ЕСТЬNULL(ЦеныСВариантом.Цена, ЕСТЬNULL(ЦеныБезВарианта.Цена, 0)) КАК Цена
ПОМЕСТИТЬ ВТ_СтоимостьТМЦ
ИЗ
РегистрНакопления.СтоимостьТМЦ.ОстаткиИОбороты(
&ДатаНач,
&ДатаКон, , ,
{(Фирма).*, (Номенклатура).*, (ВариантИсполнения).*}
) КАК СтоимостьТМЦОстаткиИОбороты
// Соединение №1: Пытаемся получить цену строго под конкретный вариант исполнения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&ДатаКон,
ТипЦен = &ТипЦен
И ВариантИсполнения <> ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка)
) КАК ЦеныСВариантом
ПО СтоимостьТМЦОстаткиИОбороты.Номенклатура = ЦеныСВариантом.Номенклатура
И СтоимостьТМЦОстаткиИОбороты.ВариантИсполнения = ЦеныСВариантом.ВариантИсполнения
// Соединение №2: Пытаемся получить базовую цену (где вариант пустой)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&ДатаКон,
ТипЦен = &ТипЦен
И ВариантИсполнения = ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка)
) КАК ЦеныБезВарианта
ПО СтоимостьТМЦОстаткиИОбороты.Номенклатура = ЦеныБезВарианта.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Фирма,
Номенклатура,
ВариантИсполнения
;
Проанализируем работу этого механизма:
ЦеныСВариантом) возвращает цену только тогда, когда у товара совпадает и сама номенклатурная позиция, и конкретный вариант исполнения. Если индивидуальной цены нет, все поля этой таблицы вернут значение NULL.ЦеныБезВарианта) ищет исключительно запись цены, привязанную к пустой ссылке справочника вариантов. Она вернет значение базовой цены для любого варианта товара, если такая цена зарегистрирована.ЕСТЬNULL(ЦеныСВариантом.Цена, ЕСТЬNULL(ЦеныБезВарианта.Цена, 0)) мы четко задаем приоритет. Если первое значение не NULL, выбирается цена по варианту. Если оно равно NULL, система переходит ко второму аргументу и проверяет базовую цену. Если и её нет, на выходе получаем 0.Такой подход полностью исключает риск задвоения записей и работает чрезвычайно быстро, так как СУБД соединяет таблицы по прямым проиндексированным равенствам.
Если база данных ERP-системы содержит миллионы записей, прямое соединение таблиц остатков с таблицей цен «на лету» (даже двойное левое соединение) может нагружать СУБД. В этих условиях рекомендуется разделить расчет на три простых шага во временных таблицах:
Шаг 1. Получение уникального набора аналитики
Сначала выберем из регистра остатков только уникальные комбинации товаров и вариантов исполнения, которые реально есть на складах на исследуемый период. Это сильно сократит объем данных для последующих соединений:
ВЫБРАТЬ РАЗЛИЧНЫЕ
СтоимостьТМЦ.Номенклатура КАК Номенклатура,
СтоимостьТМЦ.ВариантИсполнения КАК ВариантИсполнения
ПОМЕСТИТЬ ВТ_УникальныйСкладскойУчет
ИЗ
РегистрНакопления.СтоимостьТМЦ.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , ) КАК СтоимостьТМЦ
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ВариантИсполнения
;
Шаг 2. Расчет плоской таблицы цен для выбранных товаров
Теперь сопоставим только этот узкий список уникальных товаров с регистром цен, применив то же самое правило двойного соединения. В результате мы сформируем временную таблицу, в которой для каждого товара и варианта будет подготовлена ровно одна (приоритетная) цена:
ВЫБРАТЬ
УникальныеТовары.Номенклатура КАК Номенклатура,
УникальныеТовары.ВариантИсполнения КАК ВариантИсполнения,
ЕСТЬNULL(ЦеныКонкретные.Цена, ЕСТЬNULL(ЦеныОбщие.Цена, 0)) КАК Цена
ПОМЕСТИТЬ ВТ_ПодготовленныеЦены
ИЗ
ВТ_УникальныйСкладскойУчет КАК УникальныеТовары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&ДатаКон,
ТипЦен = &ТипЦен
) КАК ЦеныКонкретные
ПО УникальныеТовары.Номенклатура = ЦеныКонкретные.Номенклатура
И УникальныеТовары.ВариантИсполнения = ЦеныКонкретные.ВариантИсполнения
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
&ДатаКон,
ТипЦен = &ТипЦен
И ВариантИсполнения = ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка)
) КАК ЦеныОбщие
ПО УникальныеТовары.Номенклатура = ЦеныОбщие.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
ВариантИсполнения
;
Шаг 3. Итоговое соединение остатков с подготовленной таблицей цен
В финальной части пакета запросов мы соединяем исходные остатки с нашей плоской временной таблицей ВТ_ПодготовленныеЦены. Соединение происходит по двум полям (Номенклатура и ВариантИсполнения), что выполняется СУБД мгновенно:
ВЫБРАТЬ
ТаблОстатки.Филиал,
ТаблОстатки.Фирма,
ТаблОстатки.Склад,
ТаблОстатки.Номенклатура,
ТаблОстатки.ВариантИсполнения,
СУММА(ТаблОстатки.КолНачальныйОстаток) КАК КоличествоНачальныйОстаток,
СУММА(ТаблОстатки.КолНачальныйОстаток * ЕСТЬNULL(ВТ_ПодготовленныеЦены.Цена, 0)) КАК СуммаНачальныйОстаток
ИЗ
РегистрНакопления.ОстаткиТМЦ.ОстаткиИОбороты(&ДатаНач, &ДатаКон, , , ) КАК ТаблОстатки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПодготовленныеЦены КАК ВТ_ПодготовленныеЦены
ПО ТаблОстатки.Номенклатура = ВТ_ПодготовленныеЦены.Номенклатура
И ТаблОстатки.ВариантИсполнения = ВТ_ПодготовленныеЦены.ВариантИсполнения
СГРУППИРОВАТЬ ПО
ТаблОстатки.Филиал,
ТаблОстатки.Фирма,
ТаблОстатки.Склад,
ТаблОстатки.Номенклатура,
ТаблОстатки.ВариантИсполнения
;
Иногда в процессе настройки или после некорректного переноса данных в регистре накопления поле ВариантИсполнения может содержать значение типа Неопределено вместо пустой ссылки справочника. Это происходит, если измерение регистра имеет составной тип данных.
Если в одной таблице у вас записано значение Неопределено, а во второй — ПустаяСсылка, обычное левое соединение по равенству полей не сработает, и отчет вернет нулевую стоимость для товара.
Чтобы обезопасить отчет от таких ситуаций, рекомендуется предварительно приводить все пустые или неопределенные значения к единому знаменателю с помощью конструкции ЕСТЬNULL или ВЫБОР непосредственно на этапе подготовки временных таблиц:
ВЫБОР
КОГДА СтоимостьТМЦОстаткиИОбороты.ВариантИсполнения = НЕОПРЕДЕЛЕНО
ТОГДА ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка)
ИНАЧЕ ЕСТЬNULL(СтоимостьТМЦОстаткиИОбороты.ВариантИсполнения, ЗНАЧЕНИЕ(Справочник.ВариантыИсполнения.ПустаяСсылка))
КОНЕЦ КАК ВариантИсполнения
Применение этой несложной проверки гарантирует, что система корректно найдет базовую цену товара в регистре сведений, даже если исходная аналитика складского учета заполнена с ошибками.
Подведем итог: использование грамотного структурирования запроса с разделением на временные таблицы и отказ от неэффективных логических операторов в соединениях позволяют создавать быстрые и безотказные аналитические отчеты в любых конфигурациях 1С.