php项目统计关门防守成功几次？

PHP项目统计“关门防守成功几次”的实现方案

“关门防守”这个说法通常出现在足球/篮球赛事数据统计场景中，下面给你一个完整的 PHP 实现思路和代码示例。

需求理解

你需要统计某个球员/球队在比赛中关门防守成功的次数，核心是一个数据表设计 + 统计查询。

数据库设计

-- 防守事件记录表
CREATE TABLE defense_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL COMMENT '比赛ID',
    player_id INT NOT NULL COMMENT '球员ID',
    team_id INT NOT NULL COMMENT '球队ID',
    event_type VARCHAR(32) NOT NULL COMMENT '事件类型: close_door_success / close_door_fail',
    event_time INT NOT NULL COMMENT '比赛进行时间(秒)',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_player (player_id),
    INDEX idx_match (match_id),
    INDEX idx_type (event_type)
);

核心统计代码（PHP + PDO）

<?php
class DefenseStatService
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某球员关门防守成功次数
     */
    public function countCloseDoorSuccess(int $playerId, ?int $matchId = null): int
    {
        $sql = "SELECT COUNT(*) FROM defense_events 
                WHERE player_id = :player_id 
                  AND event_type = 'close_door_success'";
        $params = [':player_id' => $playerId];
        if ($matchId !== null) {
            $sql .= " AND match_id = :match_id";
            $params[':match_id'] = $matchId;
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }
    /**
     * 批量统计（一次查出多名球员，避免 N+1 查询）
     */
    public function batchCountByPlayers(array $playerIds, ?int $matchId = null): array
    {
        if (empty($playerIds)) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($playerIds), '?'));
        $sql = "SELECT player_id, COUNT(*) AS success_count
                FROM defense_events
                WHERE event_type = 'close_door_success'
                  AND player_id IN ($placeholders)";
        $params = $playerIds;
        if ($matchId !== null) {
            $sql .= " AND match_id = ?";
            $params[] = $matchId;
        }
        $sql .= " GROUP BY player_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $result[(int)$row['player_id']] = (int)$row['success_count'];
        }
        return $result;
    }
    /**
     * 统计成功率（成功 / 总次数）
     */
    public function successRate(int $playerId, ?int $matchId = null): array
    {
        $sql = "SELECT 
                    SUM(event_type = 'close_door_success') AS success,
                    SUM(event_type = 'close_door_fail') AS fail,
                    COUNT(*) AS total
                FROM defense_events
                WHERE player_id = :player_id";
        $params = [':player_id' => $playerId];
        if ($matchId !== null) {
            $sql .= " AND match_id = :match_id";
            $params[':match_id'] = $matchId;
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $total = (int)$row['total'];
        return [
            'success' => (int)$row['success'],
            'fail'    => (int)$row['fail'],
            'total'   => $total,
            'rate'    => $total > 0 ? round($row['success'] / $total * 100, 2) . '%' : '0%',
        ];
    }
}

使用示例

$pdo = new PDO('mysql:host=127.0.0.1;dbname=sports;charset=utf8mb4', 'root', '123456', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$service = new DefenseStatService($pdo);
// 1. 单球员总成功次数
$count = $service->countCloseDoorSuccess(1001);
echo "球员1001关门防守成功：{$count} 次\n";
// 2. 指定比赛
$count = $service->countCloseDoorSuccess(1001, 20240101);
// 3. 批量
$counts = $service->batchCountByPlayers([1001, 1002, 1003], 20240101);
print_r($counts);
// 4. 成功率
$rate = $service->successRate(1001);
print_r($rate);
// 输出: ['success'=>5, 'fail'=>2, 'total'=>7, 'rate'=>'71.43%']

关键设计要点

如果你能补充以下信息,我可以给你更贴合的代码：

1. 是足球、篮球还是其他运动？
2. “关门防守”具体对应哪些原始动作（比如篮球里的协防包夹成功抢断）？
3. 数据来源是手动录入、API 接口还是视频识别？
4. 用的是 Laravel / ThinkPHP / 原生 PHP？