📊 Урок 3.1: Агрегатные функции

👋 Привет! До этого мы просто выбирали строки — все подряд. Но что, если нужно ответить на вопросы: «Сколько всего товаров?», «Какая средняя цена?», «Какова сумма всех заказов?» Тут на помощь приходят агрегатные функции — они считают что-то по ВСЕМ строкам разом.

🧠 Что такое агрегатная функция?

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

SQL даёт нам 5 основных агрегатных функций:

📦 Готовим данные: таблица products

Давайте создадим простую таблицу с товарами и наполним её данными. Представьте, что это маленький интернет-магазин.

-- Создаём таблицу товаров
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL NOT NULL, quantity INTEGER DEFAULT 0
); -- Добавляем образцы данных
INSERT INTO products (id, name, category, price, quantity) VALUES
(1, 'Ноутбук', 'Электроника', 75000, 10),
(2, 'Мышь', 'Электроника', 1500, 50),
(3, 'Клавиатура', 'Электроника', 3500, 30),
(4, 'Кружка', 'Посуда', 500, 100),
(5, 'Тарелка', 'Посуда', 300, 200),
(6, 'Книга "SQL для чайников"', 'Книги', 1200, 15),
(7, 'Книга "Python"', 'Книги', 1500, 20),
(8, 'Наушники', 'Электроника', 4500, 25),
(9, 'Чайник', 'Посуда', 2500, 12),
(10, NULL, 'Мебель', 10000, 5); -- товар без имени (для примера)

🔢 COUNT(*) — считаем ВСЕ строки

COUNT(*) — самая простая функция. Она просто считает количество строк в таблице. Неважно, есть там NULL или нет — считаются все строки.

-- Сколько всего строк в таблице products?
SELECT COUNT(*) AS "Всего товаров"
FROM products;

COUNT(*) посчитал все 10 строк, включая ту, где имя равно NULL. Ему всё равно на содержимое — он считает строки как записи.

📝 COUNT(столбец) — считаем НЕпустые значения

А вот COUNT(столбец) — хитрее. Он считает только те строки, где в указанном столбце НЕ NULL. NULL он игнорирует.

-- Сколько товаров имеют имя?
SELECT COUNT(name) AS "Товаров с именем"
FROM products;

Видите? Всего строк 10, а COUNT(name) вернул 9. Потому что у 10-го товара имя — NULL. COUNT(имя) его проигнорировал.

Это очень полезно, когда нужно узнать, сколько записей заполнили какое-то поле. Например, сколько клиентов указали свой телефон.

-- Сравнение COUNT(*) и COUNT(column)
SELECT COUNT(*) AS "Всего строк", COUNT(name) AS "С name", COUNT(category) AS "С category", COUNT(price) AS "С price", COUNT(quantity) AS "С quantity"
FROM products;

Цифры разные! У нас есть строка, где name = NULL (десятый товар без названия), а вот category у всех товаров заполнена. COUNT(*) всегда показывает 10, а COUNT(name) — 9, потому что пропущено ровно одно значение.

💰 SUM(столбец) — складываем всё вместе

SUM — суммирует все значения в столбце. Работает только с числами. Если в столбце есть NULL — SUM его просто игнорирует (как будто его и не было).

-- Какая общая стоимость ВСЕХ товаров на складе?
-- (учитываем и цену, и количество)
SELECT SUM(price * quantity) AS "Общая стоимость товаров"
FROM products;

Давайте посчитаем, откуда взялась эта цифра:

📐 AVG(столбец) — среднее арифметическое

AVG считает среднее значение по столбцу. Формула простая: SUM(столбец) / COUNT(столбец). Только NULL опять игнорируются.

-- Какая средняя цена товара в магазине?
SELECT AVG(price) AS "Средняя цена"
FROM products;

Средняя цена = 75000 + 1500 + 3500 + 500 + 300 + 1200 + 1500 + 4500 + 2500 + 10000 = 100 500 / 10 = 10 050.

AVG отлично подходит для аналитики: средний чек, средний балл ученика, средняя температура по больнице (в переносном смысле).

-- AVG не работает с текстом или датами (бессмысленно)
-- Зато работает с числами:
SELECT AVG(price) AS "Средняя цена", AVG(quantity) AS "Среднее количество"
FROM products;

⬇️ MIN() и MAX() — минимум и максимум

MIN находит минимальное значение в столбце, MAX — максимальное. Они работают с числами, текстом и датами.

-- Самый дешёвый и самый дорогой товар
SELECT MIN(price) AS "Дешевле некуда", MAX(price) AS "Дороже некуда"
FROM products;

Давай посмотрим, как называется самый дорогой товар:

-- Минуточку... мы знаем цену 75000, но это ноутбук?
-- Давайте узнаем название самого дорогого товара:
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Это подзапрос — мы к нему вернёмся в следующих уроках. Пока просто смотри, как MIN и MAX можно комбинировать с WHERE.

🎯 DISTINCT — уникальные значения

DISTINCT — это не совсем агрегатная функция, но она часто с ними используется. DISTINCT «схлопывает» повторяющиеся значения, оставляя только уникальные.

-- Какие категории товаров есть в магазине?
SELECT DISTINCT category
FROM products;

Без DISTINCT мы бы получили «Электроника» четыре раза, «Посуда» три раза, «Книги» два раза и «Мебель» один раз. С DISTINCT — только по одному разу.

-- Сколько разных категорий?
SELECT COUNT(DISTINCT category) AS "Количество категорий"
FROM products;

И снова NULL она не посчитала, потому что мы передали столбец category, а не звездочку.

🧪 Пример 1: Анализ товаров с агрегатными функциями

Давайте теперь объединим всё вместе и проанализируем наш магазин. Один запрос — и куча полезной информации!

-- Полный анализ товаров
SELECT COUNT(*) AS "Всего товаров", COUNT(name) AS "Товаров с именем", COUNT(DISTINCT category) AS "Категорий всего", ROUND(AVG(price), 0) AS "Средняя цена (₽)", SUM(price * quantity) AS "Суммарная стоимость (₽)", MIN(price) AS "Мин. цена (₽)", MAX(price) AS "Макс. цена (₽)"
FROM products;

🍕 Пример 2: Бизнес-аналитика для пиццерии

Представим, что у нас есть сеть пиццерий, и мы хотим проанализировать продажи:

-- Создаём таблицу продаж пиццерии
CREATE TABLE sales ( id INTEGER PRIMARY KEY, pizza_name TEXT, size TEXT, price REAL, quantity INTEGER, order_date DATE
); INSERT INTO sales (id, pizza_name, size, price, quantity, order_date) VALUES
(1, 'Маргарита', 'Средняя', 450, 2, '2024-01-15'),
(2, 'Пепперони', 'Большая', 650, 1, '2024-01-15'),
(3, 'Маргарита', 'Большая', 550, 3, '2024-01-16'),
(4, 'Гавайская', 'Средняя', 500, 1, '2024-01-16'),
(5, 'Пепперони', 'Средняя', 500, 4, '2024-01-17'),
(6, 'Маргарита', 'Маленькая', 350, 2, '2024-01-17'),
(7, 'Четыре сыра', 'Большая', 700, 1, '2024-01-18'),
(8, 'Гавайская', 'Большая', 600, 2, '2024-01-18'),
(9, NULL, 'Средняя', 450, 1, '2024-01-19'),
(10, 'Маргарита', 'Средняя', 450, 3, '2024-01-19');

А теперь зададим вопросы:

-- Сколько всего продано пицц?
SELECT SUM(quantity) AS "Всего продано пицц" FROM sales; -- На какую сумму продали?
SELECT SUM(price * quantity) AS "Общая выручка" FROM sales; -- Средняя стоимость одной пиццы
SELECT ROUND(AVG(price), 2) AS "Средняя цена пиццы" FROM sales; -- Самая дорогая и самая дешёвая пицца в меню
SELECT MIN(price) AS "Самая дешёвая", MAX(price) AS "Самая дорогая"
FROM sales; -- Сколько разных видов пицц продали?
SELECT COUNT(DISTINCT pizza_name) AS "Разных видов" FROM sales;

⚠️ Подводные камни и нюансы

Давай разберём ситуации, где новички чаще всего ошибаются:

❌ Ошибка 1: NULL в SUM

-- Допустим, в таблице есть строка с price = NULL
-- SUM(price) просто проигнорирует эту строку
SELECT SUM(price) FROM products; -- Работает, но будьте внимательны

Если ВСЕ значения NULL, SUM вернёт NULL, а не 0! Будьте готовы к этому и используйте COALESCE:

-- Если все цены NULL — получим NULL, а не 0
-- Чтобы получить 0, используем COALESCE:
SELECT COALESCE(SUM(price), 0) AS "Сумма цен" FROM products;

❌ Ошибка 2: AVG с NULL

AVG делит на количество НЕ-NULL строк, а не на общее количество строк. Помни: AVG(price) = SUM(price) / COUNT(price), а не SUM(price) / COUNT(*).

❌ Ошибка 3: Использование агрегатной функции в WHERE

-- ЭТО НЕ СРАБОТАЕТ:
SELECT name, price
FROM products
WHERE price > AVG(price); -- ❌ Ошибка! -- Агрегатные функции нельзя использовать в WHERE напрямую.
-- Для таких фильтров используется HAVING (см. Урок 3.2)

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

А теперь — твоя очередь! Вот несколько задач. Попробуй решить их самостоятельно, прежде чем смотреть ответы.

SELECT COUNT(*) FROM products;

SELECT COUNT(DISTINCT category) FROM products;

SELECT ROUND(AVG(price), 2) FROM products;

SELECT SUM(price * quantity) AS total_value FROM products;

SELECT name, price FROM products WHERE price = (SELECT MAX(price) FROM products);

SELECT SUM(quantity) AS "Всего продано", SUM(price * quantity) AS "Выручка", ROUND(AVG(price), 2) AS "Средняя цена", COUNT(DISTINCT size) AS "Размеров"
FROM sales;

📋 Шпаргалка: агрегатные функции

🚀 Что дальше?

В этом уроке мы научились считать, суммировать и находить минимумы с максимумами. Но агрегатные функции по всей таблице — это только начало. В Уроке 3.2 мы научимся группировать данные с GROUP BY и считать что-то по каждой группе отдельно. Например: «средняя зарплата по каждому отделу» или «количество товаров в каждой категории». Увидимся там! 🎯

Урок 3.1 — Агрегатные функции | Модуль 3: Считаем и группируем