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

← На главную

Приветствуем вас, уважаемые коллеги! При разработке отчетов и учетных механизмов в системе «1С:Предприятие» программисты регулярно сталкиваются с классической задачей: требуется получить актуальные остатки товаров на складах или у контрагентов и одновременно вывести дату последнего движения (например, дату последней поставки или установки цены). На первый взгляд, задача кажется тривиальной, но попытка решить её «в лоб» с помощью конструкции ВЫБРАТЬ ПЕРВЫЕ 1 в левом соединении часто приводит к ошибкам или катастрофическому падению производительности СУБД.

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

Анализ проблемы: почему «ВЫБРАТЬ ПЕРВЫЕ 1» внутри ЛЕВОЕ СОЕДИНЕНИЕ не работает?

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

Первоначальный (ошибочный) запрос автора выглядел следующим образом:


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

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

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

Решение с использованием коррелированного подзапроса (работает, но медленно)

В процессе обсуждения на форуме участники предложили автору использовать коррелированный подзапрос непосредственно в условии связи ПО. В итоге автор пришел к следующему итоговому варианту, который решил его сиюминутную проблему:


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

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

Почему же этот код считается плохой практикой (антипаттерном)?

Проблема производительности (Nested Loops): При выполнении такого запроса СУБД будет вынуждена делать вложенный поиск во внутреннем регистре для каждой строки внешнего набора данных. Если у вас в остатках окажется 10 000 строк, СУБД выполнит дополнительный поиск по индексу физической таблицы 10 000 раз! На реальных промышленных базах данных это приведет к зависанию отчета или перегрузке процессора сервера СУБД.
Нарушение стандартов разработки «1С»: Стандарты разработки фирмы «1С» прямо запрещают использование вложенных запросов в условиях соединения (секция ПО), так как оптимизаторы СУБД (особенно PostgreSQL) часто не могут построить эффективный план выполнения для таких конструкций, что приводит к непредсказуемому времени выполнения.

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

Для того чтобы запрос работал мгновенно даже на миллионных объемах данных, мы должны спроектировать его с использованием механизма временных таблиц. Идея проста: сначала мы подготовим плоский список уникальных аналитик и их максимальных дат во временную таблицу, проиндексируем её, а затем выполним классическое плоское левое соединение.

Разберем это оптимальное решение по шагам.

Шаг 1. Получение максимальных дат движений во временную таблицу

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


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

СГРУППИРОВАТЬ ПО
    ТоварыУКонтрагентов.Номенклатура,
    ТоварыУКонтрагентов.СубАрендатор,
    ТоварыУКонтрагентов.Контрагент

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    СубАрендатор,
    Контрагент
;

Шаг 2. Соединение остатков с подготовленной временной таблицей

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


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

Преимущества использования временных таблиц в данной задаче:

Высокая скорость выполнения: СУБД соединяет две физически существующие и проиндексированные таблицы. Вместо построения планов типа Nested Loops (вложенные циклы) СУБД применит высокоэффективные алгоритмы слияния Hash Match или Merge Join.
Простота отладки: Вы всегда можете запустить консоль запросов и посмотреть содержимое временной таблицы ВТ_МаксимальныеДатыПрихода в отдельности, чтобы проверить правильность расчета максимальных дат.
Соответствие стандартам 1С: Данный код легко пройдет любой технологический аудит или проверку при сдаче на сертификат «1С:Специалист».

Важная деталь: использование класса «Граница»

Обратите внимание на параметр &ГраницаСреза во втором запросе. В первоначальном коде автора использовалась конструкция КОНЕЦПЕРИОДА(&ДатаСреза, ДЕНЬ).

В платформе «1С:Предприятие» при передаче обычной даты (даже времени конца дня 23:59:59) в параметры виртуальной таблицы остатков СУБД может не учесть движения, записанные ровно в эту же секунду (в зависимости от СУБД и режима совместимости). Чтобы остатки гарантированно включали все документы, проведенные в последнюю секунду дня, необходимо формировать параметр на стороне встроенного языка с помощью объекта Граница:


ГраницаПериода = Новый Граница(КонецДня(ДатаСреза), ВидГраницы.Включая);
Запрос.УстановитьПараметр("ГраницаСреза", ГраницаПериода);

Применяйте временные таблицы и правильные типы параметров в своих разработках — это гарантирует стабильность и высокую скорость работы ваших учетных систем при любых объемах данных!