Типичные ошибки при работе с агрегатами

Теперь пришло время углубиться в типичные ошибки, которые возникают при использовании этих функций. Даже самые опытные SQL-мастера иногда наступают на грабли, и наша задача - научиться эти грабли видеть и успешно их обходить.

Вы когда-нибудь писали запрос, а он ругался на что-то загадочное вроде "column must appear in the GROUP BY clause or be used in an aggregate function"? Или, может быть, результат вашего запроса выглядел странно, а вы понятия не имели, почему? Это только верхушка айсберга типичных ошибок при работе с агрегатными функциями. Эта лекция — ваш гид по выживанию в море ошибок и недоразумений.

Ошибка 1: Использование неагрегированного столбца вне GROUP BY

Проблема

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

SELECT department, salary, SUM(salary)
FROM employees
GROUP BY department;

PostgreSQL тут же скажет вам:

ERROR: column "employees.salary" must appear in the GROUP BY clause or be used in an aggregate function

Почему так происходит?

Когда вы используете GROUP BY, PostgreSQL объединяет строки по указанным столбцам. Но если вы добавляете еще один столбец (в данном случае salary), PostgreSQL не знает, что с ним делать. Он ведь не понимает, хотите вы всего одну зарплату, среднее значение или что-то еще.

Как исправить? Есть два пути:

1. Убедитесь, что все неагрегированные колонки присутствуют в GROUP BY:

SELECT department, salary
FROM employees
GROUP BY department, salary;

2. Или оберните столбец в агрегатную функцию, если это имеет смысл:

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

Совет: если PostgreSQL ругается на GROUP BY, спросите себя: "А этот столбец точно нужен в запросе? Если да, то какую именно роль он играет?"

Ошибка 2: Неправильная работа с COUNT() и NULL

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

SELECT COUNT(bonus) AS bonus_count
FROM employees;

Но вдруг замечаете, что результат меньше, чем ожидали. Почему? Потому что COUNT(column) игнорирует строки, где column равен NULL.

Решение: если вы хотите подсчитать вообще все строки, то используйте COUNT(*):

SELECT COUNT(*) AS total_count
FROM employees;

Или уточните, что вам нужны только те строки, где бонус не NULL:

SELECT COUNT(bonus) AS bonus_count
FROM employees
WHERE bonus IS NOT NULL;

Подсказка: если вы хотите учитывать разницу между записями, содержащими NULL, и полным отсутствием записей в таблице, внимательно выбирайте между COUNT(*) и COUNT(column).

Ошибка 3: Забытая фильтрация с помощью HAVING вместо WHERE

Проблема: вы хотите найти отделы, где средняя зарплата превышает 5000. Новый разработчик может написать что-то вроде этого:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE AVG(salary) > 5000
GROUP BY department;

PostgreSQL выбросит ошибку:

ERROR: aggregate functions are not allowed in WHERE clause

Это происходит, поскольку фильтрация на уровне WHERE происходит до выполнения группировки, а агрегатные функции применяются уже после группировки. Следовательно, средняя зарплата AVG(salary) еще не рассчитана на этапе выполнения WHERE.

Чтобы это исправить, используйте HAVING для фильтрации агрегированных данных:

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

Ошибка 4: Фильтрация с WHERE и дизориентация в порядке выполнения

Проблема: вы хотите узнать количество сотрудников в отделах, где возраст сотрудников больше 30 лет. Запрос может выглядеть так:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
WHERE age > 30;

PostgreSQL снова вас огорчит:

ERROR: syntax error at or near "WHERE"

Почему так происходит? Оператор WHERE всегда обрабатывается раньше, чем GROUP BY. В данном случае вы просто поставили WHERE не туда.

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

SELECT department, COUNT(*)
FROM employees
WHERE age > 30
GROUP BY department;

Ошибка 5: Использование NULL с SUM(), AVG() и другими функциями

Проблема: вы хотите найти общий бонус, выданный сотрудникам, и пишете:

SELECT SUM(bonus) AS total_bonus
FROM employees;

Но ваш результат кажется подозрительно низким. А все потому, что у половины сотрудников бонусы не указаны, и эти NULL просто игнорируются.

Решение: обрабатывайте NULL заранее. Например, вы можете заменить NULL на 0:

SELECT SUM(COALESCE(bonus, 0)) AS total_bonus
FROM employees;

Теперь все NULL будут заменены на 0, и сумма станет корректной.

Мы детально рассмотрим работу функции COALESCE буквально через пару лекций.

Ошибка 6: использование нескольких агрегатных функций без понимания их связи

Проблема: вы хотите подсчитать общее количество сотрудников и общую зарплату. Но пишете что-то, что возвращает странные результаты:

SELECT COUNT(salary) AS count_salary, SUM(salary) AS total_salary
FROM employees;

Почему тут все может пойти не так? Если у кого-то зарплата равна NULL, COUNT(salary) и SUM(salary) дадут разные результаты, что может запутать.

Всегда учитывайте, что агрегатные функции работают независимо друг от друга. Если есть NULL, то это поведет к разным результатам. Используйте COALESCE или COUNT(*), чтобы гарантировать единообразие:

SELECT COUNT(*) AS total_employees, SUM(COALESCE(salary, 0)) AS total_salary
FROM employees;

Ошибка 7: Неоптимизированные запросы с большими группировками

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

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

Перед выполнением группировки подумайте, действительно ли все столбцы нужны в GROUP BY. Чем больше уникальных значений в группе, тем дольше выполнение запроса. Если возможно, сократите группировку:

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

Эти ошибки распространены, и даже самые опытные SQL-разработчики сталкиваются с ними. Надеюсь, теперь вам легче будет обойти эти подводные камни и писать запросы, которые работают быстро, правильно и красиво.