使用 chDB 分析温布尔登网球数据

原创已于 2025-08-05 11:33:33 修改 · 895 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse

于 2025-08-05 11:32:32 首次发布

软件工程专栏收录该内容

130 篇文章

订阅专栏

本文字数：6865；估计阅读时间：18 分钟

作者：Mark Needham

本文在公众号【ClickHouseInc】首发

2025 年在罗兰加洛斯（Roland Garros）举行的 Sinner 对阵 Alcaraz 的决赛，充分体现了网球比赛那种细微的胜负边界。Sinner 在第四盘曾一度距离胜利只差一分，手握三个冠军点，但最终却被 Alcaraz 逆转，如 Andy Marshall 的精彩动画中所呈现的那样[https://x.com/AndyMarshall86/status/1931993645378658533]。

当我观看 Carlos Alcaraz 在温布尔登首轮比赛时，又回想起了那段动画。我当时直觉上觉得，虽然比赛打到了五盘，但 Alcaraz 并没有真正面临输球的危险。不过，我还是想看看数据能否支持这种直觉。

起初我打算借助 AI 编码助手（AI coding assistants）来完成一些分析，但很快就遇到了障碍。Claude 和 ChatGPT 似乎都无法理解网球独特的计分方式，它们生成的代码无法直接运行，即便我提供了额外提示，依然让我越做越挫败，效率极低。

原本打算用 Python 写写看，但后来转念一想，为什么不直接用 ClickHouse 来分析呢？以下就是我最终做出的成果：

结论：Alcaraz 实际上并没有真正处于输球的危险中！

网球计分规则详解

在开始介绍我是如何实现数据分析之前，我们先快速回顾一下网球的计分规则。

一场比赛采用三盘两胜或五盘三胜的赛制，也就是必须赢下 2 盘或 3 盘才能获胜。在大满贯赛事中，女子通常打三盘两胜，男子则是五盘三胜。

每一盘先赢下 6 局的选手获胜。但如果双方都打到 5–5，就需要拿到 7 局才能赢盘。如果双方都来到 6–6，就会进入抢七局。抢七时，先拿到 7 分就能赢，除非是决胜盘，那就需要先拿 10 分，并且在这两种情况下都必须领先对手至少 2 分。

接下来说说稍微复杂一点的单局计分：

每局从 0–0 开始，由一名球员发球。比分的递增顺序是：

第一分：15
第二分：30
第三分：40

在 40 之后再赢下一分就可以赢下该局，但前提是领先 2 分。

待翻译：What Happens at 40–40 (Deuce)?

如果比分来到 40–40（称为平分，Deuce），那么需要连续赢下 2 分才能拿下该局：

赢一分 → 获得占先（Ad）
再赢一分 → 赢得该局
输一分 → 回到平分

这个过程可能会多次反复，直到有人连续拿下两分。

clickhouse-local 简要介绍

了解完计分规则后，就可以开始在 ClickHouse 中搭建分析。ClickHouse 最常见的运行方式是通过 ClickHouse Server（标准客户端/服务器架构）。

但如果只是做这样一个小项目，还要运行服务器再连接客户端就显得过于繁琐。好在 ClickHouse 提供了 clickhouse-local，这是一款独立的命令行工具，不需要单独部署服务端，就能具备 ClickHouse 的完整功能。

你可以像平时一样下载 ClickHouse：

curl https://clickhouse.com  | sh

然后直接启动 clickhouse-local：

./clickhouse -mn --path wimbledon.chdb

再通过指定 path（文件路径）参数，就能把插入的数据持久化到本地磁盘。

编写一个函数来计算赢得一局所需的分数

当 ClickHouse 配置完成后，就可以着手编写一个函数，基于当前比分计算一名选手距离赢下该局还差多少分。

这个函数的最终版本如下：

CREATE OR REPLACE FUNCTION pointsToWinGame AS (p1Score, p2Score) -> 
  multiIf(
    p1Score = '40' AND p2Score = 'AD', 3,
    p1Score = '40' AND p2Score = '40', 2,
    p1Score = '40' AND (p2Score = '0' OR p2Score = '15' OR p2Score = '30'), 1,
    p1Score = '30' AND (p2Score = '0' OR p2Score = '15' OR p2Score = '30'), 2,
    p1Score = '30' AND p2Score = '40', 3,
    p1Score = '15' AND (p2Score = '0' OR p2Score = '15' OR p2Score = '30'), 3,
    p1Score = '15' AND p2Score = '40', 4,
    p1Score = '0' AND (p2Score = '0' OR p2Score = '15' OR p2Score = '30'), 4,
    p1Score = '0' AND p2Score = '40', 5,
    p1Score = 'AD', 1,
    0
    );

它会接收双方当前的单局得分，返回第一位选手离赢局还需要再拿下多少分。

举个例子，如果比分是 15–40，那么他需要连赢 4 分——先把比分追平到 40–40，然后再连下两分拿下该局。

这个函数可以像普通内置函数一样直接调用。我最初用不同的比分组合手动测试它：

SELECT
    p1 as player1_score,
    p2 as player2_score,
    pointsToWinGame(p1, p2) as points_to_win
FROM VALUES(
    'p1 String, p2 String',
    ('0', '0'), ('0', '15'), ('0', '30'),
    ('15', '30'),
    ('30', '15'),
    ('30', '40'),
    ('40', '30'),
    ('40', '40'),
    ('AD', '40'),
    ('0', '40'), ('15', '40'), ('30', '40'),
    ('40', 'AD')
);

对于这个简单函数来说问题不大，但当我后面编写更复杂的函数时，因为不同的计分情况，往往会不断出现 bug，需要调试修复。

所以我决定编写一些可以自动运行的测试用例，来保证函数的正确性。

在 chDB 中测试自定义函数

有一种方式是切换到 ClickHouse Server，并通过 ClickHouse 的客户端库去执行函数进行测试。

另一种方案是使用 chDB，这是一款由 ClickHouse 驱动、支持 Python、Go、Rust、NodeJS、Bun 等语言的内嵌式 SQL OLAP 引擎。

chDB 的一个亮点在于，它可以直接与 clickhouse-local 创建的数据库配合使用。配置整体也比较方便。

考虑到可测试的比分组合并不算太多，我就选择写一个完整的覆盖测试。我对 Python 的高级测试框架并不是特别熟悉，所以这里依旧选择了经典的 pytest。

下面是一个我编写的参数化测试，用来验证 pointsToWinGame 函数是否按照预期工作：

from chdb import session as chs
import pytest

sess = chs.Session("wimbledon.chdb")

@pytest.mark.parametrize("p1,p2,expected", [
  ("'0'", "'40'", 5),
  ("'0'", "'0'", 4),
  ("'0'", "'15'", 4),
  ("'0'", "'30'", 4),
  ("'15'", "'40'", 4),
  ("'15'", "'15'", 3),  
  ("'15'", "'30'", 3),
  ("'30'", "'40'", 3),
  ("'40'", "'AD'", 3),
  ("'30'", "'30'", 2),
  ("'40'", "'40'", 2),
  ("'40'", "'30'", 1),
  ("'40'", "'15'", 1),
  ("'40'", "'0'", 1),
  ("'AD'", "'40'", 1),
  
])
def test_points_to_win_normal_game(p1, p2, expected):
    result = sess.query(f"""
    SELECT pointsToWinGame({p1}, {p2}) as points
    """, "DataFrame")

    assert result["points"].values[0] == expected

在脚本的顶部，我初始化了 chDB 数据库，指向 wimbledon.chdb 目录，其中已经加载好所有的自定义函数。

再往下看，测试会接收三个参数：

p1：第一名选手当前的单局得分
p2：另一名选手当前的单局得分
expected：第一名选手距离赢局还需要的分数

随后就可以通过 uv（一个现代 Python 包管理器）来运行这些测试：

uv run --with chdb pytest test_game.py

test_game.py .......................                                                                                                                                                                                                                                                   [100%]

===================================================================================================================================== 23 passed in 0.97s =====================================================================================================================================

我反复使用这样的编写函数加测试的工作流，来判断每盘以及剩余盘数中赢得比赛需要多少分。

除此之外还包含其他一些函数，但这里就不一一展开介绍了——你可以在 wimbledon-chdb 的 GitHub 仓库中查阅它们的定义和相应的测试代码[https://github.com/mneedham/wimbledon-chdb]。

如果需要查看这些函数的完整列表，我们可以写如下查询：

SELECT name
FROM system.functions
WHERE origin = 'SQLUserDefined';

┌─name─────────────────────┐
│ pointsToWinTiebreak      │
│ pointsToWinMatch         │
│ pointsToWinFinalSet      │
│ pointsToWinGame          │
│ pointsToWinSet           │
│ pointsToWinOtherSetsBO3  │
│ pointsToWinOtherSetsBO5  │
│ pointsToWinMatchTiebreak │
└──────────────────────────┘

在 ClickHouse 中存储网球数据

温布尔登的官方网站提供了逐分数据（point-by-point），非常适合拿来探索比赛走势，并分析 Alcaraz 在首轮比赛中是否真的接近被淘汰。

我们首先会创建一个 matches 表，用来存储一些比赛的元数据信息：

CREATE TABLE matches
(
    p1Name String,
    p2Name String,
    match String,
    event String
)
ENGINE = MergeTree
ORDER BY match;

其中 event 列标识这是一场男子比赛还是女子比赛，以便确定使用三盘两胜还是五盘三胜的计分规则。

SELECT * FROM matches LIMIT 3;

┌─p1Name─────────┬─p2Name───────────┬─match─┬─event─┐
│ Jannik Sinner  │ Luca Nardi       │ 1101  │ Men   │
│ Pedro Martinez │ George Loffhagen │ 1103  │ Men   │
│ Mariano Navone │ Denis Shapovalov │ 1104  │ Men   │
└────────────────┴──────────────────┴───────┴───────┘

接下来，points 表会保存所有比赛的逐分数据。

CREATE TABLE points
(
    MatchWinner String,
    SetWinner String,
    GameWinner String,
    p1 Tuple(setsWon UInt8, gamesWon UInt8, score String),
    p2 Tuple(setsWon UInt8, gamesWon UInt8, score String),
    ElapsedTime String,
    PointNumber UInt16,
    match String
)
ORDER BY match;

每一行都包含足够的信息，来判断某位选手距离胜利还有多远——不需要结合其他行就可以得出结论。下面就是 Alcaraz 的比赛关键分信息：

SELECT * 
FROM points 
WHERE match = '1164' AND MatchWinner <> '0
FORMAT Vertical;

Row 1:
──────
MatchWinner: 2
SetWinner:   2
GameWinner:  2
p1:          (2,1,'0')
p2:          (3,6,'0')
ElapsedTime: 4:36:56
PointNumber: 357
match:       1164

计算胜利所需分数

在把数据加载进来之后，我们需要编写一个查询，用来计算一名选手距离胜利还需要再拿下多少分。下面就是 Alcaraz 首场比赛中使用的查询：

WITH
  pointsToWinMatch(
    matches.event = 'Men', MatchWinner, GameWinner, SetWinner, '1', p1.setsWon, p2.setsWon, p1.gamesWon, p2.gamesWon, p1.score, p2.score
  ) AS p1PointsToWin,
  pointsToWinMatch(
    matches.event = 'Men', MatchWinner, GameWinner, SetWinner, '2', p2.setsWon, p1.setsWon, p2.gamesWon, p1.gamesWon, p2.score, p1.score
  ) AS p2PointsToWin
select PointNumber, p1Name, p1PointsToWin AS p1, p2PointsToWin AS p2, p2Name
FROM points
JOIN matches ON matches.match = points.match
WHERE match = '1164'
ORDER BY PointNumber;

查询结果的最后五行如下：

┌─PointNumber─┬─p1Name────────┬─p1─┬─p2─┬─p2Name─────────┐
│         353 │ Fabio Fognini │ 24 │  2 │ Carlos Alcaraz │
│         354 │ Fabio Fognini │ 23 │  2 │ Carlos Alcaraz │
│         355 │ Fabio Fognini │ 22 │  2 │ Carlos Alcaraz │
│         356 │ Fabio Fognini │ 23 │  1 │ Carlos Alcaraz │
│         357 │ Fabio Fognini │ 72 │  0 │ Carlos Alcaraz │
└─────────────┴───────────────┴────┴────┴────────────────┘

可以看到 Alcaraz 离胜利越来越近，直到最后不再需要任何分数，而 Fognini 则几乎需要从头打过整场比赛才能赢。

使用 Streamlit 和 plot.ly 可视化胜利距离

最后，我想把这些分析打包成一个小型可视化应用，方便去探索不同比赛。我使用了 Streamlit 和 plot.ly 来实现这个需求。完整的代码都在仓库中，并且大部分由 AI（ChatGPT）生成，我做了一些细节调整。

你可以使用 uv（Python 包管理工具）在本地运行：

uv run --with chdb --with plotly --with streamlit \
streamlit run app.py  --server.headless True

下面这段动画可以展示该应用的效果：

你也可以访问 wimbledon.streamlit.app/，在浏览器中亲自体验一下。

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com