Skip to content

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

Здравствуйте! Сегодня мы познакомимся с агрегатными функциями в SQL, подробно разберем их работу с данными из таблиц, которые создавали в прошлых уроках.

Общее понятие

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

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

  • Суммировать выбранные значения
  • Находить среднее арифметическое значений
  • Находить минимальное и максимальное из значений

Примеры агрегатных функций SQL

Мы разберем самые часто используемые функции и приведем несколько примеров.

Функция SUM

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

Получить сумму всех заказов из таблицы Orders, которые были совершены в 2016 году.

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

onumamtodatecnumsnum
10011282016-01-0194
100218002016-04-10107
10033482017-04-0821
10045002016-06-0733
10054992017-12-0454
10063202016-03-0354
1007802017-09-0271
10087802016-03-0713
10095602017-10-0737
10109002016-01-0868

Следующий код осуществит нужную выборку:

SELECT SUM(amt) 
FROM Orders 
WHERE odate BETWEEN '2016-01-01' and '2016-12-31'; 

В результате получим:

SUM(amt)
4428

В данном запросе мы использовали функцию SUM, после которой в скобках нужно указать поле для суммирования. Затем мы указали условие в WHERE, которое отобрало строчки только с 2016 годом. На самом деле это условие можно записать по другому, но сейчас важнее агрегатная функция суммирования в SQL.

Функция AVG

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

Вывести среднюю стоимость заказа из таблицы Orders.

И сразу запрос:

SELECT AVG(amt) 
FROM Orders; 

В результате получим:

AVG(amt)
591.5

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

Функции MIN и MAX

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

Вывести максимальное и минимальное значения цены заказа, для тех заказов в которых цена менее 1000.

Получается такой запрос,

SELECT MAX(amt), MIN(amt) 
FROM Orders 
WHERE amt < 1000; 

который выведет:

MAX(amt)MIN(amt)
90080

Также стоит сказать, что в отличие от предыдущих функций, эти 2 могут работать с символьными параметрами, то есть можно написать запрос типа MIN(odate) (в данном случае дата у нас символьная), и тогда нам вернется 2016-01-01.

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

Еще одним важным моментом является то, что мы можем производить некоторые простые математические операции в запросе SELECT, например, такой запрос:

SELECT (MAX(amt) - MIN(amt)) AS 'Разница' 
FROM Orders; 

Вернет такой ответ:

Разница
1720

Функция COUNT

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

  • С ключевым словом DISTINCT, для того, чтобы подсчитать количество не повторяющихся значений
  • С использованием «*», для того, чтобы подсчитать количество всех выбранных значений
  • Теперь разберем пример использования COUNT в SQL:

    Подсчитать количество сделанных заказов и количество продавцов в таблице Orders.

    SELECT COUNT(*), COUNT(DISTINCT snum) 
    FROM Orders; 
    

    Получаем:

    COUNT(*)COUNT(snum)
    105

    Очевидно, что количество заказов — 10, но если вдруг у вас имеется большая таблица, то такая функция будет очень удобной. Что касается уникальных продавцов, то здесь необходимо использовать DISTINCT, потому что один продавец может обслужить несколько заказов.

    Оператор GROUP BY

    Теперь рассмотрим 2 важных оператора, которые помогают расширить функционал наших запросов в SQL. Первым из них является оператор GROUP BY, который осуществляет группировку по какому либо полю, что иногда является необходимым. И уже для этой группы производит заданное действие. Например:

    Вывести сумму всех заказов для каждого продавца по отдельности.

    То есть теперь нам нужно для каждого продавца в таблице Orders выделить поля с ценой заказа и просуммировать. Все это сделает оператор GROUP BY в SQL достаточно легко:

    SELECT snum, SUM(amt) AS 'Сумма всех заказов' 
    FROM Orders 
    GROUP BY snum; 
    

    И в итоге получим:

    snumСумма всех заказов
    1428
    31280
    4947
    72360
    8900

    Как видно, SQL выделил группу для каждого продавца и посчитал сумму всех их заказов.

    Оператор HAVING

    Этот оператор используется как дополнение к предыдущему. Он необходим для того, чтобы ставить условия для выборки данных при группировке. Если условие выполняется то выделяется группа, если нет — то ничего не произойдет. Рассмотрим следующий код:

    SELECT snum, SUM(amt) AS 'Сумма всех заказов' 
    FROM Orders 
    GROUP BY snum 
    HAVING MAX(amt) > 1000; 
    

    Который создаст группу для продавца и посчитает сумму заказов этой группы, только в том случае, если максимальная сумма заказа больше 1000. Очевидно, что такой продавец только один, для него выделится группа и посчитается сумма всех заказов:

    snumСумма всех заказов
    72360

    Казалось бы, почему тут не использовать условие WHERE, но SQL так построен, что в таком случае выдаст ошибку, и именно поэтому в SQL есть оператор HAVING.

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

    1. Напишите запрос, который сосчитал бы все суммы заказов, выполненных 1 января 2016 года.

    SELECT SUM(amt)
    FROM Orders
    WHERE odate = '2016-01-01';
    

    2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.

    SELECT COUNT(DISTINCT city)
    FROM customers;
    

    3. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.

    SELECT cnum, MIN(amt)
    FROM orders
    GROUP BY cnum;
    

    4. Напишите запрос, который бы выбирал заказчиков чьи имена начинаются с буквы Г.

    SELECT cname
    FROM customers
    WHERE cname LIKE 'Г%' ;
    

    5. Напишите запрос, который выбрал бы высший рейтинг в каждом городе.

    SELECT city, MAX(rating)
    FROM customers
    GROUP BY city;
    

    Заключение

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

    Если у вас остались вопросы, то задавайте их в комментариях.

    Опубликовано вSQL

    6 комментариев

    1. Светлана Светлана

      Действительно хорошая статья, все очень понятно . Жаль продолжения нет

    2. Александр Александр

      В разделе having, к условию подходит и где snum = 3, тк сумма тоже больше 1000. А так классная статья!

      • Nikiqq Nikiqq

        Спасибо за оценку статьи)

        По поводу замечания: посмотрите внимательнее, в условии having стоит MAX(amt) > 1000 — то есть у каждой группы ищется МАКСИМАЛЬНАЯ сумма одного заказа, в начале статьи можно проверить, что только у snum = 7 есть такой заказ. А в выводе идет СУММА по всем заказам.

        В вашем же случае, как вы предположили, в having должно было быть написано так:
        HAVING SUM(amt) > 1000;

    3. Наталья Наталья

      Спасибо за статью)
      У меня вопрос по запросу:
      3. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
      SELECT cnum, MIN(amt)
      FROM orders
      GROUP BY cnum;
      Мне кажется, здесь должно быть в условии написано для каждого продавца, а не заказчика.
      А во втором задании у меня тоже вопрос:
      2. Напишите запрос, который сосчитал бы число различных, отличных от NULL значений поля city в таблице заказчиков.
      SELECT COUNT(DISTINCT city)
      FROM customers;
      Здесь не надо писать условие WHERE cityNULL или как тут это пишется…..
      Я только учусь, не удивляйтесь моим вопросам))

    4. Наталья Наталья

      Ой, я написала в комментарии
      WHERE city не равно (знаки больше меньше) NULL,
      а эти знаки при отправке удалились….

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

    Ваш адрес email не будет опубликован. Обязательные поля помечены *