Продвинутая аналитика на SQL
2026-02-26 17:11 Diff
https://ru.hexlet.io/courses/analytics-with-sql/lessons/scatter-chart/theory_unit

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

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

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

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

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-запрос:

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

avg_sales_profit

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

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

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

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

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

SalesProfitQuantity

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

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

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

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

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

Выводы

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

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