php项目怎么看两队的主客场战绩差异？

wen PHP项目 2026-11-08 2

PHP项目中查看两队主客场战绩差异的方法

这是一个体育数据分析的常见需求,下面我给你一个完整的实现思路和代码示例。

php项目怎么看两队的主客场战绩差异？

数据表设计

假设你有以下数据表结构（以足球为例）：

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY,
    home_team_id INT,
    away_team_id INT,
    home_score INT,
    away_score INT,
    match_date DATE,
    league_id INT,
    status TINYINT  -- 1=已结束
);
-- 球队表
CREATE TABLE teams (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

核心统计逻辑

主客场战绩差异的核心是分别统计两队的主场战绩和客场战绩，然后对比。

查询主场战绩

<?php
function getHomeStats(PDO $pdo, int $teamId, ?int $leagueId = null): array
{
    $sql = "SELECT 
                COUNT(*) AS total,
                SUM(CASE WHEN home_score > away_score THEN 1 ELSE 0 END) AS wins,
                SUM(CASE WHEN home_score = away_score THEN 1 ELSE 0 END) AS draws,
                SUM(CASE WHEN home_score < away_score THEN 1 ELSE 0 END) AS losses,
                SUM(home_score) AS goals_for,
                SUM(away_score) AS goals_against
            FROM matches
            WHERE home_team_id = :team_id
              AND status = 1";
    if ($leagueId) {
        $sql .= " AND league_id = :league_id";
    }
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':team_id', $teamId, PDO::PARAM_INT);
    if ($leagueId) {
        $stmt->bindValue(':league_id', $leagueId, PDO::PARAM_INT);
    }
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total = (int)$row['total'];
    return [
        'total'         => $total,
        'wins'          => (int)$row['wins'],
        'draws'         => (int)$row['draws'],
        'losses'        => (int)$row['losses'],
        'goals_for'     => (int)$row['goals_for'],
        'goals_against' => (int)$row['goals_against'],
        'goal_diff'     => (int)$row['goals_for'] - (int)$row['goals_against'],
        'win_rate'      => $total ? round($row['wins'] / $total * 100, 2) : 0,
        'points'        => (int)$row['wins'] * 3 + (int)$row['draws'],
        'avg_goals_for' => $total ? round($row['goals_for'] / $total, 2) : 0,
        'avg_goals_against' => $total ? round($row['goals_against'] / $total, 2) : 0,
    ];
}

查询客场战绩

function getAwayStats(PDO $pdo, int $teamId, ?int $leagueId = null): array
{
    $sql = "SELECT 
                COUNT(*) AS total,
                SUM(CASE WHEN away_score > home_score THEN 1 ELSE 0 END) AS wins,
                SUM(CASE WHEN away_score = home_score THEN 1 ELSE 0 END) AS draws,
                SUM(CASE WHEN away_score < home_score THEN 1 ELSE 0 END) AS losses,
                SUM(away_score) AS goals_for,
                SUM(home_score) AS goals_against
            FROM matches
            WHERE away_team_id = :team_id
              AND status = 1";
    if ($leagueId) {
        $sql .= " AND league_id = :league_id";
    }
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':team_id', $teamId, PDO::PARAM_INT);
    if ($leagueId) {
        $stmt->bindValue(':league_id', $leagueId, PDO::PARAM_INT);
    }
    $stmt->execute();
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total = (int)$row['total'];
    return [
        'total'         => $total,
        'wins'          => (int)$row['wins'],
        'draws'         => (int)$row['draws'],
        'losses'        => (int)$row['losses'],
        'goals_for'     => (int)$row['goals_for'],
        'goals_against' => (int)$row['goals_against'],
        'goal_diff'     => (int)$row['goals_for'] - (int)$row['goals_against'],
        'win_rate'      => $total ? round($row['wins'] / $total * 100, 2) : 0,
        'points'        => (int)$row['wins'] * 3 + (int)$row['draws'],
        'avg_goals_for' => $total ? round($row['goals_for'] / $total, 2) : 0,
        'avg_goals_against' => $total ? round($row['goals_against'] / $total, 2) : 0,
    ];
}

对比两队主客场差异

关键思路：比赛通常是一主一客，所以要对比的是：

A队主场战绩 vs B队客场战绩（A为主场的场合）
B队主场战绩 vs A队客场战绩（B为主场的场合）

function compareTeams(PDO $pdo, int $teamA, int $teamB, ?int $leagueId = null, ?int $limit = 10): array
{
    // 可扩展：N 场只统计近期状态
    $aHome = getHomeStats($pdo, $teamA, $leagueId);
    $aAway = getAwayStats($pdo, $teamA, $leagueId);
    $bHome = getHomeStats($pdo, $teamB, $leagueId);
    $bAway = getAwayStats($pdo, $teamB, $leagueId);
    return [
        'team_a' => ['home' => $aHome, 'away' => $aAway],
        'team_b' => ['home' => $bHome, 'away' => $bAway],
        // 场景一：A队主场 vs B队客场
        'scenario_a_home' => [
            'attack_diff'  => $aHome['avg_goals_for'] - $bAway['avg_goals_against'],
            'defense_diff' => $aHome['avg_goals_against'] - $bAway['avg_goals_for'],
            'points_diff'  => $aHome['points'] - $bAway['points'],
            'win_rate_diff'=> $aHome['win_rate'] - $bAway['win_rate'],
        ],
        // 场景二：B队主场 vs A队客场
        'scenario_b_home' => [
            'attack_diff'  => $bHome['avg_goals_for'] - $aAway['avg_goals_against'],
            'defense_diff' => $bHome['avg_goals_against'] - $aAway['avg_goals_for'],
            'points_diff'  => $bHome['points'] - $aAway['points'],
            'win_rate_diff'=> $bHome['win_rate'] - $aAway['win_rate'],
        ],
    ];
}

计算主客场"偏差指数"（更高级）

有些分析会关心"某队主场是否特别强"或"客场是否特别弱"，可以计算偏差：

function homeAwayBias(array $home, array $away): array
{
    // 场均净胜球差：主场 - 客场
    $homeGoalDiffPerGame = $home['total'] ? $home['goal_diff'] / $home['total'] : 0;
    $awayGoalDiffPerGame = $away['total'] ? $away['goal_diff'] / $away['total'] : 0;
    return [
        'home_win_rate'    => $home['win_rate'],
        'away_win_rate'    => $away['win_rate'],
        'win_rate_gap'     => round($home['win_rate'] - $away['win_rate'], 2),
        'goal_diff_per_game_home' => round($homeGoalDiffPerGame, 2),
        'goal_diff_per_game_away' => round($awayGoalDiffPerGame, 2),
        'goal_diff_gap'    => round($homeGoalDiffPerGame - $awayGoalDiffPerGame, 2),
    ];
}

解读：

win_rate_gap > 20 → 典型"主场龙客场虫"
goal_diff_gap > 1.0 → 主客场表现差距明显

只取最近 N 场比赛（避免老数据干扰）

function getRecentHomeStats(PDO $pdo, int $teamId, int $limit = 10): array
{
    $sql = "SELECT home_score AS gf, away_score AS ga
            FROM matches
            WHERE home_team_id = :t AND status = 1
            ORDER BY match_date DESC
            LIMIT :limit";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':t', $teamId, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $wins = $draws = $losses = $gf = $ga = 0;
    foreach ($rows as $r) {
        $gf += $r['gf']; $ga += $r['ga'];
        if ($r['gf'] > $r['ga']) $wins++;
        elseif ($r['gf'] == $r['ga']) $draws++;
        else $losses++;
    }
    $n = count($rows);
    return [
        'total' => $n,
        'wins' => $wins, 'draws' => $draws, 'losses' => $losses,
        'goals_for' => $gf, 'goals_against' => $ga,
        'win_rate' => $n ? round($wins / $n * 100, 2) : 0,
    ];
}

性能优化建议

加索引：

CREATE INDEX idx_home_team ON matches(home_team_id, status);
CREATE INDEX idx_away_team ON matches(away_team_id, status);

预计算缓存：如果数据量大、并发高，可以建一张 team_stats 汇总表，每天或每次比赛结束后 ETL 更新：

CREATE TABLE team_stats (
    team_id INT,
    league_id INT,
    scope ENUM('home','away','all'),
    played INT, wins INT, draws INT, losses INT,
    goals_for INT, goals_against INT,
    updated_at DATETIME
);

用一条 SQL 同时查两队（减少往返）：

SELECT team_id, scope, ...
FROM (
    SELECT home_team_id AS team_id, 'home' AS scope, ... FROM matches WHERE home_team_id IN (?,?)
    UNION ALL
    SELECT away_team_id AS team_id, 'away' AS scope, ... FROM matches WHERE away_team_id IN (?,?)
) t GROUP BY team_id, scope

输出示例

$data = compareTeams($pdo, 1, 2, 10);
print_r($data);
/* 输出：
Array (
  [team_a] => [home => [win_rate=>60,...], away=>[...]],
  [team_b] => [...],
  [scenario_a_home] => [
      attack_diff => 0.8,   // A主场进攻 vs B客场防守，A场均多进0.8球
      defense_diff => -0.3, // 
      points_diff => 5,     // A主场积分比B客场多5分
  ],
  ...
)
*/

关键点回顾：