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

wen PHP项目 2026-10-23 3

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

需求拆解

"高球传中争顶成功率"是一个足球数据指标,需要拆成几个子概念:

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

概念	定义
高球传中	从边路/肋部起高球送入禁区(cross 类型)
争顶事件	传中落点附近发生的空中对抗(aerial duel)
争顶成功	争顶球权归属本方(进攻方)
成功率	争顶成功次数 / 争顶总次数 × 100%

数据模型设计

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team_id INT,
    away_team_id INT,
    match_time DATETIME
);
-- 事件表(核心)
CREATE TABLE match_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT,
    event_type VARCHAR(32) NOT NULL,   -- 'cross' | 'aerial_duel' | 'shot' ...
    event_subtype VARCHAR(32),         -- 'high_cross' / 'won' / 'lost'
    minute INT,
    x DECIMAL(6,2),                    -- 场地坐标,用于判定落点是否在禁区
    y DECIMAL(6,2),
    related_event_id BIGINT,           -- 关联的传中事件(争顶由哪次传中引发)
    success TINYINT DEFAULT NULL,      -- 争顶是否成功
    INDEX idx_match (match_id),
    INDEX idx_type (event_type),
    INDEX idx_related (related_event_id)
);

关键点:争顶事件必须通过 related_event_id 关联到触发它的那次传中,否则无法统计"传中→争顶"的转化。

核心统计 SQL

SELECT
    e.team_id,
    COUNT(DISTINCT d.id) AS total_duels,
    SUM(d.success)        AS won_duels,
    ROUND(SUM(d.success) / COUNT(DISTINCT d.id) * 100, 2) AS success_rate
FROM match_events e
JOIN match_events d
      ON d.related_event_id = e.id
     AND d.event_type = 'aerial_duel'
WHERE e.event_type  = 'cross'
  AND e.event_subtype = 'high_cross'
  AND e.match_id = ?
GROUP BY e.team_id;

PHP 服务层实现

<?php
class CrossDuelStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo) { $this->pdo = $pdo; }
    /**
     * 统计某队在某场比赛/赛季的高球传中争顶成功率
     *
     * @param int      $teamId
     * @param int|null $matchId  传 null 则统计整个赛季
     * @param string   $season
     */
    public function getStats(int $teamId, ?int $matchId = null, string $season = '2024'): array
    {
        $sql = "
            SELECT
                COUNT(DISTINCT d.id) AS total,
                COALESCE(SUM(d.success), 0) AS won
            FROM match_events e
            JOIN matches m ON m.id = e.match_id
            JOIN match_events d
                 ON d.related_event_id = e.id
                AND d.event_type = 'aerial_duel'
            WHERE e.event_type = 'cross'
              AND e.event_subtype = 'high_cross'
              AND e.team_id = :team
              AND m.season = :season
        ";
        $params = [':team' => $teamId, ':season' => $season];
        if ($matchId !== null) {
            $sql .= " AND e.match_id = :match";
            $params[':match'] = $matchId;
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $total = (int)$row['total'];
        $won   = (int)$row['won'];
        return [
            'team_id'      => $teamId,
            'total_duels'  => $total,
            'won_duels'    => $won,
            'success_rate' => $total > 0 ? round($won / $total * 100, 2) : 0.0,
        ];
    }
}

常见坑与优化

一次传中可能引发多次争顶 —— 用 COUNT(DISTINCT d.id) 而不是 COUNT(*),防止 JOIN 膨胀;或者先按传中分组取首次争顶。
二次进攻传中 —— 建议用"进攻回合"概念(进攻开始到结束)聚合,避免重复统计。
判定"高球" —— 若事件表没有 subtype,可用 cross_height > 1.8m 或落点在禁区内(x>84, y∈[18,50])作为过滤条件。
成功率口径 —— 分母是"有争顶发生的传中数"还是"所有高球传中数"要明确,前者衡量质量,后者衡量威胁，两种都存:
```
'cross_conversion' => $total > 0 ? round($total / $allHighCross, 2) : 0, // 传中→争顶转化率
```
性能 —— 赛季级别统计建议建物化视图或缓存表,related_event_id 必备索引。