Как выбрать разное количество последних документов по разным пользователям с помощью запроса 1С?

При работе с данными в 1С:Предприятии часто возникает необходимость выбирать информацию по группам с определенными условиями. Одной из таких задач является выбор различного количества документов (например, последних реализаций) для каждого пользователя из определенного набора. Представьте ситуацию: у вас есть список пользователей и для каждого из них задано, сколько последних документов реализации необходимо получить. Например, для Иванова – 5, для Петрова – 8, для Сидорова – 3 и так далее. Как же реализовать такой запрос в 1С?

Давайте разберем эту задачу подробно, рассматривая различные подходы к ее решению, их преимущества и недостатки, а также ключевые моменты, на которые стоит обратить внимание при написании таких запросов.

Постановка задачи

Исходные данные представлены во временной таблице, содержащей два ключевых реквизита: Пользователь и Количество. Наша цель – на основе этой таблицы получить выборку документов реализации, при этом для каждого пользователя из временной таблицы должно быть отобрано строго заданное количество последних реализаций.

Для ясности, представим структуру временной таблицы с требованием по количеству:

// Временная таблица с требуемым количеством документов по пользователям
ПОМЕСТИТЬ ВТ_ТребованияПоПользователям
ИЗ (ВЫБРАТЬ
    &Пользователь1 КАК Пользователь,
    &Количество1 КАК Количество
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    &Пользователь2,
    &Количество2
// ... и так далее для всех пользователей
) КАК ДанныеТребований;

В этой таблице для каждого пользователя будет указано, сколько документов нам нужно получить.

Общие принципы решения

Прежде чем перейти к конкретным примерам запросов, давайте обозначим общие шаги, которые нам предстоит выполнить. Эти шаги составляют основу логики для решения подобных задач и помогут нам структурировать процесс:

1. Определяем сортировку документов: Как мы будем определять "последние" документы? Чаще всего это делается по дате документа в убывающем порядке. Важно, чтобы поле сортировки было проиндексировано для повышения производительности.
2. Максимально ограничиваем первичную выборку: Для оптимизации запроса крайне желательно сразу же максимально сократить объем исходных данных. Это можно сделать, наложив отборы по дате (например, за последний год) или другим сильно проиндексированным реквизитам. Чем меньше данных мы будем обрабатывать на следующих этапах, тем быстрее будет работать запрос.
3. Нумеруем документы по группам: Это ключевой этап. Для каждого пользователя нам необходимо присвоить порядковый номер его документам, отсортированным по выбранному критерию (например, от 1 до N по дате убывания). Именно этот номер позволит нам отобрать нужное количество документов.
4. Соединяем пронумерованную выборку с таблицей требований: После получения пронумерованных документов мы соединяем их с нашей временной таблицей ВТ_ТребованияПоПользователям по полю Пользователь.
5. Отбираем нужное количество: На последнем этапе мы фильтруем результат соединения по условию НомерПоПорядку <= Количество, где Количество берется из таблицы требований.

Теперь давайте рассмотрим конкретные методы для реализации пункта 3 – нумерации документов по группам.

Метод 1: Нумерация через самосоединение (Нарастающий итог запросом)

Этот метод является классическим способом получения порядкового номера или "нарастающего итога" в запросах 1С до появления более современных функций. Он основан на соединении таблицы документов самой с собой.

Принцип работы

Мы соединяем таблицу Документ.РеализацияТоваровУслуг (назовем ее РеализацияТоваровУслуг) с ее же копией (назовем ее РеализацияТоваровУслуг1). Условие соединения будет следующим:

1. Документы принадлежат одному и тому же пользователю (РеализацияТоваровУслуг.Ответственный = РеализацияТоваровУслуг1.Ответственный).
2. Дата документа из первой таблицы больше или равна дате документа из второй таблицы (РеализацияТоваровУслуг.Дата >= РеализацияТоваровУслуг1.Дата).
3. Для уникальности можно также добавить условие по ссылке, если даты могут быть одинаковыми, чтобы гарантировать стабильный порядок: (РеализацияТоваровУслуг.Дата > РеализацияТоваровУслуг1.Дата ИЛИ РеализацияТоваровУслуг.Дата = РеализацияТоваровУслуг1.Дата И РеализацияТоваровУслуг.Ссылка >= РеализацияТоваровУслуг1.Ссылка)

В результате такого соединения, для каждого документа из РеализацияТоваровУслуг, подсчет количества связанных строк из РеализацияТоваровУслуг1 (с использованием СУММА(1)) даст нам его порядковый номер в группе по данному пользователю, отсортированный по дате.

Пример запроса с самосоединением

ВЫБРАТЬ
    РеализацияТоваровУслуг.Ссылка КАК Ссылка,
    РеализацияТоваровУслуг.Ответственный КАК Ответственный,
    РеализацияТоваровУслуг.Дата КАК ДатаДокумента,
    СУММА(1) КАК НомерПоПорядку
ПОМЕСТИТЬ ВТ_ПронумерованныеРеализации
ИЗ
    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг1
        ПО РеализацияТоваровУслуг.Ответственный = РеализацияТоваровУслуг1.Ответственный
        И (РеализацияТоваровУслуг.Дата > РеализацияТоваровУслуг1.Дата
            ИЛИ (РеализацияТоваровУслуг.Дата = РеализацияТоваровУслуг1.Дата
                 И РеализацияТоваровУслуг.Ссылка >= РеализацияТоваровУслуг1.Ссылка))
ГДЕ
    РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПериода И &КонецПериода // Ограничиваем выборку
СГРУППИРОВАТЬ ПО
    РеализацияТоваровУслуг.Ссылка,
    РеализацияТоваровУслуг.Ответственный,
    РеализацияТоваровУслуг.Дата
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    Пронумерованные.Ссылка,
    Пронумерованные.Ответственный,
    Пронумерованные.ДатаДокумента,
    Пронумерованные.НомерПоПорядку
ИЗ
    ВТ_ПронумерованныеРеализации КАК Пронумерованные
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ТребованияПоПользователям КАК Требования
        ПО Пронумерованные.Ответственный = Требования.Пользователь
ГДЕ
    Пронумерованные.НомерПоПорядку <= Требования.Количество
УПОРЯДОЧИТЬ ПО
    Пронумерованные.Ответственный,
    Пронумерованные.ДатаДокумента УБЫВ

Обратите внимание на использование РеализацияТоваровУслуг.Дата > РеализацияТоваровУслуг1.Дата ИЛИ (...). Это позволяет получить нумерацию, где документы с одинаковой датой будут иметь стабильный порядок, определяемый ссылкой. Если вам нужен просто порядковый номер без учета уникальности при совпадении даты, можно использовать только РеализацияТоваровУслуг.Дата >= РеализацияТоваровУслуг1.Дата, но будьте осторожны, так как порядок может быть не детерминирован.

Недостатки метода

Основным и наиболее существенным недостатком метода самосоединения является его производительность. Для большого объема данных сложность алгоритма становится квадратичной (O(N^2)), где N – количество документов в исходной выборке. Это означает, что при росте числа документов время выполнения запроса будет расти значительно быстрее, что может привести к очень медленной работе, высокому потреблению оперативной памяти и даже к исчерпанию ресурсов сервера баз данных. Об этом упоминалось в обсуждении на форуме.

Именно поэтому крайне важно максимально ограничить первичную выборку документов (например, по периоду) и убедиться, что используемые для соединения и сортировки поля (Ответственный, Дата, Ссылка) проиндексированы.

Метод 2: Нумерация с использованием функции АвтоНомерЗаписи()

Функция АвтоНомерЗаписи() (AutoRecordNumber()) – это более современный и часто более производительный способ присвоения порядковых номеров записям в выборке. Она присваивает последовательный номер каждой записи, начиная с 1.

Принцип работы

Ключевая особенность АвтоНомерЗаписи() заключается в том, что она работает с записями в том порядке, в котором они были выбраны или упорядочены в предыдущих шагах запроса. Это значит, что для получения правильной нумерации по группам (пользователям) с определенной сортировкой (например, по дате убывания), нам сначала нужно подготовить данные в нужной последовательности, а затем применить эту функцию.

Типичный подход с АвтоНомерЗаписи() выглядит так:

1. Предварительная выборка и упорядочивание: Сначала мы выбираем все необходимые документы и помещаем их во временную таблицу, обязательно упорядочив их по полю группировки (Ответственный) и полю сортировки (Дата УБЫВ). Это гарантирует, что АвтоНомерЗаписи() будет нумеровать строки в требуемом порядке.
2. Присвоение номеров: Затем мы делаем новую выборку из этой упорядоченной временной таблицы, используя АвтоНомерЗаписи(). Важно понимать, что АвтоНомерЗаписи() нумерует строки сквозным образом для всей выборки, а не сбрасывает счетчик для каждой группы. Если нам нужен сброс счетчика для каждой группы, потребуются дополнительные шаги или другой метод (оконные функции, см. ниже). Однако, для задачи "выбрать N последних документов по каждой группе" нам нужен порядковый номер в рамках группы.

На форуме обсуждалось, как АвтоНомерЗаписи() ведет себя с УПОРЯДОЧИТЬ ПО. Эксперименты показывают, что АвтоНомерЗаписи() действительно следует порядку, заданному оператором УПОРЯДОЧИТЬ ПО для временной таблицы, если она создается с этим условием или если последующая выборка из нее идет с упорядочиванием.

Пример запроса с АвтоНомерЗаписи()

Для решения нашей задачи с помощью АвтоНомерЗаписи(), нам придется немного "извернуться", чтобы сбросить счетчик для каждого пользователя. Это можно сделать, комбинируя подходы. Однако, более прямым применением АвтоНомерЗаписи() без сброса счетчика было бы получение глобальной нумерации, что не совсем подходит для "N последних по каждой группе".

Если мы хотим использовать АвтоНомерЗаписи() для получения номера внутри группы (как в "нарастающем итоге"), то прямой способ, требующий сброса счетчика для каждой группы, отсутствует в 1С без использования оконных функций. В таком случае АвтоНомерЗаписи() удобна для глобальной нумерации, но не для нумерации "по пользователю с обнулением".

Тем не менее, если бы задача была просто "отобрать первые N документов *в целом*", или если бы мы могли использовать другие приемы (например, несколько подзапросов), АвтоНомерЗаписи() могла бы быть полезна. Однако, для текущей задачи "N по каждой группе", она менее применима напрямую без использования оконных функций или сложной обвязки. Давайте рассмотрим более эффективный метод для такой задачи.

Метод 3: Нумерация с использованием оконных функций (Рекомендуемый современный подход)

В современных версиях платформы 1С:Предприятие (начиная с 8.3.17) появились аналитические, или оконные, функции. Эти функции являются наиболее производительным и элегантным способом решения задач типа "Топ N по группе" или "нарастающий итог", так как они оптимизированы для работы с группами и позволяют избежать O(N^2) сложности самосоединений.

Принцип работы

Оконные функции позволяют выполнять вычисления над набором строк (окном), связанных с текущей строкой, без необходимости явного самосоединения. Для нашей задачи идеально подходит функция НомерСтроки() (ROW_NUMBER), которая присваивает последовательный номер строкам в каждой группе. При этом мы можем указать:

1. Разбиение по группам (РАЗБИТЬ ПО / PARTITION BY): Указываем поле, по которому нужно сбрасывать счетчик нумерации (в нашем случае – Ответственный).
2. Сортировка внутри группы (УПОРЯДОЧИТЬ ПО / ORDER BY): Указываем поля, по которым нужно сортировать документы внутри каждой группы для присвоения номеров (в нашем случае – Дата УБЫВ).

Пример запроса с оконной функцией НомерСтроки()

// Шаг 1: Выбираем документы и присваиваем порядковые номера с помощью оконной функции
ВЫБРАТЬ
    РеализацияТоваровУслуг.Ссылка КАК Ссылка,
    РеализацияТоваровУслуг.Ответственный КАК Ответственный,
    РеализацияТоваровУслуг.Дата КАК ДатаДокумента,
    НОМЕРСТРОКИ(РАЗБИТЬ ПО РеализацияТоваровУслуг.Ответственный УПОРЯДОЧИТЬ ПО РеализацияТоваровУслуг.Дата УБЫВ, РеализацияТоваровУслуг.Ссылка УБЫВ) КАК НомерПоПорядку
ПОМЕСТИТЬ ВТ_ПронумерованныеРеализации
ИЗ
    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
    РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПериода И &КонецПериода // Ограничиваем выборку
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Соединяем пронумерованные документы с таблицей требований и отбираем нужное количество
ВЫБРАТЬ
    Пронумерованные.Ссылка,
    Пронумерованные.Ответственный,
    Пронумерованные.ДатаДокумента,
    Пронумерованные.НомерПоПорядку
ИЗ
    ВТ_ПронумерованныеРеализации КАК Пронумерованные
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ТребованияПоПользователям КАК Требования
        ПО Пронумерованные.Ответственный = Требования.Пользователь
ГДЕ
    Пронумерованные.НомерПоПорядку <= Требования.Количество
УПОРЯДОЧИТЬ ПО
    Пронумерованные.Ответственный,
    Пронумерованные.ДатаДокумента УБЫВ

В этом примере РАЗБИТЬ ПО РеализацияТоваровУслуг.Ответственный говорит платформе, что нумерация должна сбрасываться для каждого нового значения поля Ответственный. А УПОРЯДОЧИТЬ ПО РеализацияТоваровУслуг.Дата УБЫВ, РеализацияТоваровУслуг.Ссылка УБЫВ определяет порядок присвоения номеров внутри каждой такой группы.

Преимущества метода

Оконные функции являются наиболее предпочтительным методом для решения задач типа "Топ N по группе" по следующим причинам:

1. Высокая производительность: Они специально оптимизированы на уровне СУБД для эффективной обработки данных по группам, что позволяет избежать проблем с O(N^2) сложностью.
2. Элегантность и читаемость кода: Запрос становится значительно проще и понятнее, так как логика нумерации по группам выражается более явно и лаконично.
3. Стандарт SQL: Это стандартный подход в SQL-базах данных, что упрощает понимание для разработчиков, знакомых с SQL.

Требования

Для использования оконных функций необходима платформа 1С:Предприятие версии 8.3.17 или выше.

Окончательная сборка запроса (с использованием оконных функций)

Давайте соберем весь запрос целиком, используя самый современный и эффективный подход с оконными функциями. Для этого нам понадобится исходная временная таблица с требованиями по пользователям и, собственно, документы реализации.

1. Создаем временную таблицу с требованиями: Это наша входная информация.
   Скопировать код

   
   // Временная таблица с требуемым количеством документов по пользователям
   ПОМЕСТИТЬ ВТ_ТребованияПоПользователям
   ИЗ (ВЫБРАТЬ
       &ПользовательИванов КАК Пользователь,
       5 КАК Количество
   ОБЪЕДИНИТЬ ВСЕ
   ВЫБРАТЬ
       &ПользовательПетров,
       8
   ОБЪЕДИНИТЬ ВСЕ
   ВЫБРАТЬ
       &ПользовательСидоров,
       3
   ) КАК ДанныеТребований
   ;
   

2. Нумеруем документы реализации по пользователям: Используем оконную функцию НОМЕРСТРОКИ(). Важно применить условие по периоду (ГДЕ РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПериода И &КонецПериода), чтобы ограничить объем обрабатываемых данных.
   Скопировать код

   
   // Временная таблица с пронумерованными реализациями по каждому ответственному
   ПОМЕСТИТЬ ВТ_ПронумерованныеРеализации
   ИЗ
       (ВЫБРАТЬ
           РеализацияТоваровУслуг.Ссылка КАК Ссылка,
           РеализацияТоваровУслуг.Ответственный КАК Ответственный,
           РеализацияТоваровУслуг.Дата КАК ДатаДокумента,
           НОМЕРСТРОКИ(РАЗБИТЬ ПО РеализацияТоваровУслуг.Ответственный УПОРЯДОЧИТЬ ПО РеализацияТоваровУслуг.Дата УБЫВ, РеализацияТоваровУслуг.Ссылка УБЫВ) КАК НомерПоПорядку
       ИЗ
           Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
       ГДЕ
           РеализацияТоваровУслуг.Дата МЕЖДУ &НачалоПериода И &КонецПериода
       ) КАК ВложенныйЗапрос
   ;
   

3. Соединяем и отбираем: Теперь мы соединяем пронумерованные документы с нашей таблицей требований и фильтруем по условию НомерПоПорядку <= Количество.
   Скопировать код

   
   // Итоговая выборка документов по заданным требованиям
   ВЫБРАТЬ
       Пронумерованные.Ссылка,
       Пронумерованные.Ответственный,
       Пронумерованные.ДатаДокумента,
       Пронумерованные.НомерПоПорядку,
       Требования.Количество
   ИЗ
       ВТ_ПронумерованныеРеализации КАК Пронумерованные
           ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ТребованияПоПользователям КАК Требования
           ПО Пронумерованные.Ответственный = Требования.Пользователь
   ГДЕ
       Пронумерованные.НомерПоПорядку <= Требования.Количество
   УПОРЯДОЧИТЬ ПО
       Пронумерованные.Ответственный,
       Пронумерованные.ДатаДокумента УБЫВ
   

Это полная структура запроса, которая эффективно решает поставленную задачу. Мы начинаем с подготовки данных о требованиях, затем используем мощь оконных функций для нумерации документов внутри групп и, наконец, объединяем эти две части для получения итогового результата.

Важные рекомендации по оптимизации

Независимо от выбранного метода, всегда следуйте этим рекомендациям для обеспечения максимальной производительности запросов в 1С:

1. Ограничение первичной выборки: Как уже неоднократно упоминалось, это критически важно. Всегда старайтесь использовать условия ГДЕ для сокращения объема данных, с которыми будет работать запрос, особенно на первых этапах. Используйте параметры для дат и других отборов.
2. Индексирование: Убедитесь, что все поля, используемые в условиях соединения (ПО), группировки (РАЗБИТЬ ПО) и сортировки (УПОРЯДОЧИТЬ ПО), проиндексированы. В случае с документами 1С, поля Дата и Ответственный (или другие реквизиты, по которым идет группировка/сортировка) должны быть проиндексированы. Платформа 1С обычно сама создает индексы на стандартных реквизитах, но для нестандартных реквизитов или специфических сочетаний полей может потребоваться создание дополнительных индексов в режиме конфигуратора.
3. Использование временных таблиц (ПОМЕСТИТЬ): Для сложных запросов, состоящих из нескольких логических шагов, использование временных таблиц является хорошей практикой. Это позволяет разбить сложную логику на более управляемые итерации, улучшает читаемость запроса и часто способствует оптимизации, так как СУБД может более эффективно кэшировать и обрабатывать промежуточные результаты.
4. Тестирование производительности: Всегда тестируйте ваши запросы на реальных данных и в условиях, максимально приближенных к продуктивным. Оцените время выполнения запроса, потребление памяти и нагрузку на СУБД. Профайлер запросов в 1С (или средства СУБД, например, SQL Server Profiler) может быть очень полезен для анализа плана выполнения запроса и выявления "узких мест".

Следуя этим рекомендациям, вы сможете создавать эффективные и производительные запросы для решения самых разнообразных задач в 1С:Предприятии, включая такие нетривиальные, как выбор разного количества документов по группам.