Управление и автоматизация БД Практическая работа №5

Практическое занятие № 5

Тема: Запросы с агрегирующими функциями

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

Приобретаемые умения и навыки: понимание назначения и принципов работы агрегирующих функций.

Норма времени: 2 часа.

Оборудование: компьютер с установленным программным обеспечением и подключенный к Internet.

Методические указания по выполнению практической работы

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

Основные агрегирующие функции:

• COUNT(): Возвращает количество строк в группе.

• COUNT(*): Возвращает общее количество строк (включая строки с NULL значениями).

• COUNT(column_name): Возвращает количество строк, в которых столбец column_name не содержит NULL значения.

• COUNT(DISTINCT column_name): Возвращает количество уникальных не-NULL значений в столбце column_name.

• SUM(): Возвращает сумму числовых значений в группе. Игнорирует NULL значения.

• AVG(): Возвращает среднее арифметическое числовых значений в группе. Игнорирует NULL значения.

• MIN(): Возвращает минимальное значение в группе. Может применяться к числовым, строковым и датам. Игнорирует NULL значения.

• MAX(): Возвращает максимальное значение в группе. Может применяться к числовым, строковым и датам. Игнорирует NULL значения.

Предложение GROUP BY:

Предложение GROUP BY используется для группировки строк с одинаковыми значениями в одном или нескольких столбцах. После группировки к каждой группе применяются агрегирующие функции. Столбцы, указанные в GROUP BY, также должны присутствовать в SELECT (вне агрегирующих функций).

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

Предложение HAVING:

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

SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) 10;

Работа с NULL значениями:

Агрегирующие функции (кроме COUNT(*)) обычно игнорируют NULL значения. Важно учитывать это при анализе данных, так как пропуск NULL значений может повлиять на результаты. Иногда необходимо использовать функции, специфичные для вашей СУБД, например COALESCE или ISNULL, чтобы заменить NULL значения на другие значения перед применением агрегирующих функций.

Примеры: Предположим, у нас есть таблица Orders (OrderID, CustomerID, OrderDate, Amount):

• Подсчитать общее количество заказов:

SELECT COUNT(*) FROM Orders;

* Подсчитать количество заказов для каждого клиента:

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

* Найти клиентов, у которых больше 10 заказов:

SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID HAVING COUNT(*) 10;

* Найти среднюю сумму заказа:

SELECT AVG(Amount) FROM Orders;

* Найти общую сумму заказов за каждый месяц:

-- Обратите внимание: для этого нужен способ выделить месяц из даты, зависящий от СУБД.

-- Пример для SQL Server:

SELECT YEAR(OrderDate), MONTH(OrderDate), SUM(Amount)

FROM Orders

GROUP BY YEAR(OrderDate), MONTH(OrderDate)

ORDER BY YEAR(OrderDate), MONTH(OrderDate);

Ход работы

1. Создать запрос с нахождением максимального значения в столбце.

2. Создать запрос с любой агрегирующей функцией.

Контрольные вопросы:

1. Что такое агрегирующая функция в SQL?

2. В чем разница между COUNT(*) и COUNT(column_name)?