Пошук уроків, статей та іншого контенту
Обчислите кількість, суму, середнє, мінімум і максимум значень за допомогою агрегатних функцій.
Агрегатні функції обробляють набір рядків і повертають одне підсумкове значення. Вони дають змогу:
порахувати кількість рядків;
знайти суму значень;
обчислити середнє;
знайти мінімальне або максимальне значення.
Найчастіше агрегатні функції використовують разом із SELECT:
SELECT aggregate_function(column_name)
FROM table_name;Наприклад:
SELECT COUNT(*)
FROM orders;Цей запит повертає загальну кількість рядків у таблиці orders.
Нижче наведено самодостатній приклад. Тимчасова таблиця існує лише протягом поточного підключення до PostgreSQL.
CREATE TEMP TABLE orders (
id integer,
customer_name text,
amount numeric(10, 2),
status text
);
INSERT INTO orders (id, customer_name, amount, status)
VALUES
(1, 'Олена', 1200.00, 'paid'),
(2, 'Андрій', 850.50, 'paid'),
(3, 'Марія', 430.00, 'pending'),
(4, 'Ігор', NULL, 'cancelled'),
(5, 'Софія', 1999.99, 'paid');У стовпці amount одного замовлення значення дорівнює NULL. Це дасть змогу побачити, як агрегатні функції працюють із відсутніми значеннями.
COUNT: кількість значеньФункція COUNT підраховує кількість рядків або значень.
COUNT(*)COUNT(*) повертає кількість усіх рядків, незалежно від значень у стовпцях:
SELECT COUNT(*) AS total_orders
FROM orders;Результат:
total_orders
--------------
5Навіть рядок із amount = NULL враховується, оскільки рядок існує.
COUNT(column)COUNT(column) рахує лише ненульові значення вказаного стовпця:
SELECT COUNT(amount) AS orders_with_amount
FROM orders;Результат:
orders_with_amount
--------------------
4Рядок із amount = NULL не враховується.
Це важлива відмінність:
SELECT
COUNT(*) AS all_rows,
COUNT(amount) AS non_null_amounts
FROM orders;Результат:
all_rows | non_null_amounts
----------+------------------
5 | 4COUNT(DISTINCT column)Щоб порахувати лише унікальні ненульові значення, використовуйте DISTINCT:
SELECT COUNT(DISTINCT status) AS status_count
FROM orders;У цьому прикладі результатом буде 3: paid, pending і cancelled.
SUM: сума значеньФункція SUM обчислює суму ненульових значень:
SELECT SUM(amount) AS total_amount
FROM orders;Результат:
total_amount
--------------
4480.49NULL не додається до суми.
Якщо всі значення стовпця дорівнюють NULL або після фільтрації не залишилося рядків, SUM повертає NULL, а не 0:
SELECT SUM(amount) AS total_amount
FROM orders
WHERE status = 'unknown';Щоб отримати нуль замість NULL, використовуйте COALESCE:
SELECT COALESCE(SUM(amount), 0) AS total_amount
FROM orders
WHERE status = 'unknown';AVG: середнє значенняФункція AVG обчислює середнє арифметичне ненульових значень:
SELECT AVG(amount) AS average_amount
FROM orders;У розрахунок потрапляють лише чотири ненульові значення:
(1200.00 + 850.50 + 430.00 + 1999.99) / 4NULL не вважається нулем і не збільшує кількість значень, на яку ділиться сума.
За потреби результат можна округлити функцією ROUND:
SELECT ROUND(AVG(amount), 2) AS average_amount
FROM orders;Для цілих чисел PostgreSQL також повертає точне середнє значення, а не просто ціле число:
SELECT AVG(id) AS average_id
FROM orders;MIN і MAX: мінімальне та максимальне значенняMIN повертає найменше значення, а MAX — найбільше:
SELECT
MIN(amount) AS minimum_amount,
MAX(amount) AS maximum_amount
FROM orders;Результат:
minimum_amount | maximum_amount
----------------+----------------
430.00 | 1999.99NULL значення ігноруються.
Агрегатні функції можна застосовувати не лише до чисел. Наприклад, для тексту MIN і MAX порівнюють значення за порядком сортування:
SELECT
MIN(customer_name) AS first_customer,
MAX(customer_name) AS last_customer
FROM orders;Найчастіше MIN і MAX для тексту використовують обережно, оскільки результат означає перше й останнє значення за правилами сортування, а не найкоротше чи найдовше ім’я.
В одному SELECT можна обчислити кілька показників:
SELECT
COUNT(*) AS order_count,
COUNT(amount) AS orders_with_amount,
COALESCE(SUM(amount), 0) AS total_amount,
ROUND(AVG(amount), 2) AS average_amount,
MIN(amount) AS minimum_amount,
MAX(amount) AS maximum_amount
FROM orders
WHERE status = 'paid';Запит спочатку залишає лише оплачені замовлення, а потім обчислює агрегати для цього набору рядків.
Порядок обробки має значення:
WHERE відфільтровує рядки.
Агрегатна функція обчислює результат для рядків, що залишилися.
SELECT повертає підсумок.
Без групування агрегатна функція повертає один результат для всієї вибірки. Щоб отримати окремий результат для кожного значення, використовуйте GROUP BY.
Наприклад, підрахуємо кількість замовлень і їхню суму для кожного статусу:
SELECT
status,
COUNT(*) AS order_count,
COALESCE(SUM(amount), 0) AS total_amount,
ROUND(AVG(amount), 2) AS average_amount
FROM orders
GROUP BY status
ORDER BY status;Результат матиме по одному рядку для кожного статусу:
status | order_count | total_amount | average_amount
------------+-------------+--------------+----------------
cancelled | 1 | 0.00 |
paid | 3 | 4050.49 | 1350.16
pending | 1 | 430.00 | 430.00Для групи cancelled значення amount є NULL, тому:
COUNT(*) повертає 1, бо рядок існує;
COUNT(amount) повернув би 0;
SUM(amount) повертає NULL;
COALESCE(SUM(amount), 0) перетворює цей результат на 0;
AVG(amount) повертає NULL, бо немає жодного числового значення.
HAVINGWHERE фільтрує окремі рядки до групування. HAVING фільтрує вже сформовані групи.
Наприклад, залишимо лише статуси, для яких загальна сума оплачених замовлень перевищує 1000:
SELECT
status,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY status
HAVING SUM(amount) > 1000;У HAVING можна використовувати агрегатні функції, оскільки умова перевіряється для кожної групи.
Порівняйте призначення команд:
-- Фільтрує окремі рядки до обчислення агрегатів
SELECT status, SUM(amount)
FROM orders
WHERE status <> 'cancelled'
GROUP BY status;
-- Фільтрує групи після обчислення агрегатів
SELECT status, SUM(amount)
FROM orders
GROUP BY status
HAVING SUM(amount) > 1000;NULLNULL означає відсутнє або невідоме значення. Для більшості агрегатних функцій PostgreSQL діє правило: NULL не враховується.
SELECT
COUNT(*) AS all_rows,
COUNT(amount) AS non_null_amounts,
SUM(amount) AS amount_sum,
AVG(amount) AS amount_average,
MIN(amount) AS amount_minimum,
MAX(amount) AS amount_maximum
FROM orders;Винятково важливо пам’ятати:
COUNT(*) рахує всі рядки;
COUNT(column) рахує лише ненульові значення;
SUM, AVG, MIN і MAX ігнорують NULL;
якщо для обчислення немає жодного значення, результатом зазвичай буде NULL;
COALESCE дає змогу замінити NULL на потрібне значення.
COUNT(column) замість COUNT(*)Якщо потрібно порахувати рядки, а не заповнені значення конкретного стовпця, використовуйте COUNT(*):
-- Рахує всі замовлення
SELECT COUNT(*)
FROM orders;
-- Рахує лише замовлення з указаною сумою
SELECT COUNT(amount)
FROM orders;NULL у сумі дорівнює нулюNULL не додається як число 0. Якщо результат має бути числовим нулем, явно використовуйте COALESCE:
SELECT COALESCE(SUM(amount), 0)
FROM orders
WHERE status = 'unknown';WHEREТакий запит є неправильним:
-- WHERE не може фільтрувати результат SUM
SELECT status, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000
GROUP BY status;Для умов на агрегатні значення використовуйте HAVING:
SELECT status, SUM(amount) AS total_amount
FROM orders
GROUP BY status
HAVING SUM(amount) > 1000;AVG(amount) рахує середнє лише за ненульовими значеннями. Якщо потрібно враховувати відсутню суму як нуль, це треба зробити явно:
SELECT AVG(COALESCE(amount, 0)) AS average_with_zero
FROM orders;Це інший розрахунок, ніж:
SELECT AVG(amount) AS average_without_nulls
FROM orders;У першому випадку NULL замінюється на 0, а в другому — повністю ігнорується.
COUNT(*) рахує всі рядки.
COUNT(column) рахує лише ненульові значення стовпця.
SUM(column) обчислює суму.
AVG(column) обчислює середнє арифметичне.
MIN(column) знаходить мінімальне значення.
MAX(column) знаходить максимальне значення.
Агрегатні функції зазвичай ігнорують NULL.
COALESCE допомагає замінити NULL, наприклад, на 0.
GROUP BY обчислює агрегати окремо для кожної групи.
WHERE фільтрує рядки до агрегації, а HAVING — групи після агрегації.