При разработке отчетов в системе компоновки данных (СКД) программисты часто сталкиваются с задачей: вывести абсолютно весь список номенклатуры (даже те позиции, по которым не было движений) и показать по ним текущие остатки на складах вместе с объемами заказов или продаж за определенные периоды (например, за 30, 60 и 90 дней). На первый взгляд задача кажется простой, однако при базовом подходе разработчики совершают типичные ошибки: данные либо отсекаются, либо остатки начинают «задваиваться» и «затраиваться» в геометрической прогрессии. Рассмотрим подробно, почему возникают эти проблемы и как их правильно решить с помощью языка запросов 1С и механизмов СКД.
Проанализируем типичный некорректный запрос, с которого часто начинается разработка подобных отчетов:
ВЫБРАТЬ
НоменклатураСправочник.Ссылка КАК Номенклатура,
ТоварыНаСкладахОстатки.ВНаличииОстаток КАК ВНаличииОстаток,
ЗаказыКлиентов.Заказано КАК Заказано
ИЗ
Справочник.Номенклатура КАК НоменклатураСправочник
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыНаСкладахОстатки
ПО ТоварыНаСкладахОстатки.Номенклатура = НоменклатураСправочник.Ссылка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ЗаказыКлиентов КАК ЗаказыКлиентов
ПО ЗаказыКлиентов.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
В данном запросе кроются сразу три критические ошибки, которые приводят к неверным результатам:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ, мы принудительно исключаем из итоговой выборки всю номенклатуру, по которой не было заказов в выбранном периоде. Нам необходимо использовать исключительно ЛЕВОЕ СОЕДИНЕНИЕ, где ведущей таблицей выступает справочник номенклатуры.РегистрНакопления.ЗаказыКлиентов напрямую вместо виртуальной таблицы Обороты приводит к чтению избыточных данных (всех движений регистратора) и крайне низкой скорости работы отчета.Номенклатура, СУБД продублирует строку с остатком для каждой строки заказа. В итоге СКД при расчете общих итогов просуммирует эти остатки, и вместо реальных 10 штук пользователь увидит в отчете сотни или тысячи единиц на складе.Разберем по шагам три профессиональных способа решения этих проблем.
Чтобы избежать дублирования строк и падения производительности на больших базах данных, лучшей практикой является подготовка данных во временных таблицах. Мы должны отдельно получить остатки, отдельно сгруппировать обороты заказов по периодам, а затем соединить их со справочником номенклатуры.
Рассмотрим структуру пакета запросов, который полностью решает поставленную задачу:
// Шаг 1: Получаем остатки товаров на актуальную дату во временную таблицу
ВЫБРАТЬ
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.Характеристика КАК Характеристика,
ТоварыОстатки.ВНаличииОстаток КАК АктуальныйОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&ПериодОстатков, ) КАК ТоварыОстатки
;
// Шаг 2: Получаем обороты по заказам за максимальный период (90 дней)
// и сразу распределяем их по нужным интервалам с помощью условного суммирования
ВЫБРАТЬ
ЗаказыОбороты.Номенклатура КАК Номенклатура,
ЗаказыОбороты.Характеристика КАК Характеристика,
СУММА(ВЫБОР
КОГДА ЗаказыОбороты.Период >= &Дата30
ТОГДА ЗаказыОбороты.КоличествоПриход
ИНАЧЕ 0
КОНЕЦ) КАК ЗаказаноЗа30Дней,
СУММА(ВЫБОР
КОГДА ЗаказыОбороты.Период >= &Дата60 И ЗаказыОбороты.Период < &Дата30
ТОГДА ЗаказыОбороты.КоличествоПриход
ИНАЧЕ 0
КОНЕЦ) КАК ЗаказаноЗа60Дней,
СУММА(ВЫБОР
КОГДА ЗаказыОбороты.Период >= &Дата90 И ЗаказыОбороты.Период < &Дата60
ТОГДА ЗаказыОбороты.КоличествоПриход
ИНАЧЕ 0
КОНЕЦ) КАК ЗаказаноЗа90Дней
ПОМЕСТИТЬ ВТ_ЗаказыПоПериодам
ИЗ
РегистрНакопления.ЗаказыКлиентов.Обороты(&Дата90, &КонецПериода, День, ) КАК ЗаказыОбороты
СГРУППИРОВАТЬ ПО
ЗаказыОбороты.Номенклатура,
ЗаказыОбороты.Характеристика
;
// Шаг 3: Объединяем всё левым соединением со справочником Номенклатуры
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
СпрНоменклатура.Артикул КАК Артикул,
СпрНоменклатура.Код КАК Код,
ЕСТЬNULL(ВТ_Остатки.АктуальныйОстаток, 0) КАК ВНаличииОстаток,
ЕСТЬNULL(ВТ_ЗаказыПоПериодам.ЗаказаноЗа30Дней, 0) КАК ЗаказаноЗа30,
ЕСТЬNULL(ВТ_ЗаказыПоПериодам.ЗаказаноЗа60Дней, 0) КАК ЗаказаноЗа60,
ЕСТЬNULL(ВТ_ЗаказыПоПериодам.ЗаказаноЗа90Дней, 0) КАК ЗаказаноЗа90
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
ПО СпрНоменклатура.Ссылка = ВТ_Остатки.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЗаказыПоПериодам КАК ВТ_ЗаказыПоПериодам
ПО СпрНоменклатура.Ссылка = ВТ_ЗаказыПоПериодам.Номенклатура
Проанализируем преимущества этого подхода:
Обороты. На выходе из временной таблицы ВТ_ЗаказыПоПериодам мы имеем ровно одну уникальную строку на связку «Номенклатура + Характеристика».ВНаличииОстаток не будет задваиваться в итогах СКД.СУММА(ВЫБОР КОГДА...). Это перекладывает всю тяжелую математическую работу на уровень СУБД, освобождая ресурсы сервера приложений 1С.Если вы не хотите писать громоздкие пакетные запросы с временными таблицами, СКД предлагает элегантное архитектурное решение — использование нескольких наборов данных и настройку связей между ними. Этот подход считается наиболее «правильным» с точки зрения методологии разработки на платформе 1С:Предприятие 8.3.
Рассмотрим алгоритм реализации этого метода:
ВЫБРАТЬ
СпрНоменклатура.Ссылка КАК Номенклатура,
ЕСТЬNULL(ТоварыОстатки.ВНаличииОстаток, 0) КАК ВНаличииОстаток
ИЗ
Справочник.Номенклатура КАК СпрНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(&ПериодОстатков, ) КАК ТоварыОстатки
ПО СпрНоменклатура.Ссылка = ТоварыОстатки.Номенклатура
ВЫБРАТЬ
ЗаказыОбороты.Номенклатура КАК Номенклатура,
ЗаказыОбороты.КоличествоПриход КАК Заказано,
ЗаказыОбороты.Период КАК Период
ИЗ
РегистрНакопления.ЗаказыКлиентов.Обороты(&НачалоПериода, &КонецПериода, День, ) КАК ЗаказыОбороты
Номенклатура.При таком подходе СКД самостоятельно соединит таблицы на уровне внутренней логики компоновщика. Поскольку таблицы разделены, СКД корректно рассчитает роли полей, и остатки никогда не будут задвоены в группировках отчета.
Если по каким-то причинам вы вынуждены использовать одноуровневое левое соединение таблиц остатков и оборотов в едином простом запросе, исправить проблему суммирования остатков можно через явное указание ролей полей на вкладке Наборы данных в СКД.
Чтобы платформа понимала, как правильно агрегировать показатели, выполним следующие настройки:
ВНаличииОстаток в таблице полей СКД.Номенклатура в качестве измерения).Номенклатура установим флаг Измерение.После этого СКД применит специальный алгоритм вычисления итогов по группировкам: вместо обычного суммирования всех строк (которое приводило к задвоению) система будет рассчитывать остаток только по уникальным значениям измерений.
Когда заказчик просит вывести «вообще всю номенклатуру» (даже без остатков и оборотов), выборка текстовых реквизитов (таких как Артикул, Модель, Описание, Комментарий) напрямую в запросе из справочника Номенклатура может сильно затормозить отчет, если база содержит десятки тысяч позиций.
Рекомендуемый подход: выбирайте в запросе СКД исключительно поле Ссылка (переименовав его в Номенклатура). Не пишите в тексте запроса обращения к реквизитам справочника. Вместо этого в настройках СКД на вкладке «Компоновка данных» выберите нужные реквизиты, развернув поле Номенклатура через точку (например, Номенклатура.Артикул, Номенклатура.Модель). Платформа 1С автоматически оптимизирует SQL-запрос, получая эти тяжелые данные только для тех строк, которые реально выводятся на экран пользователя в текущий момент.