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