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