В разработке на платформе 1С часто возникает задача: для определенного объекта или идентификатора (например, физического лица или ИНН) выбрать единственную актуальную запись, соответствующую максимальной дате. При этом по одному и тому же значению идентификатора в исходной таблице может быть привязано несколько разных элементов (например, несколько элементов справочника Сотрудники на один ИНН физического лица).
Попытка решить задачу «в лоб» через простую группировку приводит к распространенной ошибке. Если мы пишем конструкцию СГРУППИРОВАТЬ ПО ВТ.Сотрудник, ВТ.ИНН и вычисляем МАКСИМУМ(ВТ.Дата), СУБД группирует записи по каждой уникальной паре «Сотрудник + ИНН». В результате мы получаем всех сотрудников со всеми их максимальными датами, а не одну итоговую строку на каждый ИНН.
Разберем подробнее, почему так происходит, как правильно составить классический запрос через временные таблицы, как избежать критических ошибок при соединении и какие современные альтернативы существуют в платформе 1С.
Рассмотрим классический и наиболее универсальный подход, работающий во всех версиях платформы 1С:Предприятие 8. Сначала мы вычисляем максимальные даты для каждого уникального ИНН в отдельной временной таблице, а затем соединяем полученный результат с исходными данными.
Проанализируем критически важную деталь, на которой часто ошибаются разработчики: при обратном соединении обязательно нужно связывать таблицы одновременно по двум полям — и по ИНН, и по Дата. Если соединить таблицы только по полю ИНН, произойдет декартово произведение, и запрос вернет все строки исходной таблицы. Также важно не забывать индексировать поля соединения во временных таблицах для оптимальной производительности.
Посмотрим на пример правильного запроса с предварительным типизированием исходных данных из таблицы значений и устранением коллизий:
ВЫБРАТЬ
ВЫРАЗИТЬ(ТЗ.Сотрудник КАК Справочник.Сотрудники) КАК Сотрудник,
ВЫРАЗИТЬ(ТЗ.ИНН КАК СТРОКА(12)) КАК ИНН,
ВЫРАЗИТЬ(ТЗ.Дата КАК ДАТА) КАК Дата
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
&ТЗ КАК ТЗ
ИНДЕКСИРОВАТЬ ПО
ИНН,
Дата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.ИНН КАК ИНН,
МАКСИМУМ(ВТ.Дата) КАК МаксДата
ПОМЕСТИТЬ ВТ_Максимумы
ИЗ
ВТ_ИсходныеДанные КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.ИНН
ИНДЕКСИРОВАТЬ ПО
ИНН,
МаксДата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Максимумы.ИНН КАК ИНН,
ВТ_Максимумы.МаксДата КАК Дата,
МАКСИМУМ(ВТ.Сотрудник) КАК Сотрудник
ИЗ
ВТ_Максимумы КАК ВТ_Максимумы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК ВТ
ПО ВТ_Максимумы.ИНН = ВТ.ИНН
И ВТ_Максимумы.МаксДата = ВТ.Дата
СГРУППИРОВАТЬ ПО
ВТ_Максимумы.ИНН,
ВТ_Максимумы.МаксДата
Обратите внимание на вызов агрегатной функции МАКСИМУМ(ВТ.Сотрудник) в результирующем пакете. Если на одну и ту же максимальную дату у одного ИНН будет зафиксировано два разных сотрудника, обычное соединение вернет две строки. Агрегация по полю Сотрудник гарантирует строгое соблюдение правила «один ИНН — ровно одна строка в результате».
Разберем еще один способ, в котором вместо явного соединения таблиц используется проверка вхождения кортежа полей в подзапрос или временную таблицу:
ВЫБРАТЬ
ВТ.ИНН КАК ИНН,
ВТ.Дата КАК Дата,
МАКСИМУМ(ВТ.Сотрудник) КАК Сотрудник
ИЗ
ВТ_ИсходныеДанные КАК ВТ
ГДЕ
(ВТ.ИНН, ВТ.Дата) В
(ВЫБРАТЬ
ВТ_Максимумы.ИНН,
ВТ_Максимумы.МаксДата
ИЗ
ВТ_Максимумы КАК ВТ_Максимумы)
СГРУППИРОВАТЬ ПО
ВТ.ИНН,
ВТ.Дата
Этот вариант выглядит компактным, однако у него есть нюансы. Конструкция ГДЕ (Поле1, Поле2) В (...) на некоторых СУБД (например, в определенных версиях PostgreSQL) может оптимизироваться хуже, чем явное ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Поэтому для больших объемов данных предпочтительнее первый способ с явной индексацией полей связи.
Начиная с версии платформы 8.3.27, в языке запросов 1С появилась поддержка аналитических оконных функций. Это позволяет решить задачу в один проход без промежуточных группировок и соединений таблиц.
Посмотрим, как выглядит решение через функцию ROW_NUMBER():
ВЫБРАТЬ
ВТ.ИНН КАК ИНН,
ВТ.Сотрудник КАК Сотрудник,
ВТ.Дата КАК Дата,
ROW_NUMBER() OVER (
PARTITION BY ВТ.ИНН
УПОРЯДОЧИТЬ ПО ВТ.Дата УБЫВ, ВТ.Сотрудник УБЫВ
) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_СНумерацией
ИЗ
ВТ_ИсходныеДанные КАК ВТ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_СНумерацией.ИНН КАК ИНН,
ВТ_СНумерацией.Сотрудник КАК Сотрудник,
ВТ_СНумерацией.Дата КАК Дата
ИЗ
ВТ_СНумерацией КАК ВТ_СНумерацией
ГДЕ
ВТ_СНумерацией.НомерСтроки = 1
Этот метод не только короче и нагляднее, но и элегантно разрешает проблему одинаковых дат: благодаря сортировке ВТ.Сотрудник УБЫВ внутри секции упорядочивания строки никогда не получат одинаковый номер, и в выборку попадет строго одна запись на каждый ИНН.
Рассмотрим классический реляционный прием без использования агрегатных функций. Мы соединяем исходную таблицу саму с собой левым соединением по условию: «в правой таблице ищем запись с тем же ИНН, но с большей датой». Если более поздней записи не существует, в правой таблице появится значение NULL.
ВЫБРАТЬ
Т1.ИНН КАК ИНН,
Т1.Сотрудник КАК Сотрудник,
Т1.Дата КАК Дата
ИЗ
ВТ_ИсходныеДанные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК Т2
ПО Т1.ИНН = Т2.ИНН
И (Т1.Дата < Т2.Дата
ИЛИ (Т1.Дата = Т2.Дата И Т1.Сотрудник < Т2.Сотрудник))
ГДЕ
Т2.ИНН ЕСТЬ NULL
Условие Т2.ИНН ЕСТЬ NULL фильтрует строки, оставляя только запись с максимальной датой. Дополнительное условие (Т1.Дата = Т2.Дата И Т1.Сотрудник < Т2.Сотрудник) предотвращает задвоение строк при точном совпадении дат.
Если исходная информация уже получена в виде объекта ТаблицаЗначений в коде модуля, задачу можно решить без обращения к СУБД:
Сортировать("ИНН Возр, Дата Убыв, Сотрудник Убыв").ИНН, запоминая текущий ИНН в переменной.Метод Свернуть() здесь не подойдет, поскольку он требует указания правил агрегации (сумма), что неприменимо к полям ссылочного типа Сотрудник.