Как преобразовать строки в колонки по типам документов и правильно использовать секцию ИМЕЮЩИЕ в запросах 1С?

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

Особенности секции ИМЕЮЩИЕ в конструкторе запросов

Для начала проанализируем ситуацию с наложением условий на уже сгруппированные данные. Если вам необходимо отфильтровать результат выполнения агрегатных функций (например, оставить только те строки, где сумма больше определенного значения), используется оператор ИМЕЮЩИЕ.

В конструкторе запросов 1С вы не найдете отдельной вкладки для секции ИМЕЮЩИЕ. Чтобы применить этот фильтр, просто задайте нужное условие на вкладке "Условия", добавив в него агрегатную функцию. Конструктор запросов сам "сообразит", что выражения, содержащие агрегатные функции (например, СУММА или МАКСИМУМ), нужно перенести не в секцию ГДЕ, а в секцию ИМЕЮЩИЕ.

Важное правило: Секция ИМЕЮЩИЕ работает исключительно как фильтр для уже сгруппированных данных. Если условие накладывается на обычное измерение или реквизит (например, отбор по конкретной номенклатуре), его нужно обязательно помещать в секцию ГДЕ. Это позволит отсечь лишние записи до начала ресурсоемкой операции группировки и значительно ускорит работу запроса.

Проблема избыточного использования временных таблиц

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

Посмотрим на пример такого громоздкого решения:

// Фрагмент неоптимального подхода
ВЫБРАТЬ 
    ВТ1.Номенклатура КАК НоменклатураВозврат, 
    СУММА(ВТ1.ВНаличииПриход) КАК ПриходВозврат 
ПОМЕСТИТЬ ВТ2_Возврат 
ИЗ ВТ1 КАК ВТ1 
ГДЕ ТИПЗНАЧЕНИЯ(ВТ1.Регистратор) = ТИП(Документ.ВозвратТоваровОтКлиента) 
СГРУППИРОВАТЬ ПО ВТ1.Номенклатура, ВТ1.Склад;

// ... создание еще 5 подобных таблиц ...

ВЫБРАТЬ 
    СУММА(ВТ2_Возврат.ПриходВозврат) КАК ПриходВозврат,
    СУММА(ВТ3_Списание.РасходСписание) КАК РасходСписание,
    // ...
ИЗ ВТ8_Групировка КАК ВТ1_Групировка 
ЛЕВОЕ СОЕДИНЕНИЕ ВТ2_Возврат КАК ВТ2_Возврат 
    ПО ВТ1_Групировка.Номенклатура = ВТ2_Возврат.НоменклатураВозврат
// ... еще множество левых соединений ...

Выясним причину, почему так делать категорически не рекомендуется. Такой подход является серьезным антипаттерном в 1С. Создание множества временных таблиц сильно нагружает дисковую подсистему и базу tempdb на SQL-сервере из-за постоянной записи и чтения промежуточных данных. Кроме того, множественные левые соединения (особенно если для временных таблиц не были созданы индексы) приводят к катастрофическому падению производительности при росте объемов данных.

Оптимальное решение: поворот данных "на лету" в один проход

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

Рассмотрим подробнее, как будет выглядеть оптимальный запрос для нашей задачи:

ВЫБРАТЬ
    ТаблицаРегистра.Номенклатура,
    ТаблицаРегистра.Склад,
    СУММА(ТаблицаРегистра.ВНаличииНачальныйОстаток) КАК НачальныйОстаток,
    СУММА(ТаблицаРегистра.ВНаличииКонечныйОстаток) КАК КонечныйОстаток,

    СУММА(ВЫБОР 
        КОГДА ТаблицаРегистра.Регистратор ССЫЛКА Документ.ВозвратТоваровОтКлиента 
        ТОГДА ТаблицаРегистра.ВНаличииПриход 
        ИНАЧЕ 0 
    КОНЕЦ) КАК ПриходВозврат,

    СУММА(ВЫБОР 
        КОГДА ТаблицаРегистра.Регистратор ССЫЛКА Документ.СписаниеНедостачТоваров 
        ТОГДА ТаблицаРегистра.ВНаличииРасход 
        ИНАЧЕ 0 
    КОНЕЦ) КАК РасходСписание,
    
    СУММА(ВЫБОР 
        КОГДА ТаблицаРегистра.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг 
        ТОГДА ТаблицаРегистра.ВНаличииРасход 
        ИНАЧЕ 0 
    КОНЕЦ) КАК РасходРеализация

ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
        &НачалоПериода, 
        &КонецПериода, 
        Регистратор, , 
        Номенклатура.ВидНоменклатуры.Наименование <> "топливо" И Склад = &Склад
    ) КАК ТаблицаРегистра

СГРУППИРОВАТЬ ПО
    ТаблицаРегистра.Номенклатура,
    ТаблицаРегистра.Склад

Разберем преимущества этого подхода:

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

Оптимизация проверки типа документа

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

Использование конструкции вида Регистратор ССЫЛКА Документ.ВозвратТоваровОтКлиента работает аналогично проверке типов, но делает код немного более компактным и привычным для стандартов разработки 1С. Применяйте оператор ССЫЛКА везде, где требуется типизировать ссылочные данные прямо внутри запроса.