Как написать запрос для выбора контрагентов с первой реализацией в периоде и расчетом суммы оплат по договору в 1С

← На главную

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

В этой статье мы подробно разберем, как правильно составить пакетный запрос для решения этой задачи, какие виртуальные таблицы и регистры использовать в различных конфигурациях (таких как «1С:Бухгалтерия предприятия 3.0» или «1С:ERP / Управление торговлей 11»), а также проанализируем важные нюансы оптимизации производительности СУБД.

Почему задачу нельзя решить простым запросом?

Попытка решить данную задачу «в лоб» с помощью одного простого запроса к таблице документов неизбежно приведет к ошибкам или крайне низкой производительности базы данных. Нам мешают несколько факторов:

Сравнение дат и получение ссылки: Использование агрегатной функции МИНИМУМ(Ссылка) не вернет ссылку на самый ранний документ по хронологии. Ссылки (уникальные идентификаторы GUID) в СУБД сравниваются по своим внутренним правилам, не связанным со временем создания документа. Чтобы получить ссылку на самый первый документ, сначала необходимо вычислить минимальную дату этого документа, а затем повторно соединить полученный результат с физической таблицей документов.
Условие отсутствия продаж по другим организациям: Проверка «черного списка» контрагентов, у которых были продажи по другим организациям до начала периода отбора, требует предварительной выборки данных за весь исторический период работы базы данных. Соединение этой выборки в одном запросе с текущими продажами перегрузит планировщик запросов СУБД.
Расчет оплат по конкретному договору: Нам нужно рассчитать сумму оплат исключительно по тому договору, который указан в первой найденной реализации за период. Это накладывает жесткое условие связи между найденным документом реализации и регистром расчетов (или регистром бухгалтерии).

Единственным правильным решением здесь является использование пакетного запроса с созданием нескольких временных таблиц (ВТ).

---

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

Рассмотрим структуру пакета запросов, который позволит нам поэтапно и максимально эффективно получить требуемый результат.

Шаг 1: Формирование черного списка контрагентов

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

Создадим временную таблицу ВТ_ИсключаемыеКонтрагенты. В качестве источника данных мы можем использовать физическую таблицу документа РеализацияТоваровУслуг или соответствующий регистр продаж.


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Реализация.Контрагент КАК Контрагент
ПОМЕСТИТЬ ВТ_ИсключаемыеКонтрагенты
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
    Реализация.Дата < &НачалоПериода
    И Реализация.Организация <> &Организация
    И Реализация.Проведен = ИСТИНА

ИНДЕКСИРОВАТЬ ПО
    Контрагент
;

Важный момент: Обратите внимание на инструкцию ИНДЕКСИРОВАТЬ ПО. Индексация временной таблицы по полю связи (в данном случае Контрагент) критически важна для быстродействия последующих соединений в пакете.

Шаг 2: Определение даты первой реализации в периоде

Теперь нам нужно найти самую раннюю дату продажи по целевой организации внутри выбранного периода для каждого контрагента. При этом мы должны сразу отсечь контрагентов, попавших в наш «черный список» на предыдущем шаге.

Используем левое соединение с временной таблицей ВТ_ИсключаемыеКонтрагенты и отфильтруем записи, где связь не установилась (то есть значение поля равно NULL).


ВЫБРАТЬ
    Реализация.Контрагент КАК Контрагент,
    МИНИМУМ(Реализация.Дата) КАК МинимальнаяДата
ПОМЕСТИТЬ ВТ_ПерваяДатаРТУ
ИЗ
    Документ.РеализацияТоваровУслуг КАК Реализация
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсключаемыеКонтрагенты КАК Исключаемые
        ПО Реализация.Contragent = Исключаемые.Контрагент
ГДЕ
    Реализация.Дата МЕЖДУ &НачалоПериода И &КонецПериода
    И Реализация.Организация = &Организация
    И Реализация.Проведен = ИСТИНА
    И Исключаемые.Контрагент ЕСТЬ NULL
ГРУППИРОВАТЬ ПО
    Реализация.Контрагент

ИНДЕКСИРОВАТЬ ПО
    Контрагент,
    МинимальнаяДата
;

Шаг 3: Получение ссылки на первую реализацию и ее договор

Имея минимальную дату продажи для каждого разрешенного контрагента, мы можем получить ссылку на сам документ РеализацияТоваровУслуг и узнать договор, по которому была совершена эта сделка. Для этого соединяем временную таблицу ВТ_ПерваяДатаРТУ обратно с физической таблицей документов по полям Контрагент, Организация и Дата.

Поскольку в одну и ту же секунду теоретически может быть проведено несколько документов по одному контрагенту, для исключения дублирования строк мы применим группировку и выберем минимальную ссылку на документ.


ВЫБРАТЬ
    ПерваяДата.Контрагент КАК Контрагент,
    ПерваяДата.МинимальнаяДата КАК ДатаРТУ,
    МИНИМУМ(Реализация.Ссылка) КАК СсылкаРТУ,
    Реализация.ДоговорКонтрагента КАК Договор
ПОМЕСТИТЬ ВТ_ПервыеРТУ
ИЗ
    ВТ_ПерваяДатаРТУ КАК ПерваяДата
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК Реализация
        ПО ПерваяДата.Контрагент = Реализация.Контрагент
        И ПерваяДата.МинимальнаяДата = Реализация.Дата
ГДЕ
    Реализация.Организация = &Организация
    И Реализация.Проведен = ИСТИНА
ГРУППИРОВАТЬ ПО
    ПерваяДата.Контрагент,
    ПерваяДата.МинимальнаяДата,
    Реализация.ДоговорКонтрагента

ИНДЕКСИРОВАТЬ ПО
    Контрагент,
    Договор
;

---

Шаг 4: Получение суммы оплат (зависит от конфигурации)

Этот шаг является наиболее вариативным, так как механизмы учета взаиморасчетов и структура регистров в разных конфигурациях 1С существенно различаются. Рассмотрим два наиболее популярных сценария реализации этой части запроса.

Вариант А: Реализация для конфигурации «Бухгалтерия предприятия 3.0»

В БП 3.0 все взаиморасчеты с контрагентами отражаются на счетах бухгалтерского учета (основной счет — 62). Для получения суммы оплат в периоде отбора по конкретному договору наиболее эффективно использовать виртуальную таблицу оборотов регистра бухгалтерии Хозрасчетный.Обороты.

Мы будем анализировать кредитовый оборот по счету 62 (или его субсчетам 62.01, 62.02). Чтобы отсечь неденежные операции (например, корректировки долга или взаимозачеты), рекомендуется наложить фильтр по корреспонденции с денежными счетами (дебет счетов 50, 51, 52, 55, 57).


ВЫБРАТЬ
    ПервыеРТУ.Контрагент КАК Контрагент,
    ПервыеРТУ.ДатаРТУ КАК ДатаРТУ,
    ПервыеРТУ.СсылкаРТУ КАК СсылкаРТУ,
    ПервыеРТУ.Договор КАК Договор,
    ЕСТЬNULL(Взаиморасчеты.СуммаОборотКт, 0) КАК СуммаОплаты
ИЗ
    ВТ_ПервыеРТУ КАК ПервыеРТУ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Обороты(
        &НачалоПериода,
        &КонецПериода,
        Период,
        Счет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетыСПокупателямиИКлиентами)),
        ,
        Организация = &Организация
            И Субконто1 В (ВЫБРАТЬ ВТ.Контрагент ИЗ ВТ_ПервыеРТУ КАК ВТ)
            И Субконто2 В (ВЫБРАТЬ ВТ.Договор ИЗ ВТ_ПервыеРТУ КАК ВТ),
        КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.Касса))
            ИЛИ КорСчет В ИЕРАРХИИ (ЗНАЧЕНИЕ(ПланСчетов.Хозрасчетный.РасчетныеСчета)),
        
    ) КАК Взаиморасчеты
        ПО ПервыеРТУ.Контрагент = Взаиморасчеты.Субконто1
        И ПервыеРТУ.Договор = Взаиморасчеты.Subkonto2
;

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

Вариант Б: Реализация для конфигураций «ERP Управление предприятием» или «Управление торговлей 11»

В ERP и УТ 11 бухгалтерский регистр не является оперативным источником данных для взаиморасчетов. Здесь учет ведется на специализированных регистрах накопления. Самым удобным источником информации об оплатах клиентов является оборотный регистр накопления РасчетыСКлиентами.

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


ВЫБРАТЬ
    ПервыеРТУ.Контрагент КАК Контрагент,
    ПервыеРТУ.ДатаРТУ КАК ДатаРТУ,
    ПервыеРТУ.СсылкаРТУ КАК СсылкаРТУ,
    ПервыеРТУ.Договор КАК Договор,
    ЕСТЬNULL(Взаиморасчеты.ОплатаОборот, 0) КАК СуммаОплаты
ИЗ
    ВТ_ПервыеРТУ КАК ПервыеРТУ
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСКлиентами.Обороты(
        &НачалоПериода,
        &КонецПериода,
        Период,
        Организация = &Организация
            И АналитикаУчетаПоПартнерам.Контрагент В (ВЫБРАТЬ ВТ.Контрагент ИЗ ВТ_ПервыеРТУ КАК ВТ)
            И Договор В (ВЫБРАТЬ ВТ.Договор ИЗ ВТ_ПервыеРТУ КАК ВТ)
    ) КАК Взаиморасчеты
        ПО ПервыеРТУ.Контрагент = Взаиморасчеты.АналитикаУчетаПоПартнерам.Контрагент
        И ПервыеРТУ.Договор = Взаиморасчеты.Договор
;

---

Технические нюансы и подводные камни для собеседования

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

Отказ от обращений к таблицам документов на больших базах: Обращение к таблице Документ.РеализацияТоваровУслуг напрямую допустимо на небольших базах или в учебных примерах. Однако на крупных промышленных базах это вызовет серьезные блокировки и падение скорости. Более профессиональным подходом считается получение данных о продажах из оборотных регистров накопления (например, ВыручкаИСебестоимостьПродаж для ERP/УТ или КнигаПродаж для БП).
Что считать «Оплатой»? На собеседовании обязательно задайте этот уточняющий вопрос. В реальном бизнесе оплатой может считаться не только поступление наличных/безналичных денежных средств, но и проведение взаимозачета (корректировка долга), зачет встречных требований или оплата подарочным сертификатом. Предложенные выше варианты запросов ориентированы именно на прямое поступление денежных средств, что чаще всего и требуется.
Контроль проведения документов: Всегда добавляйте условие Проведен = ИСТИНА при обращении к таблицам документов. Помеченные на удаление или просто сохраненные, но не проведенные документы продажи не должны участвовать в бизнес-логике отчета.
Использование временных таблиц вместо вложенных запросов: Никогда не используйте вложенные запросы внутри секции ИЗ или СОЕДИНЕНИЕ. Планировщик СУБД Microsoft SQL Server или PostgreSQL плохо справляется с оптимизацией вложенных запросов в 1С. Использование жестко типизированных и проиндексированных временных таблиц (ВТ) гарантирует стабильный и предсказуемый план выполнения запроса.

Применяя описанный выше пошаговый алгоритм разделения запроса на логические блоки с помощью временных таблиц, вы получите производительное, чистое и легко читаемое решение, которое высоко оценит любой технический специалист или архитектор систем 1С.