Это пять самых базовых агрегатных функций. Они отвечают на вопросы вида «сколько», «сколько в сумме», «сколько в среднем», «самое маленькое», «самое большое». Все они «схлопывают» группу строк в одно число.
Если обычный SELECT показывает строки, то агрегатная функция — даёт итог по строкам. Без них любая статистика была бы ручным циклом в коде приложения.
Зачем нужны агрегаты
Любая аналитика начинается с агрегатов:
- Сколько у нас пользователей? —
COUNT(*) FROM users.
- Сколько денег в этом месяце? —
SUM(amount) FROM orders WHERE month = ....
- Какая средняя оценка товара? —
AVG(rating) FROM reviews.
- Самый дорогой заказ? —
MAX(amount) FROM orders.
Они работают и без GROUP BY (тогда вся таблица — одна большая «группа»), и с GROUP BY (одно значение на группу).
COUNT — сколько строк
SELECT COUNT(*) FROM users;
COUNT(*) — посчитал все строки.
Есть три формы:
COUNT(*) — все строки, включая те, где есть NULL.COUNT(column) — строки, где column IS NOT NULL. NULL'ы пропускает.COUNT(DISTINCT column) — уникальные не-NULL значения.
Пример с таблицей users:
SELECT
COUNT(*) AS all_rows,
COUNT(email) AS with_email,
COUNT(DISTINCT name) AS unique_names
FROM users;
COUNT(*) обычно быстрее всего и используется по умолчанию. COUNT(column) — когда хочешь именно «сколько заполнено». COUNT(DISTINCT) — для уникальных.
SUM — сумма
SELECT SUM(amount) FROM orders;
Складывает все значения колонки. NULL'ы пропускает (не превращает в 0). Если все строки NULL — результат NULL, не 0 (это часто сюрприз).
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 999;
SUM работает с числами (INTEGER, NUMERIC, REAL). На текстовых колонках упадёт.
AVG — среднее
SELECT AVG(rating) FROM reviews;
Сумма всех значений / количество не-NULL значений.
Важно: AVG для INTEGER колонки в Postgres возвращает NUMERIC (точно). В некоторых других БД — может вернуть INTEGER с обрезкой:
SELECT AVG(score) FROM tests;
SELECT ROUND(AVG(score), 2) FROM tests;
NULL'ы AVG пропускает, как и SUM.
MIN, MAX — самое маленькое и самое большое
SELECT
MIN(price) AS cheapest,
MAX(price) AS most_expensive
FROM products;
Работают с числами, датами, строками (в порядке alphabetical):
SELECT MIN(created_at), MAX(created_at) FROM orders;
SELECT MIN(name), MAX(name) FROM users;
NULL пропускают.
Агрегаты с GROUP BY
Главный сценарий — отчёты по группам:
SELECT
category,
COUNT(*) AS items,
SUM(price) AS total,
AVG(price) AS avg_price,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM products
GROUP BY category;
Каждая категория получит свою строку с пятью числами.
Агрегаты с DISTINCT
Все агрегаты принимают DISTINCT:
SELECT
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(DISTINCT amount) AS sum_unique_amounts
FROM orders;
COUNT(DISTINCT customer_id) — самый частый из них, для подсчёта уникальных юзеров. SUM(DISTINCT) редко имеет смысл (обычно нужен SUM, а не сумма уникальных значений).
Поведение с NULL
| Функция |
NULL значения |
COUNT(*) |
Считает все строки, NULL не пропускает |
COUNT(column) |
Пропускает NULL |
SUM(column) |
Пропускает NULL. Если все NULL → возвращает NULL (не 0) |
AVG(column) |
Пропускает NULL. Если все NULL → NULL |
MIN/MAX(column) |
Пропускают NULL. Если все NULL → NULL |
Если хочешь, чтобы пустая выборка возвращала 0, а не NULL — оборачивай в COALESCE:
SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 999;
Частые ошибки новичков
1. COUNT(*) vs COUNT(column). На таблицах, где половина строк имеют NULL в колонке, разница огромная. COUNT(*) = 100, COUNT(email) = 47 — это нормально и значит «47 из 100 заполнили email».
2. SUM/AVG пустой выборки = NULL, не 0. SELECT SUM(amount) FROM orders WHERE status = 'never' — если ни одной строки, SUM вернёт NULL. В коде приложения тогда упадёт что-то вроде null + 5. Обернул в COALESCE(SUM(amount), 0) — спасся.
3. Агрегат + не-агрегатная колонка без GROUP BY. SELECT name, COUNT(*) FROM users упадёт. Если хочешь «имя и счёт», нужен GROUP BY name. Если хочешь только счёт — без name.
4. AVG на целых числах. В MySQL AVG(INT) возвращает float, теряя точность на больших суммах. В Postgres — NUMERIC, точное. Если есть подозрение на потерю точности — каст: AVG(amount::NUMERIC).
5. MIN/MAX по тексту даёт алфавитный, не числовой порядок. MIN(version) для '1', '10', '2' даст '1', а MAX — '2' (а не '10'). Если версии — числа, храни числами или паддуй нулями.
6. COUNT(*) на огромной таблице медленный. Postgres честно бежит по всем строкам (или индексу) — на 100M строк это секунды. Если нужна примерная цифра — есть быстрые приёмы (pg_class.reltuples, статистика планировщика). Точная — только полный COUNT.
Мини-резюме
COUNT(*) — все строки. COUNT(column) — не-NULL. COUNT(DISTINCT column) — уникальные.SUM, AVG — складывают / усредняют, NULL пропускают. Пустая выборка → NULL, не 0.MIN, MAX — работают с числами, датами, строками (по алфавиту). NULL пропускают.- Хочешь 0 вместо NULL для пустой выборки —
COALESCE(SUM(x), 0).
- В
SELECT с агрегатами все остальные колонки должны быть в GROUP BY либо завернуты в агрегат.
Это пять самых базовых агрегатных функций. Они отвечают на вопросы вида «сколько», «сколько в сумме», «сколько в среднем», «самое маленькое», «самое большое». Все они «схлопывают» группу строк в одно число.
Если обычный
SELECTпоказывает строки, то агрегатная функция — даёт итог по строкам. Без них любая статистика была бы ручным циклом в коде приложения.Зачем нужны агрегаты
Любая аналитика начинается с агрегатов:
COUNT(*) FROM users.SUM(amount) FROM orders WHERE month = ....AVG(rating) FROM reviews.MAX(amount) FROM orders.Они работают и без
GROUP BY(тогда вся таблица — одна большая «группа»), и сGROUP BY(одно значение на группу).COUNT — сколько строк
SELECT COUNT(*) FROM users; -- → 1247COUNT(*)— посчитал все строки.Есть три формы:
COUNT(*)— все строки, включая те, где есть NULL.COUNT(column)— строки, гдеcolumn IS NOT NULL. NULL'ы пропускает.COUNT(DISTINCT column)— уникальные не-NULL значения.Пример с таблицей
users:SELECT COUNT(*) AS all_rows, -- 4 COUNT(email) AS with_email, -- 3 (Боб без email пропускается) COUNT(DISTINCT name) AS unique_names -- 3 (два Боба считаются одним) FROM users;COUNT(*)обычно быстрее всего и используется по умолчанию.COUNT(column)— когда хочешь именно «сколько заполнено».COUNT(DISTINCT)— для уникальных.SUM — сумма
SELECT SUM(amount) FROM orders; -- → 156500.00Складывает все значения колонки. NULL'ы пропускает (не превращает в 0). Если все строки NULL — результат
NULL, не0(это часто сюрприз).-- Защита от NULL — обернуть в COALESCE SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 999;SUMработает с числами (INTEGER,NUMERIC,REAL). На текстовых колонках упадёт.AVG — среднее
SELECT AVG(rating) FROM reviews; -- → 4.23Сумма всех значений / количество не-NULL значений.
Важно:
AVGдляINTEGERколонки в Postgres возвращаетNUMERIC(точно). В некоторых других БД — может вернутьINTEGERс обрезкой:-- Postgres: AVG(INTEGER) → NUMERIC, точное значение SELECT AVG(score) FROM tests; -- → 4.5000000000000000 -- Если хочется округлить до 2 знаков SELECT ROUND(AVG(score), 2) FROM tests; -- → 4.50NULL'ы AVG пропускает, как и SUM.
MIN, MAX — самое маленькое и самое большое
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive FROM products;Работают с числами, датами, строками (в порядке alphabetical):
-- Самая ранняя и самая поздняя дата заказа SELECT MIN(created_at), MAX(created_at) FROM orders; -- Алфавитно первое и последнее имя SELECT MIN(name), MAX(name) FROM users;NULL пропускают.
Агрегаты с GROUP BY
Главный сценарий — отчёты по группам:
SELECT category, COUNT(*) AS items, SUM(price) AS total, AVG(price) AS avg_price, MIN(price) AS min_price, MAX(price) AS max_price FROM products GROUP BY category;Каждая категория получит свою строку с пятью числами.
Агрегаты с DISTINCT
Все агрегаты принимают
DISTINCT:SELECT COUNT(DISTINCT customer_id) AS unique_customers, SUM(DISTINCT amount) AS sum_unique_amounts FROM orders;COUNT(DISTINCT customer_id)— самый частый из них, для подсчёта уникальных юзеров.SUM(DISTINCT)редко имеет смысл (обычно нуженSUM, а не сумма уникальных значений).Поведение с NULL
COUNT(*)COUNT(column)SUM(column)AVG(column)MIN/MAX(column)Если хочешь, чтобы пустая выборка возвращала 0, а не NULL — оборачивай в
COALESCE:SELECT COALESCE(SUM(amount), 0) FROM orders WHERE customer_id = 999;Частые ошибки новичков
1.
COUNT(*)vsCOUNT(column). На таблицах, где половина строк имеют NULL в колонке, разница огромная.COUNT(*) = 100,COUNT(email) = 47— это нормально и значит «47 из 100 заполнили email».2.
SUM/AVGпустой выборки = NULL, не 0.SELECT SUM(amount) FROM orders WHERE status = 'never'— если ни одной строки,SUMвернётNULL. В коде приложения тогда упадёт что-то вродеnull + 5. Обернул вCOALESCE(SUM(amount), 0)— спасся.3. Агрегат + не-агрегатная колонка без GROUP BY.
SELECT name, COUNT(*) FROM usersупадёт. Если хочешь «имя и счёт», нуженGROUP BY name. Если хочешь только счёт — безname.4. AVG на целых числах. В MySQL
AVG(INT)возвращает float, теряя точность на больших суммах. В Postgres —NUMERIC, точное. Если есть подозрение на потерю точности — каст:AVG(amount::NUMERIC).5. MIN/MAX по тексту даёт алфавитный, не числовой порядок.
MIN(version)для'1', '10', '2'даст'1', аMAX—'2'(а не'10'). Если версии — числа, храни числами или паддуй нулями.6. COUNT(*) на огромной таблице медленный. Postgres честно бежит по всем строкам (или индексу) — на 100M строк это секунды. Если нужна примерная цифра — есть быстрые приёмы (
pg_class.reltuples, статистика планировщика). Точная — только полный COUNT.Мини-резюме
COUNT(*)— все строки.COUNT(column)— не-NULL.COUNT(DISTINCT column)— уникальные.SUM,AVG— складывают / усредняют, NULL пропускают. Пустая выборка →NULL, не0.MIN,MAX— работают с числами, датами, строками (по алфавиту). NULL пропускают.COALESCE(SUM(x), 0).SELECTс агрегатами все остальные колонки должны быть вGROUP BYлибо завернуты в агрегат.