Задача SQL + Python

На сайте используются файлы cookie, оставаясь на сайте вы принимаете политику конфиденциальности
Анализ данных — это не только финансы, маркетинг и продажи. SQL и Python позволяют исследовать самые неожиданные темы, например, атаки акул. Эти инструменты помогают находить закономерности, выявлять тренды и строить прогнозы, даже если данные на первый взгляд кажутся хаотичными.

Сегодня мы попробуем себя в роли аналитиков-исследователей:
  • Используя SQL, разберемся, где чаще всего происходят атаки, какие виды акул опаснее и чем занимались люди в момент нападения.
  • С помощью Python построим график динамики атак, чтобы увидеть, как меняется ситуация с годами.

Хотите уметь работать со связкой инструментов SQL + Python так же хорошо, как с инструментами Microsoft Office? Приглашаем вас на курс Аналитик PRO, где мы учим как базовому, так и продвинутому Python, а еще SQL, Excel, Tableau и другим полезным навыкам и инструментам. 

Готовы проверить, какие выводы можно сделать на основе данных? Тогда начнем!
Для анализа у нас есть реальная база данных об атаках акул по всему миру. Она содержит информацию о месте, времени, обстоятельствах и последствиях каждого инцидента.

Основные столбцы в таблице global shark attacks:

  • date – дата инцидента (например, 2023-05-13)
  • year – год атаки (например, 2023)
  • type – тип атаки (например, Unprovoked – неспровоцированная)
  • country – страна, где произошла атака
  • area – регион внутри страны
  • location – конкретное место инцидента
  • activity – чем занимался человек в момент атаки (плавание, серфинг и тд)
  • name – имя пострадавшего (если известно)
  • sex – пол пострадавшего (M – мужчина, F – женщина)
  • age – возраст пострадавшего
  • fatal_y_n – была ли атака смертельной (Y – да, N – нет)
  • species – вид акулы (если установлен)

Что мы будем исследовать?

  1. В каких странах чаще всего происходят атаки?
  2. Какие виды акул наиболее опасны?
  3. Какие активности связаны с наибольшим числом нападений?
  4. В какие годы было больше всего атак?

Загрузите файл с таблицей себе на компьютер по ссылке Важно сохранить название таблицы global_shark_attacks, так как с ним связаны все запросы в задаче.
Для тренировки работы с SQL мы будем использовать бесплатный сервис SQLite Online. Работа с ним не требует регистрации или других дополнительных действий. Перейдите на сайт SQLite Online и загрузите файл данных, с которым будем работать.

Как загрузить файл для старта работы, описали в этой статье – https://changellenge-education.com/blog/sql-dlya-starta-raboty. Кстати, это еще одна задача по SQL, которую вы сможете решить по подробным инструкциям.
Перейдем к первому SQL-запросу?

Первый шаг в анализе — определить географию атак. Мы хотим узнать, в каких странах нападения акул происходят чаще всего.

Для этого используем SQL-запрос:

SELECT country, COUNT(*) AS attack_count

FROM global_shark_attacks

GROUP BY country

ORDER BY attack_count DESC;

Как работает этот запрос?

1. SELECT country, COUNT(*) AS attack_count
  • country — выбираем столбец со странами.
  • COUNT(*) — считаем количество строк в каждой группе (то есть количество атак в каждой стране).
  • AS attack_count — даем столбцу с количеством атак более понятное имя.

2. FROM shark_attacks
  • Указываем таблицу, в которой хранятся данные.

3. GROUP BY country
  • Группируем все строки по значению в столбце country.

Почему мы используем GROUP BY? Без него SQL просто вернул бы длинный список всех стран (с повторами), а GROUP BY как бы "собирает" одинаковые страны в группы, чтобы можно было посчитать, сколько раз каждая встречается в данных.

4. ORDER BY attack_count DESC
  • Сортируем страны по количеству атак (attack_count).
  • DESC означает сортировку по убыванию (от большего к меньшему).

Что мы ожидаем увидеть?
На первых позициях, скорее всего, будут страны с развитой пляжной культурой и серфингом:

  • США (особенно Флорида и Калифорния).
  • Австралия (пляжи, серфинг).
  • Южная Африка (высокая активность белых акул)

Запрос 1. В каких странах зафиксировано наибольшее количество атак?

Запрос 2. Какие акулы чаще всего участвуют в атаках

Теперь посмотрим, какие акулы чаще всего участвуют в атаках. Это поможет понять, какие виды представляют наибольшую опасность для человека.

Используем следующий SQL-запрос:

SELECT species, COUNT(*) AS attack_count

FROM global_shark_attacks

WHERE species IS NOT NULL

GROUP BY species

ORDER BY attack_count DESC

LIMIT 10;

Как работает этот запрос?

1. SELECT species, COUNT(*) AS attack_count
  • species — выбираем столбец с видами акул.
  • COUNT(*) — считаем, сколько раз каждая акула фигурирует в базе.
  • AS attack_count — называем новый столбец, чтобы результат был читаемым.
2. FROM global_shark_attacks
  • Указываем таблицу с данными.

3. WHERE species IS NOT NULL
  • Исключаем строки, где вид акулы не указан (NULL).
Это условие важно: если не исключить строки с пустыми значениями (NULL), в топ-10 могут попасть случаи, где вид акулы не указан вовсе. Это исказит результаты.

4. GROUP BY species
  • Группируем данные по виду акулы, чтобы посчитать количество атак для каждого из них.

5. ORDER BY attack_count DESC
  • Сортируем результат по количеству атак (по убыванию).

6. LIMIT 10
  • Оставляем только 10 самых «агрессивных» видов акул.

Что мы ожидаем увидеть?
Вероятно, лидерами станут:

  • Белая акула (White shark) 🦈 – главная героиня «Челюстей».
  • Тигровая акула (Tiger shark) – известна своим всеядным аппетитом.
  • Бычья акула (Bull shark) – может жить как в морской, так и в пресной воде.
Как мы видим, для большинства случаев нападений не был определен вид акулы, поэтому в таблице такие случаи занимают первую позицию. Где-то вместо вида акулы указан её размер.
Не будем углубляться в формулы, но если вам интересна математика тестирования – приходите на наш курс Аналитик PRO, где мы учим не только работать с данными, но и правильно их интерпретировать!

Запрос 3. Какие виды активности связаны с наибольшим числом атак?

Люди не просто встречаются с акулами — они взаимодействуют с морем: плавают, занимаются серфингом, рыбалкой. Давайте выясним, чем чаще всего занимались люди в момент атаки.

Используем следующий SQL-запрос:

SELECT activity, COUNT(*) AS attack_count
FROM global_shark_attacks
WHERE activity IS NOT NULL
GROUP BY activity
ORDER BY attack_count DESC
LIMIT 10;

Как работает этот запрос?

1. SELECT activity, COUNT(*) AS attack_count
  • activity — выбираем колонку с занятием человека в момент атаки.
  • COUNT(*) — подсчитываем количество атак для каждого вида активности.
  • AS attack_count — даем имя новому столбцу.

2. FROM global_shark_attacks
  • Берем данные из таблицы global_shark_attacks.

3. WHERE activity IS NOT NULL
  • Исключаем строки, где нет информации о виде активности.

4. GROUP BY activity
  • Группируем данные по типу активности.

5. ORDER BY attack_count DESC
  • Сортируем по количеству атак (по убыванию).

6. LIMIT 10
  • Оставляем только топ-10 самых рискованных активностей.

Чего ожидаем?
Наиболее вероятные лидеры:

  • Серфинг (Surfing) – акулы могут путать доску с добычей.
  • Плавание (Swimming) – люди часто оказываются в воде без защиты.
  • Подводная охота (Spearfishing) – кровь добычи привлекает акул.

Запрос 4. В каком году было зафиксировано наибольшее количество атак?

Теперь посмотрим, как изменялось количество атак акул с течением времени. Это поможет выявить тренды: растет ли число нападений или остается стабильным?
Используем SQL-запрос:

SELECT year, COUNT(*) AS attack_count
FROM global_shark_attacks
WHERE year IS NOT NULL
GROUP BY year
ORDER BY attack_count DESC
LIMIT 1;

Как работает этот запрос?

1. SELECT year, COUNT(*) AS attack_count
  • year — выбираем колонку с годом происшествия.
  • COUNT(*) — считаем количество атак в каждом году.
  • AS attack_count — даем имя новому столбцу.

2. FROM global_shark_attacks
  • Берем данные из таблицы global_shark_attacks.

3. WHERE year IS NOT NULL
  • Исключаем строки без указания года.

4. GROUP BY year
  • Группируем атаки по годам.

5. ORDER BY attack_count DESC
  • Сортируем по количеству атак (по убыванию).

6. LIMIT 1
  • Выбираем только год с наибольшим числом атак.

Чего ожидаем?
Вероятно, больше всего атак зафиксировано в последние десятилетия. Возможные причины:

  • Большее число туристов в океанах.
  • Развитие серфинга и дайвинга.
  • Улучшение сбора данных (раньше атаки могли просто не фиксироваться).
Теперь самое интересное: визуализируем тренд атак с помощью Python! Готовы перейти ко второй части?

SQL помог нам обработать данные, но намного легче увидеть тренды на графике. Давайте построим динамику атак акул по годам с помощью Python и библиотеки matplotlib.

Для работы с данными мы будем использовать Python и библиотеку Pandas. Вам понадобится среда разработки Google Colab. Google Colab (Google Colaboratory) — это бесплатный облачный сервис от Google, который предоставляет среду для выполнения Python-кода прямо в браузере.

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

📊 Шаг 1. Импортируем библиотеки и загружаем данные

Сначала импортируем нужные инструменты:

import pandas as pd

import matplotlib.pyplot as plt

Теперь загрузим данные:

# Загружаем CSV-файл

file_path = "global_shark_attacks.csv"

df = pd.read_csv(file_path)

# Выводим первые строки, чтобы проверить, как выглядят данные
df.head()

Что здесь происходит?
  • pandas позволяет работать с таблицами как в SQL.
  • matplotlib.pyplot используется для построения графиков.
  • df.head() выводит первые 5 строк, чтобы мы убедились, что данные загружены правильно.
Также стоит привести названия столбцов к единому формату — например, сделать их все строчными:

df.columns = df.columns.str.lower()

Так вы избежите ошибок, связанных с тем, что в одном месте написано Country, а в другом country.

Шаг 2. Обрабатываем данные

Чтобы построить график, нужно сгруппировать данные по годам:

# Преобразуем колонку 'year' в числовой формат

df['year'] = pd.to_numeric(df['year'], errors='coerce')

# Группируем атаки по годам

yearly_attacks = df.groupby('year').size()

# Убираем некорректные года (например, 0 или очень маленькие значения)

yearly_attacks = yearly_attacks[yearly_attacks.index > 1900]

Объяснение кода:
  • pd.to_numeric(df['year'], errors='coerce') — преобразует колонку year в число, удаляя ошибки.

Этот приём помогает очистить колонку: если в ячейке вдруг окажется что-то, что не похоже на год (например, ошибка в записи), такое значение превратится в NaN и не повлияет на итоговый график.

  • .groupby('year').size() — считает, сколько атак было в каждом году.

Метод .groupby() в pandas работает похожим образом на GROUP BY в SQL. А .size() считает, сколько строк попало в каждую группу — в нашем случае, сколько атак было в каждом году.

  • yearly_attacks[yearly_attacks.index > 1900] — фильтруем странные значения (например, если в данных есть нулевые года).

Шаг 3. Строим график

Теперь визуализируем изменения числа атак по годам:

plt.figure(figsize=(10,5))  # Размер графика

plt.plot(yearly_attacks.index, yearly_attacks.values, marker='o', linestyle='-')

plt.xlabel("Год")  # Подпись оси X

plt.ylabel("Количество атак")  # Подпись оси Y

plt.title("Динамика атак акул по годам")  # Заголовок графика

plt.grid(True)  # Включаем сетку

plt.show()  # Показываем график

Что здесь происходит?

  • plt.figure(figsize=(10,5)) задаёт размер графика — ширину 10 и высоту 5 условных единиц. Это помогает сделать изображение читаемым.

  • plt.plot(...) рисует линию: по оси X идут года (yearly_attacks.index), по оси Y — количество атак (yearly_attacks.values).

  • marker='o' добавляет кружочки в каждой точке, а linestyle='-' соединяет их линией.

  • plt.xlabel(...) и plt.ylabel(...) добавляют подписи к осям, чтобы было понятно, что именно мы измеряем.

  • plt.title(...) — заголовок графика.

  • plt.grid(True) включает сетку, чтобы проще было ориентироваться по значениям.

  • plt.show() выводит график на экран.

Что мы увидим?
  • Скорее всего, рост числа атак в последние десятилетия.
  • Возможные пики в 1990-х и 2000-х годах, когда серфинг и пляжный отдых стали популярнее.
  • Иногда резкие скачки могут быть связаны с активностью СМИ (чем больше новостей об акулах, тем больше данных фиксируется).
Мы использовали SQL, чтобы исследовать данные, а Python — чтобы увидеть тренды на графике. Такой подход можно применять в любой области, от маркетинга до экологии.

Попробуйте изменить SQL-запросы и посмотреть другие закономерности. Например:

  • Как часто происходят атаки ночью vs днем?
  • Какие штаты в США наиболее опасны?
  • Изменился ли средний возраст жертв с годами?

SQL и Python дают возможность исследовать любые данные — от бизнеса до экологии. Главное — задавать правильные вопросы!

Статью подготовила:

Завадская Юлия

Changellenge >> Education
Методист