Как в запросе 1С подсчитать количество уникальных пар значений «Сотрудник — Должность»?

При разработке отчетов и аналитических выборок в системе «1С:Предприятие» программисты часто сталкиваются с задачей подсчета уникальных комбинаций нескольких полей. Типичный пример — определение количества уникальных пар «Сотрудник — Должность» в табличной части документа или регистре сведений. Сложность заключается в том, что один и тот же сотрудник может временно занимать разные должности, совмещать их или фигурировать в документах несколько раз с одной и той же должностью.

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

Анализ исходных данных и формулировка проблемы

Представим ситуацию, описанную на форуме. У нас есть табличная часть документа со следующими строками:

1. Сотрудник1 — Должность1
2. Сотрудник2 — Должность2
3. Сотрудник3 — Должность1
4. Сотрудник3 — Должность4
5. Сотрудник3 — Должность5
6. Сотрудник3 — Должность5 (Дублирующаяся строка)
7. Сотрудник3 — Должность1 (Дублирующаяся строка)
8. Сотрудник4 — Должность6
9. Сотрудник5 — [Должность не указана]
10. Сотрудник6 — Должность7
11. Сотрудник7 — [Должность не указана]
12. Сотрудник8 — [Должность не указана]

Если мы просто посчитаем общее количество строк в табличной части, то получим 12 записей. Однако нам необходимо получить количество уникальных комбинаций сотрудника и должности. Повторяющиеся строки (например, повторное появление пары «Сотрудник3 — Должность5» или «Сотрудник3 — Должность1») должны быть исключены из подсчета.

Проанализируем наши данные вручную. Уникальными парами будут:

1. Сотрудник1 — Должность1
2. Сотрудник2 — Должность2
3. Сотрудник3 — Должность1
4. Сотрудник3 — Должность4
5. Сотрудник3 — Должность5
6. Сотрудник4 — Должность6
7. Сотрудник5 — [Пусто]
8. Сотрудник6 — Должность7
9. Сотрудник7 — [Пусто]
10. Сотрудник8 — [Пусто]

Таким образом, правильный запрос должен вернуть число 10 (или 10 строк, содержащих эти уникальные комбинации). Выясним, как реализовать это на языке запросов 1С.

Ограничение функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...)

Первое интуитивное решение, которое приходит в голову начинающему разработчику, выглядит так:

ВЫБРАТЬ
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТЧ.Сотрудник, ТЧ.Должность) КАК КоличествоПар
ИЗ
    Документ.ИмяДокумента.ИмяТЧ КАК ТЧ

Однако при попытке сохранить или выполнить такой запрос платформа выдаст синтаксическую ошибку. Дело в том, что в языке запросов 1С функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) может принимать в качестве параметра только одно поле. Передать туда список полей или составную конструкцию напрямую невозможно. Для решения этой проблемы нам придется использовать обходные пути.

Способ 1. Использование вложенного запроса (Классическое решение)

Чтобы обойти ограничение платформы, мы можем сначала выбрать уникальные пары с помощью ключевого слова РАЗЛИЧНЫЕ во вложенном запросе, а во внешнем запросе подсчитать количество строк в полученной выборке.

Рассмотрим этот код подробнее:

ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК КоличествоПар
ИЗ
    (ВЫБРАТЬ РАЗЛИЧНЫЕ
        ТЧ.Сотрудник КАК Сотрудник,
        ТЧ.Должность КАК Должность
    ИЗ
        Документ.ИмяДокумента.ИмяТЧ КАК ТЧ) КАК ВложенныйЗапрос

Этот запрос работает следующим образом:

1. Внутренний запрос обращается к табличной части документа и выбирает поля Сотрудник и Должность. Благодаря ключевому слову РАЗЛИЧНЫЕ, СУБД автоматически сворачивает дублирующиеся строки. В нашем примере внутренний запрос вернет именно 10 уникальных записей.
2. Внешний запрос принимает результат работы внутреннего запроса как виртуальную таблицу и выполняет функцию КОЛИЧЕСТВО(*), которая считает общее число строк. На выходе мы получаем заветное число 10.

Способ 2. Оптимизация через Временные таблицы (Стандарты фирмы «1С»)

Хотя первый способ прост в написании и часто используется на практике, у него есть существенный недостаток. Согласно стандартам разработки фирмы «1С», использование вложенных запросов в реальных высоконагруженных базах данных крайне не рекомендуется. Вложенные запросы мешают оптимизатору СУБД (MS SQL Server, PostgreSQL) корректно построить план выполнения запроса, что на больших объемах данных может привести к серьезным просадкам производительности.

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

ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Сотрудник КАК Сотрудник,
    ТЧ.Должность КАК Должность
ПОМЕСТИТЬ ВТ_УникальныеПары
ИЗ
    Документ.ИмяДокумента.ИмяТЧ КАК ТЧ
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    КОЛИЧЕСТВО(*) КАК КоличествоСтрок
ИЗ
    ВТ_УникальныеПары КАК ВТ_УникальныеПары

Разберем по шагам, почему этот вариант предпочтительнее:

1. В первом запросе пакета мы извлекаем уникальные комбинации и физически сохраняем их во временную таблицу ВТ_УникальныеПары в базе данных TempDB.
2. Во втором запросе мы просто подсчитываем количество записей в этой временной таблице. Оптимизатор СУБД легко справляется с такой задачей, строя максимально эффективный план выполнения.
3. Код становится более читаемым, его проще отлаживать в консоли запросов.

Производительность: РАЗЛИЧНЫЕ против СГРУППИРОВАТЬ ПО

Вместо использования конструкции РАЗЛИЧНЫЕ во временной таблице или вложенном запросе мы можем применить группировку. С точки зрения конечного результата, следующие два фрагмента кода абсолютно идентичны:

// Вариант с РАЗЛИЧНЫЕ
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ТЧ.Сотрудник,
    ТЧ.Должность
...

// Вариант со СГРУППИРОВАТЬ ПО
ВЫБРАТЬ
    ТЧ.Сотрудник,
    ТЧ.Должность
ИЗ
    Документ.ИмяДокумента.ИмяТЧ КАК ТЧ
СГРУППИРОВАТЬ ПО
    ТЧ.Сотрудник,
    ТЧ.Должность

Как это работает под капотом СУБД? В большинстве современных систем управления базами данных (например, MS SQL Server) для обеих конструкций строится один и тот же план выполнения (используются алгоритмы Hash Match или Stream Aggregate). Разницы в скорости выполнения обычного запроса практически не будет.

Важное ограничение для динамических списков: Если вы разрабатываете форму списка или отчет на основе динамического списка, использовать ключевое слово РАЗЛИЧНЫЕ категорически запрещено. Это заставит СУБД сканировать всю физическую таблицу для отсечения дублей, что полностью заблокирует механизм постраничной выборки платформы (оптимизация TOP N). Интерфейс программы начнет сильно зависать при прокрутке списка пользователем. В динамических списках всегда отдавайте предпочтение конструкции СГРУППИРОВАТЬ ПО.

Поведение пустых значений и NULL

Проанализируем, как система обрабатывает незаполненные значения. В исходном примере у сотрудников 5, 7 и 8 должность не указана. Почему они корректно сгруппировались, а запрос вернул именно 10 строк?

Здесь важно различать пустые значения в физических таблицах 1С и значения NULL, возникающие при соединениях:

• Пустые ссылки в ТЧ документа: В табличных частях документов пустая должность хранится как полноценный объект — пустая ссылка типа СправочникСсылка.Должности. СУБД воспринимает ее как конкретное значение (грубо говоря, как пустую уникальную строку). Поэтому группировка по парам «Сотрудник5 — ПустаяСсылка», «Сотрудник7 — ПустаяСсылка» и «Сотрудник8 — ПустаяСсылка» работает без проблем, они учитываются в итоговом подсчете.
• Значения NULL при соединениях: Если бы мы получали должности из другой таблицы через ЛЕВОЕ СОЕДИНЕНИЕ, на месте отсутствующих должностей у нас образовался бы настоящий NULL. В этом случае нужно помнить важное правило работы агрегатных функций: функция КОЛИЧЕСТВО(ИмяПоля) полностью игнорирует значения NULL при расчете, в то время как КОЛИЧЕСТВО(*) всегда считает все строки, даже содержащие NULL.

Современные возможности оптимизации (для платформы 1С 8.3.25 и выше)

Если подобные задачи по свертке уникальных пар данных решаются в крупных корпоративных информационных системах с миллионами записей, новые релизы платформы предоставляют дополнительные инструменты:

1. Новый метод наполнения временных таблиц: Начиная с версии 1С 8.3.25, в языке запросов появилась возможность использовать конструкцию ДОБАВИТЬ В. Теперь вы можете порционно дописывать новые порции данных в уже созданную временную таблицу, избегая дублирования кода и создания множества промежуточных таблиц.
2. Составные индексы по наборам полей: В новых версиях платформы во временной таблице можно создавать уникальный составной индекс сразу по нескольким полям с помощью конструкции ИНДЕКСИРОВАТЬ ПО НАБОРАМ. В нашем случае мы можем проиндексировать временную таблицу сразу по набору полей Сотрудник, Должность. Это колоссально ускорит последующие соединения этой таблицы с другими объектами базы данных.