При работе с Системой компоновки данных (СКД) разработчики часто сталкиваются с классическим ограничением платформы «1С:Предприятие»: вкладка «Связи наборов данных» рассчитана преимущественно на простые соединения по равенству значений (оператор =). Попытка настроить связь по условию диапазона, например, когда требуется проверить вхождение даты операции в произвольный период (ДатаОт <= Дата и ДатаДо >= Дата), приводит к некорректной работе компоновщика. Вместо ожидаемой фильтрации система выполняет декартово произведение (полное соединение), дублируя строки для каждого периода.
В этой статье мы подробно проанализируем, почему возникает эта проблема, и разберем четыре практических способа реализации связей по неравенству в СКД в зависимости от версии вашей платформы и требований к производительности.
Инструмент связи наборов данных в СКД устроен принципиально иначе, чем секция ПО в классическом SQL-запросе. Поле «Условие связи», вопреки ожиданиям многих начинающих разработчиков, предназначено главным образом для организации собственной иерархии внутри одного набора данных, а не для гибкого сопоставления разных наборов. Когда компоновщик пытается связать два независимых источника данных (например, запрос к базе данных и внешнюю таблицу значений) по условиям «больше» или «меньше», он не может транслировать это в эффективный SQL-запрос со сравнением. В результате СКД возвращает полный набор данных для каждого шага итерации, игнорируя наложенные границы.
Тем не менее, решать задачу сопоставления данных с произвольными периодами нужно. Ниже приведены проверенные и наиболее эффективные архитектурные решения этой проблемы.
Этот метод основан на передаче значений дат из одного набора данных в качестве параметров для фильтрации другого набора данных. Мы можем сделать так, чтобы для каждой строки с периодом СКД выполняла запрос получения данных, подставляя границы этого периода «на лету».
Разберем этот метод по шагам:
ДатаОт, ДатаДо, Представление) будет Источником связи. Набор данных 1 (запрос к БД, содержащий детальные записи с полем Дата) будет Приемником.
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
Продажи.СуммаОборот КАК Сумма,
Продажи.Период КАК Дата
ИЗ
РегистрНакопления.Продажи.Обороты(,, Регистратор, ) КАК Продажи
ГДЕ
Продажи.Период МЕЖДУ &ДатаНачалаПериода И &ДатаОкончанияПериода
НаборДанных2, Приемник — НаборДанных1. Выражение-источник — ДатаОт. Выражение-приемник оставляем пустым. Параметр — ДатаНачалаПериода.НаборДанных2, Приемник — НаборДанных1. Выражение-источник — ДатаДо. Выражение-приемник оставляем пустым. Параметр — ДатаОкончанияПериода.Преимущества: Метод полностью поддерживается всеми версиями платформы «1С:Предприятие 8» и легко настраивается в конструкторе.
Недостатки: Если во внешней таблице периодов будет содержаться большое количество строк, производительность отчета резко снизится. СКД будет выполнять отдельный SQL-запрос к базе данных для каждой строки периода (так называемая проблема N+1 запросов).
Начиная с версии платформы 8.3.17, разработчики получили возможность передавать существующий МенеджерВременныхТаблиц непосредственно в процессор компоновки данных. Это позволяет отказаться от разделения данных на два набора в схеме компоновки и выполнить полноценное быстрое соединение по неравенству на стороне СУБД в рамках одного запроса.
Проанализируем программную реализацию этого подхода:
ПриКомпоновкеРезультата.Рассмотрим пример кода в модуле отчета:
Процедура ПриКомпоновкеРезультата(ДокументРезультат, ДанныеРасшифровки, СтандартнаяОбработка)
СтандартнаяОбработка = Ложь;
// Получаем схему и настройки по умолчанию
СхемаКомпоновкиДанных = ПолучитьМакет("ОсновнаяСхемаКомпоновкиДанных");
Настройки = КомпоновщикНастроек.ПолучитьНастройки();
// Создаем Менеджер Временных Таблиц (МВТ)
МВТ = Новый МенеджерВременныхТаблиц;
// Помещаем внешнюю таблицу (например, ТЗПериоды) во временную таблицу ВТ_Периоды
ЗапросПодготовки = Новый Запрос;
ЗапросПодготовки.МенеджерВременныхТаблиц = МВТ;
ЗапросПодготовки.Текст =
"ВЫБРАТЬ
| ТаблицаИсточник.ДатаОт КАК ДатаОт,
| ТаблицаИсточник.ДатаДо КАК ДатаДо,
| ТаблицаИсточник.Представление КАК Представление
|ПОМЕСТИТЬ ВТ_Периоды
|ИЗ
| &ВнешняяТаблица КАК ТаблицаИсточник";
// ВнешняяТаблицаПериодов — это переданная в отчет таблица значений
ЗапросПодготовки.УстановитьПараметр("ВнешняяТаблица", ВнешняяТаблицаПериодов);
ЗапросПодготовки.Выполнить();
// Выполняем компоновку макета
КомпоновщикМакета = Новый КомпоновщикМакетаКомпоновкиДанных;
МакетКомпоновки = КомпоновщикМакета.Выполнить(СхемаКомпоновкиДанных, Настройки, ДанныеРасшифровки);
// Инициализируем процессор компоновки, передавая МВТ шестым параметром!
ПроцессорКомпоновки = Новый ПроцессорКомпоновкиДанных;
ПроцессорКомпоновки.Инициализировать(МакетКомпоновки, , ДанныеРасшифровки, Истина, , МВТ);
// Выводим результат
ПроцессорВывода = Новый ПроцессорВыводаРезультатаКомпоновкиДанныхВТабличныйДокумент;
ПроцессорВывода.УстановитьДокумент(ДокументРезультат);
ПроцессорВывода.Вывести(ПроцессорКомпоновки);
КонецПроцедуры
В самой схеме СКД создаем единственный Набор данных — запрос, в котором безбоязненно обращаемся к таблице ВТ_Периоды:
ВЫБРАТЬ
Данные.Данные КАК Данные,
Данные.Дата КАК Дата,
ВТ_Периоды.Представление КАК ПериодПредставление
ИЗ
РегистрСведений.РегистрПродаж КАК Данные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Периоды КАК ВТ_Периоды
ПО Данные.Дата МЕЖДУ ВТ_Периоды.ДатаОт И ВТ_Периоды.ДатаДо
Преимущества: Максимальная производительность. Соединение выполняется СУБД за один проход, задействуются индексы таблиц, исключается избыточное чтение данных.
Если вы ограничены более старыми релизами платформы 1С, где прямая передача менеджера временных таблиц в СКД еще не реализована, можно пойти путем передачи таблицы значений в качестве параметра самого запроса схемы компоновки.
Давайте посмотрим, как настроить этот вариант:
ВЫБРАТЬ
ТЗ.ДатаОт КАК ДатаОт,
ТЗ.ДатаДо КАК ДатаДо,
ТЗ.Представление КАК Представление
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
&ВнешняяТаблица КАК ТЗ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Рег.Номенклатура,
Рег.Период,
ВТ_Периоды.Представление
ИЗ
РегистрНакопления.Продажи КАК Рег
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Периоды КАК ВТ_Периоды
ПО Рег.Период МЕЖДУ ВТ_Периоды.ДатаОт И ВТ_Периоды.ДатаДо
ВнешняяТаблица на вкладке «Параметры».
КомпоновщикНастроек.Настройки.ПараметрыДанных.УстановитьЗначениеПараметра("ВнешняяТаблица", ВашаТаблицаПериодов);
Если структура отчета относительно проста и пользователю не требуется интерактивно перенастраивать группировки по периодам внутри структуры СКД, оптимальным решением может стать перенос логики соединения в программный код модуля.
Начиная с платформы 8.3.15, объект Запрос умеет работать с таблицами значений как с источниками данных напрямую без создания менеджера временных таблиц. Мы можем получить данные из базы, соединить их по условию МЕЖДУ с таблицей периодов прямо в коде, а полученный плоский результат передать в СКД как единственный Набор данных — Объект:
// В модуле отчета
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Продажи.Номенклатура КАК Номенклатура,
| Продажи.Период КАК Дата,
| Периоды.Представление КАК ПредставлениеПериода
|ИЗ
| &ТаблицаПродаж КАК Продажи
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ &ТаблицаПериодов КАК Периоды
| ПО Продажи.Период МЕЖДУ Периоды.ДатаОт И Периоды.ДатаДо";
Запрос.УстановитьПараметр("ТаблицаПродаж", ПолучитьТаблицуПродажИзБД());
Запрос.УстановитьПараметр("ТаблицаПериодов", ТаблицаПериодов);
ТаблицаРезультат = Запрос.Выполнить().Выгрузить();
// Передаем готовую плоскую таблицу в СКД как внешний набор данных
ВнешниеНаборыДанных = Новый Структура;
ВнешниеНаборыДанных.Вставить("ИмяНабораДанныхОбъект", ТаблицаРезультат);
Мы проанализировали ограничения платформы при связывании наборов данных в СКД по неравенствам и рассмотрели четыре эффективных пути решения проблемы. Выбор конкретного подхода зависит от архитектуры вашего решения: