При разработке сложных отчетов на Системе компоновки данных (СКД) программисты часто сталкиваются с ситуацией, когда необходимо в одном запросе соединить две или более виртуальные таблицы одного и того же регистра (например, РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт), но за разные периоды. Типичный пример: получение оборотов за текущий период и накопленных данных с начала ведения учета до начала этого периода.
Проблема заключается в «интеллектуальном» механизме 1С, который при включенном флаге Автозаполнение анализирует запрос и пытается оптимизировать его. Если СКД видит стандартные параметры &НачалоПериода и &КонецПериода в нескольких таблицах, она может принудительно подставить в них одинаковые значения, игнорируя логику вашего запроса. Рассмотрим подробнее, как это происходит и какими способами можно обойти такое поведение оптимизатора.
Когда мы работаем с виртуальными таблицами в СКД, система автоматически сопоставляет параметры этих таблиц с параметрами схемы компоновки. Если в тексте запроса дважды вызывается одна и та же виртуальная таблица с использованием стандартных имен параметров, СКД считает их избыточными и «схлопывает», подставляя значения из настроек отчета (часто из поля СтандартныйПериод). В итоге, даже если в консоли запросов код отрабатывает верно, в СКД обе таблицы получают идентичные границы дат.
Проанализируем ситуацию на примере запроса, который вызывает трудности:
ВЫБРАТЬ
ХозрасчетныйОборотыДтКт.СубконтоДт1 КАК Шифр,
МАКСИМУМ(КОНЕЦПЕРИОДА(ХозрасчетныйОборотыДтКт.Период, МЕСЯЦ)) КАК ДатаКонецСписания,
МАКСИМУМ(КОНЕЦПЕРИОДА(ЕСТЬNULL(ХозрасчетныйОборотыДтКт1.Период, ДАТАВРЕМЯ(1, 1, 1)), МЕСЯЦ)) КАК ДатаВекторСписания,
СУММА(ХозрасчетныйОборотыДтКт.СуммаОборот) КАК СуммаОборот
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&НачалоПериода, &КонецПериода, ...) КАК ХозрасчетныйОборотыДтКт
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(, &НачалоПериода, ...) КАК ХозрасчетныйОборотыДтКт1
ПО (ХозрасчетныйОборотыДтКт.СубконтоДт1 = ХозрасчетныйОборотыДтКт1.СубконтоДт1)
В данном случае вторая таблица должна получить данные с пустой даты (начала учета) по &НачалоПериода. Однако СКД видит параметр &НачалоПериода и переопределяет его согласно глобальным настройкам периода отчета для всего пакета запроса.
Самый надежный способ «развести» две таблицы — это отказаться от использования предопределенных имен параметров &НачалоПериода и &КонецПериода внутри виртуальных таблиц. Мы создадим свои собственные параметры, которые оптимизатор не будет считать стандартными.
Разберем по шагам, как это реализовать:
Пример исправленного запроса:
ВЫБРАТЬ
ТаблицаПериода.СубконтоДт1 КАК Шифр,
МАКСИМУМ(ТаблицаПериода.Период) КАК ДатаОкончания,
МАКСИМУМ(ТаблицаПрошлого.Период) КАК ДатаНачалаСписания
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&ДатаНачала1, &ДатаКонца1, ...) КАК ТаблицаПериода
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(&ДатаНачала2, &ДатаКонца2, ...) КАК ТаблицаПрошлого
ПО ТаблицаПериода.СубконтоДт1 = ТаблицаПрошлого.СубконтоДт1
Теперь на вкладке Параметры схемы компоновки данных для параметров &ДатаНачала2 и &ДатаКонца2 мы можем прописать выражения, чтобы пользователю не приходилось вводить четыре даты вручную. Например, если у нас есть стандартный параметр ПериодОтчета, то для &ДатаКонца2 (которая должна быть началом текущего периода) мы пишем выражение: &ПериодОтчета.ДатаНачала.
Если запрос сложный и СКД продолжает вести себя непредсказуемо, мы можем прибегнуть к радикальному методу — отключению автозаполнения. Выясним причину, почему это помогает: при снятом флажке Автозаполнение СКД перестает самостоятельно анализировать структуру виртуальных таблиц и их параметры. Она выполняет запрос ровно в том виде, в котором он написан, как это делает консоль запросов.
Однако помните о последствиях:
{}.Существует еще один нюанс работы с объектом СтандартныйПериод. Рассмотрим ситуацию, когда мы хотим сохранить стандартные имена, но заставить их работать правильно. Выяснилось, что если определить параметры Дата1, Дата2, Дата3, Дата4 и связать их с полями СтандартныйПериод.ДатаНачала и СтандартныйПериод.ДатаОкончания в свойствах параметров СКД, система перестает конфликтовать.
Важный момент: если вы используете в запросе конструкцию с фигурными скобками, убедитесь, что вы не оставляете пустых значений там, где СКД ожидает параметр. Например, вместо того чтобы оставлять первый параметр пустым в ОборотыДтКт(, &ДатаНачала, ...), лучше явно передать туда параметр, который по умолчанию будет содержать пустую дату (ДАТАВРЕМЯ(1, 1, 1)), и также поместить его в фигурные скобки.
ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.ОборотыДтКт(
{&НачалоВремен},
{&ДатаНачалаТекущегоПериода},
Месяц, ...
) КАК Таблица1
Проанализировав поведение оптимизатора СКД, мы пришли к выводу, что наиболее «чистым» решением является использование уникальных имен параметров для каждой виртуальной таблицы в рамках одного набора данных. Это позволяет избежать автоматического «схлопывания» периодов и сохраняет возможность использования всех преимуществ автозаполнения полей.
Рекомендация: Всегда проверяйте итоговый запрос, который СКД отправляет к СУБД. Это можно сделать с помощью консоли системы компоновки данных или замерив производительность с помощью профайлера SQL. Если вы видите, что даты в условиях WHERE подменяются на одинаковые — используйте метод с переименованием параметров или связкой через выражения &СтандартныйПериод.ДатаНачала.