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