Содержание страницы
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;
И получаем ожидаемый результат.
