Как в одном запросе 1С получить продажи по товарам из определенной группы и вне ее

← На главную

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

Разберем типичные ошибки при написании таких запросов и рассмотрим оптимальные варианты решения задачи в рамках одного запроса.

Анализ типичных ошибок и причин медленной работы

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

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

Способ 1. Оптимальный запрос к табличной части с условным агрегированием (ВЫБОР КОГДА)

Рассмотрим наиболее производительный подход. Для исключения медленной проверки иерархии на каждом шаге мы сначала отбираем всю номенклатуру целевой группы во временную таблицу и индексируем ее. Затем строим запрос напрямую к табличной части Справочник.Чеки.Товары, присоединяем временную таблицу и разделяем суммы при помощи агрегатных функций с условием ВЫБОР.

Посмотрим на пример реализации запроса:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТоварыГруппы
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка В ИЕРАРХИИ(&ГруппаНоменклатуры)
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЧекиТовары.Ссылка.КассирККМ КАК Кассир,
    ЧекиТовары.Ссылка.Склад КАК Склад,
    СУММА(ВЫБОР
            КОГДА ВТ_ТоварыГруппы.Номенклатура ЕСТЬ НЕ NULL
                ТОГДА ЧекиТовары.Сумма
            ИНАЧЕ 0
        КОНЕЦ) КАК СуммаГруппа,
    СУММА(ВЫБОР
            КОГДА ВТ_ТоварыГруппы.Номенклатура ЕСТЬ NULL
                ТОГДА ЧекиТовары.Сумма
            ИНАЧЕ 0
        КОНЕЦ) КАК СуммаПрочее
ИЗ
    Справочник.Чеки.Товары КАК ЧекиТовары
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТоварыГруппы КАК ВТ_ТоварыГруппы
        ПО ЧекиТовары.Номенклатура = ВТ_ТоварыГруппы.Номенклатура
ГДЕ
    ЧекиТовары.Ссылка.ТипЧека = &ТипЧека
    И ЧекиТовары.Ссылка.Статус = &Статус
    И ЧекиТовары.Сторно = &Сторно
СГРУППИРОВАТЬ ПО
    ЧекиТовары.Ссылка.КассирККМ,
    ЧекиТовары.Ссылка.Склад

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

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

Способ 2. Формирование плоской таблицы для кросс-отчета в СКД

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

Рассмотрим текст запроса для схемы компоновки данных:


ВЫБРАТЬ
    Номенклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТоварыГруппы
ИЗ
    Справочник.Номенклатура КАК Номенклатура
ГДЕ
    Номенклатура.Ссылка В ИЕРАРХИИ(&ГруппаНоменклатуры)
ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ЧекиТовары.Ссылка КАК Чек,
    ЧекиТовары.Ссылка.КассирККМ КАК Кассир,
    ЧекиТовары.Ссылка.Склад КАК Склад,
    ЧекиТовары.Ссылка.ДатаОткрытия КАК ДатаОткрытияЧека,
    ВЫБОР
        КОГДА ВТ_ТоварыГруппы.Номенклатура ЕСТЬ НЕ NULL
            ТОГДА "Товары группы"
        ИНАЧЕ "Прочие товары"
    КОНЕЦ КАК КатегорияТовара,
    ЧекиТовары.Сумма КАК Сумма
ИЗ
    Справочник.Чеки.Товары КАК ЧекиТовары
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТоварыГруппы КАК ВТ_ТоварыГруппы
        ПО ЧекиТовары.Номенклатура = ВТ_ТоварыГруппы.Номенклатура
ГДЕ
    ЧекиТовары.Ссылка.ТипЧека = &ТипЧека
    И ЧекиТовары.Ссылка.Статус = &Статус
    И ЧекиТовары.Сторно = &Сторно

Для настройки вывода такого запроса в СКД выполним следующие шаги:

На вкладке Ресурсы укажем поле Сумма с выражением Сумма(Сумма).
На вкладке Настройки добавим элемент структуры Таблица.
В Строки таблицы добавим группировки Кассир и Склад.
В Колонки таблицы добавим группировку КатегорияТовара.

При формировании отчета СКД автоматически создаст колонки для каждой категории товара («Товары группы» и «Прочие товары»), а также добавит колонку общего итога по всем позициям.

Способ 3. Использование вычисляемых выражений ресурсов в СКД

Проанализируем еще одну возможность платформы, когда требуется передать флаг вхождения товара в группу и рассчитывать разные ресурсы на уровне СКД без изменения структуры колонок.

В запросе достаточно вернуть логическое поле:


ВЫБОР
    КОГДА ВТ_ТоварыГруппы.Номенклатура ЕСТЬ НЕ NULL
        ТОГДА ИСТИНА
    ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ЭтоТоварыГруппы

На вкладке Ресурсы СКД можно описать два независимых пользовательских ресурса:

Поле СуммаГруппа с выражением: Сумма(ВЫБОР КОГДА ЭтоТоварыГруппы ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ)
Поле СуммаПрочее с выражением: Сумма(ВЫБОР КОГДА НЕ ЭтоТоварыГруппы ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ)

Этот способ позволяет получить универсальный набор данных, сохраняя гибкость при настройке пользовательских полей и группировок отчета.