Группировка и агрегатные функции

Учимся сворачивать множество строк в сводку: сколько всего продано, на какую сумму, по сколько штук в среднем — и фильтровать уже сами итоги.

Группировка — объединение строк с одинаковыми значениями выбранных полей в одну строку, где остальные поля свёрнуты агрегатной функцией (суммой, количеством и т. п.).

Документ продаж — это набор строк: одна строка на каждую позицию в каждом чеке. Но в отчёте нам нужна сводка: «по каждому товару — сколько всего продано и на какую сумму». То есть тысячи строк документа надо свернуть до нескольких — по одной на товар. Именно это делает СГРУППИРОВАТЬ ПО.

Первый запрос с группировкой

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

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

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

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

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

Чем больше измерений в группировке, тем детальнее (и длиннее) результат: в пределе, если добавить в группировку все поля, свёртка вообще ничего не свернёт. Поэтому в СГРУППИРОВАТЬ ПО держат только те разрезы, которые действительно нужны в отчёте, — лишние измерения раздувают выборку и замедляют запрос.

Агрегатные функции

Агрегатная функция получает группу строк и возвращает по ней одно число. Основных пять:

Функция	Что считает
`СУММА(поле)`	сумму значений в группе
`КОЛИЧЕСТВО(поле)`	сколько строк в группе (не считая NULL)
`МАКСИМУМ(поле)`	наибольшее значение
`МИНИМУМ(поле)`	наименьшее значение
`СРЕДНЕЕ(поле)`	среднее арифметическое

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

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

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

ИМЕЮЩИЕ — условие на агрегаты

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

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

Порядок такой: сначала ГДЕ отсекает ненужные строки, потом строки сворачиваются в группы, и только затем ИМЕЮЩИЕ отбрасывает группы, чей итог не прошёл условие. Разница на практике: ГДЕ Продажи.Сумма > 100000 оставит только крупные строки и уже из них посчитает оборот; ИМЕЮЩИЕ СУММА(...) > 100000 посчитает оборот из всех строк и оставит контрагентов, у кого он крупный. Это два разных отчёта.

Условия можно и комбинировать: обычные — в ГДЕ, агрегатные — в ИМЕЮЩИЕ.

ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    СУММА(Продажи.Количество) КАК ВсегоШтук
ИЗ
    Документ.РеализацияТоваров.Товары КАК Продажи
ГДЕ
    Продажи.Дата МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура
ИМЕЮЩИЕ
    СУММА(Продажи.Количество) > 10

ГДЕ оставит продажи только за период, а ИМЕЮЩИЕ — только товары, которых за этот период ушло больше 10 штук. Про параметры вроде &НачалоПериода подробно рассказано в разделе «Основы».

Как это работает

При переводе в SQL СГРУППИРОВАТЬ ПО становится GROUP BY, ИМЕЮЩИЕ — HAVING, а функции — SUM, COUNT, MAX, MIN, AVG. СУБД сортирует строки по полям группировки, режет их на группы с одинаковыми значениями и по каждой группе вычисляет агрегаты — за один проход, поэтому это очень быстро даже на миллионах строк.

Полезно помнить порядок, в котором СУБД обрабатывает запрос: сначала соединения и ГДЕ (отбор исходных строк), затем группировка и вычисление агрегатов, потом ИМЕЮЩИЕ (отбор групп) и лишь в самом конце сортировка УПОРЯДОЧИТЬ ПО. Из этого порядка и вытекает, почему условие на сумму нельзя писать в ГДЕ: до шага группировки суммы ещё физически нет.

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

Частые ошибки

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

// ОШИБКА: Контрагент не в группировке и не под агрегатом
ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    Продажи.Контрагент КАК Контрагент,
    СУММА(Продажи.Сумма) КАК Оборот
ИЗ
    Документ.РеализацияТоваров.Товары КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура

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

Условие на агрегат положили в ГДЕ. Написать ГДЕ СУММА(Продажи.Сумма) > 100000 нельзя: в момент отработки ГДЕ строки ещё не свёрнуты и суммы не существует. Такие условия — только в ИМЕЮЩИЕ.

Путают КОЛИЧЕСТВО(поле) и КОЛИЧЕСТВО(РАЗЛИЧНЫЕ поле). Первое считает строки, второе — уникальные значения. «Сколько было продаж» и «скольким разным клиентам продали» — разные числа, а перепутать их легко.

Ждут ноль, получают NULL. Если после левого соединения агрегируете поле, где для части групп одни NULL, итог по такой группе будет NULL. Хотите гарантированный ноль — ЕСТЬNULL(СУММА(Поле), 0).

Итоги

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