При разработке отчетов на базе Системы компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда один и тот же запрос выдает полный набор данных в консоли запросов, но «теряет» часть строк при выводе в отчет. Особенно ярко это проявляется при работе с физическими таблицами регистров накопления или сведений. Проанализируем, почему так происходит, и разберем механизмы внутренней оптимизации СКД, которые приводят к подобным эффектам.
Рассмотрим пример запроса к основной таблице оборотного регистра РегистрНакопления.ЗаданияНаРаботу. Автор пытается получить простой список всех записей (реестр) за определенный период. В запросе используются поля измерений, ресурсов, а также вычисляемые поля с использованием РАЗНОСТЬДАТ и преобразование строк через ВЫРАЗИТЬ.
ВЫБРАТЬ
ЗаданияНаРаботу.Период КАК Период,
ЗаданияНаРаботу.Регистратор КАК Регистратор,
ЗаданияНаРаботу.НомерСтроки КАК НомерСтроки,
ЗаданияНаРаботу.Активность КАК Активность,
ЗаданияНаРаботу.Организация КАК Организация,
ЗаданияНаРаботу.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
ЗаданияНаРаботу.Контрагент КАК Контрагент,
ЗаданияНаРаботу.Договор КАК Договор,
ЗаданияНаРаботу.ЗаказПокупателя КАК ЗаказПокупателя,
ЗаданияНаРаботу.ВидРабот КАК ВидРабот,
ЗаданияНаРаботу.Сотрудник КАК Сотрудник,
ЗаданияНаРаботу.Номенклатура КАК Номенклатура,
ЗаданияНаРаботу.Характеристика КАК Характеристика,
ЗаданияНаРаботу.ЗагрузкаПлан КАК ЗагрузкаПлан,
ЗаданияНаРаботу.ЗагрузкаФакт КАК ЗагрузкаФакт,
ЗаданияНаРаботу.СуммаПлан КАК СуммаПлан,
ЗаданияНаРаботу.СуммаФакт КАК СуммаФакт,
ВЫРАЗИТЬ(ЗаданияНаРаботу.Комментарий КАК СТРОКА(1024)) КАК Комментарий,
ВЫРАЗИТЬ(ЗаданияНаРаботу.ЗаказПокупателя.Комментарий КАК СТРОКА(1024)) КАК ЗаказПокупателяКомментарий,
ЗаданияНаРаботу.ВремяНачала КАК ВремяНачала,
ЗаданияНаРаботу.ВремяОкончания КАК ВремяОкончания,
РАЗНОСТЬДАТ(ЗаданияНаРаботу.ВремяНачала, ЗаданияНаРаботу.ВремяОкончания, МИНУТА) / 60 КАК Трудоемкость
ИЗ
РегистрНакопления.ЗаданияНаРаботу КАК ЗаданияНаРаботу
ГДЕ
ЗаданияНаРаботу.Период >= &ДатаНачала
И ВЫБОР
КОГДА &ДатаОкончания <> ДАТАВРЕМЯ(1, 1, 1, 0, 0, 0)
ТОГДА ЗаданияНаРаботу.Period <= КОНЕЦПЕРИОДА(&ДатаОкончания, ДЕНЬ)
ИНАЧЕ ИСТИНА
КОНЕЦ
Проблема заключается в следующем: если в настройках СКД создать группировку по набору полей (например, «Сотрудник», «Номенклатура», «Организация») и не включить в этот список поле Период, количество строк в итоговом отчете уменьшается. Если же добавить Период в группировку — данные возвращаются в полном объеме.
Важно понимать, что СКД не выполняет текст запроса в том виде, в котором он написан в НабореДанныхЗапрос. СКД выступает в роли генератора итогового SQL-запроса к базе данных. Процесс происходит следующим образом:
Когда мы указываем в настройках СКД группировку по определенным полям, система фактически преобразует запрос, добавляя инструкцию СГРУППИРОВАТЬ ПО (GROUP BY). Если поле Период (или любое другое уникальное поле, такое как НомерСтроки или Регистратор) не указано в списке полей этой группировки, СКД исключает его из выборки. В результате несколько записей, которые различались только датой или номером строки, становятся идентичными для СУБД, и они «схлопываются» в одну строку.
Выясним причину разного поведения отчета при изменении структуры настроек. В СКД существует два принципиально разных подхода к выводу строк:
1. Использование «Детальных записей»
Если в структуре отчета мы добавляем группировку без указания полей (в интерфейсе она отображается как <Детальные записи>), СКД понимает, что нужно вывести все строки, полученные из источника данных. В этом режиме автоматическая группировка (сворачивание) не применяется. Именно поэтому, как заметил автор, при использовании детальных записей все данные выводятся корректно даже без поля Период.
2. Использование группировок по полям
Как только мы создаем группировку и перечисляем в ней поля, мы даем системе команду: «Сгруппируй результат по этим измерениям». Если в списке полей группировки нет уникального идентификатора записи, данные будут агрегированы. Если для числовых полей (ресурсов) заданы функции агрегации (Сумма, Минимум, Максимум), то значения в строках сложатся или выберутся по правилу. Если же поля не определены как ресурсы в схеме СКД, то при группировке останется только одно уникальное значение из группы, а остальные будут отброшены.
Рассмотрим по шагам, как можно заставить СКД выводить все записи, сохраняя при этом нужную нам структуру группировок.
Проанализируем вкладку «Наборы данных» в конструкторе СКД. Для каждого поля там можно настроить колонку «Роль». Если мы установим для поля Период или Регистратор флаг «Обязательное», это изменит поведение оптимизатора СКД.
Система будет принудительно включать эти поля в итоговый запрос к базе данных, даже если пользователь не выбрал их в настройках отчета и не добавил в группировки. Наличие обязательного уникального поля в итоговом SELECT не позволит СУБД схлопнуть строки при выполнении GROUP BY.
Для регистров накопления и сведений в физических таблицах всегда присутствует поле НомерСтроки. В совокупности с полем Регистратор оно гарантирует стопроцентную уникальность каждой записи в таблице. Разберем, как это применить:
Регистратор и НомерСтроки в список полей запроса.Если пропадание строк критично именно из-за потери числовых данных (например, сумма «схлопнулась», а не сложилась), нужно проверить вкладку «Ресурсы». Посмотрим, добавлены ли поля ЗагрузкаПлан, ЗагрузкаФакт, СуммаПлан и СуммаФакт в список ресурсов и назначена ли им функция Сумма(...). Если поле является ресурсом, СКД при группировке выполнит сложение, и итоговые цифры в отчете будут верными, даже если количество строк уменьшится из-за отсутствия детализации по периодам.
Для того чтобы точно понять, почему данные ведут себя именно так, рекомендуем использовать внешние инструменты отладки, такие как «Консоль системы компоновки данных». Рассмотрим алгоритм проверки:
СГРУППИРОВАТЬ ПО и удалила те поля, которые вы не указали в группировках.Важный технический нюанс: при работе с физическими таблицами (как в нашем примере) стоит также помнить про свойство «Активность». В отличие от виртуальных таблиц оборотов, физическая таблица содержит все записи, включая те, по которым была произведена отмена проведения (если Активность = Ложь). В запросе автора условия по активности нет, поэтому СКД выберет все записи, но механизм группировки может их объединить, если не заданы уникальные поля.
Таким образом, мы выяснили, что «исчезновение» записей — это не ошибка системы, а штатное поведение оптимизатора запросов СКД. Для получения реестра записей всегда используйте режим <Детальные записи>, а при необходимости сложной иерархии группировок не забывайте про роли полей и использование уникальных идентификаторов строк (Период, Регистратор, НомерСтроки).