В практике разработки на платформе 1С:Предприятие программисты регулярно сталкиваются с классической задачей: выбрать первые или последние N записей для каждой группы. В нашем случае речь идет о получении трех последних документов поступления по каждому товару из определенного списка номенклатуры.
Сложность заключается в том, что в языке запросов 1С исторически отсутствуют стандартные оконные функции СУБД (такие как ROW_NUMBER() OVER (PARTITION BY...)), которые позволяют решить эту задачу одной строкой. Тем не менее существует несколько эффективных способов реализации этого механизма. Давайте подробно разберем каждый из них, проанализируем возможные проблемы с производительностью и научимся их оптимизировать.
Если вам необходимо решить эту задачу строго в рамках одного запроса (например, для последующей обработки результата в коде), стандартным и самым надежным решением является эмуляция оконных функций СУБД с помощью левого соединения таблицы самой с собой по условию сравнения дат.
Давайте разберем по шагам рабочий алгоритм, представленный в обсуждении на форуме:
Т1.Дата <= Т2.Дата. Сгруппировав результат по первой таблице и наложив условие в секции ИМЕЮЩИЕ на количество строк из второй таблицы (не более 3), мы получаем только три самые свежие даты поступлений.Посмотрим на пример практической реализации данного алгоритма:
// Шаг 1: Помещаем целевую номенклатуру во временную таблицу
ВЫБРАТЬ
Номенклатура.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВыбраннаяНоменклатура
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
Номенклатура.Ссылка В(&МассивНоменклатуры);
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Получаем все проведенные документы поступления по этой номенклатуре
ВЫБРАТЬ
ВыбраннаяНоменклатура.Ссылка КАК Номенклатура,
ПоступлениеТовары.Ссылка КАК Ссылка,
ПоступлениеТовары.Ссылка.Дата КАК Дата
ПОМЕСТИТЬ ПоступленияНоменклатуры
ИЗ
ВыбраннаяНоменклатура КАК ВыбраннаяНоменклатура
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
ПО ВыбраннаяНоменклатура.Ссылка = ПоступлениеТовары.Номенклатура
И ПоступлениеТовары.Ссылка.Проведен;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Вычисляем временную границу (дату), отсекающую только 3 последних документа
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура,
МИНИМУМ(ВложенныйЗапрос.Дата) КАК Дата
ПОМЕСТИТЬ МинимальныеДатыПоступлений
ИЗ
(ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Дата КАК Дата
ИЗ
ПоступленияНоменклатуры КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ПоступленияНоменклатуры КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.Дата <= Т2.Дата
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.Дата
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Ссылка) <= 3) КАК ВложенныйЗапрос
СГРУППИРОВАТЬ ПО
ВложенныйЗапрос.Номенклатура;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Выгружаем финальный результат
ВЫБРАТЬ
МинимальныеДатыПоступлений.Номенклатура КАК Номенклатура,
ПоступленияНоменклатуры.Ссылка КАК Ссылка
ИЗ
МинимальныеДатыПоступлений КАК МинимальныеДатыПоступлений
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПоступленияНоменклатуры КАК ПоступленияНоменклатуры
ПО МинимальныеДатыПоступлений.Номенклатура = ПоступленияНоменклатуры.Номенклатура
И МинимальныеДатыПоступлений.Дата <= ПоступленияНоменклатуры.Дата;
Важное предупреждение по производительности: Данный алгоритм имеет квадратичную вычислительную сложность O(n²) по количеству документов на один элемент номенклатуры. Если по какому-то популярному товару в базе зарегистрировано 10 000 документов поступления, СУБД будет вынуждена произвести до 100 000 000 сопоставлений строк, что гарантированно приведет к зависанию запроса.
Чтобы избежать перегрузки сервера, обязательно применяйте оптимизацию:
ПоступленияНоменклатуры обязательно должен быть построен индекс по полям Номенклатура и Дата.Если итоговой целью вашей задачи является вывод данных в отчет для пользователей, то писать сложные и тяжелые запросы с самосоединениями совершенно не нужно. Платформа 1С предоставляет мощные инструменты группировки в СКД, которые берут на себя всю рутину по ограничению количества выводимых строк.
Рассмотрим два варианта настройки отчета в СКД:
В самом запросе схемы компоновки данных вы просто выбираете всю историю поступлений по товарам и упорядочиваете их по дате убывания. В структуре настроек СКД вы создаете группировку по Номенклатура, а внутри нее — детальные записи (документы поступления). Для детальных записей на вкладке "Отбор" задайте условие: СистемныеПоля.НомерВГруппе <= 3. СКД самостоятельно отсечет лишние строки для каждого товара.
Для более гибкого управления нумерацией в вычисляемых полях СКД можно применить выражение:
ВычислитьВыражение("НомерПоПорядкуВГруппировке()", "Номенклатура", , , , "Период Убыв")
Это позволит вам программно вычислять порядковый номер каждой строки поступления в разрезе товара и выводить только нужные строки.
Начиная с версии платформы 8.3.13, в языке запросов 1С появилась функция АВТОНОМЕРЗАПИСИ(). С ее помощью можно автоматически пронумеровать строки выборки. Кажется, что это идеальное решение нашей проблемы, но здесь разработчиков подстерегает серьезная опасность.
Давайте проанализируем нюансы работы этой функции:
АВТОНОМЕРЗАПИСИ() работает исключительно при создании временных таблиц. Вы не можете использовать ее в обычном конечном запросе.Поэтому конструкция вида ГДЕ ВременныйНомер <= 3 напрямую работать не будет и со временем начнет выдавать пустой результат. Чтобы обойти это поведение, программисту приходится вычислять минимальное значение счетчика для каждой группировки и делать дополнительное вычитание, что усложняет логику и нивелирует преимущества использования этой функции.
Еще один продвинутый способ, который поддерживается платформой 1С — использование конструкции с оператором В для сравнения нескольких колонок с коррелированным подзапросом.
Давайте посмотрим на пример реализации:
ВЫБРАТЬ
Поступления.Номенклатура КАК Номенклатура,
Поступления.Ссылка КАК Ссылка
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Поступления
ГДЕ
(Поступления.Номенклатура, Поступления.Ссылка) В
(
ВЫБРАТЬ ПЕРВЫЕ 3
Вложенный.Номенклатура,
Вложенный.Ссылка
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Вложенный
ГДЕ
Вложенный.Номенклатура = Поступления.Номенклатура
УПОРЯДОЧИТЬ ПО
Вложенный.Ссылка.Дата УБЫВ
)
Особенности метода: такой запрос выглядит лаконично, но при выполнении СУБД транслирует его в зависимый подзапрос (оператор EXISTS). На больших базах данных производительность этого решения сильно зависит от оптимизатора вашей СУБД (MS SQL, PostgreSQL и др.). Перед внедрением этого способа в промышленную эксплуатацию обязательно оцените план запроса и протестируйте скорость его выполнения на реальном объеме данных.
Каждое из рассмотренных решений имеет свои сценарии применения:
АВТОНОМЕРЗАПИСИ() из-за нестабильности стартового значения счетчика при повторных вызовах запросов в СУБД.