Агрегация для столбчатой диаграммы — Продвинутая аналитика на SQL

• Столбчатая диаграмма
• Агрегация суммарных продаж для каждого товара
• Выводы

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

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

Столбчатая диаграмма

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

Предположим, у нас есть таблица суммарных продаж товаров по разным типам:

total_sales

В этой таблице есть два столбца:

1. Тип товара — значение, которое выражается строкой. Категориальное значение
2. Суммарные продажи — числовое значение

В столбчатой диаграмме каждая категория наносится отдельным делением на одну из осей диаграммы. Это деление — основание столбца. По другой из осей мы откладываем числовое значение категории. Высота столбца будет равна этому числу.

Столбчатые диаграммы бывают:

1. Вертикальные
2. Горизонтальные

В вертикальной диаграмме столбцы направлены по вертикальной оси, а в горизонтальной — по горизонтальной оси.

Вертикальная столбчатая диаграмма по таблице выше будет выглядеть так:

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

Горизонтальная столбчатая диаграмма выглядит аналогично, но с перевернутыми осями:

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

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

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

sales

В этой таблице есть такие столбцы:

1. order_id — ID заказа
2. order_date — дата заказа
3. ship_date — дата отгрузки товара
4. ship_mode — класс отгрузки
5. customer_id — ID покупателя
6. customer_name — имя покупателя
7. segment — категория покупателя
8. country — страна
9. city — город
10. state — штат / округ
11. postal_code — почтовый индекс
12. region — регион
13. product_id — ID товара
14. category — категория товара
15. sub_category — подкатегория товара
16. product_name — наименование товара
17. sales — сумма продаж по заказу
18. quantity — количество единиц товара в заказе
19. discount — скидка на заказ
20. profit — прибыль по заказу

Мы хотим получить таблицу суммарных продаж по подкатегориям товаров и визуализировать ее с помощью столбчатой диаграммы. Для этого мы агрегируем таблицу sales по подкатегориям товаров. Мы будем использовать столбцы sub_category и sales.

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

Агрегируем таблицу продаж в суммарные продажи по подкатегориям товаров средствами SQL.

Для этого мы откроем базу данных bar_chart. В ней содержится таблица sales, в которой 1000 строк и 20 столбцов. Ее начало мы видели в таблице Sales выше. Напишем SQL-запрос для агрегации:

SELECT
    sub_category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY sub_category;

В результате этого запроса мы получили таблицу:

total_sales

Ссылка на таблицу

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

Скопируем таблицу в Google Sheets, заменим в столбце total_sales все точки на запятые и построим столбчатую диаграмму:

На рисунке мы видим столбчатую диаграмму суммарных продаж по подкатегориям товаров.

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

SELECT
    sub_category,
    SUM(sales) AS total_sales
FROM sales
GROUP BY sub_category
ORDER BY total_sales DESC;

С помощью такого запроса мы получили отсортированную таблицу:

ordered_total_sales

Ссылка на таблицу

Здесь представлена отсортированная по убыванию таблица продаж по подкатегориям товаров.

Теперь построим по этой таблице горизонтальную столбчатую диаграмму. Для этого скопируем таблицу в Google Sheets и выберем тип диаграммы «Линейчатая»:

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

Теперь попробуем исключить из агрегации штат Нью-Йорк. Напишем агрегацию на SQL с условием:

SELECT
    sub_category,
    SUM(sales) AS total_sales
FROM sales
WHERE state <> 'New York'
GROUP BY sub_category
ORDER BY total_sales DESC;

Посмотрим на таблицу, которую мы получили:

total_sales_without_NY

Ссылка на таблицу

В этой таблице содержатся суммарные продажи по подкатегориям товаров всех штатов кроме штата Нью-Йорк.

Построим по таблице диаграмму:

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

Выводы

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

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

Самостоятельная работа

Возьмите базу из урока, агрегируйте таблицу sales и найдите суммарные продажи по разным штатам. Отсортируйте новую таблицу по убыванию продаж и постройте столбчатую диаграмму. В каких топ-трех штатах самые высокие продажи?