Инструкция по использованию баз данных для аналитиков — Руководство маркетингового аналитика Gett
Международный маркетинговый аналитик Gett Алексей Куличевский составил открытый урок с инструкцией по тому, как работать с базами данных, составлять запросы и выгружать необходимую информацию.
Представьте, что вы аналитик маленького пока никому не известного стартапа под названием Uber. У вас есть дерзкий план: перевернуть рынок такси Нью-Йорка. Для этого вам нужно проанализировать рынок — узнать, как люди пользуются такси, сколько тратят на поездки.
Вам повезло — Комиссия по такси и лимузинам Нью Йорка публикует статистику поездок в открытом доступе. Остаётся только взять и проанализировать данные.
Подключаемся к базе
Подключаемся к BigQuery
Базы данных бывают разные. Большинство из них хранятся на серверах. Чтобы к ним подключиться, нужно установить специальную программу, ввести адрес, порт, логин и пароль.
Чтобы избежать этих трудностей, на курсе мы будем работать с Google Big Query. Чтобы им пользоваться, нужен аккаунт в Google, например почта Google. Если аккаунта нет, зарегистрируйтесь.
Пишем простой запрос
Получаем данные из базы
SQL расшифровывается как structured query language и переводится как «язык структурированных запросов». Вы общаетесь с базами в формате запрос-ответ.
Запросы бывают такими:
- SELECT — вы запрашиваете данные у базы.
- CREATE — создаёте в базе новую таблицу, пользователя, процедуру или что-то другое.
- INSERT — добавляете в таблицу новую запись.
- DROP — удаляете таблицу.
На курсе мы будем только забирать данные из базы. Так что нас интересуют запросы SELECT.
Они строятся по одинаковой структуре:
SELECT — перечисляете названия столбцов, которые вам нужны.
FROM — название таблицы, в которой лежат данные .
WHERE — условия отбора.
SELECT, FROM, WHERE и другие операторы принято писать прописными буквами, чтобы проще читать большие запросы. Если напишете строчными, запрос не сломается.
В левом нижнем углу вы увидите строку Public Datasets — это публичные наборы данных, с которыми могут работать кто угодно. Последний в выпадающем списке — nyc-tlc: yellow. Это статистика такси в Нью-Йорке. Открывайте его и кликайте по trips — появится список столбцов.
Там много полей, но нам нужны следующие:
- Pickup_datetime, dropoff_datetime — дата и время начала и конца поездки.
- Trip_distance — пройденное расстояние в милях.
- Total_amount — итоговая стоимость поездки в долларах.
Пишем запрос.
Ответ базы данных — таблица из четырёх столбцов. В строках таблицы — поездки. Первого января было почти 400 тысяч поездок. Вручную ни расходы, ни средний чек не посчитать.
Агрегация, группировка и сортировка
Агрегируем, группируем, сортируем
Изменим запрос, чтобы вместо списка поездок получить их количество. Для этого заменим в разделе SELECT список столбцов на функцию count (*). Звёздочка в скобках читается как «все столбцы».
Окей, поездки за 1 января мы посчитали. Теперь посчитаем поездки за 2015 год. Писать запрос под каждый день слишком долго. Нужно группировать. Добавим в конец запроса раздел GROUP BY.
Обратите внимание, мы превратили дату и время начала поездки в просто дату c помощью функции date ().
В Big Query нельзя указывать функции в разделе GROUP BY. Поэтому даём имя столбцу с количеством записей с помощью оператора AS и используем это название в группировке
Например,
date (pickup_date) AS ride_date… GROUP BY ride_date.
Уже лучше. Но результаты отсортированы в случайном порядке. В хронологическом будет удобнее. Отсортируем с помощью раздела ORDER BY.
Теперь выясним, сколько нью-йоркцы тратят на такси каждый день. К количеству поездок добавим суммарные расходы и средний чек.
Нам помогут две функции: sum () для подсчёта расходов и avg () для расчёта среднего чека. В скобках указываем total_amount, итоговую стоимость поездки.
Отсортируем по дням, когда средний чек был максимальным.
Последний штрих. В столбцах с расходами и средним чеком Big Query показывает слишком много знаков после запятой. Это неудобно. Округлим числа до одного десятичного знака с помощью функции round ().
Результаты скачиваем в виде файла .csv, чтобы потом проанализировать в Excel или «Google Таблицах».
Сегодня мы работали с одной таблицей и писали простые запросы. Мы выяснили, сколько поездок нью-йоркцы совершили за год и научились вычислять средний чек.
В следующих уроках на smysl. io я расскажу, как в одном запросе объединять данные из нескольких таблиц. Например, мы научимся получать список самых продаваемых товаров в магазине и сегментировать клиентов по количеству покупок.
© vc.ru
