При разработке отчетов в 1С программисты часто сталкиваются с задачей расчета показателей, зависящих от определенных свойств номенклатуры. Классический пример — вычисление стоимости проданного товара на основе индивидуальных тарифов, привязанных к артикулам. Когда таких артикулов становится много, стандартный подход с использованием множества конструкций ОБЪЕДИНИТЬ ВСЕ и условий ВЫБОР КОГДА превращает запрос в нечитаемый и крайне медленный код. Кроме того, при неверном проектировании связей в запросе начинают "плыть" итоговые суммы по количеству и стоимости.
В этой статье мы подробно разберем, почему возникают ошибки при подсчете итогов в объединениях, как оптимизировать такие запросы с помощью временных таблиц, как правильно передавать внешние таблицы тарифов в качестве параметров и как реализовать эту задачу наиболее изящным способом — через создание регистра сведений или использование механизмов СКД.
Проанализируем ситуацию, представленную в первом сообщении темы. Автор пытается получить данные из табличной части документа РеализацияТоваровУслуг.Товары. Для расчета поля КоличествоУмноженноеНаТариф он использует три последовательных запроса, объединенных через ОБЪЕДИНИТЬ ВСЕ. В каждом запросе жестко прописаны списки артикулов и соответствующий им тариф (например, 12, 10 или 5).
При таком подходе возникают сразу несколько критических проблем:
МАКСИМУМ(РеализацияТоваровУслугТовары.Количество). Если одна и та же торговая точка встречается в разных ветках объединения, то при последующем сворачивании данных мы получим некорректное суммирование. Использование максимума вместо суммы искажает реальные показатели продаж.ВЫБОР КОГДА проверяют только конкретные артикулы, строки документов, не попавшие под условия, возвращают значение 0 или NULL, но все равно участвуют в объединении, раздувая выборку.ПО РеализацияТоваровУслугТовары.Номенклатура.Артикул = ТоварыМакфа.Количество. Здесь строковое поле артикула сопоставляется с числовым полем количества. Такое соединение гарантированно вернет пустой результат или абсолютно непредсказуемые данные.Давайте пошагово разберем, как решить эти проблемы раз и навсегда.
Если отчет одноразовый и создавать новые объекты в метаданных базы нельзя, лучшим решением будет создание единой временной таблицы сопоставления "Артикул — Тариф" с последующим левым соединением к исходным документам. Этот подход позволяет избавиться от дублирования исходных документов в запросе.
Рассмотрим пример построения такого запроса:
// Шаг 1: Формируем временную таблицу тарифов
ВЫБРАТЬ "Мак-02994" КАК Артикул, 12 КАК Тариф
ПОМЕСТИТЬ ВТ_Тарифы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "1028-5МАК", 10
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "100-4МАК", 5
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "1178-АК", 5
;
// Шаг 2: Получаем данные документов и соединяем их с тарифами
ВЫБРАТЬ
ДокТовары.Ссылка.ТорговаяТочка КАК ТорговаяТочка,
СУММА(ДокТовары.Количество) КАК Количество,
СУММА(ДокТовары.Количество * ЕСТЬNULL(ВТ_Тарифы.Тариф, 0)) КАК КоличествоТариф
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК ДокТовары
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Тарифы КАК ВТ_Тарифы
ПО ДокТовары.Номенклатура.Артикул = ВТ_Тарифы.Артикул
ГДЕ
ДокТовары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
И ДокТовары.Ссылка.Проведен = ИСТИНА
И ДокТовары.Номенклатура В ИЕРАРХИИ(&Номенклатура)
СГРУППИРОВАТЬ ПО
ДокТовары.Ссылка.ТорговаяТочка
Преимущества этого подхода: Документы продаж считываются из базы данных ровно один раз. Суммирование количества и стоимости происходит корректно на уровне СУБД без использования сомнительных группировок по максимуму. Если у товара нет тарифа, функция ЕСТЬNULL вернет 0, предотвращая появление пустых значений в расчетах.
Когда количество артикулов превышает сотню (как упомянул автор — 143 артикула), писать 143 секции ОБЪЕДИНИТЬ ВСЕ в конструкторе запроса становится крайне утомительно. В таком случае гораздо эффективнее заполнить таблицу значений в коде 1С (например, прочитав ее из файла Excel или задав программно) и передать ее в запрос в качестве параметра.
Проанализируем пример программной подготовки и передачи таблицы тарифов:
// 1. Создаем таблицу значений и строго типизируем ее колонки
ТаблицаТарифов = Новый ТаблицаЗначений;
ТаблицаТарифов.Колонки.Добавить("Артикул", Новый ОписаниеТипов("Строка",,,, Новый КвалификаторыСтроки(150)));
ТаблицаТарифов.Колонки.Добавить("Тариф", Новый ОписаниеТипов("Число",,,, Новый КвалификаторыЧисла(15, 2)));
// 2. Наполняем таблицу данными (в реальной задаче здесь может быть обход строк Excel)
НоваяСтрока = ТаблицаТарифов.Добавить();
НоваяСтрока.Артикул = "Мак-02994";
НоваяСтрока.Тариф = 12;
НоваяСтрока = ТаблицаТарифов.Добавить();
НоваяСтрока.Артикул = "1028-5МАК";
НоваяСтрока.Тариф = 10;
// 3. Передаем таблицу в запрос
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
Запрос.УстановитьПараметр("ТаблицаТарифов", ТаблицаТарифов);
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Запрос.УстановитьПараметр("Номенклатура", ГруппаНоменклатуры);
Запрос.Текст =
"ВЫБРАТЬ
| ВнешнийИсточник.Артикул КАК Артикул,
| ВнешнийИсточник.Тариф КАК Тариф
|ПОМЕСТИТЬ ВТ_Тарифы
|ИЗ
| &ТаблицаТарифов КАК ВнешнийИсточник
|ИНДЕКСИРОВАТЬ ПО
| Артикул
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ДокТовары.Ссылка.ТорговаяТочка КАК ТорговаяТочка,
| СУММА(ДокТовары.Количество) КАК Количество,
| СУММА(ДокТовары.Количество * ЕСТЬNULL(ВТ_Тарифы.Тариф, 0)) КАК КоличествоТариф
|ИЗ
| Документ.РеализацияТоваровУслуг.Товары КАК ДокТовары
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Тарифы КАК ВТ_Тарифы
| ПО ДокТовары.Номенклатура.Артикул = ВТ_Тарифы.Артикул
|ГДЕ
| ДокТовары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
| И ДокТовары.Ссылка.Проведен = ИСТИНА
| И ДокТовары.Номенклатура В ИЕРАРХИИ(&Номенклатура)
|СГРУППИРОВАТЬ ПО
| ДокТовары.Ссылка.ТорговаяТочка";
РезультатЗапроса = Запрос.Выполнить();
Важное правило оптимизации: Обратите внимание на ключевое слово ИНДЕКСИРОВАТЬ ПО Артикул при помещении таблицы во временную. Поскольку мы планируем выполнять левое соединение по этому полю, индексация критически важна для быстродействия СУБД, особенно на больших объемах данных.
Если отчет планируется использовать регулярно, хранение тарифов в коде программы — это грубая архитектурная ошибка. Самым надежным решением будет создание нового объекта метаданных — непериодического (или периодического, если тарифы меняются со временем) регистра сведений НастройкиТарифовНоменклатуры.
Разберем структуру такого регистра:
Номенклатура (Тип: СправочникСсылка.Номенклатура). Использование ссылки вместо текстового артикула защитит базу от ошибок при опечатках в артикулах.Тариф (Тип: Число, длина 15, точность 2).Заполнение этого регистра пользователи смогут выполнять самостоятельно непосредственно в режиме "1С:Предприятие", а наш запрос примет максимально простой и лаконичный вид:
ВЫБРАТЬ
ДокТовары.Ссылка.ТорговаяТочка КАК ТорговаяТочка,
СУММА(ДокТовары.Количество) КАК Количество,
СУММА(ДокТовары.Количество * ЕСТЬNULL(Тарифы.Тариф, 0)) КАК КоличествоТариф
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК ДокТовары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.НастройкиТарифовНоменклатуры КАК Тарифы
ПО ДокТовары.Номенклатура = Тарифы.Номенклатура
ГДЕ
ДокТовары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
И ДокТовары.Ссылка.Проведен = ИСТИНА
И ДокТовары.Номенклатура В ИЕРАРХИИ(&Номенклатура)
СГРУППИРОВАТЬ ПО
ДокТовары.Ссылка.ТорговаяТочка
Такой запрос отработает мгновенно, его легко читать, а главное — он полностью соответствует стандартам разработки фирмы "1С".
Если отчет строится на базе СКД, мы можем полностью делегировать расчет итогов платформе. Для этого достаточно выполнить следующие шаги:
Номенклатура.КоличествоТариф с выражением: Количество * Тариф.Количество указать выражение Сумма(Количество), а для поля КоличествоТариф — Сумма(КоличествоТариф).Система компоновки данных самостоятельно и гарантированно правильно рассчитает итоги во всех группировках и в строке "Итого", избежав задвоения строк при соединении.
При работе с большими базами данных помните о явлении, которое называется разыменованием ссылочных полей через точку. Когда в условиях связи вы пишете ПО ДокТовары.Номенклатура.Артикул = ВТ_Тарифы.Артикул, СУБД вынуждена на лету делать неявное соединение таблицы документов с таблицей справочника номенклатуры, чтобы достать артикул. Это значительно снижает скорость выполнения запроса.
Как этого избежать? Всегда старайтесь организовывать связи по ключевым полям ссылочного типа (Номенклатура = НоменклатураСсылка). Если сопоставление по артикулу неизбежно, сначала выгрузите данные документов во временную таблицу, вытащив текстовое поле артикула отдельным полем, проиндексируйте обе таблицы по артикулу и только после этого соединяйте их.