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

Какво представляват агрегатните функции в MySQL?

An агрегатна функция чете много редове от една колона и ги свива в една стойност. Агрегатните функции са свързани със следното:

• Извършване на изчисления на множество редове
• От една колона от таблица
• И връща една единствена стойност.

Стандартът ISO определя пет (5) агрегатни функции, а именно:

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

Едно правило важи за всичките пет: Агрегираните функции игнорират NULL стойности. COUNT(*) е единственото изключение и по-долу ще разгледаме защо.

Защо да използвате агрегатни функции

Различните организационни нива имат различни информационни изисквания. Мениджърите от висше ниво обикновено се интересуват от цели цифри, а не от отделни детайли.

Агрегираните функции ни позволяват лесно да произвеждаме обобщени данни от нашата база данни.

Например, от нашата база данни myflix, ръководството може да изисква следните отчети:

• Най-малко наети филми.
• Най-наемани филми.
• Среден брой пъти, в които всеки филм е нает за един месец.

Всички гореспоменати отчети идват от агрегатни функции. Нека разгледаме всеки от тях подробно.

COUNT функция

Функцията COUNT връща общия брой стойности в посоченото поле, както за числови, така и за нечислови типове данни. Както всяка агрегатна функция, COUNT(колона) изключва NULL стойности.

COUNT(*) е специална форма, която връща броя на всички редове в таблица. Тя също така брои NULLs и дубликати, защото брои редове, а не стойности.

Таблицата movierentals съдържа следните данни:

Да предположим, че искаме да получим броя пъти, в които филмът с идентификатор 2 е бил нает.

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

Изпълнението на това в MySQL Workbench срещу 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(`платена_сума`, 0)), когато липсваща стойност означава нула.

Практически пример: Комбиниране на агрегиращи функции с 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 препратка към агрегатна функция.

Въпроси и Отговори