Базы данных. Начало

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

 

Представьте, что вы аналитик маленького, пока никому не известного стартапа под названием «Убер». У вас есть дерзкий план: перевернуть рынок такси Нью-Йорка. Для этого вам нужно проанализировать рынок — узнать, как люди пользуются такси, сколько тратят на поездки.

Вам повезло — Комиссия по такси и лимузинам Нью Йорка публикует статистику поездок в открытом доступе. Остаётся только взять и проанализировать данные.

 

Подключаемся к базе

 

https://www.youtube.com/watch?v=vTnD86uoCDc
Подключаемся к Бигквери

 

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

Чтобы избежать этих трудностей, на курсе мы будем работать с Гугл Бигквери. Чтобы им пользоваться, нужен аккаунт в Гугле, например Гугл-почта. Если аккаунта нет, зарегистрируйтесь.

 

Пишем простой запрос

 

https://www.youtube.com/watch?v=DsxhLMGrnbg
Получаем данные из базы

 

SQL расшифровывается как structured query language и переводится как язык структурированных запросов. Вы общаетесь с базами в формате запрос-ответ.

Запросы бывают такими:

  • SELECT — вы запрашиваете данные у базы;
  • CREATE — создаёте в базе новую таблицу, пользователя, процедуру или что-то другое;
  • INSERT — добавляете в таблицу новую запись;
  • DROP — удаляете таблицу.

На курсе мы будем только забирать данные из базы. Так что нас интересуют запросы SELECT.

Они строятся по одинаковой структуре:

SELECT

перечисляете названия столбцов, которые вам нужны,

FROM

название таблицы, в которой лежат данные,

WHERE

условия отбора.

SELECT, FROM, WHERE и другие операторы принято писать прописными буквами, чтобы проще читать большие запросы. Если напишете строчными, запрос не сломается.

В левом нижнем углу вы увидите строку Public Datasets — это публичные наборы данных, с которыми могут работать кто угодно. Последний в выпадающем списке — nyc-tlc:yellow. Это статистика такси в Нью-Йорке. Открывайте его и кликайте по trips — появится список столбцов.

 

Там много полей, но нам нужны следующие:

  • Pickup_datetimedropoff_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 года

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

 

Агрегация, группировка и сортировка

 

https://www.youtube.com/watch?v=luaY_pRWFlo
Агрегируем, группируем, сортируем

 

Изменим запрос, чтобы вместо списка поездок получить их количество. Для этого заменим в разделе 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 года

СГРУППИРУЙ ПО

дате начала поездки

 

Обратите внимание, мы превратили дату и время начала поездки в просто дату c помощью функции 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, чтобы потом проанализировать в Экселе или Гугл Таблицах.

В следующий раз

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

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