Как получить только один договор для контрагента в запросе 1С и СКД?

← На главную

При переносе данных, свертке остатков или разработке сложных отчетов в системе 1С:Предприятие разработчики часто сталкиваются с задачей, когда для одной главной записи (например, контрагента) необходимо получить ровно одну подчиненную запись (например, его договор). Если у контрагента заведено несколько договоров, классическое левое соединение таблиц приведет к так называемому "размножению" строк. В результате суммы остатков продублируются, и итоговый отчет покажет некорректные данные.

В этой статье мы подробно разберем, как решить эту проблему различными способами — от простых агрегатных функций до профессиональных пакетированных запросов с временными таблицами и специализированных механизмов Системы компоновки данных (СКД).

Причина возникновения проблемы

Проанализируем ситуацию на примере бухгалтерского учета. Допустим, мы получаем остатки по счету 60.01 "Расчеты с поставщиками и подрядчиками". Из-за некорректного ведения учета прошлыми бухгалтерами остатки распределились по разным договорам одного контрагента. Наша задача — "схлопнуть" все эти остатки и перенести их на один любой (или конкретный приоритетный) договор.

Если мы напишем простой запрос с левым соединением таблицы остатков и справочника договоров по полю Владелец, СУБД сопоставит каждому контрагенту все его договоры. В результате одна строка остатка превратится в несколько строк — по числу договоров в справочнике. Сумма остатка при этом будет продублирована для каждого договора.

Рассмотрим по шагам профессиональные методы решения этой задачи.

Решение 1. Быстрый метод с использованием агрегатных функций (MAX/MIN)

Самый простой и быстрый способ получить ровно один договор — использовать агрегатные функции МАКСИМУМ или МИНИМУМ при группировке данных. Этот метод отлично подходит, если нам действительно неважно, какой именно договор будет выбран (или если нам нужен договор с самым большим/маленьким кодом или наименованием).

Давайте посмотрим на пример такого запроса:


ВЫБРАТЬ
    ХозрасчетныйОстатки.Счет КАК Счет,
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    ХозрасчетныйОстатки.Организация КАК Организация,
    СУММА(ХозрасчетныйОстатки.СуммаОстаток) КАК СуммаОстаток,
    МАКСИМУМ(Договоры.Ссылка) КАК Договор
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(ДАТАВРЕМЯ(2019, 12, 31, 23, 59, 59), Счет = &Счет60_01, , ) КАК ХозрасчетныйОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК Договоры
        ПО ХозрасчетныйОстатки.Субконто1 = Договоры.Владелец
СГРУППИРОВАТЬ ПО
    ХозрасчетныйОстатки.Счет,
    ХозрасчетныйОстатки.Субконто1,
    ХозрасчетныйОстатки.Организация

Как это работает: Мы убрали поле договора из секции СГРУППИРОВАТЬ ПО и поместили его в секцию ВЫБРАТЬ с функцией МАКСИМУМ. Теперь СУБД сначала сгруппирует все остатки по контрагенту (сложив суммы), а затем выберет для этого контрагента один единственный договор, ссылка которого является максимальной по значению (внутреннему идентификатору UUID или коду).

Решение 2. Использование регистра сведений "Основные договоры контрагентов"

Выбор случайного договора через МАКСИМУМ часто не устраивает бизнес-пользователей. В реальном учете всегда есть приоритетный договор, по которому ведутся основные расчеты. В типовых конфигурациях 1С (таких как "Бухгалтерия предприятия 3.0", "ERP Управление предприятием" или "Комплексная автоматизация") основной договор контрагента хранится в специальном регистре сведений ОсновныеДоговорыКонтрагентов.

С точки зрения методологии 1С, правильнее соединять таблицу остатков именно с этим регистром. Это гарантирует получение одного, причем самого актуального договора. Посмотрим на структуру такого запроса:


ВЫБРАТЬ
    ХозрасчетныйОстатки.Счет КАК Счет,
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    ХозрасчетныйОстатки.Организация КАК Организация,
    СУММА(ХозрасчетныйОстатки.СуммаОстаток) КАК СуммаОстаток,
    ЕСТЬNULL(ОсновныеДоговоры.Договор, МАКСИМУМ(ДоговорыВсе.Ссылка)) КАК Договор
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(ДАТАВРЕМЯ(2019, 12, 31, 23, 59, 59), Счет = &Счет60_01, , ) КАК ХозрасчетныйОстатки
    
    // Пытаемся получить основной договор
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ОсновныеДоговорыКонтрагентов КАК ОсновныеДоговоры
        ПО ХозрасчетныйОстатки.Субконто1 = ОсновныеДоговоры.Контрагент
        И ХозрасчетныйОстатки.Организация = ОсновныеДоговоры.Организация
        
    // На случай, если основной договор не задан, подтягиваем любой другой
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК ДоговорыВсе
        ПО ХозрасчетныйОстатки.Субконто1 = ДоговорыВсе.Владелец
СГРУППИРОВАТЬ ПО
    ХозрасчетныйОстатки.Счет,
    ХозрасчетныйОстатки.Субконто1,
    ХозрасчетныйОстатки.Организация,
    ОсновныеДоговоры.Договор

Такой подход защитит отчет от пустых значений, если для какого-то контрагента бухгалтеры забыли установить основной договор в программе.

Решение 3. Оптимальный вариант: Двухэтапный запрос с временными таблицами

Согласно стандартам разработки фирмы "1С", соединение реальных таблиц (особенно виртуальных таблиц остатков) со справочниками и сложными вложенными запросами крайне не рекомендуется. Это может приводить к созданию неоптимальных планов запросов в СУБД и сильному замедлению работы системы на больших базах данных.

Выясним, как сделать этот процесс максимально быстрым с помощью временных таблиц. Разделим задачу на три логических шага:

Выберем уникальных контрагентов, по которым вообще есть остатки, и поместим их во временную таблицу.
Для этих контрагентов выберем по одному договору (например, первому по коду) и запишем связи во вторую временную таблицу.
Соединим исходную таблицу остатков с готовой таблицей связей.

Проанализируем готовый пакетный запрос:


// ШАГ 1: Получаем список только тех контрагентов, по которым есть остатки
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент
ПОМЕСТИТЬ ВТ_АктивныеКонтрагенты
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(ДАТАВРЕМЯ(2019, 12, 31, 23, 59, 59), Счет = &Счет60_01, , ) КАК ХозрасчетныйОстатки
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Для отобранных контрагентов находим ровно один (минимальный) договор
ВЫБРАТЬ
    Договоры.Владелец КАК Контрагент,
    МИНИМУМ(Договоры.Ссылка) КАК Договор
ПОМЕСТИТЬ ВТ_КонтрагентыИДоговоры
ИЗ
    Справочник.ДоговорыКонтрагентов КАК Договоры
ГДЕ
    Договоры.Владелец В (ВЫБРАТЬ ВТ_АктивныеКонтрагенты.Контрагент ИЗ ВТ_АктивныеКонтрагенты КАК ВТ_АктивныеКонтрагенты)
СГРУППИРОВАТЬ ПО
    Договоры.Владелец
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Получаем итоговые остатки и соединяем их с подготовленной таблицей договоров
ВЫБРАТЬ
    ХозрасчетныйОстатки.Счет КАК Счет,
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    ХозрасчетныйОстатки.Организация КАК Организация,
    СУММА(ХозрасчетныйОстатки.СуммаОстаток) КАК СуммаОстаток,
    ВТ_Связи.Договор КАК Договор
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(ДАТАВРЕМЯ(2019, 12, 31, 23, 59, 59), Счет = &Счет60_01, , ) КАК ХозрасчетныйОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_КонтрагентыИДоговоры КАК ВТ_Связи
        ПО ХозрасчетныйОстатки.Субконто1 = ВТ_Связи.Контрагент
СГРУППИРОВАТЬ ПО
    ХозрасчетныйОстатки.Счет,
    ХозрасчетныйОстатки.Субконто1,
    ХозрасчетныйОстатки.Организация,
    ВТ_Связи.Договор

Этот способ является стандартом качественной разработки. База данных выполнит этот запрос мгновенно, так как операции группировки и поиска минимума будут производиться по индексированным временным таблицам небольшого объема.

Решение 4. Коррелированные подзапросы в условии связи

Если вам нужно выбрать не просто случайный договор, а строго определенный по какому-то критерию (например, самый первый по дате создания или по коду) без использования тяжелых группировок, можно применить коррелированный подзапрос непосредственно в условии связи LEFT JOIN.

Важное ограничение платформы 1С: В языке запросов 1С отсутствуют оконные функции SQL (такие как ROW_NUMBER() OVER (PARTITION BY...)). Поэтому приходится использовать конструкцию ВЫБРАТЬ ПЕРВЫЕ 1 внутри подзапроса связи:


ВЫБРАТЬ
    ХозрасчетныйОстатки.Счет КАК Счет,
    ХозрасчетныйОстатки.Субконто1 КАК Контрагент,
    ХозрасчетныйОстатки.Организация КАК Организация,
    ХозрасчетныйОстатки.СуммаОстаток КАК СуммаОстаток,
    Договоры.Ссылка КАК Договор
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Остатки(ДАТАВРЕМЯ(2019, 12, 31, 23, 59, 59), Счет = &Счет60_01, , ) КАК ХозрасчетныйОстатки
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.ДоговорыКонтрагентов КАК Договоры
        ПО ХозрасчетныйОстатки.Субконто1 = Договоры.Владелец
        И Договоры.Ссылка В (
            ВЫБРАТЬ ПЕРВЫЕ 1 Внутр.Ссылка
            ИЗ Справочник.ДоговорыКонтрагентов КАК Внутр
            ГДЕ Внутр.Владелец = ХозрасчетныйОстатки.Субконто1
            УПОРЯДОЧИТЬ ПО Внутр.Код ВОЗР
        )

Этот запрос гарантированно вернет ровно один договор (самый первый по коду) для каждого контрагента. Однако помните, что коррелированные запросы могут снижать производительность на таблицах с сотнями тысяч записей, так как подзапрос будет выполняться для каждой строки основного набора данных.

Решение 5. Настройка связей наборов данных в СКД

Поскольку автор исходного вопроса упомянул, что разрабатывает отчет на Системе компоновки данных (СКД), мы можем решить эту задачу элегантно, вообще не усложняя текст основного запроса. СКД предоставляет мощный встроенный механизм для связи различных таблиц на уровне платформы.

Разберем этот алгоритм по шагам:

Создадим в схеме компоновки данных первый набор данных — НаборДанных1 (Запрос). В нем мы выберем только остатки по контрагентам, полностью исключив из него упоминание договоров и лишние соединения.

Создадим второй набор данных — НаборДанных2 (Запрос). В его тексте выберем список договоров и их владельцев:


ВЫБРАТЬ
    Договоры.Ссылка КАК Договор,
    Договоры.Владелец КАК Контрагент
ИЗ
    Справочник.ДоговорыКонтрагентов КАК Договоры

Перейдем на вкладку "Связи наборов данных" и добавим новую строку связи:
- Источник связи: НаборДанных1
- Приемник связи: НаборДанных2
- Условие связи: Контрагент = Контрагент
Перейдем на вкладку "Ресурсы" и добавим поле Договор в список ресурсов. В качестве выражения для расчета ресурса укажем: Максимум(Договор) или Минимум(Договор).

При таком подходе СКД сама сгруппирует данные при выводе отчета и применит агрегатную функцию к договору. На уровне детальных записей строки с суммами остатков никогда не продублируются. Для оптимизации скорости работы обязательно установите флаг "Список параметров" в настройках связи наборов данных — это позволит СКД передавать список контрагентов во второй запрос пакетами, избегая единичных обращений к базе данных.