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";
性能优化建议
| 场景 | 优化方案 |
|---|---|
| 大量比赛批量统计 | 用一条 JOIN 查询一次性拉出所有事件的"下一次威胁事件"，避免 N+1 |
| 高频查询 | 把结果预计算写入 throw_in_threat_stats 汇总表，赛后可异步计算 |
| 事件时间跨半场 | 注意半场间存在 15 分钟间隔，需按 half 字段或 period 分段，避免跨半场误判 |
| 事件粒度 | 若数据源提供 xG（预期进球），直接用 SUM(xG) 替代权重更准确 |
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;
需要你确认的关键点
- 威胁定义：是"射门/进球"算威胁，还是也要包含"进入前场 30 米区域"？
- 时间窗口：15 秒是否合适？（一般 10–20 秒，界外球直接掷入禁区可单独处理）
- 半场边界处理：是否需要防止跨半场 / 跨加时？
- 数据来源：自有采集、第三方 API（StatsBomb / Opta / 雷速）？字段名需对齐。
- "进攻威胁"是否要求同一控球阶段：更严谨的做法是追踪 possession 链，而非固定时间窗口。
告诉我你的实际字段结构和威胁定义,我可以把上面的代码改成直接可用的版本。
