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