Vista ML: player_match_stats (partita per partita)
Mentre v_player_season_stats aggrega tutto per stagione, questa pipeline produce dati a granularità di singola partita. Una riga = un giocatore in una partita, con tutti i dati uniti da tutte le fonti.
Usare questa struttura per modelli che ragionano sul singolo match: predizione del fantavoto, probabilità di gol, rendimento in funzione dell’avversario.
CSV di training: il repo privato FantaStats/training-data contiene i CSV partita per partita esportati da
player_match_stats, pronti per il training ML.
Architettura in tre livelli
v_player_match_stats ← VIEW (join live, ~273k righe)
↓
player_match_precomputed ← cache delle join costose (103k righe)
↓
player_match_stats ← tabella materializzata completa (103k righe)
v_player_match_stats: view che esegue tutte le join in tempo reale. Corretta ma lenta su export completi.player_match_precomputed: precalcola le join più costose (valore di mercato, stato infortuni, metriche di squadra/avversario Understat, tiri per tipo). Riduce drasticamente il tempo di query sulla view.player_match_stats: materializzazione completa della view — da usare per ML e export. Già tutto joinato, nessuna latenza.
Regola pratica: usa sempre
player_match_statsper ML e analisi. Usav_player_match_statssolo per verifiche spot o quando i dati sorgente sono stati appena aggiornati e non hai ancora rimaterializzato.
Aggiornamento dopo i dati sorgente
# 1. Rimaterializza la cache delle join costose
cd transformations/migrations && uv run python refresh_precomputed.py
# 2. Rimaterializza la tabella completa
cd transformations && uv run python refresh_player_match_stats.py
Eseguire sempre entrambi i comandi nell’ordine indicato dopo ogni aggiornamento dei dati sorgente (nuove partite, nuovi dati Sofascore, ecc.).
Riferimento colonne (181 totali)
p_ — Anagrafica giocatore
| Colonna | Descrizione |
|---|---|
p_player_id |
ID univoco giocatore |
p_player_name |
Nome del giocatore |
p_height |
Altezza in cm |
p_age |
Età alla data della partita |
p_foot |
Piede preferito (left / right / both) |
m_ — Contesto partita
| Colonna | Descrizione |
|---|---|
m_season |
Stagione (es. “2024” = 2024/25) |
m_match_day |
Giornata (1–38) |
m_tournament |
Sempre “Serie A” |
m_date |
Data della partita |
m_home_club |
Squadra di casa |
m_away_club |
Squadra ospite |
m_home_score |
Gol squadra di casa |
m_away_score |
Gol squadra ospite |
m_is_home |
1 se il giocatore gioca in casa |
m_player_club |
Squadra del giocatore |
fc_ — Dati Fantacalcio partita
| Colonna | Descrizione |
|---|---|
fc_role_short |
Ruolo classico (p/d/c/a) |
fc_role_mantra |
Ruolo Mantra (es. “Dc;Ds”) |
fc_q_quotation_classic |
Quotazione Classic a quella giornata |
fc_vote |
Voto classico |
fc_fantasy_vote |
Fantavoto (con bonus/malus) |
fc_sub_in_minute |
Minuto di ingresso da subentrato |
fc_sub_out_minute |
Minuto di uscita (sostituito) |
fc_goals_scored |
Gol segnati |
fc_goals_conceded |
Gol subiti (portieri) |
fc_assists |
Assist |
fc_yellow_cards |
Cartellini gialli |
fc_red_cards |
Cartellini rossi |
fc_own_goals |
Autogol |
fc_penalties_scored |
Rigori segnati |
fc_penalties_missed |
Rigori sbagliati |
fc_penalties_saved |
Rigori parati (portieri) |
inj_ — Stato infortuni
| Colonna | Descrizione |
|---|---|
inj_injured |
1 se infortunato alla data della partita, NULL altrimenti |
inj_injury_type |
Tipo (injury / illness / other) |
inj_injury_name |
Nome dell’infortunio o malattia |
tm_ — Valore di mercato
| Colonna | Descrizione |
|---|---|
tm_market_value |
Valore Transfermarkt più recente alla data della partita (€) |
ss_ — SofaScore statistiche partita
| Colonna | Descrizione |
|---|---|
ss_substitute |
1 se entrato da subentrato |
ss_minutes_played |
Minuti giocati |
ss_rating |
Rating SofaScore |
ss_alternative_rating |
Rating alternativo SofaScore |
ss_total_pass |
Passaggi totali |
ss_accurate_pass |
Passaggi precisi |
ss_total_long_balls |
Palle lunghe totali |
ss_accurate_long_balls |
Palle lunghe precise |
ss_accurate_own_half_passes |
Passaggi precisi nella propria metà |
ss_total_own_half_passes |
Passaggi totali nella propria metà |
ss_accurate_opposition_half_passes |
Passaggi precisi nella metà avversaria |
ss_total_opposition_half_passes |
Passaggi totali nella metà avversaria |
ss_total_cross |
Cross totali |
ss_accurate_cross |
Cross precisi |
ss_key_pass |
Passaggi chiave (portano a tiro) |
ss_aerial_lost |
Duelli aerei persi |
ss_aerial_won |
Duelli aerei vinti |
ss_duel_lost |
Duelli totali persi |
ss_duel_won |
Duelli totali vinti |
ss_challenge_lost |
Dribbling subiti |
ss_dispossessed |
Palle perse per duello |
ss_total_contest |
Contrasti totali |
ss_won_contest |
Contrasti vinti (dribbling riusciti) |
ss_unsuccessful_touch |
Tocchi sbagliati |
ss_touches |
Tocchi totali |
ss_ball_recovery |
Recuperi palla |
ss_possession_lost_ctrl |
Palle perse sotto pressione |
ss_shot_off_target |
Tiri fuori porta |
ss_on_target_scoring_attempt |
Tiri in porta |
ss_blocked_scoring_attempt |
Tiri bloccati |
ss_total_shots |
Tiri totali |
ss_goals |
Gol segnati |
ss_big_chance_missed |
Grandi occasioni mancate |
ss_goal_assist |
Assist |
ss_big_chance_created |
Grandi occasioni create |
ss_expected_assists |
Expected assists (xA) |
ss_expected_goals |
Expected goals (xG) |
ss_total_clearance |
Spazzate difensive |
ss_outfielder_block |
Tiri bloccati da giocatori di movimento |
ss_clearance_off_line |
Salvataggi sulla linea di porta |
ss_interception_won |
Intercettazioni |
ss_total_tackle |
Contrasti totali |
ss_won_tackle |
Contrasti vinti |
ss_was_fouled |
Falli subiti |
ss_fouls |
Falli commessi |
ss_total_offside |
Fuorigioco |
ss_saved_shots_from_inside_the_box |
Tiri salvati dall’interno area (portieri) |
ss_saves |
Parate (portieri) |
ss_good_high_claim |
Prese alte riuscite (portieri) |
ss_error_lead_to_a_shot |
Errori che portano a tiro |
ss_goals_prevented |
Gol evitati basati su xG (portieri) |
ss_ball_carries_count |
Numero di conduzioni palla |
ss_total_ball_carries_distance |
Distanza totale con palla ai piedi (m) |
ss_progressive_ball_carries_count |
Conduzioni progressive |
ss_total_progressive_ball_carries_distance |
Distanza progressiva con palla ai piedi (m) |
ss_total_progression |
Progressione campo totale |
ss_best_ball_carry_progression |
Migliore singola conduzione progressiva (m) |
ss_shot_value_normalized |
Valore normalizzato tiri — NULL per Serie A |
ss_pass_value_normalized |
Valore normalizzato passaggi — NULL per Serie A |
ss_dribble_value_normalized |
Valore normalizzato dribbling — NULL per Serie A |
ss_defensive_value_normalized |
Valore normalizzato difensivo — NULL per Serie A |
ss_m_ — Metadati partita SofaScore
| Colonna | Descrizione |
|---|---|
ss_m_home_formation |
Modulo squadra di casa |
ss_m_away_formation |
Modulo squadra ospite |
ss_m_home_manager |
Allenatore squadra di casa |
ss_m_away_manager |
Allenatore squadra ospite |
ss_m_referee |
Arbitro |
ss_m_injury_time_1 |
Recupero primo tempo (minuti) |
ss_m_injury_time_2 |
Recupero secondo tempo (minuti) |
us_ — Understat statistiche giocatore
| Colonna | Descrizione |
|---|---|
us_position |
Posizione tattica (DC/MC/GK/Sub/ecc.) |
us_time_played |
Minuti giocati |
us_shots |
Tiri totali |
us_key_passes |
Passaggi chiave |
us_xG |
Expected goals |
us_xA |
Expected assists |
us_npg |
Gol non da rigore |
us_npxG |
Expected goals non da rigore |
us_xGChain |
xG della catena d’azione in cui è coinvolto |
us_xGBuildup |
xG della fase di costruzione in cui è coinvolto |
us_m_ — Understat contesto partita
| Colonna | Descrizione |
|---|---|
us_m_forecast_w |
Probabilità pre-partita vittoria casa |
us_m_forecast_d |
Probabilità pre-partita pareggio |
us_m_forecast_l |
Probabilità pre-partita sconfitta casa |
us_m_home_xG |
xG totale squadra di casa |
us_m_away_xG |
xG totale squadra ospite |
us_t_ — Understat metriche squadra del giocatore
| Colonna | Descrizione |
|---|---|
us_t_xG |
xG prodotto dalla squadra |
us_t_xGA |
xG concesso dalla squadra |
us_t_npxG |
npxG prodotto dalla squadra |
us_t_npxGA |
npxG concesso dalla squadra |
us_t_ppda_att |
Attacchi PPDA (pressing intensità offensiva) |
us_t_ppda_def |
Difese PPDA |
us_t_ppda_allowed_att |
Attacchi PPDA consentiti |
us_t_ppda_allowed_def |
Difese PPDA consentite |
us_t_deep |
Completamenti profondi (passaggi nell’ultimo terzo) |
us_t_deep_allowed |
Completamenti profondi consentiti |
us_t_xpts |
Expected points |
us_t_npxGD |
Differenza npxG (attacco − difesa) |
us_t_wins |
Vittorie accumulate (stagione corrente fino a questa giornata) |
us_t_draws |
Pareggi accumulati |
us_t_loses |
Sconfitte accumulate |
us_t_pts |
Punti accumulati |
us_opp_ — Understat metriche avversario
Stesse 16 colonne di us_t_ ma riferite all’avversario: us_opp_xG, us_opp_xGA, us_opp_npxG, us_opp_npxGA, us_opp_ppda_att, us_opp_ppda_def, us_opp_ppda_allowed_att, us_opp_ppda_allowed_def, us_opp_deep, us_opp_deep_allowed, us_opp_xpts, us_opp_npxGD, us_opp_wins, us_opp_draws, us_opp_loses, us_opp_pts.
us_s_ — Tiri Understat per tipo, risultato e situazione
Per tipo (rightfoot / leftfoot / head / other) — 5 colonne ciascuno:
| Colonna | Descrizione |
|---|---|
us_s_{tipo} |
Numero di tiri con quel tipo |
us_s_{tipo}_avg_min |
Minuto medio dei tiri |
us_s_{tipo}_avg_x |
Coordinata X media (0–1, porta = 1) |
us_s_{tipo}_avg_y |
Coordinata Y media (0–1, centro = 0.5) |
us_s_{tipo}_avg_xg |
xG medio per tiro |
Per risultato — 1 colonna ciascuno (conteggio):
us_s_res_goal · us_s_res_saved · us_s_res_missed · us_s_res_blocked · us_s_res_on_post · us_s_res_own_goal
Per situazione — 1 colonna ciascuno (conteggio):
us_s_sit_open_play · us_s_sit_set_piece · us_s_sit_from_corner · us_s_sit_direct_freekick · us_s_sit_penalty
Logica di join in v_player_match_stats
La spine è fc_player_matches, che definisce quali giocatori hanno un record per ogni giornata:
fc_player_matches
→ fc_player_seasons (player_season_id)
→ players (player_id)
→ matches (match_id)
→ ss_matches (sofascore_event_id, player_id)
→ ss_match_meta (sofascore_event_id)
→ us_player_match_details (player_id, home_club_id, away_club_id, season_id)
→ fc_player_quotations (player_season_id, match_day) — exact match
→ tm_market_value LATERAL: snapshot più recente con date ≤ match_date
→ db_player_injuries LATERAL: infortunio attivo che copre la data della partita
→ player_match_precomputed (fc_player_match_id)
Query di esempio
Fantavoto medio per ruolo e avversario (utile per capire quali avversari favoriscono certi ruoli):
SELECT
fc_role_short,
m_away_club AS avversario,
ROUND(AVG(fc_fantasy_vote), 2) AS fantavoto_medio,
COUNT(*) AS partite
FROM player_match_stats
WHERE m_season = '2024'
AND m_is_home = 1
AND fc_vote IS NOT NULL
GROUP BY fc_role_short, m_away_club
ORDER BY fc_role_short, fantavoto_medio DESC;
Rendimento per modulo di gioco:
SELECT
ss_m_home_formation AS modulo,
fc_role_short,
ROUND(AVG(fc_fantasy_vote), 2) AS media_fantavoto,
COUNT(*) AS partite
FROM player_match_stats
WHERE m_season = '2024'
AND m_is_home = 1
AND ss_m_home_formation IS NOT NULL
GROUP BY modulo, fc_role_short
ORDER BY fc_role_short, media_fantavoto DESC;
Tiri per piede e conversioni (attaccanti):
SELECT
p_player_name,
SUM(us_s_rightfoot) AS tiri_destro,
SUM(us_s_leftfoot) AS tiri_sinistro,
SUM(us_s_head) AS tiri_testa,
SUM(us_s_res_goal) AS gol,
SUM(us_s_res_saved) AS parati,
ROUND(SUM(us_xG), 2) AS xG_totale
FROM player_match_stats
WHERE m_season = '2024'
AND fc_role_short = 'A'
GROUP BY p_player_id, p_player_name
ORDER BY gol DESC
LIMIT 20;
Giocatori con infortunio attivo che risultano titolari (anomalie nei dati):
SELECT p_player_name, m_date, m_match_day, inj_injury_name, fc_vote
FROM player_match_stats
WHERE inj_injured = 1
AND fc_vote IS NOT NULL
AND m_season = '2024'
ORDER BY m_date DESC;
xG concesso dall’avversario per giornata (contesto difensivo per i difensori):
SELECT
p_player_name,
m_match_day,
m_away_club,
us_opp_xG AS xg_avversario,
fc_fantasy_vote
FROM player_match_stats
WHERE m_season = '2024'
AND fc_role_short = 'd'
AND m_is_home = 1
ORDER BY m_match_day, p_player_name;