Почему в СКД неверно рассчитывается среднее количество строк в документах и как это исправить?

← На главную

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

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

Анализ проблемы: почему консоль запросов и СКД работают по-разному

Разберем типичный запрос, написанный для получения среднего числа строк в чеках ККМ:


ВЫБРАТЬ
    ЧекККМТовары.Ссылка КАК Ссылка,
    КОЛИЧЕСТВО(ЧекККМТовары.НомерСтроки) КАК КолвоСтрок
ПОМЕСТИТЬ втКолвоСтрокТекНеделя
ИЗ
    Документ.ЧекККМ.Товары КАК ЧекККМТовары
ГДЕ
    ЧекККМТовары.Ссылка.Склад В ИЕРАРХИИ(&Склады)
    И ЧекККМТовары.Ссылка.Проведен
    И ЧекККМТовары.Ссылка.Статус = ЗНАЧЕНИЕ(Перечисление.СтатусыЧековККМ.Пробит)
    И ЧекККМТовары.Ссылка.Дата МЕЖДУ &ТекущаяНеделя И КОНЕЦПЕРИОДА(&ТекущаяНеделя, НЕДЕЛЯ)
СГРУППИРОВАТЬ ПО
    ЧекККМТовары.Ссылка
;
ВЫБРАТЬ
    СРЕДНЕЕ(втКолвоСтрокТекНеделя.КолвоСтрок) КАК КолвоСтрок
ИЗ
    втКолвоСтрокТекНеделя КАК втКолвоСтрокТекНеделя

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

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

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

Способ 1. Замена временной таблицы на вложенные запросы

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

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


ВЫБРАТЬ
    ВложенныйЗапрос.КолвоСтрок КАК КолвоСтрок
{ВЫБРАТЬ
    КолвоСтрок}
ИЗ
    (ВЫБРАТЬ
        СРЕДНЕЕ(Среднее.КолвоСтрок) КАК КолвоСтрок
    ИЗ
        (ВЫБРАТЬ
            КОЛИЧЕСТВО(ЧекККМТовары.НомерСтроки) КАК КолвоСтрок,
            ЧекККМТовары.Ссылка КАК Ссылка
        ИЗ
            Документ.ЧекККМ.Товары КАК ЧекККМТовары
        ГДЕ
            ЧекККМТовары.Ссылка.Склад В ИЕРАРХИИ(&Склады)
            И ЧекККМТовары.Ссылка.Проведен
            И ЧекККМТовары.Ссылка.Статус = ЗНАЧЕНИЕ(Перечисление.СтатусыЧековККМ.Пробит)
            И ЧекККМТовары.Ссылка.Дата МЕЖДУ &ТекущаяНеделя И КОНЕЦПЕРИОДА(&ТекущаяНеделя, НЕДЕЛЯ)
        СГРУППИРОВАТЬ ПО
            ЧекККМТовары.Ссылка) КАК Среднее) КАК ВложенныйЗапрос

Обратите внимание: на больших объемах данных многоуровневые вложенные подзапросы могут увеличивать нагрузку на СУБД, поэтому рассмотрим альтернативные и более стандартные для СКД подходы.

Способ 2. Принудительное удержание поля группировки во временной таблице

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

Сделать это можно двумя путями:

Использование расширения языка запросов СКД: явно объявим поле Ссылка в фигурных скобках секции выбора первой таблицы:


ВЫБРАТЬ
    ЧекККМТовары.Ссылка КАК Ссылка,
    КОЛИЧЕСТВО(ЧекККМТовары.НомерСтроки) КАК КолвоСтрок
{ВЫБРАТЬ
    Ссылка.*}
ПОМЕСТИТЬ втКолвоСтрокТекНеделя
ИЗ
    Документ.ЧекККМ.Товары КАК ЧекККМТовары
...

Использование поля в финальном запросе: добавим фиктивное условие соединения или фильтрации во втором запросе:
```
ВЫБРАТЬ
    СРЕДНЕЕ(втКолвоСтрокТекНеделя.КолвоСтрок) КАК КолвоСтрок
ИЗ
    втКолвоСтрокТекНеделя КАК втКолвоСтрокТекНеделя
ГДЕ
    втКолвоСтрокТекНеделя.Ссылка ЕСТЬ НЕ NULL
```
Поскольку поле Ссылка задействовано в условии ГДЕ, СКД не сможет вырезать его из первой таблицы и сохранит группировку.

Способ 3. Вычисление среднего через ресурсы СКД (рекомендуемый подход)

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

Разберем по шагам реализацию через ресурсы:

В запросе выбираем документы и количество строк на каждый чек без агрегации среднего:


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

Переходим на вкладку Ресурсы схемы компоновки данных.
Добавляем поле КолвоСтрок и в колонке Выражение указываем формулу агрегации через массив: Среднее(ВычислитьВыражениеСГруппировкойМассив("КолвоСтрок", "Ссылка")) Либо, если запрос возвращает исходные строки табличной части (без группировки в запросе), рассчитываем математическое отношение: Количество(НомерСтроки) / Количество(Различные Ссылка)

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