При работе с базой данных 1С разработчики регулярно сталкиваются с классической задачей: необходимо выбрать из табличной части документа ровно одну строку, которая удовлетворяет определенному условию максимума. Ситуация усложняется, когда критериев выбора несколько (например, сначала ищем максимальную дату, а при совпадении дат — максимальный номер строки), и при этом нам нужно получить не просто сами эти максимальные значения, но и сопутствующие реквизиты из этой же строки (например, текстовые примечания, номенклатуру, суммы или склады).
Если бы мы решали эту задачу для одного конкретного документа, то все было бы просто: достаточно использовать конструкцию ВЫБРАТЬ ПЕРВЫЕ 1, настроить связь и применить сортировку (УПОРЯДОЧИТЬ ПО) по убыванию. Но как быть, если нужно получить срез последних строк по всей таблице документов (для тысяч или десятков тысяч объектов) одним запросом, избегая выполнения запросов в цикле? В этой статье мы детально разберем несколько эффективных способов решения этой задачи: от классического пакетного запроса на временных таблицах до изящного соединения по неравенству.
Прежде чем перейти к решениям, давайте проанализируем, почему стандартная группировка СГРУППИРОВАТЬ ПО не решает задачу напрямую. Если мы просто выберем максимальную дату и максимальный номер строки, сгруппировав результат по ссылке на документ, а затем попытаемся вытащить другие реквизиты табличной части (например, Реквизит1 и Реквизит2), СУБД потребует включить эти реквизиты в секцию группировки. Как только мы это сделаем, группировка перестанет работать так, как нам нужно, — мы получим все уникальные комбинации реквизитов, а не одну строку.
Поэтому нам необходимы обходные пути. Давайте последовательно рассмотрим три лучших алгоритма решения этой проблемы в языке запросов 1С.
Использование пакета запросов с созданием временных таблиц — это самый надежный, предсказуемый и производительный способ в системе 1С:Предприятие. Он отлично оптимизируется СУБД (MS SQL, PostgreSQL) и легко читается другими разработчиками.
В оригинальном обсуждении на форуме предлагался вариант из 5 временных таблиц. Мы же разберем оптимизированный трехшаговый алгоритм, который решает задачу быстрее и требует меньше ресурсов сервера.
Шаг 1. Получаем максимальную дату для каждого документа. На первом этапе мы группируем табличную часть только по ссылке на документ и находим наибольшую дату.
Шаг 2. Находим максимальный номер строки для этой даты. Если у нас есть несколько строк с одинаковой максимальной датой, мы делаем соединение исходной табличной части с первой временной таблицей по условиям совпадения ссылки и даты. В результате группировки по ссылке мы находим максимальный номер строки (наш «разрешитель ничьих» или tie-breaker).
Шаг 3. Получаем все целевые реквизиты. Имея точную уникальную пару ключей (Ссылка и НомерСтроки), мы соединяем исходную таблицу со второй временной таблицей и без труда выбираем любые сопутствующие реквизиты строки.
Рассмотрим детальный пример реализации этого пакета запросов:
// Шаг 1: Определяем максимальную дату для каждого документа
ВЫБРАТЬ
ТЧ.Ссылка КАК Ссылка,
МАКСИМУМ(ТЧ.ДатаТЧ) КАК МаксДатаТЧ
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ
СГРУППИРОВАТЬ ПО
ТЧ.Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Для найденной максимальной даты определяем максимальный номер строки
ВЫБРАТЬ
ТЧ.Ссылка КАК Ссылка,
МАКСИМУМ(ТЧ.НомерСтроки) КАК МаксНомерСтроки
ПОМЕСТИТЬ ВТ_ЦелевыеСтроки
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
ПО ТЧ.Ссылка = ВТ_МаксимальныеДаты.Ссылка
И ТЧ.ДатаТЧ = ВТ_МаксимальныеДаты.МаксДатаТЧ
СГРУППИРОВАТЬ ПО
ТЧ.Ссылка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем целевые строки с исходной ТЧ для получения всех реквизитов
ВЫБРАТЬ
ТЧ.Ссылка КАК СсылкаНаДокумент,
ТЧ.Ссылка.КакойТоРеквизитДокумента1 КАК РеквизитДокумента1,
ТЧ.Ссылка.КакойТоРеквизитДокумента2 КАК РеквизитДокумента2,
ТЧ.ДатаТЧ КАК ДатаТЧ,
ТЧ.НомерСтроки КАК НомерСтроки,
ТЧ.Реквизит1 КАК Реквизит1,
ТЧ.Реквизит2 КАК Реквизит2
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ЦелевыеСтроки КАК ВТ_ЦелевыеСтроки
ПО ТЧ.Ссылка = ВТ_ЦелевыеСтроки.Ссылка
И ТЧ.НомерСтроки = ВТ_ЦелевыеСтроки.МаксНомерСтроки
Преимущества метода:
ВТ_МаксимальныеДаты и ВТ_ЦелевыеСтроки во время замера производительности или в консоли запросов.Если вам по какой-то причине крайне важно решить задачу в рамках одного-единственного запроса без создания временных таблиц (например, при написании динамического списка или при жестком ограничении среды разработки), можно использовать красивый математический прием с соединением таблицы самой с собой.
Идея заключается в следующем: мы виртуально сопоставляем каждую строку табличной части (назовем ее ТЧ_Основная) со всеми другими строками этой же табличной части (назовем ее ТЧ_Альтернативная). Соединение настраивается по правилу: "найти для этого же документа строку, у которой дата больше, либо дата равна нашей, но номер строки больше".
Для самой максимальной (актуальной) строки в документе такого сопоставления в альтернативной таблице попросту не найдется. Следовательно, при левом соединении для нее поля альтернативной таблицы заполнятся значением NULL. Нам останется лишь отфильтровать результат по этому признаку в секции ГДЕ.
Давайте посмотрим, как выглядит этот лаконичный запрос:
ВЫБРАТЬ
ТЧ_Основная.Ссылка КАК СсылкаНаДокумент,
ТЧ_Основная.Ссылка.КакойТоРеквизитДокумента1 КАК РеквизитДокумента1,
ТЧ_Основная.Ссылка.КакойТоРеквизитДокумента2 КАК РеквизитДокумента2,
ТЧ_Основная.ДатаТЧ КАК ДатаТЧ,
ТЧ_Основная.НомерСтроки КАК НомерСтроки,
ТЧ_Основная.Реквизит1 КАК Реквизит1,
ТЧ_Основная.Реквизит2 КАК Реквизит2
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ_Основная
ЛЕВОЕ СОЕДИНЕНИЕ Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ_Альтернативная
ПО ТЧ_Основная.Ссылка = ТЧ_Альтернативная.Ссылка
И (ТЧ_Основная.ДатаТЧ < ТЧ_Альтернативная.ДатаТЧ
ИЛИ (ТЧ_Основная.ДатаТЧ = ТЧ_Альтернативная.ДатаТЧ
И ТЧ_Основная.НомерСтроки < ТЧ_Альтернативная.НомерСтроки))
ГДЕ
ТЧ_Альтернативная.Ссылка ЕСТЬ NULL
Преимущества метода:
Важное предупреждение: Будьте осторожны при использовании этого метода на таблицах, содержащих сотни тысяч или миллионы строк. Конструкция соединения с условием неравенства (оператор <) в сочетании с логическим оператором ИЛИ может заставить оптимизатор СУБД отказаться от использования индексов и перейти к полному сканированию таблиц (Table Scan), что приведет к сильному замедлению работы базы данных.
Этот метод идейно близок к первому варианту с временными таблицами, но собирается в рамках одного дерева запроса через вложенные подзапросы в секциях соединения. Именно к этому варианту в итоге пришел автор ветки форума в Сообщении 15.
Давайте подробно разберем структуру этого вложенного монстра, чтобы понять логику его работы. Мы последовательно строим вложенную иерархию поиска максимумов прямо в условиях ЛЕВОЕ СОЕДИНЕНИЕ:
ВЫБРАТЬ
ТЧ_Док.Ссылка КАК Ссылка,
ТЧ_Док.Реквизит1 КАК Реквизит1,
ТЧ_Док.Реквизит2 КАК Реквизит2,
ТЧ_Док.Ссылка.КакойТоРеквизитДокумента1 КАК РеквизитДокумента1,
ТЧ_Док.Ссылка.КакойТоРеквизитДокумента2 КАК РеквизитДокумента2,
ВложенныйЗапросДатаНомер.ДатаТЧ КАК ДатаТЧ,
ВложенныйЗапросДатаНомер.НомерСтроки КАК НомерСтроки
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ_Док
// Внутреннее соединение гарантирует, что мы оставим в выборке только нужные строки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (
// Первый уровень вложенности: получаем максимальную дату для каждой ссылки
ВЫБРАТЬ
ТЧ_МаксДаты.Ссылка КАК Ссылка,
МАКСИМУМ(ТЧ_МаксДаты.ДатаТЧ) КАК ДатаТЧ,
ВложенныйЗапросНомер.НомерСтроки КАК НомерСтроки
ИЗ
Документ.НекийДокумент.ТабличнаяЧасть КАК ТЧ_МаксДаты
// Второй уровень вложенности: получаем максимальный номер строки для каждой даты
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ
ТЧ_МаксНомера.Ссылка КАК Ссылка,
МАКСИМУМ(ТЧ_МаксНомера.НомерСтроки) КАК НомерСтроки,
ТЧ_МаксНомера.ДатаТЧ КАК ДатаТЧ
ИЗ
Документ.НекийДокумент.TabличнаяЧасть КАК ТЧ_МаксНомера
СГРУППИРОВАТЬ ПО
ТЧ_МаксНомера.Ссылка,
ТЧ_МаксНомера.ДатаТЧ
) КАК ВложенныйЗапросНомер
ПО ТЧ_МаксДаты.Ссылка = ВложенныйЗапросНомер.Ссылка
И ТЧ_МаксДаты.ДатаТЧ = ВложенныйЗапросНомер.ДатаТЧ
СГРУППИРОВАТЬ ПО
ТЧ_МаксДаты.Ссылка,
ВложенныйЗапросНомер.НомерСтроки
) КАК ВложенныйЗапросДатаНомер
ПО ТЧ_Док.Ссылка = ВложенныйЗапросДатаНомер.Ссылка
И ТЧ_Док.НомерСтроки = ВложенныйЗапросДатаНомер.НомерСтроки
Этот вариант абсолютно рабочий, однако с точки зрения стандартов разработки фирмы "1С" он считается менее предпочтительным по сравнению с использованием временных таблиц (Решение 1). Чтение и отладка вложенных подзапросов большой глубины крайне затруднительны, а СУБД при построении планов запросов может ошибиться с выбором оптимального индекса из-за отсутствия явных статистик по промежуточным наборам данных.
Если ваша конечная цель — вывод этих данных в отчет или форму с использованием Системы компоновки данных (СКД), вам совсем не обязательно усложнять текст SQL-запроса всеми описанными выше способами. Вы можете переложить задачу фильтрации на мощный аналитический процессор СКД.
Рассмотрим, как это сделать по шагам:
Ссылка, ДатаТЧ, НомерСтроки, Реквизит1 и другие поля.Ссылка (чтобы сгруппировать строки по документам).Детальные записи.ДатаТЧ Убыв и НомерСтроки Убыв.1.В результате СКД сама применит логику выборки первой строки (аналог оконной функции ROW_NUMBER()) для каждого документа, отсортировав строки внутри группировки по вашим правилам и выведя ровно одну запись.
Для того чтобы вам было проще принять решение в реальной практике, мы подготовили краткую сводную рекомендацию: