php项目统计反越位成功形成单刀几次？

PHP项目统计“反越位成功形成单刀”次数方案

这是一个足球赛事数据统计需求，下面给出完整的PHP实现方案。

需求拆解

“反越位成功形成单刀”需要同时满足：

数据表设计

-- 事件表
CREATE TABLE match_events (
    id INT 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,  -- pass/shoot/dribble/offside...
    event_time INT NOT NULL,          -- 比赛秒数
    x_coord DECIMAL(5,2),             -- 球场坐标
    y_coord DECIMAL(5,2),
    is_offside_position TINYINT DEFAULT 0,  -- 传球瞬间是否在越位位置
    is_offside_called TINYINT DEFAULT 0,    -- 裁判是否吹罚越位
    defenders_behind INT DEFAULT 0,         -- 身前防守球员数
    has_goalkeeper_only TINYINT DEFAULT 0,  -- 是否仅剩门将
    related_event_id INT,                   -- 关联事件(传球→接球)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 单刀机会表(可由脚本生成)
CREATE TABLE one_on_one_chances (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    chance_time INT NOT NULL,
    is_anti_offside TINYINT DEFAULT 1,  -- 是否反越位形成
    result VARCHAR(16),                  -- goal/saved/miss/foul
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心统计逻辑

方案A：基于事件表实时统计

<?php
class AntiOffsideStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某球员/球队/比赛的反越位单刀次数
     */
    public function countAntiOffsideOneOnOne(
        ?int $matchId = null,
        ?int $playerId = null,
        ?int $teamId = null
    ): int {
        $sql = "SELECT COUNT(DISTINCT o.id) AS total
                FROM match_events o
                WHERE o.event_type = 'one_on_one'
                  AND o.is_offside_position = 1
                  AND o.is_offside_called = 0
                  AND o.has_goalkeeper_only = 1";
        $params = [];
        if ($matchId !== null) {
            $sql .= " AND o.match_id = :match_id";
            $params['match_id'] = $matchId;
        }
        if ($playerId !== null) {
            $sql .= " AND o.player_id = :player_id";
            $params['player_id'] = $playerId;
        }
        if ($teamId !== null) {
            $sql .= " AND o.team_id = :team_id";
            $params['team_id'] = $teamId;
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }
    /**
     * 批量统计：每个球员的反越位单刀次数及转化率
     */
    public function statsByPlayer(int $matchId): array
    {
        $sql = "SELECT 
                    o.player_id,
                    p.name AS player_name,
                    COUNT(*) AS total,
                    SUM(CASE WHEN o.result = 'goal' THEN 1 ELSE 0 END) AS goals
                FROM match_events o
                JOIN players p ON p.id = o.player_id
                WHERE o.match_id = :match_id
                  AND o.event_type = 'one_on_one'
                  AND o.is_offside_position = 1
                  AND o.is_offside_called = 0
                  AND o.has_goalkeeper_only = 1
                GROUP BY o.player_id, p.name
                ORDER BY total DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['match_id' => $matchId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rows as &$row) {
            $row['conversion_rate'] = $row['total'] > 0
                ? round($row['goals'] / $row['total'] * 100, 2) . '%'
                : '0%';
        }
        return $rows;
    }
}

方案B：从原始事件流自动识别（更严谨）

若原始数据未直接标注单刀,需用状态机从传球/带球事件推断：

class OneOnOneDetector
{
    /**
     * 判断「反越位形成单刀」的三个关键判定
     * 
     * @param array $pass     传球事件
     * @param array $receiver 接球球员位置
     * @param array $defenders 所有防守球员位置(按x坐标)
     */
    public function isAntiOffsideOneOnOne(array $pass, array $receiver, array $defenders): bool
    {
        // 1. 传球瞬间接球者处于越位位置(倒数第二名防守球员之后)
        if (!$this->isOffsidePosition($receiver, $defenders)) {
            return false;
        }
        // 2. 传球瞬间球位置合法(反越位触发) —— 传球点不越位
        if (!$this->isPassLegal($pass, $defenders)) {
            return false;
        }
        // 3. 接球后形成单刀：身前仅剩门将
        return $this->hasOnlyGoalkeeperAhead($receiver, $defenders);
    }
    private function isOffsidePosition(array $player, array $defenders): bool
    {
        // 越位线 = 防守方倒数第二名球员的 x 坐标
        $xs = array_column($defenders, 'x');
        rsort($xs);
        $offsideLine = $xs[1] ?? $xs[0] ?? 100;  // 无第二人时用第一人
        return $player['x'] > $offsideLine;
    }
    private function isPassLegal(array $pass, array $defenders): bool
    {
        // 传球瞬间球本身不越位
        $xs = array_column($defenders, 'x');
        rsort($xs);
        $offsideLine = $xs[1] ?? $xs[0] ?? 100;
        return $pass['x'] <= $offsideLine + 0.5; // 容忍误差
    }
    private function hasOnlyGoalkeeperAhead(array $receiver, array $defenders): bool
    {
        // 找出比接球者更靠近对方球门的防守球员数
        $ahead = array_filter($defenders, fn($d) => $d['x'] > $receiver['x']);
        // 仅剩门将(门将 x 最靠后)
        return count($ahead) === 1 && $ahead[0]['position'] === 'GK';
    }
}

数据接入建议

由于国内主流数据源（如懂球帝、雷速、纳米数据、StatsBomb、Opta）字段差异大，落库前需做映射：

输出示例

{
  "match_id": 10086,
  "anti_offside_one_on_one": [
    {
      "player_name": "武磊",
      "total": 3,
      "goals": 2,
      "conversion_rate": "66.67%"
    },
    {
      "player_name": "张玉宁",
      "total": 1,
      "goals": 0,
      "conversion_rate": "0%"
    }
  ],
  "team_total": 4
}

注意事项

1. 越位判定误差：坐标数据精度有限，isPassLegal 中加 5m 容忍度。
2. VAR 回溯：若后续 VAR 改判，需支持 is_offside_called 更新并重算统计（用定时任务/事件回调）。
3. 半单刀过滤：若“单刀”定义宽松，可加参数 defenders_ahead <= 1 而非严格 == 1。
4. 结果字段：区分 goal / saved / miss / foul_drawn，便于算转化率。
5. 性能：事件表数据量大时，建议对 (match_id, event_type, player_id) 建联合索引，或用物化视图定期汇总到 one_on_one_chances。

需要我进一步补充：

• 具体的越位坐标判定算法（含门将特殊处理）？
• 查询某联赛整个赛季的球员排行榜 SQL？
• 对接某个具体数据源的 API 字段映射代码？