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