Агрегация для Scatter Chart — Продвинутая аналитика на SQL

• Точечная диаграмма
• Агрегация для точечной диаграммы
• Выводы

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

Точечные диаграммы отличаются тем, что их легко читать, значит, со знаниями о точечных диаграммах вы сможете строить отчеты для широкого круга людей.

Точечная диаграмма

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

avg_sales_profit

В этой таблице мы видим три столбца:

• sub_category — категория товара
• avg_sales — средние продажи по категориям товаров
• avg_profit — средняя прибыль по категориям товаров

По этой таблице мы хотим выявить взаимосвязь между средними продажами и средней прибылью. Для визуализации этой взаимосвязи хорошо подходят точечные диаграммы или Scatter chart.

Посмотрим, как выглядит точечная диаграмма:

На точечной диаграмме, которая изображает связь прибыли и продаж, мы видим по горизонтальной оси переменную avg_sales, а по вертикальной — avg_profit.

Точечные диаграммы используют для отображения отношения двух переменных, например, роста и веса людей или прибыли и продаж. Каждая пара значений двух переменных — это одна точка, где по оси X одна переменная, а по оси Y — вторая.

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

Мы выделим два вида точечных диаграмм:

1. Обычная точечная диаграмма или Scatter chart. Она состоит из точек, координаты которых — это пары значений наших двух переменных
2. Пузырьковая диаграмма или Bubble chart. Она состоит из «пузырьков», координаты которых — пары значений наших переменных, а размер пузырька определяется третьей переменной

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

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

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

Теперь мы перейдем к практике и агрегируем данные для точечной диаграммы продаж и прибыли.

Агрегация для точечной диаграммы

Мы будем работать с базой данных scatter_chart. В ней содержится одна таблица sales:

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 и прибыль profit по подкатегориям товаров sub_category, чтобы получить средние продажи и прибыль. Для этого мы используем агрегацию по подкатегориям с помощью GROUP BY и агрегирующую функцию AVG, чтобы вычислить среднее.

Напишем такой SQL-запрос:

SELECT
    sub_category,
    AVG(sales) AS avg_sales,
    AVG(profit) AS avg_profit
FROM sales
GROUP BY sub_category;

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

avg_sales_profit

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

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

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

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

SELECT
    sub_category,
    AVG(sales) AS avg_sales,
    AVG(profit) AS avg_profit,
    SUM(quantity) AS sum_quantity
FROM sales
GROUP BY sub_category;

В результате мы получили новую таблицу:

SalesProfitQuantity

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

В этой таблице четыре столбца:

• sub_category — подкатегория товаров
• avg_sales — средние продажи для подкатегории товаров
• avg_profit — средняя прибыль для подкатегории товаров
• sum_quantity — суммарное количество всех проданных товаров по одной подкатегории

Теперь скопируем эту таблицу в Google Sheets и построим диаграмму. В качестве типа выберем «Пузырьковая диаграмма». В настройках в поле «Размер» выберем параметр sum_quantity. И отключим легенду в «Дополнительные» -> «Легенда» -> «Расположение» -> «Не выбрано». Новая диаграмма будет выглядеть так:

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

Выводы

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

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

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

Для самостоятельной работы используйте базу данных из урока. Агрегируйте продажи sales и прибыль profit по штатам state и получите средние продажи и прибыль по штатам. По этим данным постройте точечную диаграмму. Также добавьте в агрегацию суммарное количество товаров quantity по штатам. Какие закономерности вы видите? Какие штаты самые убыточные?