Аналитика на SQL

Теория: Агрегация с помощью операторов SUM, MAX и AVG

Полный доступ к материалам

Зарегистрируйтесь и получите доступ к этому и десяткам других курсов

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

SUM
MAX
MIN
AVG

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

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

Функция `SUM`

Функция SUM подсчитывает суммы значений в определенном столбце. Это полезно, если нужно получить общую величину — например, общий доход или количество проданных товаров.

Вычислим общее количество проданных товаров:

SELECT SUM(quantity) AS total
FROM sales;

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

Оператор SELECT и функция SUM(quantity) вычисляют сумму значений столбца quantity
Ключевое слово AS присваивает результату псевдоним total, чтобы сделать вывод понятнее
Оператор FROM указывает, что нужно использовать таблицу sales как источник данных для запроса

В итоге мы получаем такой результат — общее количество проданных товаров во всех магазинах:

Untitled

Функции `MAX` и `MIN`

MAX и MIN находят наибольшее и наименьшее значение в столбце. Таким образом можно определить максимальную и минимальную цену товара или диапазон дат.

Для примера определим дату самой ранней и самой поздней продажи:

SELECT
    MIN(sale_date) AS earliest_sale,
    MAX(sale_date) AS latest_sale
FROM sales;

Получим такой результат:

Untitled

Представим, что еще нам нужно узнать разницу по времени между этими двумя датами. Простая разность двух этих показателей не приведет к нужному результату.

Чтобы вычислить временной интервал между двумя датами в SQLite, воспользуемся специфичной функцией JULIANDAY():

SELECT
    MIN(sale_date) AS earliest_sale,
    MAX(sale_date) AS latest_sale,
    JULIANDAY(MAX(sale_date)) - JULIANDAY(MIN(sale_date)) AS date_diff
FROM sales;

Такой результат получим в итоге:

Untitled

Функция `AVG`

Функция AVG вычисляет среднее значение столбца. С ее помощью можно определить любое среднее значение — например, среднюю стоимость товаров, число продаж или зарплату сотрудников.

Функция AVG работает следующим образом:

Сначала она определяет все числовые значения в указанном столбце
Затем она суммирует все эти значения
В конце она делит сумму на количество числовых значений, не учитывая нулевые

Чтобы попрактиковаться, вычислим среднюю цену товаров через функцию AVG:

SELECT AVG(price) AS average_price
FROM products;

В выводе мы увидим среднюю цену:

Untitled

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

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

SELECT ROUND(AVG(quantity), 0) AS average_quantity_rounded
FROM sales;

Результат получится без знаков после запятой, потому что для функции ROUND мы указали число 0 в качестве второго аргумента и таким образом округлили результат до целого значения:

Untitled

Выводы

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