Как объединить строки с остатками и оборотами в запросе 1С: устраняем дублирование

← На главную

При разработке отчетов по складским запасам программисты часто сталкиваются с ситуацией, когда данные по одной и той же номенклатуре за один день выводятся в нескольких строках. Вместо одной аккуратной строки с начальным остатком, приходом, расходом и конечным остатком мы получаем "лесенку" или раздвоение данных. Это классическая проблема, связанная с архитектурой запросов и пониманием работы виртуальных таблиц.

В этой статье мы подробно разберем, почему возникает "раздвоение" строк, как правильно использовать агрегатные функции и почему группировка по ресурсам — это главная ошибка начинающего разработчика 1С. Проанализируем ситуацию на конкретном примере и найдем оптимальное решение.

Причины возникновения лишних строк в запросе

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

1. Группировка по числовым ресурсам

Это самая распространенная ошибка. В языке запросов 1С секция СГРУППИРОВАТЬ ПО определяет уникальный ключ строки. Если вы включаете в нее такие поля, как КоличествоНачальныйОстаток или КоличествоРасход, система будет создавать новую строку для каждого уникального значения этих полей. Если в разные моменты времени или при разных соединениях значения остатка хотя бы минимально отличаются (например, из-за NULL), "схлопывания" не произойдет.

2. Использование типа значения регистратора

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

3. Избыточное соединение виртуальных таблиц

Проанализируем пример, где одновременно используются таблицы ОстаткиИОбороты и Обороты. Это методическая ошибка. Виртуальная таблица ОстаткиИОбороты спроектирована так, чтобы отдавать все данные сразу: и остатки на начало/конец, и обороты за период. Соединение ее с таблицей Обороты по номенклатуре порождает эффект "картезианского произведения", когда на каждую строку остатка накладываются все строки оборотов, что приводит к неконтролируемому размножению данных.

Разбираем решение по шагам

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

Шаг 1. Оптимизация выбора виртуальной таблицы

Вместо соединения нескольких таблиц регистра накопления, используем одну — ТоварыНаСкладах.ОстаткиИОбороты. Она уже содержит в себе все необходимые ресурсы.


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

Обратите внимание на параметр Периодичность (в примере указано День). Это критически важно для корректного сворачивания данных по датам.

Шаг 2. Правильная группировка и агрегация

Чтобы гарантированно "схлопнуть" строки, нам нужно применить агрегатную функцию СУММА ко всем числовым показателям и оставить в секции СГРУППИРОВАТЬ ПО только измерения (Номенклатура, Склад) и периоды.

Проанализируем исправленный фрагмент запроса:


ВЫБРАТЬ
    ВТ_Дни.ДатаКалендаря КАК ДатаКалендаря,
    Остатки.Номенклатура КАК Номенклатура,
    СУММА(ЕСТЬNULL(Остатки.beg_rest, 0)) КАК beg_rest,
    СУММА(ЕСТЬNULL(Остатки.in_qty, 0)) КАК in_qty,
    СУММА(ЕСТЬNULL(Остатки.out_qty, 0)) КАК out_qty,
    СУММА(ЕСТЬNULL(Остатки.end_rest, 0)) КАК end_rest
ПОМЕСТИТЬ ВТ_ИтоговыеДанные
ИЗ
    ДатаКалендаря КАК ВТ_Дни
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК Остатки
    ПО ВТ_Дни.ДатаКалендаря = Остатки.ДатаКалендаря
СГРУППИРОВАТЬ ПО
    ВТ_Дни.ДатаКалендаря,
    Остатки.Номенклатура

Важный нюанс: использование ЕСТЬNULL(Поле, 0) внутри суммы гарантирует, что если по какой-то номенклатуре в этот день не было движений (NULL), система корректно обработает это как ноль и не создаст лишних пустых строк при группировке.

Шаг 3. Обработка цен и стоимостных показателей

Часто требуется не просто вывести количество, но и умножить его на цену. Ошибка здесь заключается в том, что разработчики пытаются группировать по цене. Если цена менялась в течение периода, это опять приведет к раздвоению строк.

Выясним причину: цена для системы — это такое же значение группировки, как и номенклатура. Чтобы избежать проблем, сначала получаем "схлопнутые" количественные остатки во временную таблицу, и только потом левым соединением подтягиваем к ним цену из СрезПоследних.


ВЫБРАТЬ
    Данные.ДатаКалендаря,
    Данные.Номенклатура,
    Данные.beg_rest,
    Цены.Цена КАК АктуальнаяЦена,
    Данные.beg_rest * ЕСТЬNULL(Цены.Цена, 0) КАК beg_amount
ИЗ
    ВТ_ИтоговыеДанные КАК Данные
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ДатаКон, ТипЦен = &ТипЦены) КАК Цены
    ПО Данные.Номенклатура = Цены.Номенклатура

Почему не работал первоначальный запрос?

Рассмотрим фрагмент кода из обсуждения, который не работал:


СГРУППИРОВАТЬ ПО 
    ТоварыНаСкладахОстаткиИОбороты.Номенклатура, 
    ТИПЗНАЧЕНИЯ(ТоварыНаСкладахОбороты.Регистратор), 
    ТоварыНаСкладахОстаткиИОбороты.КоличествоНачальныйОстаток

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