Как в отчете СКД рассчитать и вывести количество разовых обращений клиентов?

← На главную

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

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

Разбор проблемы и быстрого решения в запросе

Начинающие разработчики часто путают логику работы СУБД при группировке данных. Попытка написать условие вида ВЫБОР КОГДА ИнформационныеКарты.Ссылка = 1 ТОГДА ... изначально ошибочна, так как поле Ссылка содержит уникальный идентификатор (GUID) объекта в базе данных, а не числовое значение количества обращений.

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

Рассмотрим структуру правильного условного выражения в списке выбора (секции ВЫБРАТЬ):


ВЫБРАТЬ
    ИнформационныеКарты.Наименование КАК Клиент,
    КОЛИЧЕСТВО(ИнформационныеКарты.Ссылка) КАК КоличествоОбращений,
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(ИнформационныеКарты.Ссылка) = 1 ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ КАК КоличествоРазовыхОбращений

В данном случае СУБД сначала сгруппирует записи по каждому клиенту, подсчитает общее число его обращений, а затем проверит условие: если получившееся количество равно единице, в колонку КоличествоРазовыхОбращений запишется 1, во всех остальных случаях — 0. Это позволит в дальнейшем легко просуммировать данное поле в итогах отчета.

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

Оптимизация исходного запроса: исправляем скрытые ошибки

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

Ошибка 1. Неявные соединения (разыменование ссылок через точку)

В тексте запроса присутствуют конструкции:

ПродажиПоДисконтнымКартам.ДисконтнаяКарта.КодКарты и ПродажиПоДисконтнымКартам.ДисконтнаяКарта.БЭ_НомерТелефона.

Обращение к реквизитам через две точки от таблицы регистра заставляет платформу 1С создавать скрытые (неявные) соединения на уровне СУБД. Поскольку в запросе уже прописано явное ЛЕВОЕ СОЕДИНЕНИЕ со справочником ИнформационныеКарты, дублирование связей через точку сильно перегружает план выполнения запроса. Выбирать эти реквизиты необходимо напрямую из уже присоединенной таблицы справочника.

Ошибка 2. Логическое противоречие в типе соединения

Использование конструкции ЛЕВОЕ СОЕДИНЕНИЕ подразумевает, что в результат могут попасть продажи по картам, которых нет в справочнике (значения полей справочника будут равны NULL). Однако в секции ИМЕЮЩИЕ прописано жесткое ограничение: КОЛИЧЕСТВО(ИнформационныеКарты.Ссылка) > 0. Это условие полностью отсекает все строки с NULL, принудительно превращая левое соединение во ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Для оптимизации работы оптимизатора СУБД следует явно указать внутреннее соединение.

Давайте посмотрим на исправленный и оптимизированный вариант классического запроса:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ИнформационныеКарты.Наименование КАК Клиент,
    ИнформационныеКарты.ДатаВыдачи КАК ДатаВыдачи,
    ИнформационныеКарты.КодКарты КАК КодКарты,
    ИнформационныеКарты.БЭ_НомерТелефона КАК НомерТелефона,
    КОЛИЧЕСТВО(ИнформационныеКарты.Ссылка) КАК КоличествоОбращений,
    ВЫБОР
        КОГДА КОЛИЧЕСТВО(ИнформационныеКарты.Ссылка) = 1 ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ КАК КоличествоРазовыхОбращений
ИЗ
    РегистрНакопления.ПродажиПоДисконтнымКартам КАК ПродажиПоДисконтнымКартам
    
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИнформационныеКарты КАК ИнформационныеКарты
    ПО ПродажиПоДисконтнымКартам.ДисконтнаяКарта = ИнформационныеКарты.Ссылка
ГДЕ
    ПродажиПоДисконтнымКартам.Период МЕЖДУ &ДатаНач И &ДатаКон
СГРУППИРОВАТЬ ПО
    ИнформационныеКарты.Наименование,
    ИнформационныеКарты.ДатаВыдачи,
    ИнформационныеКарты.КодКарты,
    ИнформационныеКарты.БЭ_НомерТелефона

Альтернативный подход: использование временных таблиц

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

Разберем этот алгоритм по шагам:

Выбираем все продажи по дисконтным картам за указанный период во временную таблицу ВТ_Продажи. Это позволит нам зафиксировать выборку данных и не обращаться повторно к физическим таблицам регистра.
Создаем вторую временную таблицу ВТ_РазовыеКарты, в которую помещаем только те карты, по которым была совершена ровно одна продажа. Отсечение выполняем с помощью конструкции ИМЕЮЩИЕ КОЛИЧЕСТВО(*) = 1.
В финальном запросе соединяем базовые данные с таблицей разовых карт. Если связь успешно установилась, выводим 1, в противном случае — 0.

Посмотрим на пример реализации этого эффективного подхода:


ВЫБРАТЬ
    Продажи.ДисконтнаяКарта КАК ДисконтнаяКарта
ПОМЕСТИТЬ ВТ_Продажи
ИЗ
    РегистрНакопления.ПродажиПоДисконтнымКартам КАК Продажи
ГДЕ
    Продажи.Период МЕЖДУ &ДатаНач И &ДатаКон
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Продажи.ДисконтнаяКарта КАК ДисконтнаяКарта
ПОМЕСТИТЬ ВТ_РазовыеКарты
ИЗ
    ВТ_Продажи КАК ВТ_Продажи
СГРУППИРОВАТЬ ПО
    ВТ_Продажи.ДисконтнаяКарта
ИМЕЮЩИЕ
    КОЛИЧЕСТВО(ВТ_Продажи.ДисконтнаяКарта) = 1
ИНДЕКСИРОВАТЬ ПО
    ДисконтнаяКарта
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ИнформационныеКарты.Наименование КАК Клиент,
    ИнформационныеКарты.ДатаВыдачи КАК ДатаВыдачи,
    ИнформационныеКарты.КодКарты КАК КодКарты,
    ИнформационныеКарты.БЭ_НомерТелефона КАК НомерТелефона,
    КОЛИЧЕСТВО(ВТ_Продажи.ДисконтнаяКарта) КАК КоличествоОбращений,
    ВЫБОР
        КОГДА ВТ_РазовыеКарты.ДисконтнаяКарта НЕ ЕСТЬ NULL ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ КАК КоличествоРазовыхОбращений
ИЗ
    ВТ_Продажи КАК ВТ_Продажи
    
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИнформационныеКарты КАК ИнформационныеКарты
    ПО ВТ_Продажи.ДисконтнаяКарта = ИнформационныеКарты.Ссылка
    
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_РазовыеКарты КАК ВТ_РазовыеКарты
    ПО ВТ_Продажи.ДисконтнаяКарта = ВТ_РазовыеКарты.ДисконтнаяКарта
СГРУППИРОВАТЬ ПО
    ИнформационныеКарты.Наименование,
    ИнформационныеКарты.ДатаВыдачи,
    ИнформационныеКарты.КодКарты,
    ИнформационныеКарты.БЭ_НомерТелефона,
    ВЫБОР
        КОГДА ВТ_РазовыеКарты.ДисконтнаяКарта НЕ ЕСТЬ NULL ТОГДА 1
        ИНАЧЕ 0
    КОНЕЦ

Использование индексирования временной таблицы ВТ_РазовыеКарты гарантирует моментальное выполнение левого соединения в финальной части пакета запросов.

Элегантное решение встроенными средствами СКД

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

Давайте проанализируем, как настроить расчет разовых обращений непосредственно в схеме компоновки данных:

В тексте запроса СКД мы оставляем только базовые поля и простое вычисление общего числа обращений:


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

Переходим на вкладку «Вычисляемые поля» нашей схемы компоновки данных и добавляем новое поле:
- Путь к данным: КоличествоРазовыхОбращений
- Выражение: Оставляем пустым (вычисление будет происходить на этапе расчета ресурсов)
- Заголовок: Разовые обращения
Переходим на вкладку «Ресурсы». Именно здесь мы опишем логику агрегации нашего показателя для итоговых строк отчета. Добавляем поле КоличествоРазовыхОбращений в список ресурсов и пишем для него следующее выражение: Сумма(Выбор Когда КоличествоОбращений = 1 Тогда 1 Иначе 0 Конец)

В чем главное преимущество решения через ресурсы СКД?

Если мы рассчитываем разовые посещения в запросе, то при любой группировке данных (например, при группировке по магазинам, менеджерам или месяцам) СУБД будет выдавать некорректные суммарные итоги, так как она складывает уже посчитанные жесткие нули и единицы детальных записей. Использование ресурсов СКД позволяет системе динамически пересчитывать логику "разовости" на каждом уровне группировки отчета.

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