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

• Круговая диаграмма
• Виды круговой диаграммы
• Агрегация для круговой диаграммы
• Кольцевая и разрезанная диаграммы
• Выводы

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

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

Круговая диаграмма

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

total_sales

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

• Тип товара — это значение, которое выражается строкой
• Суммарные продажи — это числовое значение

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

Посмотрим на круговую диаграмму по данным таблицы TotalSales:

В круговой диаграмме весь круг — это сумма всех продаж по всем категориям или 100%. А сумма продаж по каждой из категорий — доля, которую занимает одна категория в процентах от 100%.

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

Виды круговой диаграммы

Круговые диаграммы бывают трех типов:

Круговая диаграмма или Pie chart

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

Кольцевая диаграмма или Doughnut chart

Эта диаграмма похожа на круговую, только в ней есть отверстие посередине. Пример с суммарными продажами выглядит так:

Отличие кольцевой диаграммы от круговой в том, что в центре кольцевой диаграммы есть отверстие.

Разрезанная круговая диаграмма или Exploded pie chart

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

Разрезанная диаграмма будет выглядеть так:

Перейдем к практике и агрегируем данные о продажах для круговой диаграммы.

Агрегация для круговой диаграммы

Нам дана таблица sales. Выполним запрос, чтобы посмотреть на содержимое таблицы:

SELECT * FROM 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 — прибыль по заказу

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

Агрегируем таблицу Sales по подкатегориям товаров. Мы просуммируем все продажи по каждой отдельной категории с помощью функции GROUP BY. Мы будем использовать столбцы sub_category и sales. По столбцу sub_category мы агрегируем данные, а по столбцу sales будем считать суммарные продажи по каждой категории:

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

Мы получили такую таблицу:

TotalSales

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

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

Теперь построим круговую диаграмму по этой таблице. Скопируем ее в Google Sheets и заменим в столбце total_sales все точки на запятые. Вставим диаграмму и выберем тип диаграммы «Круговая диаграмма». Мы получили диаграмму, которую видели выше:

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

Убедимся, что Google Sheets считает процентные значения по категориям. Выполним запрос, который выведет сумму всех продаж:

SELECT sum(sales)
FROM sales;

Мы выполнили запрос и получили значение суммы всех продаж 228666.7670999999.

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

Скопируем это значение.

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

SELECT
    sub_category,
    SUM(sales) / 228666.7670999999 * 100 AS total_sales_per
FROM sales
GROUP BY sub_category;

В этом запросе мы рассчитали процент суммарных продаж по категориям от суммы всех продаж. Переменная total_sales_per — это процент продаж по категориям.

Запрос выдал нам таблицу:

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

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

1. sub_category — категория, по которой мы агрегировали данные продаж
2. total_sales_per — процентные доли продаж по категориям относительно суммарных продаж

Скопируем эту таблицу в Google Sheets и построим круговую диаграмму:

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

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

Кольцевая и разрезанная диаграммы

Будем использовать таблицу TotalSales. Выделим столбцы с таблицей в Google Sheets и вставим диаграмму. Выберем тип диаграммы «Кольцевая диаграмма»:

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

Google Sheets предоставляет возможности строить объемные круговые диаграммы. Снова выделим данные о продажах и построим диаграмму. В типе диаграммы выберем «Объемная круговая диаграмма»:

На рисунке мы видим объемную диаграмму. Процентные соотношения продаж по категориям остаются теми же.

Чтобы превратить любую круговую диаграмму в разорванную, нужно перейти в «Дополнительные» -> «Сектор». В этой вкладке мы выделим отдельные сектора, если укажем расстояние сектора от центра.

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

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

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

Мы видим, что категории с максимальными продажами — это Phones, Chairs и Machines. Выделим эти категории и построим разорванную круговую диаграмму. Для этого выберем во вкладке «Сектор» Phones и укажем расстояние от центра 25%. То же самое мы проделаем с категориями Chairs и Machines и получим диаграмму:

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

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

Выводы

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

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

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

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

Для самостоятельной работы мы возьмем базу данных из урока. Агрегируйте суммарную прибыль profit по категориям товаров sub_category и постройте круговую диаграмму. Найдите три самые прибыльные категории товаров и постройте по ним разорванную диаграмму. Отличается ли круговая диаграмма по прибыли от круговой диаграммы по продажам?