MySQL Агрегатні функції: SUM, COUNT, AVG & МАКС

Що таке агрегатні функції в MySQL?

An агрегатна функція зчитує багато рядків одного стовпця та згортає їх в одне значення. Агрегатні функції полягають у:

• Виконання обчислень у кількох рядках
• З одного стовпця таблиці
• І повертає одне значення.

Стандарт ISO визначає п'ять (5) агрегатних функцій, а саме:

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

Одне правило застосовується до всіх п'яти: Агрегатні функції ігнорують значення NULLCOUNT(*) — це єдиний виняток, і нижче ми розглянемо, чому.

Навіщо використовувати агрегатні функції

Різні рівні організації мають різні вимоги до інформації. Керівників вищої ланки зазвичай цікавлять цілі цифри, а не окремі деталі.

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

Наприклад, з нашої бази даних myflix керівництву можуть знадобитися такі звіти:

• Найменше взяті напрокат фільми.
• Більшість прокатних фільмів.
• Середня кількість разів, коли кожен фільм береться в прокат протягом місяця.

Усі вищезгадані звіти отримані з агрегатних функцій. Давайте розглянемо кожну з них детальніше.

функція COUNT

Функція COUNT повертає загальну кількість значень у вказаному полі як для числових, так і для нечислових типів даних. Як і кожна агрегатна функція, COUNT(column) виключає значення NULL.

COUNT(*) – це спеціальна форма, яка повертає кількість усіх рядків у таблиці. Вона також підраховує NULLs і дублікати, оскільки він підраховує рядки, а не значення.

Таблиця movierentals містить такі дані:

Припустимо, що нам потрібно отримати кількість разів, коли фільм з ідентифікатором 2 був взятий в прокат.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Виконання цього в MySQL Верстак проти myflixdb повертає 3, оскільки три рядки містять movie_id 2.

DISTINCT ключове слово

COUNT відповідає на «скільки». Наступне питання зазвичай «скільки різний «оні», і саме для цього існує DISTINCT.

Ключове слово DISTINCT виключає дублікати з наших результатів за групамиping однакові значення разом, саме так, як показано на ілюстрації вище.

Спочатку виконаємо простий запит.

SELECT `movie_id` FROM `movierentals`;

Тепер той самий запит з ключовим словом DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT пропускає дублікати записів:

COUNT проти COUNT(*) проти COUNT(DISTINCT): який з них слід використовувати?

DISTINCT також можна розмістити всередині агрегатна функція, і саме тут більшість початківців програють track з яких рядків фактично підраховано. Чотири форми нижче працюють з однією й тією ж таблицею movierentals з п'ятьма рядками, яку було показано раніше, але не всі вони повертають однакове число. Різниця зводиться до двох питань: чи підраховує форма рядки чи значення, і чи зберігає вона дублікати?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Порада: Використовуйте COUNT(*) для підрахунку рядків, COUNT(стовпець), коли NULL має означати «не застосовується», та COUNT(DISTINCT стовпець) для унікальних значень. Протилежністю DISTINCT є ALL — значення за замовчуванням, тому його рідко записують.

Функція MIN

Функція MIN повертає найменше значення у вказаному полі таблиці.

Припустимо, нам потрібен рік виходу найстарішого фільму в нашій бібліотеці. MySQLФункція MIN дає нам це.

SELECT MIN(`year_released`) FROM `movies`;

Результат:

Функція MAX

Як випливає з назви, функція MAX є протилежністю функції MIN. Це повертає найбільше значення з указаного поля таблиці.

Припустимо, нам потрібен рік виходу останнього фільму з нашої бази даних. Наступний приклад повертає його.

SELECT MAX(`year_released`) FROM `movies`;

Результат:

Функція SUM

MIN та MAX вибирають існуюче значення зі стовпця. SUM та AVG обчисліть нове число з усього стовпця.

Припустимо, нам потрібна загальна сума платежів, здійснених на даний момент. MySQL SUM функція повертає суму всіх значень у вказаному стовпці. SUM працює лише з числовими полями та Значення NULL виключаються з результату.

У наступній таблиці наведено дані з таблиці платежів.

Наведений нижче запит отримує всі здійснені платежі та підсумовує їх в один результат: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Результат:

AVG функція

Команда MySQL AVG функція повертає середнє значення у вказаному стовпці. Так само, як і функція SUM, це працює лише з числовими типами даних.

Припустимо, що нам потрібно знайти середню сплачену суму. Ми можемо використати наступний запит, який ділить загальну суму 10500 на три рядки платежів, що не є NULL.

SELECT AVG(`amount_paid`) FROM `payments`;

Результат:

⚠️ Попередження: AVG ділиться на кількість не-NULL рядків, а не на кількість рядків у таблиці. Значення NULL пропускається, а не вважається нулем, що непомітно збільшує середнє значення. Використовуйте AVG(IFNULL(`amount_paid`, 0)), коли відсутнє значення дорівнює нулю.

Практичний приклад: Поєднання агрегатних функцій з GROUP BY

Кожна з наведених вище функцій повертала одне число для всієї таблиці. Додавання GROUP BY речення повертає одну цифру на групу натомість — і саме так створюються справжні звіти.

У наступному прикладі учасники групуються за іменем, а потім підраховується загальна кількість платежів, середня сума платежу та загальна сума сум платежів для кожного учасника.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Виконання наведеного вище прикладу в MySQL Workbench дає нам такі результати.

Запит об'єднує дві таблиці в реченні WHERE — старішому стилі з'єднання комою. Сучасний код записує ту саму логіку, що й явний оператор ВНУТРІШНЄ З’ЄДНАННЯ … УВІМК.Зверніть також увагу, що кожен неагрегований стовпець у списку SELECT має відображатися в GROUP BY, або MySQL 5.7 та пізніших версій відхиляють запит за параметром ONLY_FULL_GROUP_BY. Див. офіційний MySQL посилання на агрегатну функцію.

Поширені запитання