При разработке отчетов и механизмов проведения документов в 1С программисты часто сталкиваются с классической задачей теории баз данных: выбрать одну строку из группы, обладающую экстремальным (минимальным или максимальным) значением. Задача усложняется, когда при равенстве минимальных значений необходимо применить дополнительный критерий отбора — например, выбрать наиболее ранний документ по дате.
В стандартном SQL эта задача элегантно решается с помощью оконных функций (таких как ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)). Однако в языке запросов 1С оконные функции не поддерживаются. Использование конструкции ВЫБРАТЬ ПЕРВЫЕ 1 спасает только тогда, когда нам нужно получить ровно одну строку из всей выборки, но совершенно не подходит, если срез нужно получить в разрезе каждой группы товаров или субконто.
В данной статье мы подробно разберем, как решить эту задачу без использования временных таблиц в один запрос, почему этот метод может быть опасен для производительности СУБД, и как правильно реализовать оптимальное решение с точки зрения стандартов разработки 1С.
Представим, что у нас есть следующая таблица движений (или остатков на каждую дату документа):
Наша цель — получить на выходе для каждого уникального товара ровно одну запись с минимальным остатком. Если минимальные остатки совпадают (как у Товара 1 в позициях 3 и 4, где количество равно 9), мы должны взять наиболее ранний документ по дате. Таким образом, ожидаемый результат:
Выясним причину, почему простые группировки здесь не работают. Если мы просто сгруппируем таблицу по полю Товар и выберем МИНИМУМ(Количество), мы потеряем связь с регистратором. Если же мы добавим Регистратор в поля группировки, то СУБД вернет нам абсолютно все строки, так как регистраторы у всех записей уникальны.
Существует классический паттерн реляционной алгебры, позволяющий решить задачу «выбора экстремума в группе» за один проход без использования временных таблиц. Этот метод основан на левом соединении таблицы с самой собой по определенному условию несоответствия (Anti-Join) с последующей фильтрацией по значению NULL.
Рассмотрим ситуацию подробнее. Мы берем таблицу движений Т1 и соединяем ее левым соединением с точно такой же таблицей Т2 по совпадению товара (субконто). Но в условие связи мы закладываем следующее правило: строка из Т2 должна сопоставиться только в том случае, если в ней количество меньше, чем в Т1, ИЛИ если количество равно, но дата документа в Т2 меньше (раньше), чем в Т1.
Если для строки из Т1 в таблице Т2 найдется строка, удовлетворяющая этому условию, значит, строка в Т1 не является минимальной. Если же для строки из Т1 в таблице Т2 не найдется ни одной записи (значения полей Т2 вернут NULL), это означает, что перед нами абсолютный минимум!
Посмотрим на пример реализации этого запроса в 1С:
ВЫБРАТЬ
Т1.Субконто1 КАК Субконто1,
Т1.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
Т1.Регистратор КАК Регистратор,
Т1.Регистратор.Дата КАК РегистраторДата
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК Т2
ПО Т1.Субконто1 = Т2.Субконто1
И (Т1.КоличествоКонечныйОстаток > Т2.КоличествоКонечныйОстаток
ИЛИ (Т1.КоличествоКонечныйОстаток = Т2.КоличествоКонечныйОстаток
И Т1.Регистратор.Дата > Т2.Регистратор.Дата))
ГДЕ
Т2.Субконто1 ЕСТЬ NULL
Преимущества метода: Запрос пишется в один шаг, не требует создания временных таблиц и легко читается разработчиками, знакомыми с реляционной теорией СУБД.
Недостатки метода: Крайне низкая производительность на реальных базах данных. Виртуальная таблица ОстаткиИОбороты не существует физически на диске — СУБД рассчитывает ее "на лету". Левое соединение такой таблицы самой с собой заставляет СУБД производить тяжелые вычисления повторно, что приводит к колоссальной нагрузке на процессор сервера и может вызывать взаимоблокировки.
Для оптимизации работы СУБД стандарты разработки фирмы «1С» настоятельно рекомендуют использовать временные таблицы. Это позволяет разбить сложную задачу на несколько простых шагов, зафиксировать промежуточные расчеты в памяти (или базе tempdb) и использовать индексы для быстрого сопоставления.
Разберем по шагам логику построения такого пакета запросов:
Проанализируем пример кода, реализующий эту схему:
// Шаг 1: Находим минимальный остаток для каждого субконто
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Субконто1,
МИНИМУМ(ХозрасчетныйОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КоличествоМинимум
ПОМЕСТИТЬ ВТ_МинимальныеОстатки
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК ХозрасчетныйОстаткиИОбороты
СГРУППИРОВАТЬ ПО
ХозрасчетныйОстаткиИОбороты.Субконто1
ИНДЕКСИРОВАТЬ ПО
Субконто1
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Находим минимальную дату для строк с минимальным остатком
ВЫБРАТЬ
ОстаткиИОбороты.Субконто1 КАК Субконто1,
ОстаткиИОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
МИНИМУМ(ОстаткиИОбороты.Регистратор.Дата) КАК МинимальнаяДата
ПОМЕСТИТЬ ВТ_МинимумИДата
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК ОстаткиИОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МинимальныеОстатки КАК ВТ_Минимумы
ПО ОстаткиИОбороты.Субконто1 = ВТ_Минимумы.Субконто1
И ОстаткиИОбороты.КоличествоКонечныйОстаток = ВТ_Минимумы.КоличествоМинимум
СГРУППИРОВАТЬ ПО
ОстаткиИОбороты.Субконто1,
ОстаткиИОбороты.КоличествоКонечныйОстаток
ИНДЕКСИРОВАТЬ ПО
Субконто1,
КоличествоКонечныйОстаток,
МинимальнаяДата
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Получаем итоговый документ-регистратор
ВЫБРАТЬ
ОстаткиИОбороты.Субконто1 КАК Субконто1,
ВТ_Итог.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
ОстаткиИОбороты.Регистратор КАК Регистратор,
ВТ_Итог.МинимальнаяДата КАК РегистраторДата
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК ОстаткиИОбороты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МинимумИДата КАК ВТ_Итог
ПО ОстаткиИОбороты.Субконто1 = ВТ_Итог.Субконто1
И ОстаткиИОбороты.КоличествоКонечныйОстаток = ВТ_Итог.КоличествоКонечныйОстаток
И ОстаткиИОбороты.Регистратор.Дата = ВТ_Итог.МинимальнаяДата
Хотя этот метод требует написания большего объема кода и создания нескольких временных таблиц, он работает на порядок быстрее первого варианта при объемах данных более нескольких тысяч строк. Индексация временных таблиц позволяет СУБД мгновенно находить нужные строки без повторного сканирования всего регистра.
Если выборка данных небольшая (в пределах 5-10 тысяч строк), то оптимальным решением может стать перенос логики фильтрации с СУБД на сервер приложений 1С. Мы можем написать максимально простой и легкий запрос для СУБД, а "схлопывание" данных до минимальных значений произвести методами встроенного языка.
Алгоритм действий следующий:
ТаблицаЗначений.Рассмотрим пример кода на встроенном языке:
// Выбираем исходные данные простым запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ОстаткиИОбороты.Субконто1 КАК Товар,
| ОстаткиИОбороты.КоличествоКонечныйОстаток КАК Количество,
| ОстаткиИОбороты.Регистратор КАК Регистратор,
| ОстаткиИОбороты.Регистратор.Дата КАК ДатаРегистратора
|ИЗ
| РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&д1, &д2, Регистратор, , Счет = &Счет И Организация = &Организация, , ) КАК ОстаткиИОбороты";
Запрос.УстановитьПараметр("д1", ПериодНачало);
Запрос.УстановитьПараметр("д2", ПериодКонец);
Запрос.УстановитьПараметр("Счет", ПланыСчетов.Хозрасчетный.ТоварыНаСкладах);
Запрос.УстановитьПараметр("Организация", Организация);
ТаблицаДвижений = Запрос.Выполнить().Выгрузить();
// Сортируем: сначала по возрастанию количества, затем по дате от ранних к поздним
ТаблицаДвижений.Сортировать("Количество Возр, ДатаРегистратора Возр");
// Создаем итоговую таблицу для хранения результатов
ИтоговаяТаблица = ТаблицаДвижений.СкопироватьКолонки();
ОтсеянныеТовары = Новый Соответствие;
Для Каждого СтрокаДвижения Из ТаблицаДвижений Цикл
// Если товар еще не обрабатывался, значит текущая строка содержит минимальное количество и раннюю дату
Если ОтсеянныеТовары[СтрокаДвижения.Товар] = Неопределено Тогда
ЗаполнитьЗначенияСвойств(ИтоговаяТаблица.Добавить(), СтрокаДвижения);
ОтсеянныеТовары.Вставить(СтрокаДвижения.Товар, Истина);
КонецЕсли;
КонецЦикла;
// В переменной ИтоговаяТаблица теперь находятся только нужные нам уникальные строки
Когда использовать этот метод: Данный подход идеален для динамических списков, заполнения небольших табличных частей в документах или при разработке обработок, где критически важна скорость реакции интерфейса на малых объемах данных.
Для того чтобы выбрать наиболее подходящий метод для вашей задачи, сопоставим их по ключевым характеристикам:
Применение этих практических подходов позволит вам писать не просто работающий, но и высокопроизводительный код, устойчивый к росту объемов базы данных.