Разработка отчетов в системе компоновки данных (СКД) часто преподносит сюрпризы. Типичная ситуация: программист пишет запрос, тестирует его в консоли запросов — всё работает идеально. Но стоит перенести этот запрос в схему компоновки данных, как в готовом отчете начинают выводиться некорректные («левые») данные, пропадают нужные строки или дублируются группировки. Особенно ярко эта проблема проявляется при попытке получить топ-N элементов (например, «Первые 5 популярных поставщиков»), объединив остальные данные в группу «Прочее».
В этой статье мы подробно разберем, почему один и тот же запрос выдает разные результаты в консоли и в СКД, проанализируем внутренние механизмы работы компоновщика макета и научимся правильно проектировать такие отчеты.
Рассмотрим классическую задачу. Нам необходимо получить список из 5 наиболее популярных контрагентов по объему закупок (оборотам счета 60), а всех остальных контрагентов объединить под общим именем «Прочее». Для этого в запросе обычно используется следующий алгоритм:
NULL), то с помощью конструкции ВЫБОР КОГДА ... ЕСТЬ NULL ТОГДА "Прочее" ему присваивается текстовое представление «Прочее».В консоли запросов такой подход отрабатывает корректно. Однако в СКД отчет выводит совершенно случайных контрагентов. Выясним, почему это происходит.
Главная причина расхождения кроется в том, что СКД — это не просто инструмент для выполнения SQL-запросов, а интеллектуальная система. Перед отправкой запроса в СУБД СКД выполняет его **модификацию** (если включено стандартное «Автозаполнение»). Она анализирует, какие поля пользователь выбрал в настройках отчета (в группировках, колонках или отборах), и безжалостно удаляет из итогового запроса все «лишние», по ее мнению, поля для экономии ресурсов.
Если в вашем вложенном запросе или во временной таблице используется конструкция ПЕРВЫЕ 5 с упорядочиванием по полю Сумма, но само поле Сумма (или промежуточные поля группировки) не выведено напрямую в финальные настройки компоновки, СКД может исключить это поле из выборки вложенного запроса.
В результате СУБД получает команду выбрать ПЕРВЫЕ 5 записей, но **без сортировки** (поскольку поле сортировки было вырезано оптимизатором СКД). СУБД возвращает первые попавшиеся на физическом диске 5 записей, которые оказываются случайными («левыми») контрагентами.
Чтобы запретить СКД модифицировать вложенные таблицы и удалять из них поля, критически важные для сортировки и группировки, мы должны использовать настройки ролей полей набора данных.
Разберем по шагам, как это сделать:
Сумма, Организация, Контрагент).Установка флага Обязательное жестко указывает компоновщику СКД: «Это поле должно присутствовать в результирующем запросе к СУБД всегда, независимо от того, выбрал его пользователь в настройках отчета или нет». Это сохранит корректную сортировку внутри конструкции ПЕРВЫЕ.
Вторая частая причина несовпадения данных — разница в обработке параметров начала и конца периода. В консоли запросов разработчики обычно заполняют параметры вручную и указывают точное время, например, 31.10.2019 23:59:59.
В СКД для удобства пользователя часто используется стандартный параметр Период типа «СтандартныйПериод». Если в схеме компоновки не настроено выражение для приведения дат, СКД может передать в запрос значение конца периода как 31.10.2019 00:00:00. Из-за этого целый день операций (все закупки за последнее число месяца) выпадает из расчетов СКД. Естественно, топ-5 популярных контрагентов меняется.
Чтобы исключить эту ошибку, проанализируем вкладку «Параметры» в СКД. Для параметра КонецПериода необходимо прописать выражение:
КонецПериода(&КонецПериода, "День")
Это гарантирует, что граница периода всегда будет автоматически сдвигаться на последнюю секунду дня, обеспечивая полную идентичность расчетов с консолью запросов.
В сообщениях форума развернулась дискуссия о допустимости левых соединений с виртуальными таблицами: «Иникогда слева не ставь виртуальные таблицы!». Давайте разберем техническую сторону этого утверждения.
Использование виртуальных таблиц (таких как РегистрБухгалтерии.Хозрасчетный.Обороты) непосредственно в левых или правых соединениях является грубой ошибкой оптимизации запросов в 1С. Платформа трансформирует обращение к виртуальной таблице в сложный подзапрос к СУБД. При соединении такого подзапроса с другими таблицами СУБД не может построить эффективный план выполнения (выбрать правильные индексы и метод соединения таблиц), что приводит к катастрофическому падению производительности или непредсказуемым результатам при неявном приведении типов.
Правильный алгоритм работы с виртуальными таблицами в СКД выглядит так:
Посмотрим на пример правильной структуры пакета запросов:
// ПАКЕТ 1: Получаем ТОП-5 поставщиков во временную таблицу
ВЫБРАТЬ ПЕРВЫЕ 5
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты) КАК Контрагент,
ХозрасчетныйОбороты.Организация КАК Организация,
СУММА(ХозрасчетныйОбороты.СуммаОборотКт) КАК Сумма
ПОМЕСТИТЬ ВТ_ТопПоставщиков
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачалоПериода, &КонецПериода, Авто, Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками)), , , КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Товары)), ) КАК ХозрасчетныйОбороты
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты),
ХозрасчетныйОбороты.Организация
УПОРЯДОЧИТЬ ПО
Сумма УБЫВ
ИНДЕКСИРОВАТЬ ПО
Контрагент,
Организация;
////////////////////////////////////////////////////////////////////////////////
// ПАКЕТ 2: Получаем общие обороты по всем поставщикам во временную таблицу
ВЫБРАТЬ
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты) КАК Контрагент,
ХозрасчетныйОбороты.Организация КАК Организация,
СУММА(ХозрасчетныйОбороты.СуммаОборотКт) КАК Сумма
ПОМЕСТИТЬ ВТ_ВсеОбороты
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачалоПериода, &КонецПериода, Авто, Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками)), , , КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Товары)), ) КАК ХозрасчетныйОбороты
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты),
ХозрасчетныйОбороты.Организация
ИНДЕКСИРОВАТЬ ПО
Контрагент,
Организация;
////////////////////////////////////////////////////////////////////////////////
// ПАКЕТ 3: Соединяем две временные таблицы и выделяем "Прочее"
ВЫБРАТЬ
ВТ_ВсеОбороты.Организация КАК Организация,
ВЫБОР
КОГДА ВТ_ТопПоставщиков.Контрагент ЕСТЬ NULL ТОГДА "Прочее"
ИНАЧЕ ВТ_ВсеОбороты.Contragent // Для СКД лучше использовать типизированное поле
КОНЕЦ КАК ПоставщикПредставление,
СУММА(ВТ_ВсеОбороты.Сумма) КАК Сумма
ИЗ
ВТ_ВсеОбороты КАК ВТ_ВсеОбороты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТопПоставщиков КАК ВТ_ТопПоставщиков
ПО ВТ_ВсеОбороты.Контрагент = ВТ_ТопПоставщиков.Contragent
И ВТ_ВсеОбороты.Организация = ВТ_ТопПоставщиков.Организация
СГРУППИРОВАТЬ ПО
ВТ_ВсеОбороты.Организация,
ВЫБОР
КОГДА ВТ_ТопПоставщиков.Контрагент ЕСТЬ NULL ТОГДА "Прочее"
ИНАЧЕ ВТ_ВсеОбороты.Контрагент
КОНЕЦ
Обратите внимание на конструкцию ВЫБОР КОГДА ... ТОГДА "Прочее" ИНАЧЕ Контрагент КОНЕЦ. Это поле имеет составной тип: оно может быть строкой ("Прочее") или ссылкой на справочник (Справочник.Контрагенты). СКД крайне негативно относится к полям составного типа, содержащим одновременно ссылочные типы данных и строки, особенно когда по этим полям строятся группировки отчета.
Это приводит к тому, что СКД не может корректно сопоставить значения и выводит строку «Прочее» несколько раз (для каждого контрагента, попавшего под это условие, вместо того чтобы схлопнуть их в одну строку). Чтобы решить эту проблему в СКД, рекомендуется использовать вкладку «Вычисляемые поля» или настроить выгрузку представления в самом запросе, а еще лучше — использовать предопределенный элемент справочника (например, завести в справочнике контрагента с наименованием «Все остальные») вместо текстового литерала.
Если вы работаете на современных версиях платформы 1С:Предприятие (начиная с 8.3.20), то задачу ранжирования и разделения на топ-группы можно решить гораздо изящнее и быстрее без дублирования обращений к виртуальным таблицам. Для этого используются **оконные функции**.
Рассмотрим пример современного и оптимального запроса:
ВЫБРАТЬ
ХозрасчетныйОбороты.Организация КАК Организация,
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты) КАК Контрагент,
СУММА(ХозрасчетныйОбороты.СуммаОборотКт) КАК Сумма,
// Применяем оконную функцию автонумерации по убыванию суммы в разрезе организации
АВТОНОМЕР_ЗАПИСИ() ПО (РАЗДЕЛИТЬ ПО ХозрасчетныйОбороты.Организация УПОРЯДОЧИТЬ ПО СУММА(ХозрасчетныйОбороты.СуммаОборотКт) УБЫВ) КАК НомерМеста
ПОМЕСТИТЬ ВТ_Ранжированная
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачалоПериода, &КонецПериода, Авто, Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПоставщикамиИПодрядчиками)), , , КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Товары)), ) КАК ХозрасчетныйОбороты
СГРУППИРОВАТЬ ПО
ХозрасчетныйОбороты.Организация,
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты);
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Ранжированная.Организация КАК Организация,
ВЫБОР
КОГДА ВТ_Ранжированная.НомерМеста <= 5 ТОГДА ВТ_Ранжированная.Контрагент
ИНАЧЕ "Прочее"
КОНЕЦ КАК Поставщик,
СУММА(ВТ_Ранжированная.Сумма) КАК Сумма
ИЗ
ВТ_Ранжированная КАК ВТ_Ранжированная
СГРУППИРОВАТЬ ПО
ВТ_Ранжированная.Организация,
ВЫБОР
КОГДА ВТ_Ранжированная.НомерМеста <= 5 ТОГДА ВТ_Ранжированная.Контрагент
ИНАЧЕ "Прочее"
КОНЕЦ
Использование оконной функции АВТОНОМЕР_ЗАПИСИ() позволяет нам избавиться от тяжелых левых соединений. Мы за один проход по регистру ранжируем поставщиков внутри каждой организации и делим их на категории. Этот запрос работает стабильно, предсказуемо оптимизируется СУБД и не искажает данные при переносе в СКД.
При переносе сложных запросов из консоли в СКД всегда помните о следующих правилах:
ПЕРВЫЕ (или TOP), обязательно устанавливайте роль «Обязательное» для всех полей сортировки и связей во вкладке «Наборы данных» СКД.