При формировании отчетов в Системе Компоновки Данных (СКД) разработчики часто сталкиваются с ситуацией, когда данные задваиваются, хотя прямой запрос в консоли возвращает корректный результат без дубликатов. Эта проблема особенно актуальна при использовании левых соединений (ЛЕВОЕ СОЕДИНЕНИЕ) и временных таблиц (ПОМЕСТИТЬ ВТ_...) в запросах. Давайте подробно разберем, почему возникает такая ситуация и какие шаги можно предпринять для ее устранения.
Рассмотрим типовой сценарий, описанный нашим коллегой, использующим платформу 1С:Предприятие 8.3.24.1548 и конфигурацию 1С:ERP Управление предприятием 2 (2.5.16.107) с СУБД MS SQL 2019. Для детального анализа можно выполнить преобразование запроса 1С в SQL. В его отчете СКД наблюдалось задвоение строк, несмотря на то, что тот же запрос, выполненный в консоли запросов, давал ожидаемый результат.
Основная сложность заключалась в том, что запрос, который отлично работал вне СКД, начинал выдавать дубликаты именно при его использовании в схеме компоновки данных. Анализ показал, что задвоение возникало в последнем пакете запроса, где использовалось левое соединение.
Рассмотрим структуру запроса, который приводил к задвоениям:
ВЫБРАТЬ
ЗаказыКлиентовОбороты.ЗаказКлиента КАК ЗаказКлиента,
ЗаказыКлиентовОбороты.Номенклатура КАК Номенклатура,
ЗаказыКлиентовОбороты.КодСтроки КАК КодСтроки,
ЗаказыКлиентовОбороты.СуммаПриход КАК СуммаСНДС
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
РегистрНакопления.ЗаказыКлиентов.Обороты(&НачалоПериода, &КонецПериода, Регистратор, ) КАК ЗаказыКлиентовОбороты
ГДЕ
ТИПЗНАЧЕНИЯ(ЗаказыКлиентовОбороты.Регистратор) = ТИП(Документ.ЗаказКлиента)
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента,
Номенклатура,
КодСтроки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Заказы.ЗаказКлиента КАК ЗаказКлиента
ПОМЕСТИТЬ ВТ_ТолькоЗаказы
ИЗ
ВТ_Заказы КАК ВТ_Заказы
СГРУППИРОВАТЬ ПО
ВТ_Заказы.ЗаказКлиента
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЗаказКлиентаТовары.Ссылка КАК ЗаказКлиента,
ЗаказКлиентаТовары.Номенклатура КАК Номенклатура,
ЗаказКлиентаТовары.КодСтроки КАК КодСтроки,
ЗаказКлиентаТовары.ИдентификаторСтроки КАК ИдентификаторСтроки,
ЗаказКлиентаТовары.ДатаОтгрузки КАК ДатаОтгрузки
ПОМЕСТИТЬ ВТ_ТЧ_Заказов
ИЗ
Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
ГДЕ
ЗаказКлиентаТовары.Ссылка В
(ВЫБРАТЬ
ВТ_ТолькоЗаказы.ЗаказКлиента КАК ЗаказКлиента
ИЗ
ВТ_ТолькоЗаказы КАК ВТ_ТолькоЗаказы)
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента,
Номенклатура,
КодСтроки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Заказы.ЗаказКлиента КАК ЗаказКлиента,
ВТ_Заказы.Номенклатура КАК Номенклатура,
ВТ_ТЧ_Заказов.ИдентификаторСтроки КАК ИдентификаторСтроки,
ВТ_Заказы.СуммаСНДС КАК СуммаСНДС,
ВТ_ТЧ_Заказов.ДатаОтгрузки КАК ДатаОтгрузки
ПОМЕСТИТЬ ВТ_Врем
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТЧ_Заказов КАК ВТ_ТЧ_Заказов
ПО ВТ_Заказы.ЗаказКлиента = ВТ_ТЧ_Заказов.ЗаказКлиента
И ВТ_Заказы.Номенклатура = ВТ_ТЧ_Заказов.Номенклатура
И ВТ_Заказы.КодСтроки = ВТ_ТЧ_Заказов.КодСтроки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СуммыДокументовВВалютахУчета.Регистратор КАК ЗаказКлиента,
СуммыДокументовВВалютахУчета.ИдентификаторСтроки КАК ИдентификаторСтроки,
СуммыДокументовВВалютахУчета.СуммаБезНДС КАК СуммаБезНДС_Вал,
СуммыДокументовВВалютахУчета.СуммаБезНДС + СуммыДокументовВВалютахУчета.СуммаНДС КАК СуммаСНДС_Вал,
СуммыДокументовВВалютахУчета.СуммаБезНДСРегл КАК СуммаБезНДС_Руб,
СуммыДокументовВВалютахУчета.СуммаБезНДСРегл + СуммыДокументовВВалютахУчета.СуммаНДСРегл КАК СуммаСНДС_Руб
ПОМЕСТИТЬ ВТ_Суммы
ИЗ
РегистрСведений.СуммыДокументовВВалютахУчета КАК СуммыДокументовВВалютахУчета
ГДЕ
СуммыДокументовВВалютахУчета.Регистратор В
(ВЫБРАТЬ
ВТ_ТолькоЗаказы.ЗаказКлиента КАК ЗаказКлиента
ИЗ
ВТ_ТолькоЗаказы КАК ВТ_ТолькоЗаказы)
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента,
ИдентификаторСтроки
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВыручкаИСебестоимостьПродажОбороты.ЗаказКлиента КАК ЗаказКлиента,
ВыручкаИСебестоимостьПродажОбороты.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
СУММА(ВЫБОР
КОГДА НАЧАЛОПЕРИОДА(ВыручкаИСебестоимостьПродажОбороты.Период, ГОД) = НАЧАЛОПЕРИОДА(&КонецПериода, ГОД)
ТОГДА ВыручкаИСебестоимостьПродажОбороты.СуммаВыручкиРеглОборот
ИНАЧЕ 0
КОНЕЦ) КАК ОтгрузкаФактТекущийГод
ПОМЕСТИТЬ ВТ_ОтгрузкиФакт
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(, , Регистратор, ) КАК ВыручкаИСебестоимостьПродажОбороты
ГДЕ
(ТИПЗНАЧЕНИЯ(ВыручкаИСебестоимостьПродажОбороты.Регистратор) = ТИП(Документ.АктВыполненныхРабот)
ИЛИ ТИПЗНАЧЕНИЯ(ВыручкаИСебестоимостьПродажОбороты.Регистратор) = ТИП(Документ.РеализацияТоваровУслуг))
СГРУППИРОВАТЬ ПО
ВыручкаИСебестоимостьПродажОбороты.ЗаказКлиента,
ВыручкаИСебестоимостьПродажОбороты.АналитикаУчетаНоменклатуры.Номенклатура
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента,
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Врем.ЗаказКлиента КАК ЗаказКлиента,
ВТ_Врем.Номенклатура КАК Номенклатура,
ВТ_Врем.ИдентификаторСтроки КАК ИдентификаторСтроки,
ВТ_Суммы.СуммаБезНДС_Вал КАК СуммаБезНДС_Вал,
ВТ_Суммы.СуммаСНДС_Вал КАК СуммаСНДС_Вал,
ВТ_Суммы.СуммаБезНДС_Руб КАК СуммаБезНДС_Руб,
ВТ_Суммы.СуммаСНДС_Руб КАК СуммаСНДС_Руб
ИЗ
ВТ_Врем КАК ВТ_Врем
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Суммы КАК ВТ_Суммы
ПО (ВТ_Врем.ЗаказКлиента = ВТ_Суммы.ЗаказКлиента)
И (ВТ_Врем.ИдентификаторСтроки = ВТ_Суммы.ИдентификаторСтроки)
УПОРЯДОЧИТЬ ПО
ЗаказКлиента,
Номенклатура
ИТОГИ
СУММА(СуммаБезНДС_Вал),
СУММА(СуммаСНДС_Вал),
СУММА(СуммаБезНДС_Руб),
СУММА(СуммаСНДС_Руб)
ПО
ОБЩИЕ
АВТОУПОРЯДОЧИВАНИЕ
Ключевой момент проблемы заключался в пакете, где таблицы ВТ_Врем и ВТ_Суммы соединялись по полям ЗаказКлиента и ИдентификаторСтроки. В консоли запросов результат был корректным, но в СКД появлялись дубликаты.
Давайте выясним основные причины, по которым СКД может задваивать строки, даже если исходный запрос кажется корректным:
Особенности оптимизации запросов СКД. СКД не просто выполняет ваш запрос "как есть". Она может модифицировать его, добавляя свои группировки, фильтры или, наоборот, опуская их там, где это ожидается. Это поведение может существенно отличаться от прямого выполнения запроса в консоли. Например, СКД может "додумывать" группировки, если в выборке присутствуют агрегатные функции (так называемые "ресурсы" СКД, где часто требуется расчет нарастающего итога или дельты), но если таких функций нет, она может просто выводить все строки, удовлетворяющие условиям соединения, без их схлопывания.
Наш коллега подтвердил это наблюдение: если в выборке из временной таблицы есть ресурс (числовой показатель с агрегатной функцией), то поля группируются автоматически. Если же выбирать поля без ресурсов, то задвоение происходит. Это критически важное понимание работы СКД.
Отсутствие явной группировки или уникального ключа во временных таблицах. Если в одной из таблиц, участвующих в левом соединении (чаще всего в правой), по каким-то причинам существуют несколько строк, которые удовлетворяют условиям связи для одной строки из левой таблицы, СКД вернет каждую такую комбинацию. Это приводит к задвоению строк с точки зрения отчета.
Например, если по одному ЗаказКлиента и ИдентификаторСтроки в таблице ВТ_Суммы существует две записи (что в данном случае было исключено, но является типичной причиной), то каждая строка из ВТ_Врем будет соединена с обеими записями из ВТ_Суммы, создавая дубликат.
Некорректная настройка структуры отчета СКД. Задвоение может происходить из-за того, что в настройках СКД не указаны необходимые поля для группировки или не установлены связи на уровне наборов данных. Если в настройках отчета отсутствует достаточная детализация или агрегация, СКД может выводить повторяющиеся записи.
Разные уровни детализации полей. Если соединяются таблицы, имеющие разную степень детализации по общим полям, это также может привести к появлению дубликатов. Например, одна таблица содержит данные по дням, другая — по месяцам, и соединение происходит по дате без должной агрегации.
Прежде чем переходить к решениям, необходимо правильно диагностировать проблему. Рассмотрим, как провести эффективную диагностику:
Проверка вывода временных таблиц в консоли запросов. Для начала убедитесь, что каждая временная таблица (ВТ_Заказы, ВТ_ТолькоЗаказы, ВТ_ТЧ_Заказов, ВТ_Врем, ВТ_Суммы и т.д.) действительно содержит уникальные данные по ключевым полям, которые будут использоваться для последующих соединений. Выполните запросы для каждой временной таблицы по отдельности и проверьте наличие дубликатов по полям, участвующим в условиях соединения (например, ЗаказКлиента, Номенклатура, КодСтроки, ИдентификаторСтроки). Если дубликаты есть уже на этом этапе, значит, их нужно устранять внутри соответствующей временной таблицы.
Проверка вывода временных таблиц через СКД. Это очень важный шаг, который часто упускается. Поскольку СКД может модифицировать запрос, необходимо посмотреть, какие данные СКД "видит" в каждой временной таблице. Для этого создайте отдельные наборы данных в СКД для каждой временной таблицы и выведите их в простом отчете. Это позволит вам увидеть, появляются ли дубликаты уже на этапе формирования промежуточных временных таблиц внутри СКД, прежде чем они будут объединены в итоговый результат.
Например, наш коллега проверил таблицы ВТ_Врем (левая) и ВТ_Суммы (правая) именно через СКД и подтвердил отсутствие задвоений по полям ЗаказКлиента и ИдентификаторСтроки в этих таблицах по отдельности. Это сужает область поиска проблемы до самого соединения или последующей обработки СКД.
Анализ условий соединения. Внимательно перепроверьте условия соединения между таблицами. Убедитесь, что все ключевые поля, необходимые для уникальной идентификации строки, включены в условие соединения. В нашем случае, соединение между ВТ_Врем и ВТ_Суммы происходит по ЗаказКлиента и ИдентификаторСтроки.
Теперь, когда мы понимаем причины и методы диагностики, рассмотрим конкретные подходы к решению проблемы задвоения строк.
Как показал наш коллега, самое эффективное решение – это явная группировка временных таблиц по ключевым полям перед использованием их в соединении. Это гарантирует, что каждая временная таблица содержит уникальные комбинации полей, которые будут использоваться в условиях соединения.
Принцип: Если временная таблица (например, правая часть в ЛЕВОЕ СОЕДИНЕНИЕ) потенциально может содержать несколько записей по одному набору ключевых полей, используйте оператор СГРУППИРОВАТЬ ПО и агрегатные функции для схлопывания этих строк до одной уникальной комбинации.
Рассмотрим, как можно модифицировать запрос для таблицы ВТ_Врем, чтобы исключить задвоения, если они могли возникать на этом этапе, или для ВТ_Суммы, если она могла содержать дубликаты. В данном случае, проблема была решена группировкой именно таблицы ВТ_Врем.
Пример модификации пакета для ВТ_Врем:
Исходный пакет:
ВЫБРАТЬ
ВТ_Заказы.ЗаказКлиента КАК ЗаказКлиента,
ВТ_Заказы.Номенклатура КАК Номенклатура,
ВТ_ТЧ_Заказов.ИдентификаторСтроки КАК ИдентификаторСтроки,
ВТ_Заказы.СуммаСНДС КАК СуммаСНДС,
ВТ_ТЧ_Заказов.ДатаОтгрузки КАК ДатаОтгрузки
ПОМЕСТИТЬ ВТ_Врем
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТЧ_Заказов КАК ВТ_ТЧ_Заказов
ПО ВТ_Заказы.ЗаказКлиента = ВТ_ТЧ_Заказов.ЗаказКлиента
И ВТ_Заказы.Номенклатура = ВТ_ТЧ_Заказов.Номенклатура
И ВТ_Заказы.КодСтроки = ВТ_ТЧ_Заказов.КодСтроки
;
Модифицированный пакет с группировкой:
ВЫБРАТЬ
ВТ_Заказы.ЗаказКлиента КАК ЗаказКлиента,
ВТ_Заказы.Номенклатура КАК Номенклатура,
ВТ_ТЧ_Заказов.ИдентификаторСтроки КАК ИдентификаторСтроки,
СУММА(ВТ_Заказы.СуммаСНДС) КАК СуммаСНДС,
МАКСИМУМ(ВТ_ТЧ_Заказов.ДатаОтгрузки) КАК ДатаОтгрузки
ПОМЕСТИТЬ ВТ_Врем
ИЗ
ВТ_Заказы КАК ВТ_Заказы
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТЧ_Заказов КАК ВТ_ТЧ_Заказов
ПО ВТ_Заказы.ЗаказКлиента = ВТ_ТЧ_Заказов.ЗаказКлиента
И ВТ_Заказы.Номенклатура = ВТ_ТЧ_Заказов.Номенклатура
И ВТ_Заказы.КодСтроки = ВТ_ТЧ_Заказов.КодСтроки
СГРУППИРОВАТЬ ПО
ВТ_Заказы.ЗаказКлиента,
ВТ_Заказы.Номенклатура,
ВТ_ТЧ_Заказов.ИдентификаторСтроки
ИНДЕКСИРОВАТЬ ПО
ЗаказКлиента,
Номенклатура,
ИдентификаторСтроки
;
Важные моменты при группировке:
СГРУППИРОВАТЬ ПО, должны быть агрегированы с помощью функций (СУММА, МАКСИМУМ, МИНИМУМ, СРЕДНЕЕ и т.д.). Выбор функции зависит от того, как вы хотите обрабатывать дублирующиеся значения. Например, для суммы используйте СУММА(), для даты, если важна только одна, используйте МАКСИМУМ() или МИНИМУМ().СГРУППИРОВАТЬ ПО все поля, которые должны однозначно идентифицировать строку в результате соединения.После применения группировки к ВТ_Врем проблема задвоения строк в отчете СКД была решена. Это подтверждает, что СКД более чувствительна к потенциальным дубликатам в промежуточных данных, если явная группировка отсутствует и в выборке нет агрегатных функций (ресурсов).
Иногда проблему можно решить, изменив структуру запроса, особенно если используются вложенные запросы или неоптимальные соединения. Например, в пакете ВТ_Суммы:
ВЫБРАТЬ
СуммыДокументовВВалютахУчета.Регистратор КАК ЗаказКлиента,
СуммыДокументовВВалютахУчета.ИдентификаторСтроки КАК ИдентификаторСтроки,
// ... прочие поля
ПОМЕСТИТЬ ВТ_Суммы
ИЗ
РегистрСведений.СуммыДокументовВВалютахУчета КАК СуммыДокументовВВалютахУчета
ГДЕ
СуммыДокументовВВалютахУчета.Регистратор В
(ВЫБРАТЬ
ВТ_ТолькоЗаказы.ЗаказКлиента КАК ЗаказКликаз
ИЗ
ВТ_ТолькоЗаказы КАК ВТ_ТолькоЗаказы)
Вместо использования вложенного запроса в условии ГДЕ, можно применить внутреннее соединение, что часто улучшает план запроса и исключает неявные проблемы:
ВЫБРАТЬ
СуммыДокументовВВалютахУчета.Регистратор КАК ЗаказКлиента,
СуммыДокументовВВалютахУчета.ИдентификаторСтроки КАК ИдентификаторСтроки,
// ... прочие поля
ПОМЕСТИТЬ ВТ_Суммы
ИЗ
ВТ_ТолькоЗаказы КАК ВТ_ТолькоЗаказы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СуммыДокументовВВалютахУчета КАК СуммыДокументовВВалютахУчета
ПО ВТ_ТолькоЗаказы.ЗаказКлиента = СуммыДокументовВВалютахУчета.Регистратор
Также, если нет необходимости в предварительной агрегации, можно попробовать напрямую присоединять регистр к ВТ_Врем, минуя создание промежуточной временной таблицы ВТ_Суммы. Это упрощает структуру и снижает вероятность ошибок.
РАЗЛИЧНЫЕ (DISTINCT)В некоторых случаях, если задвоение происходит из-за особенностей данных и нет возможности явно сгруппировать все промежуточные таблицы, можно использовать оператор РАЗЛИЧНЫЕ (DISTINCT) в конечной выборке. Однако этот метод имеет существенные недостатки:
Производительность: Для больших объемов данных РАЗЛИЧНЫЕ может быть очень неэффективным, так как СУБД придется проверять каждую строку на уникальность.
Маскировка проблемы: РАЗЛИЧНЫЕ не решает корневую проблему задвоения, а лишь скрывает ее, что может привести к неправильной интерпретации данных или повторному возникновению проблемы при изменении запроса.
Поэтому к использованию РАЗЛИЧНЫЕ следует прибегать только в крайних случаях, предварительно исчерпав все остальные возможности.
В настройках самой схемы компоновки данных можно влиять на поведение отчета:
Связи наборов данных: Убедитесь, что для каждого соединения между наборами данных в СКД определены корректные связи полей. Если вы используете несколько наборов данных, связанных между собой, СКД использует эти связи для объединения информации. Неправильно настроенные связи могут приводить к некорректному объединению и появлению дубликатов.
Характеристики связей: В СКД для каждой связи можно указать ее характеристику (например, "Обязательная", "Необязательная", "Левое соединение"). Это позволяет более точно управлять поведением СКД при объединении данных.
Группировки в отчете: На вкладке "Настройки" отчета СКД создайте необходимые группировки по ключевым полям, которые должны однозначно идентифицировать строку. Если вы хотите получить уникальную строку по ЗаказКлиента и Номенклатура, обязательно включите эти поля в группировку. СКД будет автоматически агрегировать ресурсы по этим группировкам, что может устранить видимые дубликаты.
Проблема задвоения строк в отчетах СКД при левом соединении, когда запрос в консоли работает корректно, чаще всего связана с особенностями интерпретации и оптимизации запроса самой Системой Компоновки Данных. Главный вывод, который мы можем сделать из данного случая – СКД может вести себя по-разному в зависимости от наличия агрегатных функций (ресурсов) в выборке временных таблиц. Если ресурсы отсутствуют, СКД может не применять неявные группировки, выводя все возможные комбинации строк.
Наиболее надежным и универсальным решением является явная группировка временных таблиц по ключевым полям с использованием агрегатных функций для всех остальных полей. Это обеспечивает, что на каждом этапе соединения СКД работает с однозначно идентифицированными строками, предотвращая нежелательные дубликаты.
Всегда помните о необходимости тщательно диагностировать проблему, проверяя содержимое временных таблиц как напрямую в консоли, так и через СКД, чтобы точно определить этап возникновения дубликатов.