Почему неверно группируются данные в СКД и как правильно связать показатели по месяцам

← На главную

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

Анализ проблемы: почему пропадают данные при соединении

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

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


ВЫБРАТЬ
    БаллыРозничныхРаботников.Балл КАК БаллРаботника,
    БаллыРозничныхРаботников.Работник КАК РаботникРозницы,
    БаллыРозничныхРаботников.Период КАК период2,
    БонусыРозничныхРаботниковРуководителя.Баллы КАК БаллРуководителя
ИЗ
    РегистрСведений.БаллыРозничныхРаботников КАК БаллыРозничныхРаботников
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрСведений.БонусыРозничныхРаботниковРуководителя КАК БонусыРозничныхРаботниковРуководителя
ПО 
    БаллыРозничныхРаботников.Работник = БонусыРозничныхРаботниковРуководителя.Работник 
    И БаллыРозничныхРаботников.Период = БонусыРозничныхРаботниковРуководителя.Период

Основная причина неудачи здесь заключается в избыточной точности условия связи. Для бизнес-логики оба начисления относятся к «Февралю», но для СУБД это разные моменты времени.

Решение №1: Использование функции НАЧАЛОПЕРИОДА в условиях связи

Самый быстрый способ исправить ситуацию — это «огрубить» время до начала месяца прямо в условии соединения. Выясним, как это работает: мы приводим обе даты к первому числу месяца 00:00:00, и тогда записи, созданные в разные дни одного месяца, успешно сопоставляются.

Скорректированный запрос будет выглядеть так:


ВЫБРАТЬ
    БаллыРозничныхРаботников.Работник КАК Работник,
    НАЧАЛОПЕРИОДА(БаллыРозничныхРаботников.Период, МЕСЯЦ) КАК Месяц,
    БаллыРозничныхРаботников.Балл КАК БаллРаботника,
    БонусыРозничныхРаботниковРуководителя.Баллы КАК БаллРуководителя
ИЗ
    РегистрСведений.БаллыРозничныхРаботников КАК БаллыРозничныхРаботников
ЛЕВОЕ СОЕДИНЕНИЕ 
    РегистрСведений.БонусыРозничныхРаботниковРуководителя КАК БонусыРозничныхРаботниковРуководителя
ПО 
    БаллыРозничныхРаботников.Работник = БонусыРозничныхРаботниковРуководителя.Работник 
    И НАЧАЛОПЕРИОДА(БаллыРозничныхРаботников.Период, МЕСЯЦ) = НАЧАЛОПЕРИОДА(БонусыРозничныхРаботниковРуководителя.Период, МЕСЯЦ)

Важное предостережение: использование функций в условии ПО (JOIN) крайне негативно сказывается на производительности при больших объемах данных. СУБД не может использовать индексы по полям Период, так как они обернуты в функцию, и будет вынуждена сканировать всю таблицу. Если записей десятки тысяч, отчет начнет «тормозить».

Решение №2: Использование объединения (UNION ALL) вместо соединений

Опытные разработчики 1С рекомендуют использовать метод объединения. Это более надежный подход, который гарантирует, что данные не потеряются (даже если в одном месяце есть бонусы, но нет баллов) и не задвоятся при расчете итогов. Мы выбираем данные из каждого регистра отдельными порциями и «склеиваем» их вертикально.

Рассмотрим пример запроса с объединением:


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

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Работник,
    НАЧАЛОПЕРИОДА(Период, МЕСЯЦ),
    0,
    Баллы
ИЗ
    РегистрСведений.БонусыРозничныхРаботниковРуководителя

После этого в настройках СКД мы определяем БаллРаботника и БаллРуководителя как ресурсы с функцией Сумма(). Группировка по полям Работник и Месяц в самом отчете автоматически «схлопнет» эти строки в одну красивую строку, где будут заполнены оба показателя. Это самый архитектурно верный путь для СКД.

Решение №3: Связь наборов данных в СКД

Если данные должны оставаться в разных запросах, можно воспользоваться механизмом «Связи наборов данных» в самой схеме компоновки. Рассмотрим алгоритм настройки:

Создаем Набор данных 1 для баллов работников.
Создаем Набор данных 2 для бонусов руководителей.
На вкладке «Связи наборов данных» указываем источником Набор данных 1, а приемником Набор данных 2.
В условиях связи выбираем поля Работник и Месяц.

Преимущество этого метода в том, что СКД сама оптимизирует запросы к базе данных. Однако нужно помнить, что для эффективной связи поля Месяц в обоих наборах должны быть предварительно вычислены в запросе через НАЧАЛОПЕРИОДА.

Тонкости настройки ресурсов и группировок

Часто проблема «неверной группировки» кроется не в запросе, а в настройках полей. Разберем, на что обратить внимание:

1. Роли полей периода. Чтобы СКД правильно понимала иерархию времени, для поля Месяц на вкладке «Наборы данных» нужно установить роль. Укажите тип «Период» и выберите уровень (например, 2, если есть еще и день). Это позволит платформе корректно выводить пустые периоды и правильно суммировать итоги.

2. Проблема дублирования при соединении «один ко многим». Если в одном месяце у работника пять записей с баллами, а у руководителя — одна запись с бонусом, то при обычном LEFT JOIN бонус руководителя продублируется пять раз. Сумма будет неверной. Чтобы этого избежать, в выражении ресурса можно использовать функцию:

ВычислитьВыражениеСГруппировкойМассив("Максимум(БаллРуководителя)", "Работник, Месяц")

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

Использование временных таблиц для ускорения

Проанализируем ситуацию с производительностью. Если мы решили использовать соединение, но база данных велика, нам помогут временные таблицы. Сначала подготовим данные, вычислим периоды и проиндексируем их.


ВЫБРАТЬ
    Работник,
    НАЧАЛОПЕРИОДА(Период, МЕСЯЦ) КАК Месяц,
    СУММА(Балл) КАК Балл
ПОМЕСТИТЬ ВТ_БаллыРаботников
ИЗ
    РегистрСведений.БаллыРозничныхРаботников
СГРУППИРОВАТЬ ПО
    Работник,
    НАЧАЛОПЕРИОДА(Период, МЕСЯЦ)
ИНДЕКСИРОВАТЬ ПО
    Работник, Месяц
;

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

ВЫБРАТЬ
    ВТ_БаллыРаботников.Работник,
    ВТ_БаллыРаботников.Месяц,
    ВТ_БаллыРаботников.Балл,
    ВТ_БонусыРуководителей.БаллРуководителя
ИЗ
    ВТ_БаллыРаботников КАК ВТ_БаллыРаботников
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_БонусыРуководителей КАК ВТ_БонусыРуководителей
ПО ВТ_БаллыРаботников.Работник = ВТ_БонусыРуководителей.Работник
И ВТ_БаллыРаботников.Месяц = ВТ_БонусыРуководителей.Месяц

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

Резюме по шагам

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

Всегда приводим даты к началу периода (началу месяца) в запросе.
Используем UNION ALL, если хотим гарантированно избежать потери записей из «правой» таблицы.
Настраиваем Роли для полей периодов, чтобы СКД корректно строила иерархию.
Если используем соединения, проверяем, не происходит ли размножение строк, и при необходимости корректируем формулы ресурсов.
Для повышения скорости работы отчета на больших объемах используем временные таблицы с индексами.

Применяя эти методы, мы добьемся того, что отчет будет выводить реальные данные, а группировка по месяцам станет работать предсказуемо и быстро.