Почему даты документов задваиваются при выборе иерархии номенклатуры в отчетах СКД и как это исправить?

Программист 1С v8.3 (Управляемые формы) 1С:Управление торговлей Управленческий учет Торговля и дистрибуция
← К списку

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

Выясняем причины задваивания данных

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

  1. Декартово произведение таблиц (Cartesian Product). Это одна из самых распространенных причин. Декартово произведение возникает, когда мы соединяем таблицы в запросе без явного указания условий связи или с некорректными условиями. В таком случае каждая запись из одной таблицы сопоставляется со всеми записями из другой, что приводит к многократному повторению данных. Например, если у нас есть 10 записей в Таблице1 и 5 записей в Таблице2, декартово произведение даст 50 записей. Если в запросе мы видим несколько таблиц в секции ИЗ, разделенных запятыми, без ключевого слова СОЕДИНЕНИЕ, это почти всегда декартово произведение.
  2. Неверные виды соединений. Неправильное использование видов соединений, таких как ЛЕВОЕ СОЕДИНЕНИЕ, ВНУТРЕННЕЕ СОЕДИНЕНИЕ, ПРАВОЕ СОЕДИНЕНИЕ или ПОЛНОЕ СОЕДИНЕНИЕ, также может привести к дублированию строк. Например, если мы используем ЛЕВОЕ СОЕДИНЕНИЕ там, где ожидается только одно соответствие, но фактически их несколько, каждая соответствующая запись из правой таблицы будет "присоединена" к записи из левой, дублируя ее.
  3. Ошибки в группировках. Если мы ожидаем агрегированные данные (например, сумму остатков или максимальную дату), но в запросе или в настройках СКД отсутствуют или некорректно настроены группировки, детальные записи могут выводиться по несколько раз, создавая иллюзию задваивания.
  4. Избыточные условия отбора. Иногда мы можем применять одни и те же условия отбора на разных этапах запроса или к уже отфильтрованным данным. Например, если мы уже отобрали данные по иерархии номенклатуры в виртуальной таблице, а затем пытаемся применить то же условие В ИЕРАРХИИ в секции ГДЕ при соединении с другой таблицей, это может привести к непредсказуемым результатам или быть просто излишним.
  5. Особенности работы с иерархией. При использовании оператора В ИЕРАРХИИ важно понимать, как он работает и где его применять. Его некорректное использование, особенно в сочетании с соединениями, может усложнить отладку и привести к дублированию.

Разбираем решение проблемы по шагам

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

1. Корректное использование оператора "В ИЕРАРХИИ" и виртуальных таблиц

Начнем с правильного формирования запроса к виртуальной таблице остатков, используя оператор В ИЕРАРХИИ. Этот подход позволяет отфильтровать данные по иерархии на самом раннем этапе. Мы рассмотрим пример запроса к регистру накопления ТоварыНаСкладах.Остатки: 


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

Здесь мы сразу отбираем данные по иерархии номенклатуры и склада на уровне виртуальной таблицы Остатки. Это очень важный момент, так как он позволяет получить уже отфильтрованные данные перед дальнейшими соединениями. Параметры &Дата, &Номенклатура и &Склад будут задаваться в настройках СКД.

2. Правильное использование соединений и избегание декартова произведения

Теперь, когда у нас есть корректно отобранные остатки, мы можем присоединять к ним данные из других таблиц, например, информацию о движениях. Ключевой момент — всегда явно указывать условия соединения и использовать правильный тип соединения. Проанализируем ситуацию, когда данные о движениях (например, перемещениях, приобретениях) присоединяются к остаткам. Если мы хотим получить дату последнего движения, нам нужно быть особенно внимательными. Рассмотрим пример, где нам нужно получить дату последнего перемещения для номенклатуры и склада, используя ЛЕВОЕ СОЕДИНЕНИЕ: 


ВЫБРАТЬ
    ПеремещениеТоваровТовары.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ПеремещениеТоваровТовары.Ссылка.Дата) КАК ДатаПеремещение,
    ПеремещениеТоваровТовары.Ссылка.СкладПолучатель КАК СкладПолучатель,
    ТоварыНаСкладахОстатки.ВНаличииОстаток КАК ВНаличииОстаток
ПОМЕСТИТЬ ПеремещениеТоваровДвижения
ИЗ
    ТоварыНаСкладахОстатки КАК ТоварыНаСкладахОстатки
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПеремещениеТоваров.Товары КАК ПеремещениеТоваровТовары
        ПО (ПеремещениеТоваровТовары.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура)
            И (ПеремещениеТоваровТовары.Ссылка.СкладПолучатель = ТоварыНаСкладахОстатки.Склад)
СГРУППИРОВАТЬ ПО
    ПеремещениеТоваровТовары.Ссылка.СкладПолучатель,
    ПеремещениеТоваровТовары.Номенклатура,
    ТоварыНаСкладахОстатки.ВНаличииОстаток

Здесь мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы получить все остатки, даже если по ним не было перемещений. Условия соединения ПО четко определяют связь между таблицами по Номенклатуре и СкладуПолучателю (который в данном контексте соответствует Складу из остатков). Мы также используем функцию МАКСИМУМ(ПеремещениеТоваровТовары.Ссылка.Дата) и СГРУППИРОВАТЬ ПО, чтобы получить только одну, самую последнюю дату перемещения для каждой комбинации номенклатуры и склада, избегая задваивания дат. Важный момент: Обратите внимание, что после такого соединения, если исходные ТоварыНаСкладахОстатки уже были отфильтрованы по иерархии (как в примере выше), то дополнительная секция ГДЕ с условиями В ИЕРАРХИИ для ПеремещениеТоваровТовары будет избыточной, так как мы уже соединяемся с отобранными данными.

3. Использование оборотных регистров для получения дат движений

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

  1. Получаем остатки. Это можно сделать так, как мы показали выше, с помощью РегистрНакопления.ТоварыНаСкладах.Остатки.
  2. Получаем обороты. Для получения информации о движениях, включая даты, мы можем обратиться к РегистрНакопления.ТоварыНаСкладах.Обороты. Это позволит нам получить детализацию до регистратора, где Период будет датой движения, а Регистратор — ссылкой на документ. Мы можем ограничить период для оборотов, например, за последний квартал.
  3. Соединяем данные. Затем мы можем соединить остатки с оборотами, чтобы выявить номенклатуру, по которой есть остатки, но нет движений за определенный период.

Пример запроса на получение оборотов с детализацией: 


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

После этого мы можем соединить ТоварыНаСкладахОстатки с ТоварыНаСкладахОбороты через ЛЕВОЕ СОЕДИНЕНИЕ, чтобы найти товары, по которым не было движений.

4. Объединение данных из разных источников

Если нам нужно собрать данные о датах из разных типов документов (ПриобретениеТоваровУслуг, ПеремещениеТоваров, ОтчетОРозничныхПродажах и т.д.), и при этом получить только одну, самую последнюю дату для каждой номенклатуры/склада, мы можем использовать следующий подход:

  1. Для каждого типа документа создаем свой временный запрос, который выбирает Номенклатуру, Склад и Дату документа, группируя по номенклатуре и складу и находя МАКСИМУМ(Дата).
  2. Объединяем результаты этих запросов с помощью ОБЪЕДИНИТЬ ВСЕ.
  3. Из полученного общего набора данных снова группируем по Номенклатуре и Складу и находим МАКСИМУМ(Дата), чтобы получить единую последнюю дату из всех типов документов.
  4. Соединяем этот результат с таблицей ТоварыНаСкладахОстатки.

Пример структуры такого запроса: 


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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВсеДаты.Номенклатура КАК Номенклатура,
    ВсеДаты.Склад КАК Склад,
    МАКСИМУМ(ВсеДаты.ДатаДвижения) КАК ПоследняяДатаДвижения
ПОМЕСТИТЬ ПоследниеДатыДвижений
ИЗ
    (ВЫБРАТЬ
        ПриобретенияДаты.Номенклатура КАК Номенклатура,
        ПриобретенияДаты.Склад КАК Склад,
        ПриобретенияДаты.ДатаДвижения КАК ДатаДвижения
    ИЗ
        ПриобретенияДаты КАК ПриобретенияДаты

    ОБЪЕДИНИТЬ ВСЕ

    ВЫБРАТЬ
        ПеремещенияДаты.Номенклатура,
        ПеремещенияДаты.Склад,
        ПеремещенияДаты.ДатаДвижения
    ИЗ
        ПеремещенияДаты КАК ПеремещенияДаты) КАК ВсеДаты
СГРУППИРОВАТЬ ПО
    ВсеДаты.Номенклатура,
    ВсеДаты.Склад
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Остатки.Номенклатура,
    Остатки.Склад,
    Остатки.ВНаличииОстаток,
    ПоследниеДаты.ПоследняяДатаДвижения
ИЗ
    ТоварыНаСкладахОстатки КАК Остатки
        ЛЕВОЕ СОЕДИНЕНИЕ ПоследниеДатыДвижений КАК ПоследниеДаты
        ПО Остатки.Номенклатура = ПоследниеДаты.Номенклатура
            И Остатки.Склад = ПоследниеДаты.Склад

Этот пример демонстрирует, как мы можем сначала подготовить агрегированные данные по датам из разных источников, а затем корректно присоединить их к основному набору данных об остатках.

Общие рекомендации по отладке и предотвращению задваивания

Чтобы минимизировать появление дубликатов в отчетах СКД, рекомендуем придерживаться следующих правил:

  1. Детальный анализ запроса. Всегда проверяйте детальные записи, которые получаются в результате выполнения запроса. Используйте консоль запросов 1С или консоль компоновки данных для пошаговой отладки. Просматривайте результаты каждого временного запроса и каждой секции СОЕДИНЕНИЕ, чтобы точно определить, на каком этапе возникают дубликаты.
  2. Явное указание связей. При соединении нескольких таблиц всегда явно указывайте условия соединения с помощью ключевого слова ПО. Избегайте неявных соединений (через запятую в секции ИЗ без СОЕДИНЕНИЕ), так как это почти всегда приводит к декартову произведению.
  3. Оптимизация запросов. Проверяйте запросы на наличие неоптимальных конструкций. Используйте индексы регистров и справочников, чтобы ускорить выполнение запросов.
  4. Корректное использование группировок. Если вы используете агрегатные функции (СУММА, МАКСИМУМ, МИНИМУМ, КОЛИЧЕСТВО), не забывайте указывать все неагрегированные поля в секции СГРУППИРОВАТЬ ПО.
  5. Понимание работы СКД. Помните, что СКД может преобразовывать ваш исходный запрос, например, исключая поля, которые не используются в результате. Используйте инструменты разработчика для просмотра того, какой SQL-запрос генерирует СКД в конечном итоге.

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

← К списку