Почему в отчетах 1С на СКД запрос ВЫБРАТЬ работает как ВЫБРАТЬ РАЗЛИЧНЫЕ?

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

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

В чем заключается проблема?

Представим, что у нас есть пакет запросов, где мы формируем временную таблицу. Изначально данные в этой таблице могут содержать строки, которые отличаются лишь одним полем. Например, две строки: (1, 2, 35) и (1, 2, 48). Они различны по третьему полю. Если в последующем запросе мы выбираем только Поле1 и Поле2, а Поле3 не указываем в списке выбранных полей, то СКД может "оптимизировать" запрос таким образом, что данные для Поле3 будут исключены из результирующего набора. В результате, строки (1, 2, 35) и (1, 2, 48) станут неотличимы (обе превратятся в (1, 2)) и СКД может объединить их, фактически применив неявное РАЗЛИЧНЫЕ.

Рассмотрим пример запроса, демонстрирующий такое поведение:


// Шаг 1: Формируем временную таблицу с двумя строками,
// отличающимися по Поле3
ВЫБРАТЬ
    1 КАК Поле1,
    2 КАК Поле2,
    35 КАК Поле3
ПОМЕСТИТЬ ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    1,
    2,
    48
;
// Ожидаем: ВТ1 содержит две строки: (1, 2, 35) и (1, 2, 48)

// Шаг 2: Создаем еще одну временную таблицу ВТ,
// используя ВТ1 с ВЫБРАТЬ РАЗЛИЧНЫЕ.
// Здесь мы явно выбираем все три поля.
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ1.Поле1 КАК Поле1,
    ВТ1.Поле2 КАК Поле2,
    ВТ1.Поле3 КАК Поле3
ПОМЕСТИТЬ ВТ
ИЗ
    ВТ1 КАК ВТ1
;
// Ожидаем: ВТ также содержит две строки: (1, 2, 35) и (1, 2, 48),
// так как строки ВТ1 были различны по Поле3.

// Шаг 3: Финальный запрос для отчета.
// Здесь мы сознательно не выбираем Поле3.
ВЫБРАТЬ
    ВТ.Поле1 КАК Поле1,
    ВТ.Поле2 КАК Поле2
ИЗ
    ВТ КАК ВТ

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

Еще более критичной ситуация становится при использовании агрегатных функций, например, КОЛИЧЕСТВО(). Если в предыдущем примере вместо ВЫБРАТЬ ВТ.Поле1, ВТ.Поле2 мы напишем:


ВЫБРАТЬ
    ВТ.Поле1 КАК Поле1,
    КОЛИЧЕСТВО(ВТ.Поле2) КАК Поле2
ИЗ
    ВТ КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Поле1

Мы ожидаем получить строку (1, 2), так как исходно было две строки, для которых Поле1 равно 1. Однако, из-за описанной оптимизации СКД, мы получим (1, 1), поскольку СКД посчитает, что для Поле1=1 существует только одна уникальная строка (1, 2).

Почему СКД так себя ведет? Механизмы оптимизации

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

  1. Исключение неиспользуемых полей: Если какое-либо поле из вашей временной таблицы или основного запроса не используется в финальном отчете (не выводится, не участвует в группировках, сортировках, отборах или условном форматировании), СКД может просто удалить его из текста исполняемого запроса. Это основная причина того, что строки, ранее отличавшиеся по этому полю, становятся идентичными и схлопываются.
  2. Модификация текста запроса: Запрос, который вы пишете в СКД, никогда не выполняется платформой в неизменном виде. СКД может добавлять в него служебные поля, изменять параметры периода, накладывать отборы и упорядочивание в зависимости от настроек отчета.
  3. Использование фигурных скобок {}: В запросах СКД фигурные скобки используются для обозначения необязательных частей запроса. Например, вы можете поместить поле или условие в фигурные скобки, и СКД включит его в реальный запрос только в том случае, если оно востребовано в настройках отчета (например, пользователь включил это поле в выбранные поля). Если поле находится в фигурных скобках и не используется, оно будет исключено, что усугубляет проблему.
  4. Оптимизация временных таблиц: Если данные из временных таблиц не используются в конечном запросе, СКД может исключить эти временные таблицы из пакета запросов, даже если вы думаете, что они нужны для промежуточной логики.

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

Решения проблемы: Как заставить СКД работать корректно

1. Настройка полей в схеме компоновки данных

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

  1. Установите признак "Обязательное" для полей:

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

    Пример: Если Поле3 необходимо для сохранения уникальности строк, но его не нужно отображать, мы должны добавить его в список полей набора данных и установить для него свойство "Обязательное". Для этого в конструкторе СКД перейдите на вкладку "Наборы данных", выберите ваш набор данных, затем на вкладке "Поля". Найдите Поле3 и в его свойствах установите флажок "Обязательное".

  2. Выполнение агрегации на закладке "Ресурсы":

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

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

    
КоличествоПоле2 = КОЛИЧЕСТВО(Различные ВТ.Поле2)

    Или, если нужно просто посчитать количество строк:

    
КоличествоСтрок = КОЛИЧЕСТВО(Различные ВТ.Поле1, ВТ.Поле2, ВТ.Поле3)
// Или просто КОЛИЧЕСТВО(1) если уникальность строк уже сохранена

    После этого мы можем использовать этот ресурс в настройках отчета. Группировку при этом устанавливаем по Поле1 на закладке "Настройки" СКД.

  3. Включение всех необходимых полей в результирующий запрос и управление группировками:

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

2. Корректировка текста запроса: Удаление фигурных скобок {}

Один из самых действенных методов, который показал свою эффективность в реальных задачах, заключается в

удалении фигурных скобок {} из текста запроса в схеме компоновки данных. Фигурные скобки используются СКД для обозначения "опциональных" частей запроса – полей, условий, отборов, которые СКД может включать или исключать в зависимости от настроек отчета. Хотя это дает гибкость, оно же является источником непредсказуемого поведения, так как СКД "додумывает" за разработчика, что именно нужно оставить, а что можно отбросить.

Если в вашем запросе присутствуют конструкции вида {Поле1, Поле2 ИЗ Справочник.Номенклатура ГДЕ ...} или ВЫБРАТЬ {Поле1 КАК Поле1}, СКД будет рассматривать эти части как необязательные. Удаление этих скобок и их содержимого (если оно не является обязательной частью запроса) заставляет СКД выполнять запрос максимально близко к тому виду, в котором он был написан. Это существенно снижает вероятность "нежелательной оптимизации", когда СКД самовольно удаляет поля, влияющие на уникальность строк.

Пример: Если ваш запрос в СКД выглядит так:


ВЫБРАТЬ
    1 КАК Поле1,
    2 КАК Поле2,
    35 КАК Поле3
ПОМЕСТИТЬ ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    1,
    2,
    48
;

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ1.Поле1 КАК Поле1,
    ВТ1.Поле2 КАК Поле2,
    {ВТ1.Поле3 КАК Поле3} // Это поле в скобках!
ПОМЕСТИТЬ ВТ
ИЗ
    ВТ1 КАК ВТ1
;

ВЫБРАТЬ
    ВТ.Поле1 КАК Поле1,
    ВТ.Поле2 КАК Поле2
ИЗ
    ВТ КАК ВТ

Проблема может возникать из-за {ВТ1.Поле3 КАК Поле3}. Если Поле3 не используется явно в дальнейшем, СКД может решить не включать его в запрос для ВТ, что приведет к дублированию строк в ВТ. В этом случае, мы должны убрать фигурные скобки:


ВЫБРАТЬ
    1 КАК Поле1,
    2 КАК Поле2,
    35 КАК Поле3
ПОМЕСТИТЬ ВТ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    1,
    2,
    48
;

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ1.Поле1 КАК Поле1,
    ВТ1.Поле2 КАК Поле2,
    ВТ1.Поле3 КАК Поле3 // Теперь без скобок!
ПОМЕСТИТЬ ВТ
ИЗ
    ВТ1 КАК ВТ1
;

ВЫБРАТЬ
    ВТ.Поле1 КАК Поле1,
    ВТ.Поле2 КАК Поле2
ИЗ
    ВТ КАК ВТ

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

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

Дополнительные рекомендации и тонкости

  1. Анализ реального запроса СКД:

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

  2. Использование Менеджера Временных Таблиц (МВТ) при программном формировании:

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

  3. Явное указание типов данных для полей временных таблиц:

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

    
ВЫБРАТЬ
    ВЫРАЗИТЬ(1 КАК ЧИСЛО(10)) КАК Поле1,
    ВЫРАЗИТЬ(2 КАК ЧИСЛО(10)) КАК Поле2,
    ВЫРАЗИТЬ(35 КАК ЧИСЛО(10)) КАК Поле3
ПОМЕСТИТЬ ВТ1
...

  4. Версия платформы:

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

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

← На главную