PHP项目统计高球传中争顶成功率
这是一个足球数据分析的典型需求,下面给你一套完整的实现思路和代码。
业务定义
争顶成功率通常指：
争顶成功率 = 争顶成功次数 / 争顶总次数 × 100%高球传中场景下需要的数据：
- 传中总次数
- 传中后形成争顶的次数
- 争顶成功次数（本方抢到第一落点/完成攻门/有效控制）
- 争顶失败次数
数据库设计
-- 传中事件表
CREATE TABLE `cross_events` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`match_id` INT UNSIGNED NOT NULL COMMENT '比赛ID',
`team_id` INT UNSIGNED NOT NULL COMMENT '传中球队ID',
`player_id` INT UNSIGNED NOT NULL COMMENT '传中球员ID',
`minute` TINYINT UNSIGNED NOT NULL COMMENT '比赛分钟',
`cross_type` ENUM('high','low','ground') DEFAULT 'high' COMMENT '传中类型',
`is_success` TINYINT(1) DEFAULT 0 COMMENT '是否形成有效进攻',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY `idx_match_team` (`match_id`, `team_id`)
) ENGINE=InnoDB;
-- 争顶事件表
CREATE TABLE `aerial_duels` (
`id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`match_id` INT UNSIGNED NOT NULL,
`team_id` INT UNSIGNED NOT NULL COMMENT '争顶球队',
`player_id` INT UNSIGNED NOT NULL,
`cross_event_id` INT UNSIGNED DEFAULT NULL COMMENT '关联的传中事件',
`minute` TINYINT UNSIGNED NOT NULL,
`is_won` TINYINT(1) NOT NULL COMMENT '1成功 0失败',
`zone` VARCHAR(20) DEFAULT NULL COMMENT '区域：禁区/禁区弧/边路',
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
KEY `idx_match_team` (`match_id`, `team_id`),
KEY `idx_cross` (`cross_event_id`)
) ENGINE=InnoDB;
核心统计代码
1 单场/单队争顶成功率
<?php
class CrossAerialStats
{
private PDO $pdo;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
/**
* 统计某场比赛某球队的高球传中争顶成功率
*/
public function getTeamStats(int $matchId, int $teamId): array
{
// 1. 高球传中总数
$sql1 = "SELECT COUNT(*) FROM cross_events
WHERE match_id = :mid AND team_id = :tid AND cross_type = 'high'";
$stmt = $this->pdo->prepare($sql1);
$stmt->execute(['mid' => $matchId, 'tid' => $teamId]);
$totalCrosses = (int)$stmt->fetchColumn();
// 2. 形成争顶的次数（关联传中的争顶）
$sql2 = "SELECT COUNT(DISTINCT ad.id) FROM aerial_duels ad
INNER JOIN cross_events ce ON ad.cross_event_id = ce.id
WHERE ad.match_id = :mid AND ad.team_id = :tid
AND ce.cross_type = 'high'";
$stmt = $this->pdo->prepare($sql2);
$stmt->execute(['mid' => $matchId, 'tid' => $teamId]);
$totalDuels = (int)$stmt->fetchColumn();
// 3. 争顶成功次数
$sql3 = "SELECT COUNT(DISTINCT ad.id) FROM aerial_duels ad
INNER JOIN cross_events ce ON ad.cross_event_id = ce.id
WHERE ad.match_id = :mid AND ad.team_id = :tid
AND ce.cross_type = 'high' AND ad.is_won = 1";
$stmt = $this->pdo->prepare($sql3);
$stmt->execute(['mid' => $matchId, 'tid' => $teamId]);
$wonDuels = (int)$stmt->fetchColumn();
$successRate = $totalDuels > 0
? round($wonDuels / $totalDuels * 100, 2)
: 0.0;
return [
'total_crosses' => $totalCrosses,
'total_duels' => $totalDuels,
'won_duels' => $wonDuels,
'lost_duels' => $totalDuels - $wonDuels,
'success_rate' => $successRate, // 争顶成功率 %
'cross_duel_rate' => $totalCrosses > 0
? round($totalDuels / $totalCrosses * 100, 2)
: 0.0, // 传中后形成争顶比例 %
];
}
}
2 球员维度统计
public function getPlayerStats(int $matchId): array
{
$sql = "SELECT
ad.player_id,
p.name,
COUNT(ad.id) AS duels,
SUM(ad.is_won) AS won,
ROUND(SUM(ad.is_won) / COUNT(ad.id) * 100, 2) AS rate
FROM aerial_duels ad
INNER JOIN cross_events ce ON ad.cross_event_id = ce.id
LEFT JOIN players p ON p.id = ad.player_id
WHERE ad.match_id = :mid AND ce.cross_type = 'high'
GROUP BY ad.player_id, p.name
HAVING duels >= 1
ORDER BY rate DESC, duels DESC";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(['mid' => $matchId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
3 区域维度（禁区内/外围）
public function getZoneStats(int $matchId, int $teamId): array
{
$sql = "SELECT
ad.zone,
COUNT(*) AS duels,
SUM(ad.is_won) AS won,
ROUND(SUM(ad.is_won) / COUNT(*) * 100, 2) AS rate
FROM aerial_duels ad
INNER JOIN cross_events ce ON ad.cross_event_id = ce.id
WHERE ad.match_id = :mid AND ad.team_id = :tid
AND ce.cross_type = 'high'
GROUP BY ad.zone";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(['mid' => $matchId, 'tid' => $teamId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
多场累计/赛季统计
public function getSeasonStats(int $teamId, string $from, string $to): array
{
$sql = "SELECT
COUNT(ad.id) AS total_duels,
SUM(ad.is_won) AS won,
ROUND(SUM(ad.is_won) / NULLIF(COUNT(ad.id),0) * 100, 2) AS rate
FROM aerial_duels ad
INNER JOIN cross_events ce ON ad.cross_event_id = ce.id
WHERE ad.team_id = :tid AND ce.cross_type = 'high'
AND ad.created_at BETWEEN :from AND :to";
$stmt = $this->pdo->prepare($sql);
$stmt->execute(['tid'=>$teamId, 'from'=>$from, 'to'=>$to]);
return $stmt->fetch(PDO::FETCH_ASSOC);
}
性能优化要点
| 场景 | 优化方案 |
|---|---|
| 数据量大（万级比赛） | 建汇总表 team_match_summary 定时聚合 |
| 实时性要求高 | 用 Redis Hash 缓存当日统计，按 match_id 分 key |
| 复杂筛选（区域/时间段） | 物化视图 或 ClickHouse 分列存储 |
| 关联查询慢 | cross_event_id 需建索引；避免 SELECT * |
定期聚合表示例：
CREATE TABLE `team_aerial_summary` ( `match_id` INT, `team_id` INT, `high_crosses` INT DEFAULT 0, `total_duels` INT DEFAULT 0, `won_duels` INT DEFAULT 0, `success_rate` DECIMAL(5,2) DEFAULT 0, `updated_at` DATETIME, PRIMARY KEY (`match_id`,`team_id`) );
用定时任务（如 crontab + 脚本）在赛后批量写入，前端直接查询汇总表。
常见口径陷阱 ⚠️
- "成功"的定义要统一：第一落点抢到？还是形成射门？还是有效控制球权？不同平台口径不同，必须在项目内写死。
- 传中与争顶未必一一对应：一次传中可能引发多次连续争顶，建议用
cross_event_id明确归属。 - 对方争顶要不要算：如果统计"本方成功率"，只统计本方球员；若要分析"对抗质量"，需同时记录对方结果。
- 低平传中排除：
cross_type = 'high'必须过滤，混入会拉低/虚高数据。 - 重复事件去重：使用
COUNT(DISTINCT id)防止 join 造成重复计数。
返回数据示例
{
"total_crosses": 18,
"total_duels": 12,
"won_duels": 7,
"lost_duels": 5,
"success_rate": 58.33,
"cross_duel_rate": 66.67
}
如果需要我进一步提供：① 争顶事件的采集接口设计（供前端/数据录入用）、② 用 Redis 缓存的完整封装、③ 视频事件时间轴的数据结构，告诉我具体场景，我可以继续展开。
