JupySQL — это библиотека Python, которая позволяет запускать SQL-код в Jupyter notebooks и в оболочке IPython. В этом руководстве мы научимся запрашивать данные с использованием chDB и JupySQL.
Мы будем использовать один из наборов данных Джеффа Сакмана tennis_atp, который содержит метаданные о игроках и их позициях в рейтингах с течением времени. Давайте начнем с загрузки файлов с рейтингами:
from urllib.request import urlretrieve
files = ['00s', '10s', '20s', '70s', '80s', '90s', 'current']
base = "https://raw.githubusercontent.com/JeffSackmann/tennis_atp/master"
for file in files:
_ = urlretrieve(
f"{base}/atp_rankings_{file}.csv",
f"atp_rankings_{file}.csv",
)
Теперь мы собираемся сохранить данные из этих CSV файлов в таблицу. По умолчанию база данных не сохраняет данные на диске, поэтому нам нужно сначала создать другую базу данных:
%sql CREATE DATABASE atp
Теперь мы создадим таблицу с названием rankings, структура которой будет заимствована из структуры данных в CSV файлах:
%%sql
CREATE TABLE atp.rankings
ENGINE=MergeTree
ORDER BY ranking_date AS
SELECT * REPLACE (
toDate(parseDateTime32BestEffort(toString(ranking_date))) AS ranking_date
)
FROM file('atp_rankings*.csv')
SETTINGS schema_inference_make_columns_nullable=0
Прием данных завершен, теперь пора к интересной части - запросам к данным!
Теннисные игроки получают очки на основе того, как хорошо они выступают на турнирах. Очки для каждого игрока за 52-недельный период. Мы напишем запрос, который найдет максимальные очки, накопленные каждым игроком, а также их рейтинг на тот момент:
%%sql
SELECT name_first, name_last,
max(points) as maxPoints,
argMax(rank, points) as rank,
argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
LIMIT 10
Мы можем сохранять запросы, используя параметр --save на той же строке, что и магия %%sql. Параметр --no-execute означает, что выполнение запроса будет пропущено.
%%sql --save best_points --no-execute
SELECT name_first, name_last,
max(points) as maxPoints,
argMax(rank, points) as rank,
argMax(ranking_date, points) as date
FROM atp.players
JOIN atp.rankings ON rankings.player = players.player_id
GROUP BY ALL
ORDER BY maxPoints DESC
Когда мы запускаем сохраненный запрос, он будет преобразован в Общие Табличные Выражения (CTE) перед выполнением. В следующем запросе мы вычисляем максимальные очки, достигнутые игроками, когда они были на первом месте:
Мы также можем использовать параметры в наших запросах. Параметры — это просто обычные переменные:
rank = 10
Затем мы можем использовать синтаксис {{variable}} в нашем запросе. Следующий запрос находит игроков, у которых было меньше всего дней между первым попаданием в рейтинг топ-10 и последним попаданием в рейтинг топ-10:
%%sql
SELECT name_first, name_last,
MIN(ranking_date) AS earliest_date,
MAX(ranking_date) AS most_recent_date,
most_recent_date - earliest_date AS days,
1 + (days/7) AS weeks
FROM atp.rankings
JOIN atp.players ON players.player_id = rankings.player
WHERE rank <= {{rank}}
GROUP BY ALL
ORDER BY days
LIMIT 10
JupySQL также имеет ограниченные функции построения графиков. Мы можем создавать коробчатые диаграммы или гистограммы.
Мы собираемся создать гистограмму, но для начала давайте напишем (и сохраним) запрос, который вычисляет рейтинги в топ-100, которые каждый игрок достиг. Мы сможем использовать это для создания гистограммы, которая подсчитывает, сколько игроков достигли каждого рейтинга:
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
Затем мы можем создать гистограмму, выполнив следующее:
