При разработке отчетов и алгоритмов в среде «1С:Предприятие» программисты регулярно сталкиваются со следующей задачей: необходимо выбрать из базы данных не всю информацию, а только ограниченное количество первых или последних записей для каждого аналитического разреза. Например, вывести список всех контрагентов и для каждого из них показать ровно 3 последних документа поступления. Или выбрать всю номенклатуру склада и отобразить по 5 серий с наибольшим (или наименьшим) сроком годности.
В классическом языке SQL (а также во многих СУБД вроде MS SQL Server, PostgreSQL, Oracle) эта задача решается элегантно с помощью аналитических (оконных) функций, таких как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...). Однако в платформе «1С:Предприятие» язык запросов (SDBL) имеет ряд ограничений, и **оконные функции в нем отсутствуют**. Использование простой конструкции ПЕРВЫЕ N во вложенном запросе также не дает нужного результата, так как она ограничивает выборку глобально для всего запроса, а не внутри конкретной группы.
В этой статье мы подробно рассмотрим четыре основных и наиболее эффективных метода решения этой задачи: от классического самосоединения таблиц в запросе до современных подходов с использованием Системы Компоновки Данных (СКД) и оптимизированной постобработки данных на встроенном языке 1С.
---
Рассмотрим первый способ, который позволяет решить задачу исключительно средствами языка запросов 1С. Идея метода заключается в том, что мы берем исходную таблицу и соединяем её с собственной копией по полям группировки (например, по владельцу серии или контрагенту), накладывая при этом условие неравенства на сортируемое поле (например, на дату или срок годности).
Давайте проанализируем, как это работает на примере поиска 5 серий номенклатуры с наибольшим сроком годности для каждой позиции номенклатуры. Для каждой серии из первой таблицы мы подсчитываем, сколько серий из второй таблицы имеют срок годности больше, чем у текущей серии. Если таких серий оказывается меньше 5, значит, наша текущая серия входит в топ-5 самых «свежих».
Важное предупреждение о дубликатах дат: если у нескольких записей совпадают значения сортируемого поля (например, две серии имеют абсолютно одинаковый срок годности), то простое условие сравнения по дате вернет некорректный результат. В этом случае счетчик превысит ограничение, и записи вообще могут выпасть из выборки. Чтобы этого избежать, мы обязаны использовать второе, гарантированно уникальное поле (например, ссылку Ссылка или уникальный номер строки) для однозначной сортировки при совпадении дат.
Посмотрим на пример корректного и безопасного запроса:
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
ВложенныйЗапрос.СерияНоменклатуры КАК СерияНоменклатуры,
ВложенныйЗапрос.СрокГодности КАК СрокГодности
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
Серии1.Владелец КАК Владелец,
Серии1.Ссылка КАК СерияНоменклатуры,
Серии1.СрокГодности КАК СрокГодности
ИЗ
Справочник.СерииНоменклатуры КАК Серии1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.СерииНоменклатуры КАК Серии2
ПО Серии1.Владелец = Серии2.Владелец
И (Серии1.СрокГодности < Серии2.СрокГодности
ИЛИ (Серии1.СрокГодности = Серии2.СрокГодности И Серии1.Ссылка <= Серии2.Ссылка))
СГРУППИРОВАТЬ ПО
Серии1.Владелец,
Серии1.Ссылка,
Серии1.СрокГодности
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Серии2.Ссылка) <= 5
) КАК ВложенныйЗапрос
ПО Номенклатура.Ссылка = ВложенныйЗапрос.Владелец
УПОРЯДОЧИТЬ ПО
Номенклатура,
СрокГодности УБЫВ
Разберем логику связи в этом запросе по шагам:
Серии1 с таблицей Серии2 по равенству владельцев: Серии1.Владелец = Серии2.Владелец.Серии1.СрокГодности < Серии2.СрокГодности. Это позволяет сопоставить текущей серии все серии с большим сроком годности.ИЛИ (Серии1.СрокГодности = Серии2.СрокГодности И Серии1.Ссылка <= Серии2.Ссылка). Это гарантирует строгое распределение позиций по их уникальному идентификатору, если сроки годности совпадают.ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Серии2.Ссылка) <= 5 отсекает все записи, для которых в базе нашлось более 5 «более новых» серий. Таким образом, остаются ровно 5 записей.Несмотря на изящество, этот метод имеет существенный недостаток: **он обладает квадратичной вычислительной сложностью** $O(N^2)$ по отношению к количеству записей в разрезе одной группировки. Если у одной номенклатурной позиции в базе накопятся десятки тысяч серий или документов, такое самосоединение полностью заблокирует работу СУБД из-за необходимости построения декартова произведения таблиц. Поэтому данный подход рекомендуется использовать только на небольших объемах данных или при жестком ограничении периода выборки.
---
Если конечной целью вашей задачи является вывод данных в отчет, то СКД позволяет решить эту проблему невероятно просто, быстро и без написания сложных, тяжелых запросов. В СКД встроен внутренний механизм постобработки выборок, который умеет лимитировать вывод строк в подчиненных группировках.
Выясним, как настроить такое ограничение в компоновщике настроек:
Номенклатура, Серия, СрокГодности).Номенклатура.Серия).Серия) и перейдите в правое нижнее окно на вкладку «Другие настройки».5.СрокГодности Убыв).При формировании отчета СКД самостоятельно выберет из базы данных все строки, отсортирует их по убыванию срока годности в разрезе каждой номенклатуры и выведет на экран ровно по 5 первых записей для каждого владельца. Данный метод является **наиболее производительным** и простым в поддержке при разработке отчетов.
---
Если данные нужны вам не для отчета, а для дальнейших программных манипуляций в коде (например, для заполнения табличной части документа, проведения расчетов или передачи во внешние системы), то наиболее производительным решением будет разделение задачи на два этапа: выборка упорядоченного списка простым линейным запросом и последующая фильтрация результатов в цикле обхода.
Давайте подробно разберем этот алгоритм на практике. Мы пишем максимально простой и быстрый запрос с сортировкой по двум полям:
Номенклатура). Это гарантирует, что все записи по конкретному товару будут идти в результате запроса последовательно, друг за другом.СрокГодности УБЫВ).Затем мы обходим результат запроса в цикле и при помощи счетчика отсекаем лишние записи. Рассмотрим пример кода:
// Шаг 1. Формируем простой линейный запрос с сортировкой
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| СерииНоменклатуры.Владелец КАК Номенклатура,
| СерииНоменклатуры.Ссылка КАК Серия,
| СерииНоменклатуры.СрокГодности КАК СрокГодности
|ИЗ
| Справочник.СерииНоменклатуры КАК СерииНоменклатуры
|УПОРЯДОЧИТЬ ПО
| Номенклатура,
| СрокГодности УБЫВ";
РезультатЗапроса = Запрос.Выполнить();
// Шаг 2. Обходим выборку с ручным контролем смены группировки
ТекущаяНоменклатура = Неопределено;
СчетчикЗаписей = 0;
ТаблицаРезультатов = Новый ТаблицаЗначений;
ТаблицаРезультатов.Колонки.Добавить("Номенклатура", Новый ОписаниеТипов("СправочникСсылка.Номенклатура"));
ТаблицаРезультатов.Колонки.Добавить("Серия", Новый ОписаниеТипов("СправочникСсылка.СерииНоменклатуры"));
ТаблицаРезультатов.Колонки.Добавить("СрокГодности", Новый ОписаниеТипов("ОписаниеТипов"));
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
// Если началась новая номенклатура, сбрасываем счетчик количества выбранных серий
Если ТекущаяНоменклатура <> Выборка.Номенклатура Тогда
ТекущаяНоменклатура = Выборка.Номенклатура;
СчетчикЗаписей = 0;
КонецЕсли;
// Обрабатываем только первые 5 записей для каждой номенклатуры
Если СчетчикЗаписей < 5 Тогда
НоваяСтрока = ТаблицаРезультатов.Добавить();
ЗаполнитьЗначенияСвойств(НоваяСтрока, Выборка);
СчетчикЗаписей = СчетчикЗаписей + 1;
КонецЕсли;
КонецЦикла;
Почему этот способ работает в разы быстрее, чем сложные запросы с внутренними соединениями? Дело в том, что СУБД выполняет данный запрос за линейное время $O(N)$ с использованием индексов. Нам не требуется строить декартово произведение таблиц в памяти сервера баз данных. Обход выборки и фильтрация в оперативной памяти на сервере приложений 1С происходят практически мгновенно, даже если исходная выборка содержит сотни тысяч строк.
---
Если логика вашей конфигурации требует, чтобы результат выборки первых записей по группировкам обязательно находился внутри единого пакетного запроса (например, для последующего соединения с другими таблицами в СУБД), то метод №1 (самосоединение) необходимо существенно доработать с точки зрения оптимизации производительности.
Проанализируем ключевые правила оптимизации таких запросов:
Посмотрим на пример правильной структуры пакетного запроса с временными таблицами:
// Шаг 1: Отбираем только нужные данные во временную таблицу и индексируем их
ВЫБРАТЬ
Серии.Владелец КАК Номенклатура,
Серии.Ссылка КАК Серия,
Серии.СрокГодности КАК СрокГодности
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
Справочник.СерииНоменклатуры КАК Серии
ГДЕ
Серии.Владелец В (&СписокНоменклатуры) // ограничение выборки
И Серии.СрокГодности > ДАТАВРЕМЯ(2023, 1, 1)
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
СрокГодности,
Серия
;
// Шаг 2: Выполняем самосоединение проиндексированной временной таблицы
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Серия КАК Серия,
Т1.СрокГодности КАК СрокГодности
ИЗ
ВТ_ИсходныеДанные КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И (Т1.СрокГодности < Т2.СрокГодности
ИЛИ (Т1.СрокГодности = Т2.СрокГодности И Т1.Серия <= Т2.Серия))
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.Серия,
Т1.СрокГодности
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Серия) <= 5
УПОРЯДОЧИТЬ ПО
Номенклатура,
СрокГодности УБЫВ
Создание проиндексированной временной таблицы ВТ_ИсходныеДанные позволяет СУБД использовать операторы Index Seek при выполнении соединения по неравенству, сокращая время выполнения тяжелого запроса с нескольких минут до долей секунды.
---
Подводя итоги, дадим общие рекомендации по выбору оптимального инструмента для решения этой задачи в зависимости от контекста: