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