php项目统计界外球进攻威胁次数？

PHP项目：统计界外球进攻威胁次数

这是一个足球数据分析的需求,下面我给你一个完整的实现方案。

需求拆解

"界外球进攻威胁"需要定义清楚：

• 界外球：throw-in 事件
• 进攻威胁：界外球后 N 秒内（或 N 次传球内）发生的威胁事件，如射门、进球、进入禁区、关键传球等

数据库设计

-- 比赛事件表
CREATE TABLE match_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT,
    event_type VARCHAR(32) NOT NULL,   -- throw_in, pass, shot, goal, cross, corner...
    event_time INT NOT NULL,           -- 秒
    x_coord DECIMAL(5,2),              -- 场地坐标 0-100
    y_coord DECIMAL(5,2),
    end_x DECIMAL(5,2),
    end_y DECIMAL(5,2),
    outcome VARCHAR(16),               -- success / fail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match_time (match_id, event_time),
    INDEX idx_team_type (match_id, team_id, event_type)
);
-- 威胁事件定义表（可配置）
CREATE TABLE threat_rules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_type VARCHAR(32) NOT NULL,
    min_x DECIMAL(5,2),                -- 事件终点 x 坐标阈值
    weight DECIMAL(3,2) DEFAULT 1.0,   -- 威胁权重
    description VARCHAR(128)
);
INSERT INTO threat_rules (event_type, min_x, weight, description) VALUES
('shot',   70, 1.0, '射门'),
('goal',   70, 1.5, '进球'),
('cross',  80, 0.6, '传中'),
('through_ball', 60, 0.8, '直塞');

核心统计逻辑

思路：遍历每支球队的界外球事件，检查其后一个时间窗口内（如 15 秒）是否发生威胁事件。

<?php
class ThrowInThreatAnalyzer
{
    private PDO $pdo;
    private int $windowSeconds;
    private array $threatRules;
    public function __construct(PDO $pdo, int $windowSeconds = 15)
    {
        $this->pdo = $pdo;
        $this->windowSeconds = $windowSeconds;
        $this->threatRules = $this->loadThreatRules();
    }
    private function loadThreatRules(): array
    {
        $stmt = $this->pdo->query("SELECT * FROM threat_rules");
        $rules = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
            $rules[$r['event_type']] = $r;
        }
        return $rules;
    }
    /**
     * 统计一场比赛某队的界外球威胁次数
     */
    public function countForMatch(int $matchId, int $teamId): array
    {
        // 1. 取出该队所有界外球
        $stmt = $this->pdo->prepare(
            "SELECT id, event_time FROM match_events
             WHERE match_id = ? AND team_id = ? AND event_type = 'throw_in'
             ORDER BY event_time"
        );
        $stmt->execute([$matchId, $teamId]);
        $throwIns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (empty($throwIns)) {
            return ['total_throw_ins' => 0, 'threats' => 0, 'threat_throw_ins' => []];
        }
        $threatEvents = $this->fetchThreatEvents($matchId, $teamId);
        $threatThrowIns = [];
        $threatScore = 0.0;
        foreach ($throwIns as $ti) {
            $start = (int)$ti['event_time'];
            $end   = $start + $this->windowSeconds;
            $hits = array_filter($threatEvents, function ($e) use ($start, $end) {
                $t = (int)$e['event_time'];
                return $t > $start && $t <= $end;
            });
            if (!empty($hits)) {
                $weighted = 0.0;
                foreach ($hits as $h) {
                    $rule = $this->threatRules[$h['event_type']] ?? null;
                    if ($rule) $weighted += (float)$rule['weight'];
                }
                $threatThrowIns[] = [
                    'throw_in_id'   => (int)$ti['id'],
                    'time'          => $start,
                    'threat_count'  => count($hits),
                    'weighted_score'=> round($weighted, 2),
                    'events'        => array_values($hits),
                ];
                $threatScore += $weighted;
            }
        }
        return [
            'match_id'        => $matchId,
            'team_id'         => $teamId,
            'total_throw_ins' => count($throwIns),
            'threats'         => count($threatThrowIns),
            'threat_rate'     => round(count($threatThrowIns) / count($throwIns), 3),
            'threat_score'    => round($threatScore, 2),
            'threat_throw_ins'=> $threatThrowIns,
        ];
    }
    private function fetchThreatEvents(int $matchId, int $teamId): array
    {
        $types = array_keys($this->threatRules);
        if (empty($types)) return [];
        $in = implode(',', array_fill(0, count($types), '?'));
        $sql = "SELECT id, event_type, event_time, end_x, end_y
                FROM match_events
                WHERE match_id = ? AND team_id = ?
                  AND event_type IN ($in)
                  AND (end_x IS NULL OR end_x >= ?)
                ORDER BY event_time";
        $stmt = $this->pdo->prepare($sql);
        // 只取规则中最低的 x 阈值做粗过滤
        $minX = min(array_column($this->threatRules, 'min_x'));
        $stmt->execute(array_merge([$matchId, $teamId], $types, [$minX]));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 批量统计整个赛季某队
     */
    public function countForTeamSeason(int $teamId, int $seasonId): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id FROM matches WHERE season_id = ? AND (home_team_id = ? OR away_team_id = ?)"
        );
        $stmt->execute([$seasonId, $teamId, $teamId]);
        $matchIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
        $result = ['total_throw_ins'=>0, 'threats'=>0, 'threat_score'=>0.0, 'matches'=>[]];
        foreach ($matchIds as $mid) {
            $r = $this->countForMatch((int)$mid, $teamId);
            $result['total_throw_ins'] += $r['total_throw_ins'];
            $result['threats']         += $r['threats'];
            $result['threat_score']    += $r['threat_score'];
            $result['matches'][] = $r;
        }
        $result['threat_score'] = round($result['threat_score'], 2);
        $result['threat_rate']  = $result['total_throw_ins']
            ? round($result['threats'] / $result['total_throw_ins'], 3) : 0;
        return $result;
    }
}

调用示例

$pdo = new PDO('mysql:host=127.0.0.1;dbname=football;charset=utf8mb4', 'user', 'pass');
$analyzer = new ThrowInThreatAnalyzer($pdo, windowSeconds: 15);
// 单场
$r = $analyzer->countForMatch(matchId: 1001, teamId: 5);
print_r($r);
// 赛季累计
$season = $analyzer->countForTeamSeason(teamId: 5, seasonId: 2024);
echo "界外球总数: {$season['total_throw_ins']}\n";
echo "威胁界外球: {$season['threats']}\n";
echo "威胁率: {$season['threat_rate']}\n";
echo "加权威胁分: {$season['threat_score']}\n";

性能优化建议

SQL 版一次性方案（推荐大数据量）

SELECT 
    t.id AS throw_in_id,
    t.event_time,
    COUNT(e.id) AS threat_count
FROM match_events t
LEFT JOIN match_events e
    ON  e.match_id = t.match_id
    AND e.team_id  = t.team_id
    AND e.event_type IN ('shot','goal','cross','through_ball')
    AND e.event_time > t.event_time
    AND e.event_time <= t.event_time + 15
WHERE t.match_id = :mid
  AND t.team_id  = :tid
  AND t.event_type = 'throw_in'
GROUP BY t.id, t.event_time
HAVING threat_count > 0;

需要你确认的关键点

1. 威胁定义：是"射门/进球"算威胁，还是也要包含"进入前场 30 米区域"？
2. 时间窗口：15 秒是否合适？（一般 10–20 秒，界外球直接掷入禁区可单独处理）
3. 半场边界处理：是否需要防止跨半场 / 跨加时？
4. 数据来源：自有采集、第三方 API（StatsBomb / Opta / 雷速）？字段名需对齐。
5. "进攻威胁"是否要求同一控球阶段：更严谨的做法是追踪 possession 链，而非固定时间窗口。

告诉我你的实际字段结构和威胁定义,我可以把上面的代码改成直接可用的版本。