Как сгруппировать строки в запросе 1С и убрать дубли при наличии лишних данных

В практике разработчика 1С часто возникают ситуации, когда данные в регистрах сведений заполнены не совсем корректно или содержат избыточную информацию. Например, при обращении к периодическому регистру мы можем получить несколько строк на одну дату, которые отличаются только тем, что в одной строке ресурс заполнен, а в другой — равен нулю. Стандартное использование ключевого слова РАЗЛИЧНЫЕ в таких случаях не помогает, так как строки формально разные. Разберем подробно, как «схлопнуть» такие записи и получить чистый результат.

Анализ проблемы: почему РАЗЛИЧНЫЕ не работает

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

1. Измерение — 100, Ресурс1 — 200, Ресурс2 — 0
2. Измерение — 100, Ресурс1 — 0, Ресурс2 — 0

Если мы используем ВЫБРАТЬ РАЗЛИЧНЫЕ, система честно вернет обе строки, так как комбинация значений во всех колонках уникальна. Однако с точки зрения бизнес-логики нам нужна только одна строка, где ресурс имеет значение 200. Ситуация осложняется тем, что в реальных конфигурациях могут присутствовать «лишние» измерения, которые пользователь заполнил ошибочно, что и порождает такие дубли.

Решение 1: Использование агрегатной функции МАКСИМУМ

Самый простой и эффективный способ избавиться от нулевых значений, если в других строках по тем же ключевым полям есть ненулевые данные — это использование функции МАКСИМУМ. Рассмотрим, как это работает на практике.

Вместо простого перечисления полей, мы используем группировку:

ВЫБРАТЬ
    Регистр1.Измерение КАК Измерение,
    МАКСИМУМ(Регистр1.Ресурс1) КАК Ресурс1,
    МАКСИМУМ(Регистр1.Ресурс2) КАК Ресурс2
ИЗ
    РегистрСведений.РегистрСведений1 КАК Регистр1
СГРУППИРОВАТЬ ПО
    Регистр1.Измерение

Почему это помогает? Функция МАКСИМУМ для числовых полей выберет наибольшее значение. Если у нас есть 200 и 0, результатом будет 200. Если есть только нули, результатом будет 0. Это позволяет «не замечать» нулевые записи, если по данному измерению существует хотя бы одна заполненная строка. Важно помнить: этот метод подходит только в том случае, если нам не нужно суммировать значения, а нужно именно выбрать одно наиболее полное.

Решение 2: Условное суммирование через ВЫБОР КОГДА

Иногда логика выбора данных сложнее, чем просто поиск максимума. Рассмотрим вариант, предложенный участниками форума, когда нам нужно отсечь записи по условию взаимосвязи ресурсов. Разберем по шагам пример кода с использованием конструкции ВЫБОР внутри агрегатной функции СУММА.

ВЫБРАТЬ
    Регистр1.Измерение КАК Измерение,
    СУММА(ВЫБОР
            КОГДА Регистр1.Ресурс1 * Регистр1.Ресурс2 = 0
                ТОГДА 0
            ИНАЧЕ Регистр1.Ресурс1
        КОНЕЦ) КАК Ресурс1,
    СУММА(ВЫБОР
            КОГДА Регистр1.Ресурс1 * Регистр1.Ресурс2 = 0
                ТОГДА 0
            ИНАЧЕ Регистр1.Ресурс2
        КОНЕЦ) КАК Ресурс2
ИЗ
    РегистрСведений.РегистрСведений1 КАК Регистр1
СГРУППИРОВАТЬ ПО
    Регистр1.Измерение

Проанализируем это решение. Здесь мы применяем фильтрацию прямо в момент суммирования. Если произведение ресурсов равно нулю (что означает наличие хотя бы одного нуля в строке), система заменяет значение на 0 для итоговой суммы. Это более специфичный метод, который полезен, когда данные считаются валидными только при одновременном заполнении нескольких полей.

Решение 3: Защита от NULL и группировка по «грязным» измерениям

Выясним причину появления дублей, связанную с «лишними» измерениями. Если пользователь заполнил измерение, которое не должно влиять на уникальность в вашем конкретном отчете, его нужно просто исключить из списка СГРУППИРОВАТЬ ПО. При этом крайне желательно использовать функцию ЕСТЬNULL, особенно если ваш запрос включает левые соединения с другими таблицами.

Рассмотрим улучшенный вариант запроса с защитой от пустых значений:

ВЫБРАТЬ
    Регистр1.ОсновноеИзмерение КАК ОсновноеИзмерение,
    СУММА(ВЫБОР
            КОГДА ЕСТЬNULL(Регистр1.Ресурс1, 0) <> 0
                ТОГДА Регистр1.Ресурс1
            ИНАЧЕ 0
        КОНЕЦ) КАК Ресурс1,
    МАКСИМУМ(ЕСТЬNULL(Регистр1.Ресурс2, 0)) КАК Ресурс2
ИЗ
    РегистрСведений.РегистрСведений1 КАК Регистр1
СГРУППИРОВАТЬ ПО
    Регистр1.ОсновноеИзмерение

В данном примере мы разделяем логику для разных ресурсов: один мы суммируем (если он не нулевой), а для другого берем максимальное значение. Это позволяет гибко настроить получение данных даже из «замусоренного» регистра.

Рекомендации по работе с виртуальными таблицами

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

Чтобы этого избежать, примените следующую методику:

1. Сначала создадим временную таблицу, в которую выберем только нужные измерения и максимальный период.
2. Затем соединим эту временную таблицу с основной таблицей регистра для получения ресурсов.
3. На конечном этапе применим группировку МАКСИМУМ по ресурсам, как мы разбирали выше.

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

Подведение итогов

Для успешной группировки строк без лишнего суммирования в 1С следует придерживаться следующих правил:

• Всегда используйте СГРУППИРОВАТЬ ПО вместо РАЗЛИЧНЫЕ, если нужно управлять логикой схлопывания полей.
• Для полей, которые должны быть «уникальными», используйте МАКСИМУМ — это надежный способ отсечь нули.
• Применяйте ВЫБОР КОГДА внутри агрегатных функций для сложной логики фильтрации.
• Не забывайте про ЕСТЬNULL, чтобы избежать непредсказуемого поведения запроса при работе с пустыми значениями в базе данных.

Рассмотренные методы позволяют эффективно очищать данные на уровне запроса, обеспечивая корректность отчетов даже при наличии ошибок в исходных записях регистров.