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

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

А те са:

• SUM();
• MAX();
• MIN();
• AVG();
• COUNT();

В скобите обикновено присъства име на колона, за която желаем да бъде приложена агрегатната функция.

Нека малко да разширим базата данни. Ще създадем две нови таблици. Едната ще отразява плащанията на всеки ученик. Ще съдържа информация за това кой плаща, за коя група, за кой месец, за коя година, сума и дата, на която е извършено плащането. Дргата таблица ще бъде за месечни заплати на треньорите. Тя ще съдържа информация за това кой колко е получил, за кой месец на коя година и дата на получаване.

USE school_sport_clubs;

DROP TABLE IF EXISTS taxesPayments;
CREATE TABLE taxesPayments(
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
group_id INT NOT NULL,
paymentAmount double NOT NULL,
month TINYINT,
year YEAR,
dateOfPayment datetime not null,
CONSTRAINT FOREIGN KEY (student_id) references students(id),
CONSTRAINT FOREIGN KEY (group_id) references sportgroups(id)
);




DROP TABLE IF EXISTS salaryPayments;
CREATE TABLE salaryPayments(
id INT AUTO_INCREMENT PRIMARY KEY,
coach_id INT NOT NULL,
month TINYINT,
year YEAR,
salaryAmount double,
dateOfPayment datetime not null,
CONSTRAINT FOREIGN KEY (coach_id) references coaches(id),
UNIQUE KEY(`coach_id`,`month`,`year`)
);

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

Датата на плащането нека бъде навсякъде днешна (използваме фукнцията now()-връща днешна дата), макар и за старите плащания.

• COUNT(); – връща броя на редовете, в които има ненулеви стойности.

Например – трябва ни броят на всички групи, които си имат треньори:

SELECT COUNT(coach_id) as CountOFSportGroupsWithCoaches
FROM sportgroups;

• SUM() – Пресмята и връща сумата от стойностите в дадена колона

Например: Нека изведем сумата на всички такси на ученик с  id = 1.

SELECT SUM(paymentAmount) as SumOfStudentPayment
FROM taxespayments
WHERE student_id = 1;

• MIN() и MAX()-намират най-малката или най-голямата стойност в дадена колона.

Например: Да се изведе най-минималната сума,която е плащал студент с id = 1.

SELECT MIN(paymentAmount) as MinOfStudentPayment
FROM taxespayments
WHERE student_id = 1;

• AVG() – Намира средна стойност на числата в дадена колона.

Например: Изведете средната стойност на таксите, платени за група с id = 1;

SELECT AVG(paymentAmount) as AvgOfAllPayment
FROM taxespayments
WHERE group_id = 1;

Групиране на данни – GROUP BY:

Както виждате, агрегатните функции работят с цялото множество от стойности на колоните, които са им подадени и съответно изчисялват и връщат един резултат.  Как да накараме, обаче, една агрегатна функция да изчислява не цялото множество, а определени групички от него. Например искаме да изведем средната стойност на платените такси по групи – тоест в резултата ще искаме група – средна стойност на такса. Това няма как да стане без да ползваме групиране. Ще трябва да укажем на агрегатната функция някакъв критерий, на който тя да раздели множеството от стойности в колоната на отделни подмножества, да се изпълни над всяко едно от тях по отделно и за всяко да върне резултат. За целта използваме клаузата GROUP BY. Обикновено групирането се използва в комбинация с агрегатни функции. Целта е агрегатната функция да се приложи върху множество по-малки резултатни таблици или групи, които накрая ще се обединят в една обща.

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

SELECT group_id as GroupId, AVG(paymentAmount) as AvgOfAllPaymentPerGroup
FROM taxespayments
GROUP BY group_id;

Нека изведем имената и сумите от платените такси на всички ученици по месеци през годините – тоест за всички януарски такси сумата е общо Х лв., за всички февруарски такси е Улв. и така.

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

SELECT students.id, students.name as StudentName, SUM(tp.paymentAmount) as SumOfAllPaymentPerGroup, tp.month as Month
FROM taxespayments as tp JOIN students 
ON tp.student_id = students.id
GROUP BY month, student_id

Ето и резултата – Имаме студент, сума и месец. Може да използваме и ORDER BY, за да подредим резултата. Също така може да искаме да не виждаме всички резултати, а първите 10 или 15 например. Тогава изпозлваме LIMIT. Например:

SELECT students.id, students.name as StudentName, SUM(tp.paymentAmount) as SumOfAllPaymentPerGroup, tp.month as Month
FROM taxespayments as tp JOIN students 
ON tp.student_id = students.id
GROUP BY month, student_id
ORDER BY StudentName
LIMIT 15;

Нека разширим задачата. Интересуват ни само студенти с такси по месеци над 1000 лева.

Как да разрешим този проблем? Не можем да сложим WHERE клауза, защото сумата няма да се е изчислила по време на изпълнението на where клаузата (тя се изпълнява първа). Трябва да наложим ограничения, но след като се е изпълнила агрегатната функция. Това се прави с клауза HAVING

 

Клауза HAVING:

Използва се за финално филтриране на данните в резултата, след изпълнението на агрегатната функция. Условията, които касаят резултат от агрегатна функция, се слагат в HAVING, другите ограничаващи условия може да се сложат също в HAVING, но не е добра практика, тъй като те ще се проверят и ще ограничат само и единствено крайната резултатна таблица и по време на самата заявка агрегатната функция ще работи с пълното множество от данни, съединенията ще се правят между пълни множества и т.н.  Затова ползвайте правилото – ограничаващите условия в WHERE, а всички, които касаят резултат от функция – в HAVING.

SELECT students.id, students.name as StudentName, SUM(tp.paymentAmount) as SumOfAllPaymentPerGroup, tp.month as Month
FROM taxespayments as tp JOIN students 
ON tp.student_id = students.id
group by month, student_id
HAVING SumOfAllPaymentPerGroup >1000;

Даниел Джолев