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

← На главную

При разработке отчетов, обработок или механизмов автозаполнения в системе 1С:Предприятие часто возникает задача упорядочивания списка контрагентов по определенному бизнес-критерию. Одним из наиболее частых требований является сортировка клиентов по дате их последней активности — например, по дате последнего оформленного документа ЗаказПокупателя.

Если решать эту задачу «в лоб», начинающие разработчики или специалисты, совмещающие функции администраторов и программистов, часто прибегают к обходу исходного массива в цикле и выполнению запроса на каждую итерацию. Такой подход является серьезной ошибкой проектирования (так называемая проблема N+1 запроса), поскольку он лавинообразно увеличивает нагрузку на СУБД и замедляет работу системы. В этой статье мы подробно разберем, как решить эту задачу в рамках одного-единственного запроса, избегая распространенных подводных камней.

Почему цикл запросов — это критическая ошибка?

Прежде чем перейти к написанию оптимального кода, проанализируем, почему выполнение запроса в цикле недопустимо для производительных систем. Каждый вызов метода Запрос.Выполнить() порождает полноценное обращение к СУБД через сетевой сокет, компиляцию плана запроса сервером баз данных и передачу данных обратно на сервер приложений 1С. Если в вашем массиве содержится 100 контрагентов, система выполнит 100 обращений к базе данных. На реальных многопользовательских базах данных «1С:Управление торговлей 10.3» (или любых других конфигурациях) это приведет к заметным задержкам интерфейса и блокировкам таблиц.

Единственный верный способ — передать весь массив контрагентов в СУБД за один раз, произвести там все необходимые расчеты средствами SQL и вернуть уже готовый, отсортированный результат.

Проектируем правильный алгоритм решения

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

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

Пошаговый разбор построения запроса

Рассмотрим логику построения нашего запроса по шагам.

Шаг 1. Создание временной таблицы. Сначала мы передаем исходный массив контрагентов в качестве параметра запроса и помещаем его во временную таблицу ВТ_Контрагенты. Это гарантирует, что мы не потеряем ни одного клиента из исходного списка, даже если по нему нет никаких движений в базе.

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

Шаг 3. Соединение данных и сортировка. Мы соединяем временную таблицу ВТ_Контрагенты с вложенным запросом максимальных дат с помощью конструкции ЛЕВОЕ СОЕДИНЕНИЕ. Для всех контрагентов, у которых не нашлось заказов, мы заменяем полученное значение NULL на пустую дату ДАТАВРЕМЯ(1, 1, 1) с помощью функции ЕСТЬNULL. В самом конце мы вызываем инструкцию УПОРЯДОЧИТЬ ПО ... УБЫВ.

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


// Шаг 1: Помещаем переданный массив во временную таблицу для сохранения целостности списка
ВЫБРАТЬ
    ИсходныйСписок.Ссылка КАК Контрагент
ПОМЕСТИТЬ ВТ_Контрагенты
ИЗ
    &МассивКонтрагентов КАК ИсходныйСписок
;

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

УПОРЯДОЧИТЬ ПО
    ДатаПоследнегоЗаказа УБЫВ

Как использовать решение во встроенном языке 1С?

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


// Предположим, что в переменной "М" хранится ваш исходный массив ссылок на контрагентов
МассивКонтрагентов = М; 

Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |	ИсходныйСписок.Ссылка КАК Контрагент
    |ПОМЕСТИТЬ ВТ_Контрагенты
    |ИЗ
    |	&МассивКонтрагентов КАК ИсходныйСписок
    |;
    |
    |ВЫБРАТЬ
    |	ВТ_Контрагенты.Контрагент КАК Контрагент,
    |	ЕСТЬNULL(МаксимальныеДаты.ДатаПоследнегоЗаказа, ДАТАВРЕМЯ(1, 1, 1)) КАК ДатаПоследнегоЗаказа
    |ИЗ
    |	ВТ_Контрагенты КАК ВТ_Контрагенты
    |		ЛЕВОЕ СОЕДИНЕНИЕ (
    |			ВЫБРАТЬ
    |				ЗаказПокупателя.Контрагент КАК Контрагент,
    |				МАКСИМУМ(ЗаказПокупателя.Дата) КАК ДатаПоследнегоЗаказа
    |			ИЗ
    |				Документ.ЗаказПокупателя КАК ЗаказПокупателя
    |			ГДЕ
    |				ЗаказПокупателя.Контрагент В (&МассивКонтрагентов)
    |				И ЗаказПокупателя.Проведен = ИСТИНА
    |			СГРУППИРОВАТЬ ПО
    |				ЗаказПокупателя.Контрагент
    |		) КАК МаксимальныеДаты
    |		ПО ВТ_Контрагенты.Contragent = МаксимальныеДаты.Контрагент
    |
    |УПОРЯДОЧИТЬ ПО
    |	ДатаПоследнегоЗаказа УБЫВ";

Запрос.УстановитьПараметр("МассивКонтрагентов", МассивКонтрагентов);

РезультатЗапроса = Запрос.Выполнить();
ТаблицаРезультата = РезультатЗапроса.Выгрузить();

// Быстро и без циклов получаем отсортированный массив контрагентов:
ОтсортированныйМассив = ТаблицаРезультата.ВыгрузитьКолонку("Контрагент");

Анализ производительности решения

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

Поле Контрагент в документе ЗаказПокупателя в типовых конфигурациях (включая УТ 10.3, УПП, ERP) является индексируемым по умолчанию.
Благодаря явному условию ГДЕ ЗаказПокупателя.Контрагент В (&МассивКонтрагентов) во вложенном запросе, СУБД не производит полное сканирование (Full Table Scan) таблицы заказов. Вместо этого СУБД использует индекс и мгновенно выбирает документы только по тем контрагентам, которые были переданы в параметрах.
Группировка и вычисление функции МАКСИМУМ происходят в оперативной памяти сервера базы данных над уже отфильтрованным, минимальным объемом записей, что минимизирует нагрузку на дисковую подсистему.

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