PHP项目:伤病停赛影响数据对比系统
下面给你一个完整的实现思路和代码示例,用于统计和对比球队/球员因伤病停赛造成的影响数据。

需求分析
需要对比的核心指标:
| 维度 | 指标 |
|---|---|
| 球队层面 | 缺阵场次、缺阵球员数、胜率变化、进失球变化 |
| 球员层面 | 缺阵场次、球队在其缺阵时的胜率、场均数据变化 |
| 时间对比 | 有伤病期 vs 无伤病期 |
数据库设计
-- 球队表
CREATE TABLE teams (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
-- 球员表
CREATE TABLE players (
id INT PRIMARY KEY AUTO_INCREMENT,
team_id INT NOT NULL,
name VARCHAR(50) NOT NULL,
position VARCHAR(20),
is_core TINYINT DEFAULT 0 COMMENT '是否核心球员'
);
-- 伤病停赛记录
CREATE TABLE injuries (
id INT PRIMARY KEY AUTO_INCREMENT,
player_id INT NOT NULL,
team_id INT NOT NULL,
type ENUM('injury','suspension') DEFAULT 'injury',
reason VARCHAR(100),
start_date DATE NOT NULL,
end_date DATE,
missed_matches INT DEFAULT 0,
INDEX idx_team_date (team_id, start_date, end_date)
);
-- 比赛结果
CREATE TABLE matches (
id INT PRIMARY KEY AUTO_INCREMENT,
team_id INT NOT NULL,
match_date DATE NOT NULL,
opponent VARCHAR(50),
goals_for INT DEFAULT 0,
goals_against INT DEFAULT 0,
result ENUM('win','draw','loss'),
INDEX idx_team_date (team_id, match_date)
);
核心统计逻辑(PHP)
<?php
class InjuryImpactAnalyzer
{
private PDO $db;
public function __construct(PDO $db)
{
$this->db = $db;
}
/**
* 对比球队有伤病期 vs 无伤病期的表现
*/
public function compareTeamImpact(int $teamId, string $from, string $to): array
{
// 1. 获取所有比赛
$stmt = $this->db->prepare(
"SELECT * FROM matches WHERE team_id = ? AND match_date BETWEEN ? AND ? ORDER BY match_date"
);
$stmt->execute([$teamId, $from, $to]);
$matches = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 2. 获取伤病区间列表
$stmt = $this->db->prepare(
"SELECT start_date, end_date FROM injuries
WHERE team_id = ? AND start_date <= ? AND (end_date >= ? OR end_date IS NULL)"
);
$stmt->execute([$teamId, $to, $from]);
$injuryRanges = $stmt->fetchAll(PDO::FETCH_ASSOC);
$withInjury = [];
$withoutInjury = [];
foreach ($matches as $m) {
$date = $m['match_date'];
$affected = false;
foreach ($injuryRanges as $r) {
$end = $r['end_date'] ?? '9999-12-31';
if ($date >= $r['start_date'] && $date <= $end) {
$affected = true;
break;
}
}
if ($affected) {
$withInjury[] = $m;
} else {
$withoutInjury[] = $m;
}
}
return [
'with_injury' => $this->calcStats($withInjury),
'without_injury' => $this->calcStats($withoutInjury),
'diff' => $this->calcDiff($withInjury, $withoutInjury),
];
}
/**
* 计算一组比赛的统计
*/
private function calcStats(array $matches): array
{
$total = count($matches);
if ($total === 0) {
return ['total'=>0,'win'=>0,'draw'=>0,'loss'=>0,'win_rate'=>0,
'avg_goals_for'=>0,'avg_goals_against'=>0,'points_per_match'=>0];
}
$win = $draw = $loss = $gf = $ga = 0;
foreach ($matches as $m) {
$gf += $m['goals_for'];
$ga += $m['goals_against'];
if ($m['result'] === 'win') $win++;
elseif ($m['result'] === 'draw') $draw++;
else $loss++;
}
return [
'total' => $total,
'win' => $win,
'draw' => $draw,
'loss' => $loss,
'win_rate' => round($win / $total * 100, 2),
'avg_goals_for' => round($gf / $total, 2),
'avg_goals_against' => round($ga / $total, 2),
'points_per_match' => round(($win * 3 + $draw) / $total, 2),
];
}
/**
* 计算差异百分比
*/
private function calcDiff(array $a, array $b): array
{
$statsA = $this->calcStats($a);
$statsB = $this->calcStats($b);
$diff = [];
foreach (['win_rate','avg_goals_for','avg_goals_against','points_per_match'] as $k) {
$base = $statsB[$k] ?: 0.01;
$diff[$k] = [
'with' => $statsA[$k],
'without' => $statsB[$k],
'change' => round($statsA[$k] - $statsB[$k], 2),
'pct' => round(($statsA[$k] - $statsB[$k]) / $base * 100, 2) . '%',
];
}
return $diff;
}
/**
* 按球员统计:该球员缺阵时球队表现
*/
public function playerImpact(int $playerId): array
{
// 获取球员所有缺阵区间
$stmt = $this->db->prepare(
"SELECT start_date, end_date FROM injuries WHERE player_id = ?"
);
$stmt->execute([$playerId]);
$ranges = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $this->db->prepare("SELECT team_id FROM players WHERE id = ?");
$stmt->execute([$playerId]);
$teamId = $stmt->fetchColumn();
// 获取球队全部比赛
$stmt = $this->db->prepare("SELECT * FROM matches WHERE team_id = ? ORDER BY match_date");
$stmt->execute([$teamId]);
$matches = $stmt->fetchAll(PDO::FETCH_ASSOC);
$out = $in = [];
foreach ($matches as $m) {
$affected = false;
foreach ($ranges as $r) {
$end = $r['end_date'] ?? '9999-12-31';
if ($m['match_date'] >= $r['start_date'] && $m['match_date'] <= $end) {
$affected = true;
break;
}
}
if ($affected) $out[] = $m;
else $in[] = $m;
}
return [
'player_out' => $this->calcStats($out), // 球员缺阵时
'player_in' => $this->calcStats($in), // 球员在阵时
'diff' => $this->calcDiff($in, $out),
];
}
}
使用示例
$db = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$analyzer = new InjuryImpactAnalyzer($db);
// 球队整体影响对比(近一个赛季)
$teamImpact = $analyzer->compareTeamImpact(1, '2024-08-01', '2025-05-31');
print_r($teamImpact);
// 单个球员缺阵影响
$playerImpact = $analyzer->playerImpact(10);
print_r($playerImpact);
输出示例:
[with_injury]
total: 8, win_rate: 37.5%, points_per_match: 1.25
[without_injury]
total: 22, win_rate: 63.6%, points_per_match: 2.09
[diff]
win_rate: -26.1% ← 缺阵期胜率下降
进阶建议
- 多维度对比:按主客场、对手强弱分层统计,避免混淆变量。
- 控制变量:只对比"同对手等级"的比赛,用 FIFA/ELO 评分分组。
- 权重计算:核心球员 vs 替补球员赋予不同权重。
- 可视化前端:用 ECharts 展示对比柱状图、雷达图。
- 缓存优化:统计结果用 Redis 缓存(T+1 更新),避免每次实时计算。
- 赛事类型区分:联赛/杯赛/欧战分别统计。
常见坑
- 伤病区间重叠:同一时间多人受伤,需要判断"受影响比赛"避免重复。
- 无结束日期:长期伤病
end_date为 NULL,需按"当前"处理。 - 转会/换队:球员跨队伤病需按
team_id分别计算。 - 比赛日期边界:伤病"结束日期"通常可出战,判断用
<=需注意开闭区间。
需要我帮你补充前端可视化方案、Redis 缓存层,还是按对手强弱分层的统计逻辑?
友情链接
版权声明:本站文章为原创整理,转载请注明出处。