Краткий ответ

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

Исторический контекст

GROUP BY и агрегатные функции решают задачу свёртки множества строк в группы и итоговые значения. Однако аналитические отчёты часто требуют нескольких уровней свёртки: сначала получить показатель по подразделениям, затем вычислить показатель по этим подразделениям.

Для разделения таких этапов в SQL используются отдельные query block — вложенные запросы, CTE или представления. Это делает границу между исходными строками и промежуточным набором данных явной.

Постановка проблемы

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

Если попытаться выразить это как вложенный агрегат на одном уровне, СУБД не сможет однозначно применить внутренний агрегат к каждой группе, а внешний — к получившемуся набору групп. Ошибка или невозможность такой записи предотвращает смешение двух разных уровней агрегации.

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

Подробное решение

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

WITH regional AS (
    SELECT region, SUM(amount) AS region_total
    FROM sales
    GROUP BY region
)
SELECT AVG(region_total) AS average_region_total
FROM regional;

Внутри CTE выполняется первая агрегация, а во внешнем запросе — вторая. Если региональные суммы равны 100 и 300, результат равен 200 независимо от количества исходных продаж в каждом регионе.

Альтернативой может быть оконная функция над уже сгруппированными строками, если нужно сохранить каждую региональную строку и одновременно показать общий показатель. Но оконная функция не заменяет первый этап группировки: она работает с результатом конкретного query block.

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

Ситуация из практики

В отчёте руководителю требовалось сравнить среднюю месячную выручку филиалов. В исходной таблице одна строка соответствовала операции, поэтому прямое среднее по таблице показывало среднюю операцию, а не среднюю выручку филиала.

Рассматривались два варианта. Можно было сразу посчитать среднее по операциям — это просто и быстро, но метрика не соответствовала задаче. Можно было сначала сгруппировать операции по филиалам, а затем усреднить полученные суммы — это требует дополнительного query block, зато точно сохраняет смысл показателя.

Выбрали второй вариант через CTE. Он дал одну сумму на филиал и затем среднее этих сумм; результат стал сопоставимым между филиалами независимо от числа операций. При необходимости филиалы без операций включают отдельным справочником и явно определяют, должны ли они участвовать в среднем.

Что кандидаты часто упускают

Всегда ли вложенный запрос нужен для последовательной агрегации?

Да, если второй расчёт должен получать результат первого агрегирования как набор строк. Вместо CTE можно использовать производную таблицу или представление; принципиально важно наличие отдельного уровня запроса. Исключение по смыслу — когда задача выражается другой операцией, например одной формулой через SUM и COUNT, без промежуточных групп.

Чем отличается среднее средних от общего среднего?

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

Можно ли после группировки применить оконный агрегат к агрегированным значениям?

Да. Оконная функция в следующем логическом уровне может работать со строками, уже полученными после GROUP BY. Например, после получения суммы по регионам можно вычислить сумму этих региональных сумм без схлопывания строк. Это отличается от вложенного агрегата на одном уровне: оконная функция выполняется над результатом запроса, а не пытается вложить два обычных агрегата в один этап.