php项目统计直塞球成功率是多少？

PHP项目统计直塞球成功率的实现方案

你问的是足球数据统计中"直塞球成功率"如何在PHP项目中实现，下面给你完整的方案。

概念定义

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

• 直塞球（Through Ball）：穿透对方防线的向前传球，接球队员通常处于有利进攻位置
• 成功直塞：接球队员成功接到球且未被拦截
• 失败直塞：被防守球员拦截、出界、或接球队员未能控住

数据库设计

-- 比赛事件表（核心）
CREATE TABLE match_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    event_type VARCHAR(32) NOT NULL COMMENT 'through_ball / pass / shot ...',
    event_result VARCHAR(16) NOT NULL COMMENT 'success / fail',
    minute INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match_player (match_id, player_id),
    INDEX idx_type_result (event_type, event_result)
);

核心PHP统计代码

单球员直塞成功率

<?php
class ThroughBallStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某球员在指定比赛范围的直塞成功率
     */
    public function getPlayerRate(int $playerId, ?int $matchId = null): array
    {
        $sql = "SELECT 
                    COUNT(*) AS total,
                    SUM(event_result = 'success') AS success
                FROM match_events
                WHERE player_id = :pid
                  AND event_type = 'through_ball'";
        $params = [':pid' => $playerId];
        if ($matchId !== null) {
            $sql .= " AND match_id = :mid";
            $params[':mid'] = $matchId;
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $total   = (int) $row['total'];
        $success = (int) $row['success'];
        $rate    = $total > 0 ? round($success / $total * 100, 2) : 0.0;
        return [
            'player_id' => $playerId,
            'total'     => $total,
            'success'   => $success,
            'failed'    => $total - $success,
            'rate'      => $rate,  // 百分比，如 78.5
        ];
    }
}

球队/全队排名（一次SQL搞定）

public function getTeamRanking(int $teamId, int $limit = 20): array
{
    $sql = "SELECT 
                p.id,
                p.name,
                COUNT(e.id) AS total,
                SUM(e.event_result = 'success') AS success,
                ROUND(SUM(e.event_result = 'success') / COUNT(e.id) * 100, 2) AS rate
            FROM match_events e
            JOIN players p ON p.id = e.player_id
            WHERE e.team_id = :tid
              AND e.event_type = 'through_ball'
            GROUP BY p.id, p.name
            HAVING total >= 5           -- 过滤样本过小的球员
            ORDER BY rate DESC, total DESC
            LIMIT :lim";
    $stmt = $this->pdo->prepare($sql);
    $stmt->bindValue(':tid', $teamId, PDO::PARAM_INT);
    $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

调用示例

$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'user', 'pass');
$stats = new ThroughBallStats($pdo);
// 单球员
print_r($stats->getPlayerRate(1001));
// 输出: ['total'=>20, 'success'=>16, 'failed'=>4, 'rate'=>80.0]
// 球队排名
print_r($stats->getTeamRanking(5));

进阶统计维度

分区域示例：

SELECT zone,
       COUNT(*) AS total,
       ROUND(SUM(event_result='success')/COUNT(*)*100,2) AS rate
FROM match_events
WHERE player_id = :pid AND event_type = 'through_ball'
GROUP BY zone;

性能与数据质量建议

1. 索引优化：(player_id, event_type) 联合索引是核心
2. 物化统计表：数据量大时，用定时任务预聚合到 player_season_stats
3. 样本过滤：直塞次数 < 5 的不参与排名，避免"1/1=100%"的噪声
4. 异常校验：SUM(event_result='success') 用布尔转1，注意 NULL 值，建议列加 NOT NULL DEFAULT 'fail'
5. 缓存：排名类查询用 Redis 缓存 5–10 分钟

如果你用的是现成框架

• Laravel：用 Eloquent 的 selectRaw + groupBy 即可，逻辑同上
• 数据源：Opta / StatsBomb / Wyscout 的 API 一般直接把事件分类为 through_ball（或 pass_type=through_ball），直接按 result 字段聚合即可

需要我针对具体表结构（比如你已有的字段名）改写 SQL，或者加上 Redis 缓存层 / 定时聚合任务 的完整代码吗？