Агрегация и группировка данных в MySQL

Введение

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

Базовые агрегатные функции

На этом этапе мы рассмотрим основные агрегатные функции в MySQL. Эти функции выполняют расчеты по нескольким строкам и возвращают одно значение, что делает их незаменимыми для анализа данных.

Сначала подключимся к MySQL и выберем нашу базу данных:

sudo mysql -u root

После подключения:

USE sales_db;

Функция COUNT

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

-- Подсчет общего количества продаж
SELECT COUNT(*) as total_sales
FROM sales;

Вы должны увидеть такой вывод:

+--------------+
| total_sales  |
+--------------+
| 12           |
+--------------+

Функцию COUNT можно использовать различными способами:

-- Подсчет уникальных проданных продуктов
SELECT COUNT(DISTINCT product_name) as unique_products
FROM sales;

-- Подсчет продаж по категориям
SELECT category, COUNT(*) as sales_count
FROM sales
GROUP BY category;

Пояснение:

• COUNT(*) подсчитывает все строки в таблице, включая значения NULL
• COUNT(DISTINCT column) подсчитывает только уникальные значения в указанном столбце
• При использовании с GROUP BY функция COUNT вычисляет итоги для каждой группы отдельно
• Ключевое слово as создает псевдонимы для столбцов результатов, делая вывод более читаемым

Функция SUM

Функция SUM вычисляет сумму числовых столбцов:

-- Вычисление общего количества проданных товаров
SELECT SUM(quantity) as total_items_sold
FROM sales;

-- Вычисление общей выручки
SELECT
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales;

Пояснение:

• Функцию SUM можно использовать только с числовыми столбцами
• Вычисления, такие как quantity * unit_price, выполняются до агрегации
• ROUND(x, 2) округляет числа до 2 десятичных знаков
• При вычислении выручки умножение выполняется перед суммированием, чтобы сохранить точность

Функция AVG

Функция AVG вычисляет среднее значение:

-- Вычисление средней цены за единицу
SELECT
    ROUND(AVG(unit_price), 2) as avg_price
FROM sales;

-- Вычисление среднего количества товаров в каждой продаже
SELECT
    ROUND(AVG(quantity), 1) as avg_quantity
FROM sales;

Пояснение:

• Функция AVG автоматически игнорирует значения NULL при своих расчетах
• Второй параметр в функции ROUND задает количество десятичных знаков
• Результаты автоматически преобразуются в тип DECIMAL для обеспечения точности
• Функция AVG обычно используется для нахождения типичных значений в ваших данных

Функции MAX и MIN

Эти функции находят наибольшее и наименьшее значения:

-- Поиск диапазона цен продуктов
SELECT
    MIN(unit_price) as lowest_price,
    MAX(unit_price) as highest_price
FROM sales;

-- Поиск дат первой и последней продажи
SELECT
    MIN(sale_date) as first_sale,
    MAX(sale_date) as last_sale
FROM sales;

Пояснение:

• Функции MIN/MAX работают с числами, строками и датами
• В случае дат функция MIN находит самую раннюю дату, а функция MAX - самую позднюю
• Несколько агрегатных функций можно комбинировать в одном операторе SELECT
• Как и другие агрегатные функции, они автоматически игнорируют значения NULL
• Эти функции полезны для нахождения диапазонов и границ значений в ваших данных

Группировка данных с использованием GROUP BY

На этом этапе мы научимся группировать данные с помощью предложения GROUP BY. Группировка позволяет выполнять агрегатные расчеты на подмножествах данных на основе значений определенных столбцов.

Базовая группировка

Начнем с простых операций группировки:

-- Количество продаж и общее количество товаров по категориям
SELECT
    category,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
ORDER BY total_quantity DESC;

Этот запрос показывает, сколько продаж и какое общее количество товаров было продано в каждой категории. Вы должны увидеть такой вывод:

+-------------+-------------+----------------+
| category    | sales_count | total_quantity |
+-------------+-------------+----------------+
| Furniture   |           5 |             22 |
| Electronics |           5 |             21 |
| Appliances  |           2 |             10 |
+-------------+-------------+----------------+

Пояснение:

• GROUP BY объединяет строки с одинаковой категорией в одну строку
• ORDER BY total_quantity DESC сортирует результаты по убыванию количества
• Каждая агрегатная функция (COUNT, SUM) работает независимо в каждой группе
• Ненакопительные столбцы в SELECT должны быть указаны в предложении GROUP BY

Группировка по нескольким столбцам

Мы можем группировать по нескольким столбцам, чтобы получить более детальную информацию:

-- Анализ продаж по категориям и регионам
SELECT
    category,
    region,
    COUNT(*) as sales_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category, region
ORDER BY category, total_revenue DESC;

Пояснение:

• Группировка по нескольким столбцам создает подгруппы для каждой уникальной комбинации
• Порядок столбцов в GROUP BY влияет на то, как данные будут сгруппированы
• Результаты сортируются сначала по категории, а затем по общей выручке внутри каждой категории
• Этот подход помогает определить наиболее успешные регионы в каждой категории

Группировка по датам

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

-- Сводка по ежедневным продажам
SELECT
    sale_date,
    COUNT(*) as transactions,
    SUM(quantity) as items_sold,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Пояснение:

• Группирует все транзакции, произошедшие в одну и ту же дату
• ORDER BY sale_date упорядочивает результаты по хронологии
• Подсчитывает количество транзакций в день и вычисляет ежедневные итоги
• Полезно для выявления ежедневных моделей и тенденций продаж
• Можно модифицировать запрос для группировки по месяцам или годам с использованием функций DATE

Фильтрация сгруппированных данных с использованием HAVING

На этом этапе мы узнаем о предложении HAVING, которое позволяет фильтровать результаты после группировки. В то время как предложение WHERE фильтрует отдельные строки перед группировкой, предложение HAVING фильтрует сами группы.

Базовое использование HAVING

Найдем категории, в которых было продано более 15 единиц товара в общей сложности:

SELECT
    category,
    SUM(quantity) as total_quantity
FROM sales
GROUP BY category
HAVING total_quantity > 15;

Этот запрос показывает только те категории, в которых общее количество товаров превышает 15 единиц:

+-------------+----------------+
| category    | total_quantity |
+-------------+----------------+
| Electronics |             21 |
| Furniture   |             22 |
+-------------+----------------+

Пояснение:

• Предложение HAVING фильтрует группы после применения GROUP BY.
• В предложении HAVING можно ссылаться на результаты агрегатных функций.
• Условие фильтрации использует псевдоним 'total_quantity'.
• Категории, в которых было продано 15 или менее единиц товара, исключаются из результатов.

Комбинирование WHERE и HAVING

Мы можем использовать предложения WHERE и HAVING вместе. Предложение WHERE фильтрует строки перед группировкой, а предложение HAVING - после группировки:

-- Найти категории с большим объемом продаж в регионе North
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity
FROM sales
WHERE region = 'North'
GROUP BY category
HAVING total_quantity > 5;

Пояснение:

• Предложение WHERE фильтрует отдельные строки (region = 'North') перед группировкой.
• Затем данные группируются по категориям.
• Наконец, предложение HAVING фильтрует сгруппированные результаты (total_quantity > 5).
• Порядок выполнения операций важен: FROM → WHERE → GROUP BY → HAVING → SELECT.

Сложные условия в HAVING

Мы можем использовать несколько условий в предложении HAVING:

-- Найти категории с большим объемом продаж и высокой выручкой
SELECT
    category,
    COUNT(*) as sale_count,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
HAVING total_quantity > 10 AND total_revenue > 1000
ORDER BY total_revenue DESC;

Пояснение:

• Несколько условий можно комбинировать с использованием операторов AND/OR.
• Все агрегатные вычисления доступны для условий в предложении HAVING.
• В предложении HAVING можно использовать арифметические операции.
• Сортировка ORDER BY применяется после оценки условий в предложении HAVING.
• Этот тип запроса полезен для определения наиболее успешных категорий.

Продвинутые техники агрегации

На этом последнем этапе мы рассмотрим более продвинутые техники агрегации, объединив все, что мы узнали. Мы создадим комплексные отчеты о продажах, которые продемонстрируют мощь этих инструментов в совокупности.

Панель мониторинга эффективности продаж

Создадим комплексный анализ продаж:

SELECT
    category,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as total_transactions,
    SUM(quantity) as total_quantity,
    ROUND(AVG(quantity), 1) as avg_quantity_per_sale,
    ROUND(MIN(unit_price), 2) as min_price,
    ROUND(MAX(unit_price), 2) as max_price,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Пояснение:

• Создает полный обзор эффективности каждой категории.
• Использует DISTINCT для подсчета уникальных продуктов в каждой категории.
• Комбинирует несколько агрегатных функций для комплексного анализа.
• Показывает диапазон цен с помощью MIN и MAX.
• Вычисляет средние количества и общую выручку.
• Результаты упорядочиваются по выручке, чтобы выделить наиболее успешные категории.

Анализ эффективности продаж по регионам

Проанализируем эффективность продаж по регионам:

SELECT
    region,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as total_revenue,
    ROUND(SUM(quantity * unit_price) / SUM(quantity), 2) as avg_price_per_unit
FROM sales
GROUP BY region
HAVING total_revenue > 1000
ORDER BY total_revenue DESC;

Пояснение:

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

Анализ ежедневных тенденций

Создадим отчет о ежедневных тенденциях продаж:

SELECT
    sale_date,
    COUNT(DISTINCT category) as categories_sold,
    COUNT(DISTINCT product_name) as unique_products,
    COUNT(*) as transactions,
    SUM(quantity) as total_quantity,
    ROUND(SUM(quantity * unit_price), 2) as daily_revenue,
    ROUND(AVG(quantity * unit_price), 2) as avg_transaction_value
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

Пояснение:

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

Резюме

В этом практическом занятии (лабораторной работе) мы рассмотрели основные аспекты агрегации и группировки данных в MySQL:

1. Использование базовых агрегатных функций (COUNT, SUM, AVG, MAX, MIN) для суммаризации данных
2. Группировка данных с помощью предложения GROUP BY для анализа шаблонов и тенденций
3. Фильтрация сгруппированных результатов с использованием предложения HAVING
4. Комбинирование нескольких методов для создания комплексного анализа данных

Эти навыки являются фундаментальными для анализа и отчетности по данным в MySQL. Понимание того, как эффективно агрегировать и группировать данные, позволяет извлекать значимую информацию из ваших баз данных и создавать ценные отчеты для бизнеса.