Skip to content

lesson05.md

Latest commit

 

History

History
454 lines (343 loc) · 20.6 KB

lesson05.md

File metadata and controls

454 lines (343 loc) · 20.6 KB

Урок 5. Группировка и агрегаты

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

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

COUNT — количество строк

COUNT(*) считает все строки в результате запроса:

-- Сколько всего товаров в магазине?
SELECT COUNT(*) FROM products;
┌──────────┐
│ COUNT(*) │
├──────────┤
│       22 │
└──────────┘

COUNT(столбец) считает строки где значение столбца не равно NULL:

-- Сколько пользователей зарегистрировано?
SELECT COUNT(id) FROM users;

Разница между COUNT(*) и COUNT(столбец) проявляется только когда в столбце есть NULL-значения. COUNT(*) считает все строки включая те где есть NULL, COUNT(столбец) — только те где значение есть. В нашей базе данных все поля заполнены, поэтому результат одинаков. Для надёжности в большинстве случаев используют COUNT(*).

Псевдонимы с агрегатными функциями — обязательная практика, иначе заголовок столбца выглядит как COUNT(*):

SELECT COUNT(*) AS total_users FROM users;
┌─────────────┐
│ total_users │
├─────────────┤
│          15 │
└─────────────┘

SUM — сумма значений

-- Общая стоимость всех товаров на складе
SELECT SUM(price * stock) AS total_stock_value FROM products;
┌───────────────────┐
│ total_stock_value │
├───────────────────┤
│        2270200.00 │
└───────────────────┘

-- Сколько всего единиц товаров на складе
SELECT SUM(stock) AS total_items FROM products;

AVG — среднее значение

-- Средняя цена товара в магазине
SELECT AVG(price) AS avg_price FROM products;
┌──────────────────┐
│ avg_price        │
├──────────────────┤
│ 19972.7272727273 │
└──────────────────┘

AVG возвращает точное среднее — без округления. Для округления используется функция ROUND(значение, знаки_после_запятой):

SELECT ROUND(AVG(price), 2) AS avg_price FROM products;
┌───────────┐
│ avg_price │
├───────────┤
│  19972.73 │
└───────────┘

MIN и MAX — минимум и максимум

-- Самый дешёвый и самый дорогой товар
SELECT
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products;
┌───────────┬───────────┐
│ min_price │ max_price │
├───────────┼───────────┤
│    800.00 │  75000.00 │
└───────────┴───────────┘

-- Самый ранний и самый поздний заказ
SELECT
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders;
┌─────────────┬────────────┐
│ first_order │ last_order │
├─────────────┼────────────┤
│  2024-02-01 │ 2024-06-14 │
└─────────────┴────────────┘

Агрегатные функции игнорируют NULL-значения — SUM, AVG, MIN, MAX пропускают строки где значение отсутствует. COUNT(*) — единственное исключение, он считает все строки включая те где есть NULL.

GROUP BY — группировка строк

Агрегатные функции выше считали итоги по всей таблице. Но чаще нужны итоги по группам — отдельно для каждой категории, для каждого города, для каждого статуса.

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

-- Количество товаров в каждой категории
SELECT
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id;
┌─────────────┬───────────────┐
│ category_id │ product_count │
├─────────────┼───────────────┤
│           1 │             6 │
│           2 │             5 │
│           3 │             3 │
│           4 │             4 │
│           5 │             4 │
└─────────────┴───────────────┘

Вместо одной строки с итогом по всей таблице — пять строк, по одной для каждой группы.

Как работает GROUP BY

Можно представить это так: GROUP BY category_id как будто "складывает" все строки с одинаковым category_id в одну кучку, а затем агрегатная функция обрабатывает каждую кучку:

Кучка category_id = 1:   Ноутбук Lenovo, Ноутбук ASUS, Смартфон Samsung...  → COUNT = 6
Кучка category_id = 2:   Мышь Logitech, Клавиатура Keychron, Монитор LG...  → COUNT = 5
Кучка category_id = 3:   Стол IKEA, Кресло Hara, Подставка...               → COUNT = 3
...

Важное правило GROUP BY

В SELECT можно указывать только те столбцы которые:

  • перечислены в GROUP BY, или
  • обёрнуты в агрегатную функцию

Это правило — не прихоть синтаксиса, а логика: если строки сгруппированы, у каждой группы одно значение category_id, но много разных name. Какое имя выводить? СУБД не знает — поэтому запрещает:

-- Ошибка: name не входит в GROUP BY и не агрегирован
SELECT category_id, name, COUNT(*) FROM products
GROUP BY category_id;

-- Правильно: только category_id и агрегат
SELECT category_id, COUNT(*) AS product_count FROM products
GROUP BY category_id;

GROUP BY с несколькими агрегатами

В одном запросе можно использовать сразу несколько агрегатных функций:

-- Статистика по каждой категории
SELECT
    category_id,
    COUNT(*)            AS product_count,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price)          AS min_price,
    MAX(price)          AS max_price,
    SUM(stock)          AS total_stock
FROM products
GROUP BY category_id
ORDER BY avg_price DESC;
┌─────────────┬───────────────┬───────────┬───────────┬───────────┬─────────────┐
│ category_id │ product_count │ avg_price │ min_price │ max_price │ total_stock │
├─────────────┼───────────────┼───────────┼───────────┼───────────┼─────────────┤
│           3 │             3 │  16833.33 │  3500.00  │  32000.00 │          39 │
│           1 │             6 │  39166.67 │  22000.00 │  75000.00 │         106 │
│           2 │             5 │  11220.00 │   2200.00 │  32000.00 │         153 │
│           4 │             4 │   1375.00 │   1100.00 │   1800.00 │         330 │
│           5 │             4 │   1650.00 │    800.00 │   2500.00 │         475 │
└─────────────┴───────────────┴───────────┴───────────┴───────────┴─────────────┘

GROUP BY по нескольким столбцам

Можно группировать по нескольким столбцам одновременно. Группа тогда — уникальная комбинация значений всех указанных столбцов:

-- Количество заказов каждого статуса для каждого пользователя
SELECT
    user_id,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id, status
ORDER BY user_id;
┌─────────┬───────────┬─────────────┐
│ user_id │ status    │ order_count │
├─────────┼───────────┼─────────────┤
│       1 │ delivered │           2 │
│       2 │ delivered │           1 │
│       3 │ cancelled │           1 │
│       3 │ delivered │           1 │
│       5 │ delivered │           2 │
│ ...     │ ...       │         ... │
└─────────┴───────────┴─────────────┘

HAVING — фильтрация групп

WHERE фильтрует строки до группировки. Но что если нужно отфильтровать уже сгруппированные результаты? Для этого существует HAVING.

-- Категории в которых больше 4 товаров
SELECT
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING product_count > 4;
┌─────────────┬───────────────┐
│ category_id │ product_count │
├─────────────┼───────────────┤
│           1 │             6 │
│           2 │             5 │
└─────────────┴───────────────┘

-- Пользователи у которых больше одного заказа
SELECT
    user_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING order_count > 1;
┌─────────┬─────────────┐
│ user_id │ order_count │
├─────────┼─────────────┤
│       1 │           2 │
│       3 │           2 │
│       5 │           2 │
└─────────┴─────────────┘

WHERE vs HAVING — разница и совместное использование

Это один из самых частых вопросов — когда что использовать. Ответ простой:

WHERE HAVING
Когда применяется До группировки После группировки
К чему применяется К отдельным строкам К группам (результатам агрегации)
Может использовать агрегаты Нет Да

WHERE и HAVING можно использовать в одном запросе — они работают на разных этапах:

-- Среди товаров дороже 1000 рублей (WHERE)
-- найти категории где средняя цена выше 10 000 (HAVING)
SELECT
    category_id,
    ROUND(AVG(price), 2) AS avg_price,
    COUNT(*)             AS product_count
FROM products
WHERE price > 1000
GROUP BY category_id
HAVING avg_price > 10000
ORDER BY avg_price DESC;
┌─────────────┬───────────┬───────────────┐
│ category_id │ avg_price │ product_count │
├─────────────┼───────────┼───────────────┤
│           1 │  39166.67 │             6 │
│           3 │  23500.00 │             2 │
│           2 │  17220.00 │             4 │
└─────────────┴───────────┴───────────────┘

Как выполняется этот запрос шаг за шагом:

  1. FROM products — берём все строки таблицы
  2. WHERE price > 1000 — оставляем только строки где цена больше 1000
  3. GROUP BY category_id — группируем оставшиеся строки по категории
  4. HAVING avg_price > 10000 — оставляем только группы где среднее больше 10 000
  5. ORDER BY avg_price DESC — сортируем результат

Попытка написать WHERE AVG(price) > 10000 вызовет ошибку — агрегатные функции в WHERE недопустимы. Именно для этого существует HAVING.

Полный порядок клаузул

После этого урока структура запроса выглядит так:

SELECT [DISTINCT] столбцы / агрегаты
FROM таблица
WHERE условие_по_строкам
GROUP BY столбец
HAVING условие_по_группам
ORDER BY столбец [ASC|DESC]
LIMIT n OFFSET m;

Логический порядок выполнения SQL-запроса с группировкой и HAVING:

1. FROM       — выбираются таблицы и выполняются соединения (JOIN)
2. WHERE      — фильтруются отдельные строки
3. GROUP BY   — строки объединяются в группы
4. HAVING     — фильтруются группы по условиям (агрегатные функции)
5. SELECT     — формируются итоговые столбцы, создаются псевдонимы и выполняются агрегаты
6. DISTINCT   — удаляются дубликаты строк
7. ORDER BY   — сортировка результата
8. LIMIT/OFFSET — ограничение количества строк

Таким образом, хотя синтаксис запроса начинается с SELECT, СУБД сначала отбирает строки, затем фильтрует их, потом группирует, применяет HAVING, и только потом формирует итоговые столбцы. Это объясняет, почему агрегаты нельзя использовать в WHERE, но можно в HAVING и в SELECT.

Вопросы

  1. Чем отличается COUNT(*) от COUNT(столбец)?
  2. Почему следующий запрос вызовет ошибку?
  3. Что произойдёт если написать WHERE COUNT(*) > 2 вместо HAVING COUNT(*) > 2?
  4. На каком этапе выполняется WHERE, а на каком HAVING?
  5. Что вернёт AVG(price) если в одной из строк price равен NULL?
  6. Можно ли использовать ORDER BY вместе с GROUP BY? Если да — к чему применяется сортировка?
  7. Чем GROUP BY user_id отличается от GROUP BY user_id, status?
  8. Как получить только среднее значение без лишних знаков после запятой?
  9. Можно ли в HAVING использовать условие без агрегатной функции? Например: HAVING category_id = 1?
  10. Каков полный порядок выполнения клаузул в запросе с GROUP BY?

Задачи

Задача 1.

Посчитайте общее количество заказов в таблице orders. Выведите результат с псевдонимом total_orders.

Задача 2.

Найдите минимальную, максимальную и среднюю (округлить до 2 знаков) цену среди всех товаров.

Задача 3.

Посчитайте количество заказов с каждым статусом. Выведите status и order_count. Отсортируйте по количеству по убыванию.

Задача 4.

Для каждого пользователя посчитайте количество его заказов. Выведите user_id и order_count. Отсортируйте по user_id.

Задача 5.

Найдите суммарное количество товаров на складе (SUM(stock)) и общую стоимость складского остатка (SUM(price * stock)) для каждой категории. Округлите стоимость до 2 знаков. Отсортируйте по суммарной стоимости по убыванию.

Задача 6.

Найдите пользователей у которых ровно один заказ. Выведите user_id и количество заказов.

Задача 7.

Найдите категории где средняя цена товара ниже 5000 рублей. Выведите category_id и среднюю цену округлённую до 2 знаков.

Задача 8.

Среди товаров дешевле 30 000 рублей найдите категории где суммарный складской остаток превышает 100 единиц. Выведите category_id и суммарный остаток.

Задача 9.

Найдите количество пользователей из каждого города. Выведите только те города где пользователей больше одного. Отсортируйте по количеству по убыванию.

Задача 10.

Для каждого пользователя у которого есть хотя бы два заказа — найдите количество заказов с каждым статусом. Выведите user_id, status, order_count. Отсортируйте по user_id, затем по status.

Эта задача должна быть разделена на два шага: сначала найти пользователей с двумя и более заказами, затем для них посчитать разбивку по статусам. Чистое решение требует подзапроса (Урок 7).

Предыдущий урок | Следующий урок