В процессе разработки часто возникает задача сопоставить данные из двух таблиц не по прямому совпадению значений в полях, а по их порядковому номеру в отсортированном списке. Например, нужно связать первую по дате запись из одной таблицы с первой по дате записью из другой, вторую со второй и так далее. Классический пример — сопоставление плановых и фактических поступлений денежных средств по принципу ФИФО (первый пришел — первый ушел).
Проанализируем конкретную ситуацию: есть две таблицы. В одной три записи, в другой — четыре. Необходимо получить результат, где минимальное значение из первой таблицы связано с минимальным из второй, следующее по возрастанию — со следующим, и так далее. Итоговое количество строк должно быть равно минимальному количеству записей в одной из таблиц.
Давайте разберем, как решить эту задачу с помощью языка запросов 1С.
Основная идея заключается в том, чтобы искусственно создать общее поле для связи. Для этого мы пронумеруем строки в каждой таблице в нужном нам порядке, а затем соединим таблицы по этому новому полю-номеру.
Рассмотрим по шагам:
Поле1) с помощью конструкции УПОРЯДОЧИТЬ ПО.НомерСтроки, и присвоим ему значение с помощью функции АВТОНОМЕРЗАПИСИ(). Эта функция пронумерует отсортированные строки, начиная с 1.НомерСтроки.НомерСтроки. Тип соединения выберем ВНУТРЕННЕЕ, так как нам нужны только те строки, для которых нашлись пары в обеих таблицах.Посмотрим на пример кода. Сначала подготовим исходные данные, как в задаче:
ВЫБРАТЬ
1 КАК Поле1,
"Л" КАК Поле2
ПОМЕСТИТЬ Таблица1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
2,
"Л"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
3,
"Л"
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
5 КАК Поле1,
"П" КАК Поле2
ПОМЕСТИТЬ Таблица2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
7,
"П"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
6,
"П"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
8,
"П"
А теперь напишем запрос, который решает нашу задачу:
ВЫБРАТЬ
Таблица1.Поле1,
Таблица1.Поле2,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Таблица1
ИЗ
Таблица1 КАК Таблица1
УПОРЯДОЧИТЬ ПО
Поле1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Таблица2.Поле1,
Таблица2.Поле2,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Таблица2
ИЗ
Таблица2 КАК Таблица2
УПОРЯДОЧИТЬ ПО
Поле1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Поле1 КАК Поле1,
Т1.Поле2 КАК Поле2,
Т2.Поле1 КАК Поле3,
Т2.Поле2 КАК Поле4
ИЗ
ВТ_Таблица1 КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК Т2
ПО Т1.НомерСтроки = Т2.НомерСтроки
В результате выполнения этого запроса мы получим именно тот результат, который требовался:
Поле1 = 1 из первой таблицы соединится со строкой с Поле1 = 5 из второй (минимальные значения).Поле1 = 2 соединится со строкой с Поле1 = 6 (вторые по возрастанию значения).Поле1 = 3 соединится со строкой с Поле1 = 7 (третьи по возрастанию значения).Запись с Поле1 = 8 из второй таблицы не попадет в результат, так как для нее не найдется пары в первой таблице с номером строки 4.
При использовании этого метода важно помнить о некоторых нюансах работы функции АВТОНОМЕРЗАПИСИ():
ПОМЕСТИТЬ. Эту функцию нельзя использовать в простом запросе ВЫБРАТЬ или во вложенном запросе без создания временной таблицы. Она предназначена исключительно для нумерации строк при их помещении во временную таблицу.УПОРЯДОЧИТЬ ПО. Если ее не указать, порядок строк будет непредсказуемым и может меняться от запуска к запуску, что приведет к неверным результатам.Что если в наших данных могут быть дубли? Например, два плановых платежа на одну и ту же дату. Стандартная сортировка УПОРЯДОЧИТЬ ПО ДатаПлатежа не гарантирует, что эти две записи всегда будут идти в одном и том же порядке. Это может привести к тому, что при повторном формировании отчета связь между записями "поплывет".
Решение: чтобы обеспечить стабильную, или детерминированную, сортировку, в конец списка полей УПОРЯДОЧИТЬ ПО всегда добавляйте уникальное поле. Чаще всего для этого используют поле Ссылка документа или УникальныйИдентификатор.
Пример для документов:
УПОРЯДОЧИТЬ ПО
ДатаПлатежа,
Ссылка
Даже если Ссылка не нужна в итоговой выборке, ее добавление в сортировку гарантирует, что порядок записей будет одинаковым при каждом выполнении запроса.
В исходной задаче требовалось получить количество записей, равное минимальному набору. Для этого идеально подходит ВНУТРЕННЕЕ СОЕДИНЕНИЕ (INNER JOIN).
Однако в реальных задачах сверки "План-Факт" часто требуется видеть всю картину: не только совпадения, но и расхождения. В этом случае лучше использовать ПОЛНОЕ СОЕДИНЕНИЕ (FULL JOIN). Оно позволит увидеть:
При использовании ПОЛНОЕ СОЕДИНЕНИЕ нужно будет дополнительно обрабатывать значения NULL в полях тех таблиц, где пара не нашлась.
Когда вы работаете с большими объемами данных (тысячи и десятки тысяч строк), операция соединения таблиц может стать узким местом по производительности. Чтобы ускорить ее, после создания временных таблиц рекомендуется явно создавать индекс по полю, которое будет использоваться для связи.
Делается это с помощью команды ИНДЕКСИРОВАТЬ ПО:
ВЫБРАТЬ
Таблица1.Поле1,
Таблица1.Поле2,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Таблица1
ИЗ
Таблица1 КАК Таблица1
УПОРЯДОЧИТЬ ПО
Поле1
;
ИНДЕКСИРОВАТЬ ПО ВТ_Таблица1.НомерСтроки;
Это действие подскажет СУБД использовать эффективный механизм поиска по индексу вместо полного перебора строк, что значительно ускорит выполнение финального запроса на соединение.
Важно понимать: описанный метод подходит только для ситуаций "один к одному", когда одна плановая запись закрывается ровно одной фактической. Если же один крупный фактический платеж должен погасить несколько мелких плановых (или наоборот), этот подход не сработает.
Для полноценного распределения сумм по ФИФО используются более сложные техники, основанные на расчете нарастающих итогов для обеих таблиц и их последующем соединении по пересекающимся интервалам сумм. Это уже задача другого уровня сложности.