毫秒级体育历史盘口导出与滚球水位序列提取引擎

# 读取极速盘研终端导出的英超滚球水位时间序列 (CSV 或 Parquet)
import pandas as pd
import numpy as np

df = pd.read_csv(
    "D:/DesktopOdds_Export/EPL_2023_2024_InPlay_Ticks.csv",
    parse_dates=["tick_time_utc"]
)

# 1. 过滤有效可成交盘面（剔除VAR与危险球封盘瞬间，避免未来函数）
valid_ticks = df[
    (df["market_status"] == "OPEN") &
    (df["match_minute"].between(60, 75)) &
    (df["score_diff"] == 0)
].copy()

# 2. 筛选平博(Pinnacle)与皇冠(Crown)在平局60-75分钟主胜水位急跌超过0.12的信号
valid_ticks["water_drop_3m"] = valid_ticks.groupby(["match_id", "bookmaker"])["home_water"].diff(periods=6)
signals = valid_ticks[valid_ticks["water_drop_3m"] <= -0.12]

# 3. 计算亚盘结算净收益率 (ROI) 与最大回撤
signals["pnl"] = np.where(
    signals["final_ah_result"] == "WIN_FULL", signals["home_water"] - 1.0,
    np.where(signals["final_ah_result"] == "WIN_HALF", (signals["home_water"] - 1.0) * 0.5, -1.0)
)
print(f"触发样本数: {len(signals)} | 策略净ROI: {signals['pnl'].mean()*100:.2f}%")

-- 使用 DuckDB 直接查询极速盘研导出的本地 Parquet 历史盘口文件
SELECT 
    league_name,
    bookmaker,
    COUNT(DISTINCT match_id) AS sample_matches,
    ROUND(AVG(closing_home_water - opening_home_water), 4) AS avg_water_shift,
    ROUND(SUM(CASE WHEN final_score_home > final_score_away THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS home_win_rate_pct
FROM read_parquet('D:/DesktopOdds_Export/Top5_Leagues_2018_2024.parquet')
WHERE opening_ah_line = -0.5 
  AND closing_ah_line = -0.75   -- 临场升盘：半球升半一
  AND closing_kelly_home <= 0.93 -- 凯利指数低于赔付率安全阈值
GROUP BY league_name, bookmaker
ORDER BY sample_matches DESC;

# 使用 R data.table 高速加载百万行滚球大小球水位序列
library(data.table)

dt <- fread("D:/DesktopOdds_Export/OverUnder_InPlay_Series.csv", encoding = "UTF-8")

# 筛选上半场0-0且第35分钟大小球盘口仍维持在 1.5/2 球低水的比赛
ht_signals <- dt[
  match_minute == 35 & 
  current_score == "0-0" & 
  ou_line >= 1.75 & 
  over_water <= 1.85 & 
  inplay_shots_on_target_total >= 5
]

ht_signals[, .(
  total_matches = .N,
  second_half_goals_avg = mean(ft_total_goals),
  over_hit_rate = mean(ft_total_goals >= 2)
), by = .(league_name)]