|
Это урок из онлайн-курса, в котором я делюсь знаниями о том, как формулировать гипотезы, самостоятельно находить и анализировать данные, и принимать решения по результатам анализа. Если интересно, записывайтесь на курс.
|
|
|
Начинаем работать с базами данных. Сегодня разберём, как к ним подключаться и научимся писать запросы. Приготовьтесь, урок долгий.
|
|
|
Представьте, что вы аналитик маленького, пока никому не известного стартапа под названием «Убер». У вас есть дерзкий план: перевернуть рынок такси Нью-Йорка. Для этого вам нужно проанализировать рынок — узнать, как люди пользуются такси, сколько тратят на поездки.
Вам повезло — Комиссия по такси и лимузинам Нью Йорка публикует статистику поездок в открытом доступе. Остаётся только взять и проанализировать данные.
|
|
|
|
Подключаемся к Бигквери
|
|
|
Базы данных бывают разные. Большинство из них хранятся на серверах. Чтобы к ним подключиться, нужно установить специальную программу, ввести адрес, порт, логин и пароль.
Чтобы избежать этих трудностей, на курсе мы будем работать с Гугл Бигквери. Чтобы им пользоваться, нужен аккаунт в Гугле, например Гугл-почта. Если аккаунта нет, зарегистрируйтесь.
|
|
|
|
Получаем данные из базы
|
|
|
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 — итоговая стоимость поездки в долларах.
|
|
|
Перейдем к анализу рынка такси Нью-Йорка. Начнём с простого — выгрузим все поездки за один день, например, 1 января 2015 года.
Пишем запрос:
|
|
Запрос
SELECT
pickup_datetime,
dropoff_datetime,
trip_distance,
total_amount
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-01-01 23:59'
|
По-русски
ДОСТАНЬ
дату и время начала, и окончания поездки, ее длительность и итоговую стоимость
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫE
начались в промежутке с 00:00 по 23:59 1 января 2015 года
|
|
|
Ответ базы данных — таблица из четырёх столбцов. В строках таблицы — поездки. Первого января было почти четыреста тысяч поездок. Вручную ни расходы, ни средний чек не посчитать.
|
|
Агрегация, группировка и сортировка
|
|
|
|
Агрегируем, группируем, сортируем
|
|
|
Изменим запрос, чтобы вместо списка поездок получить их количество. Для этого заменим в разделе SELECT список столбцов на функцию count(*). Звёздочка в скобках читается как «все столбцы».
|
|
Запрос
SELECT
count(*)
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-01-01 23:59'
|
По-русски
ДОСТАНЬ
количество записей
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫE
начались в промежутке с 00:00 по 23:59 1 января 2015 года
|
|
|
Окей, поездки за 1 января мы посчитали. Теперь посчитаем поездки за 2015 год. Писать запрос под каждый день слишком долго. Нужно группировать. Добавим в конец запроса раздел GROUP BY.
|
|
Запрос
SELECT
date(pickup_datetime) AS ride_date,
count(*) AS rides
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-12-31 23:59'
GROUP BY
ride_date
|
По-русски
ДОСТАНЬ
дату начала поездки и количество поездок
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫЕ
начались в промежутке с 00:00 1 января по 23:59 31 декабря 2015 года
СГРУППИРУЙ ПО
дате начала поездки
|
|
|
Обратите внимание, мы превратили дату и время начала поездки в просто дату, используя функцию date().
|
|
В Бигквери нельзя указывать функции в разделе GROUP BY. Поэтому даём имя столбцу с количеством записей с помощью оператора AS и используем это название в группировке
Например,
date(pickup_date) AS ride_date... GROUP BY ride_date.
|
|
|
Уже лучше. Но результаты отсортированы в случайном порядке. В хронологическом будет удобнее. Отсортируем с помощью раздела ORDER BY.
|
|
Запрос
SELECT
date(pickup_datetime) AS ride_date,
count(*) AS number_of_rides
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-12-31 23:59'
GROUP BY
ride_date
ORDER BY
ride_date
|
По-русски
ДОСТАНЬ
дату начала поездки и количество поездок
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫЕ
начались в промежутке с 00:00 1 января по 23:59 31 декабря 2015 года
СГРУППИРУЙ ПО
дате начала поездки
ОТСОРТИРУЙ ПО
дате начала поездки
|
|
|
Теперь выясним, сколько ньюйоркцы тратят на такси каждый день. К количеству поездок добавим суммарные расходы и средний чек.
Нам помогут две функции: sum() для подсчёта расходов и avg() для расчёта среднего чека. В скобках указываем total_amount, итоговую стоимость поездки.
Отсортируем по дням, когда средний чек был максимальным.
|
|
Запрос
SELECT
date(pickup_datetime) AS ride_date,
count(*) AS number_of_rides,
sum(total_amount) AS costs,
avg(total_amount) AS average_check
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-12-31 23:59'
GROUP BY
ride_date
ORDER BY
average_check DESC
|
По-русски
ДОСТАНЬ
дату начала поездки,количество поездок, сумму затртрат и средний чек
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫЕ
начались в промежутке с 00:00 1 января по 23:59 31 декабря 2015 года
СГРУППИРУЙ ПО
дате поездки
ОТСОРТИРУЙ ПО
среднему чеку по убыванию
|
|
|
Последний штрих. В столбцах с расходами и средним чеком Бигквери показывает слишком много знаков после запятой. Это неудобно. Округлим числа до одного десятичного знака с помощью функции round().
|
|
Запрос
SELECT
date(pickup_datetime) AS ride_date,
count(*) AS number_of_rides,
round(sum(total_amount)) AS costs,
round(avg(total_amount)) AS average_check
FROM
[nyc-tlc:yellow.trips]
WHERE
pickup_datetime >= '2015-01-01 00:00'
AND pickup_datetime <= '2015-12-31 23:59'
GROUP BY
ride_date
ORDER BY
average_check DESC
|
По-русски
ДОСТАНЬ
дату начала поездки,количество поездок, сумму затрат и средний чек, округленные до 0.1
ИЗ ТАБЛИЦЫ
trips из набора данных nyc-tlc.yellow
КОТОРЫЕ
начались в промежутке с 00:00 1 января по 23:59 31 декабря 2015 года
СГРУППИРУЙ ПО
дате поездки
ОТСОРТИРУЙ ПО
среднему чеку по убыванию
|
|
|
Результаты скачиваем в виде файла .csv, чтобы потом проанализировать в Экселе или Гугл Таблицах.
В следующий раз
Сегодня мы работали с одной таблицей и писали простые запросы. Мы выяснили, сколько поездок нью-йоркцы совершили за год и научились вычислять средний чек.
В четвёртом уроке я расскажу, как в одном запросе объединять данные из нескольких таблиц. Например, мы научимся получать список самых продаваемых товаров в магазине и сегментировать клиентов по количеству покупок.
Домашка тоже будет на следующей неделе.
|
|
|
Стей тюнд,
Алексей Куличевский
|
|
|
|
