Функции агрегирования и NULL

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

Основные функции агрегирования и их работа с NULL

COUNT

• COUNT(*): Считает количество строк в результате запроса, включая строки с NULL значениями.

• COUNT(column_name): Считает количество строк, где значение в указанном столбце не равно NULL.

Примеры:

-- Считает все строки, включая строки с NULL
SELECT COUNT(*) AS total_rows
FROM employees;

-- Считает количество строк, где значение в столбце salary не NULL
SELECT COUNT(salary) AS non_null_salaries
FROM employees;

SUM

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

Пример:

-- Суммирует значения в столбце salary, игнорируя NULL
SELECT SUM(salary) AS total_salary
FROM employees;

Если все значения в salary равны NULL, то результатом будет NULL.

AVG

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

Пример:

-- Вычисляет среднее значение в столбце salary, игнорируя NULL
SELECT AVG(salary) AS average_salary
FROM employees;

MIN и MAX

Функции MIN и MAX игнорируют значения NULL. Они возвращают минимальное и максимальное значения среди ненулевых данных. Если все значения в столбце NULL, результатом будет NULL.

Примеры:

-- Находит минимальное значение в столбце salary, игнорируя NULL
SELECT MIN(salary) AS min_salary
FROM employees;

-- Находит максимальное значение в столбце salary, игнорируя NULL
SELECT MAX(salary) AS max_salary
FROM employees;

Обработка NULL значений при агрегации

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

Функция COALESCE может быть использована для замены NULL значений на другое значение в результате выполнения агрегатных функций.

Пример:

-- Заменяет NULL на 0 в результате суммирования
SELECT COALESCE(SUM(salary), 0) AS total_salary
FROM employees;

Этот запрос заменяет NULL на 0, если SUM(salary) возвращает NULL.

Использование CASE для более сложных условий

Можно использовать конструкцию CASE для более сложной логики обработки NULL значений в агрегациях.

Пример:

-- Считает количество ненулевых зарплат, но добавляет 1, если все значения NULL
SELECT CASE
    WHEN COUNT(salary) = 0 THEN 1
    ELSE COUNT(salary)
END AS salary_count
FROM employees;

Этот запрос считает количество ненулевых зарплат и возвращает 1, если все значения равны NULL.

Примеры использования функций агрегирования с NULL

Пример 1: Суммирование значений с учетом NULL

-- Суммирует зарплаты и заменяет NULL на 0
SELECT COALESCE(SUM(salary), 0) AS total_salary
FROM employees;

Этот запрос возвращает сумму зарплат, заменяя NULL на 0.

Пример 2: Вычисление среднего значения с учетом NULL

-- Вычисляет среднее значение зарплат и заменяет NULL на 0
SELECT COALESCE(AVG(salary), 0) AS average_salary
FROM employees;

Этот запрос возвращает среднее значение зарплат, заменяя NULL на 0.

Пример 3: Подсчет строк с ненулевыми значениями

-- Считает количество сотрудников с ненулевой зарплатой
SELECT COUNT(salary) AS count_non_null_salaries
FROM employees;

Этот запрос возвращает количество сотрудников с ненулевой зарплатой.

Пример 4: Обработка данных с NULL в GROUP BY

-- Группирует сотрудников по отделам и суммирует зарплаты
-- С заменой NULL в сумме зарплаты на 0
SELECT department_id, COALESCE(SUM(salary), 0) AS total_salary
FROM employees
GROUP BY department_id;

Этот запрос группирует сотрудников по отделам и суммирует их зарплаты. Если в каком-то отделе нет сотрудников, то сумма будет равна 0.

Заключение

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