При проектировании сложных отчетов в системе компоновки данных (СКД) разработчики часто сталкиваются с необходимостью объединения данных из нескольких регистров накопления (например, остатки товаров на складах, товары переданные, резервы товаров). Для решения этой задачи традиционно используется конструкция ОБЪЕДИНИТЬ ВСЕ. Однако стандартное объединение таблиц «в лоб» приводит к неприятному эффекту — строки в итоговом отчете начинают задваиваться или затраиваться.
В этой статье мы подробно разберем, почему возникает проблема дублирования строк при использовании ОБЪЕДИНИТЬ ВСЕ, как правильно сгруппировать полученные результаты с помощью временных таблиц, а также рассмотрим архитектурные возможности СКД, которые позволяют решить эту задачу без написания громоздкого SQL-кода.
Проанализируем ситуацию на простом примере. Предположим, у нас есть две таблицы: остатки товаров и резервы товаров. Мы хотим получить плоскую таблицу, в которой для каждой номенклатуры будут выведены и остаток, и резерв.
Когда мы выполняем запрос с конструкцией ОБЪЕДИНИТЬ ВСЕ, СУБД просто последовательно соединяет (склеивает) строки из первой и второй таблиц. Если в первом запросе пакета мы выбираем остаток и жестко прописываем ноль в поле резерва, а во втором запросе выбираем резерв и пишем ноль в поле остатка, на выходе мы получим две разные строки для одной и той же номенклатуры:
Группировка внутри каждого отдельного объединяемого запроса никак не решает эту проблему, так как группировка выполняется СУБД до того, как строки будут объединены в один результирующий набор. Чтобы избавиться от дублей, нам необходимо выполнить финальную группировку уже после того, как все данные будут собраны вместе.
Согласно стандартам разработки фирмы "1С", для объединения и последующей группировки больших объемов данных необходимо использовать механизм временных таблиц. Использование вложенных запросов для этой задачи нежелательно, так как оптимизатор СУБД часто строит неэффективные планы запросов из-за невозможности точно оценить количество строк (кардинальность) во вложенной выборке.
Разберем по шагам, как построить правильный пакетный запрос:
Посмотрим на пример практической реализации этого подхода:
// ШАГ 1: Объединение исходных данных и помещение во временную таблицу
ВЫБРАТЬ
ОстаткиТМЦ.Номенклатура КАК Номенклатура,
ОстаткиТМЦ.ВариантИсполнения КАК ВариантИсполнения,
ОстаткиТМЦ.Филиал КАК Филиал,
ОстаткиТМЦ.Фирма КАК Фирма,
ОстаткиТМЦ.Склад КАК Склад,
ОстаткиТМЦ.КолОстаток КАК Кол_Остаток,
0 КАК Кол_Резерв
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
РегистрНакопления.ОстаткиТМЦ.Остатки(&ДатаКон, Склад = &Склад) КАК ОстаткиТМЦ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Резервы.Номенклатура,
Резервы.ВариантИсполнения,
Резервы.Филиал,
Резервы.ДокЗаказ.Фирма,
Резервы.Склад,
0,
Резервы.КолОстаток
ИЗ
РегистрНакопления.Резервы.Остатки(&ДатаКон, Склад = &Склад) КАК Резервы
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Финальная группировка и суммирование ресурсов
ВЫБРАТЬ
ВТ.Номенклатура КАК Номенклатура,
ВТ.ВариантИсполнения КАК ВариантИсполнения,
ВТ.Филиал КАК Филиал,
ВТ.Фирма КАК Фирма,
ВТ.Склад КАК Склад,
СУММА(ВТ.Кол_Остаток) КАК Кол_Остаток,
СУММА(ВТ.Кол_Резерв) КАК Кол_Резерв,
СУММА(ВТ.Кол_Остаток) - СУММА(ВТ.Кол_Резерв) КАК Кол_СвободныйОстаток
ИЗ
ВТ_ИсходныеДанные КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Номенклатура,
ВТ.ВариантИсполнения,
ВТ.Филиал,
ВТ.Фирма,
ВТ.Склад
В исходном коде автора темы содержалась грубейшая ошибка производительности, способная полностью заблокировать работу базы данных при росте объемов информации:
ГДЕ ОстаткиТМЦ.Склад.Наименование = "Склад ГП"
Проанализируем, почему этот код является антипаттерном:
ГДЕ СУБД сначала рассчитывает остатки по абсолютно всем складам предприятия в оперативной памяти и только потом отсекает ненужные строки. На реальных базах это вызывает колоссальные задержки. Фильтрацию необходимо накладывать строго в параметрах виртуальной таблицы.Склад.Наименование заставляет СУБД выполнять неявное левое соединение с таблицей справочника складов, что сильно снижает скорость выполнения запроса.= "Склад ГП" крайне неэффективен, так как по текстовым полям в СУБД обычно нет индексов, а само наименование склада может измениться в любой момент пользователем в режиме предприятия.Правильное решение: передавать в параметры виртуальной таблицы ссылку на конкретный склад через параметр запроса:
РегистрНакопления.ОстаткиТМЦ.Остатки(&ДатаКон, Склад = &Склад)
Если в системе нет предопределенного склада "Склад ГП", правильнее всего предоставить пользователю возможность выбрать этот склад в параметрах отчета СКД, либо программно устанавливать этот параметр при компоновке макета.
Поскольку отчет разрабатывается на СКД, мы можем полностью отказаться от ручного написания сложных многоуровневых запросов с ОБЪЕДИНИТЬ ВСЕ. Система компоновки данных предоставляет для этого два элегантных штатных механизма:
Кол_Остаток, Кол_Резерв) перенести на вкладку Ресурсы в СКД, указав для них выражение СУММА(Кол_Остаток). При выводе отчета компоновщик автоматически свернет все строки по выбранным пользователем группировкам без какого-либо дублирования данных.Кроме того, расчет свободного остатка также очень просто реализовать на вкладке Вычисляемые поля в СКД. Достаточно добавить поле Кол_СвободныйОстаток и прописать выражение:
ЕСТЬNULL(Кол_Остаток, 0) - ЕСТЬNULL(Кол_Резерв, 0)
Это исключит необходимость писать сложные математические формулы внутри самого SQL-запроса и сделает схему компоновки гораздо более гибкой и легкой в поддержке.