Сложные запросы с различными операторами

Агрегатные функции без GROUP BY

Начнём с функции COUNT, которая подсчитывает количество строк. Она уже встречалась ранее, поэтому разберём сразу практический пример. Из таблицы покупателей DimCustomer получим количество клиентов с семейным положением MaritalStatus = 'M' (состоят в браке). Запрос возвращает 10 011 человек из 18 484. Если вместо столбца MaritalStatus указать первичный ключ CustomerKey, результат будет тем же — в каждой строке ровно одно значение статуса, поэтому число строк не изменится.

Перейдём к SUM. Отфильтровав женатых клиентов, посчитаем общее количество детей и общее число автомобилей во владении. Получаем 20 827 детей и 15 211 автомобилей. Добавим столбец со средним годовым доходом — функцию AVG (среднее арифметическое). Поскольку ни одного неагрегированного столбца не возвращается, группировка (GROUP BY) не нужна.

Далее — сумма продаж с округлением до двух знаков после запятой. Данные берутся из FactResellerSales (продажи в розничных точках). С помощью INNER JOIN соединяемся с таблицей магазинов DimReseller, а через неё — с географическим справочником DimGeography, чтобы отобрать только Германию. Период задаём условием BETWEEN по ключу даты OrderDateKey, который имеет формат ГГГГММДД (например, 20130101). Анализируем продажи за 2013 год.

Аналогично вычисляем сумму интернет-продаж в Соединённом Королевстве за 2013 год, используя таблицу FactInternetSales.

Функция AVG позволяет найти среднюю цену за единицу товара. Для подкатегории 3 (городские велосипеды, Touring Bikes) получаем среднюю цену продажи через интернет-магазин с точностью до двух знаков.
Далее строим запрос, который возвращает товары с ценой ниже средней по выбранным подкатегориям (1, 2 или 3). В подзапросе вычисляем среднюю цену по тем же подкатегориям. Результат сортируем по убыванию цены.

Функции MIN, MAX и DISTINCT

Рассмотрим MIN и MAX. Для подкатегории 2 (Mountain Bikes) вычисляем минимальную цену, максимальную цену и среднюю цену. Но товары могут различаться размером (значения 40, 42, 44 и т.д.) или цветом, однако цена иногда остаётся одинаковой. Чтобы средняя учитывала каждую уникальную цену ровно один раз, применяем DISTINCT внутри агрегатной функции: AVG(DISTINCT ListPrice). Без DISTINCT повторяющиеся цены повлияли бы на итог. Также подсчитываем количество товаров в подкатегории через COUNT(ProductKey).

Подзапросы с агрегатными функциями

Теперь объединяем агрегаты и подзапросы. Первый пример — поиск товаров с минимальной исторической ценой в своей группе. Берём таблицу DimProduct дважды: основная таблица P1 и её копия P2. Из P1 возвращаем ключ, английское название и цену. В условии WHERE указываем, что цена P1.ListPrice должна равняться минимальной цене из P2, где совпадают альтернативные ключи AlternateKey. Это коррелированный подзапрос: он выполняется для каждой строки P1 и находит минимум среди всех строк с тем же AlternateKey. Таким образом выбирается самая низкая цена, которая когда-либо была у каждого товара (цена может меняться со временем).

Второй подзапрос — максимальная маржа (разница между ценой продажи ListPrice и дилерской ценой DealerPrice). Из P1 выбираем ключ, название, цену продажи, дилерскую цену и вычисляем PriceDifference = ListPrice – DealerPrice. В WHERE задаём условие: разница должна быть равна максимальной разнице в истории (снова коррелированный подзапрос по тому же продукту). Результат — строка с максимальной маржой для каждого продукта.

Последний запрос демонстрирует сравнение сумм продаж за два года с помощью подзапросов в списке SELECT. Первое поле — сумма продаж из FactInternetSales за 2013 год (это подзапрос, возвращающий скаляр). Второе поле — сумма продаж за 2014 год (ещё один подзапрос). Третье поле — разница между ними. Важно: в подзапросах используется функция SUM, а не MAX, как могло показаться из описания. Логика построения запроса остаётся прежней: мы получаем константные значения для разных периодов и выполняем арифметическую операцию над ними.

Переход к GROUP BY

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

Краткие итоги

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

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

По-настоящему гибким инструментарий становится при включении подзапросов. Скалярный подзапрос в условии WHERE позволяет динамически сравнивать каждую строку с агрегированным показателем — скажем, отбирать товары дешевле средней цены по категории. Коррелированный подзапрос, опирающийся на связь через альтернативный ключ, решает задачу выбора экстремальных значений внутри групп: минимальной исторической цены товара или максимальной маржи. Такой подход имитирует функциональность оконных функций и группировки, оставаясь при этом в рамках классического SQL.

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

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