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

Повертаємось до 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 починає ставити серйозні питання.

І ти теж маєш.