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

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

Основные причины некорректного поведения СКД

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

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

Рекомендации по диагностике и устранению проблемы

Для эффективного устранения проблемы с дублированием строк в отчете СКД, мы предлагаем пошаговый подход:

Шаг 1: Проанализируйте окончательный запрос СКД

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

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

   
   // В модуле объекта отчета или в форме отчета
   Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
       СтандартнаяОбработка = Ложь;
       КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
       МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, КомпоновщикНастроек.Настройки, ДанныеРасшифровки);
       ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
       ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, , ДанныеРасшифровки);
   
       // Получаем текст запроса, который будет выполнен
       ТекстЗапроса = ПроцессорКомпоновки.ПолучитьПараметры().Отладка.ТекстЗапроса;
       Сообщить(ТекстЗапроса); // Выводим запрос для анализа
       
       ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
       ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
       ПроцессорВывода.Вывести(ПроцессорКомпоновки, Истина);
   КонецПроцедуры
   

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

2. Использование консоли отчетов СКД. Существуют специализированные внешние обработки-"консоли отчетов СКД", которые позволяют не только выполнять отчеты, но и просматривать сгенерированный СКД текст запроса.

Шаг 2: Детально проверьте настройки СКД

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

1. Настройка группировок. Убедитесь, что все поля, которые должны однозначно идентифицировать строку, включены в структуру отчета либо как поля группировки, либо, что особенно важно, в группировку "Детальные записи". Если какое-то уникальное поле отсутствует в детализации или группировке, СКД может посчитать строки одинаковыми.
2. Определение ресурсов. Проверьте корректность определения ресурсов и их выражений расчета. Если ресурс агрегируется по неправильному набору полей или используется неверная агрегатная функция, это может привести к некорректным итогам. Укажите функцию "Сумма" для числовых полей, которые должны сворачиваться.
3. Параметры запроса. Удостоверьтесь, что параметры запроса (особенно даты, такие как &НачалоПериода и &КонецПериода) передаются в СКД корректно, с учетом времени (начало/конец дня, месяца, квартала). Неточности в параметрах могут приводить к попаданию или непопаданию данных в период, что влияет на остатки и обороты.
4. Связи наборов данных. При наличии нескольких наборов данных, перепроверьте условия связей между ними. Ошибки в условиях ПО при соединениях (особенно ПОЛНОЕ СОЕДИНЕНИЕ или ЛЕВОЕ СОЕДИНЕНИЕ) могут вызывать дублирование или потерю строк.
5. Роль полей. Для полей, связанных с остатками и оборотами (например, ОстатокНаНачало, Приход, Расход), убедитесь, что в настройках полей набора данных СКД корректно указана "Роль" (например, "Остаток", "Оборот", "Период", "Измерение") для правильного расчета итогов и построения временных рядов.
6. Установка полей как обязательных. В настройках полей набора данных вы можете пометить некоторые поля как "Обязательные". Это может предотвратить их исключение СКД из запроса при оптимизации, что является причиной дублирования. Вернемся к вашему вопросу, одно из предложений было: "Сделай все поля обязательными". Это означает, что если СКД отбрасывает какое-то поле, которое делает строку уникальной, то явное указание его как обязательного принудит СКД оставить его, сохраняя уникальность строк.

Шаг 3: Используйте вложенные запросы для контроля над оптимизацией

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

ВЫБРАТЬ
    Т1.Поле1,
    Т1.Поле2,
    Т2.Поле3
ИЗ
    (ВЫБРАТЬ
        Источник1.Поле1 КАК Поле1,
        Источник2.Поле2 КАК Поле2
    ИЗ
        РегистрСведений.Источник1 КАК Источник1
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Источник2 КАК Источник2
            ПО Источник1.Ссылка = Источник2.Ссылка
    ГДЕ
        Источник1.Дата МЕЖДУ &Начало И &Конец
    ) КАК Т1
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    (ВЫБРАТЬ
        Источник3.ПолеX КАК ПолеX,
        Источник3.Поле3 КАК Поле3
    ИЗ
        РегистрСведений.Источник3 КАК Источник3
    ГДЕ
        Источник3.Дата МЕЖДУ &Начало И &Конец
    ) КАК Т2
    ПО Т1.ПолеX = Т2.ПолеX

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

Шаг 4: Применение ВЫБРАТЬ РАЗЛИЧНЫЕ

Если дублирование данных происходит на уровне самого запроса, даже до обработки СКД, добавление ключевого слова РАЗЛИЧНЫЕ после ВЫБРАТЬ в начале основного или проблемного вложенного запроса может помочь устранить повторяющиеся строки. Например:

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ.Поле1,
    ВТ.Поле2,
    ...

Однако помните, что использование РАЗЛИЧНЫЕ может быть ресурсоемким для больших объемов данных и следует применять его с осторожностью, понимая, почему именно возникают дубликаты на этом этапе.

Шаг 5: Полное пересоздание схемы СКД

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

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