При построении аналитических отчетов в 1С часто возникает задача сегментации данных по активности. Типичный сценарий: требуется распределить номенклатуру или клиентов по трем категориям:
Сложность заключается в совмещении двух разных контекстов выборки: глобального (вся история базы без фильтра по датам) и локального (выбранный интервал отчета). Разберем, как правильно реализовать подобный расчет в схеме компоновки данных (СКД) с помощью языка запросов и встроенных механизмов платформы 1С.
В практических задачах источником информации часто выступают регистры сведений или регистры накопления заказов. Рассмотрим распространенную структуру независимого непериодического регистра сведений Заказы со следующими полями:
ИдентификаторЗаказа.Клиент, Номенклатура, ДокументВыполнения, ДатаВыполнения.Если мы просто установим условие ГДЕ ДатаВыполнения МЕЖДУ &НачалоПериода И &КонецПериода в основном запросе, система полностью отсечет историю вне указанных дат. В результате невозможно будет отличить объект, купленный один раз за все время, от объекта, купленного один раз в этом месяце, но регулярно покупавшегося в прошлом году.
Также важно учитывать поведение агрегатной функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...):
NULL, но подсчитывает пустые ссылки, пустые строки и значение НЕОПРЕДЕЛЕНО как валидные уникальные значения.ИНАЧЕ конструкции ВЫБОР необходимо возвращать именно NULL.ЗНАЧЕНИЕ(Документ.ЗаказКлиента.ПустаяСсылка)). Если поле составного типа, то может встретиться и НЕОПРЕДЕЛЕНО.Проанализируем оптимальный по быстродействию вариант. Вместо ограничения выборки в секции ГДЕ мы выбираем данные без жесткого отсечения периода (или с ограничением только по верхней границе), а разделение на общую историю и интервал отчета выполняем непосредственно внутри агрегатных функций.
Посмотрим на пример текста запроса для набора данных СКД:
ВЫБРАТЬ
РСЗаказы.Номенклатура КАК Номенклатура,
РСЗаказы.Клиент КАК Клиент,
// Подсчет уникальных заказов за всю историю базы
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РСЗаказы.ИдентификаторЗаказа) КАК ЗаказовВсего,
// Подсчет уникальных выполненных заказов строго в выбранном периоде
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВЫБОР
КОГДА РСЗаказы.ДатаВыполнения МЕЖДУ &НачалоПериода И &КонецПериода
И РСЗаказы.ДокументВыполнения <> НЕОПРЕДЕЛЕНО
И РСЗаказы.ДокументВыполнения IS NOT NULL
ТОГДА РСЗаказы.ИдентификаторЗаказа
ИНАЧЕ NULL
КОНЕЦ) КАК ЗаказовВПериоде
ПОМЕСТИТЬ ВТ_АгрегированныеДанные
ИЗ
РегистрСведений.Заказы КАК РСЗаказы
СГРУППИРОВАТЬ ПО
РСЗаказы.Номенклатура,
РСЗаказы.Клиент
;
ВЫБРАТЬ
Данные.Номенклатура КАК Номенклатура,
Данные.Клиент КАК Клиент,
Данные.ЗаказовВсего КАК ЗаказовВсего,
Данные.ЗаказовВПериоде КАК ЗаказовВПериоде,
ВЫБОР
КОГДА Данные.ЗаказовВПериоде > 1 ТОГДА "Повторный"
КОГДА Данные.ЗаказовВПериоде = 1 И Данные.ЗаказовВсего = 1 ТОГДА "Нулевой (разовый)"
КОГДА Данные.ЗаказовВПериоде = 1 И Данные.ЗаказовВсего > 1 ТОГДА "Первичный в периоде"
ИНАЧЕ "Не заказывался в периоде"
КОНЕЦ КАК КатегорияЗаказа
ИЗ
ВТ_АгрегированныеДанные КАК Данные
ГДЕ
Данные.ЗаказовВПериоде > 0
Разберем логику классификации в этом запросе:
ГДЕ Данные.ЗаказовВПериоде > 0 в итоговой выборке отсекает строки, по которым в выбранном интервале не было активности, оставляя только актуальные для отчета позиции.Рассмотрим альтернативный вариант: расчет категорий можно вынести из текста запроса в механизмы компоновки данных. Это удобно, если требуется динамически менять правила категоризации без правки самого запроса.
Разберем по шагам настройку схемы компоновки данных:
ЗаказовВсего и ЗаказовВПериоде), как в первой временной таблице предыдущего примера.СтатусЗаказов.
Выбор
Когда ЗаказовВПериоде > 1 Тогда "Много"
Когда ЗаказовВПериоде = 1 И ЗаказовВсего = 1 Тогда "Нулевой"
Когда ЗаказовВПериоде = 1 Тогда "Один раз"
Иначе "Нет заказов"
Конец
Когда регистр содержит миллионы записей, полное сканирование всей таблицы может замедлить отчет. В этом случае эффективнее применить соединение двух независимых выборок:
Посмотрим на реализацию пакетного запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РСЗаказы.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураПериода
ИЗ
РегистрСведений.Заказы КАК РСЗаказы
ГДЕ
РСЗаказы.ДатаВыполнения МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
РСЗаказы.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РСЗаказы.ИдентификаторЗаказа) КАК ЗаказовВПериоде
ПОМЕСТИТЬ ВТ_ДанныеПериода
ИЗ
РегистрСведений.Заказы КАК РСЗаказы
ГДЕ
РСЗаказы.ДатаВыполнения МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
РСЗаказы.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
РСЗаказы.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РСЗаказы.ИдентификаторЗаказа) КАК ЗаказовВсего
ПОМЕСТИТЬ ВТ_ДанныеВсего
ИЗ
РегистрСведений.Заказы КАК РСЗаказы
ГДЕ
РСЗаказы.Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_НоменклатураПериода)
СГРУППИРОВАТЬ ПО
РСЗаказы.Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Период.Номенклатура КАК Номенклатура,
Период.ЗаказовВПериоде КАК ЗаказовВПериоде,
Всего.ЗаказовВсего КАК ЗаказовВсего,
ВЫБОР
КОГДА Период.ЗаказовВПериоде > 1 ТОГДА "Повторный"
КОГДА Период.ЗаказовВПериоде = 1 И Всего.ЗаказовВсего = 1 ТОГДА "Нулевой"
ИНАЧЕ "Первичный"
КОНЕЦ КАК Категория
ИЗ
ВТ_ДанныеПериода КАК Период
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ДанныеВсего КАК Всего
ПО Период.Номенклатура = Всего.Номенклатура
В этом варианте фильтр ГДЕ Номенклатура В (...) предотвращает сканирование всей истории базы по позициям, которые не продавались в выбранном временном отрезке.
Если в конфигурации используется независимый непериодический регистр сведений, обратим внимание на организацию индексов СУБД:
Клиент, Номенклатура или ДатаВыполнения находятся в секции Реквизиты, фильтрация и группировка по ним приведет к полному сканированию всей таблицы базы данных (Table Scan).