php项目统计传球成功率哪队更高？

PHP项目：统计哪支球队传球成功率更高

下面是一个完整的PHP实现方案，包括数据模型、计算逻辑和示例。

核心概念

传球成功率 = 成功传球次数 / 总传球次数 × 100%

数据结构设计

数据库表结构（MySQL）

-- 球队表
CREATE TABLE teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);
-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team_id INT,
    away_team_id INT,
    match_date DATE
);
-- 球员表
CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_id INT,
    name VARCHAR(50)
);
-- 传球事件表（核心）
CREATE TABLE passes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT,
    player_id INT,
    team_id INT,
    is_successful TINYINT(1) DEFAULT 1,  -- 1成功 0失败
    pass_time DATETIME
);
CREATE INDEX idx_team ON passes(team_id);
CREATE INDEX idx_match ON passes(match_id);

PHP 核心计算代码

方式1：纯 SQL 聚合（推荐，性能最好）

<?php
class PassStats
{
    private PDO $db;
    public function __construct(PDO $db)
    {
        $this->db = $db;
    }
    /**
     * 统计所有球队的传球成功率，返回排序结果
     */
    public function getTeamPassAccuracy(): array
    {
        $sql = "
            SELECT 
                t.id,
                t.name,
                COUNT(p.id) AS total_passes,
                SUM(p.is_successful) AS successful_passes,
                ROUND(SUM(p.is_successful) / COUNT(p.id) * 100, 2) AS accuracy
            FROM teams t
            LEFT JOIN passes p ON p.team_id = t.id
            GROUP BY t.id, t.name
            HAVING total_passes > 0
            ORDER BY accuracy DESC
        ";
        return $this->db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 统计某场比赛两支球队的传球成功率并比较
     */
    public function compareMatch(int $matchId): array
    {
        $sql = "
            SELECT 
                t.id AS team_id,
                t.name AS team_name,
                COUNT(p.id) AS total,
                SUM(p.is_successful) AS success,
                ROUND(SUM(p.is_successful) / COUNT(p.id) * 100, 2) AS accuracy
            FROM passes p
            JOIN teams t ON t.id = p.team_id
            WHERE p.match_id = :match_id
            GROUP BY t.id, t.name
            ORDER BY accuracy DESC
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $this->buildComparison($rows);
    }
    /**
     * 构建比较结果（谁更高、差多少）
     */
    private function buildComparison(array $rows): array
    {
        if (count($rows) < 2) {
            return ['error' => '数据不足，无法比较'];
        }
        $first  = $rows[0];
        $second = $rows[1];
        $diff   = round($first['accuracy'] - $second['accuracy'], 2);
        return [
            'winner'    => $first['team_name'],
            'loser'     => $second['team_name'],
            'winner_acc'=> $first['accuracy'] . '%',
            'loser_acc' => $second['accuracy'] . '%',
            'diff'      => $diff . '%',
            'detail'    => $rows,
        ];
    }
}

方式2：PHP 内存计算（适合小数据量或数据来自 API）

<?php
/**
 * 从传球事件数组计算各队传球成功率
 * @param array $passes 每条含 ['team_id'=>1, 'is_successful'=>1]
 */
function calcPassAccuracy(array $passes): array
{
    $stats = [];
    foreach ($passes as $p) {
        $tid = $p['team_id'];
        if (!isset($stats[$tid])) {
            $stats[$tid] = ['total' => 0, 'success' => 0];
        }
        $stats[$tid]['total']++;
        $stats[$tid]['success'] += (int)$p['is_successful'];
    }
    foreach ($stats as $tid => &$s) {
        $s['accuracy'] = $s['total'] > 0
            ? round($s['success'] / $s['total'] * 100, 2)
            : 0.0;
    }
    unset($s);
    // 按成功率降序
    uasort($stats, fn($a, $b) => $b['accuracy'] <=> $a['accuracy']);
    return $stats;
}

使用示例

<?php
$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stats = new PassStats($pdo);
// 1. 联赛所有球队排名
echo "=== 传球成功率排行 ===\n";
foreach ($stats->getTeamPassAccuracy() as $row) {
    printf("%-12s 传球%d次 成功%d次 成功率: %s%%\n",
        $row['name'], $row['total_passes'],
        $row['successful_passes'], $row['accuracy']);
}
// 2. 某场比赛对比
echo "\n=== 比赛 #1 对比 ===\n";
$result = $stats->compareMatch(1);
if (isset($result['error'])) {
    echo $result['error'];
} else {
    printf("胜者: %s (%s)\n", $result['winner'], $result['winner_acc']);
    printf("对手: %s (%s)\n", $result['loser'], $result['loser_acc']);
    printf("领先: %s\n", $result['diff']);
}

输出示例：

=== 传球成功率排行 ===
Manchester City  传球850次 成功780次 成功率: 91.76%
Barcelona        传球920次 成功820次 成功率: 89.13%
...
=== 比赛 #1 对比 ===
胜者: Manchester City (91.76%)
对手: Liverpool (85.30%)
领先: 6.46%

进阶优化建议

汇总表示例（性能优化）

CREATE TABLE team_match_stats (
    match_id INT,
    team_id INT,
    total_passes INT,
    successful_passes INT,
    accuracy DECIMAL(5,2),
    PRIMARY KEY (match_id, team_id)
);

每次比赛结束后批量更新一次，查询时直接 SELECT，避免实时扫描海量 passes 明细表。

平局判断补充

if ($first['accuracy'] == $second['accuracy']) {
    return [
        'result' => 'draw',
        'message' => "两队传球成功率相同：{$first['accuracy']}%",
    ];
}

需要我针对特定数据来源（如 CSV 导入、Opta/StatsBomb API、FIFA 数据）或前端展示（图表） 进一步扩展吗？