Базы данных Практическое занятие 9

Использование агрегатных функций

1. Введение в агрегатные функции

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

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

В SQL, и в частности в PostgreSQL, наиболее распространенными агрегатными функциями являются:

COUNT(): Подсчитывает количество строк или значений.
SUM(): Вычисляет сумму значений в столбце.
AVG(): Вычисляет среднее значение в столбце.
MIN(): Находит минимальное значение в столбце.
MAX(): Находит максимальное значение в столбце.

Эти функции являются важными инструментами для анализа данных и создания отчетов непосредственно в базе данных.

2. Подготовка примера данных

Для наших примеров мы будем использовать упрощенную таблицу employees. Если у вас нет этой таблицы, выполните следующие команды SQL в Инструменте запросов (Query Tool) pgAdmin 4, чтобы создать и заполнить ее.

Использование Инструмента запросов pgAdmin 4:

В дереве обозревателя pgAdmin 4 перейдите к вашей базе данных.
Щелкните правой кнопкой мыши по имени базы данных (например, postgres) или по схеме public.
Выберите “Query Tool” (Инструмент запросов).
Скопируйте и вставьте приведенный ниже код SQL в панель Редактора запросов.
Выполните код (нажмите кнопку “Execute/Refresh” - часто это значок молнии, или нажмите F5).

-- Удалить таблицу, если она существует, чтобы начать с чистого листа
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

-- Создать таблицу Departments (Отделы)
CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

-- Создать таблицу Employees (Сотрудники)
CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE NOT NULL,
    job_title VARCHAR(100),
    salary NUMERIC(10, 2),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Вставить примерные данные в Departments (Отделы)
INSERT INTO departments (department_name) VALUES
('Human Resources'),
('Engineering'),
('Sales'),
('Marketing');

-- Вставить примерные данные в Employees (Сотрудники)
INSERT INTO employees (first_name, last_name, hire_date, job_title, salary, department_id) VALUES
('Alice', 'Smith', '2020-03-15', 'Software Engineer', 75000.00, 2),
('Bob', 'Johnson', '2019-08-01', 'Senior Software Engineer', 95000.00, 2),
('Charlie', 'Williams', '2021-01-10', 'HR Manager', 80000.00, 1),
('Diana', 'Brown', '2022-05-20', 'Sales Representative', 60000.00, 3),
('Ethan', 'Jones', '2021-11-01', 'Marketing Specialist', 65000.00, 4),
('Fiona', 'Garcia', '2020-07-12', 'Software Engineer', 72000.00, 2),
('George', 'Miller', '2022-02-28', 'Sales Manager', 85000.00, 3),
('Hannah', 'Davis', '2023-01-05', 'Junior Developer', 60000.00, 2),
('Ian', 'Rodriguez', '2021-06-15', NULL, 55000.00, 3), -- Должность NULL (NULL job title)
('Julia', 'Martinez', '2019-12-01', 'HR Assistant', 48000.00, 1),
('Kevin', 'Hernandez', '2023-03-01', 'Intern', 30000.00, NULL); -- department_id = NULL

Проверьте данные, выполнив SELECT * FROM employees; и SELECT * FROM departments; в Инструменте запросов.

3. Объяснение основных агрегатных функций

Давайте рассмотрим каждую функцию с примерами, используя нашу таблицу employees. Выполните эти запросы в Инструменте запросов pgAdmin 4.

3.1. `COUNT()`

Функция COUNT() подсчитывает количество строк или значений.

COUNT(*): Подсчитывает общее количество строк в результирующем наборе (включая строки со значениями NULL).
```
-- Подсчитать всех сотрудников
SELECT COUNT(*) AS total_employees
FROM employees;
```
Результат: Вернет общее количество строк, вставленных в таблицу employees (в нашем примере данных это 11).
COUNT(имя_столбца): Подсчитывает количество строк, где указанный имя_столбца НЕ равен NULL.
```
-- Подсчитать сотрудников с известной должностью
SELECT COUNT(job_title) AS employees_with_job_title
FROM employees;
```
Результат: Подсчитывает строки, где job_title не равен NULL (10 в нашем примере данных, исключая Иана Родригеса).
```
-- Подсчитать сотрудников, назначенных в отдел
SELECT COUNT(department_id) AS employees_in_departments
FROM employees;
```
Результат: Подсчитывает строки, где department_id не равен NULL (10 в нашем примере данных, исключая Кевина Эрнандеса).
COUNT(DISTINCT имя_столбца): Подсчитывает количество уникальных, не-NULL значений в указанном столбце.
```
-- Подсчитать количество уникальных должностей
SELECT COUNT(DISTINCT job_title) AS distinct_job_titles
FROM employees;
```
Результат: Возвращает количество уникальных имеющихся должностей (например, ‘Software Engineer’, ‘Senior Software Engineer’, ‘HR Manager’ и т.д., исключая NULL). Проверьте данные для подтверждения количества (должно быть 8).

3.2. `SUM()`

Функция SUM() вычисляет сумму всех не-NULL значений в числовом столбце. Возвращает NULL, если нет не-NULL значений.

-- Вычислить общие расходы на зарплату
SELECT SUM(salary) AS total_salary_expenditure
FROM employees;

Результат: Складывает все значения в столбце salary для всех сотрудников.

-- Вычислить общую зарплату для отдела Engineering (department_id = 2)
-- Примечание: Здесь используется WHERE, рассмотренное в P5.
SELECT SUM(salary) AS engineering_salary_total
FROM employees
WHERE department_id = 2;

Результат: Суммирует зарплаты только тех сотрудников, у которых department_id равен 2.

3.3. `AVG()`

Функция AVG() вычисляет среднее (арифметическое) всех не-NULL значений в числовом столбце. Она игнорирует значения NULL при вычислении.

-- Вычислить среднюю зарплату всех сотрудников
SELECT AVG(salary) AS average_salary
FROM employees;

Результат: Возвращает среднее значение всех не-NULL зарплат. PostgreSQL обычно возвращает тип numeric или double precision для точности.

-- Вычислить среднюю зарплату для должности 'Software Engineer'
SELECT AVG(salary) AS avg_salary_software_engineer
FROM employees
WHERE job_title = 'Software Engineer';

Результат: Вычисляет среднюю зарплату специально для сотрудников с должностью ‘Software Engineer’.

3.4. `MIN()`

Функция MIN() находит минимальное (наименьшее) значение в столбце среди не-NULL значений. Она работает с числовыми, строковыми типами и типами даты/времени.

-- Найти самую низкую зарплату среди всех сотрудников
SELECT MIN(salary) AS lowest_salary
FROM employees;

Результат: Возвращает минимальное значение из столбца salary.

-- Найти самую раннюю дату приема на работу
SELECT MIN(hire_date) AS earliest_hire_date
FROM employees;

Результат: Возвращает самую раннюю дату из столбца hire_date.

3.5. `MAX()`

Функция MAX() находит максимальное (наибольшее) значение в столбце среди не-NULL значений. Она также работает с числовыми, строковыми типами и типами даты/времени.

-- Найти самую высокую зарплату среди всех сотрудников
SELECT MAX(salary) AS highest_salary
FROM employees;

Результат: Возвращает максимальное значение из столбца salary.

-- Найти самую позднюю дату приема на работу
SELECT MAX(hire_date) AS latest_hire_date
FROM employees;

Результат: Возвращает самую последнюю дату из столбца hire_date.

4. Использование агрегатных функций с `GROUP BY`

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

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

Примеры:

-- Подсчитать количество сотрудников в каждом отделе
SELECT
    department_id,
    COUNT(*) AS num_employees -- Подсчитывает всех сотрудников в каждой группе
FROM employees
WHERE department_id IS NOT NULL -- Необязательно: Исключить сотрудников без отдела
GROUP BY department_id
ORDER BY department_id; -- Необязательно: Отсортировать результаты

Результат: Показывает каждый department_id и количество сотрудников, принадлежащих к нему.

-- Вычислить среднюю зарплату для каждой должности
SELECT
    job_title,
    AVG(salary) AS average_salary_for_job,
    COUNT(*) AS number_of_employees_with_job
FROM employees
WHERE job_title IS NOT NULL -- Исключить сотрудников с должностью NULL
GROUP BY job_title
ORDER BY average_salary_for_job DESC; -- Необязательно: Отсортировать по средней зарплате

Результат: Перечисляет каждую не-NULL job_title вместе со средней зарплатой и количеством сотрудников с этой должностью.

-- Найти минимальную и максимальную зарплату в каждом отделе
SELECT
    department_id,
    MIN(salary) AS min_salary_in_dept,
    MAX(salary) AS max_salary_in_dept
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
ORDER BY department_id;

Результат: Показывает каждый department_id с самой низкой и самой высокой зарплатой, найденной в этом отделе.

5. Фильтрация групп с помощью `HAVING`

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

Примеры:

-- Найти отделы с более чем 2 сотрудниками
SELECT
    department_id,
    COUNT(*) AS num_employees
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING COUNT(*) > 2 -- Фильтровать группы на основе результата агрегатной функции COUNT
ORDER BY department_id;

Результат: Перечисляет только те department_id, где количество сотрудников в группе больше 2.

-- Найти должности, где средняя зарплата превышает $70,000
SELECT
    job_title,
    AVG(salary) AS average_salary_for_job
FROM employees
WHERE job_title IS NOT NULL AND salary IS NOT NULL
GROUP BY job_title
HAVING AVG(salary) > 70000 -- Фильтровать группы на основе вычисления средней зарплаты
ORDER BY average_salary_for_job DESC;

Результат: Перечисляет только те должности, средняя зарплата по которым превышает $70,000.

6. Важные соображения

Обработка NULL:
- COUNT(*) подсчитывает все строки.
- COUNT(имя_столбца) и COUNT(DISTINCT имя_столбца) игнорируют NULL в указанном столбце.
- SUM(), AVG(), MIN(), MAX() все игнорируют значения NULL при своих вычислениях. Будьте особенно внимательны с AVG(), так как игнорирование NULL может повлиять на результат по сравнению с обработкой их как нуля (если бы это требовалось, вам нужно было бы явно обрабатывать NULL, например, используя COALESCE(salary, 0) - хотя COALESCE может считаться частью стандартных функций P9).
Типы данных:
- SUM() и AVG() работают с числовыми типами (INTEGER, NUMERIC, REAL, DOUBLE PRECISION и т.д.).
- MIN() и MAX() работают с любыми типами данных, которые можно упорядочить (числа, строки, даты и т.д.).
- COUNT() работает с любыми типами данных, так как в основном подсчитывает строки или не-NULL вхождения.
Требование предложения GROUP BY: Как было повторено ранее, неагрегированные столбцы в списке SELECT должны присутствовать в предложении GROUP BY.

Использование агрегатных функций

1. Введение в агрегатные функции

2. Подготовка примера данных

3. Объяснение основных агрегатных функций

3.1. COUNT()

3.2. SUM()

3.3. AVG()

3.4. MIN()

3.5. MAX()

4. Использование агрегатных функций с GROUP BY

5. Фильтрация групп с помощью HAVING