Изчисления с функциите COUNT, SUM, AVG, MIN, MAX в SQL

Автор: Дикран Хачикян

Функциите COUNT(), SUM(), AVG(), MIN(), MAX() в езика SQL са в групата на агрегиращите функции и присъстват в множество заявки и отчети с изчисления.

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

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

Групиране на данните

При групиране на данните, сървъра сравнява стойностите по редовете и ако открие два или повече реда със съвпадащи стойности в съответните колони, сървъра ги представя като един ред.

За да се посочи на сървъра, че трябва да групира данните, след FROM  клаузата или след WHERE клаузата,  ако има такава, трябва да се постави GROUP BY.

След GROUP BY като минимум се изброяват онези колони, които са изброени и в частта SELECT без в това число да се включват агрегиращите функции.

Да разгледаме как сървъра групира данните с помощта на следващите три примера.

Пример 1:

Имаме следната данни:

и зявка:

SELECT col1,col2,col3, COUNT(*)
FROM ...
GROUP BY col1,col2,col3

В колона col3 всички стойности са уникални по редовете. По тази причина, сървърът няма да може да групира данните, тъй като няма два или повече реда с повтарящи се стойности по съответните колони. От това следва, че няма смисъл да се използва която и да е функция от групата на агрегиращите.

Пример 2:

Ако изключим третата колона ще имаме следните данни:

и заявка:

SELECT col1,col2, COUNT(*)
FROM ...
GROUP BY col1,col2

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

Пример 3:

Ако изключим и втората колона,

и имаме заявка:

SELECT col1, COUNT(*)
FROM ...
GROUP BY col1

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

Важно е да запомните, че в зависимост от това кои колони включвате или изключвате от заявката, не само резултатите от агрегиращите функции ще са различни, но и смисълът на заявката се променя коренно. По тази причина, първата и най-важна стъпка при използването на която и да е агрегираща функция е да намерим правилен отговор на въпроса "Как трябва да се групират данните за да се получи желаният резултат?".

Можете да видите практически примери в използването на агрегиращите функции и вземането на решение за групиране на данните в това видео:

Във видеото е използвана базата данни HR, която се инсталира заедно със сървъра Oracle XE. Можете да свалите безплатна версия от тук.

Заявките използвани във видеото:

--създаване на таблицата 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

--преглед на данните в таблицата
SELECT *
FROM employees_data

SELECT department_name
, last_name
, salary
FROM employees_data

--в кой отдел на фирмата, колко човека работят
SELECT department_name
, COUNT(*) NumEmployees
FROM employees_data
GROUP BY department_name

--в кой отдел на фирмата, колко човека работят на една и съща позиция
SELECT department_name
, job_title
, COUNT(*) NumEmployees
FROM employees_data
GROUP BY department_name, job_title

--за всеки отдел на фирмата, коя е минималната, средната и максималната заплата,
--както и сума на заплатите за отдела
SELECT department_name
, MIN(salary) MinSalary
, AVG(salary) AvgSalary
, MAX(salary) MaxSalary
, SUM(salary) SumSalary
FROM employees_data
GROUP BY department_name

--по отдели във фирмата, за всяка позиция, коя е минималната, средната и максималната
--заплата, сумата от заплатите на работещите на дадена позиция
--колко човека работят на дадената позиция
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