ИТ.03 - 07 - Агрегатные функции, операторы GROUP BY и HAVING

Введение

На предыдущих занятиях вы освоили выборку и фильтрацию данных (SELECT, WHERE, LIKE, сортировку, ограничение выборки), а также модификаторы DISTINCT и работу с составными условиями. Сегодня — первый шаг к сводной аналитике по группам: агрегатные функции, группировка GROUP BY и постфильтрация групп HAVING.

Пример таблицы orders

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  title TEXT,
  category TEXT,
  customer TEXT,
  amount INTEGER
);

INSERT INTO orders (id, title, category, customer, amount) VALUES
  (1,  'Футболка BASIC',         'Одежда',      'ООО Альфа',     5),
  (2,  'носки хлопок',           'Одежда',      'ООО Бета',      6),
  (3,  'Шорты спортивные',       'Одежда',      'ИП Сокол',      10),
  (4,  'футболка Basic',         'Одежда',      'ООО Альфа',     10),
  (5,  'Шорты спортивные',       'Одежда',      'ЗАО Гелиос',    5),
  (6,  'Носки Хлопок',           'Одежда',      'ООО Бета',      6),
  (7,  'ФУТБОЛКА BASIC',         'Одежда',      'ООО Ромашка',   5),
  (8,  'Шорты спортивные',       'Одежда',      'ООО Вектор',    5),
  (9,  'Носки хлопок',           'Одежда',      'ИП Атлас',      3),
  (10, 'футболка BASIC',         'Одежда',      'ООО Альфа',     5),
  (11, 'Носки Хлопок',           'Одежда',      'ИП Атлас',      3),
  (12, 'Шорты спортивные',       'Одежда',      'ЗАО Гелиос',    10),
  (13, 'Футболка Basic',         'Одежда',      'ООО Альфа',     5),
  (14, 'Носки Хлопок',           'Одежда',      'ООО Бета',      12),
  (15, 'Шорты спортивные',       'Одежда',      'ООО Вектор',    5),
  (16, 'Кроссовки Trail',        'Обувь',       'ООО Бета',      2),
  (17, 'Кроссовки Trail',        'Обувь',       'ООО Альфа',     2),
  (18, 'Кроссовки Trail',        'Обувь',       'ИП Сокол',      15),
  (19, 'Кроссовки Trail',        'Обувь',       'ЗАО Гелиос',    30),
  (20, 'Кроссовки Trail',        'Обувь',       'ООО Ромашка',   1),
  (21, 'Джемпер шерстяной',      'Одежда',      'ООО Вектор',    7),
  (22, 'Джемпер шерстяной',      'Одежда',      'ООО Вектор',    7),
  (23, 'Джемпер шерстяной',      'Одежда',      'ООО Вектор',    7),
  (24, 'Шапка вязаная',          'Аксессуары',  'ООО Ромашка',   1),
  (25, 'Шапка вязаная',          'Аксессуары',  'ООО Ромашка',   1),
  (26, 'Шапка вязаная',          'Аксессуары',  'ИП Атлас',      1),
  (27, 'Футболка BASIC',         'Одежда',      'ООО Альфа',     20),
  (28, 'Футболка Basic',         'Одежда',      'ЗАО Гелиос',    8),
  (29, 'Носки хлопок',           'Одежда',      'ООО Бета',      1),
  (30, 'Шорты спортивные',       'Одежда',      'ИП Сокол',      12),
  (31, 'Кеды Canvas',            'Обувь',       'ООО Альфа',     4),
  (32, 'Кеды Canvas',            'Обувь',       'ООО Ромашка',   9),
  (33, 'Рюкзак городской',       'Аксессуары',  'ООО Альфа',     3),
  (34, 'Рюкзак городской',       'Аксессуары',  'ЗАО Гелиос',    6),
  (35, 'Бутылка для воды',       'Спорт',       'ИП Атлас',      12),
  (36, 'Фитнес-резинка',         'Спорт',       'ООО Вектор',    25),
  (37, 'Перчатки лыжные',        'Аксессуары',  'ООО Бета',      5),
  (38, 'перчатки ЛЫЖНЫЕ',        'Аксессуары',  'ООО Бета',      7),
  (39, 'Кроссовки Trail',        'Обувь',       'ООО Вектор',    11),
  (40, 'Кроссовки Trail',        'Обувь',       'ИП Атлас',      13);

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

Агрегатная функция — это функция, которая выполняет вычисление на наборе значений и возвращает одиночное значение.

В разных СУБД существует довольно много разных агрегатных функций, но мы рассмотрим основные:

Подсчёт количества значений

SELECT
  COUNT(*) AS total_orders
FROM orders;

Результат:
Сколько всего строк (заказов) в таблице.

Подсчёт суммы значений

SELECT
  SUM(amount) AS total_qty
FROM orders;

Результат:
Сколько единиц товара продано суммарно.

Подсчёт уникальных значений

SELECT
  COUNT(DISTINCT category) AS unique_categories
FROM orders;

Результат:
Сколько различных категорий товаров встречается в заказах.

Группировка GROUP BY

Задача группировки — собрать строки с одинаковым значением ключа в логические группы и посчитать по каждой группе агрегаты (итоги).

Ключевые правила:

• В SELECT после группировки допустимы только поля из списка GROUP BY и агрегатные выражения.
• Условия по всем строкам исходной таблицы пишутся в блоке WHERE (до группировки), условия по каждой сформированной группе — в блоке HAVING (после группировки).
• Порядок выполнения: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Простейшая группировка по товару

SELECT
  title,
  SUM(amount) AS total_qty
FROM orders
GROUP BY title
ORDER BY total_qty DESC;

Результат:
Сколько единиц каждого товара продано суммарно.

Группировка по покупателю

SELECT
  customer,
  SUM(amount) AS total_qty
FROM orders
GROUP BY customer
ORDER BY total_qty DESC;

Результат:
Сколько единиц заказал каждый покупатель.

Группировка по категории

SELECT
  category,
  SUM(amount) AS total_qty
FROM orders
GROUP BY category
ORDER BY total_qty DESC;

Результат:
Суммарные продажи по категориям.

Группировка по двум ключам (покупатель × категория)

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

SELECT
  customer,
  category,
  SUM(amount) AS total_qty
FROM orders
GROUP BY customer, category
ORDER BY customer ASC, total_qty DESC;

Результат:
Сколько покупатель взял по каждой категории

Заказы на каждый товар

SELECT
  title,
  COUNT(*) AS orders_count
FROM orders
GROUP BY title
ORDER BY orders_count DESC;

Результат:
Сколько заказов пришлось на каждый товар.

Средний размер заказа

SELECT
  category,
  AVG(amount) AS avg_per_category
FROM orders
GROUP BY category
ORDER BY avg_per_category DESC;

Результат:
Средний размер заказа по каждой категории

Группировка по выражению

Группировать можно по вычисляемому выражению, например по «нормализованному» названию:

SELECT
  LOWER(title) AS norm_title,
  SUM(amount)  AS total_qty
FROM orders
GROUP BY LOWER(title)
ORDER BY total_qty DESC;

Результат:
Объединены возможные варианты регистра в одно наименование (без учёта кириллицы).

Оператор HAVING

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

Выборка количества после группировки по клиентам

SELECT
  customer,
  COUNT(id) AS orders_count
FROM orders
GROUP BY customer
HAVING orders_count >= 3;

Результат:
Клиенты, у которых не менее 3 заказов.

Выборка суммы после группировки по клиентам

SELECT
  customer,
  SUM(amount) AS total_qty
FROM orders
GROUP BY customer
HAVING total_qty >= 40
ORDER BY total_qty DESC;

Результат:
Покупатели с суммарными закупками не менее 40 единиц

Выборка минимума после группировки по категориям

SELECT
  category,
  MIN(amount) AS min_per_category
FROM orders
GROUP BY category
HAVING min_per_category >= 5;

Результат:
Категории, в которых минимальный размер заказа не ниже 5.

Выборка среднего после группировки по категориям

SELECT
  category,
  AVG(amount) AS avg_per_category
FROM orders
GROUP BY category
HAVING avg_per_category BETWEEN 5 AND 8;

Результат:
Средний размер заказа на категорию в пределах от 5 до 8 включительно.

Выборка количества после группировки по категориям

SELECT
  category,
  COUNT(id) AS rows_count
FROM orders
GROUP BY category
HAVING rows_count >= 3
ORDER BY rows_count DESC;

Результат:
Категории, по которым сделано не менее 3 строк заказов

HAVING без GROUP BY

Можно фильтровать агрегат по всей таблице одной строкой результата:

SELECT SUM(amount) AS total_qty
FROM orders
HAVING total_qty > 60;

Результат:
Показать суммарные продажи только если они превышают 60.

Сравнение WHERE и HAVING

• WHERE фильтрует строки до группировки.
• HAVING фильтрует группы после вычисления агрегатов.

Механика (упрощённо):

Пример 1. Отобрать «крупные» строки заказов

SELECT
  category,
  SUM(amount) AS total_qty
FROM orders
WHERE amount >= 25
GROUP BY category
ORDER BY total_qty DESC;

Результат:
Только те строки, где amount >= 25 (до группировки).

Почему так:
WHERE amount >= 25 сначала убирает все строки с меньшими значениями. В GROUP BY category попадают только оставшиеся строки; SUM(amount) суммирует уже урезанный набор.

Интерпретация:
останутся лишь те категории, где встретилась хотя бы одна «крупная» строка (>= 25), а сумма считается по крупным строкам.

Пример 2. Отобрать «крупные» товары по итогу

SELECT
  category,
  SUM(amount) AS total_qty
FROM orders
GROUP BY category
HAVING total_qty >= 25
ORDER BY total_qty DESC;

Результат:
Все строки учитываются, но оставляем только те группы, где суммарные продажи по товару total_qty >= 25.

Почему так:
сначала группируем все строки и считаем итог SUM(amount) по каждой категории; затем HAVING total_qty >= 25 отбрасывает категории с малой суммой.

Интерпретация:
категория пройдёт, даже если в ней нет ни одной отдельной строки >= 25, но суммарно набирается >= 25 за счёт многих мелких заказов.

Порядок и «скелет» запроса сводной аналитики

1. Сформулировать вопрос на обычном языке.
2. Выбрать ключ группировки.
3. Выбрать агрегаты.
4. Доп-фильтры по строкам (WHERE).
5. Фильтр по итогам групп (HAVING).
6. Сортировка результатов (ORDER BY) и порог (LIMIT).

Шаблон:

SELECT group_key, AGG(...) AS metric, ...
FROM table
[WHERE ...]
GROUP BY group_key
[HAVING ...]
[ORDER BY metric DESC]
[LIMIT N];

Типичные ошибки и как их избежать

• Выбор неагрегированных полей вне GROUP BY. Работает не везде / даёт недетерминированные значения. Решение: добавьте поле в GROUP BY или заверните в агрегат/выражение.
• Использование HAVING вместо WHERE для построчных условий. Если критерий относится к строкам (напр. amount >= 10) — это WHERE. Если к агрегатам группы (SUM(amount) >= 25) — это HAVING.
• Неуправляемый порядок строк. Добавляйте явный ORDER BY по метрике/ключу.
• Смешение регистров/вариантов написания ключа группировки. Нормализуйте (LOWER(...), TRIM(...)) в GROUP BY.

Практические задания

Задание 1

-- Ваш код можете писать тут

SELECT
  SUM(amount) AS total_qty,
  COUNT(*)    AS total_orders
FROM orders;

Задание 2

-- Ваш код можете писать тут

SELECT
  DISTINCT customer AS unique_customers
FROM orders;
SELECT
  COUNT(DISTINCT customer) AS unique_customers_count
FROM orders;

Задание 3

-- Ваш код можете писать тут

SELECT
  category,
  SUM(amount) AS total_qty
FROM orders
GROUP BY category
ORDER BY total_qty DESC
LIMIT 3;

Задание 4

-- Ваш код можете писать тут

SELECT
  customer,
  AVG(amount) AS avg_per_order,
  COUNT(id)   AS orders_count
FROM orders
GROUP BY customer
ORDER BY avg_per_order DESC;

Задание 5

-- Ваш код можете писать тут

SELECT
  category,
  SUM(amount) AS total_qty
FROM orders
GROUP BY category
HAVING total_qty >= 50
ORDER BY total_qty DESC;

Задание 6

-- Ваш код можете писать тут

SELECT
  customer,
  COUNT(id)   AS orders_count,
  SUM(amount) AS total_qty
FROM orders
GROUP BY customer
HAVING orders_count <= 5 AND total_qty >= 30
ORDER BY orders_count DESC, customer ASC;