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

• Что такое агрегация и зачем она нужна
• Как агрегировать данные о продажах
• Выводы

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

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

Что такое агрегация и зачем она нужна

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

Таблица выглядит так:

sales

ссылка на DB Fiddle с этим датасетом.

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

Как аналитику нам нужно построить суммарные продажи в каждый день — составить таблицу подневных продаж. Но такая таблица будет содержать 500 строк. Поэтому проанализировать максимальные продажи по таблице будет сложно. По ней не будет понятно, как меняются продажи.

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

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

Агрегация – сбор одного статистического показателя по определенной части данных и преобразование этой части данных в одно значение. Примеры статистических показателей: минимум, максимум, среднее, сумма. В SQL для этого есть агрегационные функции: min, max, avg, sum.

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

На этом рисунке мы построили график продаж по четырем точкам, где каждая точка — одна транзакция.

Если мы агрегируем данные и посчитаем суммарную прибыль по каждому из дней, мы получим такой график:

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

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

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

Как агрегировать данные о продажах

Сагрегируем витрину продаж в таблицу подневных продаж. Мы будем использовать базу данных sales_visualization. В базе данных есть одна таблица sales. Напишем SQL-запрос и посмотрим, что в ней содержится:

SELECT * FROM sales;

sales

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

Мы написали запрос, чтобы увидеть содержание таблицы sales. В таблице 501 строка.

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

SELECT
    order_date,
    SUM(sales) AS sum_sales
FROM sales
GROUP BY order_date
ORDER BY order_date;

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

daily_sales

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

Здесь представлена таблица из двух колонок: дата и суммарные продажи в эту дату.

Скопируем агрегированную таблицу в Google Sheets и заменим в столбце sum_sales все точки на запятые. Выбираем Правка -> Найти и заменить.

Теперь построим линейный график подневных продаж:

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

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

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

Выводы

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

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

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

В самостоятельной работе мы будем использовать витрину из урока. Агрегируйте средствами SQL таблицу продаж и найдите средние подневные продажи. Скопируйте итоговую таблицу в Google Sheets и постройте линейный график средних подневных продаж. Видите ли вы выбросы?