Почему запрос в 1С или СКД не схлопывает строки при группировке по ресурсам?

← На главную

При разработке сложных отчетов в 1С:Предприятии с использованием промежуточных временных таблиц разработчики часто сталкиваются с ситуацией, когда итоговый запрос не схлопывает записи. Ожидается, что взаимозачитывающиеся положительные и отрицательные движения дадут нулевой результат и сольются в одну строку либо полностью исчезнут, однако в отчете формируются две или более визуально одинаковые строки с противоположными суммами. Разберем по шагам, почему так происходит, как локализовать ошибку и какими способами можно исключить нулевые остатки из итоговой выборки.

Анализ проблемы: скрытые различия в группировочных полях

Рассмотрим типовую ситуацию. Имеется временная таблица с итогами расчетов, из которой выбираются аналитические разрезы и суммируемые поля:


ВЫБРАТЬ
    ВТ.Организация,
    ВТ.Контрагент,
    ВТ.ДоговорКонтрагента,
    ВТ.СтатьяДвиженияДенежныхСредств,
    СУММА(ВТ.Авансы) КАК Авансы,
    СУММА(ВТ.ВСЕГОИТОГ) КАК ВСЕГОИТОГ
ИЗ
    ВТ_ДатаРасчетов КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Организация,
    ВТ.Контрагент,
    ВТ.ДоговорКонтрагента,
    ВТ.СтатьяДвиженияДенежныхСредств

Если после выполнения такого запроса строки с одинаковыми на первый взгляд значениями аналитики не сворачиваются, значит, значения в одном из полей группировки фактически различаются. В СУБД сравнение значений происходит со строгим учетом типов данных.

Выделим главные причины такого поведения:

Несоответствие типов: NULL и пустая ссылка. Это наиболее распространенная ошибка при объединении данных из разных источников (например, левых соединений регистров накопления и остатков). В одной ветке запроса при отсутствии соответствия поле заполняется значением NULL, а в другой ветке передается пустая ссылка справочника Значение(Справочник.СтатьиДвиженияДенежныхСредств.ПустаяСсылка). В пользовательском интерфейсе обе записи отображаются как пустые ячейки, но для механизма СГРУППИРОВАТЬ ПО это два принципиально разных значения, разделяющих итог на две независимые строки.
Случайное попадание ресурсов в секцию СГРУППИРОВАТЬ ПО. Если числовое поле (например, СуммаВВалюте) по ошибке перечислено в блоке группировки без агрегатной функции СУММА(), СУБД сгруппирует строки только при полном совпадении этого числа. Если в одной записи сумма составляла 100, а в корректирующей -100, они никогда не схлопнутся.
Время в полях типа «Дата». Если в группировку включено поле даты со временем, визуальный формат в отчете (например, ДФ=dd.MM.yyyy) скрывает секунды. Разница даже в одну секунду между записями разделит их на разные строки.
Дробная часть и машинное округление. Курсы валют, коэффициенты и расчетные показатели с плавающей точкой могут иметь различия в невидимых знаках после запятой (например, 0.0000001).
Невидимые спецсимволы в строках. Пробелы в начале или конце строковых полей, переводы строк и неразрывные пробелы делают строки визуально идентичными, но неравными при группировке.
Удаленные объекты («битые» ссылки). Если в базе удален элемент, на который ссылается документ, в отчет выводится строка вида <Объект не найден>.... Разные удаленные ссылки обладают разными уникальными идентификаторами (GUID), поэтому они не группируются вместе.

Методика поиска проблемного поля

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

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

Решение: нормализация данных в запросе

Разберем, как исправить конфликт типов между NULL и пустой ссылкой. В каждой исходной таблице или ветке объединения необходимо привести пустые значения к единому формату:


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

Если в группировке участвуют даты, обязательно приводим их к началу дня с помощью функции НАЧАЛОПЕРИОДА():

НАЧАЛОПЕРИОДА(Таблица.Период, ДЕНЬ) КАК ПериодДень

Для строковых полей применяем функцию очистки пробелов СОКРЛП(), а для расчетных коэффициентов фиксируем точность через конструкцию ВЫРАЗИТЬ():

ВЫРАЗИТЬ(Таблица.Курс КАК ЧИСЛО(15, 4)) КАК Курс

Как исключить нулевые строки из итогового отчета

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

Способ 1. Фильтрация на уровне запроса через секцию ИМЕЮЩИЕ

Конструкция ИМЕЮЩИЕ (аналог HAVING в SQL) отсекает записи после расчета агрегатных функций. Это оптимизирует производительность, уменьшая объем передаваемых в компоновку данных:


ВЫБРАТЬ
    ВТ.Контрагент КАК Контрагент,
    ВТ.ДоговорКонтрагента КАК ДоговорКонтрагента,
    СУММА(ВТ.ВСЕГОИТОГ) КАК ВСЕГОИТОГ
ИЗ
    ВТ_ДатаРасчетов КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Контрагент,
    ВТ.ДоговорКонтрагента
ИМЕЮЩИЕ
    ВЫРАЗИТЬ(СУММА(ВТ.ВСЕГОИТОГ) КАК ЧИСЛО(15, 2)) <> 0

Обратим внимание на использование ВЫРАЗИТЬ(): явное округление суммы защищает от ситуаций, когда из-за машинной погрешности в регистре зависает остаток вида 0.000001, который не равен нулю при строгом сравнении.

Способ 2. Отбор по ресурсу в настройках СКД

Если отчет строится на Системе компоновки данных, аналогичный результат можно получить через стандартные настройки:

Переходим на вкладку Ресурсы и убеждаемся, что для числового поля задано агрегатное выражение: Сумма(ВСЕГОИТОГ).
На вкладке Настройки в структуре отчета выбираем группировку верхнего уровня (или весь отчет).
В панели отборов добавляем условие: [ВСЕГОИТОГ] Не равно 0.

Важно понимать разницу: наложение отбора на поле, объявленное как Ресурс, в СКД выполняется после расчета итогов группировки (подобно секции ИМЕЮЩИЕ). Если же наложить отбор на числовое поле, которое не является ресурсом, СКД отфильтрует строки до свертки, что исказит сальдо и приведет к потере промежуточных данных.

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