Создание базовых отчетов

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

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

• SUM(): подсчитывает сумму значений.
• AVG(): вычисляет среднее значение.
• COUNT(): считает количество строк в выборке.

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

Подсчёт строк: функция COUNT()

Функция COUNT() позволяет подсчитать количество строк в таблице. Разберем её работу с примерами.

-- Простой подсчет всех строк в таблице заказов
SELECT COUNT(*) AS total_orders
FROM orders;

-- Подсчет уникальных клиентов
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

-- Подсчет заказов, у которых сумма больше 100
SELECT COUNT(*) AS high_value_orders
FROM orders
WHERE total_amount > 100;

Функция COUNT() часто используется для подсчёта записей, уникальных значений, а также в комбинации с фильтрацией, например, "сколько студентов записались на курсы по Python".

Суммирование данных: функция SUM()

Функция SUM() вычисляет сумму значений в столбце. Давайте теперь посчитаем сумму всех покупок клиентов.

-- Подсчет общей выручки
SELECT SUM(total_amount) AS total_revenue
FROM orders;

-- Сумма покупок конкретного клиента
SELECT SUM(total_amount) AS customer_spending
FROM orders
WHERE customer_id = 101;

-- Сумма заказов по категориям
SELECT category, SUM(total_amount) AS category_revenue
FROM orders
GROUP BY category;

SUM() — это главный инструмент для аналитики продаж, выручки и любых других сумм. Например, вам нужно узнать доходы бизнес-центра за последний месяц? Легко.

Средние значения: функция AVG()

Функция AVG() помогает вычислить среднее значение по набору данных. Например, средняя оценка студентов или средний чек клиента.

-- Средняя сумма заказа
SELECT AVG(total_amount) AS average_order_value
FROM orders;

-- Средняя сумма заказов по категориям
SELECT category, AVG(total_amount) AS average_order_value
FROM orders
GROUP BY category;

-- Средний чек клиентов за последние 7 дней
SELECT AVG(total_amount) AS avg_check_last_week
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days';

Среднее значение полезно для анализа качества обслуживания, выявления аномалий и расчёта ключевых метрик, вроде средней прибыли на клиента.

Использование агрегатных функций в аналитике

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

Пример 1: общая выручка и количество заказов

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

SELECT
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE order_date >= '2023-10-01' AND order_date <= '2023-10-31';

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

А что если мы захотим разбить выручку по категориям товаров?

SELECT
    category,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY category;

Пример 3: заказы за последние 7 дней

Часто требуется анализировать метрики за короткие периоды, например, за последнюю неделю.

SELECT
    COUNT(*) AS orders_last_week,
    SUM(total_amount) AS revenue_last_week,
    AVG(total_amount) AS avg_check_last_week
FROM orders
WHERE order_date >= NOW() - INTERVAL '7 days';

Практические кейсы с конкретными задачами

Задача: Анализ продаж по регионам

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

SELECT
    region,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS average_order_value
FROM orders
GROUP BY region
ORDER BY total_revenue DESC;

Задача: Топ-10 клиентов по выручке

Теперь добавим немного логики, чтобы выделить топ-10 клиентов по общей сумме заказов.

SELECT
    customer_id,
    SUM(total_amount) AS total_spending
FROM orders
GROUP BY customer_id
ORDER BY total_spending DESC
LIMIT 10;

Задача: Сравнение выручки по дням недели

Хотите узнать, в какие дни недели ваш бизнес зарабатывает больше всего? Вот пример:

SELECT
    TO_CHAR(order_date, 'Day') AS day_of_week,
    SUM(total_amount) AS total_revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY TO_CHAR(order_date, 'Day')
ORDER BY total_revenue DESC;

2

Задача

SQL SELF, 59 уровень, 2 лекция

Недоступна

Анализ средней выручки за последние 30 дней

Анализ средней выручки за последние 30 дней

Открыть