Как исправить задвоение движений в отчете «Ведомость по партиям номенклатуры» при добавлении цен?

← На главную

При доработке типовых конфигураций (таких как «Управление торговлей 10.3», «Управление производственным предприятием 1.3» или «Комплексная автоматизация 1.1») программисты часто сталкиваются с необходимостью вывода цен номенклатуры в материальных отчетах. Типичный пример — добавление цен компании в стандартную ведомость по партиям. Однако при решении этой задачи «в лоб» через простое соединение таблиц разработчики часто получают неприятный результат: показатели количественного и суммового учета в отчете начинают удваиваться, утраиваться или вовсе пропадать.

В этой статье мы подробно разберем, почему возникает проблема задвоения движений, проанализируем архитектурные ошибки исходного запроса и рассмотрим два надежных способа решения проблемы: через оптимизацию текста запроса с использованием временных таблиц и через механизмы компоновки данных (СКД).

Анализ проблемы: почему двоятся данные в исходном запросе?

Рассмотрим представленный в теме исходный запрос. Проблема кроется в финальной части, где описывается выбор источников данных и условий их сопоставления:


ИЗ
    РегистрСведений.ЦеныКомпании.СрезПоследних(&НаДату {(&НаДату)}, ТипЦен = &ТипЦен) КАК ЦеныКомпанииСрезПоследних
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты(...) КАК ИсточникДанных
        ПО (ИсточникДанных.Номенклатура = ЦеныКомпанииСрезПоследних.Номенклатура)

Проанализируем эту конструкцию. Здесь допущены сразу три критические ошибки, которые нарушают логику выборки данных и приводят к искажению результатов отчета.

Ошибка №1: Неверное направление ЛЕВОГО СОЕДИНЕНИЯ

В исходном запросе в качестве ведущей (левой) таблицы выступает виртуальная таблица цен РегистрСведений.ЦеныКомпании.СрезПоследних, а ведомость по партиям РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты присоединяется к ней справа.

Вспомним, как работает оператор ЛЕВОЕ СОЕДИНЕНИЕ (LEFT JOIN). Он возвращает абсолютно все строки из левой таблицы, сопоставляя их со строками из правой таблицы по указанному условию. Если в правой таблице нет совпадений, поля заполняются значениями NULL.

Применительно к нашему отчету это приводит к следующим критическим последствиям:

Пропажа товаров без установленных цен. Если по позиции номенклатуры были реальные складские движения и остатки, но на дату формирования отчета для нее не была зарегистрирована цена в регистре ЦеныКомпании, эта позиция вообще исчезнет из отчета! Это недопустимо для материальной ведомости, которая должна отражать точные остатки на складах независимо от наличия прайс-листа.
Избыточные строки для товаров без движений. Если для товара установлена цена, но никаких движений по складу в выбранном периоде не происходило, этот товар все равно попадет в отчет с пустыми (нулевыми) показателями остатков и оборотов. Это загромождает ведомость лишней информацией.

Правило: Главной (левой) таблицей в материальных отчетах всегда должен быть регистр накопления (остатки и обороты товаров), а справочная информация (цены, свойства, категории) должна присоединяться к нему справочно (справа).

Ошибка №2: Неполное условие связи по измерениям

Вторая и самая главная причина лавинообразного задвоения (и размножения) строк — это условие связи:

ПО (ИсточникДанных.Номенклатура = ЦеныКомпанииСрезПоследних.Номенклатура)

Регистр сведений ЦеныКомпании (или его аналог ЦеныНоменклатуры в различных конфигурациях), помимо измерения Номенклатура, практически всегда содержит второе ключевое измерение — ХарактеристикаНоменклатуры. Даже если в информационной базе характеристики используются не для всех товаров, это измерение присутствует в структуре регистра.

Когда мы соединяем таблицы только по полю Номенклатура, СУБД выполняет сопоставление каждой строки движений товара со всеми имеющимися в срезе последних записями цен для данного товара по всем его характеристикам (цветам, размерам, свойствам).

Например, если по товару «Ботинки мужские» было 1 движение по партии, а в регистре цен для этого товара задано 5 цен для разных размеров (характеристик), в результате такого соединения мы получим 5 строк в отчете. Количественные и суммовые показатели движения партии увеличатся ровно в 5 раз!

Правило: При соединении таблиц необходимо связывать их по всем совпадающим ключевым разрезам учета (измерениям). В данном случае обязательна связь как по номенклатуре, так и по характеристикам.

Ошибка №3: Соединение двух виртуальных таблиц напрямую

С точки зрения производительности и оптимизации запросов в системе «1С:Предприятие», прямое соединение двух тяжелых виртуальных таблиц (таких как ОстаткиИОбороты и СрезПоследних) является крайне нежелательным.

СУБД не всегда способна построить оптимальный план выполнения для таких запросов. Это приводит к высокой нагрузке на сервер, длительному времени формирования отчета и возможным взаимоблокировкам базы данных при одновременной работе пользователей.

---

Вариант решения №1: Оптимизация запроса через Временные Таблицы

Рассмотрим, как правильно переписать исходный запрос с исправлением всех архитектурных ошибок. Для обеспечения высокой скорости работы и исключения дублирования данных мы воспользуемся механизмом временных таблиц. Разделим один тяжелый запрос на последовательные шаги.

Рассмотрим по шагам логику построения нового пакета запросов:

Шаг 1. Выгрузка партионных движений во временную таблицу. Сначала мы выбираем все необходимые нам движения и остатки из регистра накопления во временную таблицу ВТ_ДвиженияПартий. Это позволит зафиксировать набор данных и проиндексировать его для последующего быстрого соединения.


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ИсточникДанных.Номенклатура КАК Номенклатура,
    ИсточникДанных.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ИсточникДанных.Склад КАК Склад,
    ИсточникДанных.Партия КАК Партия,
    ИсточникДанных.ДокументДвижения КАК ДокументДвижения,
    ИсточникДанных.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
    ИсточникДанных.КоличествоПриход КАК КоличествоПриход,
    ИсточникДанных.КоличествоРасход КАК КоличествоРасход,
    ИсточникДанных.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
    // (Добавьте остальные необходимые числовые поля сумм по аналогии)
    ИсточникДанных.ПериодСекунда КАК ПериодСекунда
ПОМЕСТИТЬ ВТ_ДвиженияПартий
ИЗ
    РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Авто, , ) КАК ИсточникДанных
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ХарактеристикаНоменклатуры
;

Шаг 2. Получение цен номенклатуры. Теперь подготовим цены. Выберем актуальные цены из среза последних во вторую временную таблицу. Важно наложить фильтр по типу цен на уровне параметров виртуальной таблицы, чтобы отсечь лишние данные еще на этапе обращения к базе данных.


ВЫБРАТЬ
    ЦеныСрезПоследних.Номенклатура КАК Номенклатура,
    ЦеныСрезПоследних.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ЦеныСрезПоследних.Цена КАК Цена
ПОМЕСТИТЬ ВТ_ЦеныНоменклатуры
ИЗ
    РегистрСведений.ЦеныКомпании.СрезПоследних(&НаДату, ТипЦен = &ТипЦен) КАК ЦеныСрезПоследних
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ХарактеристикаНоменклатуры
;

Шаг 3. Соединение таблиц в финальном запросе. На заключительном этапе мы объединяем подготовленные данные. Основной таблицей выступает ВТ_ДвиженияПартий, а цены из ВТ_ЦеныНоменклатуры присоединяются к ней с помощью левого соединения по двум ключевым полям: номенклатуре и характеристике.


ВЫБРАТЬ
    Партии.ДокументДвижения КАК ДокументДвижения,
    Партии.ПериодСекунда КАК ПериодСекунда,
    Партии.Номенклатура КАК Номенклатура,
    Партии.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    Партии.Склад КАК Склад,
    Партии.Партия КАК Партия,
    Партии.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
    Партии.КоличествоПриход КАК КоличествоПриход,
    Партии.КоличествоРасход КАК КоличествоРасход,
    Партии.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
    ЕСТЬNULL(ТаблицаЦен.Цена, 0) КАК Цена
ИЗ
    ВТ_ДвиженияПартий КАК Партии
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЦеныНоменклатуры КАК ТаблицаЦен
        ПО Партии.Номенклатура = ТаблицаЦен.Номенклатура
        И Партии.ХарактеристикаНоменклатуры = ТаблицаЦен.ХарактеристикаНоменклатуры

Использование функции ЕСТЬNULL позволяет нам корректно вывести нулевую цену для тех товаров, по которым были движения, но цена которых не была задана в системе.

---

Вариант решения №2: Связи наборов данных в Системе компоновки данных (СКД)

Если ведомость по партиям построена с использованием Системы компоновки данных (что является стандартом для большинства современных отчетов 1С), у нас есть еще более элегантный и производительный способ решения проблемы. Нам вообще не требуется изменять сложный и объемный основной запрос ведомости. Вместо этого мы настроим связь наборов данных непосредственно в СКД.

Этот подход разделяет получение данных на два независимых потока (набора данных), которые СКД автоматически и безошибочно объединяет в оперативной памяти непосредственно перед выводом пользователю.

Разберем процесс настройки по шагам:

Откройте схему компоновки данных (СКД) вашего отчета.
Перейдите на вкладку Наборы данных. У вас уже должен присутствовать основной набор данных — запрос (например, с именем НаборДанных1), который отвечает за вывод партионного учета.

Добавьте новый набор данных типа «Запрос» и назовите его НаборДанныхЦены. Напишите для него максимально простой и быстрый запрос получения цен:


ВЫБРАТЬ
    ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
    ЦеныНоменклатурыСрезПоследних.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ИЗ
    РегистрСведений.ЦеныКомпании.СрезПоследних(&ПериодЦен, ТипЦен = &ТипЦен) КАК ЦеныНоменклатурыСрезПоследних

Перейдите на вкладку Связи наборов данных.
Добавьте новую строку связи и заполните ее параметры следующим образом:
- Источник связи: НаборДанных1 (основной запрос партий).
- Приемник связи: НаборДанныхЦены.
- Условие связи: Добавьте две строки сопоставления полей:
  1. Номенклатура = Номенклатура
  2. ХарактеристикаНоменклатуры = ХарактеристикаНоменклатуры
- Параметры: В колонке «Параметры» укажите имя параметра, который будет передавать дату для среза цен из основного запроса в подчиненный (например, свяжите параметр ПериодЦен с полем Период или датой окончания отчета).
Перейдите на вкладку Настройки и добавьте новое поле Цена в выбранные поля вашего отчета.

Преимущества использования связей наборов данных в СКД:

Абсолютная защита от дублей. СКД сопоставляет данные на уровне платформы. Даже если структура исходных таблиц сложна, механизм связи наборов гарантирует, что строки отчета не будут перемножаться.
Простота поддержки. Вам не приходится переписывать и отлаживать гигантский типовой запрос партионного учета, который может содержать множество внутренних условий и переключателей.
Экономия ресурсов. СКД оптимизирует выполнение запросов к базе данных, выбирая цены только для тех элементов номенклатуры, которые реально попали в результат выполнения основного запроса (после наложения всех пользовательских фильтров).

Таким образом, мы детально изучили причины некорректного поведения отчетов при соединении материальных таблиц с ценовыми регистрами и разобрали надежные методы устранения ошибок. Выбор конкретного метода зависит от архитектуры вашего отчета, но для отчетов на СКД связывание наборов данных всегда остается наиболее предпочтительным решением.