Изчисления с функциите COUNT, SUM, AVG, MIN, MAX в SQL
Към курса Анализ на данни и изготвяне на отчети с SQL
Автор: Дикран Хачикян
Функциите COUNT(), SUM(), AVG(), MIN(), MAX() в езика SQL са в групата на агрегиращите функции и присъстват в множество заявки и отчети с изчисления.
COUNT()
брои
AVG()
изчислява средно аритметично
SUM()
сумира
MIN()
намира минимум
MAX()
намира максимум
В зависимост от сървъра, който използваме в тази група може да има и други функции (проверете в документацията), но изброените в таблицата са основните функции и ги има във всички сървъри за бази данни.
Важна особеност за правилната работа на тези функции е групирането на данните в резултата.
Групиране на данните
При групиране на данните, сървъра сравнява стойностите по редовете и ако открие два или повече реда със съвпадащи стойности в съответните колони, сървъра ги представя като един ред.
За да се посочи на сървъра, че трябва да групира данните, след FROM клаузата или след WHERE клаузата, ако има такава, трябва да се постави GROUP BY.
След GROUP BY като минимум се изброяват онези колони, които са изброени и в частта SELECT без в това число да се включват агрегиращите функции.
Да разгледаме как сървъра групира данните с помощта на следващите три примера.
Пример 1:
Имаме следната данни:
и зявка:
|
1 2 3 |
SELECT col1,col2,col3, COUNT(*) FROM ... GROUP BY col1,col2,col3 |
В колона col3 всички стойности са уникални по редовете. По тази причина, сървърът няма да може да групира данните, тъй като няма два или повече реда с повтарящи се стойности по съответните колони. От това следва, че няма смисъл да се използва която и да е функция от групата на агрегиращите.
Пример 2:
Ако изключим третата колона ще имаме следните данни:
и заявка:
|
1 2 3 |
SELECT col1,col2, COUNT(*) FROM ... GROUP BY col1,col2 |
В този случай, първита два реда и последните два реда имат съвпадащи стойности по редовете, от което следва, че сървъра ще ги представи в две групи по два реда. Съответно, функцията COUNT(*) ще върне стойността 2 за всяка от групите.
Пример 3:
Ако изключим и втората колона,
и имаме заявка:
|
1 2 3 |
SELECT col1, COUNT(*) FROM ... GROUP BY col1 |
В резултата ще получим само един ред, съдържащ стойността а и функцията COUNT(*) ще върне стойността 4, тъй като са групирани 4 реда.
Важно е да запомните, че в зависимост от това кои колони включвате или изключвате от заявката, не само резултатите от агрегиращите функции ще са различни, но и смисълът на заявката се променя коренно. По тази причина, първата и най-важна стъпка при използването на която и да е агрегираща функция е да намерим правилен отговор на въпроса „Как трябва да се групират данните за да се получи желаният резултат?“.
Можете да видите практически примери в използването на агрегиращите функции и вземането на решение за групиране на данните в това видео:
Във видеото е използвана базата данни HR, която се инсталира заедно със сървъра Oracle XE. Можете да свалите безплатна версия от тук.
Заявките използвани във видеото:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--създаване на таблицата employees_data CREATE TABLE employees_data AS SELECT t1.department_name , t2.first_name , t2.last_name , t3.job_title , t2.salary FROM departments t1 INNER JOIN employees t2 ON t2.department_id = t1.department_id INNER JOIN jobs t3 ON t2.job_id = t3.job_id |
|
1 2 3 |
--преглед на данните в таблицата SELECT * FROM employees_data |
|
1 2 3 4 |
SELECT department_name , last_name , salary FROM employees_data |
|
1 2 3 4 5 |
--в кой отдел на фирмата, колко човека работят SELECT department_name , COUNT(*) NumEmployees FROM employees_data GROUP BY department_name |
|
1 2 3 4 5 6 |
--в кой отдел на фирмата, колко човека работят на една и съща позиция SELECT department_name , job_title , COUNT(*) NumEmployees FROM employees_data GROUP BY department_name, job_title |
|
1 2 3 4 5 6 7 8 9 |
--за всеки отдел на фирмата, коя е минималната, средната и максималната заплата, --както и сума на заплатите за отдела SELECT department_name , MIN(salary) MinSalary , AVG(salary) AvgSalary , MAX(salary) MaxSalary , SUM(salary) SumSalary FROM employees_data GROUP BY department_name |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
--по отдели във фирмата, за всяка позиция, коя е минималната, средната и максималната --заплата, сумата от заплатите на работещите на дадена позиция --колко човека работят на дадената позиция SELECT department_name , job_title , MIN(salary) MinSalary , TO_CHAR(AVG(salary), '99999.99') AvgSalary , MAX(salary) MaxSalary , SUM(salary) SumSalary , COUNT(*) NumEmployees FROM employees_data GROUP BY department_name, job_title |
Искате да научите повече за заявките в SQL? Вижте още за предстоящия курс Анализ на данни и изготвяне на отчети с SQL