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

wen PHP项目 2026-10-16 4

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

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

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

需求拆解

"犯规战术阻止反击"通常指：对方形成快攻/反击时，防守方通过犯规中断进攻,需要统计的数据要素：

字段	说明
match_id	比赛ID
team_id	犯规方球队
player_id	犯规球员
minute	发生时间
is_counter_attack	是否在对方反击中
counter_type	反击类型（快攻/反击）
zone	区域（前场/中场/后场）

数据库设计

CREATE TABLE `fouls` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `match_id` INT UNSIGNED NOT NULL,
  `team_id` INT UNSIGNED NOT NULL,
  `player_id` INT UNSIGNED NOT NULL,
  `minute` TINYINT UNSIGNED NOT NULL,
  `is_counter_attack` TINYINT(1) DEFAULT 0,  -- 1=反击中犯规
  `counter_type` ENUM('fast_break','counter') DEFAULT NULL,
  `zone` ENUM('own_half','midfield','opp_half') DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_match` (`match_id`),
  INDEX `idx_team` (`match_id`, `team_id`),
  INDEX `idx_counter` (`match_id`, `is_counter_attack`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心统计逻辑

统计某场比赛各队"战术犯规阻止反击"次数

<?php
class FoulTacticStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某场比赛各队的战术犯规阻止反击次数
     */
    public function getCounterFoulsByMatch(int $matchId): array
    {
        $sql = "SELECT 
                    t.id AS team_id,
                    t.name AS team_name,
                    COUNT(f.id) AS counter_foul_count,
                    SUM(CASE WHEN f.zone = 'own_half' THEN 1 ELSE 0 END) AS own_half_count,
                    SUM(CASE WHEN f.zone = 'midfield' THEN 1 ELSE 0 END) AS midfield_count,
                    SUM(CASE WHEN f.zone = 'opp_half' THEN 1 ELSE 0 END) AS opp_half_count
                FROM teams t
                LEFT JOIN fouls f 
                    ON f.team_id = t.id 
                    AND f.match_id = :match_id
                    AND f.is_counter_attack = 1
                WHERE t.id IN (
                    SELECT team_id FROM match_teams WHERE match_id = :match_id
                )
                GROUP BY t.id, t.name";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['match_id' => $matchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 统计某队某赛季的战术犯规阻止反击次数
     */
    public function getSeasonStats(int $teamId, int $seasonId): int
    {
        $sql = "SELECT COUNT(*) FROM fouls f
                JOIN matches m ON m.id = f.match_id
                WHERE f.team_id = :team_id
                  AND m.season_id = :season_id
                  AND f.is_counter_attack = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['team_id' => $teamId, 'season_id' => $seasonId]);
        return (int) $stmt->fetchColumn();
    }
    /**
     * 统计某球员的战术犯规阻止反击次数（含时间分布）
     */
    public function getPlayerStats(int $playerId): array
    {
        $sql = "SELECT 
                    COUNT(*) AS total,
                    SUM(CASE WHEN minute <= 15 THEN 1 ELSE 0 END) AS m0_15,
                    SUM(CASE WHEN minute BETWEEN 16 AND 30 THEN 1 ELSE 0 END) AS m16_30,
                    SUM(CASE WHEN minute BETWEEN 31 AND 45 THEN 1 ELSE 0 END) AS m31_45,
                    SUM(CASE WHEN minute BETWEEN 46 AND 60 THEN 1 ELSE 0 END) AS m46_60,
                    SUM(CASE WHEN minute BETWEEN 61 AND 75 THEN 1 ELSE 0 END) AS m61_75,
                    SUM(CASE WHEN minute > 75 THEN 1 ELSE 0 END) AS m76_90
                FROM fouls
                WHERE player_id = :player_id AND is_counter_attack = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['player_id' => $playerId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}

使用示例

$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'user', 'pass');
$stats = new FoulTacticStats($pdo);
// 某场比赛
$result = $stats->getCounterFoulsByMatch(1001);
/*
[
  ['team_id'=>1,'team_name'=>'A队','counter_foul_count'=>5,'own_half_count'=>3,...],
  ['team_id'=>2,'team_name'=>'B队','counter_foul_count'=>2,'own_half_count'=>1,...]
]
*/
// 某球员整个赛季
$playerStats = $stats->getPlayerStats(88);
echo "本赛季阻止反击犯规：{$playerStats['total']} 次";

关键判断：如何标记"阻止反击"

在录入数据时（或从事件流自动识别），需要判定一次犯规是否属于"阻止反击",参考规则：

function isCounterAttackFoul(array $event, array $prevEvents): bool
{
    // 1. 犯规方为非控球方（对方正在推进）
    if ($event['possession_team'] === $event['foul_team']) {
        return false;
    }
    // 2. 犯规发生在对方快速推进中（最近N秒内或最近X次传球内）
    $recentProgress = $event['progress_rate'] ?? 0;  // 推进速度
    if ($recentProgress < 1.2) {  // 阈值可调
        return false;
    }
    // 3. 反击方人数优势或空间优势
    $attackers = $event['attackers_ahead'] ?? 0;
    $defenders = $event['defenders_behind'] ?? 0;
    if ($attackers <= $defenders) {
        return false;
    }
    return true;
}

进阶统计维度

维度	SQL 思路
场均次数	`COUNT(*) / COUNT(DISTINCT match_id)`
犯规球员排名	`GROUP BY player_id ORDER BY COUNT(*) DESC`
主客场区分	关联 `matches.home_team_id` 判断
得失分关联	关联失球表看犯规后是否被罚点球/破门
比赛阶段	用 `minute` 分档统计（体能下降期犯规多）

场均统计示例

public function getAvgPerMatch(int $teamId, int $seasonId): float
{
    $sql = "SELECT 
                COUNT(*) / COUNT(DISTINCT f.match_id) AS avg_per_match
            FROM fouls f
            JOIN matches m ON m.id = f.match_id
            WHERE f.team_id = :team_id 
              AND m.season_id = :season_id 
              AND f.is_counter_attack = 1";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute(['team_id' => $teamId, 'season_id' => $seasonId]);
    return (float) $stmt->fetchColumn();
}