⚡ Ќовый онлайн курс по —етевым “ехнологи€м

до запуска осталось

ћерион Ќетворкс

28 минут чтени€

SQL, произноситс€ как "эс-кью-эль", €вл€етс€ критическим инструментом дл€ ученых в области данных. Ќа самом деле, возможно, это самый важный €зык дл€ работы с данными. ¬ этом руководстве дл€ начинающих рассмотрим основы SQL.

Ќачнем с ответа на простой вопрос:


„то такое SQL?

SQL (Structured Query Language) расшифровываетс€ как €зык структурированных запросов. язык запросов - это своего рода €зык программировани€, предназначенный дл€ облегчени€ получени€ конкретной информации из баз данных, именно это делает SQL. ѕроще говор€, SQL - это €зык баз данных.

Ёто важно, потому что большинство компаний хран€т свои данные в базах данных. » хот€ есть много типов баз данных (как MySQL, PostgreSQL, Microsoft SQL Server), большинство из них говор€т на SQL, так что, вз€в на вооружение основы SQL, вы сможете работать с любым из них.

ƒаже если вы планируете выполнить анализ на другом €зыке, таком как Python, в большинстве компаний, скорее всего, потребуетс€ использовать SQL дл€ извлечени€ необходимых данных из базы данных компании. —огласно данным платформы Indeed на данный момент на только в —Ўј существует более 80 000 предложений по SQL.

ƒавайте начнем с изучени€ SQL! ¬ руководстве мы будем работать с набором данных сервиса обмена велосипедами Hubway, который включает в себ€ данные о более чем 1,5 миллионах поездок, совершенных через этот сервис.

сервис обмена велосипедами Hubway

ћы начнем с того, что рассмотрим базы данных, каковы они и почему мы их используем, прежде чем начнем писать собственные запросы в SQL.

ƒл€ изучени€ нам потребуетс€ база, которую вы можете загрузить здесь:


ќсновы SQL: рел€ционные базы данных

–ел€ционна€ база данных - это база данных, котора€ хранит св€занную информацию в нескольких таблицах и позвол€ет запрашивать информацию из нескольких таблиц одновременно.

Ћегче пон€ть, как это работает, продумав пример. ѕредставьте, что у вас есть бизнес и вы хотите следить за информацией о продажах. ¬ Excel можно настроить электронную таблицу со всей информацией, которую необходимо отслеживать, в виде отдельных столбцов: Ќомер заказа, дата, сумма к оплате, номер отслеживани€ отгрузки, им€, адрес и номер телефона клиента.

¬ Excel можно настроить электронную таблицу

“акое решение отлично подойдет дл€ отслеживани€ информации, на начальных этапах работы, но, когда вы начнете получать повторные заказы от одного и того же клиента, вы обнаружите, что их им€, адрес и номер телефона хран€тс€ в нескольких строках вашей электронной таблицы.

ѕо мере роста вашего бизнеса и увеличени€ количества отслеживаемых заказов эти избыточные данные займут ненужное место и, как правило, сниз€т эффективность вашей системы отслеживани€ продаж. “акже могут возникнуть проблемы с целостностью данных. Ќапример, нет гарантии, что дл€ каждого пол€ будет установлен правильный тип данных или что им€ и адрес будут каждый раз вводитьс€ одинаково.

целостность данных

ѕри использовании рел€ционной базы данных, как и на приведенной выше схеме, можно избежать всех этих проблем. ћожно настроить две таблицы: одну дл€ заказов и одну дл€ клиентов. “аблица " лиенты" будет включать уникальный идентификационный номер дл€ каждого клиента, а также им€, адрес и номер телефона, которые мы уже отслеживали. “аблица "«аказы" будет включать номер заказа, дату, сумму к оплате, номер отслеживани€ и вместо отдельного пол€ дл€ каждой позиции данных клиента будет содержать столбец дл€ идентификатора клиента.

Ёто позвол€ет нам получить всю информацию о клиенте дл€ любого заказа, но записи будут введены в базу только один раз, избега€ при этом избыточных данных.


— чем бы будем работать

Ќачнем с открыти€ нашей базу данных. Ѕаза данных имеет две таблицы, trips ( далее "поездки") и stations (далее "станции"). ƒавайте просто посмотрим на таблицу trips. ќна содержит следующие столбцы:

  • id - уникальное целое число, которое служит ссылкой на каждую поездку.
  • duration - продолжительность поездки, измеренна€ в секундах;
  • start_date - ƒата и врем€ начала поездки;
  • start_station - целое число, соответствующее столбцу id в таблице stations дл€ станции, на котором был арендован велосипед;
  • end_date - ƒата и врем€ окончани€ поездки;
  • end_station - »дентификатор станции, на которой завершилась поездка;
  • bike_number - уникальный идентификатор Hubway дл€ велосипеда, используемого в поездке;
  • sub_type - тип подписки пользовател€. "«арегистрированный" дл€ пользователей с членством, "—лучайный" дл€ пользователей без членства;
  • zip_code - почтовый индекс пользовател€ (доступен только дл€ зарегистрированных участников);
  • birth_date - год рождени€ пользовател€ (доступен только дл€ зарегистрированных участников);
  • gender - пол пользовател€ (доступен только дл€ зарегистрированных участников);

„то мы анализируем

¬низу перечислены вопросы, на которые мы должны ответить, использу€ информацию из Ѕƒ и командами SQL, которые мы изучим чуть позже:

  •  акова была продолжительность самого длительного путешестви€?
  • —колько поездок совершили "зарегистрированные" пользователи?
  •  акова средн€€ продолжительность поездки?
  •  то совершил более длительные поездки: зарегистрированные пользователи или гости?
  •  акой велосипед использовалс€ дл€ большинства поездок?
  •  акова средн€€ продолжительность поездок пользователей старше 30 лет?

 оманды SQL, которые мы будем использовать дл€ ответа на эти вопросы:

  • SELECT
  • WHERE
  • LIMIT
  • ORDER BY
  • GROUP BY
  • AND
  • OR
  • MIN
  • MAX
  • AVG
  • SUM
  • COUNT

”становка и настройка

¬ данном руководстве мы будем использовать систему баз данных под названием SQLite3. SQLite стала частью Python начина€ с версии 2.5, так что, если у вас установлен Python, почти наверн€ка уже в системе есть и SQLite. Python и библиотеку SQLite3 можно легко установить и настроить с помощью Anaconda, если они еще не установлены.

»спользование Python дл€ запуска кода SQL позвол€ет импортировать результаты в датафрейм Pandas, что существенно облегчит отображение результатов в удобном дл€ чтени€ формате. Ёто также означает, что мы сможем выполнить дальнейший анализ и визуализацию данных, которые мы получим из базы данных, хот€ это будет выходить за рамки данного учебного пособи€.

≈сли же не хотите использовать или устанавливать Python, SQLite3 можно запустить из командной строки. ѕросто загрузите "предварительно скомпилированные двоичные файлы" с сайта SQLite3 и используйте следующую команду дл€ открыти€ базы данных:

~$ sqlite hubway.db SQLite version 3.14.0 2016-07-26 15:17:14 Enter ".help" for usage hints.sqlite>	

ќтсюда можно просто ввести запрос, который нужно выполнить, и увидеть возвращенные данные, в нашем окне терминала.

јльтернативой использованию терминала €вл€етс€ подключение к базе данных SQLite через Python. Ёто позволит нам использовать Jupyter дл€ отображени€ результатов наших запросов в аккуратно отформатированной таблице.

ƒл€ этого определим функцию, котора€ принимает наш запрос (сохраненный в виде строки) в качестве входных данных и показывает результат в виде форматированного датафрейма:

import sqlite3
import pandas as pd
db = sqlite3.connect('hubway.db')
def run_query(query):
    return pd.read_sql_query(query,db)

SELECT

ѕерва€ команда, с которой мы будем работать - SELECT. SELECT будет основой почти каждого запроса, который мы напишем - он сообщает базе данных, какие столбцы мы хотим видеть. ћожно указать столбцы по имени (разделенные зап€тыми) или использовать подстановочный знак * дл€ возврата всех столбцов из таблицы.

¬ дополнение к столбцам, которые мы хотим получить, мы также должны сообщить базе данных, из какой таблицы их получить. ƒл€ этого используетс€ ключевое слово FROM, за которым следует им€ таблицы. Ќапример, если требуетс€ просмотреть start_date и bike_number дл€ каждой поездки в таблице trips, можно использовать следующий запрос:

SELECT start_date, bike_number FROM trips;

¬ этом примере мы начали с команды SELECT, чтобы база данных знала, что она должна найти некоторые данные. «атем мы сказали базе данных, что нас интересуют столбцы start_date и bike_number. Ќаконец, чтобы сообщить базе, что столбцы, которые мы хотим получить, €вл€ютс€ частью таблицы trips мы использовали команду FROM.

¬ажно знать, что при записи SQL-запросов необходимо заканчивать каждый запрос точкой с зап€той (;). Ќа самом деле не кажда€ база данных SQL требует этого, но такой стиль считаетс€ хорошим тоном создани€ запросов, поэтому лучше всего сформировать эту привычку.


LIMIT

—ледующа€ команда, которую мы должны знать перед началом выполнени€ запросов к нашей базе данных Hubway, - LIMIT. LIMIT просто сообщает базе данных, сколько строк требуетс€ вернуть.

«апрос SELECT, который мы рассмотрели в предыдущем разделе, возвращает запрашиваемую информацию дл€ каждой строки в таблице trips, но обычно это очень большое количество данных, в которых нет необходимости в данный момент. ≈сли всех записей из Ѕƒ нужно получить start_date и bike_number только дл€ первых п€ти поездок, к запросу следует добавить ключевое слово LIMIT следующим образом:

SELECT start_date, bike_number FROM trips LIMIT 5;

ћы просто добавили команду LIMIT, а затем число, представл€ющее количество строк, которые мы хотим вернуть. ¬ данном примере мы использовали 5, но вы можете заменить их любым числом, чтобы получить соответствующий объем данных дл€ проекта, над которым вы работаете.

ћы будем много использовать LIMIT в наших запросах к базе данных Hubway в этом учебном пособии - таблица поездок содержит более 1,5 миллионов строк данных, и нам, конечно, не нужно отображать все.

ƒавайте запустим наш первый запрос в базе данных Hubway. —начала сохраним наш запрос в переменной типа "строка", а затем используем функцию, определенную ранее, чтобы отправить запрос к базе данных. ¬згл€ните на следующий пример:

query = 'SELECT * FROM trips LIMIT 5;'
run_query(query)
LIMIT

Ётот запрос использует * в качестве подстановочного символа вместо указани€ возвращаемых столбцов. Ёто означает, что команда SELECT вернула нам каждый столбец из таблицы trips. ћы также использовали функцию LIMIT, чтобы ограничить вывод первыми п€тью строками таблицы.

¬ы часто столкнетесь с ситуацией, когда программисты в своих запросах ключевые слова будут писать с заглавной буквы. Ёто негласное соглашение, которому мы будем следовать в этом учебном пособии, хот€ это в основном вопрос предпочтени€. ѕросто заглавна€ буква облегчает чтение кода, но на самом деле она никак не вли€ет на его функциональность. ≈сли вы предпочитаете писать запросы строчными буквами, они будут выполн€тьс€ правильно.

¬ предыдущем примере были возвращены все столбцы из таблицы поездок. ≈сли бы нас интересовали только длительность и дата начала, подстановочный символ можно было бы заменить на названи€ столбцов следующим образом:

query = 'SELECT duration, start_date FROM trips LIMIT 5'
run_query(query)
SELECT duration, start_date FROM trips LIMIT 5

ORDER BY

ѕоследн€€ команда, которую мы должны знать, прежде чем мы сможем ответить на первый из наших вопросов, - ORDER BY. Ёта команда позвол€ет отсортировать базу данных по указанному столбцу.

„тобы использовать его, просто укажите им€ столбца, по которому требуетс€ выполнить сортировку. ѕо умолчанию, ORDER BY сортирует результаты по возрастанию. ≈сли требуетс€ указать пор€док сортировки базы данных, можно добавить ключевое слово ASC дл€ сортировки по возрастанию или DESC - по убыванию.

Ќапример, если требуетс€ отсортировать вывод из таблицы поездок от самой короткой по продолжительности до самой длинной, в запрос можно добавить следующую строку:

ORDER BY duration ASC

¬ооружившись командами SELECT, LIMIT и ORDER BY мы теперь можем попытатьс€ ответить на наш первый вопрос: какова была продолжительность самой длительной поездки?

„тобы ответить на этот вопрос, полезно разбить его на разделы и определить, какие команды нам потребуютс€ дл€ каждой части.

—начала нам нужно извлечь информацию из столбца duration таблицы поездок. «атем, чтобы найти, кака€ поездка сама€ длинна€, мы можем отсортировать столбец длительности в пор€дке убывани€. »так, чтобы сформировать запрос, который получит нужную нам информацию, мы можем следовать следующей последовательности:

  • »спользовать SELECT дл€ извлечени€ столбца duration FROM таблицы trips.
  • »спользовать ORDER BY дл€ сортировки по столбцу duration и ключевое слово DESC дл€ сортировки в пор€дке убывани€.
  • »спользовать LIMIT, чтобы ограничить вывод 1 строкой

»спользование этих команд таким образом вернет одну строку с наибольшей длительностью, что даст нам ответ на наш вопрос.

≈ще одна деталь, которую следует отметить - по мере увеличени€ длины запроса, чтобы облегчить чтение кода, лучше раздел€ть запрос на несколько строк. Ёто, как и в случае с заглавным шрифтом, вопрос личного предпочтени€. Ёто не вли€ет на выполнение кода (система считывает код от начала до точки с зап€той), но может сделать запросы более четкими и простыми дл€ выполнени€. ¬ Python можно разделить строку на несколько строк, использу€ тройные кавычки.

ƒавайте выполним запрос и вы€сним, сколько длилась сама€ длинна€ поездка.

query = '''
SELECT duration FROM trips
ORDER BY duration DESC
LIMIT 1;
'''
run_query(query)
ORDER BY

“еперь мы знаем, что сама€ длинна€ поездка длилась 9999 секунд, или чуть более 166 минут. — максимальным значением 9999, однако, мы не знаем, действительно ли это длина самого длинного рейса или база данных была настроена только дл€ разрешени€ четырехзначного номера.

≈сли это правда, то более длительные поездки сокращаютс€ базой данных до четырех символов. ¬ результате ожидаемо увидеть много поездок длительностью 9999 секунд. „тобы убедитьс€, что все отрабатывает корректно, попробуем выполнить тот же запрос, что и раньше, но изменим LIMIT так, чтобы вернуть 10 наибольших по длительности поездок:

query = '''
SELECT durationFROM trips
ORDER BY duration DESC
LIMIT 10
'''
run_query(query)
LIMIT 10

—уд€ по выводу, в базе нет кучи поездок продолжительностью 9999, а это означает, что база ничего не отсекает. Ќо все еще трудно сказать, €вл€етс€ ли это реальной длиной поездки или только максимально допустимое значение.

Hubway взимает дополнительную комиссию за поездки длительностью более 30 минут (кто-то, продержавший велосипед в течение 9999 секунд, должен был бы заплатить дополнительные 25 долларов в виде комиссии), поэтому вполне веро€тно, что разработчики решили обойтись 4-м€ цифрами дл€ отслеживани€ большинства поездок.


WHERE

ѕредыдущие команды отлично подход€т дл€ извлечени€ отсортированной информации дл€ определенных столбцов, но что делать, если существует определенное подмножество данных, которые мы хотим просмотреть? ¬от здесь на помощь приходит WHERE.  оманда WHERE позвол€ет использовать логический оператор дл€ указани€ строк, которые должны быть возвращены. Ќапример, можно использовать следующую команду, чтобы вернуть каждую поездку, выполненную велосипедом с ID B00400:

WHERE bike_number = "B00400"

Ќаверное, уже заметили, что в этом запросе используем кавычки. Ёто потому, что bike_number хранитс€ в виде строки. ≈сли столбец содержит числовые типы данных, необходимость в кавычках отпадает.

ƒавайте напишем запрос, который использует WHERE дл€ возврата каждого столбца в таблице поездок дл€ каждой строки длительностью больше 9990 секунд:

query = '''
SELECT * FROM trips
WHERE duration > 9990;
'''
run_query(query)
WHERE

 ак мы видим, этот запрос вернул 14 различных поездок, кажда€ продолжительностью 9990 секунд или более. „то-то, что выдел€етс€ в этом запросе, что все, кроме одного из результатов имеет sub_type "—лучайный". ¬озможно, это свидетельствует о том, что "«арегистрированные" пользователи больше осведомлены о дополнительных сборах за длительные поездки. ћожет быть, ’абуэй мог бы лучше передать свою политику ценообразовани€ случайным пользовател€м, чтобы помочь им избежать чрезмерных расходов.

ћы уже видим, что даже SQL запрос начального уровн€ может помочь нам ответить на бизнес-вопросы и найти информацию в наших данных.

¬озвраща€сь к WHERE, мы также можем объединить несколько логических тестов в нашем предложении WHERE, использу€ AND или OR. ≈сли, например, в нашем предыдущем запросе мы хотели получить только поездки длительностью более 9990 секунд, которые также имеют sub_type Registered, мы могли бы использовать оператор AND дл€ указани€ обоих условий.

¬от еще одна лична€ рекомендаци€: используйте круглые скобки, чтобы разделить каждый логический блок, как показано в коде ниже. Ёто не об€зательно дл€ работы кода, но круглые скобки упрощают понимание запросов по мере их усложнени€.

ƒавайте сейчас запустим этот запрос. ћы уже знаем, что это должно вернуть только один результат, так что должно быть легко проверить, что мы получили его правильно:

query = '''
SELECT * FROM trips
WHERE (duration >= 9990) AND (sub_type = "Registered")
ORDER BY duration DESC;
'''
run_query(query)
WHERE (duration >= 9990)

—ледующий вопрос, который мы изложили в начале статьи, - "—колько поездок было совершено "зарегистрированными" пользовател€ми?" „тобы ответить на него, мы могли бы выполнить тот же запрос, что и выше, и изменить выражение WHERE, чтобы вернуть все строки, где sub_type равно "Registered", а затем подсчитать их.

Ќа самом деле SQL имеет встроенную команду COUNT дл€ такого рода подсчетов, так что напр€гать глаза нам не придетс€.

COUNT позвол€ет выполн€ть вычислени€ в базе данных и избавить нас от проблем с написанием дополнительных сценариев дл€ подсчета результатов. „тобы использовать его, мы просто включаем COUNT (column_name) вместо (или в дополнение к) столбцов, которые мы хотим выбрать, как указано ниже:

SELECT COUNT(id)
FROM trips

¬ данном случае не имеет значени€, какой столбец мы выбираем дл€ подсчета, потому что каждый столбец должен иметь данные дл€ каждой строки в нашем запросе. Ќо иногда запрос может иметь отсутствующие (или "нулевые") значени€ дл€ некоторых строк. ≈сли мы не уверены, содержит ли столбец значени€ null, мы можем запустить COUNT дл€ столбца id - столбец id никогда не имеет значение null, поэтому мы можем быть уверены, что наш счетчик не пропустит ничего.

ћы также можем использовать COUNT (1) или COUNT (*) дл€ подсчета каждой строки в нашем запросе. —тоит отметить, что иногда может потребоватьс€ выполнение COUNT дл€ столбца со значени€ми NULL. Ќапример, может потребоватьс€ узнать, сколько строк в нашей базе данных имеют пустые значени€ дл€ указанного столбца.

ƒавайте посмотрим на запрос, который позволит ответить на наш вопрос. ћы можем использовать SELECT COUNT (*) дл€ подсчета общего количества возвращенных строк и WHERE sub_type = "Registered", чтобы убедитьс€, что мы подсчитываем только поездки, сделанные зарегистрированными пользовател€ми.

query = '''
SELECT COUNT(*)FROM trips
WHERE sub_type = "Registered";
'''
run_query(query)
COUNT

Ётот запрос сработал и вернул ответ на наш вопрос. Ќо заголовок столбца не особенно показателен. ≈сли кто-то еще посмотрит на эту таблицу, он не сможет пон€ть, что это значит. ≈сли мы хотим сделать наши результаты более читаемыми, мы можем использовать ключевое слово AS, чтобы дать нашему выводу псевдоним. ƒавайте снова запустим предыдущий запрос, но дадим

query = '''
SELECT COUNT(*) AS "Total Trips by Registered Users"
FROM trips
WHERE sub_type = "Registered";
'''
run_query(query)
SELECT COUNT(*)

‘ункции агрегировани€

COUNT не единственный математический трюк, которым располагает SQL. ћы также можем использовать функции SUM, AVG, MIN и MAX дл€ возврата суммы, среднего значени€, минимума и максимума столбца соответственно. Ёти функции нар€ду с COUNT называютс€ функци€ми агрегировани€.

»так, чтобы ответить на наш третий вопрос " акова была средн€€ продолжительность поездки?", мы можем использовать функцию AVG дл€ столбца duration (и, еще раз, использовать AS, чтобы дать нашему выходному столбцу более описательное им€):

query = '''
SELECT AVG(duration) AS "Average Duration"
FROM trips;
'''
run_query(query)
AVG(duration)

ѕолучаетс€, что средн€€ продолжительность поездки составл€ет 912 секунд, что составл€ет около 15 минут. Ёто имеет смысл, так как мы знаем, что Hubway взимает дополнительную плату за поездки в течение 30 минут. —ервис рассчитан на то, чтобы участники брали велосипеды на короткие дистанции, односторонние поездки.

 ак насчет нашего следующего вопроса, какие пользователи выполн€ют более длительные поездки? ћы уже знаем один способ ответить на этот вопрос - мы могли бы запустить два запроса SELECT AVG (duration) FROM trips добавив ключевое слово WHERE, которые ограничивают вывод дл€ "зарегистрированных" и дл€ "случайных" пользователей по отдельности.

Ќо давайте сделаем это по-другому. SQL имеет в своем арсенале функцию и дл€ ответа на этот вопрос в одном запросе с помощью команды GROUP BY.


GROUP BY

GROUP BY раздел€ет строки на группы на основе содержимого определенного столбца и позвол€ет выполн€ть агрегирующие функции дл€ каждой группы.

„тобы лучше пон€ть, как это работает, давайте посмотрим на столбец gender.  ажда€ строка может иметь одно из трех возможных значений в столбце пола "ћужской", "∆енский" или "Ќулевой" (отсутствует; у нас нет гендерных данных дл€ случайных пользователей).

 огда мы используем GROUP BY, база данных будет раздел€ть каждую из строк на разные группы на основе значени€ в столбце пола, примерно так же, как мы можем разделить колоду карт по маст€м. Ёто можно представить, как две группы, в одной все представители мужского пола, в другой - женского.

 ак только у нас будут две отдельные кучи, база данных будет выполн€ть любые агрегирующие функции в нашем запросе по каждой из них по очереди. Ќапример, если бы мы использовали COUNT, запрос подсчитывал бы количество строк в каждой группе и возвращал бы значение дл€ каждого по отдельности.

ƒавайте посмотрим, как именно написать запрос, чтобы ответить на наш вопрос о том, кто совершает более длительные поездки: зарегистрированные пользователи или случайные.

  •  ак и в случае с каждым из наших запросов, мы начнем с SELECT, чтобы сообщить базе данных, какую информацию мы хотим получить. ¬ этом случае нам нужны sub_type и AVG (duration).
  • ћы также включим GROUP BY sub_type, чтобы разделить наши данные по типу подписки и вычислить средние значени€ зарегистрированных и случайных пользователей по отдельности.

¬от как выгл€дит код, когда мы складываем все вместе:

query = '''
SELECT sub_type, AVG(duration) AS "Average Duration"
FROM trips
GROUP BY sub_type;
'''
run_query(query)
SELECT sub_type, AVG(duration)

Ёто больша€ разница! ¬ среднем зарегистрированные пользователи совершают поездки продолжительностью около 11 минут, в то врем€ как случайные пользователи трат€т почти 25 минут на поездку. «арегистрированные пользователи, веро€тно, совершают более короткие и частые поездки, возможно, в рамках поездок на работу. —лучайные пользователи, с другой стороны, трат€т в два раза больше времени на поездку.

¬полне возможно, что случайные пользователи, как правило, приезжие (например, туристы), которые более склонны совершать более длительные поездки, чтобы убедитьс€, что они обойдут и увид€т все достопримечательности.  ак только мы обнаружим эту разницу в данных, компани€ сможет исследовать ее, чтобы лучше пон€ть, что данна€ разница вызвана

ќднако давайте продолжим. Ќаш следующий вопрос был: " акой велосипед использовалс€ дл€ большинства поездок?" ћы можем ответить на это с помощью очень похожего запроса. ¬згл€ните на следующий пример и посмотрите, можете ли вы вы€снить, что делает кажда€ строка - мы пройдем ее шаг за шагом после этого, чтобы вы могли проверить, правильно ли вы получили:

query = '''
SELECT bike_number as "Bike Number", COUNT(*) AS "Number of Trips"
FROM trips
GROUP BY bike_number
ORDER BY COUNT(*) DESC
LIMIT 1;
'''
run_query(query)
велосипедные B00490 вз€ли больше всего поездок

 ак видно из выходных данных, велосипедные B00490 вз€ли больше всего поездок. ƒавайте рассмотрим, как получили данное значение:

  • ѕерва€ строка - это предложение SELECT, указывающее базе данных, что мы хотим получить столбец bike_number и число всех строк в Ѕƒ. ¬ запросе также используетс€ AS дл€ указани€ базе данных отображать каждый столбец с более пон€тным названием.
  • ¬о второй строке используем FROM, чтобы указать, что данные, которые мы ищем, наход€тс€ в таблице поездок.
  • ¬ третьей строке прибегли к хитрости. “ут используетс€ команда GROUP BY, чтобы указать функции COUNT в первой строке подсчитать каждое значение дл€ bike_number отдельно.
  • ¬ четвертой строке у нас есть функци€ ORDER BY, чтобы отсортировать таблицу в пор€дке убывани€ и убедитьс€, что наш наиболее используемый велосипед находитс€ на самом верху.
  • Ќаконец, мы используем LIMIT, чтобы ограничить вывод одной строкой, котора€, как мы знаем, будет велосипедом, который использовалс€ в наибольшем количестве поездок из-за того, как мы сортировали данные на четвертой строке.

јрифметические операторы

Ќаш последний вопрос немного сложнее других. ћы хотим знать среднюю продолжительность поездок зарегистрированных участников в возрасте старше 30 лет.

ћы могли бы просто найти год, в котором родились 30-летние, а затем подставить это значение. Ќо есть более элегантным решение - использование арифметических операций непосредственно в нашем запросе. SQL позвол€ет использовать +, -, * и/дл€ выполнени€ арифметической операции над всем столбцом одновременно.

query = '''
SELECT AVG(duration) FROM trips
WHERE (2017 - birth_date) > 30;
'''
run_query(query)
јрифметические операторы

JOIN

ƒо сих пор мы рассматривали запросы, которые извлекают данные только из таблицы поездок. ќднако одна из причин, по которой SQL настолько эффективен, заключаетс€ в том, что он позвол€ет извлекать данные из нескольких таблиц в одном запросе.

Ќаша база данных по обмену велосипедами содержит вторую таблицу, станции. “аблица stations содержит информацию о каждом терминале в сети Hubway и включает столбец id, на который ссылаетс€ таблица trips.

ѕрежде чем мы начнем работать с некоторыми реальными примерами из этой базы данных, давайте посмотрим на гипотетическую базу данных отслеживани€ заказов из более ранних. ¬ этой базе данных были две таблицы, заказы и клиенты, и они были св€заны столбцом customer_id.

ƒопустим, мы хотели написать запрос, который вернул order_number и name дл€ каждого заказа в базе данных. ≈сли они оба были сохранены в одной таблице, можно было бы использовать следующий запрос:

SELECT order_number, name
FROM orders;

  сожалению, столбец order_number и столбец name хран€тс€ в двух разных таблицах, поэтому необходимо добавить несколько дополнительных команд. ƒавайте подумаем о дополнительных командах, которые должна знать база данных, прежде чем она сможет вернуть нужную информацию:

  • ¬ какой таблице находитс€ столбец order_number?
  • ¬ какой таблице находитс€ столбец name?
  •  ак информаци€ в таблице заказов св€зана с информацией в таблице клиентов?

„тобы ответить на первые два из этих вопросов, мы можем включить имена таблиц дл€ каждого столбца в нашу команду SELECT. —пособ сделать это - просто написать им€ таблицы и им€ столбца, разделив их символом точки ".". Ќапример, вместо SELECT order_number мы бы написали SELECT orders.order_number, customers.name. ƒобавление здесь имен таблиц помогает базе данных находить столбцы, которые мы ищем, указыва€, в какой таблице искать каждый из них.

„тобы сообщить базе данных, как соедин€ютс€ таблицы заказов и клиентов, мы используем JOIN и ON. JOIN указывает, какие таблицы должны быть соединены, а ON указывает, какие столбцы в каждой таблице св€заны.

ћы будем использовать внутреннее соединение, что означает, что команда вернет только те строки, где столбцы, указанные в ON, совпадают. ¬ этом примере мы хотим использовать JOIN дл€ любой таблицы, которую мы не включили в команду FROM. “аким образом, мы можем либо использовать FROM orders INNER JOIN customers, либо FROM customers INNER JOIN orders.

 ак мы уже обсуждали ранее, эти таблицы св€заны между собой столбцом customer_id в каждой таблице. ѕоэтому используем ON, чтобы сообщить базе данных, что эти два столбца ссылаютс€ на одно и то же:

ON orders.customer_ID = customers.customer_id

≈ще раз используем "." чтобы база данных знала, в какой таблице находитс€ каждый из этих столбцов. »так, когда мы сложим все это вместе, мы получим запрос, который выгл€дит следующим образом:

SELECT orders.order_number, customers.name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id

Ётот запрос вернет номер каждого заказа в базе данных нар€ду с именем клиента, который сделал данные заказ.

¬озвраща€сь к нашей базе данных Hubway, мы теперь можем написать несколько запросов, чтобы увидеть JOIN в действии.

ѕрежде чем начать, мы должны взгл€нуть на остальные столбцы в таблице станций. ¬от запрос, чтобы показать нам первые 5 строк, чтобы мы могли увидеть, как выгл€дит таблица станций:

query = '''
SELECT * FROM stations
LIMIT 5;
'''
run_query(query)
JOIN
  • id - уникальный идентификатор дл€ каждой станции (соответствует столбцам start_station и end_station в таблице поездок);
  • station - название станции;
  • municipality - муниципалитет, в котором находитс€ станци€ (Ѕостон, Ѕруклин,  ембридж или —омервилл);
  • lat - Ўирота станции;
  • lng - долгота станции;

ј вот вопросы, на которые мы должны ответить, исследовав данные из базы:

  •  акие станции чаще всего используютс€ дл€ поездок в оба конца?
  • —колько поездок начинаетс€ и заканчиваетс€ в разных муниципалитетах?

 ак и прежде, мы попытаемс€ ответить на некоторые вопросы основыва€сь на данных, первый вопрос: " ака€ станци€ €вл€етс€ наиболее частой отправной точкой?" ƒавайте шаг за шагом сформируем запрос дл€ ответа на этот вопрос:

  • —начала мы используем SELECT дл€ возврата столбца station из таблицы станций и COUNT дл€ получени€ числа строк.
  • ƒалее мы указываем таблицы, которые мы хотим объединить JOIN, и указываем базе данных соединить их столбцом ON start_station из таблицы поездок и столбцом id в таблице станций.
  • «атем переходим к основной части нашего запроса - мы группируем по столбцу станци€ таблицы станций (GROUP BY station), чтобы наш COUNT подсчитал количество поездок дл€ каждой станции отдельно
  • Ќаконец, мы можем отсортировать по нашим COUNT ORDER BY COUNT LIMIT и ограничить вывод дл€ управлени€ количеством результатов.
query = '''
SELECT stations.station AS "Station", COUNT(*) AS "Count"
FROM trips INNER JOIN stations
ON trips.start_station = stations.id
GROUP BY stations.station 
ORDER BY COUNT(*) DESC
LIMIT 5;
'''
run_query(query)
ограничить вывод дл€ управлени€ количеством результатов

≈сли вы знакомы с Ѕостоном, вы поймете, почему это самые попул€рные станции. ёжный вокзал €вл€етс€ одной из главных пригородных железнодорожных станций в городе, „арльз-стрит проходит вдоль реки недалеко от некоторых красивых живописных маршрутов, а улицы Ѕойлстон и Ѕикон наход€тс€ пр€мо в центре вблизи офисных зданий.

—ледующий вопрос, который мы рассмотрим, - какие станции чаще всего используютс€ дл€ поездок туда и обратно? ћы можем использовать тот же запрос, что и раньше. ћы будем выбирать те же выходные столбцы и присоедин€ть таблицы таким же образом, но на этот раз мы добавим условие WHERE, чтобы ограничить наш COUNT поездками, где start_station совпадает с end_station.

query = '''
SELECT stations.station AS "Station", COUNT(*) AS "Count"
FROM trips INNER JOIN stations
ON trips.start_station = stations.id
WHERE trips.start_station = trips.end_station
GROUP BY stations.station
ORDER BY COUNT(*) DESC
LIMIT 5;
'''
run_query(query)
р€д станций из этого списка аналогичен предыдущему списку

 ак мы видим, р€д станций из этого списка аналогичен предыдущему списку, но их количество значительно ниже. —амые загруженные станции те же самые, но более низкие цифры в целом дают возможность предполагать, что люди обычно используют велосипеды Hubway, чтобы добратьс€ от точки A до точки B, а не ездить на велосипеде некоторое врем€, прежде чем вернутьс€ туда, где они начали.

«десь есть одно существенное отличие - Ёспланд, который не был одной из самых загруженных станций по результатам нашего первого запроса, кажетс€ самым загруженным дл€ поездок туда и обратно. ѕочему? „то ж, картина стоит тыс€чи слов. Ёто, безусловно, выгл€дит как хорошее место дл€ велопробега:

хорошее место дл€ велопробега

Ќа следующий вопрос: сколько поездок начинаетс€ и заканчиваетс€ в разных муниципалитетах? Ётот вопрос делает заставл€ет нас двигатьс€ дальше. ћы хотим знать, сколько поездок начинаетс€ и заканчиваетс€ в другом муниципалитете. „тобы достичь этого, нам нужно присоединить таблицу поездок к таблице станций дважды. ќдин раз по столбцу start_station, а затем по столбцу end_station.

ƒл€ этого необходимо создать псевдоним дл€ таблицы станций, чтобы можно было различать данные, относ€щиес€ к start_station, и данные, относ€щиес€ к end_station. ћы можем сделать это точно так же, как присваивали псевдонимы отдельным столбцам, чтобы они отображались с более интуитивным именем, использу€ AS.

Ќапример, чтобы присоединить таблицу станций к таблице поездок с помощью псевдонима "start", можно использовать следующий код. «атем мы можем объединить "start" с именами столбцов с помощью "." дл€ ссылки на данные, полученные из этого конкретного JOIN (вместо второго JOIN, где мы объединим таблицы по end_station столбец):

INNER JOIN stations AS start ON trips.start_station = start.id

¬от как будет выгл€деть последний запрос при его выполнении. ќбратите внимание, что мы использовали " " дл€ представлени€ "не равно", но != также будет работать.

query =
'''
SELECT COUNT(trips.id) AS "Count"
FROM trips INNER JOIN stations AS start
ON trips.start_station = start.id
INNER JOIN stations AS end
ON trips.end_station = end.id
WHERE start.municipality <> end.municipality;
'''
run_query(query)
около 300 000 из 1,5 миллионов поездок (или 20%) закончились в другом муниципалитете

Ёто показывает, что около 300 000 из 1,5 миллионов поездок (или 20%) закончились в другом муниципалитете, - еще одно доказательство того, что люди в основном используют велосипеды Hubway дл€ относительно коротких путешествий, а не более длительных поездок между городами.

≈сли вы зашли так далеко, поздравл€ю! ¬ы начали осваивать основы SQL. ћы рассмотрели р€д важных команд, а также агрегирующие и арифметические функции. ќни дадут вам прочную основу дл€ развити€ по мере того, как вы продолжите свой путь в изучении SQL.


„то дальше?

ѕосле завершени€ данного руководства по SQL можно будет выбрать базу данных, которую вы найдете интересной, и написать запросы дл€ извлечени€ информации из неЄ. ’орошим первым шагом может быть продолжение работы с базой данных Hubway, чтобы узнать, чего еще интересного можно достать из этой базы. ¬от несколько вопросов, на которые вы можете попытатьс€ ответить:

  • —колько поездок понесли дополнительные сборы (длились дольше 30 минут)?
  •  акой велосипед использовалс€ дольше всего?
  •  акие пользователи, зарегистрированные или случайные, совершали больше поездок в оба конца?
  •  акой муниципалитет имеет наибольшую среднюю продолжительность?

>