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

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

作者：Mark Needham

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

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

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

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

curl https://clickhouse.com  | sh

./clickhouse -mn --path wimbledon.chdb

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
    );

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')
);

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

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

uv run --with chdb pytest test_game.py

test_game.py .......................                                                                                                                                                                                                                                                   [100%]
===================================================================================================================================== 23 passed in 0.97s =====================================================================================================================================

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

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

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

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   │
└────────────────┴──────────────────┴───────┴───────┘

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;

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

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 │
└─────────────┴───────────────┴────┴────┴────────────────┘

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