本文目录导读：
我来帮你设计一个PHP项目统计伤病停赛影响数据对比的系统,这个系统可以帮助分析伤病对球队成绩的影响。
系统架构设计
数据库表结构
-- 球员表
CREATE TABLE players (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
team_id INT,
status ENUM('active', 'injured', 'suspended') DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 伤病记录表
CREATE TABLE injuries (
id INT PRIMARY KEY AUTO_INCREMENT,
player_id INT,
injury_type VARCHAR(100),
injury_date DATE,
recovery_date DATE,
severity ENUM('minor', 'moderate', 'severe'),
matches_missed INT DEFAULT 0,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (player_id) REFERENCES players(id)
);
-- 比赛数据表
CREATE TABLE matches (
id INT PRIMARY KEY AUTO_INCREMENT,
match_date DATE,
team_id INT,
opponent_id INT,
goals_for INT,
goals_against INT,
result ENUM('win', 'draw', 'loss'),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 球队表
CREATE TABLE teams (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
league VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
PHP核心统计类
<?php
class InjuryImpactAnalyzer {
private $db;
public function __construct($dbConnection) {
$this->db = $dbConnection;
}
/**
* 获取球队伤病统计
*/
public function getTeamInjuryStats($teamId, $startDate, $endDate) {
$query = "SELECT
COUNT(DISTINCT i.player_id) as total_injured_players,
COUNT(i.id) as total_injury_records,
SUM(i.matches_missed) as total_matches_missed,
AVG(i.matches_missed) as avg_matches_missed_per_injury,
(SELECT COUNT(*) FROM players
WHERE team_id = ? AND status = 'injured'
AND created_at BETWEEN ? AND ?) as currently_injured
FROM injuries i
INNER JOIN players p ON i.player_id = p.id
WHERE p.team_id = ?
AND i.injury_date BETWEEN ? AND ?";
$stmt = $this->db->prepare($query);
$stmt->bind_param("ississ", $teamId, $startDate, $endDate, $teamId, $startDate, $endDate);
$stmt->execute();
return $stmt->get_result()->fetch_assoc();
}
/**
* 对比伤病期间与健康期间的比赛成绩
*/
public function comparePerformanceWithInjuries($teamId, $seasonStart, $seasonEnd) {
// 获取所有比赛
$matches = $this->getMatchesWithInjuryInfo($teamId, $seasonStart, $seasonEnd);
$stats = [
'with_injuries' => [
'matches' => 0,
'wins' => 0,
'draws' => 0,
'losses' => 0,
'goals_scored' => 0,
'goals_conceded' => 0,
'win_rate' => 0
],
'without_injuries' => [
'matches' => 0,
'wins' => 0,
'draws' => 0,
'losses' => 0,
'goals_scored' => 0,
'goals_conceded' => 0,
'win_rate' => 0
]
];
foreach ($matches as $match) {
$hasInjury = $match['injured_players'] > 0;
$key = $hasInjury ? 'with_injuries' : 'without_injuries';
$stats[$key]['matches']++;
$stats[$key]['wins'] += ($match['result'] == 'win') ? 1 : 0;
$stats[$key]['draws'] += ($match['result'] == 'draw') ? 1 : 0;
$stats[$key]['losses'] += ($match['result'] == 'loss') ? 1 : 0;
$stats[$key]['goals_scored'] += $match['goals_for'];
$stats[$key]['goals_conceded'] += $match['goals_against'];
}
// 计算胜率
foreach ($stats as $key => $value) {
if ($value['matches'] > 0) {
$stats[$key]['win_rate'] = round(($value['wins'] / $value['matches']) * 100, 2);
$stats[$key]['avg_goals_scored'] = round($value['goals_scored'] / $value['matches'], 2);
$stats[$key]['avg_goals_conceded'] = round($value['goals_conceded'] / $value['matches'], 2);
}
}
// 计算差异
$stats['difference'] = [
'win_rate_diff' => round($stats['with_injuries']['win_rate'] - $stats['without_injuries']['win_rate'], 2),
'goals_diff' => round($stats['with_injuries']['avg_goals_scored'] - $stats['without_injuries']['avg_goals_scored'], 2)
];
return $stats;
}
/**
* 获取球员伤病影响排行
*/
public function getPlayerInjuryImpactRanking($teamId, $limit = 10) {
$query = "SELECT
p.name,
p.position,
COUNT(i.id) as injury_count,
SUM(i.matches_missed) as total_matches_missed,
AVG(i.matches_missed) as avg_matches_missed,
MAX(i.injury_date) as last_injury_date,
CASE
WHEN SUM(i.matches_missed) > 20 THEN '高'
WHEN SUM(i.matches_missed) > 10 THEN '中'
ELSE '低'
END as impact_level,
GROUP_CONCAT(DISTINCT i.injury_type SEPARATOR ', ') as injury_types
FROM players p
LEFT JOIN injuries i ON p.id = i.player_id
WHERE p.team_id = ?
GROUP BY p.id, p.name, p.position
ORDER BY total_matches_missed DESC
LIMIT ?";
$stmt = $this->db->prepare($query);
$stmt->bind_param("ii", $teamId, $limit);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
/**
* 获取比赛伤病信息
*/
private function getMatchesWithInjuryInfo($teamId, $startDate, $endDate) {
$query = "SELECT
m.*,
(SELECT COUNT(DISTINCT i.player_id)
FROM injuries i
WHERE i.player_id IN (SELECT id FROM players WHERE team_id = m.team_id)
AND i.injury_date <= m.match_date
AND (i.recovery_date >= m.match_date OR i.recovery_date IS NULL)
) as injured_players
FROM matches m
WHERE m.team_id = ?
AND m.match_date BETWEEN ? AND ?
ORDER BY m.match_date DESC";
$stmt = $this->db->prepare($query);
$stmt->bind_param("iss", $teamId, $startDate, $endDate);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
/**
* 生成对比报告
*/
public function generateComparisonReport($teamId, $periods) {
$report = [];
foreach ($periods as $periodName => $dates) {
$stats = $this->comparePerformanceWithInjuries(
$teamId,
$dates['start'],
$dates['end']
);
$report[$periodName] = [
'team_stats' => $stats,
'injury_stats' => $this->getTeamInjuryStats(
$teamId,
$dates['start'],
$dates['end']
)
];
}
return $report;
}
/**
* 计算伤病对成绩的具体影响
*/
public function calculateInjuryEffect($teamId, $seasonStart, $seasonEnd) {
$stats = $this->comparePerformanceWithInjuries($teamId, $seasonStart, $seasonEnd);
// 计算影响百分比
$impact = [
'points_per_match' => [
'with_injuries' => $this->calculatePointsPerMatch($stats['with_injuries']),
'without_injuries' => $this->calculatePointsPerMatch($stats['without_injuries'])
],
'goals_impact' => [
'scoring' => $stats['difference']['goals_diff'],
'conceding' => $stats['without_injuries']['avg_goals_conceded'] -
$stats['with_injuries']['avg_goals_conceded']
]
];
// 计算位置影响
$impact['position_impact'] = $this->getPositionImpact($teamId, $seasonStart, $seasonEnd);
return $impact;
}
/**
* 获取位置伤病影响
*/
private function getPositionImpact($teamId, $startDate, $endDate) {
$query = "SELECT
p.position,
COUNT(i.id) as injury_count,
SUM(i.matches_missed) as matches_missed,
AVG(m.goals_for) as avg_goals_when_injured
FROM players p
LEFT JOIN injuries i ON p.id = i.player_id
LEFT JOIN matches m ON m.team_id = p.team_id
WHERE p.team_id = ?
AND i.injury_date BETWEEN ? AND ?
GROUP BY p.position
ORDER BY matches_missed DESC";
$stmt = $this->db->prepare($query);
$stmt->bind_param("iss", $teamId, $startDate, $endDate);
$stmt->execute();
return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
}
private function calculatePointsPerMatch($stats) {
if ($stats['matches'] == 0) return 0;
return round(($stats['wins'] * 3 + $stats['draws']) / $stats['matches'], 2);
}
}
控制器和视图示例
<?php
// controller/InjuryController.php
class InjuryController {
private $analyzer;
public function __construct($db) {
$this->analyzer = new InjuryImpactAnalyzer($db);
}
public function compareAction($teamId) {
// 获取两个时期进行对比
$periods = [
'first_half' => ['start' => '2024-01-01', 'end' => '2024-06-30'],
'second_half' => ['start' => '2024-07-01', 'end' => '2024-12-31']
];
$report = $this->analyzer->generateComparisonReport($teamId, $periods);
$ranking = $this->analyzer->getPlayerInjuryImpactRanking($teamId);
// 获取统计数据用于视图
$viewData = [
'report' => $report,
'ranking' => $ranking,
'teamId' => $teamId
];
return $viewData;
}
}
?>
前端展示模板
<!-- views/injury_comparison.php -->
<!DOCTYPE html>
<html>
<head>伤病影响统计对比</title>
<style>
.container { max-width: 1200px; margin: 0 auto; padding: 20px; }
.comparison-table { width: 100%; border-collapse: collapse; margin: 20px 0; }
.comparison-table th, .comparison-table td { padding: 10px; border: 1px solid #ddd; }
.comparison-table th { background: #f5f5f5; }
.highlight { color: #e74c3c; font-weight: bold; }
.impact-badge { padding: 3px 8px; border-radius: 3px; }
.impact-high { background: #f8d7da; color: #721c24; }
.impact-medium { background: #fff3cd; color: #856404; }
.impact-low { background: #d4edda; color: #155724; }
</style>
</head>
<body>
<div class="container">
<h1>伤病停赛影响数据对比系统</h1>
<?php if (!empty($report)): ?>
<?php foreach ($report as $periodName => $data): ?>
<h2><?php echo $periodName; ?></h2>
<!-- 伤病统计 -->
<div class="injury-stats">
<h3>伤病概况</h3>
<p>受伤球员数: <?php echo $data['injury_stats']['total_injured_players']; ?></p>
<p>伤病记录数: <?php echo $data['injury_stats']['total_injury_records']; ?></p>
<p>错过比赛场次: <?php echo $data['injury_stats']['total_matches_missed']; ?></p>
<p>平均每例伤病错失: <?php echo $data['injury_stats']['avg_matches_missed_per_injury']; ?> 场</p>
</div>
<!-- 成绩对比 -->
<div class="performance-compare">
<h3>成绩对比</h3>
<table class="comparison-table">
<tr>
<th>指标</th>
<th>有伤病影响</th>
<th>无伤病影响</th>
<th>差异</th>
</tr>
<tr>
<td>比赛场数</td>
<td><?php echo $data['team_stats']['with_injuries']['matches']; ?></td>
<td><?php echo $data['team_stats']['without_injuries']['matches']; ?></td>
<td></td>
</tr>
<tr>
<td>胜率</td>
<td><?php echo $data['team_stats']['with_injuries']['win_rate']; ?>%</td>
<td><?php echo $data['team_stats']['without_injuries']['win_rate']; ?>%</td>
<td class="highlight"><?php echo $data['team_stats']['difference']['win_rate_diff']; ?>%</td>
</tr>
<tr>
<td>场均进球</td>
<td><?php echo $data['team_stats']['with_injuries']['avg_goals_scored']; ?></td>
<td><?php echo $data['team_stats']['without_injuries']['avg_goals_scored']; ?></td>
<td class="highlight"><?php echo $data['team_stats']['difference']['goals_diff']; ?></td>
</tr>
</table>
</div>
<?php endforeach; ?>
<?php endif; ?>
<!-- 球员伤病影响排行 -->
<h2>球员伤病影响排行</h2>
<table class="comparison-table">
<tr>
<th>球员</th>
<th>位置</th>
<th>伤病次数</th>
<th>错失场次</th>
<th>平均错失</th>
<th>影响等级</th>
<th>主要伤病</th>
</tr>
<?php foreach ($ranking as $player): ?>
<tr>
<td><?php echo $player['name']; ?></td>
<td><?php echo $player['position']; ?></td>
<td><?php echo $player['injury_count']; ?></td>
<td><?php echo $player['total_matches_missed']; ?></td>
<td><?php echo $player['avg_matches_missed']; ?></td>
<td>
<span class="impact-badge impact-<?php echo strtolower($player['impact_level']); ?>">
<?php echo $player['impact_level']; ?>
</span>
</td>
<td><?php echo $player['injury_types']; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
</body>
</html>
使用示例
<?php
// 使用示例
require_once 'config/database.php';
require_once 'controller/InjuryController.php';
require_once 'InjuryImpactAnalyzer.php';
$db = new mysqli('localhost', 'username', 'password', 'football_db');
$controller = new InjuryController($db);
// 获取对比数据
$teamId = 1; // 球队ID
$viewData = $controller->compareAction($teamId);
// 加载视图
extract($viewData);
include 'views/injury_comparison.php';
?>
高级功能扩展建议
可视化图表
- 使用 Chart.js 或 ECharts 生成折线图、柱状图
- 展示伤病趋势和成绩变化
预测分析
- 基于历史数据预测未来伤病影响
- 使用线性回归或机器学习算法
位置价值分析
public function getPositionValueAnalysis($teamId) {
// 分析不同位置球员伤病对成绩的影响权重
// 前锋受伤影响得分
// 后卫受伤影响失球
}
赛程密度分析
public function analyzeScheduleLoad($teamId, $seasonStart, $seasonEnd) {
// 分析密集赛程期间的伤病频率
}
这个系统可以帮助球队管理层和教练组更好地理解伤病对球队成绩的影响,从而做出更科学的轮换决策和转会策略。
