Агрегация и группировка

1. Агрегатные функции: считаем метрики

Агрегатные функции принимают множество строк и возвращают одно значение.

COUNT(): считаем строки

SELECT COUNT(*)
FROM employees;

→ 8 (всего сотрудников)

⚠️ Важный нюанс:COUNT(*) считает все строки, включая те, где есть NULL.

Если хочешь посчитать только строки, где в конкретном поле есть значение:

SELECT COUNT(bonus)
FROM employees;

→ 4 (только у Анны, Марии, Елены, Дарьи есть премия)

COUNT(bonus) игнорирует NULL. Это частая причина ошибок: если ты хочешь узнать, сколько сотрудников получают премию, используй COUNT(bonus), а не COUNT(*).

Можно посчитать уникальные значения:

SELECT COUNT(DISTINCT city)
FROM employees;

→ 3 (Москва, СПб, Казань)

SUM(): сумма значений

SELECT SUM(salary)
FROM employees;

→ 760 000 (общий фонд зарплат)

Если в столбце есть NULL, SUM() их игнорирует. Если все строки NULL, результат будет NULL.

AVG(): среднее значение

SELECT AVG(salary)
FROM employees;

→ 95 000 (760 000 / 8)

AVG() тоже игнорирует NULL. Если у тебя 10 сотрудников, но у 3-х зарплата NULL, среднее считается по 7.

MIN() и MAX(): минимум и максимум

SELECT MIN(salary), MAX(salary)
FROM employees;

→ 70 000 (Андрей), 130 000 (Мария)

Работают не только с числами, но и с датами, строками (лексикографический порядок).

2. GROUP BY: группируем данные

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

Например: сколько сотрудников в каждом отделе?

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Результат:

Как это работает

1. База берёт все строки из employees.
2. GROUP BY department разбивает их на группы по значению department.
3. Для каждой группы считается COUNT(*).

💡 Ключевое правило: в SELECT можно указывать только:

• поля из GROUP BY (по которым группируем)
• агрегатные функции

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

-- НЕПРАВИЛЬНО:
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

В отделе IT три сотрудника (Анна, Мария, Павел). Какое имя показать? База не знает, поэтому выдаст ошибку.

Группировка по нескольким полям

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

SELECT department, city, COUNT(*)
FROM employees
GROUP BY department, city;

Результат:

Каждая уникальная комбинация (department, city) — отдельная группа.

3. HAVING: фильтруем группы

WHERE фильтрует строки до группировки. HAVING фильтрует группы после группировки.

Пример: найдём отделы, где больше 2 сотрудников.

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Результат:

Почему нельзя использовать WHERE? Потому что WHERE работает до GROUP BY, когда групп ещё нет. А HAVING — после, когда группы уже сформированы и можно применять агрегаты.

WHERE vs HAVING: когда что использовать

Используй WHERE, если фильтр не зависит от агрегатов:

-- Сотрудники из Москвы, сгруппированные по отделам
SELECT department, COUNT(*)
FROM employees
WHERE city = 'Москва'
GROUP BY department;

→ IT: 2, Маркетинг: 1, Продажи: 1

Используй HAVING, если фильтр зависит от агрегата:

-- Отделы, где средняя зарплата больше 100 000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

→ IT: 120 000

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

-- Медленнее (считает агрегат для всех, потом фильтрует):
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING department = 'IT';

-- Быстрее (сразу берёт только IT):
SELECT department, AVG(salary)
FROM employees
WHERE department = 'IT'
GROUP BY department;

4. Порядок выполнения запроса: полная картина

Теперь, когда ты знаешь GROUP BY и HAVING, давай соберём полный порядок:

Следствия из порядка выполнения

1. В WHERE нельзя использовать псевдонимы из SELECT:

-- НЕ РАБОТАЕТ:
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 1000000;

Почему? Потому что WHERE выполняется до SELECT, и псевдоним annual_salary ещё не существует.

Правильно:

SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 1000000;

2. В WHERE нельзя использовать агрегаты:

-- НЕ РАБОТАЕТ:
SELECT department, COUNT(*)
FROM employees
WHERE COUNT(*) > 2
GROUP BY department;

Почему? Потому что WHERE выполняется до GROUP BY, и групп ещё нет.

Правильно — использовать HAVING:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

3. В ORDER BY можно использовать псевдонимы:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Почему работает? Потому что ORDER BY выполняется после SELECT, и псевдоним уже существует.

4. В HAVING можно использовать псевдонимы (в некоторых СУБД):

В PostgreSQL можно:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

Но в некоторых СУБД (например, MySQL старых версий) нельзя. Лучше писать явно: HAVING AVG(salary) > 100000.

5. Комбинированные примеры

Пример 1: Средняя зарплата по отделам, только для отделов с 3+ сотрудниками

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY avg_salary DESC;

Результат:

Логика:

1. Группируем по department.
2. Оставляем только группы, где COUNT(*) >= 3 (IT и Продажи).
3. Считаем AVG(salary) для оставшихся групп.
4. Сортируем по убыванию средней зарплаты.

Пример 2: Количество сотрудников в каждом городе, которые получают премию

SELECT city, COUNT(*) AS bonus_employees
FROM employees
WHERE bonus IS NOT NULL
GROUP BY city;

Результат:

Логика:

1. WHERE bonus IS NOT NULL — оставляем только тех, у кого есть премия (Анна, Мария, Елена, Дарья).
2. Группируем по city.
3. Считаем количество в каждой группе.

Пример 3: Отделы, где максимальная зарплата больше 100 000, и количество сотрудников

SELECT department, MAX(salary) AS max_salary, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING MAX(salary) > 100000;

Результат:

6. Практика: 8 задач на этот этап

Попробуй решить самостоятельно.

1. Посчитай общее количество сотрудников.
2. Посчитай, сколько сотрудников получают премию.
3. Найди минимальную, максимальную и среднюю зарплату.
4. Посчитай количество сотрудников в каждом отделе.
5. Найди среднюю зарплату в каждом городе.
6. Найди отделы, где средняя зарплата больше 90 000.
7. Для каждого города посчитай количество сотрудников, которые получают премию.
8. Найди отделы, где больше 2 сотрудников и средняя зарплата больше 80 000. Отсортируй по убыванию средней зарплаты.

-- 1
SELECT COUNT(*)
FROM employees;

-- 2
SELECT COUNT(bonus)
FROM employees
WHERE bonus IS NOT NULL;

-- 3
SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal, AVG(salary) AS avg_sal
FROM employees;

-- 4
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- 5
SELECT city, AVG(salary)
FROM employees
GROUP BY city;

-- 6
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 90000;

-- 7
SELECT city, COUNT(*) AS bonus_count
FROM employees
WHERE bonus IS NOT NULL
GROUP BY city;

-- 8
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 2 AND AVG(salary) > 80000
ORDER BY avg_salary DESC;

Mini-чеклист, что этап освоен

Ты готова к третьему этапу, если можешь без подсказки объяснить:

• чем COUNT(*) отличается от COUNT(column_name);
• почему в SELECT после GROUP BY можно использовать только поля из GROUP BY и агрегаты;
• чем WHERE отличается от HAVING по моменту выполнения;
• почему в WHERE нельзя использовать псевдонимы из SELECT;
• как оптимизировать запрос, если фильтр не зависит от агрегата.