13 Дек 2022
4 мин
1851

Агрегатные функции в SQL: суть понятия и примеры

Разбор на примерах: SUM, AVG, MIN, MAX, COUNT.

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

Содержание

В статье поговорим про агрегирующие функции SQL: узнаем, зачем они нужны, и обсудим их основные понятия и возможности. Разберем на примерах функций: COUNT, SUM, MAX, MIN, AVG.

Что такое агрегатные функции SQL

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

Для сравнения — скалярные функции принимают на вход одно значение и возвращают одно значение. Пример скалярной функции: abs — функция, принимающая число и возвращающая абсолютное значение этого числа.

Свойства агрегатных функций:

  • Игнорируют NULL-значения. Исключение — функция COUNT(*). Дальше детально рассмотрим, что это значит.
  • Все агрегатные функции детерминированы. Это значит, что для одинакового набора данных функции возвращают одинаковое значение.

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

С агрегатными функциями можно использовать ключевые слова DISTINCT и ALL, которые мы рассмотрим ниже. Синтаксис выглядит так:

 aggregation_function(DISTINCT | ALL expression)

В примерах будем использовать таблицу Employees («Сотрудники»).

name office_id salary role
Ivan 1 500 SWE
Misha 2 750 Manager
Olya 2 600 QA
Kolya 1 900 SWE
Max 2 NULL Manager

Создать ее можно с помощью команды:

CREATE TABLE IF NOT EXISTS employees (
name VARCHAR ( 50 ) NOT NULL,
office_id INT,
salary INT,
role VARCHAR ( 50 ) NOT NULL
);

INSERT INTO employees VALUES
('Ivan', 1, 500, 'SWE'), ('Misha', 2, 750, 'Manager'), ('Olya', 2, 600, 'QA'), ('Kolya', 1, 900, 'SWE'), ('Max', 2, NULL, 'Manager');

Протестировать команды ниже поможет сервис DB Fiddle. Используемый код совместим с программой PostgreSQL v14 — системой управления базами данных.

Функция COUNT

COUNT считает количество строк в таблице. Она может принимать в качестве параметров как числовые, так и нечисловые типы данных.

COUNT(*) — специальная форма функции COUNT, которая возвращает количество всех строк в указанной таблице. Обратите внимание: COUNT(*) считает дубликат и NULL.

Напишем запрос к таблице Employees, который будет считать количество сотрудников для каждой роли:

SELECT role, count(*) number_of_empoyee FROM employees GROUP BY role;

Посчитаем количество сотрудников уникальных офисов:

role number_of _employee
QA 1
Manager 2
SWE 2
SELECT COUNT(DiSTINCT office_id) AS unique_offices FROM employees;
unique_offices
2

SUM

Функция SUM вычисляет суммы всех выбранных столбцов. Обратите внимание: она работает только с числовыми полями.

Посчитать суммарную зарплату для всех сотрудников можно так:

SELECT SUM(salary) AS total_salary FROM employees;
total_salary
2750

MAX и MIN

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

Например, минимальная зарплата среди всех сотрудников:

SELECT MIN(salary) AS min_salary FROM employees;
min_salary
500

Напишем запрос сложнее, который использует оператор HAVING, — максимальная зарплата для каждого офиса, где больше двух сотрудников:

SELECT office_id, MAX(salary) AS max_salary FROM employees GROUP BY office_id HAVING COUNT(*) > 2;
office_id max_salary
2 750

AVG

Функция используется для вычисления среднего значения заданного столбца.
Например, рассчитаем среднюю зарплату по всем сотрудникам:

SELECT AVG(salary) AS avg_salary FROM employees;
avg_salary
687.500000000000

Важно: в столбце salary у нас есть значение NULL, но функция AVG проигнорировала его и посчитала среднее значение как (500 + 750 + 600 + 900) / 4 = 687,5

Где применяют агрегирующие функции

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

  • строят модели в аналитике;
  • строят графики в реальном времени с метриками сервисов;
  • пишут отчеты.

Для некоторых продуктов критически важна производительность агрегирующих функций (например, для построения аналитики в реальном времени). Поэтому есть базы данных, которые оптимизированы для вычислений агрегатных функций: ClickHouse и Apache Druid. Еще есть хранилище данных, которое оптимизировано под выполнение запросов с агрегатными функциями, — firebolt.

Краткие итоги

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

Разобрали четыре функции:

  • COUNT — считает количество строк в таблице;
  • SUM — вычисляет суммы выбранных столбцов;
  • MAX и MIN — находят максимальное и минимальное значение определенного столбца;
  • AVG — вычисляет среднее значение столбца.

Агрегатные функции используют с операторами GROUP BY и HAVING:

  • GROUP BY — группирует строки с одинаковыми значениями в одну строку;
  • HAVING — используется как фильтр для запросов, в которых есть оператор GROUP BY.
Получите пошаговый план развития в карьере после записи на курс

    Оставляя заявку, вы принимаете условия соглашения

    Учёба ради учёбы — это не прикольно. На наших курсах вы ставите конкретные цели и достигаете их в заданные сроки. Начинайте карьеру с первых достижений!

    Добавить комментарий

    Вставить формулу как
    Блок
    Строка
    Дополнительные настройки
    Цвет формулы
    Цвет текста
    #333333
    Используйте LaTeX для набора формулы
    Предпросмотр
    \({}\)
    Формула не набрана
    Вставить