В процессе разработки в среде «1С:Предприятие» программисты регулярно сталкиваются с необходимостью обработки таблиц значений (ТЗ). Типичный пример — задача «свертки» данных. Метод платформы ТаблицаЗначений.Свернуть() отлично справляется, когда нам нужно просто сгруппировать строки и просуммировать или найти максимум/минимум по числовым полям. Однако стандартный метод пасует, если нам требуется сгруппировать таблицу по ключевым полям, найти строку с минимальным показателем, но при этом сохранить в результате значения других колонок, которые соответствовали именно этой выбранной «лучшей» строке.
Разберем классическую ситуацию. У нас есть таблица со следующей структурой:
Если мы просто применим группировку по всем полям, уникальность нарушится из-за разных значений в колонке К3 (Партнер). Если мы исключим К3 из группировки, то потеряем информацию о том, какой именно поставщик предложил эту минимальную цену. Рассмотрим подробнее, как решить эту задачу изящно и оптимально с помощью запросов и инструментов встроенного языка 1С.
Этот подход является наиболее надежным и «классическим» с точки зрения реляционных баз данных. Мы разобьем задачу на два логических шага в рамках одного пакета запросов:
Проанализируем пример кода, реализующий эту схему:
// Создаем запрос для обработки переданной ТаблицыЗначений
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ИсходнаяТЗ.Артикул КАК Артикул,
| ИсходнаяТЗ.Производитель КАК Производитель,
| ИсходнаяТЗ.Партнер КАК Партнер,
| ИсходнаяТЗ.Цена КАК Цена
|ПОМЕСТИТЬ ВТ_ИсходныеДанные
|ИЗ
| &ТаблицаНоменклатуры КАК ИсходнаяТЗ
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ВТ_Минимумы.Артикул КАК Артикул,
| ВТ_Минимумы.Производитель КАК Производитель,
| ВТ_Минимумы.Цена КАК Цена,
| МАКСИМУМ(ВТ_ИсходныеДанные.Партнер) КАК Партнер
|ИЗ
| (ВЫБРАТЬ
| ВТ_Группировка.Артикул КАК Артикул,
| ВТ_Группировка.Производитель КАК Производитель,
| МИНИМУМ(ВТ_Группировка.Цена) КАК Цена
| ИЗ
| ВТ_ИсходныеДанные КАК ВТ_Группировка
|
| СГРУППИРОВАТЬ ПО
| ВТ_Группировка.Артикул,
| ВТ_Группировка.Производитель) КАК ВТ_Минимумы
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК ВТ_ИсходныеДанные
| ПО ВТ_Минимумы.Артикул = ВТ_ИсходныеДанные.Артикул
| И ВТ_Минимумы.Производитель = ВТ_ИсходныеДанные.Производитель
| И ВТ_Минимумы.Цена = ВТ_ИсходныеДанные.Цена
|
|СГРУППИРОВАТЬ ПО
| ВТ_Минимумы.Артикул,
| ВТ_Минимумы.Производитель,
| ВТ_Минимумы.Цена";
Запрос.УстановитьПараметр("ТаблицаНоменклатуры", МояТаблицаНоменклатуры);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Обратите внимание на важный нюанс! В итоговой выборке мы применили агрегатную функцию МАКСИМУМ(ВТ_ИсходныеДанные.Партнер) и добавили группировку по остальным полям. Выясним причину: если в исходных данных для одного и того же артикула и производителя у двух разных партнеров будет указана абсолютно одинаковая минимальная цена, то обычное соединение без группировки вернет две строки (произойдет дублирование данных). Использование функции МАКСИМУМ или МИНИМУМ гарантирует, что в результат попадет строго одна запись для каждой уникальной пары «Артикул + Производитель».
Если обрабатываемая таблица значений содержит десятки или сотни тысяч строк, то операция внутреннего соединения может сильно нагрузить процессор сервера СУБД. Чтобы избежать замедлений (Full Table Scan), необходимо принудительно проиндексировать временную таблицу по ключевым полям связи. Посмотрим, как модифицировать первый пакет запроса:
"ВЫБРАТЬ
| ИсходнаяТЗ.Артикул КАК Артикул,
| ИсходнаяТЗ.Производитель КАК Производитель,
| ИсходнаяТЗ.Партнер КАК Партнер,
| ИсходнаяТЗ.Цена КАК Цена
|ПОМЕСТИТЬ ВТ_ИсходныеДанные
|ИЗ
| &ТаблицаНоменклатуры КАК ИсходнаяТЗ
|
|ИНДЕКСИРОВАТЬ
| Артикул,
| Производитель,
| Цена"
Добавление инструкции ИНДЕКСИРОВАТЬ позволяет СУБД мгновенно находить нужные сопутствующие строки по сформированному индексу, уменьшая время выполнения операции до долей секунды.
Существует еще один изящный способ реализации логики свертки с помощью оператора В. Он позволяет отказаться от явного соединения таблиц в коде, делая запрос визуально более лаконичным. Современные СУБД оптимизируют подобные конструкции, преобразуя их в эффективный план выполнения.
ВЫБРАТЬ
ВТ.Артикул КАК Артикул,
ВТ.Производитель КАК Производитель,
ВТ.Партнер КАК Партнер,
ВТ.Цена КАК Цена
ИЗ
ВТ_ИсходныеДанные КАК ВТ
ГДЕ
(ВТ.Артикул, ВТ.Производитель, ВТ.Цена) В
(ВЫБРАТЬ
Вложенный.Артикул,
Вложенный.Производитель,
МИНИМУМ(Вложенный.Цена)
ИЗ ВТ_ИсходныеДанные КАК Вложенный
СГРУППИРОВАТЬ ПО Вложенный.Артикул, Вложенный.Производитель)
В данном случае условие ГДЕ (А, Б, В) В (ВЫБРАТЬ...) осуществляет фильтрацию исходного массива данных, оставляя только те строки, у которых цена совпадает с минимальной по группе. Помните, что для исключения потенциальных дублей при совпадении цен у разных партнеров, к результату этого запроса также желательно применить финальное сворачивание/группировку.
Иногда передача таблицы значений в запрос и обратно на клиент (или сервер приложений) нецелесообразна из-за накладных расходов. Если задача выполняется на клиенте или вам принципиально важно не нагружать сервер базы данных, можно использовать элегантный алгоритм сортировки и обхода в обратном направлении.
Идея метода проста: мы сортируем таблицу так, чтобы строки группировки шли последовательно, а сортировка ресурса (Цены) была выставлена по возрастанию. Таким образом, первая строка каждой группы гарантированно будет содержать минимальную цену. Все последующие строки в этой группе мы можем просто удалить.
Разберем по шагам реализацию данного алгоритма:
// Шаг 1. Сортируем таблицу по ключевым полям и по возрастанию цены
// Сортировка по возрастанию гарантирует, что строка с МИНИМАЛЬНОЙ ценой будет ПЕРВОЙ в группе.
МояТаблицаНоменклатуры.Сортировать("Артикул Возр, Производитель Возр, Цена Возр");
// Шаг 2. Обходим таблицу с конца, чтобы при удалении строк не сбивались индексы
ВсегоСтрок = МояТаблицаНоменклатуры.Количество();
Для ОбратныйИндекс = -(ВсегоСтрок - 1) По 0 Цикл
ТекущийИндекс = -ОбратныйИндекс;
// Первая строка таблицы (индекс 0) не имеет предыдущей строки для сравнения, ее пропускаем
Если ТекущийИндекс = 0 Тогда
Продолжить;
КонецЕсли;
ТекущаяСтрока = МояТаблицаНоменклатуры[ТекущийИндекс];
ПредыдущаяСтрока = МояТаблицаНоменклатуры[ТекущийИндекс - 1];
// Шаг 3. Если ключи группировки совпадают, удаляем текущую строку
// Так как таблица отсортирована, текущая строка содержит заведомо большую (или равную) цену,
// а строка с минимальной ценой останется нетронутой в самом начале группы.
Если ТекущаяСтрока.Артикул = ПредыдущаяСтрока.Артикул
И ТекущаяСтрока.Производитель = ПредыдущаяСтрока.Производитель Тогда
МояТаблицаНоменклатуры.Удалить(ТекущийИндекс);
КонецЕсли;
КонецЦикла;
Преимущества данного метода:
Подведем краткий итог. Выбор метода зависит от контекста выполнения задачи: