php项目如何评估教练换人得分能力？

PHP项目评估教练换人得分能力的技术方案

这是一个体育数据分析类的需求，我来系统性地拆解如何用PHP实现"教练换人得分能力"的评估。

明确评估指标（先定义"得分能力"）

教练换人得分能力本质是衡量：替补球员上场后对球队进攻/得分的贡献效率,核心指标建议包括：

数据模型设计（数据库表）

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY,
    match_date DATETIME,
    home_team_id INT,
    away_team_id INT,
    status TINYINT
);
-- 换人事件表
CREATE TABLE substitutions (
    id INT PRIMARY KEY,
    match_id INT,
    team_id INT,
    coach_id INT,
    player_out_id INT,      -- 下场球员
    player_in_id INT,       -- 上场球员
    minute INT,             -- 换人发生的比赛分钟
    score_diff_at_sub INT,  -- 换人时本队净胜分
    period TINYINT          -- 第几节/半场
);
-- 得分事件表（用于关联换人后得分）
CREATE TABLE scoring_events (
    id INT PRIMARY KEY,
    match_id INT,
    team_id INT,
    player_id INT,
    minute INT,
    points INT,
    event_type VARCHAR(20)
);
-- 球员上场时间段表（用于精确计算在场时间）
CREATE TABLE player_stints (
    id INT PRIMARY KEY,
    match_id INT,
    player_id INT,
    team_id INT,
    start_minute INT,
    end_minute INT
);

PHP核心计算逻辑

计算单次换人的得分贡献

<?php
class SubstitutionEvaluator
{
    private PDO $db;
    // 评估窗口：换人后观察多少分钟
    private int $windowMinutes = 10;
    public function __construct(PDO $db)
    {
        $this->db = $db;
    }
    /**
     * 计算某次换人后的净得分（换人后窗口期内）
     */
    public function evaluateSingleSubstitution(int $subId): array
    {
        $sub = $this->getSubstitution($subId);
        $startMin = $sub['minute'];
        $endMin   = $startMin + $this->windowMinutes;
        // 换人前后本队与对方得分
        $teamScoreGained = $this->getTeamScore($sub['match_id'], $sub['team_id'], $startMin, $endMin);
        $oppScoreGained  = $this->getTeamScore($sub['match_id'], $this->getOpponentId($sub), $startMin, $endMin);
        $netScore = $teamScoreGained - $oppScoreGained;
        // 单位时间得分率（每分钟净得分）
        $perMinute = $netScore / $this->windowMinutes;
        return [
            'sub_id'        => $subId,
            'team_score'    => $teamScoreGained,
            'opp_score'     => $oppScoreGained,
            'net_score'     => $netScore,
            'per_minute'    => round($perMinute, 3),
        ];
    }
    private function getTeamScore(int $matchId, int $teamId, int $from, int $to): int
    {
        $sql = "SELECT COALESCE(SUM(points),0) FROM scoring_events
                WHERE match_id = :m AND team_id = :t
                  AND minute >= :from AND minute < :to";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':m'=>$matchId, ':t'=>$teamId, ':from'=>$from, ':to'=>$to]);
        return (int)$stmt->fetchColumn();
    }
    private function getSubstitution(int $subId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM substitutions WHERE id = ?");
        $stmt->execute([$subId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: throw new RuntimeException("换人记录不存在");
    }
    private function getOpponentId(array $sub): int
    {
        $stmt = $this->db->prepare("SELECT home_team_id, away_team_id FROM matches WHERE id = ?");
        $stmt->execute([$sub['match_id']]);
        $m = $stmt->fetch(PDO::FETCH_ASSOC);
        return $m['home_team_id'] == $sub['team_id'] ? $m['away_team_id'] : $m['home_team_id'];
    }
}

汇总教练的换人得分能力评分

class CoachSubstitutionScorer
{
    public function __construct(private PDO $db, private SubstitutionEvaluator $evaluator) {}
    /**
     * 计算某教练在时间范围内的换人得分能力综合评分
     */
    public function scoreCoach(int $coachId, string $from, string $to): array
    {
        $sql = "SELECT s.id FROM substitutions s
                JOIN matches m ON m.id = s.match_id
                WHERE s.coach_id = :c AND m.match_date BETWEEN :from AND :to";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':c'=>$coachId, ':from'=>$from, ':to'=>$to]);
        $subIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $totalNet = 0; $perMinuteSum = 0; $positive = 0;
        $count = count($subIds);
        if ($count === 0) {
            return ['coach_id'=>$coachId, 'sub_count'=>0, 'score'=>0];
        }
        foreach ($subIds as $id) {
            $r = $this->evaluator->evaluateSingleSubstitution($id);
            $totalNet    += $r['net_score'];
            $perMinuteSum += $r['per_minute'];
            if ($r['net_score'] > 0) $positive++;
        }
        // 归一化评分（示例权重，可调）
        $avgNet        = $totalNet / $count;            // 平均每次换人净得分
        $avgPerMinute  = $perMinuteSum / $count;        // 平均每分钟净得分
        $successRate   = $positive / $count;            // 换人有效率
        // 综合评分（0-100），权重可配置
        $score = $this->normalize($avgNet,        -10, 10)  * 0.4
               + $this->normalize($avgPerMinute,  -1, 1)    * 0.3
               + $successRate * 100                          * 0.3;
        return [
            'coach_id'      => $coachId,
            'sub_count'     => $count,
            'avg_net_score' => round($avgNet, 2),
            'avg_per_min'   => round($avgPerMinute, 3),
            'success_rate'  => round($successRate, 3),
            'score'         => round($score, 2),
        ];
    }
    /** 将值线性映射到 0-100 */
    private function normalize(float $v, float $min, float $max): float
    {
        $v = max($min, min($max, $v));
        return ($v - $min) / ($max - $min) * 100;
    }
}

关键难点与处理策略

1. 换人窗口期的选择：固定10分钟是简化的做法，更严谨的做法是按"该替补下次被换下"或"半场结束"为终点，用 player_stints 表精确控制。
2. 对手强度校正：用对手排名/防守效率做加权，避免"打弱队刷分"虚高。
3. 样本量问题：换人次数少的教练评分不可靠，需加置信度门槛（如少于20次换人标注"数据不足"）。
4. 位置/角色归一化：前锋换人天然得分高，需按球员位置或预期得分(xP)做基线校正。
5. 垃圾时间剔除：分差>20分钟的时间段可标记剔除,避免失真。
6. 时间衰减：近期比赛权重更高（如指数衰减 w = exp(-λ·天数)）。

性能与工程建议

• 预计算 + 缓存：换人评估结果可离线跑批（如每日凌晨）写入 coach_scores 汇总表，前端只读缓存（Redis）,避免实时聚合。
• 避免 N+1 查询：批量取某场比赛全部得分事件后，在 PHP 内存中按分钟区间聚合，比每次 SUM 查询快得多。
• 异步/队列：全量重算用队列（Laravel Queue / Symfony Messenger）分片处理。
• 数据一致性：换人时间点与得分事件分钟需统一口径（同一边界规则，建议左闭右开）。

返回给前端的结构示例

{
  "coach_id": 12,
  "coach_name": "张三",
  "period": "2024-01-01 ~ 2024-12-31",
  "sub_count": 86,
  "avg_net_score": 2.35,
  "avg_per_min": 0.21,
  "success_rate": 0.62,
  "score": 74.8,
  "confidence": "high",
  "dimensions": {
    "close_game_score": 78.2,
    "blowout_score": 65.1
  }
}

如果你能补充具体运动项目（篮球/足球/冰球换人规则差异大）、现有数据字段和评估目的（选帅 / 赛后复盘 / 竞猜模型）,我可以进一步细化指标公式和表结构。