Агрегатные функции: считаем, суммируем и находим полезные числа

Возвращаемся к PostgreSQL.

В предыдущем уроке ты изучил JOIN.

Ты научился читать данные из связанных таблиц.

Очень важно.

Теперь мы можем задавать PostgreSQL вопросы вроде:

Покажи продукты с названиями категорий.
Покажи заказы с именами клиентов.
Покажи посты с именами авторов.

Отлично.

Но иногда мы не хотим видеть каждую отдельную строку.

Иногда нам нужен ответ.

Например:

Сколько продуктов у нас есть?
Какая общая стоимость всех продуктов?
Какая средняя цена?
Какой продукт самый дешёвый?
Какой продукт самый дорогой?
Сколько продуктов в каждой категории?

Вот здесь помогают агрегатные функции.

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

Очень вежливо.

Очень продуктивно.

Как уставший бухгалтер с суперсилой.

Что ты изучишь

В этом уроке ты изучишь:

• что такое агрегатные функции;
• как использовать COUNT;
• как использовать SUM;
• как использовать AVG;
• как использовать MIN;
• как использовать MAX;
• какая разница между COUNT(*) и COUNT(column);
• как NULL влияет на агрегатные функции;
• как использовать GROUP BY;
• как считать строки по группам;
• как агрегировать данные вместе с JOIN;
• как использовать HAVING;
• типичные ошибки в агрегатных запросах.

К концу этого урока ты сможешь задавать PostgreSQL полезные практические вопросы.

Не только:

Покажи мне всё.

А так:

Подведи итог этому хаосу и скажи, что здесь важно.

Это большой шаг вперёд.

База данных перестаёт быть просто коробкой для хранения данных.

Она становится помощником, который умеет считать.

А это полезно.

Потому что люди не всегда хорошо считают.

Особенно после четвёртого кофе.

Подготовь базу данных

Открой PostgreSQL:

sudo -iu postgres psql

Подключись к своей базе данных:

\c learning_postgresql

Если базы ещё нет, создай её:

CREATE DATABASE learning_postgresql;

Потом подключись:

\c learning_postgresql

Теперь удали старые таблицы, если они существуют:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Мы удаляем products первой, потому что эта таблица зависит от categories.

PostgreSQL помнит связи.

Иногда лучше, чем люди.

Создаём таблицы

Создай таблицу categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Создай таблицу products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Эта таблица содержит:

• название продукта;
• цену;
• количество;
• категорию.

Это уже похоже на что-то реальное.

Ещё не полноценная система магазина.

Но достаточно, чтобы PostgreSQL немного поработал.

Здорово.

Образовательно.

Без жалоб на переработки.

Добавляем данные

Добавь категории:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office'),
  ('Kitchen');

Добавь продукты:

INSERT INTO products (name, price, quantity, category_id)
VALUES
  ('Laptop', 900.00, 5, 1),
  ('Mouse', 25.00, 30, 1),
  ('Keyboard', 70.00, 15, 1),
  ('Desk Chair', 150.00, 8, 2),
  ('Bookshelf', 120.00, 4, 2),
  ('Notebook', 5.00, 100, 3),
  ('Pen', 2.00, 200, 3),
  ('Whiteboard', 80.00, 3, 3);

Проверь данные:

SELECT * FROM categories;
SELECT * FROM products;

Обрати внимание на одну вещь.

Категория Kitchen не имеет продуктов.

Это пригодится позже.

Бедная Kitchen.

Ждёт ложку.

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

Агрегатные функции вычисляют один результат на основе многих строк.

Например, в таблице много продуктов.

Но мы можем спросить:

Сколько есть продуктов?

PostgreSQL может вернуть одно число.

Это и есть агрегация.

Самые распространённые агрегатные функции:

COUNT
SUM
AVG
MIN
MAX

Они отвечают на вопросы вроде:

Сколько?
Сколько всего?
Какое среднее значение?
Какое самое маленькое значение?
Какое самое большое значение?

Это очень полезно в dashboard, отчётах, админ-панелях, API, счетах, статистике и бизнес-инструментах.

То есть везде, где люди хотят видеть числа.

А потом спорить о них на встречах.

Классика.

COUNT

COUNT считает строки.

Выполни:

SELECT COUNT(*)
FROM products;

Результат:

count
-----
8

COUNT(*) означает:

Посчитай все строки.

Ему не важна конкретная колонка.

Он просто считает строки.

Просто.

Сильно.

Без драмы.

COUNT с псевдонимом

Колонка результата называется count.

Это нормально.

Но мы можем сделать результат понятнее:

SELECT COUNT(*) AS total_products
FROM products;

Результат:

total_products
--------------
8

Гораздо лучше.

Псевдонимы делают результаты проще для понимания.

Будущий ты будет благодарен.

Будущий ты уже устал.

Помоги ему.

COUNT для колонки

Можно также считать конкретную колонку:

SELECT COUNT(category_id) AS products_with_category
FROM products;

Этот запрос считает строки, где category_id не является NULL.

В наших данных каждый продукт имеет категорию.

Поэтому результат:

products_with_category
----------------------
8

Но здесь есть важная разница.

COUNT(*) считает все строки.

COUNT(column) считает только те строки, где эта колонка не является NULL.

Это очень важно.

Потому что NULL — это не ноль.

NULL означает отсутствующее или неизвестное значение.

PostgreSQL относится к этому внимательно.

И правильно делает.

NULL — это не число.

Это маленький призрак базы данных.

Пример с NULL

Добавь продукт без категории:

INSERT INTO products (name, price, quantity, category_id)
VALUES ('Mystery Box', 50.00, 2, NULL);

Теперь выполни:

SELECT COUNT(*) AS total_products
FROM products;

Получишь:

total_products
--------------
9

Теперь выполни:

SELECT COUNT(category_id) AS products_with_category
FROM products;

Получишь:

products_with_category
----------------------
8

Почему?

Потому что Mystery Box имеет category_id = NULL.

Поэтому COUNT(category_id) его не считает.

Это важно.

Очень важно.

Важно как база данных.

Не как “не забудь купить молоко”.

А больше как “почему мой отчёт неправильный?” важно.

SUM

SUM суммирует значения.

Например, мы можем посчитать общее количество товаров на складе:

SELECT SUM(quantity) AS total_items
FROM products;

Это складывает все значения quantity.

Пример результата:

total_items
-----------
367

Также можно посчитать общую стоимость склада.

Для каждого продукта нам нужно:

price * quantity

Потом суммируем результат:

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

Пример результата:

total_stock_value
-----------------
8945.00

Это полезно.

Теперь PostgreSQL не просто хранит данные.

Он вычисляет полезную информацию.

Очень профессионально.

Очень “кажется, кому-то нужно выставить счёт”.

AVG

AVG вычисляет среднее значение.

Пример:

SELECT AVG(price) AS average_price
FROM products;

PostgreSQL может вернуть много цифр после запятой.

Чтобы сделать результат красивее, используй ROUND:

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

Пример результата:

average_price
-------------
155.78

Точное число зависит от твоих данных.

AVG полезна для вопросов вроде:

Какая средняя цена продукта?
Какая средняя сумма заказа?
Какая средняя зарплата?
Какой средний уровень хаоса в этом проекте?

PostgreSQL ответит на первые три.

Для четвёртого нужна эмоциональная поддержка.

MIN

MIN находит самое маленькое значение.

Пример:

SELECT MIN(price) AS cheapest_price
FROM products;

Результат:

cheapest_price
--------------
2.00

Это показывает самую низкую цену.

Но не показывает, какой продукт имеет эту цену.

Для этого можно использовать ORDER BY и LIMIT:

SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 1;

Результат:

name | price
-----|------
Pen  | 2.00

MIN даёт минимальное значение.

ORDER BY с LIMIT может показать всю строку.

Оба варианта полезны.

Разные инструменты.

Та же коробка с инструментами.

MAX

MAX находит самое большое значение.

Пример:

SELECT MAX(price) AS highest_price
FROM products;

Результат:

highest_price
-------------
900.00

Чтобы найти самый дорогой продукт:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1;

Результат:

name   | price
-------|------
Laptop | 900.00

Очень хорошо.

PostgreSQL нашёл дорогого зверя.

Наверное, с RGB-подсветкой.

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

Можно использовать несколько агрегатных функций в одном запросе.

Пример:

SELECT
  COUNT(*) AS total_products,
  SUM(quantity) AS total_items,
  ROUND(AVG(price), 2) AS average_price,
  MIN(price) AS cheapest_price,
  MAX(price) AS highest_price
FROM products;

Это возвращает одну итоговую строку.

Пример:

total_products | total_items | average_price | cheapest_price | highest_price
---------------|-------------|---------------|----------------|--------------
9              | 367         | 155.78        | 2.00           | 900.00

Идеально для dashboard.

Один запрос.

Много полезных чисел.

PostgreSQL делает офисную работу и даже не просит стул.

Уважение.

GROUP BY

До этого агрегатные функции давали один результат для всей таблицы.

Но часто нам нужны результаты по группам.

Например:

Сколько продуктов есть в каждой категории?

Для этого используют GROUP BY.

Пример:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
GROUP BY category_id;

Пример результата:

category_id | products_count
------------|---------------
1           | 3
2           | 2
3           | 3
            | 1

Пустая категория означает NULL.

Это наш Mystery Box.

У него нет категории.

Очень загадочный.

Очень box.

GROUP BY с JOIN

category_id полезен для PostgreSQL.

Но люди хотят видеть названия категорий.

Поэтому используем JOIN.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Результат:

category_name | products_count
--------------|---------------
Electronics   | 3
Furniture     | 2
Office        | 3

Гораздо красивее.

Но обрати внимание.

Kitchen исчезла.

Почему?

Потому что JOIN здесь означает INNER JOIN.

Показываются только те строки, где есть совпадение.

Kitchen не имеет продуктов.

Поэтому она не появляется.

Бедная Kitchen снова.

GROUP BY с LEFT JOIN

Если мы хотим показать все категории, даже те, что без продуктов, используем LEFT JOIN.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Результат:

category_name | products_count
--------------|---------------
Electronics   | 3
Furniture     | 2
Kitchen       | 0
Office        | 3

Теперь Kitchen появилась.

Важная деталь:

Мы использовали:

COUNT(p.id)

А не:

COUNT(*)

Почему?

Потому что с LEFT JOIN, COUNT(*) посчитал бы даже строку категории, если продукта нет.

Для категории без продуктов COUNT(*) мог бы дать 1.

Это было бы неправильно.

COUNT(p.id) считает только реальные продукты.

Эта деталь спасает отчёты.

А иногда и работу.

SUM с GROUP BY

Мы можем посчитать стоимость склада по категориям.

SELECT
  c.name AS category_name,
  SUM(p.price * p.quantity) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Пример результата:

category_name | stock_value
--------------|------------
Electronics   | 6750.00
Furniture     | 1680.00
Office        | 1140.00
Kitchen       |

Kitchen не имеет продуктов.

Поэтому сумма равна NULL.

Если мы хотим показать 0 вместо NULL, можно использовать COALESCE.

SELECT
  c.name AS category_name,
  COALESCE(SUM(p.price * p.quantity), 0) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Теперь Kitchen показывает 0.

COALESCE возвращает первое значение, которое не является NULL.

То есть это:

COALESCE(SUM(p.price * p.quantity), 0)

означает:

Если сумма существует, покажи её.
Если сумма NULL, покажи 0.

Очень полезно.

Очень практично.

Очень “пожалуйста, не ломай мой dashboard”.

AVG с GROUP BY

Можем посчитать среднюю цену продукта в каждой категории:

SELECT
  c.name AS category_name,
  ROUND(AVG(p.price), 2) AS average_price
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY average_price DESC;

Пример результата:

category_name | average_price
--------------|---------------
Electronics   | 331.67
Furniture     | 135.00
Office        | 29.00
Kitchen       |

Снова Kitchen не имеет продуктов.

Поэтому средняя цена равна NULL.

И это правильно.

Невозможно посчитать среднее значение из отсутствия значений.

PostgreSQL отказывается придумывать числа.

Хорошая база данных.

HAVING

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

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

Это важно.

Допустим, мы хотим увидеть категории, где больше двух продуктов.

Неправильно:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;

Это не сработает.

Почему?

Потому что WHERE не может использовать результат агрегатной функции.

Используй HAVING:

SELECT
  category_id,
  COUNT(*) AS products_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

Результат:

category_id | products_count
------------|---------------
1           | 3
3           | 3

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

Простая идея.

Очень частый источник ошибок.

SQL любит порядок.

Люди любят угадывать.

SQL побеждает.

HAVING с JOIN

Теперь покажем названия категорий.

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
HAVING COUNT(p.id) > 2
ORDER BY products_count DESC;

Результат:

category_name | products_count
--------------|---------------
Electronics   | 3
Office        | 3

Это означает:

Покажи только категории, которые имеют больше двух продуктов.

Это полезно для отчётов.

Например:

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

HAVING нужен для фильтрации агрегированных результатов.

Не забывай о нём.

Он тихо ждёт.

Как серьёзный SQL-библиотекарь.

WHERE и HAVING вместе

Можно использовать WHERE и HAVING в одном запросе.

Пример:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
WHERE p.price >= 10
GROUP BY c.name
HAVING COUNT(p.id) >= 2
ORDER BY products_count DESC;

Это означает:

Сначала оставь только продукты с ценой не меньше 10.
Потом сгруппируй их по категориям.
Потом покажи только категории, где таких продуктов как минимум два.

Порядок имеет значение.

WHERE работает до группировки.

HAVING работает после группировки.

Запомни это, и SQL будет болеть меньше.

Не никогда.

Меньше.

Типичные ошибки

Использовать WHERE вместо HAVING

Неправильно:

SELECT category_id, COUNT(*)
FROM products
WHERE COUNT(*) > 2
GROUP BY category_id;

Правильно:

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2;

Используй WHERE для обычных строк.

Используй HAVING для групп.

Забыть GROUP BY

Неправильно:

SELECT category_id, COUNT(*)
FROM products;

PostgreSQL пожалуется, потому что category_id не агрегируется и не группируется.

Правильно:

SELECT category_id, COUNT(*)
FROM products
GROUP BY category_id;

Если ты выбираешь обычную колонку вместе с агрегатной функцией, обычно нужен GROUP BY.

PostgreSQL строгий.

Но справедливый.

В основном.

Считать не то с LEFT JOIN

Неправильно для подсчёта продуктов по категориям:

SELECT
  c.name,
  COUNT(*) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Это может посчитать категорию без продуктов как 1.

Лучше так:

SELECT
  c.name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name;

Считай колонку из той таблицы, которую действительно хочешь считать.

Маленькая деталь.

Большая разница.

Классическая ловушка баз данных.

Практика

Посчитай все продукты:

SELECT COUNT(*) AS total_products
FROM products;

Посчитай общее количество товаров:

SELECT SUM(quantity) AS total_items
FROM products;

Посчитай общую стоимость склада:

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

Найди среднюю цену:

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

Найди самую низкую и самую высокую цену:

SELECT
  MIN(price) AS cheapest_price,
  MAX(price) AS highest_price
FROM products;

Посчитай продукты по категориям:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY c.name;

Посчитай стоимость склада по категориям:

SELECT
  c.name AS category_name,
  COALESCE(SUM(p.price * p.quantity), 0) AS stock_value
FROM categories AS c
LEFT JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
ORDER BY stock_value DESC;

Покажи только категории, где больше двух продуктов:

SELECT
  c.name AS category_name,
  COUNT(p.id) AS products_count
FROM categories AS c
JOIN products AS p
ON c.id = p.category_id
GROUP BY c.name
HAVING COUNT(p.id) > 2;

Запускай запросы.

Меняй числа.

Добавляй продукты.

Удаляй продукты.

Смотри, как меняются результаты.

Это самая интересная часть.

Ну хорошо.

Интересная для базы данных.

Другой вид веселья.

Мини-задание

Создай две таблицы:

customers
orders

Правила:

• один клиент может иметь много заказов;
• каждый заказ принадлежит одному клиенту;
• имя клиента обязательно;
• сумма заказа должна быть ноль или больше;
• некоторые клиенты могут не иметь заказов.

Создай таблицы:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Добавь данные:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia'),
  ('Luca');

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2),
  (200.00, 2),
  (15.00, 2),
  (80.00, 3);

Теперь напиши запросы, которые:

• посчитают все заказы;
• посчитают общие продажи;
• посчитают среднюю сумму заказа;
• найдут самый маленький заказ;
• найдут самый большой заказ;
• покажут, сколько потратил каждый клиент;
• покажут количество заказов каждого клиента;
• покажут клиентов без заказов;
• покажут только клиентов, которые потратили больше 100.

Подсказка для клиентов без заказов:

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
GROUP BY c.name
HAVING COUNT(o.id) = 0;

Это SQL из реального мира.

Клиенты.

Заказы.

Суммы.

Отчёты.

Именно для таких вещей базы данных и созданы.

Как электронные таблицы.

Но с большей дисциплиной.

И без загадочных объединённых ячеек.

Итог

Сегодня ты изучил:

• агрегатные функции вычисляют результаты из многих строк;
• COUNT(*) считает все строки;
• COUNT(column) считает только значения, которые не являются NULL;
• SUM суммирует значения;
• AVG вычисляет среднее значение;
• MIN находит самое маленькое значение;
• MAX находит самое большое значение;
• GROUP BY создаёт группы;
• агрегатные функции можно использовать вместе с JOIN;
• LEFT JOIN полезен, когда нужно показать группы с нулевым количеством связанных строк;
• COUNT(p.id) безопаснее, чем COUNT(*), при подсчёте связанных строк;
• COALESCE может заменить NULL на более удобное значение;
• WHERE фильтрует строки перед группировкой;
• HAVING фильтрует группы после группировки.

Это очень важный урок.

Теперь ты можешь анализировать данные.

Не только читать их.

Ты можешь создавать отчёты.

Ты можешь создавать dashboard.

Ты можешь отвечать на бизнес-вопросы.

Ты можешь заставить PostgreSQL считать вещи, пока ты пьёшь кофе.

Вот это цивилизация.

Следующий урок

В следующем уроке мы изучим индексы и базовую производительность.

Мы увидим, почему некоторые запросы становятся медленными.

Мы изучим, как индексы помогают PostgreSQL быстрее находить данные.

Потому что когда база данных имеет десять строк, всё быстро.

Когда она имеет десять миллионов строк, PostgreSQL начинает задавать серьёзные вопросы.

И ты тоже должен.