Разработка интерактивных аналитических отчетов типа «Шахматка» (или кросс-таблиц) — частая задача в торговых компаниях. Типичный пример такого отчета: по горизонтали (в колонках) необходимо вывести все группы номенклатуры с соблюдением иерархии, по вертикали (в строках) — всех клиентов и их торговые точки (склады клиентов), а на пересечении — суммы продаж за выбранный период. При этом критически важно отобразить даже тех клиентов и те группы товаров, по которым продаж вообще не было.
В этой статье мы подробно разберем, почему стандартные подходы «в лоб» приводят к зависанию базы данных, как устроена архитектура регистров продаж в ERP-решениях 1С (на примере Управление торговлей 11), и как правильно спроектировать быстрый и эффективный запрос в системе компоновки данных (СКД).
Часто начинающие разработчики пытаются построить такой отчет напрямую по табличным частям документов, например, Документ.ЗаказКлиента.Товары или Документ.РеализацияТоваровУслуг.Товары. Проанализируем типичный пример неоптимального запроса:
ВЫБРАТЬ
ЗаказКлиентаТовары.Номенклатура,
ЗаказКлиентаТовары.Сумма,
ЗаказКлиентаТовары.Ссылка.СкладКлиента,
ЗаказКлиентаТовары.Ссылка.Партнер
ИЗ
Документ.ЗаказКлиента.Товары КАК ЗаказКлиентаТовары
ГДЕ
ЗаказКлиентаТовары.Ссылка.Проведен
И ЗаказКлиентаТовары.Ссылка.Дата МЕЖДУ &НачДата И &КонДата
Выясним причину, почему данный запрос вызывает зависание системы при работе с реальной базой данных. Обращение к реквизитам шапки документа «через точку» от табличной части (ЗаказКлиентаТовары.Ссылка.СкладКлиента) заставляет СУБД выполнять неявное левое соединение (LEFT JOIN) основной таблицы со всеми связанными таблицами документов для каждой строчки товаров. Если в базе сотни тысяч строк документов, SQL-сервер тратит колоссальные ресурсы на выполнение этих соединений, что приводит к критическому падению производительности.
Кроме того, получение данных о продажах напрямую из документов концептуально неверно. Для анализа движений активов в ERP-системах существуют специализированные регистры накопления.
В конфигурациях на платформе 1С:Предприятие 8, построенных на базе современной ERP-платформы (включая УТ 11, КА 2 и ERP 2), учет выручки ведется в регистре накопления ВыручкаИСебестоимостьПродаж. Однако, открыв этот регистр, мы не обнаружим привычных измерений Партнер, Контрагент, Договор или Номенклатура. Вместо них используются так называемые ключи аналитики:
АналитикаУчетаНоменклатуры — ссылается на одноименный регистр сведений, где хранится связка номенклатуры, характеристик и складов.АналитикаУчетаПартнеров — ссылается на регистр сведений, хранящий информацию о партнере, контрагенте и организации.Такая архитектура позволяет существенно уменьшить размер физических таблиц БД. Чтобы получить реальные названия номенклатуры или партнеров, нам необходимо связать виртуальную таблицу оборотов регистра с соответствующими регистрами сведений ключей аналитики.
Рассмотрим, как правильно связать эти таблицы в запросе:
ВЫБРАТЬ
АналитикаПартнеров.Партнер КАК Партнер,
АналитикаНоменклатуры.Номенклатура КАК Номенклатура,
ВыручкаОбороты.СуммаВыручкиОборот КАК СуммаПродаж
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ВыручкаОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартнеров КАК АналитикаПартнеров
ПО ВыручкаОбороты.АналитикаУчетаПартнеров = АналитикаПартнеров.КлючАналитики
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаНоменклатуры КАК АналитикаНоменклатуры
ПО ВыручкаОбороты.АналитикаУчетаНоменклатуры = АналитикаНоменклатуры.КлючАналитики
Такой запрос будет выполняться мгновенно, поскольку СУБД работает с подготовленными индексными таблицами ключей аналитики и предварительно рассчитанными итогами регистра.
В нашей задаче присутствует нетиповой подчиненный справочник Н_СкладыКлиентов (торговые точки клиентов). Данный реквизит присутствует только в документах продажи и отсутствует в ключах аналитики регистра. Как получить его, не потеряв в производительности?
Для этого мы воспользуемся обращением к регистратору. Чтобы не перегружать СУБД, предварительно выберем все ссылки документов продажи и их нетиповые склады во временную таблицу, проиндексируем ее, а затем соединим с оборотами регистра. Посмотрим на этот прием:
// Шаг 1: Собираем документы со складами во временную таблицу
ВЫБРАТЬ РАЗЛИЧНЫЕ
Реализация.Ссылка КАК Ссылка,
Реализация.СкладКлиента КАК СкладКлиента
ПОМЕСТИТЬ ВТ_ДокументыСклады
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Проведен
И Реализация.Дата МЕЖДУ &НачалоПериода И &КонецПериода;
ИНДЕКСИРОВАТЬ ПО
Ссылка;
Теперь эту временную таблицу ВТ_ДокументыСклады мы сможем быстро и безопасно связать с таблицей оборотов регистра по полю Регистратор.
Основное требование заказчика: вывести в отчет всех клиентов со складами и все группы номенклатуры, независимо от того, были по ним продажи или нет. Если решать это через левое соединение справочников с продажами в одном запросе, мы рискуем получить избыточное декартово произведение, которое "повесит" систему, либо лишимся динамической иерархии СКД.
Правильный паттерн проектирования кросс-отчетов в СКД — использование механизма ОБЪЕДИНИТЬ ВСЕ в рамках одного набора данных. Мы объединяем три независимых вектора информации:
NULL и 0).Система компоновки данных самостоятельно сгруппирует эти три потока информации в оперативной памяти. На пересечении строк и колонок без продаж отобразятся нули или пустоты (так как по ресурсу Сумма произойдет сложение нулей из первых двух пакетов), а там, где продажи были — выведется фактическая сумма.
Рассмотрим итоговый текст запроса, который мы заложим в схему компоновки данных:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Реализация.Ссылка КАК Ссылка,
Реализация.СкладКлиента КАК СкладКлиента
ПОМЕСТИТЬ ВТ_ДокументыСклады
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализация
ГДЕ
Реализация.Проведен
И Реализация.Дата МЕЖДУ &НачалоПериода И &КонецПериода
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Партнеры.Ссылка КАК Партнер,
Н_СкладыКлиентов.Ссылка КАК СкладКлиента,
NULL КАК Номенклатура,
0 КАК СуммаПродаж
ИЗ
Справочник.Н_СкладыКлиентов КАК Н_СкладыКлиентов
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Партнеры КАК Партнеры
ПО Н_СкладыКлиентов.Владелец = Партнеры.Ссылка
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
NULL,
NULL,
Номенклатура.Ссылка,
0
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.ЭтоГруппа
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
АналитикаПартнеров.Партнер,
ДокументыСклады.СкладКлиента,
АналитикаНоменклатуры.Номенклатура,
ВыручкаОбороты.СуммаВыручкиОборот
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж.Обороты(&НачалоПериода, &КонецПериода, Авто, ) КАК ВыручкаОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаПартнеров КАК АналитикаПартнеров
ПО ВыручкаОбороты.АналитикаУчетаПартнеров = АналитикаПартнеров.КлючАналитики
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АналитикаУчетаНоменклатуры КАК АналитикаНоменклатуры
ПО ВыручкаОбороты.АналитикаУчетаНоменклатуры = АналитикаНоменклатуры.КлючАналитики
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДокументыСклады КАК ДокументыСклады
ПО ВыручкаОбороты.Регистратор = ДокументыСклады.Ссылка
После того как запрос написан, перейдем к настройке интерфейса вывода в СКД. Выполним следующие шаги:
СуммаПродаж с выражением расчета Сумма(СуммаПродаж).Партнер, а внутрь нее подчиненную группировку по полю СкладКлиента.Номенклатура. Нажимаем на нее правой кнопкой мыши и выбираем тип группировки — «Иерархия» (или «Только иерархия», если необходимо отображать только группы).Проанализируем результат: благодаря объединению векторов мастер-данных, СКД построит полную сетку из всех партнеров со складами и всей иерархической структуры номенклатуры. На пересечении тех элементов, где движений в периоде не было, ресурс СуммаПродаж посчитает сумму всех нулей и выведет аккуратный нулевой результат. Там же, где продажи были зафиксированы регистратором, ресурс отобразит корректную сумму выручки. Отчет работает стабильно и быстро даже на крупных базах данных за счет отказа от ресурсоемких соединений через точку и прямого обращения к регистрам сведений аналитики.