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