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

 Лабораториялық жұмыс № 6 (4 сағат)

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

1. Жұмыстың мақсаты

Изучить основы агрегации данных, функцию MAX, MIN, SUM, AVG, COUNT, группировку данных, а также  применение фильтрации в группировке данных.

2. Теориялық бөлім

Агрегатные функции используются для получения обобщающих значений. Они, в   отличие от скалярных функций, оперируют значениями столбцов множества строк.

К агрегатным функциям относятся:

–        SUM – вычисляет итог;

–        MAX – возвращает наибольшее значение;

–        MIN – возвращает наименьшее значение;

–        AVG – вычисляет среднее значение;

–        COUNT – вычисляет количество значений в столбце.

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

Вложенность не допускается.

Из агрегатных функций можно составлять любые выражения.

Для функций SUM и AVG столбец должен содержать числовые значения.

Для функций COUNT () можно указать аргумент * для подсчета всех строк без исключения.

По умолчанию вышеперечисленные пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющиеся значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT (кроме COUNT (*)). По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки. Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать. Агрегатные функции можно применить не только на всю таблицу, но также на группу значений. Для этого применяется команда GROUP BY, которая пишется после WHERE. После команды GROUP BY перечисляется название столбцов, по которым следует группировать данные. Предложение GROUP BY указывает, что результаты запроса следует разделить на группы, применить агрегатную функцию по отдельности к каждой группе и получить для каждой группы одну строку результатов.

В качестве элемента группировки должен выступать любой возвращаемый элемент, указанный в предложении SELECT, кроме значений агрегатных функций.

Если столбец, по которому производится группировка, содержит значение NULL, то строки со значением NULL составят отдельную группу.

Команда HAVING <условие> применяется для фильтрации строк, возвращаемых при использовании предложения GROUP BY. HAVING пишется после GROUP BY, имеет такой формат, как WHERE, но в качестве значения используется значение, возвращаемое агрегатными функциями.

3. Практикалық бөлім

«Страны» кестесі берілген: 

Пример 1: Вывести максимальную площадь стран: SELECT

MAX(Площадь) AS Макс_площадь

FROM Страны

Пример 2: Вывести наименьшее население стран в Африке: SELECT

MIN(Население) AS Мин_население

FROM Страны

WHERE Континент = 'Африка'

Пример 3: Вывести суммарное население стран Северной и Южной Америки: SELECT

SUM(Население) AS Суммарное_население

FROM Страны

WHERE Континент = 'Северная Америка' OR Континент = 'Южная Америка'

Пример 4: Вывести среднее население стран, кроме европейских. Результат округлить до двух знаков:

SELECT

ROUND(AVG(CAST(Население AS FLOAT)), 2) AS Среднее_население

FROM Страны

WHERE Континент != 'Европа'

Пример 5: Вывести количество стран, название которых начинается с буквы «С»:

SELECT

COUNT(*) AS Количество

FROM Страны 

WHERE LEFT(Название, 1) = 'С' 

Пример 6: Вывести количество континентов, где есть страны: SELECT

COUNT(DISTINCT Континент) AS Количество_Континентов

FROM Страны

Пример 7: Вывести разницу населения между странами с наибольшим и наименьшим количеством граждан:

SELECT

MAX(Население) - MIN(Население) AS Разница