Обобщение, группировка и сортировка. Реальные маркетинговые запросы

COUNT, DISTINCT, SUM, MIN, MAX, AVG

Здравствуйте и вновь добро пожаловать на занятия по теме «SQL для маркетологов».

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

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

SELECT COUNT(*)

FROM user_actions

WHERE product = ‘Mango’;

Теперь предположим, что мы хотим узнать, сколько различных фруктов находятся в базе данных. Поскольку нам нужно выяснить лишь количество различных фруктов, нам понадобится служебное слово DICTINCT:

SELECT COUNT(DISTINCT product)

FROM user_actions;

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

SELECT SUM(price)

FROM user_actions

WHERE product = ‘Mango’ AND action = ‘purchase’;

Мы также можем найти среднюю, минимальную и максимальную стоимость в таблице:

SELECT AVG(price)

FROM user_actions

WHERE product = ‘Mango’;

Правда, поскольку все цены в таблице у нас установлены равными 0,99 доллара, то, конечно, все эти функции дадут значение 0,99 доллара.

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

 

GROUP BY, SORT, LIMIT

Сейчас рассмотрим более сложные операции с данными функциями. Выше мы суммировали лишь одну величину – стоимость манго, но что, если нам необходимо выяснить общую стоимость каждого продукта в таблице? Для этого используется оператор GROUP BY:

SELECT product, SUM(price)

FROM user_actions

WHERE actions = ‘purchase’

GROUP BY product;

Попробуем теперь проделать это в командной строке.

select product, sum(price) from user_actions where action = ‘purchase’ group by product

В результате мы видим все продукты и количество потраченных на них денег.

Допустим теперь, что нам нужно узнать доход в порядке убывания. Для этого необходимо сделать две вещи. Во-первых, добавить в конце оператор ORDER BY, а во-вторых, указать название столбца для упорядочения:

SELECT product, SUM(price) as total_revenue

FROM user_actions

WHERE actions = ‘purchase’

GROUP BY product

ORDER BY total_revenue desc;

Обратите внимание, что по умолчанию упорядочивание идёт по возрастанию, сейчас же мы указываем порядок убывания. В командной строке:

select product, sum(price) as total_revenue from user_actions where action = ‘purchase’ group by product order by total_revenue desc;

Теперь информация упорядочена.

А теперь предположим, что нам нужна информация лишь по трём лучшим продуктам. Для этого используется служебное слово LIMIT:

SELECT product, SUM(price) as total_revenue

FROM user_actions

WHERE actions = ‘purchase’

GROUP BY product

ORDER BY total_revenue desc

LIMIT 3;

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

Попробуем теперь в командной строке:

select product, sum(price) as total_revenue from user_actions where action = ‘purchase’ group by product order by total_revenue desc limit 3;

Теперь выводится лишь три строки.

 

Воронки продаж, ежегодный доход и продажи по местоположению

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

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

SELECT action, COUNT (*)

FROM user_actions

GROUP BY action;

Попробуем применить этот запрос в нашей таблице:

select action, count(*) from user_actions group by action;

Следующее – доход по сравнению с прошлым годом, обычно отображаемый в Excel. Для этого нам нужна таблица с двумя столбцами – дата и доход. Поскольку у нас нет таблицы с какими-либо датами, нам придётся использовать скрипт для создания таковой. Скрипт написан мною заранее и помещён в репозитарий. Запустите в командной строке команду

python generate_actions_dt.py

После этого в SQLite создайте новую таблицу и импортируйте в неё данные с помощью команды

CREATE TABLE user_actions_with_dt(name TEXT, product TEXT, action TEXT, price REAL, dt DATETIME);

.mode csv

.import dt_actions.csv user_actions_with_dt

Попробуем:

create table user_actions_with_dt(name text, product text, action text, price real, dt datetime);

.mode csv

.import dt_actions.csv user_actions_with_dt

И тут мы сталкиваемся с проблемой. В новой таблице содержатся дата и время, но нам вовсе не нужно время, да и полная дата тоже – нам необходимо лишь группирование по месяцам. Как же нам преобразовать дату и время в месяцы? Ответом является использование общей для всех языков программирования функции strftime. В качестве аргументов эта функция обычно принимает два параметра – текстовую строку, позволяющую определить формат исходящей информации, и дату/время:

strftime(output_format, datetime_object)

Возвращаемое значение функции – текстовая строка в определённом вами формате, полученная из даты/времени.

Создадим новый столбец «Месяц» (month) и проведём группирование по этому столбцу:

SELECT strftime(‘%Y-%m’, dt) as month, SUM(price) as revenue

FROM user_actions_with_dt

GROUP BY month;

В командной строке это будет выглядеть так:

select strftime(‘%y-%m’, dt) as month, sum(price) as revenue from user_actions_with_dt group by month;

Как вы можете видеть, информация выведена в формате, который подходит для Excel. Вы можете скопировать эту информацию вручную или преобразовать её в формат CSV. Чтобы преобразовать результат в формат CSV, перейдите в режим CSV и используйте команду .out c названием файла, после чего введите только что запускавшийся нами запрос:

.mode csv

.out outputfile.csv

<ваш запрос>

И последний пример, который мы рассмотрим, – это сортировка количества продаж по местоположению. Для этого нам придётся использовать объединения, так как данные по продажам находятся в таблице «Действия клиентов» (user_actions), а данные по местоположению – в таблице «Клиенты» (users):

SELECT COUNT() as sales, location

FROM (SELECT product, location

     FROM user_actions

     INNER JOIN users

     ON user_actions.name = users.name)

GROUP BY location

ORDER BY sales DESC;

Тут мы встречаемся с несколькими новыми понятиями.

Во-первых, обратите внимание, что функция COUNT в действительности не требует никаких аргументов. В некоторых версиях баз данных SQL она требует, но в SQLite – нет. В таком случае это будет просто подсчёт количества строк в данной группе.

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

Попробуем запустить этот запрос с командной строки:

select count() as sales, location from (select product, location from user_actions inner join users on user_actions.name = users.name) group by location order by sales desc;

И получаем ожидаемый результат.

Понравилась статья? Поделить с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: