php项目统计高位逼抢夺回球权几次？

PHP项目统计高位逼抢夺回球权次数

这是一个足球数据分析的需求,下面给你一个完整的实现方案。

数据模型设计

首先需要设计数据库表结构：

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_name VARCHAR(100),
    match_date DATE
);
-- 球员表
CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    team_id INT
);
-- 事件表（核心）
CREATE TABLE match_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT,
    player_id INT,
    event_type VARCHAR(30),      -- 事件类型
    zone VARCHAR(20),            -- 区域: defensive_third/middle_third/attacking_third
    minute INT,
    success TINYINT DEFAULT 1,   -- 是否成功
    created_at DATETIME,
    INDEX idx_match (match_id),
    INDEX idx_type (event_type)
);
-- 高位逼抢定义表（可配置）
CREATE TABLE pressing_rules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    rule_name VARCHAR(50),
    zone VARCHAR(20),            -- 触发区域
    event_types VARCHAR(200)     -- 允许的事件类型, 逗号分隔
);

核心统计逻辑（PHP）

<?php
class HighPressStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某个球员/球队的高位逼抢夺回球权次数
     * 
     * @param int      $matchId   比赛ID (0=全部)
     * @param int|null $playerId  球员ID (null=全队)
     * @param int|null $teamId    球队ID
     */
    public function countHighPressRecoveries(
        int $matchId = 0,
        ?int $playerId = null,
        ?int $teamId = null
    ): array {
        // 高位逼抢定义：在前场(attacking_third)或中场高压区
        // 事件类型：抢断(tackle)、拦截(interception)、争抢成功(duel_won)、压迫成功(press_success)
        $pressEventTypes = ['tackle', 'interception', 'duel_won', 'press_success'];
        $highZones = ['attacking_third', 'middle_third'];
        $sql = "SELECT 
                    COUNT(*) AS total_recoveries,
                    COUNT(DISTINCT me.match_id) AS matches_count,
                    me.player_id,
                    p.name AS player_name
                FROM match_events me
                LEFT JOIN players p ON p.id = me.player_id
                WHERE me.event_type IN (" . $this->placeholders($pressEventTypes) . ")
                  AND me.zone IN (" . $this->placeholders($highZones) . ")
                  AND me.success = 1";
        $params = array_merge($pressEventTypes, $highZones);
        if ($matchId > 0) {
            $sql .= " AND me.match_id = ?";
            $params[] = $matchId;
        }
        if ($playerId !== null) {
            $sql .= " AND me.player_id = ?";
            $params[] = $playerId;
        }
        if ($teamId !== null) {
            $sql .= " AND p.team_id = ?";
            $params[] = $teamId;
        }
        $sql .= " GROUP BY me.player_id, p.name
                  ORDER BY total_recoveries DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 统计总次数（单值）
     */
    public function countTotal(int $matchId = 0, ?int $playerId = null): int
    {
        $result = $this->countHighPressRecoveries($matchId, $playerId);
        return array_sum(array_column($result, 'total_recoveries'));
    }
    private function placeholders(array $arr): string
    {
        return implode(',', array_fill(0, count($arr), '?'));
    }
}

使用示例

$pdo = new PDO('mysql:host=localhost;dbname=football', 'user', 'pass');
$stats = new HighPressStats($pdo);
// 1. 统计整场比赛的高位逼抢成功次数
$total = $stats->countTotal(matchId: 101);
echo "本场高位逼抢夺回球权: {$total} 次\n";
// 2. 按球员统计（某场比赛）
$players = $stats->countHighPressRecoveries(matchId: 101);
foreach ($players as $row) {
    echo "{$row['player_name']}: {$row['total_recoveries']} 次\n";
}
// 3. 统计某球队整个赛季
$teamStats = $stats->countHighPressRecoveries(matchId: 0, teamId: 5);

关键设计说明

进阶：汇总表（大数据量推荐）

-- 每日/每场汇总，避免每次实时聚合
CREATE TABLE high_press_summary (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT,
    player_id INT,
    team_id INT,
    recoveries INT DEFAULT 0,
    updated_at DATETIME,
    UNIQUE KEY uk (match_id, player_id)
);

在事件入库时通过触发器或业务代码增量更新汇总表,查询时直接读取即可。

如果你能补充以下信息,我可以给出更精确的实现：

1. 你的数据源格式（CSV导入？API？手工录入？）
2. "高位逼抢"的具体判定规则（是否按坐标区域？）
3. 是否需要按赛季/球队/球员多维度报表