Почему не группируются одинаковые строки в запросе 1С и как это исправить?

← На главную

Каждый разработчик 1С рано или поздно сталкивается с ситуацией, когда результат выполнения запроса ведет себя вопреки очевидной логике. Яркий пример такой загадки — когда в запросе используется секция СГРУППИРОВАТЬ ПО, но на выходе мы все равно получаем дублирующиеся строки с абсолютно одинаковым текстовым представлением (например, несколько строк с текстом "НДФЛ"). При этом, если выгрузить этот же результат в Таблицу Значений и применить метод ТЗ.Свернуть(), данные схлопываются идеально.

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

Выясняем причину проблемы: Ссылки против Строк

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

Когда мы пишем запрос к таблице (например, к временной таблице ВТ_НачисленияУдержанияОтбор), поле ВидРасчета имеет ссылочный тип данных (например, ПланВидовРасчетаСсылка.Удержания или составной тип). На уровне СУБД (MS SQL Server, PostgreSQL и др.) ссылка — это не текстовая строка, а уникальный 16-байтный идентификатор (UUID/GUID), хранящийся в бинарном виде.

Разберем это по шагам:

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

Почему не помогло использование функции ПРЕДСТАВЛЕНИЕ()?

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


ВЫБРАТЬ
    ПРЕДСТАВЛЕНИЕ(ВТ_НачисленияУдержанияОтбор.ВидРасчета) КАК ВидРасчета
ИЗ
    ВТ_НачисленияУдержанияОтбор КАК ВТ_НачисленияУдержанияОтбор
ГДЕ
    ВТ_НачисленияУдержанияОтбор.Удержано <> 0
СГРУППИРОВАТЬ ПО
    ПРЕДСТАВЛЕНИЕ(ВТ_НачисленияУдержанияОтбор.ВидРасчета)

Однако такой подход не работает, и строки по-прежнему двоятся. Выясним, почему так происходит. Дело в том, что функция ПРЕДСТАВЛЕНИЕ() является виртуальной функцией самой платформы 1С, а не базы данных. SQL Server ничего не знает о представлениях объектов 1С.

При трансляции этого запроса в SQL-код платформа заменяет группировку по представлению на группировку по фактическому ссылочному полю (UUID). СУБД группирует записи по ссылкам, отдает детальный результат платформе 1С, и уже сама платформа на сервере приложений преобразует ссылки в их строковые представления перед выводом пользователю. В результате мы получаем те же самые дубли, но уже в виде строк.

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

Решение проблемы непосредственно в запросе

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

Способ 1. Группировка по наименованию с явным приведением типов

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

Если же поле ВидРасчета имеет составной тип данных (например, может содержать ссылки на разные планы видов расчета или справочники), нам необходимо предварительно типизировать его с помощью конструкции ВЫРАЗИТЬ. Рассмотрим, как это сделать на примере:


ВЫБРАТЬ
    ВЫБОР
        КОГДА ВТ_НачисленияУдержанияОтбор.ВидРасчета ССЫЛКА ПланВидовРасчета.Начисления
            ТОГДА ВЫРАЗИТЬ(ВТ_НачисленияУдержанияОтбор.ВидРасчета КАК ПланВидовРасчета.Начисления).Наименование
        КОГДА ВТ_НачисленияУдержанияОтбор.ВидРасчета ССЫЛКА ПланВидовРасчета.Удержания
            ТОГДА ВЫРАЗИТЬ(ВТ_НачисленияУдержанияОтбор.ВидРасчета КАК ПланВидовРасчета.Удержания).Наименование
        ИНАЧЕ ""
    КОНЕЦ КАК ВидРасчетаСтрока
ПОМЕСТИТЬ ВТ_СтроковыеДанные
ИЗ
    ВТ_НачисленияУдержанияОтбор КАК ВТ_НачисленияУдержанияОтбор
ГДЕ
    ВТ_НачисленияУдержанияОтбор.Удержано <> 0
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_СтроковыеДанные.ВидРасчетаСтрока КАК ВидРасчета
ИЗ
    ВТ_СтроковыеДанные КАК ВТ_СтроковыеДанные
СГРУППИРОВАТЬ ПО
    ВТ_СтроковыеДанные.ВидРасчетаСтрока
УПОРЯДОЧИТЬ ПО
    ВидРасчета

Почему мы используем временную таблицу? Мы разделили запрос на два этапа: сначала мы извлекли строковые наименования и поместили их во временную таблицу ВТ_СтроковыеДанные, а во втором запросе пакета выполнили по ним простую и быструю группировку. Это позволяет СУБД построить оптимальный план запроса и исключает ошибки синтаксиса при группировке по сложным условным конструкциям.

Способ 2. Подготовка строковых данных при создании ВТ

Наиболее красивым и архитектурно правильным решением является подготовка строковых представлений на самом раннем этапе — при формировании исходной временной таблицы ВТ_НачисленияУдержанияОтбор.

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


// Этап формирования временной таблицы (пример схемы наполнения)
ВЫБРАТЬ
    ВЫРАЗИТЬ(Регистр.ВидРасчета.Наименование КАК СТРОКА(150)) КАК ВидРасчетаСтрока,
    Регистр.СуммаУдержано КАК Удержано
ПОМЕСТИТЬ ВТ_НачисленияУдержанияОтбор
ИЗ
    РегистрНакопления.ВзаиморасчетыССотрудниками КАК Регистр
;

////////////////////////////////////////////////////////////////////////////////
// Итоговый запрос с быстрой группировкой по строке
ВЫБРАТЬ
    ВТ_НачисленияУдержанияОтбор.ВидРасчетаСтрока КАК ВидРасчета
ИЗ
    ВТ_НачисленияУдержанияОтбор КАК ВТ_НачисленияУдержанияОтбор
ГДЕ
    ВТ_НачисленияУдержанияОтбор.Удержано <> 0
СГРУППИРОВАТЬ ПО
    ВТ_НачисленияУдержанияОтбор.ВидРасчетаСтрока
УПОРЯДОЧИТЬ ПО
    ВидРасчета

При таком подходе СУБД группирует данные по чистому строковому индексированному полю, что гарантирует моментальное выполнение запроса даже на огромных объемах информации и полностью исключает появление дубликатов с одинаковым текстом.

Способ 3. Использование возможностей Системы компоновки данных (СКД)

Если ваш запрос разрабатывается для отчета, строящегося на базе Системы компоновки данных (СКД), то выполнять преобразования типов в самом запросе нет необходимости. Компоновщик макета 1С умеет корректно объединять ссылочные поля по их текстовым представлениям на уровне вывода.

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

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

Каждый из рассмотренных способов имеет свои преимущества. Для динамических списков и программной обработки таблиц идеально подойдут Способ 1 и Способ 2. Для сложных аналитических отчетов лучшим выбором станет использование встроенного функционала СКД.