Как в запросе 1С получить последний заказ по каждому партнеру?

← На главную

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

Разберем типичные ошибки при реализации такой выборки, выясним причины сбоев транслятора запросов СУБД и рассмотрим правильную архитектуру запроса с использованием временных таблиц.

Типичные ошибки при решении задачи

Проанализируем распространенные подходы, которые приводят к ошибкам или некорректным результатам:

Использование конструкции ВЫБРАТЬ ПЕРВЫЕ 1 во вложенном запросе левого соединения. Конструкция ВЫБРАТЬ ПЕРВЫЕ 1 ... УПОРЯДОЧИТЬ ПО Дата УБЫВ выбирает ровно одну запись из всей таблицы базы данных, а не по одной записи для каждого партнера. В итоге соединение сработает только для того единственного партнера, чей заказ оказался самым последним в системе.
Попытка агрегации ссылочного поля через МАКСИМУМ(Ссылка) во вложенных запросах с DISTINCT или UNION. Внутренний механизм платформы 1С при трансляции запроса с агрегатными функциями по ссылкам на документы обращается к служебным полям даты и номера. Если структура полей в подзапросе не совпадает с ожиданиями платформы, возникает ошибка: «SDBL: Поле DATE_TIME отсутствует в разделе SELECT».
Обращение к регистрам накопления. Регистры накопления содержат движения только проведенных документов. Если стоит задача отобразить даже черновики или удаленные документы, регистры использовать нельзя — необходимо обращаться напрямую к таблице документов Документ.ЗаказКлиента.

Оптимальное решение: использование временных таблиц и среза максимальных дат

Рассмотрим по шагам правильную структуру запроса. Чтобы получить последний документ по каждому партнеру из произвольного списка (например, отобранных партнеров без контрагентов), построим пакетный запрос:

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

Посмотрим на готовый текст запроса, решающий поставленную задачу:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    Контрагенты.Партнер КАК Партнер
ПОМЕСТИТЬ ВТ_ПартнерыСКонтрагентами
ИЗ
    Справочник.Контрагенты КАК Контрагенты
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Партнеры.Ссылка КАК Партнер,
    Партнеры.Код КАК Код
ПОМЕСТИТЬ ВТ_ПроблемныеПартнеры
ИЗ
    Справочник.Партнеры КАК Партнеры
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПартнерыСКонтрагентами КАК ВТ_ПартнерыСКонтрагентами
        ПО (Партнеры.Ссылка = ВТ_ПартнерыСКонтрагентами.Партнер)
ГДЕ
    ВТ_ПартнерыСКонтрагентами.Партнер ЕСТЬ NULL

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

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

ИНДЕКСИРОВАТЬ ПО
    Партнер,
    МаксДата
;

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

Нюансы реализации и оптимизация производительности

Разберем важные технические особенности представленного решения:

Проблема одинакового времени документов. Если по партнеру создано несколько заказов с абсолютно одинаковой датой и временем (с точностью до секунды), простое соединение по дате приведет к дублированию строк в результате. Применение агрегатной функции МАКСИМУМ(ЗаказКлиента.Ссылка) в финальной выборке гарантирует, что в отчет попадет строго одна строка на каждого партнера.
Ограничение объема сканируемых данных. Обратите внимание, что во временной таблице ВТ_МаксимальныеДаты соединение с таблицей Документ.ЗаказКлиента происходит сразу с фильтрацией по ВТ_ПроблемныеПартнеры. Это предотвращает полное сканирование (Table Scan) многомиллионной таблицы заказов по всей базе данных.
Индексация временных таблиц. Указание директивы ИНДЕКСИРОВАТЬ ПО ускоряет последующие операции соединения, особенно при обработке больших списков элементов.

Альтернативные способы решения

Рассмотрим другие варианты решения задачи в зависимости от условий:

Выборка по одному партнеру. Если требуется получить последний заказ только по одному конкретному партнеру, достаточно простого запроса с параметром:


ВЫБРАТЬ ПЕРВЫЕ 1
    ЗаказКлиента.Ссылка КАК Заказ,
    ЗаказКлиента.Дата КАК Дата
ИЗ
    Документ.ЗаказКлиента КАК ЗаказКлиента
ГДЕ
    ЗаказКлиента.Партнер = &Партнер
УПОРЯДОЧИТЬ ПО
    ЗаказКлиента.Дата УБЫВ

Связи наборов данных в СКД. При разработке отчета на Схеме компоновки данных можно создать два независимых набора данных: «Партнеры» и «Заказы». На вкладке «Связи наборов данных» настраивается связь по полю Партнер. В подчиненном наборе заказов можно настроить отбор по максимальной дате или ограничение количества записей, что избавит от необходимости писать сложные пакетные соединения вручную.