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
Затем создадим таблицу players на основе содержимого CSV-файла.
Мы также приведём поле dob к типу Date32.
В ClickHouse тип Date поддерживает только даты, начиная с 1970 года. Поскольку столбец dob содержит даты до 1970 года, вместо него будем использовать тип Date32.
%%sql
CREATE TABLE atp.players
Engine=MergeTree
ORDER BY player_id AS
SELECT * REPLACE (
makeDate32(
toInt32OrNull(substring(toString(dob), 1, 4)),
toInt32OrNull(substring(toString(dob), 5, 2)),
toInt32OrNull(substring(toString(dob), 7, 2))
)::Nullable(Date32) AS dob
)
FROM file('atp_players.csv')
SETTINGS schema_inference_make_columns_nullable=0
После завершения выполнения мы сможем просмотреть принятые данные:
%sql SELECT * FROM atp.players LIMIT 10
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| player_id | name_first | name_last | hand | dob | ioc | height | wikidata_id |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
| 100001 | Gardnar | Mulloy | R | 1913-11-22 | USA | 185 | Q54544 |
| 100002 | Pancho | Segura | R | 1921-06-20 | ECU | 168 | Q54581 |
| 100003 | Frank | Sedgman | R | 1927-10-02 | AUS | 180 | Q962049 |
| 100004 | Giuseppe | Merlo | R | 1927-10-11 | ITA | 0 | Q1258752 |
| 100005 | Richard | Gonzalez | R | 1928-05-09 | USA | 188 | Q53554 |
| 100006 | Grant | Golden | R | 1929-08-21 | USA | 175 | Q3115390 |
| 100007 | Abe | Segal | L | 1930-10-23 | RSA | 0 | Q1258527 |
| 100008 | Kurt | Nielsen | R | 1930-11-19 | DEN | 0 | Q552261 |
| 100009 | Istvan | Gulyas | R | 1931-10-14 | HUN | 0 | Q51066 |
| 100010 | Luis | Ayala | R | 1932-09-18 | CHI | 170 | Q1275397 |
+-----------+------------+-----------+------+------------+-----+--------+-------------+
Ингестия данных завершена, теперь самое интересное — выполнение запросов к данным!
Теннисисты получают очки в зависимости от того, насколько хорошо они выступают на турнирах, в которых участвуют.
Очки для каждого игрока учитываются за скользящий период в 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).
В следующем запросе мы вычисляем максимальное количество очков, набранное игроками, когда они занимали 1-е место:
Мы также можем использовать параметры в наших запросах.
Параметры — это обычные переменные:
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 также имеет ограниченную функциональность для построения графиков.
Мы можем создавать ящиковые диаграммы (box plot) или гистограммы.
Мы построим гистограмму, но сначала давайте напишем (и сохраним) запрос, который вычисляет, каких позиций в пределах топ-100 достиг каждый игрок.
Затем мы сможем использовать его, чтобы построить гистограмму, показывающую, сколько игроков достигли каждой позиции:
%%sql --save players_per_rank --no-execute
select distinct player, rank
FROM atp.rankings
WHERE rank <= 100
Затем мы можем создать гистограмму, выполнив следующую команду:
