При разработке отчетов и механизмов проведения документов в 1С часто возникает задача: выбрать из набора данных только ту строку, у которой определенный реквизит или вычисляемое поле принимает максимальное значение. Например, требуется определить финальный этап согласования заявки, актуальный статус заказа или последнюю версию строки табличной части.
Рассмотрим несколько подходов к решению этой задачи на языке запросов 1С: от простого получения единственной строки до выборки максимумов в разрезе ключевых аналитик с помощью временных таблиц.
Если нам требуется получить ровно одну строку со всего результирующего набора данных, наиболее лаконичным и быстродействующим решением является ограничение количества строк через предложение ПЕРВЫЕ 1 в сочетании с сортировкой УПОРЯДОЧИТЬ ПО ... УБЫВ.
Разберем, как выглядит исходный запрос с вычислением числового веса статуса через конструкцию ВЫБОР КОГДА:
ВЫБРАТЬ ПЕРВЫЕ 1
ВЫБОР
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ТретийЭтапСогласования)
ТОГДА 4
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ВторойЭтапСогласования)
ТОГДА 3
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ПервыйЭтапСогласования)
ТОГДА 2
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.Формирование)
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК Порядок,
Таблица.Ссылка.СтатусПотребности КАК Статус,
Таблица.ПредыдущийЭтап.СтатусПотребности КАК ПредыдущийСтатус
ИЗ
мтоЭтапЗаявочнойКампанииФилиалы КАК Таблица
УПОРЯДОЧИТЬ ПО
Порядок УБЫВ
Обратим внимание на важную особенность платформы 1С: значения любого перечисления уже упорядочены на уровне базы данных в соответствии с тем, как они расположены в дереве метаданных конфигуратора. Если расположить элементы перечисления в конфигураторе в логической последовательности жизненного цикла, отпадает необходимость формировать громоздкую конструкцию ВЫБОР. Платформа отсортирует значения автоматически:
ВЫБРАТЬ ПЕРВЫЕ 1
Таблица.Ссылка.СтатусПотребности КАК Статус,
Таблица.ПредыдущийЭтап.СтатусПотребности КАК ПредыдущийСтатус
ИЗ
мтоЭтапЗаявочнойКампанииФилиалы КАК Таблица
УПОРЯДОЧИТЬ ПО
Таблица.Ссылка.СтатусПотребности УБЫВ
Ограничения подхода: конструкция ПЕРВЫЕ 1 возвращает только одну строку на всю выборку. Если необходимо найти строку с максимальным статусом по каждому филиалу или документу отдельно, такой способ не подойдет. Кроме того, если максимальный статус имеют сразу несколько строк, без дополнительного поля сортировки (например, даты или уникальной ссылки) результат выборки может оказаться недетерминированным.
Когда требуется смоделировать поведение функции «срез последних» (например, выбрать максимальный этап для каждого филиала), применим пакетный запрос с временной таблицей. В языке запросов 1С отсутствуют аналитические оконные функции, поэтому классическим решением является расчет агрегата МАКСИМУМ с последующим соединением с исходными данными.
Разберем по шагам реализацию такого алгоритма:
Посмотрим на пример пакетного запроса:
ВЫБРАТЬ
Таблица.Филиал КАК Филиал,
Таблица.Ссылка КАК ДокументЭтапа,
ВЫБОР
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ТретийЭтапСогласования)
ТОГДА 4
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ВторойЭтапСогласования)
ТОГДА 3
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.ПервыйЭтапСогласования)
ТОГДА 2
КОГДА Таблица.Ссылка.СтатусПотребности = ЗНАЧЕНИЕ(Перечисление.мтоСтатусыПотребности.Формирование)
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК Порядок,
Таблица.Ссылка.СтатусПотребности КАК Статус,
Таблица.ПредыдущийЭтап.СтатусПотребности КАК ПредыдущийСтатус
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ИЗ
мтоЭтапЗаявочнойКампанииФилиалы КАК Таблица
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ.Филиал КАК Филиал,
МАКСИМУМ(ВТ.Порядок) КАК МаксимальныйПорядок
ПОМЕСТИТЬ ВТ_Максимумы
ИЗ
ВТ_ИсходныеДанные КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Филиал
ИНДЕКСИРОВАТЬ ПО
Филиал,
МаксимальныйПорядок
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Исходные.Филиал КАК Филиал,
Исходные.ДокументЭтапа КАК ДокументЭтапа,
Исходные.Статус КАК Статус,
Исходные.ПредыдущийСтатус КАК ПредыдущийСтатус,
Исходные.Порядок КАК Порядок
ИЗ
ВТ_ИсходныеДанные КАК Исходные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Максимумы КАК Максимумы
ПО Исходные.Филиал = Максимумы.Филиал
И Исходные.Порядок = Максимумы.МаксимальныйПорядок
Важный нюанс: если в рамках одного филиала окажется несколько строк с одинаковым максимальным значением порядка, внутреннее соединение вернет их все, что приведет к дублированию записей. Чтобы исключить коллизии, во второй временной таблице можно добавить дополнительный критерий разрешения неоднозначности, например: МАКСИМУМ(ВТ.ДокументЭтапа).
Проанализируем альтернативный вариант, когда мы хотим обойтись без большого количества временных таблиц в пакете. Платформа 1С поддерживает конструкцию ГДЕ (Поле1, Поле2) В (...):
ВЫБРАТЬ
Исходные.Филиал КАК Филиал,
Исходные.Ссылка КАК ДокументЭтапа,
Исходные.Ссылка.СтатусПотребности КАК Статус,
Исходные.ПредыдущийЭтап.СтатусПотребности КАК ПредыдущийСтатус
ИЗ
мтоЭтапЗаявочнойКампанииФилиалы КАК Исходные
ГДЕ
(Исходные.Филиал, Исходные.Ссылка.СтатусПотребности) В
(ВЫБРАТЬ
Вложенный.Филиал,
МАКСИМУМ(Вложенный.Ссылка.СтатусПотребности)
ИЗ
мтоЭтапЗаявочнойКампанииФилиалы КАК Вложенный
СГРУППИРОВАТЬ ПО
Вложенный.Филиал)
Такой подход делает текст запроса более компактным, однако на больших массивах данных соединение через проиндексированную временную таблицу работает значительно быстрее и предсказуемее для оптимизатора СУБД.
ИНДЕКСИРОВАТЬ ПО для полей связи. Это предотвращает полное сканирование (Table Scan) временных таблиц в памяти СУБД.Таблица.Ссылка.СтатусПотребности или Таблица.ПредыдущийЭтап.СтатусПотребности превращаются платформой в неявные соединения LEFT JOIN. Вынесение таких полей в первую временную таблицу исключает повторное выполнение тяжелых соединений на последующих этапах пакета.