IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

php项目统计高球传中争顶成功率？

wen PHP项目 2

PHP项目统计高球传中争顶成功率

需求拆解

"高球传中争顶成功率"是足球数据分析中的复合指标,需要拆解为：

php项目统计高球传中争顶成功率？

高球传中争顶成功率 = 争顶成功次数 / 争顶总次数 × 100%

其中需要在事件数据层面做筛选：

  • 事件类型 = 传中（cross）
  • 传球方式 = 高球（high / lofted）
  • 后续结果 = 进入争顶（aerial duel）
  • 结果 = 成功 / 失败

数据库设计

事件表 match_events

CREATE TABLE match_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id INT UNSIGNED NOT NULL,
    team_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED NOT NULL,
    event_type VARCHAR(32) NOT NULL COMMENT 'cross/pass/shot/aerial_duel...',
    event_sub_type VARCHAR(32) DEFAULT NULL COMMENT 'high/low/...',
    event_time INT UNSIGNED NOT NULL COMMENT '秒',
    related_event_id BIGINT UNSIGNED DEFAULT NULL COMMENT '关联事件',
    outcome TINYINT NOT NULL DEFAULT 0 COMMENT '1成功 0失败',
    x DECIMAL(5,2) DEFAULT NULL,
    y DECIMAL(5,2) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY idx_match_team (match_id, team_id),
    KEY idx_type (event_type, event_sub_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

统计结果表（预聚合，提升查询性能）

CREATE TABLE match_cross_stats (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    match_id INT UNSIGNED NOT NULL,
    team_id INT UNSIGNED NOT NULL,
    player_id INT UNSIGNED DEFAULT NULL,
    cross_total INT UNSIGNED DEFAULT 0,
    cross_high INT UNSIGNED DEFAULT 0,
    aerial_total INT UNSIGNED DEFAULT 0,
    aerial_success INT UNSIGNED DEFAULT 0,
    success_rate DECIMAL(6,2) DEFAULT 0.00,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_match_team_player (match_id, team_id, player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心统计逻辑

关键问题：如何将"高球传中"和"随后的"争顶事件"关联起来？

有两种常见方案：

方案 A：事件关联法（related_event_id）

在入库时，将争顶事件的 related_event_id 指向对应传中事件 id，这是最可靠的方式（人工/半自动标注）。

<?php
class CrossAerialStatsService
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某场比赛某支球队的高球传中争顶成功率
     */
    public function calcByMatchTeam(int $matchId, int $teamId): array
    {
        $sql = "
            SELECT
                COUNT(DISTINCT c.id) AS high_cross_total,
                COUNT(DISTINCT d.id) AS aerial_total,
                SUM(CASE WHEN d.outcome = 1 THEN 1 ELSE 0 END) AS aerial_success
            FROM match_events c
            LEFT JOIN match_events d
                ON d.related_event_id = c.id
               AND d.event_type = 'aerial_duel'
            WHERE c.match_id = :match_id
              AND c.team_id = :team_id
              AND c.event_type = 'cross'
              AND c.event_sub_type = 'high'
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId, ':team_id' => $teamId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $total   = (int)($row['aerial_total'] ?? 0);
        $success = (int)($row['aerial_success'] ?? 0);
        return [
            'match_id'         => $matchId,
            'team_id'          => $teamId,
            'high_cross_total' => (int)$row['high_cross_total'],
            'aerial_total'     => $total,
            'aerial_success'   => $success,
            'success_rate'     => $total > 0 ? round($success / $total * 100, 2) : 0.00,
        ];
    }
}

方案 B：时间窗口法（无关联字段时兜底）

如果数据源没有 related_event_id，可用"传中事件后 N 秒内、同场地邻近区域的争顶事件"近似匹配：

public function calcByTimeWindow(int $matchId, int $teamId, int $windowSec = 5): array
{
    $sql = "
        SELECT
            COUNT(*) AS aerial_total,
            SUM(d.outcome) AS aerial_success
        FROM match_events c
        JOIN match_events d
            ON d.match_id = c.match_id
           AND d.event_time BETWEEN c.event_time + 1 AND c.event_time + :window
           AND d.event_type = 'aerial_duel'
        WHERE c.match_id = :match_id
          AND c.team_id = :team_id
          AND c.event_type = 'cross'
          AND c.event_sub_type = 'high'
    ";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        ':match_id' => $matchId,
        ':team_id'  => $teamId,
        ':window'   => $windowSec,
    ]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total   = (int)($row['aerial_total'] ?? 0);
    $success = (int)($row['aerial_success'] ?? 0);
    return [
        'aerial_total'   => $total,
        'aerial_success' => $success,
        'success_rate'   => $total > 0 ? round($success / $total * 100, 2) : 0.00,
    ];
}

⚠️ 推荐：优先使用方案 A（精确），方案 B 仅作为历史数据兜底，方案 B 存在误关联风险（例如传中后另一处发生无关争顶）。

按球员维度统计

public function calcByPlayer(int $matchId, int $teamId): array
{
    $sql = "
        SELECT
            c.player_id,
            p.name AS player_name,
            COUNT(DISTINCT c.id) AS cross_total,
            COUNT(DISTINCT d.id) AS aerial_total,
            SUM(CASE WHEN d.outcome = 1 THEN 1 ELSE 0 END) AS aerial_success
        FROM match_events c
        LEFT JOIN match_events d
            ON d.related_event_id = c.id
           AND d.event_type = 'aerial_duel'
        LEFT JOIN players p ON p.id = c.player_id
        WHERE c.match_id = :match_id
          AND c.team_id = :team_id
          AND c.event_type = 'cross'
          AND c.event_sub_type = 'high'
        GROUP BY c.player_id, p.name
    ";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([':match_id' => $matchId, ':team_id' => $teamId]);
    $result = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $total = (int)$r['aerial_total'];
        $succ  = (int)$r['aerial_success'];
        $result[] = [
            'player_id'      => (int)$r['player_id'],
            'player_name'    => $r['player_name'],
            'cross_total'    => (int)$r['cross_total'],
            'aerial_total'   => $total,
            'aerial_success' => $succ,
            'success_rate'   => $total > 0 ? round($succ / $total * 100, 2) : 0.00,
        ];
    }
    return $result;
}

入库时自动关联事件（关键）

在保存每条争顶事件时，反查最近 5 秒内同场同队的高球传中并建立关联：

public function saveAerialDuel(array $event): void
{
    if ($event['event_type'] !== 'aerial_duel') {
        // 普通入库
        $this->insertEvent($event);
        return;
    }
    $sql = "
        SELECT id FROM match_events
        WHERE match_id = :match_id
          AND team_id = :team_id
          AND event_type = 'cross'
          AND event_sub_type = 'high'
          AND event_time BETWEEN :t1 AND :t2
        ORDER BY event_time DESC LIMIT 1
    ";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        ':match_id' => $event['match_id'],
        ':team_id'  => $event['team_id'],
        ':t1'       => $event['event_time'] - 5,
        ':t2'       => $event['event_time'],
    ]);
    $related = $stmt->fetchColumn();
    $event['related_event_id'] = $related ?: null;
    $this->insertEvent($event);
}

批处理与预聚合

比赛事件数据量大时，建议在赛后或准实时跑一遍聚合，写入 match_cross_stats：

public function rebuildMatchStats(int $matchId): void
{
    $this->pdo->beginTransaction();
    try {
        $this->pdo->prepare("DELETE FROM match_cross_stats WHERE match_id = ?")
                  ->execute([$matchId]);
        $sql = "
            SELECT
                c.team_id,
                c.player_id,
                COUNT(c.id) AS cross_high,
                SUM(CASE WHEN d.id IS NOT NULL THEN 1 ELSE 0 END) AS aerial_total,
                SUM(CASE WHEN d.outcome = 1 THEN 1 ELSE 0 END) AS aerial_success
            FROM match_events c
            LEFT JOIN match_events d
                ON d.related_event_id = c.id
               AND d.event_type = 'aerial_duel'
            WHERE c.match_id = :match_id
              AND c.event_type = 'cross'
              AND c.event_sub_type = 'high'
            GROUP BY c.team_id, c.player_id
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        $ins = $this->pdo->prepare("
            INSERT INTO match_cross_stats
                (match_id, team_id, player_id, cross_high, aerial_total, aerial_success, success_rate)
            VALUES (:m, :t, :p, :ch, :at, :as, :sr)
        ");
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $at = (int)$r['aerial_total'];
            $as = (int)$r['aerial_success'];
            $ins->execute([
                ':m' => $matchId,
                ':t' => $r['team_id'],
                ':p' => $r['player_id'],
                ':ch' => (int)$r['cross_high'],
                ':at' => $at,
                ':as' => $as,
                ':sr' => $at > 0 ? round($as / $at * 100, 2) : 0.00,
            ]);
        }
        $this->pdo->commit();
    } catch (Throwable $e) {
        $this->pdo->rollBack();
        throw $e;
    }
}

易踩的坑

坑 说明 解决
分母定义 传中全部算分母？还是只有"实际发生争顶"的才进分母？ 两种口径都需支持，明确产品定义，推荐仅统计进入争顶的传中作为分母，否则成功率会被严重稀释
重复计数 一次传中引出多次争顶（头球解围再争） 用 COUNT(DISTINCT c.id) 传中数、COUNT(DISTINCT d.id) 争顶数
归属方 争顶事件是进攻方还是防守方记录 通常记录到执行动作的一方；统计"高球传中争顶成功率"看进攻方是否赢得第一落点
传中方式判定 高球/低平球/半高球的判定标准 数据源需明确；或用 x/y 坐标+球速模型推断
零除 分母为 0 $total > 0 ? ... : 0.00
精度 用浮点存储成功率 用 DECIMAL(6,2) 存储；展示层再格式化
索引 大表 join 慢 (match_id, team_id, event_type, event_sub_type) 联合索引 + 预聚合

扩展建议

  1. 多场累计：把 WHERE match_id = ? 换成 WHERE match_id IN (...) 或按联赛/赛季聚合。
  2. 可视化：用 ECharts 在前端展示球队/球员成功率排行。
  3. 对比分析：加入"高球传中次数""平均争顶位置"等衍生指标。
  4. 实时流：用 Redis Streams / Kafka 增量消费事件，实时更新统计,避免每次扫全表。
  5. 口径配置化：把"高球定义""时间窗口""分母口径"做成配置,便于不同产品线使用。

如果需要我针对你的具体数据源（如 Opta / StatsBomb / 自建标注平台）给出字段映射示例,可以补充说明。

上一篇php项目怎么看这场比赛的观赏性？

下一篇综合php项目，历史交锋数据有何规律？

相关文章

抱歉，评论功能暂时关闭!