В процессе разработки часто возникает задача: для набора исходных данных необходимо найти соответствующие им значения в базе данных по определенному правилу. Классический пример — для списка чисел найти ближайшие к ним по значению коды из большого справочника. Первое, что приходит на ум, — это перебрать наш список в цикле и для каждого элемента выполнить отдельный запрос к базе данных. Однако такой подход крайне неэффективен и создает избыточную нагрузку на сервер.
Рассмотрим ситуацию: у нас есть от 8 до 16 случайных чисел, и для каждого из них нужно найти ближайший, но не превышающий его, числовой код из справочника «Платформы». Выполнять 16 отдельных запросов — плохая практика. Проанализируем, как решить эту задачу оптимально, с помощью одного-единственного запроса.
Основная идея оптимального решения заключается в том, чтобы передать все наши исходные числа внутрь запроса и уже там, на стороне СУБД, выполнить все необходимые сопоставления. Для этого в 1С существует мощный механизм — временные таблицы. Они позволяют создавать в рамках одного запроса таблицы, которые "живут" только во время его выполнения, и работать с ними так же, как с обычными таблицами базы данных.
Разберем процесс по шагам.
Сначала нам нужно собрать все сгенерированные числа в одну структуру. Идеально для этой цели подходит ТаблицаЗначений. Создадим таблицу с одной колонкой, например, "СгенерированноеЧисло", и заполним ее нашими данными.
Посмотрим на пример кода:
// Создаем таблицу значений для передачи в запрос
ТаблицаСгенерированныхЧисел = Новый ТаблицаЗначений;
ТаблицаСгенерированныхЧисел.Колонки.Добавить("СгенерированноеЧисло", Новый ОписаниеТипов("Число"));
// Генерируем случайные числа и заполняем таблицу
// (в реальной задаче здесь будет ваш алгоритм генерации)
ГСЧ = Новый ГенераторСлучайныхЧисел();
КоличествоЧисел = ГСЧ.СлучайноеЧисло(8, 16);
Для сч = 1 По КоличествоЧисел Цикл
НоваяСтрока = ТаблицаСгенерированныхЧисел.Добавить();
НоваяСтрока.СгенерированноеЧисло = ГСЧ.СлучайноеЧисло(100, 999);
КонецЦикла;
Теперь у нас есть переменная ТаблицаСгенерированныхЧисел, которую мы можем передать в запрос.
Чтобы использовать нашу таблицу значений в запросе, нам понадобится МенеджерВременныхТаблиц. Запрос будет состоять из двух пакетов:
ТаблицыЗначений во временную таблицу.Проанализируем текст запроса:
Запрос = Новый Запрос;
// Использование менеджера временных таблиц обязательно
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Передаем нашу таблицу значений как параметр в запрос
Запрос.УстановитьПараметр("ТаблицаЧисел", ТаблицаСгенерированныхЧисел);
Запрос.Текст =
"ВЫБРАТЬ
| ТаблицаЧисел.СгенерированноеЧисло
|ПОМЕСТИТЬ ВТ_СгенерированныеЧисла
|ИЗ
| &ТаблицаЧисел КАК ТаблицаЧисел
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ИсходныеЧисла.СгенерированноеЧисло КАК СгенерированноеЧисло,
| МАКСИМУМ(Платформы.ЧисловойКод) КАК БлижайшийКод
|ИЗ
| ВТ_СгенерированныеЧисла КАК ИсходныеЧисла
| ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Платформы КАК Платформы
| ПО ИсходныеЧисла.СгенерированноеЧисло >= Платформы.ЧисловойКод
|
|СГРУППИРОВАТЬ ПО
| ИсходныеЧисла.СгенерированноеЧисло";
РезультатЗапроса = Запрос.Выполнить();
Выборка = РезультатЗапроса.Выбрать();
Пока Выборка.Следующий() Цикл
Сообщить("Для числа " + Выборка.СгенерированноеЧисло + " ближайший код: " + Выборка.БлижайшийКод);
КонецЦикла;
Разберем логику второго пакета подробнее:
ИЗ ВТ_СгенерированныеЧисла КАК ИсходныеЧисла: Мы выбираем данные из нашей временной таблицы, которую создали в первом пакете.ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Платформы КАК Платформы ПО ИсходныеЧисла.СгенерированноеЧисло >= Платформы.ЧисловойКод: Это ключевой момент решения. Мы соединяем каждую строку из нашей временной таблицы со всеми строками из справочника "Платформы", у которых ЧисловойКод меньше или равен нашему сгенерированному числу. Таким образом, для числа, например, 850, мы получим все коды из справочника, которые ему не превышают (849, 840, 700 и т.д.).СГРУППИРОВАТЬ ПО ИсходныеЧисла.СгенерированноеЧисло: Мы группируем результат по исходному числу, чтобы для каждого из них получить одно итоговое значение.МАКСИМУМ(Платформы.ЧисловойКод) КАК БлижайшийКод: Используя агрегатную функцию МАКСИМУМ(), мы из всех найденных кодов (которые меньше или равны исходному) выбираем самый большой. Это и будет искомое нами "ближайшее, но не превышающее" значение.Рассмотренный метод является наиболее оптимальным, но полезно знать и о других аспектах подобных задач.
Поиск абсолютно ближайшего значения
Что если бы задача стояла найти ближайшее число, которое может быть как меньше, так и больше исходного? В этом случае запрос усложняется. Нам пришлось бы найти два значения:
МАКСИМУМ).<= и агрегатную функцию МИНИМУМ).После получения этих двух кандидатов уже в коде 1С нужно было бы вычислить разницу (модуль) между исходным числом и каждым из найденных, чтобы определить, какое из них действительно ближе.
Важность индексации
Для того чтобы соединение в запросе работало быстро, особенно на больших справочниках (тысячи и десятки тысяч записей), очень важно, чтобы поле, по которому происходит поиск и соединение (в нашем случае ЧисловойКод), было проиндексировано. В 1С это можно настроить в свойствах реквизита в конфигураторе, установив флаг "Индексировать".
Сравнение с неоптимальными подходами
В итоге, использование пакетного запроса с временными таблицами — это стандартный, правильный и самый производительный способ решения задач по сопоставлению наборов данных в 1С. Он позволяет выполнить всю вычислительно сложную работу на стороне СУБД за одно обращение, минимизируя нагрузку и время выполнения.