JupySQL — это библиотека Python, которая позволяет выполнять SQL в Jupyter блокнотах и в оболочке IPython. В этом руководстве мы научимся запрашивать данные с помощью chDB и JupySQL.
Мы будем использовать один из наборов данных Jeff Sackmann's 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:
%%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
Затем мы можем создать гистограмму, выполнив следующее:
