php项目统计犯规战术阻止反击几次？

PHP项目统计犯规战术阻止反击次数

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

需求分析

"犯规战术阻止反击" 的判定条件通常包括：

1. 发生犯规（foul）
2. 发生在对方由守转攻的反击阶段（fast break / counter attack）
3. 犯规位置/时机阻止了明显的进攻机会

数据表设计

-- 比赛事件表
CREATE TABLE match_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,           -- 犯规方
    player_id INT NOT NULL,
    event_type VARCHAR(20),         -- 'foul', 'card', 'shot' 等
    event_time INT,                 -- 比赛分钟
    zone VARCHAR(10),               -- 'own_half', 'midfield', 'opp_half'
    is_counter_attack TINYINT(1) DEFAULT 0,  -- 是否处于反击阶段
    counter_speed DECIMAL(4,1),     -- 反击速度(米/秒),可选
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match (match_id),
    INDEX idx_team (team_id)
);
-- 或使用战术标记表更灵活
CREATE TABLE tactical_fouls (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_id INT NOT NULL,          -- 关联 match_events.id
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    reason VARCHAR(50),             -- 'stop_counter', 'tactical', 'last_man'
    counter_attack_id INT,          -- 关联被中断的反击ID
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

核心统计代码

方式1：基于事件表直接统计

<?php
class TacticalFoulStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某场比赛某队"犯规阻止反击"次数
     */
    public function countByMatch(int $matchId, int $teamId): int
    {
        $sql = "SELECT COUNT(*) 
                FROM match_events 
                WHERE match_id = :match_id 
                  AND team_id = :team_id 
                  AND event_type = 'foul' 
                  AND is_counter_attack = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId, ':team_id' => $teamId]);
        return (int) $stmt->fetchColumn();
    }
    /**
     * 按球队汇总整个赛季
     */
    public function seasonSummary(int $seasonId): array
    {
        $sql = "SELECT t.id, t.name, COUNT(e.id) AS stop_counter_count
                FROM teams t
                LEFT JOIN match_events e 
                       ON e.team_id = t.id 
                      AND e.event_type = 'foul' 
                      AND e.is_counter_attack = 1
                LEFT JOIN matches m ON m.id = e.match_id AND m.season_id = :season
                WHERE m.season_id = :season OR e.id IS NULL
                GROUP BY t.id, t.name
                ORDER BY stop_counter_count DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':season' => $seasonId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 按球员统计（谁是"战术犯规专家"）
     */
    public function topPlayers(int $seasonId, int $limit = 10): array
    {
        $sql = "SELECT p.id, p.name, t.name AS team_name, COUNT(*) AS fouls
                FROM match_events e
                JOIN players p ON p.id = e.player_id
                JOIN teams t ON t.id = e.team_id
                JOIN matches m ON m.id = e.match_id
                WHERE m.season_id = :season
                  AND e.event_type = 'foul'
                  AND e.is_counter_attack = 1
                GROUP BY p.id, p.name, t.name
                ORDER BY fouls DESC
                LIMIT :limit";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':season', $seasonId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

方式2：从原始事件流自动识别（更智能）

如果原始数据没有 is_counter_attack 标记，可以基于时间窗口 + 位置变化推断：

<?php
class CounterAttackDetector
{
    /** 反击判定：丢球后 N 秒内，且推进速度超过阈值 */
    private const COUNTER_WINDOW_SEC = 15;
    private const PROGRESS_THRESHOLD_M = 30;
    /**
     * 检测反击中的犯规
     * @param array $events 按时间排序的事件
     */
    public function detect(array $events): array
    {
        $result = [];
        $lastPossessionLoss = null;  // ['time'=>, 'x'=>, 'team'=>]
        foreach ($events as $i => $e) {
            // 记录控球转换点
            if ($e['event_type'] === 'possession_change') {
                $lastPossessionLoss = $e;
                continue;
            }
            if ($e['event_type'] !== 'foul' || !$lastPossessionLoss) {
                continue;
            }
            $dt = $e['event_time'] - $lastPossessionLoss['event_time'];
            $progress = $e['pitch_x'] - $lastPossessionLoss['pitch_x'];
            if ($dt <= self::COUNTER_WINDOW_SEC 
                && $progress >= self::PROGRESS_THRESHOLD_M
                && $e['team_id'] === $lastPossessionLoss['team_id']) {  // 犯规方=原控球方
                $result[] = [
                    'foul_event_id' => $e['id'],
                    'counter_start' => $lastPossessionLoss['event_time'],
                    'seconds_elapsed' => $dt,
                    'meters_progressed' => $progress,
                ];
            }
        }
        return $result;
    }
}

输出示例

$stats = new TacticalFoulStats($pdo);
// 单场统计
$count = $stats->countByMatch(matchId: 1001, teamId: 5);
echo "本场比赛犯规阻止反击次数: {$count}\n";
// 赛季汇总
foreach ($stats->seasonSummary(seasonId: 2024) as $row) {
    printf("%-20s %d 次\n", $row['name'], $row['stop_counter_count']);
}
// 输出：
// 曼城                 12 次
// 利物浦               10 次
// 阿森纳                8 次

关键要点提醒

如果你能告诉我数据来源格式（是已有 is_counter_attack 字段，还是要从原始事件流推断），我可以帮你把代码调得更贴合实际。