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

При разработке отчетов, механизмов ценообразования или систем расчета себестоимости в 1С перед программистом регулярно возникает классическая задача: получить срез последних записей в разрезе определенной группировки. Типичный пример — вывод цен пяти последних поставок по каждой позиции номенклатуры из регистра накопления Закупки. Сложность заключается в том, что в языке запросов платформы «1С:Предприятие» отсутствует поддержка стандартных оконных функций SQL, таких как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). Из-за этого разработчикам приходится прибегать к альтернативным, но не менее эффективным алгоритмам.

В этой статье мы подробно разберем четыре наиболее эффективных и проверенных на практике способа решения этой задачи. Мы проанализируем их производительность, рассмотрим детальные примеры кода и выясним, в каких ситуациях каждый из методов будет наиболее оптимальным.

Способ 1. Выборка с обходом результата запроса по группировкам (ИТОГИ)

Этот способ является одним из самых надежных и простых в реализации, когда нам требуется обработать данные программно (например, при заполнении табличной части документа, в модуле обработки или менеджере объекта). Вместо того чтобы пытаться ограничить количество строк на уровне базы данных сложными и "тяжелыми" соединениями, мы перекладываем задачу фильтрации на встроенный язык 1С.

Рассмотрим алгоритм реализации этого метода. Мы выбираем все движения по интересующей нас номенклатуре без ограничения количества, но упорядочиваем их по убыванию даты. При этом мы используем секцию ИТОГИ ПО для группировки данных по номенклатуре. Посмотрим на пример кода:

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

ВыборкаНоменклатуры = Запрос.Выполнить().Выбрать(ОбходРезультатЗапроса.ПоГруппировкам);
Пока ВыборкаНоменклатуры.Следующий() Цикл
    
    // Выводим или обрабатываем родительскую группировку
    Запись("Номенклатура: " + ВыборкаНоменклатуры.Номенклатура);
    
    // Переходим к детальным записям для текущей номенклатуры
    ВыборкаДетали = ВыборкаНоменклатуры.Выбрать();
    Счетчик = 0;
    
    Пока ВыборкаДетали.Следующий() Цикл
        
        // Нам нужны только первые 5 поступлений (так как сортировка ПЕРИОД УБЫВ)
        Счетчик = Счетчик + 1;
        Если Счетчик > 5 Тогда
            Прервать;
        КонецЕсли;
        
        ЦенаПоступления = ?(ВыборкаДетали.Количество = 0, 0, ВыборкаДетали.Стоимость / ВыборкаДетали.Количество);
        
        // Здесь выполняем обработку полученных данных, например, заполняем Таблицу Значений
        // Или производим необходимые расчеты
        
    КонецЦикла;
КонецЦикла;

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

• Плюсы: Запрос выполняется очень быстро, так как СУБД делает простую выборку и сортировку по индексу без ресурсоемких соединений. Весь расчет лимита выполняется на сервере приложений 1С чрезвычайно быстро. Это самое надежное решение с точки зрения стабильности планов запросов СУБД.
• Минусы: Этот метод не подходит, если результат запроса нужно передать напрямую в динамический список, СКД или использовать как вложенную таблицу в другом запросе. Кроме того, если по каждой позиции в базе накоплены десятки тысяч движений, СУБД вернет избыточный объем данных на сервер приложений, что может повысить потребление оперативной памяти. Для минимизации этого эффекта крайне важно накладывать жесткие отборы по периоду или списку номенклатуры в секции ГДЕ.

Способ 2. Использование коррелированного (связанного) подзапроса

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

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

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

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

Важные нюансы производительности: Для быстрой работы данного запроса в СУБД обязательно должны быть построены эффективные индексы. В случае с регистром накопления Закупки платформа 1С автоматически создает индекс по комбинации полей Номенклатура + Период, что гарантирует высокую скорость работы подзапроса. Однако на СУБД PostgreSQL при огромных объемах данных планировщик может выбрать неоптимальный план (например, Nested Loop вместо Index Scan), поэтому данный способ рекомендуется тщательно тестировать на реальных базах данных.

Способ 3. Математический метод с использованием левого соединения и группировки (HAVING)

Это классический паттерн проектирования SQL-запросов, позволяющий обойти отсутствие оконных функций в реляционных базах данных. Мы используем левое соединение таблицы самой с собой по условию «дата второго документа строго больше даты первого».

Давайте разберем логику этого метода. Если мы хотим получить 5 последних поступлений для каждого товара, это означает, что для самого последнего (1-го) поступления количество более поздних документов будет равно 0. Для 2-го по счету поступления найдется ровно 1 документ, который был позже него. Для 5-го поступления найдется ровно 4 документа, созданных позже. Таким образом, если мы сгруппируем данные и наложим условие в секции ИМЕЮЩИЕ (HAVING): КОЛИЧЕСТВО(Т2.Регистратор) < 5, мы получим ровно 5 последних документов по каждой номенклатуре.

Рассмотрим практическую реализацию этого запроса в 1С:

ВЫБРАТЬ
    Т1.Номенклатура КАК Номенклатура,
    Т1.Регистратор КАК Регистратор,
    Т1.Период КАК Период,
    МАКСИМУМ(ВЫБОР
        КОГДА Т1.Количество = 0 ТОГДА 0
        ИНАЧЕ Т1.Стоимость / Т1.Количество
    КОНЕЦ) КАК Цена
ИЗ
    РегистрНакопления.Закупки КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Закупки КАК Т2
    ПО Т1.Номенклатура = Т2.Номенклатура
    И Т1.Период < Т2.Период
ГДЕ
    Т1.Организация = &Организация
СГРУППИРОВАТЬ ПО
    Т1.Номенклатура,
    Т1.Регистратор,
    Т1.Период
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Регистратор) < 5
УПОРЯДОЧИТЬ ПО
    Номенклатура,
    Период УБЫВ

Критически важный анализ производительности: Несмотря на свою элегантность, этот метод имеет существенный недостаток — квадратичную сложность вычислений внутри каждой группы номенклатуры. Если по какому-то товару в базе зарегистрировано, например, 10 000 поступлений, то левое соединение по условию "меньше" породит миллионы временных строк для сопоставления. Это приведет к колоссальной нагрузке на процессор сервера СУБД и переполнению временной базы данных (tempdb). Используйте этот метод исключительно в тех случаях, когда вы гарантированно ограничили выборку небольшим периодом или когда количество документов на одну позицию номенклатуры заведомо не превышает нескольких десятков.

Способ 4. Реализация ограничения в Системе компоновки данных (СКД)

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

Разберем по шагам, как настроить такое ограничение:

1. Создайте стандартный набор данных-запрос в СКД, который выбирает все записи из регистра накопления Закупки без каких-либо ограничений на количество строк. Обязательно добавьте поле Период для упорядочивания.
2. Перейдите на вкладку Вычисляемые поля и добавьте новое поле с именем НомерСтрокиГруппировки.
3. Перейдите на вкладку Ресурсы. Добавьте ваше вычисляемое поле в список ресурсов. В колонке Выражение укажите следующую функцию компоновки данных: ВычислитьВыражение("НомерПоПорядкуВГруппировке()", "Номенклатура", "Группировка", "Период Убыв") Это выражение указывает СКД рассчитывать порядковый номер строки для каждой детальной записи внутри группировки по номенклатуре, сортируя данные по убыванию периода.
4. На вкладке Настройки добавьте структуру отчета: Группировка по Номенклатура, а внутри нее — Детальные записи.
5. Для Детальных записей перейдите на вкладку Отбор и добавьте условие: НомерСтрокиГруппировки Меньше Или Равно 5

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

Сравнительная таблица методов

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

• Обход выборки с ИТОГАМИ (Способ 1): Лучший выбор для кода, обработок, проведения документов. Легко отлаживается, работает быстро, не нагружает СУБД. Нельзя напрямую использовать в динамических списках.
• Коррелированный запрос (Способ 2): Идеален для сложных запросов, где результат нужно соединять с другими таблицами базы данных. Требует наличия индексов, имеет умеренную нагрузку на СУБД.
• Соединение с группировкой HAVING (Способ 3): Академический метод. Имеет крайне низкую производительность на больших объемах данных. Использовать с осторожностью и только при жестких отборах.
• Средства СКД (Способ 4): Лучшее и самое производительное решение для любых отчетов. Не требует усложнения SQL-запросов, гибко настраивается пользователем без изменения конфигурации.

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