← вся теориятеория к собесу · Язык запросов 1С

Группировки, ИТОГИ и ОБЪЕДИНИТЬ в запросах 1С

разбор писал Даниил, автор Сеньорчика

Зачем это спрашивают

Группировки, ИТОГИ и ОБЪЕДИНИТЬ спрашивают задачами: посчитать приход и расход в две колонки, найти дубли, склеить два источника. Ловушка с ОБЪЕДИНИТЬ без ВСЕ теряет деньги в отчёте молча. Результаты ниже сверены на PostgreSQL.

// Так спрашивают: «Чем ОБЪЕДИНИТЬ отличается от ОБЪЕДИНИТЬ ВСЕ?» «Как вывести приход и расход в две колонки?» «Куда писать условие на агрегат?» «Что делает ИТОГИ ПО ... ИЕРАРХИЯ?»

ОБЪЕДИНИТЬ и ОБЪЕДИНИТЬ ВСЕ

ОБЪЕДИНИТЬ склеивает результаты запросов и убирает одинаковые строки, ОБЪЕДИНИТЬ ВСЕ сохраняет все строки и работает быстрее, потому что не сравнивает. Имена полей берутся из первой части. Склеим выручку из двух источников, где у молока две одинаковые строки по 500:

Две продажи молока на 500 из разных магазинов совпали по всем полям и слились в одну. Сумма отчёта потеряла 500 и ничего не сообщила. Источники для сложения склеивают ОБЪЕДИНИТЬ ВСЕ.

ВЫБРАТЬ "молоко" КАК Товар, 500 КАК Сумма
ОБЪЕДИНИТЬ
ВЫБРАТЬ "молоко", 500
ОБЪЕДИНИТЬ
ВЫБРАТЬ "хлеб", 300
УПОРЯДОЧИТЬ ПО
    Товар

Группировка и ИМЕЮЩИЕ

Поле выборки при группировке либо стоит в СГРУППИРОВАТЬ ПО, либо обёрнуто агрегатом. Условие на агрегат пишут в ИМЕЮЩИЕ: ГДЕ отбирает строки до группировки, ИМЕЮЩИЕ отбирает группы после. Приход и расход в две колонки делают условной агрегацией за один проход:

КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) считает уникальные значения, КОЛИЧЕСТВО(Поле) непустые, а КОЛИЧЕСТВО(*) все строки. Дубли справочника находят группировкой по наименованию с ИМЕЮЩИЕ КОЛИЧЕСТВО(*) > 1. Группировка по ссылке и по наименованию различается: два разных товара «Молоко» склеятся по наименованию и не склеятся по ссылке.

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

ИТОГИ

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

ИТОГИ удобны для отчёта-дерева в коде. В отчётах для пользователя иерархию и итоги лучше отдать системе компоновки данных (СКД), она построит их сама и даст пользователю менять группировки.

Как отвечать: «Почему отчёт по выручке из двух источников показывает меньше, чем сумма источников?»

Похоже, источники склеены ОБЪЕДИНИТЬ без ВСЕ. Он убирает одинаковые строки, и две продажи с одинаковым товаром и суммой из разных магазинов слились в одну. Для сложения нужен ОБЪЕДИНИТЬ ВСЕ, он ещё и быстрее, потому что не сравнивает строки. Если дубли действительно надо убрать, это решают явно, группировкой по ключу.

Ответ находит причину по симптому и объясняет механизм. Замечание про скорость показывает, что ОБЪЕДИНИТЬ ВСЕ годится по умолчанию.

На чём валят

  • −Складывают источники через ОБЪЕДИНИТЬ. Совпавшие строки склеиваются, сумма теряет слагаемое. Берут ОБЪЕДИНИТЬ ВСЕ.
  • −Пишут агрегат в ГДЕ. Условие на агрегат допустимо только в ИМЕЮЩИЕ.
  • −Группируют по наименованию, когда нужна группировка по ссылке. Разные объекты с одним именем склеиваются. Группируют по ссылке.
  • −Считают приход и расход двумя запросами. Хватает одного прохода с СУММА(ВЫБОР … КОНЕЦ).

Проверь себя

Пять вопросов из банка по этой подтеме. Всего их 12, остальные разбираются в тренажёре.

  1. #oc_q_grouping1 / 5
    Как отобрать товары, у которых сумма продаж больше 100 000?
    A)ИТОГИ ПО Сумма > 100000
    B)ГДЕ СУММА(П.Сумма) > 100000
    C)ГДЕ П.Сумма > 100000
    D)ИМЕЮЩИЕ СУММА(П.Сумма) > 100000
    показать ответ и разбор
    +D)`ИМЕЮЩИЕ СУММА(П.Сумма) > 100000`

    // разбор: ГДЕ отбирает строки до группировки и агрегаты в нём недопустимы. Условие на агрегат пишут в ИМЕЮЩИЕ, после СГРУППИРОВАТЬ ПО. Условие ГДЕ П.Сумма > 100000 отберёт отдельные крупные продажи, а не товары с большой суммарной выручкой.

  2. #oc_q_grouping2 / 5
    Как посчитать число разных покупателей товара?
    A)СУММА(1)
    B)КОЛИЧЕСТВО(П.Покупатель)
    C)КОЛИЧЕСТВО(РАЗЛИЧНЫЕ П.Покупатель)
    D)РАЗЛИЧНЫЕ КОЛИЧЕСТВО(*)
    показать ответ и разбор
    +C)`КОЛИЧЕСТВО(РАЗЛИЧНЫЕ П.Покупатель)`

    // разбор: КОЛИЧЕСТВО(Поле) считает строки с непустым полем, повторы входят. КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) считает уникальные значения. СУММА(1) — число строк. Последний вариант синтаксически неверен.

  3. #oc_q_grouping3 / 5
    В объединении два запроса: в первом поле названо Сумма, во втором Итого. Как будет называться колонка результата?
    A)Обе колонки останутся рядом
    B)Итого — имена берутся из последнего запроса
    C)Сумма, из первой части
    D)Ошибка: имена должны совпадать
    показать ответ и разбор
    +C)`Сумма`, из первой части

    // разбор: В объединении поля сопоставляются по позиции, а имена и псевдонимы берутся из первого запроса. Число полей во всех частях должно совпадать. Если типы полей в частях разные, итоговое поле получит составной тип.

  4. #oc_q_grouping4 / 5
    Что даёт ИТОГИ СУММА(Сумма) ПО ОБЩИЕ, Контрагент в запросе?
    A)Только одну строку общего итога по запросу
    B)Итоговые строки над детальными
    C)Группировку без детальных записей
    D)Сортировку результата по контрагенту
    показать ответ и разбор
    +B)Итоговые строки над детальными

    // разбор: ИТОГИ добавляет в результат строки итогов: общий итог (ОБЩИЕ) и итоги по каждому контрагенту, а под ними детальные записи. Обходят такой результат по группировкам: Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам). Если нужны только суммы без деталей, проще СГРУППИРОВАТЬ ПО.

  5. #oc_q_grouping5 / 5
    Как одним запросом получить приход и расход по товару в двух колонках из таблицы движений?
    A)Условная агрегация: СУММА(ВЫБОР ...)
    B)Два запроса через ОБЪЕДИНИТЬ без группировки
    C)СГРУППИРОВАТЬ ПО ВидДвижения и транспонировать в коде
    D)ИТОГИ ПО ВидДвижения с двумя ресурсами
    показать ответ и разбор
    +A)Условная агрегация: `СУММА(ВЫБОР ...)`

    // разбор: Условная агрегация: внутри СУММА выражение ВЫБОР возвращает количество для нужного вида движения и ноль для остальных. Так строки разворачиваются в колонки за один проход. Для регистров остатков то же самое уже даёт виртуальная таблица Обороты с полями Приход и Расход.

дальше

Теорию прочитал. Навык ставится повторением

В Сеньорчике эта подтема идёт в ежедневных сессиях: движок возвращает её, пока ответы не станут уверенными, и ведёт прогресс отдельно по каждой подтеме. Теория внутри тоже бесплатна, бесплатная часть практики — одна сессия дня в сутки.

Начать бесплатноВся тема: Язык запросов 1С
читать дальше