Агрегатные функции: COUNT, SUM, AVG, MIN, MAX

Агрегатные функции: COUNT, SUM, AVG, MIN, MAX

До этого момента все запросы возвращали строки из таблицы — одну за одной, по одной строке результата на одну строку исходных данных. Но огромный класс реальных задач требует другого: «сколько заказов за сегодня?», «какова средняя зарплата в IT-отделе?», «какой самый дорогой товар?», «сколько денег пришло за месяц?». На все эти вопросы отвечает не строка данных, а одно число — результат вычисления над множеством строк. Это и есть агрегатные функции.

Что такое агрегация

Агрегатная функция принимает на вход набор строк и возвращает одно значение — сводный результат. Она «схлопывает» множество строк в одно число (или строку, или дату).

SQL предоставляет пять стандартных агрегатных функций:

Все они доступны в стандартном SQL и работают во всех СУБД.

COUNT: подсчёт строк

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

SELECT COUNT(*) FROM employees;
-- Вернёт: 5 (если в таблице 5 сотрудников)

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

SELECT COUNT(phone) FROM employees;
-- Вернёт: 3 (если у 2 сотрудников phone IS NULL)

Разница важна: COUNT(*) считает все строки, COUNT(phone) — только строки с заполненным телефоном.

Пример с фильтрацией — количество разработчиков:

SELECT COUNT(*) AS developers_count
FROM   employees
WHERE  department = 'Разработка';

WHERE работает до агрегации: сначала фильтруются строки, затем считается количество.

Проверь себя: что вернёт COUNT(*) для таблицы без строк?

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

SUM суммирует числовую колонку:

SELECT SUM(salary) AS total_salary
FROM   employees;

С фильтрацией — суммарный фонд оплаты труда IT-отдела:

SELECT SUM(salary) AS it_payroll
FROM   employees
WHERE  department = 'IT';

SUM с NULL: функция автоматически игнорирует NULL-значения. Если в колонке 5 значений и одно NULL, SUM посчитает четыре.

Если все значения NULL, SUM вернёт NULL (не 0). Используйте COALESCE(SUM(col), 0) если нужен 0 вместо NULL.

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

AVG вычисляет среднее арифметическое:

SELECT AVG(salary) AS avg_salary
FROM   employees
WHERE  department = 'Разработка';

Как и SUM, AVG игнорирует NULL. Это значит, что AVG — это SUM поделённый на количество не-NULL значений, а не на общее число строк.

Значения: 90000, NULL, 85000, 95000
SUM = 270000, COUNT = 3 (не 4!), AVG = 90000

Результат AVG — дробное число. PostgreSQL сохраняет точность: AVG(integer) возвращает NUMERIC.

MIN и MAX: экстремальные значения

MIN возвращает наименьшее значение, MAX — наибольшее:

SELECT MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM   employees;

MIN и MAX работают не только с числами, но и со строками (лексикографически) и с датами:

SELECT MIN(hire_date) AS earliest_hire,
       MAX(hire_date) AS latest_hire
FROM   employees;

Это вернёт дату найма самого «старого» и самого «нового» сотрудника.

Несколько агрегатов в одном запросе

Можно вычислять несколько агрегатов одновременно:

SELECT COUNT(*) AS total,
       SUM(salary) AS payroll,
       AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM   employees;

Результат — одна строка с пятью колонками. СУБД вычислит все пять значений за один проход по данным.

Пример реального дашборда — сводка по заказам:

SELECT COUNT(*)                     AS orders_count,
       SUM(total_amount)            AS revenue,
       AVG(total_amount)            AS avg_order,
       MIN(total_amount)            AS min_order,
       MAX(total_amount)            AS max_order
FROM   orders
WHERE  status = 'delivered'
  AND  created_at >= '2024-01-01';

Один запрос — полная картина по доставленным заказам с начала 2024 года.

COUNT DISTINCT: уникальные значения

Часто нужно подсчитать количество уникальных значений в колонке:

SELECT COUNT(DISTINCT department) AS departments_count
FROM   employees;

Это вернёт количество уникальных отделов, а не общее число сотрудников. COUNT(DISTINCT col) — комбинация агрегации и дедупликации.

Пример: сколько уникальных клиентов сделало заказы в этом месяце:

SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM   orders
WHERE  created_at >= '2024-01-01' AND created_at < '2024-02-01';

Агрегатные функции и строки из таблицы

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

-- Ошибка!
SELECT name, COUNT(*) FROM employees;
-- ERROR: column "employees.name" must appear in GROUP BY clause or be used in aggregate function

PostgreSQL не знает, какое именно name вернуть, если строк несколько, а результат — одна строка. Нужно либо GROUP BY name, либо убрать name из SELECT. Это разберём подробно в следующем уроке.

Производительность агрегатных функций

Агрегатные функции вычисляются после WHERE-фильтрации. Это значит, что WHERE уменьшает набор строк, над которым считается агрегат. Всегда фильтруйте через WHERE перед агрегацией — это быстрее, чем агрегировать всё и фильтровать потом.

На больших таблицах COUNT(*) с хорошим индексом работает быстро — PostgreSQL может использовать только индекс, не читая данные строк. SUM и AVG требуют обхода всех отфильтрованных строк для вычисления. Это нормально для аналитических запросов, которые выполняются редко, но учитывайте при проектировании высоконагруженных OLTP-систем.

Практический пример: HR-отчёт

Задача: подготовить базовый HR-отчёт по компании.

SELECT COUNT(*)                     AS total_employees,
       COUNT(DISTINCT department)   AS departments,
       SUM(salary)                  AS total_payroll,
       ROUND(AVG(salary), 2)        AS avg_salary,
       MIN(salary)                  AS min_salary,
       MAX(salary)                  AS max_salary,
       MAX(salary) - MIN(salary)    AS salary_range
FROM   employees
WHERE  status = 'active';

ROUND(AVG(salary), 2) — округляет среднее до 2 знаков после запятой. MAX - MIN — разброс зарплат. Один запрос — полная сводка.

Разница между COUNT(*), COUNT(col) и COUNT(DISTINCT col)

Три формы COUNT часто путают. Разберём на примере:

id | name   | department  | phone
---+--------+-------------+-------------
1  | Анна   | IT          | +7-999-1111
2  | Борис  | IT          | NULL
3  | Вера   | HR          | +7-999-3333
4  | Гриша  | HR          | +7-999-4444
5  | Дина   | IT          | NULL

SELECT COUNT(*)               AS all_rows,       -- 5
       COUNT(phone)           AS with_phone,     -- 3 (у Бориса и Дины нет телефона)
       COUNT(department)      AS with_dept,      -- 5 (NULL-ов нет)
       COUNT(DISTINCT department) AS dept_count  -- 2 (IT и HR)
FROM   employees;

COUNT(*) — абсолютно все строки, включая те, где все колонки NULL.
COUNT(col) — строки, где указанная колонка не NULL.
COUNT(DISTINCT col) — количество уникальных значений в колонке (без NULL).

Агрегаты в реальных аналитических задачах

Агрегатные функции — основа любой аналитики. Вот типичные сценарии:

Метрики e-commerce:

SELECT COUNT(*)              AS total_orders,
       COUNT(DISTINCT user_id) AS unique_buyers,
       SUM(total_amount)     AS gross_revenue,
       AVG(total_amount)     AS average_check,
       MAX(total_amount)     AS largest_order
FROM   orders
WHERE  status = 'completed'
  AND  created_at >= '2024-01-01';

Проверка качества данных:

SELECT COUNT(*)                            AS total_rows,
       COUNT(*) - COUNT(email)             AS missing_emails,
       COUNT(*) - COUNT(phone)             AS missing_phones,
       COUNT(DISTINCT email) - COUNT(*)    AS duplicate_emails
FROM   customers;

Этот запрос сразу покажет: сколько клиентов без email, без телефона, и есть ли дублирующиеся email-адреса. Такие «аудит-запросы» — стандартный инструмент при анализе качества данных в любой БД.

Краткий итог

• Агрегатные функции возвращают одно значение для набора строк
• COUNT(*) — все строки; COUNT(col) — только не-NULL; COUNT(DISTINCT col) — уникальные
• SUM, AVG игнорируют NULL; если все NULL — возвращают NULL
• MIN, MAX работают с числами, строками и датами
• Нельзя смешивать обычные колонки и агрегаты без GROUP BY
• WHERE применяется до агрегации — фильтруйте заранее

Что дальше

Одно агрегированное число по всей таблице — это полезно. Но ещё полезнее — сгруппировать строки и получить агрегат для каждой группы отдельно. Например, средняя зарплата по каждому отделу, а не по всей компании. Это делает клауза GROUP BY — разбирём её в следующем уроке.