Вам интересно узнать о агрегатных функциях SQL и о том, как они применяются в реальных сценариях? В этой статье мы рассмотрим агрегатные функции SQL, продемонстрируем реальные примеры и дадим ценную информацию о том, как ими овладеть.
Агрегирование данных — это процесс сбора нескольких строк данных и объединения их в единый результат или сводку. При работе с большими наборами данных это бесценно, поскольку позволяет извлекать релевантную информацию без необходимости тщательного изучения каждой отдельной точки данных.
Итак, что же такое агрегатные функции SQL? Это специализированные функции, которые выполняют вычисления над группами переменных и возвращают единый результат. В отличие от традиционных функций, агрегатные функции работают с группами строк данных. Это позволяет эффективно вычислять статистику или генерировать сводную информацию из набора данных.
В этой статье мы рассмотрим важность агрегатных функций SQL и способы их использования. Мы объясним их на реальных примерах.
Распространенные агрегатные функции в SQL
В мире SQL агрегатные функции являются важными инструментами для эффективного обобщения и анализа данных. Они обладают уникальной способностью преобразовывать большие наборы данных в значимые аналитические данные, облегчать статистический анализ и упрощать сложные структуры данных.
Наиболее часто используются следующие агрегатные функции:
- SUM()
- COUNT()
- AVG()
- MIN()
- MAX()
Давайте рассмотрим каждую из них ниже.
1. SUM()
Функция SUM() возвращает итоговое значение числового столбца. Обычно она используется, когда вам нужно найти итоговое значение таких значений, как доход от продаж, количество или расходы.
Представьте, что вы хотите узнать весь доход вашей компании от продаж; вы можете сделать это, выполнив следующий запрос:
2. COUNT()
Следующая на очереди функция COUNT(). Целью этой функции является подсчет количества строк в таблице.
Предположим, вы хотите узнать, сколько товаров продается в вашем магазине; вы можете использовать следующий запрос:
В этом запросе мы используем звездочку (*) для обозначения всех столбцов. И мы подсчитываем все строки в запросе.
3. AVG()
Когда вам нужно вычислить среднее значение числового столбца, вы можете воспользоваться функцией AVG(). Это полезно при поиске средней цены, рейтинга, проданных единиц и так далее. Этот запрос, например, был бы полезен, если вы запускаете потоковую службу на основе подписки и хотите узнать среднюю продолжительность пользовательских сеансов на вашей платформе:
4. MIN()
Функция MIN() возвращает наименьшее значение в столбце. Она особенно полезна для поиска наименьшего значения в наборе данных.
Представьте себе менеджера, пытающегося определить максимально быстрое время ответа на вопрос клиента для обращения в службу поддержки; следующий запрос может вернуть ответ:
5. MAX()
С другой стороны, функция MAX() возвращает наибольшее значение в столбце. Она полезна для определения наибольшего значения в наборе данных.
Предположим, у вас есть сайт и вы хотите узнать максимальную стоимость заказа, сделанного клиентом в вашем интернет-магазине. Это запрос, который вы бы написали:
Агрегирование данных в SQL
Теперь, когда мы рассмотрели некоторые из наиболее используемых агрегатных функций SQL, давайте подробнее рассмотрим концепцию агрегирования данных в SQL.
Агрегирование данных — это мощный метод обобщения и анализа информации из набора данных. Он позволяет получать полезную информацию, выполняя вычисления над группировками связанных данных, устраняя необходимость в трудоемкой проверке отдельных записей.
Чтобы сделать эту концепцию более конкретной, рассмотрим следующие реальные примеры использования:
- Продажи: Понимание того, как различные категории товаров влияют на общий объем продаж, имеет решающее значение для владельца магазина. Вы можете определить, какая категория товаров приносит наибольший доход, объединив данные о продажах для каждой категории товаров.
- Оценки учащихся: Если вы учитель, возможно, вам захочется подсчитать среднюю оценку по каждому предмету в вашем классе. Агрегируя данные об оценках учащихся, вы можете определить средние значения по предмету и определить области для улучшения.
- Посещаемость сайта: Предположим, вы запускаете сайт и хотите знать, какие страницы получают наибольший трафик. Вы можете находить самые популярные страницы и более эффективно управлять ресурсами, агрегируя статистику посещаемости.
- Заработная плата сотрудников: Представьте, что вы менеджер по персоналу в компании, который хочет получить представление о заработной плате в разных отделах. SQL может помочь вам агрегировать и анализировать данные по каждому отделу.
Раскрытие возможностей агрегатных функций SQL с помощью GROUP BY
По большей части использование агрегатных функций SQL означает использование оператора GROUP BY. Эта мощная комбинация позволяет выполнять агрегированные вычисления для определенных групп данных в вашем наборе данных.
Оператор GROUP BY разделяет данные по группам на основе совпадающих значений в одном или нескольких столбцах; это позволяет независимо применять агрегатные функции к каждой группе. Ниже приведен обзор синтаксиса GROUP BY:
В этом запросе данные с совпадающими значениями в column1 и column2 станут группами; aggregate_function(column3) это агрегатная функция, которую вы хотите применить к значениям column3 внутри каждой группы. Давайте рассмотрим несколько примеров.
Пример 1: Группировка по городам и расчет средней заработной платы
Представьте, что у вас есть база данных сотрудников, которая содержит информацию об именах сотрудников, городах и зарплатах. Ваша цель — выяснить, какова средняя зарплата в каждом городе. Вы можете сделать это с помощью следующего запроса:
Теперь давайте разберем запрос шаг за шагом:
Шаг 1: Группировка по городам
База данных запускает процесс, объединяя сотрудников в группы в зависимости от их города проживания. Каждая группа городов визуально представлена ниже разными цветами:
- Группа 1: Нью-Йорк (желтая)
- Группа 2: Сан-Франциско (красная)
- Группа 3: Лос-Анджелес (зеленый)
Шаг 2: Расчет средней заработной платы
В каждой группе городов база данных рассчитывает среднюю зарплату, применяя агрегатную функцию AVG() к столбцу salary .
Конечный результат:
Этот SQL-запрос выдает среднюю зарплату по каждому городу, как показано ниже. Выполнение этого запроса дает ценную информацию о тенденциях и различиях в оплате труда в городах, где проживают ваши сотрудники. Эти сведения могут сыграть важную роль в принятии обоснованных решений относительно корректировки заработной платы, учета стоимости жизни и многого другого.
Пример 2: Группировка по категориям товаров и определение общего объема продаж
Предположим, у вас есть набор данных о продажах с категориями продуктов и объемами продаж. Ваша цель — определить общий объем продаж для каждой категории продуктов. Вот запрос:
Этот запрос группирует данные о продажах по категориям продуктов и вычисляет общие суммы продаж для каждой группы. Он предоставляет полезную информацию о наиболее прибыльных категориях продуктов.
Пример 3: Группировка по годам и месяцам для подсчета заказов
Допустим, вы запускаете платформу электронной коммерции с базой данных, включающей даты заказов. Вы хотите отслеживать количество размещенных заказов за каждый год и месяц. Для достижения этой цели можно использовать следующий запрос:
Этот запрос группирует заказы по годам и месяцам, предоставляя временную шкалу количества заказов. Он весьма полезен для отслеживания сезонных изменений и оценки количества заказов за месяц.
В каждом из этих примеров оператор GROUP BY разбивает данные на группы на основе определенных столбцов, а агрегатные функции (AVG(), SUM() и COUNT()) выполняют вычисления для каждой группы.
Реальные примеры использования агрегатных функций SQL
Теперь, когда мы рассмотрели основы агрегатных функций и оператор GROUP BY, давайте посмотрим, как мы можем использовать их на дополнительных реальных примерах.
1. Группировка по категориям продуктов для анализа продаж
Представьте, что у вас есть база данных продаж и вы хотите найти самую последнюю дату заказа для каждой категории товаров. Для достижения этой цели нам нужно сгруппировать данные по столбцам product_category и использовать функцию MAX() для выбора самой последней (т. е. наибольшей) даты заказа:
Анализ самой последней даты заказа для каждой категории продуктов помогает определить текущие тенденции рынка и спрос на продукт.
2. Анализ продаж по категориям продуктов и годам
Для более подробного анализа может потребоваться группировка данных по нескольким столбцам. Например, предположим, вам нужно знать общий объем продаж по каждой товарной категории и году. Вот запрос:
Этот запрос группирует данные по двум столбцам, позволяя анализировать продажи как по категориям продуктов, так и по годам, таким образом, предоставляя более подробную информацию.
3. Фильтрация категорий товаров с высоким уровнем продаж
Возможно, вам потребуется отфильтровать группы в зависимости от выходных данных агрегатной функции. В таких случаях вам нужен оператор HAVING. Предположим, вы хотите найти категории продуктов с общим объемом продаж более 10 000 долларов:
Оператор HAVING фильтрует сгруппированные результаты, отображая категории товаров с общим объемом продаж, который превышает установленный порог.
Важно отметить, что агрегатные функции нельзя использовать с оператором WHERE. Агрегатные функции работают с данными, которые были сгруппированы с помощью оператора GROUP BY, в то время как оператор WHERE фильтрует отдельные строки до того, как они будут сгруппированы.
4. Подсчет уникальных клиентов
Подсчет значений в столбце важен для различных анализов. Например, вы можете захотеть найти количество уникальных клиентов:
Этот запрос подсчитывает количество уникальных идентификаторов клиентов в данных о продажах, что может быть полезно при работе с наборами данных, содержащими дубликаты.
5. Отличительные особенности COUNT(*) и COUNT(expression)
Важно понимать разницу между COUNT(*) и COUNT(expression). COUNT(*) подсчитывает все строки в группе, тогда как COUNT(expression) подсчитывает только ненулевые значения предоставленного выражения внутри группы. Нулевые значения представляют отсутствующие или неизвестные данные в наборе данных.
Давайте рассмотрим пример:
Здесь с помощью COUNT(*) подсчитываются все заказы в каждой категории товаров, включая заказы нулевыми значениями в столбце sales. C другой стороны, с помощью COUNT(discount), учитываются только заказы с ненулевыми значениями в столбце discount внутри каждой категории.
6. Получение всестороннего представления о категориях продуктов
SQL позволяет использовать множество агрегатных функций в одном запросе, чтобы получить полную картину ваших данных. Например, давайте найдем общий объем продаж, среднюю цену и минимальную цену для каждой категории товаров:
Этот запрос эффективно извлекает множество полезных данных из вашей БД и в то же время предоставляет краткое резюме.
Часто задаваемые вопросы о агрегатных функциях SQL
Чтобы подвести итог, давайте рассмотрим некоторые часто задаваемые вопросы о агрегатных функциях SQL:
Могу ли я использовать две агрегатные функции в запросе?
ДА. Как показано в предыдущих примерах, вы можете использовать несколько агрегатных функций в одном SQL-запросе. Использование нескольких агрегатных функций обеспечивает более полное представление о ваших данных. Например, чтобы получить более четкое представление о распределении доходов, вы можете захотеть рассчитать не только среднюю зарплату в отделе, но также максимальную и минимальную зарплаты.
В дополнение к общим агрегатным функциям, которые мы обсуждали, базы данных SQL часто предлагают специализированные агрегатные функции, адаптированные к конкретным потребностям. Например, SQL Server включает функцию STRING_AGG() для объединения строк внутри группы. При работе над проектами с уникальными требованиями к обработке данных эти специализированные возможности могут оказаться незаменимыми.
Как я могу посчитать строки, используя условное выражение?
В SQL условные выражения, обычно реализуемые с помощью CASE WHEN, могут использоваться для подсчета строк на основе заданных условий. Например, вы можете подсчитать количество заказов с общим доходом более 1000 долларов:
Зачем мне использовать оператор HAVING с агрегатными функциями в SQL?
В SQL-запросах, содержащих агрегатные функции, оператор HAVING используется для фильтрации результатов на основе результата агрегатной функции. Оно позволяет применять условия к группировкам данных после их агрегирования. Например, если вы хотите получить только отделы со средней зарплатой в 50 000 долларов или больше, вы можете использовать оператор HAVING для соответствующей фильтрации сгруппированных результатов.
Ссылка на оригинал статьи: https://learnsql.com/blog/aggregate-functions/