Свёртываем данные в запросе 1С правильно и быстро

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

Когда разрезов несколько или данные надо подготовить перед соединением, свёртку разбивают на шаги через временные таблицы (ПОМЕСТИТЬ). На больших объёмах скорость решают три вещи: фильтр до группировки, виртуальные таблицы регистров и индексы на временных таблицах.

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

Что такое свёртка и почему её делают запросом, а не циклом

Свёртка — это агрегация: строки с одинаковыми значениями измерений объединяются в одну, а числовые поля при этом суммируются, усредняются или по ним берётся максимум. Из тысячи строк регистра продаж получается, скажем, 40 строк «по номенклатуре».

Тот же результат можно собрать циклом: выбрать все движения и складывать суммы в Соответствие по коду номенклатуры. Так делать не надо.

Правило простое: всё, что можно сложить, фильтровать и соединить, делаем в запросе. В код отдаём уже свёрнутый результат.

Наглядно это выглядит так. Было пять строк движений:

После свёртки по номенклатуре осталось две:

Проверим арифметику: болт — 300 + 200 + 500 = 1000, гайка — 150 + 150 = 300. Три документа по болту схлопнулись в одну строку. Именно это и делает СГРУППИРОВАТЬ ПО.

Группировка через СГРУППИРОВАТЬ ПО

Базовая конструкция: в выборке перечисляем поля-измерения и агрегаты, а измерения дублируем в СГРУППИРОВАТЬ ПО.

ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(Продажи.Количество) КАК Количество,
    СУММА(Продажи.Сумма) КАК Сумма
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура

Здесь каждая номенклатура даст одну строку с общими количеством и суммой, сколько бы документов её ни двигали.

Какие агрегатные функции есть

• 🧮 СУММА(Поле) — сложение числовых значений.
• 🔢 КОЛИЧЕСТВО(Поле) — число строк, где поле не NULL.
• 🔢 КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) — число уникальных значений (например, сколько разных документов).
• ⬆️ МАКСИМУМ(Поле) и ⬇️ МИНИМУМ(Поле) — крайние значения, работают и с датами.
• 📊 СРЕДНЕЕ(Поле) — среднее по ненулевым строкам.

КОЛИЧЕСТВО(*) считает все строки группы, включая те, где поле пустое, — это отличает его от КОЛИЧЕСТВО(Поле).

Правило: каждое неагрегированное поле — в группировку

Главное правило свёртки: любое поле выборки либо обёрнуто в агрегатную функцию, либо стоит в СГРУППИРОВАТЬ ПО. Нарушите — запрос не выполнится.

Отсюда практический вывод: не тащите в выборку лишние поля. Добавили в список Продажи.Документ «на всякий случай» — придётся добавить его и в группировку, и строки перестанут схлопываться по номенклатуре. Разрез измельчится, суммы «размажутся» по документам.

Фильтр по итогу: ИМЕЮЩИЕ, а не ГДЕ

Отобрать строки по значению агрегата (например, оставить номенклатуру с выручкой больше 100 000) через ГДЕ нельзя — на момент отбора агрегат ещё не посчитан. Для этого есть ИМЕЮЩИЕ:

ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(Продажи.Сумма) КАК Сумма
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура
ИМЕЮЩИЕ
    СУММА(Продажи.Сумма) > 100000

Разделение чёткое: ГДЕ отсекает строки до свёртки (по значениям самих движений), ИМЕЮЩИЕ — группы после свёртки (по значениям агрегатов).

Многоступенчатая свёртка через временные таблицы

Когда свёртку нужно сделать в несколько приёмов — сначала собрать по контрагенту и номенклатуре, потом только по контрагенту — применяют пакетный запрос с временной таблицей. Запросы в пакете разделяются точкой с запятой, а оператор ПОМЕСТИТЬ ИмяТаблицы сохраняет промежуточный результат.

ВЫБРАТЬ
    Продажи.Контрагент КАК Контрагент,
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(Продажи.Сумма) КАК Сумма
ПОМЕСТИТЬ ВтПродажи
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Контрагент,
    Продажи.Номенклатура
ИНДЕКСИРОВАТЬ ПО
    Контрагент
;
ВЫБРАТЬ
    ВтПродажи.Контрагент КАК Контрагент,
    СУММА(ВтПродажи.Сумма) КАК Сумма
ИЗ
    ВтПродажи КАК ВтПродажи
СГРУППИРОВАТЬ ПО
    ВтПродажи.Контрагент

Зачем так, если можно сразу сгруппировать по контрагенту? Ради соединений. Когда к свёрнутым данным нужно присоединить другую таблицу (цены, остатки, справочные поля), присоединяют именно к маленькой временной таблице, а не к миллионам движений.

Вот как это работает на практике, когда надо сопоставить продажи и возвраты по номенклатуре. Свернём каждую таблицу по отдельности, а затем соединим уже готовые итоги:

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

Если бы мы соединили исходные таблицы движений до свёртки, одна строка продаж совпала бы с несколькими строками возвратов (и наоборот) — суммы бы задвоились. Свёртка до соединения снимает эту проблему: с каждой стороны по одной строке на номенклатуру. ЕСТЬNULL подставляет ноль там, где возвратов не было.

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

Как ускорить свёртку на больших объёмах

На таблице в сотни тысяч строк разница между «правильным» и «наивным» запросом — секунды против минут. Собрал чек-лист приёмов, которые дают основной выигрыш.

Отдельно про виртуальные таблицы. У регистров накопления и сведений есть готовые таблицы Обороты, Остатки, ОстаткиИОбороты. Они уже выполняют свёртку движений, причём на стороне СУБД, и принимают параметры — период, периодичность, отбор:

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

Отбор и период передают в параметры виртуальной таблицы, а не в ГДЕ снаружи. Так СУБД отсекает лишнее до расчёта оборотов, а не после — это и есть «фильтр до группировки» на практике.

У таблицы Обороты есть ещё один параметр — периодичность. Он сворачивает обороты не только по измерениям, но и по отрезкам времени: день, неделя, месяц, квартал, год. Для отчёта «продажи по номенклатуре помесячно» не нужно ни НАЧАЛОПЕРИОДА, ни отдельная группировка — достаточно задать периодичность:

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

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

Свёртка с условиями и вычисляемыми полями

Часто надо сложить не всё подряд, а по условию: отдельно положительные суммы, отдельно отменённые. Условие прячут внутрь агрегата через конструкцию ВЫБОР … КОГДА … ТОГДА … ИНАЧЕ … КОНЕЦ:

ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(ВЫБОР
            КОГДА Продажи.Сумма > 0
                ТОГДА Продажи.Сумма
            ИНАЧЕ 0
        КОНЕЦ) КАК СуммаПоложительная,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Продажи.Регистратор) КАК ЧислоДокументов
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура

Так в одной свёртке получаются сразу несколько показателей по разным условиям — не плодя подзапросы. Этот приём закрывает большинство задач вида «посчитай отдельно это, отдельно то в разрезе одного и того же измерения»: продажи и возвраты, оплаченное и неоплаченное, в срок и с просрочкой. Каждый показатель — своя СУММА(ВЫБОР …) в одном списке выборки.

Единственное, за чем стоит следить: условие внутри ВЫБОР должно опираться только на поля исходной таблицы, а не на уже посчитанные агрегаты. Фильтр по итогам — это по-прежнему ИМЕЮЩИЕ, а не ВЫБОР внутри СУММА.

Группировать можно и по вычисляемому полю: месяцу из даты, первой букве наименования, результату ВЫБОР. Правило то же — выражение повторяется в СГРУППИРОВАТЬ ПО целиком:

ВЫБРАТЬ
    НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ) КАК Месяц,
    СУММА(Продажи.Сумма) КАК Сумма
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    НАЧАЛОПЕРИОДА(Продажи.Период, МЕСЯЦ)

Какой способ свёртки выбрать

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

Про ВЫБРАТЬ РАЗЛИЧНЫЕ стоит сказать отдельно: это тоже свёртка, но «лёгкая» — она просто убирает полные дубли строк, ничего не складывая. Если задача — получить список уникальных контрагентов, а не их обороты, РАЗЛИЧНЫЕ короче и понятнее, чем группировка без агрегатов.

Частые ошибки при свёртке

Большинство проблем со свёрткой — это либо отказ выполнять запрос, либо «тихо» неверные суммы. Вторые опаснее: запрос отрабатывает, а цифры кривые. Основные случаи и что с ними делать:

Частые вопросы