Агрегатни функции в MySQL

Агрегираните функции са всичко

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

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

1) БРОЯ
2) СУМА
3) AVG
4) МИН
5) МАКС

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

От гледна точка на бизнеса различните организационни нива имат различни информационни изисквания. Мениджърите от най-високо ниво обикновено се интересуват да знаят цели цифри и не са необходими отделните подробности.

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

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

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

Ние лесно създаваме горните отчети, като използваме агрегатни функции.

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

COUNT функция

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

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

Показаната по-долу таблица показва данни в таблицата с филми под наем

референтен_ номер	дата на транзакцията	дата_на_връщане	членски_ номер	movie_id	movie_ върнат
11	20-06-2012	NULL	1	1	0
12	22-06-2012	25-06-2012	1	2	0
13	22-06-2012	25-06-2012	3	2	0
14	21-06-2012	24-06-2012	2	2	0
15	23-06-2012	NULL	3	3	0

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

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

Изпълнение на горната заявка в MySQL работна маса срещу myflixdb ни дава следните резултати.

COUNT('movie_id')



3

DISTINCT Ключова дума

Ключовата дума DISTINCT, която ни позволява да пропускаме дубликати от нашите резултати. Това се постига чрез групиране на подобни стойности заедно.

За да оцените концепцията за Distinct, нека изпълним проста заявка

SELECT `movie_id` FROM `movierentals`;




movie_id


1


2


2


2


3

Сега нека изпълним същата заявка с отделната ключова дума-

SELECT DISTINCT `movie_id` FROM `movierentals`;

Както е показано по-долу, distinct пропуска дублиращи се записи от резултатите.

movie_id



1


2


3

MIN функция

Функцията MIN връща най-малката стойност в указаното поле на таблицата.

Като пример, да предположим, че искаме да знаем годината, в която е пуснат най-старият филм в нашата библиотека, можем да използваме MySQLфункция MIN, за да получите желаната информация.

Следното запитване ни помага да постигнем това

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

Изпълнение на горната заявка в MySQL workbench срещу myflixdb ни дава следните резултати.

MIN('year_released')



2005

Функция MAX

Точно както подсказва името, функцията MAX е противоположна на функцията MIN. то връща най-голямата стойност от указаното поле на таблицата.

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

Следващият пример връща последната издадена филмова година.

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

Изпълнение на горната заявка в MySQL workbench с помощта на myflixdb ни дава следните резултати.

MAX('year_released')



2012

Функция SUM

Да предположим, че искаме отчет, който дава общата сума на извършените досега плащания. Можем да използваме MySQL SUM функция, която връща сумата от всички стойности в указаната колона. SUM работи само с числови полета. Нулевите стойности се изключват от върнатия резултат.

Следващата таблица показва данните в таблицата с плащания-

плащане_ид	членски_ номер	дата на плащане_	описание	сума_ платена	външен_референтен_номер
1	1	23-07-2012	Плащане за наем на филм	2500	11
2	1	25-07-2012	Плащане за наем на филм	2000	12
3	3	30-07-2012	Плащане за наем на филм	6000	NULL

Заявката, показана по-долу, получава всички извършени плащания и ги сумира, за да върне един резултат.

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

Изпълнение на горната заявка в MySQL workbench срещу myflixdb дава следните резултати.

SUM('amount_paid')



10500

AVG функция

MySQL AVG функция връща средната стойност на стойностите в определена колона. Точно като функцията SUM, тя работи само с числови типове данни.

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

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

Изпълнение на горната заявка в MySQL работна маса, ни дава следните резултати.

AVG('amount_paid')



3500

Главоблъсканица

Мислите, че агрегатните функции са лесни. Опитайте това!

Следващият пример групира членовете по име, отчита общия брой плащания, средната сума на плащането и общия сбор на сумите на плащането.

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 ни дава следните резултати.

Oбобщение

• MySQL поддържа всичките пет (5) стандартни ISO агрегатни функции COUNT, SUM, AVG, МИН и МАКС.
• SUM и AVG функциите работят само с числови данни.
• Ако искате да изключите дублиращи се стойности от резултатите от обобщената функция, използвайте ключовата дума DISTINCT. Ключовата дума ALL включва дори дубликати. Ако не е посочено нищо, ALL се приема за стандартно.
• Агрегираните функции могат да се използват заедно с други SQL клаузи, като напр ГРУПИРАЙ ПО