1225 lines
53 KiB
Python
1225 lines
53 KiB
Python
"""
|
||
Odds-Band Historical Performance Analyzer (V28)
|
||
================================================
|
||
Uses historical betting odds as a lookup key to analyze how a team
|
||
actually performed when priced at similar odds ranges.
|
||
|
||
Instead of using odds as raw model features (V25 approach), this
|
||
module treats odds as a *historical reference* — answering the
|
||
question: "When this team was priced at ~1.55, what actually happened?"
|
||
|
||
This creates powerful calibration features that reveal systematic
|
||
market inefficiencies specific to each team.
|
||
|
||
All methods are fail-safe and return sensible defaults.
|
||
"""
|
||
|
||
from __future__ import annotations
|
||
|
||
from typing import Any, Dict, Optional, Tuple
|
||
|
||
from psycopg2.extras import RealDictCursor
|
||
|
||
|
||
# ─── Band Range Configuration ──────────────────────────────────────
|
||
def get_band_range(odds: float) -> Tuple[float, float]:
|
||
"""
|
||
Calculate the odds band range for historical lookup.
|
||
|
||
Narrower bands for low odds (strong favorites — more data),
|
||
wider bands for high odds (underdogs — less data).
|
||
"""
|
||
if odds <= 0.0:
|
||
return (1.01, 1.50) # Invalid odds → default band
|
||
if odds <= 1.30:
|
||
margin = 0.05 # Very strong favorite → tight band
|
||
elif odds <= 2.00:
|
||
margin = 0.10 # Normal favorite
|
||
elif odds <= 3.50:
|
||
margin = 0.15 # Draw zone / slight underdog
|
||
else:
|
||
margin = 0.25 # Big underdog → wide band (less data)
|
||
return (max(1.01, odds - margin), odds + margin)
|
||
|
||
|
||
# ─── Minimum sample sizes ──────────────────────────────────────────
|
||
MIN_TEAM_SAMPLE = 8 # Min matches for team-level band stats
|
||
MIN_LEAGUE_SAMPLE = 15 # Min matches for league-level fallback
|
||
MAX_LOOKBACK = 50 # Max historical matches to consider
|
||
|
||
|
||
class OddsBandAnalyzer:
|
||
"""
|
||
Stateless service — receives a psycopg2 cursor and computes
|
||
odds-band historical features for any team + market combination.
|
||
"""
|
||
|
||
# Default features when no data is available
|
||
_DEFAULTS: Dict[str, float] = {
|
||
"band_ms_win_rate": 0.33,
|
||
"band_ms_draw_rate": 0.33,
|
||
"band_ms_loss_rate": 0.34,
|
||
"band_ms_sample": 0.0,
|
||
"band_ou25_over_rate": 0.50,
|
||
"band_ou25_sample": 0.0,
|
||
"band_ou15_over_rate": 0.65,
|
||
"band_ou15_sample": 0.0,
|
||
"band_ou35_over_rate": 0.35,
|
||
"band_ou35_sample": 0.0,
|
||
"band_btts_rate": 0.50,
|
||
"band_btts_sample": 0.0,
|
||
"band_ms_value_signal": 0.0,
|
||
"band_ou25_value_signal": 0.0,
|
||
"band_btts_value_signal": 0.0,
|
||
}
|
||
|
||
# ─── Public Interface ──────────────────────────────────────────
|
||
|
||
def compute_all(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
odds: Dict[str, float],
|
||
before_ts: int,
|
||
referee_name: Optional[str] = None,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
Compute odds-band features for both home and away teams.
|
||
|
||
Args:
|
||
cur: psycopg2 RealDictCursor
|
||
home_team_id: Home team ID
|
||
away_team_id: Away team ID
|
||
league_id: League ID for fallback queries
|
||
odds: Current match odds dict (ms_h, ms_d, ms_a, ou25_o, etc.)
|
||
before_ts: Match timestamp (ms) — only look at matches before this
|
||
referee_name: Optional referee name for card profiling
|
||
|
||
Returns:
|
||
Dict with odds-band features (home_ and away_ prefixed)
|
||
"""
|
||
result = {}
|
||
|
||
ms_home_odds = float(odds.get("ms_h", 0))
|
||
ms_away_odds = float(odds.get("ms_a", 0))
|
||
ou25_over_odds = float(odds.get("ou25_o", 0))
|
||
ou25_under_odds = float(odds.get("ou25_u", 0))
|
||
ou15_over_odds = float(odds.get("ou15_o", 0))
|
||
ou35_over_odds = float(odds.get("ou35_o", 0))
|
||
btts_yes_odds = float(odds.get("btts_y", 0))
|
||
|
||
# ── Home team band analysis ───────────────────────────────
|
||
home_ms = self._compute_ms_band(
|
||
cur, home_team_id, league_id, ms_home_odds,
|
||
is_home=True, before_ts=before_ts,
|
||
)
|
||
for key, val in home_ms.items():
|
||
result[f"home_{key}"] = val
|
||
|
||
# ── Away team band analysis ───────────────────────────────
|
||
away_ms = self._compute_ms_band(
|
||
cur, away_team_id, league_id, ms_away_odds,
|
||
is_home=False, before_ts=before_ts,
|
||
)
|
||
for key, val in away_ms.items():
|
||
result[f"away_{key}"] = val
|
||
|
||
# ── Match-level OU/BTTS band analysis ─────────────────────
|
||
ou25_band = self._compute_ou_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
ou25_over_odds, line=2.5, before_ts=before_ts,
|
||
)
|
||
for key, val in ou25_band.items():
|
||
result[f"band_ou25_{key}"] = val
|
||
|
||
ou15_band = self._compute_ou_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
ou15_over_odds, line=1.5, before_ts=before_ts,
|
||
)
|
||
for key, val in ou15_band.items():
|
||
result[f"band_ou15_{key}"] = val
|
||
|
||
ou35_band = self._compute_ou_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
ou35_over_odds, line=3.5, before_ts=before_ts,
|
||
)
|
||
for key, val in ou35_band.items():
|
||
result[f"band_ou35_{key}"] = val
|
||
|
||
btts_band = self._compute_btts_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
btts_yes_odds, before_ts=before_ts,
|
||
)
|
||
for key, val in btts_band.items():
|
||
result[f"band_btts_{key}"] = val
|
||
|
||
# ── DC (Çifte Şans) band ──────────────────────────────────
|
||
dc_1x_odds = float(odds.get("dc_1x", 0))
|
||
dc_x2_odds = float(odds.get("dc_x2", 0))
|
||
dc_12_odds = float(odds.get("dc_12", 0))
|
||
dc_band = self._compute_dc_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
dc_1x_odds, dc_x2_odds, dc_12_odds, before_ts=before_ts,
|
||
)
|
||
for key, val in dc_band.items():
|
||
result[f"band_dc_{key}"] = val
|
||
|
||
# ── HT (İlk Yarı Sonucu) band ────────────────────────────
|
||
ht_h_odds = float(odds.get("ht_h", 0))
|
||
ht_a_odds = float(odds.get("ht_a", 0))
|
||
home_ht = self._compute_ht_band(
|
||
cur, home_team_id, league_id, ht_h_odds,
|
||
is_home=True, before_ts=before_ts,
|
||
)
|
||
for key, val in home_ht.items():
|
||
result[f"home_{key}"] = val
|
||
away_ht = self._compute_ht_band(
|
||
cur, away_team_id, league_id, ht_a_odds,
|
||
is_home=False, before_ts=before_ts,
|
||
)
|
||
for key, val in away_ht.items():
|
||
result[f"away_{key}"] = val
|
||
|
||
# ── HT OU 0.5 / 1.5 bands ────────────────────────────────
|
||
ht_ou05_odds = float(odds.get("ht_ou05_o", 0))
|
||
ht_ou05_band = self._compute_ou_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
ht_ou05_odds, line=0.5, before_ts=before_ts,
|
||
half_time=True,
|
||
)
|
||
for key, val in ht_ou05_band.items():
|
||
result[f"band_ht_ou05_{key}"] = val
|
||
|
||
ht_ou15_odds = float(odds.get("ht_ou15_o", 0))
|
||
ht_ou15_band = self._compute_ou_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
ht_ou15_odds, line=1.5, before_ts=before_ts,
|
||
half_time=True,
|
||
)
|
||
for key, val in ht_ou15_band.items():
|
||
result[f"band_ht_ou15_{key}"] = val
|
||
|
||
# ── OE (Tek/Çift) band ────────────────────────────────────
|
||
oe_odd_odds = float(odds.get("oe_odd", 0))
|
||
oe_band = self._compute_oe_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
oe_odd_odds, before_ts=before_ts,
|
||
)
|
||
for key, val in oe_band.items():
|
||
result[f"band_oe_{key}"] = val
|
||
|
||
# ── Cards (Kart Alt/Üst) — Hakem + Takım profili ──────────
|
||
cards_o_odds = float(odds.get("cards_o", 0))
|
||
cards_band = self._compute_cards_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
cards_o_odds, before_ts=before_ts,
|
||
referee_name=referee_name,
|
||
)
|
||
for key, val in cards_band.items():
|
||
result[f"band_cards_{key}"] = val
|
||
|
||
# ── HTFT (İY/MS) — 9 Kombinasyon ──────────────────────────
|
||
htft_band = self._compute_htft_band(
|
||
cur, home_team_id, away_team_id, league_id,
|
||
odds, before_ts=before_ts,
|
||
)
|
||
for key, val in htft_band.items():
|
||
result[key] = val # already prefixed with band_htft_
|
||
|
||
# ── Value signals ─────────────────────────────────────────
|
||
self._add_value_signal(result, "home_band_ms_value_signal",
|
||
result.get("home_band_ms_win_rate", 0.33), ms_home_odds)
|
||
self._add_value_signal(result, "away_band_ms_value_signal",
|
||
result.get("away_band_ms_win_rate", 0.33), ms_away_odds)
|
||
self._add_value_signal(result, "band_ou25_value_signal",
|
||
result.get("band_ou25_over_rate", 0.50), ou25_over_odds)
|
||
self._add_value_signal(result, "band_ou15_value_signal",
|
||
result.get("band_ou15_over_rate", 0.65), ou15_over_odds)
|
||
self._add_value_signal(result, "band_ou35_value_signal",
|
||
result.get("band_ou35_over_rate", 0.35), ou35_over_odds)
|
||
self._add_value_signal(result, "band_btts_value_signal",
|
||
result.get("band_btts_yes_rate", 0.50), btts_yes_odds)
|
||
self._add_value_signal(result, "band_dc_1x_value_signal",
|
||
result.get("band_dc_1x_rate", 0.60), dc_1x_odds)
|
||
self._add_value_signal(result, "band_dc_x2_value_signal",
|
||
result.get("band_dc_x2_rate", 0.60), dc_x2_odds)
|
||
self._add_value_signal(result, "band_dc_12_value_signal",
|
||
result.get("band_dc_12_rate", 0.67), dc_12_odds)
|
||
self._add_value_signal(result, "home_band_ht_value_signal",
|
||
result.get("home_band_ht_win_rate", 0.33), ht_h_odds)
|
||
self._add_value_signal(result, "away_band_ht_value_signal",
|
||
result.get("away_band_ht_win_rate", 0.33), ht_a_odds)
|
||
self._add_value_signal(result, "band_ht_ou05_value_signal",
|
||
result.get("band_ht_ou05_over_rate", 0.50), ht_ou05_odds)
|
||
self._add_value_signal(result, "band_ht_ou15_value_signal",
|
||
result.get("band_ht_ou15_over_rate", 0.35), ht_ou15_odds)
|
||
self._add_value_signal(result, "band_oe_value_signal",
|
||
result.get("band_oe_odd_rate", 0.50), oe_odd_odds)
|
||
# Cards value signal
|
||
self._add_value_signal(result, "band_cards_value_signal",
|
||
result.get("band_cards_combined_over_rate", 0.50), cards_o_odds)
|
||
# HTFT value signals (9 combinations)
|
||
for combo in ("11", "1x", "12", "x1", "xx", "x2", "21", "2x", "22"):
|
||
htft_key = f"htft_{combo}"
|
||
htft_odds_key = f"htft_{combo}"
|
||
htft_odds_val = float(odds.get(htft_odds_key, 0))
|
||
self._add_value_signal(result, f"band_htft_{combo}_value_signal",
|
||
result.get(f"band_htft_{combo}_rate", 0.11), htft_odds_val)
|
||
|
||
return result
|
||
|
||
# ─── Shared value signal helper ───────────────────────────────
|
||
@staticmethod
|
||
def _add_value_signal(
|
||
result: Dict[str, float], key: str,
|
||
actual_rate: float, odds_val: float,
|
||
) -> None:
|
||
if odds_val > 1.0:
|
||
result[key] = round(actual_rate - (1.0 / odds_val), 4)
|
||
else:
|
||
result[key] = 0.0
|
||
|
||
# ─── MS Band ──────────────────────────────────────────────────
|
||
|
||
def _compute_ms_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
team_id: str,
|
||
league_id: Optional[str],
|
||
team_odds: float,
|
||
is_home: bool,
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
Compute MS win/draw/loss rate for a team in a given odds band.
|
||
|
||
Looks up: "When this team had odds ~X, how often did they win?"
|
||
"""
|
||
defaults = {
|
||
"band_ms_win_rate": 0.33,
|
||
"band_ms_draw_rate": 0.33,
|
||
"band_ms_loss_rate": 0.34,
|
||
"band_ms_sample": 0.0,
|
||
"band_ms_avg_goals_scored": 1.3,
|
||
"band_ms_avg_goals_conceded": 1.1,
|
||
}
|
||
|
||
if team_odds <= 1.0:
|
||
return defaults
|
||
|
||
band_low, band_high = get_band_range(team_odds)
|
||
|
||
try:
|
||
# Query: find finished matches where this team's odds
|
||
# fell within the band, then calculate actual outcomes
|
||
cur.execute("""
|
||
WITH team_matches_in_band AS (
|
||
SELECT
|
||
m.id,
|
||
m.score_home,
|
||
m.score_away,
|
||
m.home_team_id,
|
||
m.away_team_id,
|
||
CASE
|
||
WHEN m.home_team_id = %(team_id)s THEN os_sel.odd_value::numeric
|
||
ELSE os_sel2.odd_value::numeric
|
||
END AS team_odds
|
||
FROM matches m
|
||
JOIN odd_categories oc
|
||
ON oc.match_id = m.id
|
||
AND oc.name IN ('Maç Sonucu', 'Mac Sonucu', 'Match Result', '1x2')
|
||
LEFT JOIN odd_selections os_sel
|
||
ON os_sel.odd_category_db_id = oc.db_id
|
||
AND os_sel.name = '1'
|
||
AND m.home_team_id = %(team_id)s
|
||
LEFT JOIN odd_selections os_sel2
|
||
ON os_sel2.odd_category_db_id = oc.db_id
|
||
AND os_sel2.name = '2'
|
||
AND m.away_team_id = %(team_id)s
|
||
WHERE (m.home_team_id = %(team_id)s OR m.away_team_id = %(team_id)s)
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.score_away IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND COALESCE(os_sel.odd_value::numeric, os_sel2.odd_value::numeric)
|
||
BETWEEN %(band_low)s AND %(band_high)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(max_lookback)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS sample_size,
|
||
COALESCE(AVG(CASE
|
||
WHEN (home_team_id = %(team_id)s AND score_home > score_away)
|
||
OR (away_team_id = %(team_id)s AND score_away > score_home)
|
||
THEN 1.0 ELSE 0.0 END), 0.33) AS win_rate,
|
||
COALESCE(AVG(CASE
|
||
WHEN score_home = score_away THEN 1.0
|
||
ELSE 0.0 END), 0.33) AS draw_rate,
|
||
COALESCE(AVG(CASE
|
||
WHEN (home_team_id = %(team_id)s AND score_home < score_away)
|
||
OR (away_team_id = %(team_id)s AND score_away < score_home)
|
||
THEN 1.0 ELSE 0.0 END), 0.34) AS loss_rate,
|
||
COALESCE(AVG(CASE
|
||
WHEN home_team_id = %(team_id)s THEN score_home
|
||
ELSE score_away END), 1.3) AS avg_goals_scored,
|
||
COALESCE(AVG(CASE
|
||
WHEN home_team_id = %(team_id)s THEN score_away
|
||
ELSE score_home END), 1.1) AS avg_goals_conceded
|
||
FROM team_matches_in_band
|
||
""", {
|
||
"team_id": team_id,
|
||
"before_ts": before_ts,
|
||
"band_low": band_low,
|
||
"band_high": band_high,
|
||
"max_lookback": MAX_LOOKBACK,
|
||
})
|
||
|
||
row = cur.fetchone()
|
||
if not row or int(row["sample_size"]) < MIN_TEAM_SAMPLE:
|
||
# Fallback to league-level if team sample is too small
|
||
return self._compute_ms_band_league_fallback(
|
||
cur, league_id, team_odds, before_ts, defaults
|
||
)
|
||
|
||
return {
|
||
"band_ms_win_rate": round(float(row["win_rate"]), 4),
|
||
"band_ms_draw_rate": round(float(row["draw_rate"]), 4),
|
||
"band_ms_loss_rate": round(float(row["loss_rate"]), 4),
|
||
"band_ms_sample": float(row["sample_size"]),
|
||
"band_ms_avg_goals_scored": round(float(row["avg_goals_scored"]), 2),
|
||
"band_ms_avg_goals_conceded": round(float(row["avg_goals_conceded"]), 2),
|
||
}
|
||
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ MS band query failed: {e}")
|
||
return defaults
|
||
|
||
def _compute_ms_band_league_fallback(
|
||
self,
|
||
cur: RealDictCursor,
|
||
league_id: Optional[str],
|
||
team_odds: float,
|
||
before_ts: int,
|
||
defaults: Dict[str, float],
|
||
) -> Dict[str, float]:
|
||
"""League-level fallback when team-specific sample is too small."""
|
||
if not league_id or team_odds <= 1.0:
|
||
return defaults
|
||
|
||
band_low, band_high = get_band_range(team_odds)
|
||
|
||
try:
|
||
cur.execute("""
|
||
WITH league_matches_in_band AS (
|
||
SELECT
|
||
m.id,
|
||
m.score_home,
|
||
m.score_away,
|
||
os_h.odd_value AS home_odds
|
||
FROM matches m
|
||
JOIN odd_categories oc
|
||
ON oc.match_id = m.id
|
||
AND oc.name IN ('Maç Sonucu', 'Mac Sonucu', 'Match Result', '1x2')
|
||
JOIN odd_selections os_h
|
||
ON os_h.odd_category_db_id = oc.db_id
|
||
AND os_h.name = '1'
|
||
WHERE m.league_id = %(league_id)s
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.score_away IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND os_h.odd_value::numeric BETWEEN %(band_low)s AND %(band_high)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(max_lookback)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS sample_size,
|
||
COALESCE(AVG(CASE WHEN score_home > score_away
|
||
THEN 1.0 ELSE 0.0 END), 0.33) AS home_win_rate,
|
||
COALESCE(AVG(CASE WHEN score_home = score_away
|
||
THEN 1.0 ELSE 0.0 END), 0.33) AS draw_rate,
|
||
COALESCE(AVG(score_home + score_away), 2.5) AS avg_total_goals
|
||
FROM league_matches_in_band
|
||
""", {
|
||
"league_id": league_id,
|
||
"before_ts": before_ts,
|
||
"band_low": band_low,
|
||
"band_high": band_high,
|
||
"max_lookback": MAX_LOOKBACK * 2, # Wider for league
|
||
})
|
||
|
||
row = cur.fetchone()
|
||
if not row or int(row["sample_size"]) < MIN_LEAGUE_SAMPLE:
|
||
return defaults
|
||
|
||
win_rate = float(row["home_win_rate"])
|
||
draw_rate = float(row["draw_rate"])
|
||
return {
|
||
"band_ms_win_rate": round(win_rate, 4),
|
||
"band_ms_draw_rate": round(draw_rate, 4),
|
||
"band_ms_loss_rate": round(1.0 - win_rate - draw_rate, 4),
|
||
"band_ms_sample": float(row["sample_size"]),
|
||
"band_ms_avg_goals_scored": round(float(row["avg_total_goals"]) / 2, 2),
|
||
"band_ms_avg_goals_conceded": round(float(row["avg_total_goals"]) / 2, 2),
|
||
}
|
||
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ League MS fallback failed: {e}")
|
||
return defaults
|
||
|
||
# ─── OU Band ──────────────────────────────────────────────────
|
||
|
||
def _compute_ou_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
over_odds: float,
|
||
line: float,
|
||
before_ts: int,
|
||
half_time: bool = False,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
Compute Over/Under rate for matches where teams had similar OU odds.
|
||
When half_time=True, uses IY category names and score_ht fields.
|
||
"""
|
||
defaults = {
|
||
"over_rate": 0.50,
|
||
"under_rate": 0.50,
|
||
"avg_total_goals": 2.5 if not half_time else 1.0,
|
||
"sample": 0.0,
|
||
}
|
||
|
||
if over_odds <= 1.0:
|
||
return defaults
|
||
|
||
band_low, band_high = get_band_range(over_odds)
|
||
|
||
line_str = str(line).replace(".", ",")
|
||
if half_time:
|
||
cat_names = [
|
||
f"1. Yarı {line_str} Alt/Üst",
|
||
f"1. Yari {line_str} Alt/Ust",
|
||
f"İlk Yarı {line_str} Alt/Üst",
|
||
f"Ilk Yari {line_str} Alt/Ust",
|
||
]
|
||
score_expr = "COALESCE(m.ht_score_home, 0) + COALESCE(m.ht_score_away, 0)"
|
||
else:
|
||
cat_names = [
|
||
f"{line_str} Alt/Üst",
|
||
f"{line_str} Alt/Ust",
|
||
f"Over/Under {line}",
|
||
]
|
||
score_expr = "m.score_home + m.score_away"
|
||
|
||
try:
|
||
query = f"""
|
||
WITH ou_matches AS (
|
||
SELECT
|
||
{score_expr} AS total_goals
|
||
FROM matches m
|
||
JOIN odd_categories oc
|
||
ON oc.match_id = m.id
|
||
AND oc.name = ANY(%(cat_names)s)
|
||
JOIN odd_selections os_over
|
||
ON os_over.odd_category_db_id = oc.db_id
|
||
AND os_over.name IN ('Üst', 'Ust', 'Over')
|
||
WHERE (m.home_team_id = %(home_id)s OR m.away_team_id = %(home_id)s
|
||
OR m.home_team_id = %(away_id)s OR m.away_team_id = %(away_id)s)
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND os_over.odd_value::numeric BETWEEN %(band_low)s AND %(band_high)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(max_lookback)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS sample_size,
|
||
COALESCE(AVG(CASE WHEN total_goals > %(line)s
|
||
THEN 1.0 ELSE 0.0 END), 0.5) AS over_rate,
|
||
COALESCE(AVG(total_goals), %(default_avg)s) AS avg_total
|
||
FROM ou_matches
|
||
"""
|
||
cur.execute(query, {
|
||
"home_id": home_team_id,
|
||
"away_id": away_team_id,
|
||
"cat_names": cat_names,
|
||
"before_ts": before_ts,
|
||
"band_low": band_low,
|
||
"band_high": band_high,
|
||
"line": line,
|
||
"default_avg": 1.0 if half_time else 2.5,
|
||
"max_lookback": MAX_LOOKBACK,
|
||
})
|
||
|
||
row = cur.fetchone()
|
||
if not row or int(row["sample_size"]) < MIN_TEAM_SAMPLE:
|
||
return defaults
|
||
|
||
over_rate = float(row["over_rate"])
|
||
return {
|
||
"over_rate": round(over_rate, 4),
|
||
"under_rate": round(1.0 - over_rate, 4),
|
||
"avg_total_goals": round(float(row["avg_total"]), 2),
|
||
"sample": float(row["sample_size"]),
|
||
}
|
||
|
||
except Exception as e:
|
||
ht_label = "HT_" if half_time else ""
|
||
print(f"[OddsBand] ⚠ {ht_label}OU{line} band query failed: {e}")
|
||
return defaults
|
||
|
||
# ─── BTTS Band ────────────────────────────────────────────────
|
||
|
||
def _compute_btts_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
btts_yes_odds: float,
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
Compute BTTS rate for matches where teams had similar BTTS odds.
|
||
"""
|
||
defaults = {
|
||
"yes_rate": 0.50,
|
||
"no_rate": 0.50,
|
||
"sample": 0.0,
|
||
}
|
||
|
||
if btts_yes_odds <= 1.0:
|
||
return defaults
|
||
|
||
band_low, band_high = get_band_range(btts_yes_odds)
|
||
|
||
try:
|
||
cur.execute("""
|
||
WITH btts_matches AS (
|
||
SELECT
|
||
m.score_home,
|
||
m.score_away
|
||
FROM matches m
|
||
JOIN odd_categories oc
|
||
ON oc.match_id = m.id
|
||
AND oc.name IN ('Karşılıklı Gol', 'Karsilikli Gol',
|
||
'Both Teams to Score', 'BTTS')
|
||
JOIN odd_selections os_yes
|
||
ON os_yes.odd_category_db_id = oc.db_id
|
||
AND os_yes.name IN ('Var', 'Yes')
|
||
WHERE (m.home_team_id = %(home_id)s OR m.away_team_id = %(home_id)s
|
||
OR m.home_team_id = %(away_id)s OR m.away_team_id = %(away_id)s)
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND os_yes.odd_value::numeric BETWEEN %(band_low)s AND %(band_high)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(max_lookback)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS sample_size,
|
||
COALESCE(AVG(CASE WHEN score_home > 0 AND score_away > 0
|
||
THEN 1.0 ELSE 0.0 END), 0.5) AS btts_rate
|
||
FROM btts_matches
|
||
""", {
|
||
"home_id": home_team_id,
|
||
"away_id": away_team_id,
|
||
"before_ts": before_ts,
|
||
"band_low": band_low,
|
||
"band_high": band_high,
|
||
"max_lookback": MAX_LOOKBACK,
|
||
})
|
||
|
||
row = cur.fetchone()
|
||
if not row or int(row["sample_size"]) < MIN_TEAM_SAMPLE:
|
||
return defaults
|
||
|
||
yes_rate = float(row["btts_rate"])
|
||
return {
|
||
"yes_rate": round(yes_rate, 4),
|
||
"no_rate": round(1.0 - yes_rate, 4),
|
||
"sample": float(row["sample_size"]),
|
||
}
|
||
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ BTTS band query failed: {e}")
|
||
return defaults
|
||
|
||
# ─── DC (Çifte Şans) Band ─────────────────────────────────────
|
||
|
||
def _compute_dc_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
dc_1x_odds: float,
|
||
dc_x2_odds: float,
|
||
dc_12_odds: float,
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""Compute DC hit rates from matches with similar DC odds."""
|
||
defaults = {
|
||
"1x_rate": 0.60, "x2_rate": 0.60, "12_rate": 0.67,
|
||
"1x_sample": 0.0, "x2_sample": 0.0, "12_sample": 0.0,
|
||
}
|
||
result = {}
|
||
for sel_key, odds_val, label in [
|
||
("1x", dc_1x_odds, "1-X"),
|
||
("x2", dc_x2_odds, "X-2"),
|
||
("12", dc_12_odds, "1-2"),
|
||
]:
|
||
if odds_val <= 1.0:
|
||
result[f"{sel_key}_rate"] = defaults[f"{sel_key}_rate"]
|
||
result[f"{sel_key}_sample"] = 0.0
|
||
continue
|
||
band_low, band_high = get_band_range(odds_val)
|
||
try:
|
||
cur.execute("""
|
||
WITH dc_matches AS (
|
||
SELECT m.score_home, m.score_away
|
||
FROM matches m
|
||
JOIN odd_categories oc ON oc.match_id = m.id
|
||
AND oc.name IN ('Çifte Şans', 'Cifte Sans', 'Double Chance')
|
||
JOIN odd_selections os_sel ON os_sel.odd_category_db_id = oc.db_id
|
||
AND os_sel.name IN (%(label)s, %(label_compact)s)
|
||
WHERE (m.home_team_id = %(hid)s OR m.away_team_id = %(hid)s
|
||
OR m.home_team_id = %(aid)s OR m.away_team_id = %(aid)s)
|
||
AND m.sport = 'football' AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND os_sel.odd_value::numeric BETWEEN %(bl)s AND %(bh)s
|
||
ORDER BY m.mst_utc DESC LIMIT %(ml)s
|
||
)
|
||
SELECT COUNT(*) AS ss,
|
||
COALESCE(AVG(CASE
|
||
WHEN %(sel_key)s = '1x' AND (score_home >= score_away) THEN 1.0
|
||
WHEN %(sel_key)s = 'x2' AND (score_away >= score_home) THEN 1.0
|
||
WHEN %(sel_key)s = '12' AND (score_home != score_away) THEN 1.0
|
||
ELSE 0.0 END), %(def_rate)s) AS hit_rate
|
||
FROM dc_matches
|
||
""", {
|
||
"hid": home_team_id, "aid": away_team_id,
|
||
"label": label, "label_compact": label.replace("-", ""),
|
||
"before_ts": before_ts, "bl": band_low, "bh": band_high,
|
||
"ml": MAX_LOOKBACK, "sel_key": sel_key,
|
||
"def_rate": defaults[f"{sel_key}_rate"],
|
||
})
|
||
row = cur.fetchone()
|
||
if row and int(row["ss"]) >= MIN_TEAM_SAMPLE:
|
||
result[f"{sel_key}_rate"] = round(float(row["hit_rate"]), 4)
|
||
result[f"{sel_key}_sample"] = float(row["ss"])
|
||
else:
|
||
result[f"{sel_key}_rate"] = defaults[f"{sel_key}_rate"]
|
||
result[f"{sel_key}_sample"] = 0.0
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ DC-{sel_key} band failed: {e}")
|
||
result[f"{sel_key}_rate"] = defaults[f"{sel_key}_rate"]
|
||
result[f"{sel_key}_sample"] = 0.0
|
||
return result
|
||
|
||
# ─── HT (İlk Yarı Sonucu) Band ───────────────────────────────
|
||
|
||
def _compute_ht_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
team_id: str,
|
||
league_id: Optional[str],
|
||
team_odds: float,
|
||
is_home: bool,
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""Compute HT win/draw/loss rate for a team in a given HT odds band."""
|
||
defaults = {
|
||
"band_ht_win_rate": 0.33, "band_ht_draw_rate": 0.40,
|
||
"band_ht_loss_rate": 0.27, "band_ht_sample": 0.0,
|
||
}
|
||
if team_odds <= 1.0:
|
||
return defaults
|
||
band_low, band_high = get_band_range(team_odds)
|
||
try:
|
||
cur.execute("""
|
||
WITH ht_matches AS (
|
||
SELECT m.ht_score_home, m.ht_score_away,
|
||
m.home_team_id, m.away_team_id
|
||
FROM matches m
|
||
JOIN odd_categories oc ON oc.match_id = m.id
|
||
AND oc.name IN ('1. Yarı Sonucu', '1. Yari Sonucu',
|
||
'İlk Yarı Sonucu', 'Ilk Yari Sonucu',
|
||
'Half Time Result')
|
||
LEFT JOIN odd_selections os1 ON os1.odd_category_db_id = oc.db_id
|
||
AND os1.name = '1' AND m.home_team_id = %(tid)s
|
||
LEFT JOIN odd_selections os2 ON os2.odd_category_db_id = oc.db_id
|
||
AND os2.name = '2' AND m.away_team_id = %(tid)s
|
||
WHERE (m.home_team_id = %(tid)s OR m.away_team_id = %(tid)s)
|
||
AND m.sport = 'football' AND m.status = 'FT'
|
||
AND m.ht_score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND COALESCE(os1.odd_value::numeric, os2.odd_value::numeric)
|
||
BETWEEN %(bl)s AND %(bh)s
|
||
ORDER BY m.mst_utc DESC LIMIT %(ml)s
|
||
)
|
||
SELECT COUNT(*) AS ss,
|
||
COALESCE(AVG(CASE
|
||
WHEN (home_team_id = %(tid)s AND ht_score_home > ht_score_away)
|
||
OR (away_team_id = %(tid)s AND ht_score_away > ht_score_home)
|
||
THEN 1.0 ELSE 0.0 END), 0.33) AS win_rate,
|
||
COALESCE(AVG(CASE WHEN ht_score_home = ht_score_away
|
||
THEN 1.0 ELSE 0.0 END), 0.40) AS draw_rate
|
||
FROM ht_matches
|
||
""", {
|
||
"tid": team_id, "before_ts": before_ts,
|
||
"bl": band_low, "bh": band_high, "ml": MAX_LOOKBACK,
|
||
})
|
||
row = cur.fetchone()
|
||
if not row or int(row["ss"]) < MIN_TEAM_SAMPLE:
|
||
return defaults
|
||
w = float(row["win_rate"])
|
||
d = float(row["draw_rate"])
|
||
return {
|
||
"band_ht_win_rate": round(w, 4),
|
||
"band_ht_draw_rate": round(d, 4),
|
||
"band_ht_loss_rate": round(1.0 - w - d, 4),
|
||
"band_ht_sample": float(row["ss"]),
|
||
}
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ HT band query failed: {e}")
|
||
return defaults
|
||
|
||
# ─── OE (Tek/Çift) Band ──────────────────────────────────────
|
||
|
||
def _compute_oe_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
oe_odd_odds: float,
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""Compute Odd/Even rate from matches with similar OE odds."""
|
||
defaults = {"odd_rate": 0.50, "even_rate": 0.50, "sample": 0.0}
|
||
if oe_odd_odds <= 1.0:
|
||
return defaults
|
||
band_low, band_high = get_band_range(oe_odd_odds)
|
||
try:
|
||
cur.execute("""
|
||
WITH oe_matches AS (
|
||
SELECT m.score_home + m.score_away AS total
|
||
FROM matches m
|
||
JOIN odd_categories oc ON oc.match_id = m.id
|
||
AND oc.name IN ('Tek/Çift', 'Tek/Cift', 'Odd/Even')
|
||
JOIN odd_selections os_odd ON os_odd.odd_category_db_id = oc.db_id
|
||
AND os_odd.name IN ('Tek', 'Odd')
|
||
WHERE (m.home_team_id = %(hid)s OR m.away_team_id = %(hid)s
|
||
OR m.home_team_id = %(aid)s OR m.away_team_id = %(aid)s)
|
||
AND m.sport = 'football' AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
AND os_odd.odd_value::numeric BETWEEN %(bl)s AND %(bh)s
|
||
ORDER BY m.mst_utc DESC LIMIT %(ml)s
|
||
)
|
||
SELECT COUNT(*) AS ss,
|
||
COALESCE(AVG(CASE WHEN total %% 2 = 1
|
||
THEN 1.0 ELSE 0.0 END), 0.5) AS odd_rate
|
||
FROM oe_matches
|
||
""", {
|
||
"hid": home_team_id, "aid": away_team_id,
|
||
"before_ts": before_ts, "bl": band_low, "bh": band_high,
|
||
"ml": MAX_LOOKBACK,
|
||
})
|
||
row = cur.fetchone()
|
||
if not row or int(row["ss"]) < MIN_TEAM_SAMPLE:
|
||
return defaults
|
||
odd_rate = float(row["odd_rate"])
|
||
return {
|
||
"odd_rate": round(odd_rate, 4),
|
||
"even_rate": round(1.0 - odd_rate, 4),
|
||
"sample": float(row["ss"]),
|
||
}
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ OE band query failed: {e}")
|
||
return defaults
|
||
|
||
# ─── Cards (Kart Alt/Üst) Band — 3-Layer Profiling ───────────
|
||
|
||
def _compute_cards_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
cards_o_odds: float,
|
||
before_ts: int,
|
||
referee_name: Optional[str] = None,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
3-layer card analysis:
|
||
1) Referee card profile (avg cards/match, over-line rate)
|
||
2) Team card profile (both teams' card averages)
|
||
3) Composite weighted average
|
||
|
||
Card data comes from match_player_events (event_type='card'),
|
||
NOT from score columns.
|
||
"""
|
||
# Detect the card line from odds (3.5, 4.5, 5.5 etc.)
|
||
card_line = self._detect_card_line(cards_o_odds)
|
||
|
||
defaults: Dict[str, float] = {
|
||
"referee_avg": 0.0,
|
||
"referee_over_rate": 0.50,
|
||
"referee_sample": 0.0,
|
||
"team_avg": 0.0,
|
||
"team_over_rate": 0.50,
|
||
"team_sample": 0.0,
|
||
"combined_over_rate": 0.50,
|
||
"sample": 0.0,
|
||
}
|
||
|
||
referee_avg = 0.0
|
||
referee_over_rate = 0.50
|
||
referee_sample = 0.0
|
||
team_avg = 0.0
|
||
team_over_rate = 0.50
|
||
team_sample = 0.0
|
||
|
||
# ── Layer 1: Referee card profile ──────────────────────────
|
||
if referee_name:
|
||
try:
|
||
cur.execute("""
|
||
WITH ref_matches AS (
|
||
SELECT m.id AS match_id
|
||
FROM matches m
|
||
JOIN match_officials mo ON mo.match_id = m.id
|
||
WHERE mo.name = %(ref)s
|
||
AND mo.role_id = 1
|
||
AND m.status = 'FT'
|
||
AND m.mst_utc < %(before_ts)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT 50
|
||
),
|
||
card_counts AS (
|
||
SELECT rm.match_id,
|
||
COUNT(*) AS total_cards
|
||
FROM ref_matches rm
|
||
JOIN match_player_events mpe ON mpe.match_id = rm.match_id
|
||
WHERE mpe.event_type = 'card'
|
||
GROUP BY rm.match_id
|
||
)
|
||
SELECT
|
||
COUNT(DISTINCT rm.match_id) AS ss,
|
||
COALESCE(AVG(COALESCE(cc.total_cards, 0)), 0) AS avg_cards,
|
||
COALESCE(AVG(CASE WHEN COALESCE(cc.total_cards, 0) > %(line)s
|
||
THEN 1.0 ELSE 0.0 END), 0.5) AS over_rate
|
||
FROM ref_matches rm
|
||
LEFT JOIN card_counts cc ON cc.match_id = rm.match_id
|
||
""", {
|
||
"ref": referee_name,
|
||
"before_ts": before_ts,
|
||
"line": card_line,
|
||
})
|
||
row = cur.fetchone()
|
||
if row and int(row["ss"]) >= 5:
|
||
referee_avg = float(row["avg_cards"])
|
||
referee_over_rate = float(row["over_rate"])
|
||
referee_sample = float(row["ss"])
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ Cards referee query failed: {e}")
|
||
|
||
# ── Layer 2: Team card profile ─────────────────────────────
|
||
try:
|
||
cur.execute("""
|
||
WITH team_matches AS (
|
||
SELECT m.id AS match_id
|
||
FROM matches m
|
||
WHERE (m.home_team_id = %(hid)s OR m.away_team_id = %(hid)s
|
||
OR m.home_team_id = %(aid)s OR m.away_team_id = %(aid)s)
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.mst_utc < %(before_ts)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(ml)s
|
||
),
|
||
card_counts AS (
|
||
SELECT tm.match_id,
|
||
COUNT(*) AS total_cards
|
||
FROM team_matches tm
|
||
JOIN match_player_events mpe ON mpe.match_id = tm.match_id
|
||
WHERE mpe.event_type = 'card'
|
||
GROUP BY tm.match_id
|
||
)
|
||
SELECT
|
||
COUNT(DISTINCT tm.match_id) AS ss,
|
||
COALESCE(AVG(COALESCE(cc.total_cards, 0)), 0) AS avg_cards,
|
||
COALESCE(AVG(CASE WHEN COALESCE(cc.total_cards, 0) > %(line)s
|
||
THEN 1.0 ELSE 0.0 END), 0.5) AS over_rate
|
||
FROM team_matches tm
|
||
LEFT JOIN card_counts cc ON cc.match_id = tm.match_id
|
||
""", {
|
||
"hid": home_team_id, "aid": away_team_id,
|
||
"before_ts": before_ts, "line": card_line,
|
||
"ml": MAX_LOOKBACK,
|
||
})
|
||
row = cur.fetchone()
|
||
if row and int(row["ss"]) >= MIN_TEAM_SAMPLE:
|
||
team_avg = float(row["avg_cards"])
|
||
team_over_rate = float(row["over_rate"])
|
||
team_sample = float(row["ss"])
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ Cards team query failed: {e}")
|
||
|
||
# ── Layer 3: Composite ─────────────────────────────────────
|
||
total_sample = referee_sample + team_sample
|
||
if total_sample > 0:
|
||
# Referee weight = 60% when available (referee insight is more predictive)
|
||
ref_weight = 0.6 if referee_sample >= 5 else 0.0
|
||
team_weight = 1.0 - ref_weight
|
||
combined = (referee_over_rate * ref_weight) + (team_over_rate * team_weight)
|
||
else:
|
||
combined = 0.50
|
||
|
||
return {
|
||
"referee_avg": round(referee_avg, 2),
|
||
"referee_over_rate": round(referee_over_rate, 4),
|
||
"referee_sample": referee_sample,
|
||
"team_avg": round(team_avg, 2),
|
||
"team_over_rate": round(team_over_rate, 4),
|
||
"team_sample": team_sample,
|
||
"combined_over_rate": round(combined, 4),
|
||
"sample": max(referee_sample, team_sample),
|
||
}
|
||
|
||
@staticmethod
|
||
def _detect_card_line(cards_o_odds: float) -> float:
|
||
"""
|
||
Detect the card line from over odds.
|
||
Low odds (< 1.60) → probably 3.5 line
|
||
Medium odds (1.60-2.20) → probably 4.5 line
|
||
High odds (> 2.20) → probably 5.5 line
|
||
"""
|
||
if cards_o_odds <= 0:
|
||
return 4.5 # Default assumption
|
||
if cards_o_odds < 1.60:
|
||
return 3.5
|
||
if cards_o_odds < 2.20:
|
||
return 4.5
|
||
return 5.5
|
||
|
||
# ─── HTFT (İY/MS) Band — 9 Combination Analysis ──────────────
|
||
|
||
_HTFT_COMBOS = ("11", "1x", "12", "x1", "xx", "x2", "21", "2x", "22")
|
||
|
||
def _compute_htft_band(
|
||
self,
|
||
cur: RealDictCursor,
|
||
home_team_id: str,
|
||
away_team_id: str,
|
||
league_id: Optional[str],
|
||
odds: Dict[str, float],
|
||
before_ts: int,
|
||
) -> Dict[str, float]:
|
||
"""
|
||
Compute HTFT (İlk Yarı / Maç Sonucu) hit rates for all 9 combinations.
|
||
|
||
For each combination, looks at historical matches where the HTFT odds
|
||
were in a similar band, and computes how often that specific HTFT
|
||
outcome actually occurred.
|
||
|
||
Uses ht_score_home, ht_score_away (half-time) and
|
||
score_home, score_away (full-time) from matches table.
|
||
"""
|
||
defaults: Dict[str, float] = {}
|
||
for combo in self._HTFT_COMBOS:
|
||
defaults[f"band_htft_{combo}_rate"] = 0.11 # ~1/9
|
||
defaults[f"band_htft_{combo}_sample"] = 0.0
|
||
|
||
# Check if any HTFT odds exist
|
||
has_htft_odds = any(
|
||
float(odds.get(f"htft_{combo}", 0)) > 1.0
|
||
for combo in self._HTFT_COMBOS
|
||
)
|
||
if not has_htft_odds:
|
||
return defaults
|
||
|
||
# Pick the most-traded HTFT combo (lowest odds = most likely) for band
|
||
# This gives us the best sample for the band lookup
|
||
try:
|
||
# Strategy: query all matches for both teams where HTFT odds exist,
|
||
# then compute hit rates for each combination
|
||
cur.execute("""
|
||
WITH htft_matches AS (
|
||
SELECT
|
||
m.id,
|
||
m.ht_score_home,
|
||
m.ht_score_away,
|
||
m.score_home,
|
||
m.score_away,
|
||
CASE
|
||
WHEN m.ht_score_home > m.ht_score_away THEN '1'
|
||
WHEN m.ht_score_home = m.ht_score_away THEN 'x'
|
||
ELSE '2'
|
||
END AS ht_result,
|
||
CASE
|
||
WHEN m.score_home > m.score_away THEN '1'
|
||
WHEN m.score_home = m.score_away THEN 'x'
|
||
ELSE '2'
|
||
END AS ft_result
|
||
FROM matches m
|
||
WHERE (m.home_team_id = %(hid)s OR m.away_team_id = %(hid)s
|
||
OR m.home_team_id = %(aid)s OR m.away_team_id = %(aid)s)
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.ht_score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(ml)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS total_matches,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '11') AS c_11,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '1x') AS c_1x,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '12') AS c_12,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'x1') AS c_x1,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'xx') AS c_xx,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'x2') AS c_x2,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '21') AS c_21,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '2x') AS c_2x,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '22') AS c_22
|
||
FROM htft_matches
|
||
""", {
|
||
"hid": home_team_id, "aid": away_team_id,
|
||
"before_ts": before_ts,
|
||
"ml": MAX_LOOKBACK,
|
||
})
|
||
base_row = cur.fetchone()
|
||
base_total = int(base_row["total_matches"]) if base_row else 0
|
||
|
||
if base_total < MIN_TEAM_SAMPLE:
|
||
# Fallback to league level
|
||
if league_id:
|
||
cur.execute("""
|
||
WITH htft_lg AS (
|
||
SELECT
|
||
CASE
|
||
WHEN m.ht_score_home > m.ht_score_away THEN '1'
|
||
WHEN m.ht_score_home = m.ht_score_away THEN 'x'
|
||
ELSE '2'
|
||
END AS ht_result,
|
||
CASE
|
||
WHEN m.score_home > m.score_away THEN '1'
|
||
WHEN m.score_home = m.score_away THEN 'x'
|
||
ELSE '2'
|
||
END AS ft_result
|
||
FROM matches m
|
||
WHERE m.league_id = %(lid)s
|
||
AND m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.ht_score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT 200
|
||
)
|
||
SELECT
|
||
COUNT(*) AS total_matches,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '11') AS c_11,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '1x') AS c_1x,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '12') AS c_12,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'x1') AS c_x1,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'xx') AS c_xx,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = 'x2') AS c_x2,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '21') AS c_21,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '2x') AS c_2x,
|
||
COUNT(*) FILTER (WHERE ht_result || ft_result = '22') AS c_22
|
||
FROM htft_lg
|
||
""", {"lid": league_id, "before_ts": before_ts})
|
||
base_row = cur.fetchone()
|
||
base_total = int(base_row["total_matches"]) if base_row else 0
|
||
|
||
if base_total < MIN_TEAM_SAMPLE:
|
||
return defaults
|
||
|
||
result: Dict[str, float] = {}
|
||
for combo in self._HTFT_COMBOS:
|
||
count = int(base_row[f"c_{combo}"])
|
||
rate = count / base_total if base_total > 0 else 0.11
|
||
result[f"band_htft_{combo}_rate"] = round(rate, 4)
|
||
result[f"band_htft_{combo}_sample"] = float(base_total)
|
||
|
||
# ── Odds-band refinement: for combos with odds, check
|
||
# if similar-odds matches had different hit rates
|
||
for combo in self._HTFT_COMBOS:
|
||
combo_odds = float(odds.get(f"htft_{combo}", 0))
|
||
if combo_odds <= 1.0:
|
||
continue
|
||
band_low, band_high = get_band_range(combo_odds)
|
||
try:
|
||
cur.execute("""
|
||
WITH band_htft AS (
|
||
SELECT
|
||
CASE
|
||
WHEN m.ht_score_home > m.ht_score_away THEN '1'
|
||
WHEN m.ht_score_home = m.ht_score_away THEN 'x'
|
||
ELSE '2'
|
||
END || CASE
|
||
WHEN m.score_home > m.score_away THEN '1'
|
||
WHEN m.score_home = m.score_away THEN 'x'
|
||
ELSE '2'
|
||
END AS htft_outcome
|
||
FROM matches m
|
||
JOIN odd_categories oc ON oc.match_id = m.id
|
||
AND oc.name IN (
|
||
'İlk Yarı/Maç Sonucu',
|
||
'Ilk Yarı/Maç Sonucu',
|
||
'İlk Yarı/Mac Sonucu',
|
||
'Ilk Yari/Mac Sonucu',
|
||
'IY/MS'
|
||
)
|
||
JOIN odd_selections os ON os.odd_category_db_id = oc.db_id
|
||
AND os.odd_value::numeric BETWEEN %(bl)s AND %(bh)s
|
||
WHERE m.sport = 'football'
|
||
AND m.status = 'FT'
|
||
AND m.score_home IS NOT NULL
|
||
AND m.ht_score_home IS NOT NULL
|
||
AND m.mst_utc < %(before_ts)s
|
||
ORDER BY m.mst_utc DESC
|
||
LIMIT %(ml)s
|
||
)
|
||
SELECT
|
||
COUNT(*) AS ss,
|
||
COALESCE(AVG(CASE WHEN htft_outcome = %(target)s
|
||
THEN 1.0 ELSE 0.0 END), 0.0) AS hit_rate
|
||
FROM band_htft
|
||
""", {
|
||
"bl": band_low, "bh": band_high,
|
||
"before_ts": before_ts,
|
||
"ml": MAX_LOOKBACK,
|
||
"target": combo,
|
||
})
|
||
brow = cur.fetchone()
|
||
if brow and int(brow["ss"]) >= MIN_TEAM_SAMPLE:
|
||
# Blend base rate with band-specific rate (60/40 band preference)
|
||
base_rate = result[f"band_htft_{combo}_rate"]
|
||
band_rate = float(brow["hit_rate"])
|
||
blended = (band_rate * 0.6) + (base_rate * 0.4)
|
||
result[f"band_htft_{combo}_rate"] = round(blended, 4)
|
||
result[f"band_htft_{combo}_sample"] = float(brow["ss"])
|
||
except Exception:
|
||
pass # Keep base rate
|
||
|
||
return result
|
||
|
||
except Exception as e:
|
||
print(f"[OddsBand] ⚠ HTFT band query failed: {e}")
|
||
return defaults
|
||
|