При работе с большими объемами данных в 1С часто возникает задача: получить не все записи сразу, а лишь ограниченную выборку для каждого периода. Например, нам нужно выбрать по 30 документов поступления за каждый год, начиная с 1998-го. Стандартная конструкция ВЫБРАТЬ ПЕРВЫЕ 30 в языке запросов здесь не поможет, так как она ограничит общий результат всей выборки, а не каждую группу по отдельности. В этой статье мы разберем несколько эффективных способов решения этой задачи: от современных оконных функций до возможностей системы компоновки данных (СКД).
Начиная с версии платформы 8.3.20, разработчикам стал доступен мощный инструмент — оконные функции. Это наиболее производительный и элегантный способ пронумеровать строки внутри каждой группы (в нашем случае — года) и затем отсечь лишние. Рассмотрим, как это реализовать. Нам понадобится функция НОМЕР_СТРОКИ() с конструкцией ПЕРЕГОРОДКА ПО.
Проанализируем пример запроса, который решает нашу задачу:
ВЫБРАТЬ
ПартииОбороты.Регистратор КАК Документ,
НАЧАЛОПЕРИОДА(ПартииОбороты.Период, ГОД) КАК ПериодГод,
НОМЕР_СТРОКИ() ИЗ (
ВЫБРАТЬ
Партии.Регистратор,
Партии.Период
ИЗ
РегистрНакопления.ПартииТоваровНаСкладах КАК Партии
УПОРЯДОЧИТЬ ПО
Партии.Период,
Партии.Регистратор
) КАК Подзапрос
ПЕРЕГОРОДКА ПО
НАЧАЛОПЕРИОДА(Партии.Период, ГОД)
УПОРЯДОЧИТЬ ПО
Партии.Период
ПОМЕСТИТЬ ВТ_НумерованныеСтроки;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_НумерованныеСтроки.Документ,
ВТ_НумерованныеСтроки.ПериодГод
ИЗ
ВТ_НумерованныеСтроки КАК ВТ_НумерованныеСтроки
ГДЕ
ВТ_НумерованныеСтроки.НомерСтроки <= 30
Важный момент: использование оконных функций требует четкого понимания того, как работает ПЕРЕГОРОДКА ПО (аналог PARTITION BY в SQL). Мы как бы разделяем всю таблицу на "пакеты" по годам, и внутри каждого пакета счетчик строк начинается заново. Это позволяет нам в конечном итоге просто отфильтровать записи, где номер строки меньше или равен 30.
Если ваша задача — вывести данные в отчет, то программирование сложных запросов может и не понадобиться. СКД обладает штатным механизмом ограничения количества записей в группах. Разберем по шагам, как это настроить:
ПЕРВЫЕ.ПериодГод (или используйте дополнение периодов).30.При таком подходе компоновщик макета сам сформирует оптимальные запросы к базе данных. Для каждого значения из группировки "Год" система выберет ровно указанное количество записей. Это самый простой и надежный метод для отчетов.
Если вы работаете на версии платформы ниже 8.3.20 и не можете использовать СКД, придется прибегнуть к классическому методу — соединению таблицы с самой собой по условию неравенства. Мы будем подсчитывать, сколько записей "предшествуют" текущей записи внутри одного года.
Рассмотрим логику этого метода:
ВЫБРАТЬ
Т1.ДокументОприходования КАК Документ,
Т1.ПериодГод КАК Год
ИЗ
ВТ_Данные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
ПО Т1.ПериодГод = Т2.ПериодГод
И Т1.ДокументОприходования.МоментВремени > Т2.ДокументОприходования.МоментВремени
СГРУППИРОВАТЬ ПО
Т1.ДокументОприходования,
Т1.ПериодГод
ИМЕЮЩИЕ
КОЛИЧЕСТВО(Т2.ДокументОприходования) < 30
Здесь ВТ_Данные — это предварительно подготовленная временная таблица со списком документов. Суть проста: если для документа "А" в этом же году существует менее 30 документов, у которых дата меньше, чем у "А", то документ "А" попадает в нашу выборку. Внимание: этот метод крайне ресурсозатратный для больших таблиц, так как количество сравнений растет в геометрической прогрессии.
Когда запрос становится слишком тяжелым ("монструозным") и СУБД не может его эффективно оптимизировать, на помощь приходит комбинированный метод. Вместо одного сложного запроса мы выполним серию простых.
Выполним следующие шаги:
ГраницаГода и инструкцией ВЫБРАТЬ ПЕРВЫЕ 30.ТаблицаЗначений.Посмотрим на пример кода:
ЗапросГода = Новый Запрос("ВЫБРАТЬ РАЗЛИЧНЫЕ НАЧАЛОПЕРИОДА(Период, ГОД) КАК Год ИЗ РегистрНакопления.ПартииТоваровНаСкладах");
ВыборкаГод = ЗапросГода.Выполнить().Выбрать();
ОбщийРезультат = Новый ТаблицаЗначений;
Пока ВыборкаГод.Следующий() Цикл
ЗапросДоки = Новый Запрос;
ЗапросДоки.Текст =
"ВЫБРАТЬ ПЕРВЫЕ 30
| Регистратор
|ИЗ
| РегистрНакопления.ПартииТоваровНаСкладах
|ГДЕ
| Период МЕЖДУ &НачДата И &КонДата
|УПОРЯДОЧИТЬ ПО
| Период, Ссылка";
ЗапросДоки.УстановитьПараметр("НачДата", НачалоГода(ВыборкаГод.Год));
ЗапросДоки.УстановитьПараметр("КонДата", КонецГода(ВыборкаГод.Год));
ТЗ = ЗапросДоки.Выполнить().Выгрузить();
// Объединяем ТЗ с ОбщийРезультат
КонецЦикла;
Этот метод часто работает быстрее на огромных базах, так как СУБД гораздо проще выполнить 20 маленьких запросов по индексу, чем один огромный с вложенными соединениями.
Проанализируем еще один критический аспект — детерминированность выборки. Документы часто создаются в одну и ту же секунду. Чтобы при каждом запуске запроса вы получали одни и те же "первые 30" документов, необходимо всегда добавлять уникальное поле в сортировку.
Всегда используйте: УПОРЯДОЧИТЬ ПО Период, Ссылка. Если сортировать только по Период, порядок записей с одинаковым временем может меняться в зависимости от внутренней логики работы SQL-сервера, что приведет к непредсказуемым результатам.
Также помните о различиях в виртуальных таблицах. Если ваша цель — получить именно документы (регистраторы), работайте с реальной таблицей регистра или виртуальной таблицей Обороты с периодичностью Регистратор. Использование периодичности Год в параметрах виртуальной таблицы приведет к тому, что система вернет вам агрегированные данные, а не конкретные документы, что является частой ошибкой при решении подобных задач.