Перейти к содержимому

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

Курс MS SQL с нуля · Урок 5 из 12 · Часть 2. Соединения и агрегация

Агрегация превращает тысячи строк в управляемый отчёт. Урок даёт GROUP BY, пять базовых агрегатов, отличие HAVING от WHERE и логический порядок выполнения оператора SELECT — опору всей дальнейшей диагностики.

Перед уроком база обязана быть чистой: если проходили уроки изменения данных, восстановите TechStore скриптами урока 2.

Содержание

1. Что вы освоите

К концу урока вы:

  • называете шесть шагов логического выполнения SELECT по порядку;
  • пишете GROUP BY с корректными столбцами в SELECT;
  • отсеиваете группы в HAVING и отличаете его от WHERE;
  • предсказываете поведение COUNT и AVG при NULL в столбце;
  • читаете Msg 8120 и устраняете его двумя способами осознанно.
↑ К оглавлению

2. Логический порядок SELECT

Сервер читает оператор не так, как он написан. Логический порядок:

ШагПредложениеЧто делает
1FROMоткуда данные, включая соединения
2WHEREотсев строк до группировки
3GROUP BYсшивание оставшихся строк в группы
4HAVINGотсев готовых групп
5SELECTвычисление столбцов результата
6ORDER BYсортировка результата
Два строгих следствия: столбец в SELECT либо входит в GROUP BY, либо завёрнут в агрегат — иначе Msg 8120. WHERE выполняется до группировки и агрегатов не видит: условия на суммы и счётчики пишутся в HAVING.
↑ К оглавлению

3. Пять агрегатов и их подвохи

Пять базовых агрегатов и их подвохи:

Чистый код

SELECT COUNT(*)         AS rows_cnt,
       COUNT(Segment)   AS filled_cnt,
       COUNT(DISTINCT o.CustomerID) AS clients_cnt,
       SUM(o.TotalAmount) AS revenue,
       AVG(o.TotalAmount) AS avg_check
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate);
SQL
  • Построчный разбор

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

    SELECT COUNT(*)         AS rows_cnt,
-- → строк в группе: звёздочка считает и NULL-значения
       COUNT(Segment)   AS filled_cnt,
-- → не-NULL значений столбца: пустые пропускаются
       COUNT(DISTINCT o.CustomerID) AS clients_cnt,
-- → сколько разных клиентов попало в группу
       SUM(o.TotalAmount) AS revenue,
       AVG(o.TotalAmount) AS avg_check
-- → оборот и средний чек; NULL в сумму и среднее не входит
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate);
-- → одна строка результата на каждый год даты заказа
    SQL
  • COUNT(*) считает строки, а COUNT(столбец) — заполненные значения: разница этих чисел на столбце с NULL — готовая диагностика;
  • AVG игнорирует NULL: среднее за период с пропусками «искажается» само собой; в отчётах пропуски заменяют нулём до агрегата, если это соответствует смыслу;
  • деление целых отсекает дробную часть (5 пополам даст 2): привычка — приводить делимое к DECIMAL множителем 100.0 или CAST;
  • GROUP BY считает все NULL одной группой: «без значения» — полноправная строка результата.
Деление без приведения: COUNT(*) / 2 всегда целое: дробная часть отсекается бесшумно, и «доля» в отчёте превращается в ноль. Приведение типа делается до деления — CAST к DECIMAL или множитель 100.0.

HAVING против WHERE

Оба отсекают, но на разных шагах: WHERE — строки до группировки, HAVING — группы после. Дешевле отсекать рано: фильтр, который можно выразить строками, пишется в WHERE:

Чистый код

SELECT c.City, COUNT(*) AS orders_cnt, AVG(o.TotalAmount) AS avg_check
FROM dbo.Orders    AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE NOT (o.Status = N'Cancelled')
GROUP BY c.City
HAVING COUNT(*) BETWEEN 100 AND 999999999;
SQL
  • Построчный разбор

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

    SELECT c.City, COUNT(*) AS orders_cnt, AVG(o.TotalAmount) AS avg_check
-- → отчёт: город, число заказов, средний чек
FROM dbo.Orders    AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE NOT (o.Status = N'Cancelled')
-- → строки отсекаются до группировки: рано и дёшево
GROUP BY c.City
HAVING COUNT(*) BETWEEN 100 AND 999999999;
-- → условие на агрегат допустимо только здесь, уже на готовых группах
    SQL
↑ К оглавлению

4. Задачи и эталонные решения

Шесть задач на контексте TechStore.

  1. число заказов в каждом статусе, статусы по убыванию числа; сверка: Cancelled примерно пять процентов, сумма по статусам — ровно 25000;
  2. выручка и число заказов по месяцам за весь период, свежие сначала; ключ группы — год и месяц вместе;
  3. средний чек и число заказов по городам, только города от ста заказов — отсев именно в HAVING;
  4. десятка клиентов по сумме неотменённых заказов с именем через соединение;
  5. категории без единственного активного товара — LEFT JOIN и HAVING с счётчиком, равным нулю;
  6. COUNT(*) против COUNT(Email) по клиентам и AVG «как есть» против AVG с заменой NULL нулём на учебном ряде — с письменным объяснением разниц.

Эталоны урока

открывать после самостоятельной попытки

Чистый код

SELECT o.Status, COUNT(*) AS orders_cnt
FROM dbo.Orders AS o
GROUP BY o.Status
ORDER BY orders_cnt DESC;
GO

SELECT YEAR(o.OrderDate) AS order_year, MONTH(o.OrderDate) AS order_month,
       COUNT(*) AS orders_cnt, SUM(o.TotalAmount) AS revenue
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY order_year DESC, order_month DESC;
GO

SELECT TOP (10) c.CustomerID, c.FullName, SUM(o.TotalAmount) AS total_spent
FROM dbo.Orders    AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE NOT (o.Status = N'Cancelled')
GROUP BY c.CustomerID, c.FullName
ORDER BY total_spent DESC;
GO

SELECT cat.CategoryID, cat.Name
FROM dbo.Categories AS cat
LEFT JOIN dbo.Products AS p
       ON p.CategoryID = cat.CategoryID AND p.IsActive = 1
GROUP BY cat.CategoryID, cat.Name
HAVING COUNT(p.ProductID) = 0;
GO
решения задач 5.1, 5.2, 5.4, 5.5
  • Построчный разбор

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

    SELECT o.Status, COUNT(*) AS orders_cnt
-- → задача 5.1: распределение заказов по статусам
FROM dbo.Orders AS o
GROUP BY o.Status
ORDER BY orders_cnt DESC;
-- → сортировка по алиасу вычисленной колонки
GO

SELECT YEAR(o.OrderDate) AS order_year, MONTH(o.OrderDate) AS order_month,
       COUNT(*) AS orders_cnt, SUM(o.TotalAmount) AS revenue
-- → задача 5.2: динамика по годам и месяцам, обе части даты в ключе
FROM dbo.Orders AS o
GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY order_year DESC, order_month DESC;
-- → новые периоды вверху
GO

SELECT TOP (10) c.CustomerID, c.FullName, SUM(o.TotalAmount) AS total_spent
-- → задача 5.4: десятка клиентов по сумме покупок
FROM dbo.Orders    AS o
JOIN dbo.Customers AS c ON c.CustomerID = o.CustomerID
WHERE NOT (o.Status = N'Cancelled')
-- → отменённые заказы в траты не входят: отсев до группировки
GROUP BY c.CustomerID, c.FullName
-- → имя в ключе группы: иначе Msg 8120
ORDER BY total_spent DESC;
GO

SELECT cat.CategoryID, cat.Name
-- → задача 5.5: пустые категории без активных товаров
FROM dbo.Categories AS cat
LEFT JOIN dbo.Products AS p
       ON p.CategoryID = cat.CategoryID AND p.IsActive = 1
-- → фильтр активности в ON: перевести его в WHERE — левость погибнет
GROUP BY cat.CategoryID, cat.Name
HAVING COUNT(p.ProductID) = 0;
-- → после LEFT пустота правой стороны даёт ноль строк
GO
    решения задач 5.1, 5.2, 5.4, 5.5
↑ К оглавлению

5. Типичные ошибки урока

  1. Msg 8120 «столбец ни в агрегате, ни в GROUP BY» — лечится одним из двух: убрать столбец или дописать его в ключ группировки; выбор — по смыслу отчёта.
  2. WHERE на агрегате — условие по COUNT или SUM принадлежит HAVING: WHERE выполняется раньше групп.
  3. Отсев после вместо до — фильтр строки, написанный в HAVING, заставляет группировать лишнее; строки отсеивают в WHERE.
  4. Целочисленная доля — 100 пополам целочисленно это 50, но COUNT пополам — ноль; приведение типа до деления.
  5. Сортировка по старому имени — сортируется результат, поэтому ORDER BY пишется по алиасу вычисленной колонки.
  6. Забытая NULL-группа — группа с NULL в ключе существует; в отчётах её подписывают явно через COALESCE (урок 9), а не теряют.
↑ К оглавлению

6. Чек-лист практики и источники

Чек-лист урока 5

отметки делаются по факту

Источники для углубления:

  1. GROUP BY: синтаксис и примеры;
  2. HAVING: отсев групп;
  3. Агрегатные функции: список и семантика NULL.
Итог: тысячи строк превращаются в отчётные строки управляемо. Урок 6 — подзапросы и множества: скаляр, список, производная таблица, EXISTS и UNION.

Проверено: 23.10.2026

↑ К оглавлению
Конспект:
Четверг, 22 октября 2026