228 lines
9.4 KiB
SQL
228 lines
9.4 KiB
SQL
-- =====================================================================
|
|
-- Inspection views for Dixon-Coles model + betting odds tables (bookie)
|
|
-- Assumes tables already exist. Safe to re-run (CREATE OR REPLACE).
|
|
--
|
|
-- psql -U <user> -d <db> -f sql/model_and_odds_views.sql
|
|
-- =====================================================================
|
|
|
|
-- Predictions with match & team context
|
|
CREATE OR REPLACE VIEW bookie.v_match_prediction_detail AS
|
|
SELECT
|
|
mp.prediction_id,
|
|
mp.match_id,
|
|
m.kickoff_at,
|
|
m.status,
|
|
ht.name AS home_team,
|
|
at.name AS away_team,
|
|
l.name AS league,
|
|
l.country,
|
|
t.name AS team_name,
|
|
(m.home_team_id = t.team_id) AS is_home,
|
|
mp.predicted_goals,
|
|
mp.predicted_shots_total,
|
|
mp.predicted_shots_on_target,
|
|
mp.predicted_corners,
|
|
mp.predicted_fouls,
|
|
mp.predicted_yellow_cards,
|
|
mp.model_trained_at,
|
|
mp.half_life_days,
|
|
mp.predicted_at
|
|
FROM bookie.match_prediction mp
|
|
JOIN bookie.match m ON m.match_id = mp.match_id
|
|
JOIN bookie.team t ON t.team_id = mp.team_id
|
|
JOIN bookie.team ht ON ht.team_id = m.home_team_id
|
|
JOIN bookie.team at ON at.team_id = m.away_team_id
|
|
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
|
|
JOIN bookie.season s ON s.season_id = md.season_id
|
|
JOIN bookie.league l ON l.league_id = s.league_id
|
|
ORDER BY m.kickoff_at DESC, mp.match_id, is_home DESC;
|
|
|
|
-- League Dixon-Coles parameters
|
|
CREATE OR REPLACE VIEW bookie.v_league_model_param_detail AS
|
|
SELECT
|
|
l.league_id,
|
|
l.name AS league_name,
|
|
l.country,
|
|
l.tier_level,
|
|
p.home_advantage,
|
|
p.rho,
|
|
p.avg_home_goals,
|
|
p.avg_away_goals,
|
|
p.matches_used,
|
|
p.half_life_days,
|
|
p.trained_at,
|
|
(SELECT COUNT(*) FROM bookie.team_strength ts WHERE ts.league_id = l.league_id) AS team_strength_count
|
|
FROM bookie.league l
|
|
LEFT JOIN bookie.league_model_param p ON p.league_id = l.league_id
|
|
ORDER BY l.country, l.name;
|
|
|
|
-- Team attack/defense with readable multipliers
|
|
CREATE OR REPLACE VIEW bookie.v_team_strength_detail AS
|
|
SELECT
|
|
ts.team_strength_id,
|
|
ts.team_id,
|
|
t.name AS team_name,
|
|
ts.league_id,
|
|
l.name AS league_name,
|
|
l.country,
|
|
ts.log_attack,
|
|
ts.log_defense,
|
|
ROUND(EXP(ts.log_attack)::numeric, 4) AS attack_factor,
|
|
ROUND(EXP(ts.log_defense)::numeric, 4) AS defense_factor,
|
|
ts.matches_used,
|
|
ts.trained_at
|
|
FROM bookie.team_strength ts
|
|
JOIN bookie.team t ON t.team_id = ts.team_id
|
|
JOIN bookie.league l ON l.league_id = ts.league_id
|
|
ORDER BY l.country, l.name, attack_factor DESC;
|
|
|
|
-- 1X2 odds per bookmaker with match context
|
|
CREATE OR REPLACE VIEW bookie.v_match_odds_detail AS
|
|
SELECT
|
|
mo.match_odds_id,
|
|
mo.match_id,
|
|
m.kickoff_at,
|
|
m.status,
|
|
ht.name AS home_team,
|
|
at.name AS away_team,
|
|
l.name AS league,
|
|
mo.bookmaker,
|
|
mo.home_odds,
|
|
mo.draw_odds,
|
|
mo.away_odds,
|
|
ROUND((1 / mo.home_odds)::numeric, 4) AS home_implied,
|
|
ROUND((1 / mo.draw_odds)::numeric, 4) AS draw_implied,
|
|
ROUND((1 / mo.away_odds)::numeric, 4) AS away_implied,
|
|
mo.total_line,
|
|
mo.over_odds,
|
|
mo.under_odds,
|
|
CASE WHEN mo.over_odds IS NOT NULL THEN ROUND((1 / mo.over_odds)::numeric, 4) END AS over_implied,
|
|
CASE WHEN mo.under_odds IS NOT NULL THEN ROUND((1 / mo.under_odds)::numeric, 4) END AS under_implied,
|
|
mo.fetched_at
|
|
FROM bookie.match_odds mo
|
|
JOIN bookie.match m ON m.match_id = mo.match_id
|
|
JOIN bookie.team ht ON ht.team_id = m.home_team_id
|
|
JOIN bookie.team at ON at.team_id = m.away_team_id
|
|
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
|
|
JOIN bookie.season s ON s.season_id = md.season_id
|
|
JOIN bookie.league l ON l.league_id = s.league_id
|
|
ORDER BY m.kickoff_at DESC, mo.match_id, mo.bookmaker;
|
|
|
|
-- Averaged 1X2 odds per match (what predict uses for market blending)
|
|
CREATE OR REPLACE VIEW bookie.v_match_odds_consensus AS
|
|
SELECT
|
|
mo.match_id,
|
|
m.kickoff_at,
|
|
m.status,
|
|
ht.name AS home_team,
|
|
at.name AS away_team,
|
|
l.name AS league,
|
|
COUNT(*) AS bookmaker_count,
|
|
ROUND(AVG(mo.home_odds)::numeric, 3) AS avg_home_odds,
|
|
ROUND(AVG(mo.draw_odds)::numeric, 3) AS avg_draw_odds,
|
|
ROUND(AVG(mo.away_odds)::numeric, 3) AS avg_away_odds,
|
|
ROUND((1 / AVG(mo.home_odds))::numeric, 4) AS home_implied,
|
|
ROUND((1 / AVG(mo.draw_odds))::numeric, 4) AS draw_implied,
|
|
ROUND((1 / AVG(mo.away_odds))::numeric, 4) AS away_implied,
|
|
ROUND(AVG(mo.total_line)::numeric, 2) AS avg_total_line,
|
|
ROUND(AVG(mo.over_odds)::numeric, 3) AS avg_over_odds,
|
|
ROUND(AVG(mo.under_odds)::numeric, 3) AS avg_under_odds,
|
|
CASE WHEN AVG(mo.over_odds) IS NOT NULL
|
|
THEN ROUND((1 / AVG(mo.over_odds))::numeric, 4) END AS over_implied,
|
|
CASE WHEN AVG(mo.under_odds) IS NOT NULL
|
|
THEN ROUND((1 / AVG(mo.under_odds))::numeric, 4) END AS under_implied,
|
|
COUNT(mo.total_line) AS totals_bookmaker_count,
|
|
MAX(mo.fetched_at) AS latest_fetched_at
|
|
FROM bookie.match_odds mo
|
|
JOIN bookie.match m ON m.match_id = mo.match_id
|
|
JOIN bookie.team ht ON ht.team_id = m.home_team_id
|
|
JOIN bookie.team at ON at.team_id = m.away_team_id
|
|
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
|
|
JOIN bookie.season s ON s.season_id = md.season_id
|
|
JOIN bookie.league l ON l.league_id = s.league_id
|
|
GROUP BY mo.match_id, m.kickoff_at, m.status, ht.name, at.name, l.name
|
|
ORDER BY m.kickoff_at DESC;
|
|
|
|
-- Team name aliases for odds API matching
|
|
CREATE OR REPLACE VIEW bookie.v_team_odds_alias_detail AS
|
|
SELECT
|
|
a.team_id,
|
|
t.name AS team_name,
|
|
a.odds_api_name,
|
|
(a.odds_api_name = t.name) AS exact_name_match
|
|
FROM bookie.team_odds_alias a
|
|
JOIN bookie.team t ON t.team_id = a.team_id
|
|
ORDER BY t.name;
|
|
|
|
-- Extra markets (corners/cards O/U)
|
|
CREATE OR REPLACE VIEW bookie.v_match_extra_odds_detail AS
|
|
SELECT
|
|
eo.match_extra_odds_id,
|
|
eo.match_id,
|
|
m.kickoff_at,
|
|
ht.name AS home_team,
|
|
at.name AS away_team,
|
|
eo.market,
|
|
eo.bookmaker,
|
|
eo.line,
|
|
eo.over_odds,
|
|
eo.under_odds,
|
|
eo.fetched_at
|
|
FROM bookie.match_extra_odds eo
|
|
JOIN bookie.match m ON m.match_id = eo.match_id
|
|
JOIN bookie.team ht ON ht.team_id = m.home_team_id
|
|
JOIN bookie.team at ON at.team_id = m.away_team_id
|
|
ORDER BY m.kickoff_at DESC, eo.match_id, eo.market, eo.bookmaker;
|
|
|
|
-- One-row-per-match snapshot for pre-match review (model + odds + prediction coverage)
|
|
CREATE OR REPLACE VIEW bookie.v_prematch_model_snapshot AS
|
|
SELECT
|
|
m.match_id,
|
|
m.kickoff_at,
|
|
m.status,
|
|
ht.name AS home_team,
|
|
at.name AS away_team,
|
|
l.league_id,
|
|
l.name AS league,
|
|
l.country,
|
|
s.name AS season,
|
|
lmp.home_advantage,
|
|
lmp.rho,
|
|
lmp.avg_home_goals,
|
|
lmp.avg_away_goals,
|
|
lmp.matches_used AS model_matches_used,
|
|
lmp.trained_at AS model_trained_at,
|
|
hs.log_attack AS home_log_attack,
|
|
hs.log_defense AS home_log_defense,
|
|
aws.log_attack AS away_log_attack,
|
|
aws.log_defense AS away_log_defense,
|
|
oc.bookmaker_count,
|
|
oc.avg_home_odds,
|
|
oc.avg_draw_odds,
|
|
oc.avg_away_odds,
|
|
oc.home_implied,
|
|
oc.draw_implied,
|
|
oc.away_implied,
|
|
oc.avg_total_line,
|
|
oc.avg_over_odds,
|
|
oc.avg_under_odds,
|
|
oc.over_implied,
|
|
oc.under_implied,
|
|
hp.predicted_goals AS home_pred_goals,
|
|
ap.predicted_goals AS away_pred_goals,
|
|
hp.predicted_at AS home_predicted_at,
|
|
ap.predicted_at AS away_predicted_at
|
|
FROM bookie.match m
|
|
JOIN bookie.team ht ON ht.team_id = m.home_team_id
|
|
JOIN bookie.team at ON at.team_id = m.away_team_id
|
|
JOIN bookie.matchday md ON md.matchday_id = m.matchday_id
|
|
JOIN bookie.season s ON s.season_id = md.season_id
|
|
JOIN bookie.league l ON l.league_id = s.league_id
|
|
LEFT JOIN bookie.league_model_param lmp ON lmp.league_id = l.league_id
|
|
LEFT JOIN bookie.team_strength hs ON hs.team_id = m.home_team_id AND hs.league_id = l.league_id
|
|
LEFT JOIN bookie.team_strength aws ON aws.team_id = m.away_team_id AND aws.league_id = l.league_id
|
|
LEFT JOIN bookie.v_match_odds_consensus oc ON oc.match_id = m.match_id
|
|
LEFT JOIN bookie.match_prediction hp ON hp.match_id = m.match_id AND hp.team_id = m.home_team_id
|
|
LEFT JOIN bookie.match_prediction ap ON ap.match_id = m.match_id AND ap.team_id = m.away_team_id
|
|
ORDER BY m.kickoff_at DESC;
|