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

← На главную

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

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

Способ 1. Коррелированный подзапрос в соединении

Проанализируем базовый вариант, который чаще всего приходит на ум разработчикам: выполнить левое соединение списка уникальных участников с выборкой документов, где в условии соединения используется вложенный подзапрос с конструкцией ПЕРВЫЕ 3.

Посмотрим на пример такого запроса:


ВЫБРАТЬ РАЗЛИЧНЫЕ
    ЗаказТураУчастники.Участник КАК Участник
ПОМЕСТИТЬ ВТ_Участники
ИЗ
    Документ.ЗаказТура.Участники КАК ЗаказТураУчастники
;

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ_Участники.Участник КАК Участник,
    ЗаказТураУчастники.Ссылка КАК Ссылка
ИЗ
    ВТ_Участники КАК ВТ_Участники
    ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказТура.Участники КАК ЗаказТураУчастники
        ПО (ЗаказТураУчастники.Ссылка В
            (ВЫБРАТЬ ПЕРВЫЕ 3
                Подзапрос.Ссылка
            ИЗ
                Документ.ЗаказТура.Участники КАК Подзапрос
            ГДЕ
                Подзапрос.Участник = ВТ_Участники.Участник
                И Подзапрос.Ссылка.Проведен
                И НЕ Подзапрос.Ссылка.Аннулирован
                И НЕ Подзапрос.Ссылка.ПометкаУдаления
            УПОРЯДОЧИТЬ ПО
                Подзапрос.Ссылка.Дата УБЫВ))

Рассмотрим подробнее особенности и слабые места этого подхода:

Корреляция подзапроса: Условие Подзапрос.Участник = ВТ_Участники.Участник связывает внутренний запрос с текущей строкой внешней таблицы. СУБД вынуждена выполнять внутреннюю выборку с сортировкой и ограничением строк индивидуально для каждой записи, что на больших объемах данных приводит к катастрофическому падению скорости.
Разыменование полей через точку: Конструкции вида Подзапрос.Ссылка.Проведен и Подзапрос.Ссылка.Дата при обращении к табличной части создают скрытые неявные соединения с шапкой документа. Это исключает эффективное использование индексов СУБД и приводит к полному сканированию таблиц.

Способ 2. Предварительная фильтрация и каскадные срезы (Оптимальный для запросов)

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

Разберем процесс по шагам:

Откажемся от разыменования через точку в табличной части. Сначала отберем проведенные документы из шапки во временную таблицу.
Соединим отобранные документы с табличной частью участников и проиндексируем результат по полям Участник, Дата, Ссылка.
Последовательно выберем первый максимум, затем второй (исключив первый) и третий (исключив первые два).
Объединим результаты через ОБЪЕДИНИТЬ ВСЕ.


ВЫБРАТЬ
    Заказ.Ссылка КАК Ссылка,
    Заказ.Дата КАК Дата
ПОМЕСТИТЬ ВТ_ЗаказыШапка
ИЗ
    Документ.ЗаказТура КАК Заказ
ГДЕ
    Заказ.Проведен
    И НЕ Заказ.Аннулирован
    И НЕ Заказ.ПометкаУдаления
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Участник КАК Участник,
    Шапка.Дата КАК Дата,
    Шапка.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_ВсеЗаказы
ИЗ
    ВТ_ЗаказыШапка КАК Шапка
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЗаказТура.Участники КАК ТЧ
        ПО Шапка.Ссылка = ТЧ.Ссылка
ИНДЕКСИРОВАТЬ ПО
    Участник,
    Дата,
    Ссылка
;

// Шаг 1: Самый последний заказ
ВЫБРАТЬ
    ВТ.Участник КАК Участник,
    МАКСИМУМ(ВТ.Дата) КАК Дата
ПОМЕСТИТЬ ВТ_Заказ1_Дата
ИЗ
    ВТ_ВсеЗаказы КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Участник
;

ВЫБРАТЬ
    ВТ.Участник КАК Участник,
    ВТ.Дата КАК Дата,
    МАКСИМУМ(ВТ.Ссылка) КАК Ссылка
ПОМЕСТИТЬ ВТ_Заказ1
ИЗ
    ВТ_ВсеЗаказы КАК ВТ
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Заказ1_Дата КАК Даты
        ПО ВТ.Участник = Даты.Участник
        И ВТ.Дата = Даты.Дата
СГРУППИРОВАТЬ ПО
    ВТ.Участник,
    ВТ.Дата
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

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

ВЫБРАТЬ
    ВТ.Участник КАК Участник,
    ВТ.Дата КАК Дата,
    МАКСИМУМ(ВТ.Ссылка) КАК Ссылка
ПОМЕСТИТЬ ВТ_Заказ2
ИЗ
    ВТ_ВсеЗаказы КАК ВТ
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Заказ2_Дата КАК Даты
        ПО ВТ.Участник = Даты.Участник
        И ВТ.Дата = Даты.Дата
СГРУППИРОВАТЬ ПО
    ВТ.Участник,
    ВТ.Дата
ИНДЕКСИРОВАТЬ ПО
    Ссылка
;

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

ВЫБРАТЬ
    ВТ.Участник КАК Участник,
    ВТ.Дата КАК Дата,
    МАКСИМУМ(ВТ.Ссылка) КАК Ссылка
ПОМЕСТИТЬ ВТ_Заказ3
ИЗ
    ВТ_ВсеЗаказы КАК ВТ
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Заказ3_Дата КАК Даты
        ПО ВТ.Участник = Даты.Участник
        И ВТ.Дата = Даты.Дата
СГРУППИРОВАТЬ ПО
    ВТ.Участник,
    ВТ.Дата
;

// Итоговое объединение
ВЫБРАТЬ Участник, Ссылка, Дата ИЗ ВТ_Заказ1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Участник, Ссылка, Дата ИЗ ВТ_Заказ2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Участник, Ссылка, Дата ИЗ ВТ_Заказ3

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

Способ 3. Нумерация строк через самосоединение

Если количество отбираемых строк N является переменным параметром, можно применить нумерацию строк в запросе через соединение таблицы с самой собой.

Выясним принцип работы:


ВЫБРАТЬ
    Т1.Участник КАК Участник,
    Т1.Ссылка КАК Ссылка,
    Т1.Дата КАК Дата
ИЗ
    ВТ_ВсеЗаказы КАК Т1
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ВсеЗаказы КАК Т2
        ПО Т1.Участник = Т2.Участник
        И (Т1.Дата < Т2.Дата ИЛИ (Т1.Дата = Т2.Дата И Т1.Ссылка <= Т2.Ссылка))
СГРУППИРОВАТЬ ПО
    Т1.Участник,
    Т1.Ссылка,
    Т1.Дата
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Ссылка) <= 3

В этом запросе для каждого документа подсчитывается, сколько существует документов с датой, большей либо равной текущей. Условие ИМЕЮЩИЕ КОЛИЧЕСТВО(...) <= 3 оставляет ровно три самых свежих заказа. Важно помнить, что квадратичная сложность такого соединения может приводить к замедлению, если по одному участнику накоплены тысячи заказов.

Способ 4. Использование возможностей СКД

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

Ограничение записей в настройках группировки: Строим простой запрос с сортировкой УПОРЯДОЧИТЬ ПО Дата УБЫВ. В настройках структуры отчета создаем родительскую группировку по полю Участник, а подчиненной группировкой делаем детальные записи (или группировку по полю Ссылка). В свойствах подчиненной группировки находим параметр «Количество записей» и указываем значение 3. При выводе СКД сама ограничит вывод тремя записями для каждого родительского элемента.
Связь двух наборов данных: Создаем два набора-запроса. Первый выбирает список участников. Второй набор выбирает заказы с условием ГДЕ Участник = &ПараметрУчастник, сортировкой по убыванию даты и директивой ВЫБРАТЬ ПЕРВЫЕ 3. На вкладке «Связи наборов данных» связываем наборы по полю Участник с передачей значения в параметр. СКД будет параметрически запрашивать ровно по 3 строки на каждого участника.

Способ 5. Программный обход выборки с итогами

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


Запрос = Новый Запрос;
Запрос.Текст = 
    "ВЫБРАТЬ
    |    ТЧ.Участник КАК Участник,
    |    Шапка.Ссылка КАК Ссылка,
    |    Шапка.Дата КАК Дата
    |ИЗ
    |    Документ.ЗаказТура КАК Шапка
    |    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ЗаказТура.Участники КАК ТЧ
    |        ПО Шапка.Ссылка = ТЧ.Ссылка
    |ГДЕ
    |    Шапка.Проведен
    |    И НЕ Шапка.Аннулирован
    |    И НЕ Шапка.ПометкаУдаления
    |УПОРЯДОЧИТЬ ПО
    |    Шапка.Дата УБЫВ
    |ИТОГИ ПО
    |    Участник";

ВыборкаУчастников = Запрос.Выполнить().Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);

ПоследниеЗаказы = Новый Соответствие;

Пока ВыборкаУчастников.Следующий() Цикл
    МассивДокументов = Новый Массив;
    ВыборкаДокументов = ВыборкаУчастников.Выбрать();
    
    Пока ВыборкаДокументов.Следующий() Цикл
        МассивДокументов.Добавить(ВыборкаДокументов.Ссылка);
        Если МассивДокументов.Количество() = 3 Тогда
            Прервать;
        КонецЕсли;
    КонецЦикла;
    
    ПоследниеЗаказы.Вставить(ВыборкаУчастников.Участник, МассивДокументов);
КонецЦикла;

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