SQL实现NBA赛事套利计算器数据处理方案问询

本人是SQL新手，已通过Python脚本将NBA赛事赔率数据导入至NBAgameoddsML表。表字段说明：

• run_date/run_time：数据加载时间
• gameid：赛事唯一标识
• home_team/away_team：主客队
• sportsbook：博彩商
• home_ml/away_ml：赔率
• home_pct/away_pct：获胜隐含概率

需求：创建CombinedPercentages2表，按gameid分组，将不同博彩商的主队赔率/概率与客队赔率/概率两两组合，计算：

• total_pct：主队概率+客队概率
• arbitrage_pct：1 - total_pct（套利空间，当total_pct < 1时存在套利机会）

尝试的SQL语句生成数据过多且不准确，询问是否可通过SQL实现该需求，若不行则用Python如何实现。

原尝试的表创建SQL和插入SQL如下：

CREATE TABLE CombinedPercentages2 (
    run_date datetime,
    run_time time,
    gameid varchar(40),
    home_team varchar(30),
    away_team varchar(30),
    sportsbook_Home varchar(30),
    sportsbook_Away varchar(30),
    home_ml int,
    away_ml int,
    home_pct float,
    away_pct float,
    total_pct float,
    arbitrage_pct float
);

INSERT INTO CombinedPercentages2 (run_date, run_time, gameid, home_team, away_team, sportsbook_Home, sportsbook_Away, home_ml, away_ml, home_pct, away_pct, total_pct,arbitrage_pct)
SELECT
    ng.run_date,
    ng.run_time,
    ng.gameid,
    ng.home_team,
    ng.away_team,
    ng1.sportsbook AS sportsbook_Home,
    ng2.sportsbook AS sportsbook_Away,
    ng1.home_ml,
    ng2.away_ml,
    ng1.home_pct,
    ng2.away_pct,
    (ng1.home_pct + ng2.away_pct) AS total_pct,
    1 - (ng1.home_pct + ng2.away_pct) AS arbitrage_pct
FROM
    NBAgameoddsML ng
INNER JOIN
    NBAgameoddsML ng1 ON ng.gameid = ng1.gameid AND ng.sportsbook != ng1.sportsbook
INNER JOIN
    NBAgameoddsML ng2 ON ng.gameid = ng2.gameid AND ng.sportsbook != ng2.sportsbook
WHERE
    ng1.home_team = ng.home_team
    AND ng2.away_team = ng.away_team
    AND ng.run_time = '20:20:00.0000000'
GROUP BY
    ng.run_date,
    ng.run_time,
    ng.gameid,
    ng.home_team,
    ng.away_team,
    ng1.sportsbook,
    ng2.sportsbook,
    ng1.home_ml,
    ng2.away_ml,
    ng1.home_pct,
    ng2.away_pct;

SQL实现方案 #

原SQL问题在于多了不必要的主表ng关联，导致重复生成数据。正确做法是直接将同一场赛事、同一加载时间的主队数据（来自任意博彩商）和客队数据（来自任意其他博彩商）做关联，生成所有合法组合。

修正后的插入SQL：

INSERT INTO CombinedPercentages2 (
    run_date, run_time, gameid, home_team, away_team,
    sportsbook_Home, sportsbook_Away, home_ml, away_ml,
    home_pct, away_pct, total_pct, arbitrage_pct
)
SELECT
    home.run_date,
    home.run_time,
    home.gameid,
    home.home_team,
    home.away_team,
    home.sportsbook AS sportsbook_Home,
    away.sportsbook AS sportsbook_Away,
    home.home_ml,
    away.away_ml,
    home.home_pct,
    away.away_pct,
    home.home_pct + away.away_pct AS total_pct,
    1 - (home.home_pct + away.away_pct) AS arbitrage_pct
FROM NBAgameoddsML home
JOIN NBAgameoddsML away
    ON home.gameid = away.gameid
    AND home.run_date = away.run_date
    AND home.run_time = away.run_time
    AND home.sportsbook != away.sportsbook  -- 限制为不同博彩商组合
WHERE home.run_time = '20:20:00.0000000'
-- 可选：仅保留存在套利机会的记录
-- AND (home.home_pct + away.away_pct) < 1;

说明 #

• 用home和away别名关联同一张表，确保是同一场赛事、同一加载时间的记录
• 通过home.sportsbook != away.sportsbook保证组合来自不同博彩商
• 无需额外分组，因为需求就是生成所有合法两两组合的结果

Python实现方案 #

若用Python处理，推荐使用pandas库，步骤如下：

1. 读取数据库数据 #

import pandas as pd
import sqlite3  # 若使用其他数据库，替换为对应驱动（如psycopg2 for PostgreSQL）

# 连接数据库
conn = sqlite3.connect('your_database.db')
# 读取指定时间的赛事赔率数据
df = pd.read_sql("SELECT * FROM NBAgameoddsML WHERE run_time = '20:20:00.0000000'", conn)
conn.close()

2. 生成两两组合并计算指标 #

combined_records = []

# 按赛事分组处理
for gameid, group in df.groupby('gameid'):
    # 遍历所有主队数据，与其他博彩商的客队数据组合
    for _, home_row in group.iterrows():
        for _, away_row in group.iterrows():
            if home_row['sportsbook'] != away_row['sportsbook']:
                total_pct = home_row['home_pct'] + away_row['away_pct']
                combined = {
                    'run_date': home_row['run_date'],
                    'run_time': home_row['run_time'],
                    'gameid': gameid,
                    'home_team': home_row['home_team'],
                    'away_team': home_row['away_team'],
                    'sportsbook_Home': home_row['sportsbook'],
                    'sportsbook_Away': away_row['sportsbook'],
                    'home_ml': home_row['home_ml'],
                    'away_ml': away_row['away_ml'],
                    'home_pct': home_row['home_pct'],
                    'away_pct': away_row['away_pct'],
                    'total_pct': total_pct,
                    'arbitrage_pct': 1 - total_pct
                }
                combined_records.append(combined)

# 转换为DataFrame
combined_df = pd.DataFrame(combined_records)
# 可选：仅保留套利机会记录
combined_df = combined_df[combined_df['total_pct'] < 1]

3. 将结果写入数据库 #

conn = sqlite3.connect('your_database.db')
# 写入CombinedPercentages2表，若表已存在则追加数据
combined_df.to_sql('CombinedPercentages2', conn, if_exists='append', index=False)
conn.close()

内容的提问来源于stack exchange，提问作者downingg