php项目统计快发任意球尝试几次？

wen PHP项目 2026-11-11 2

PHP项目统计快发任意球尝试次数方案

在足球数据统计项目中,"快发任意球"(Quick Free Kick)的统计需要结合事件数据和比赛状态判断，下面给出完整实现方案。

php项目统计快发任意球尝试几次？

数据模型设计

事件表结构

CREATE TABLE match_events (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    event_type VARCHAR(32) NOT NULL,      -- 'free_kick', 'foul', 'goal' 等
    event_subtype VARCHAR(32),            -- 'quick' / 'normal'
    minute INT NOT NULL,
    second INT NOT NULL,
    x_coord DECIMAL(6,2),
    y_coord DECIMAL(6,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match_time (match_id, minute, second),
    INDEX idx_type (event_type, event_subtype)
);
CREATE TABLE free_kick_attempts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT NOT NULL,
    foul_event_id BIGINT,                 -- 关联导致任意球的犯规事件
    free_kick_event_id BIGINT NOT NULL,   -- 关联实际开球事件
    delay_seconds INT,                    -- 犯规到开球的间隔
    is_quick BOOLEAN DEFAULT 0,           -- 是否判定为快发
    outcome VARCHAR(16),                  -- 'success'/'blocked'/'goal'/'out'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

快发任意球的判定逻辑

核心判定标准(可按业务调整阈值):

<?php
class QuickFreeKickDetector
{
    // 快发判定阈值(秒)
    const QUICK_THRESHOLD = 10;
    // 允许快发的区域(距球门一定距离外)
    const MIN_DISTANCE_TO_GOAL = 9.15;  // 米
    /**
     * 判断一次任意球是否为快发
     */
    public function isQuickFreeKick(array $foulEvent, array $freeKickEvent, array $matchState): bool
    {
        // 1. 时间间隔判定
        $delay = $this->calcDelaySeconds($foulEvent, $freeKickEvent);
        if ($delay > self::QUICK_THRESHOLD) {
            return false;
        }
        // 2. 裁判未鸣哨(比赛未暂停)
        if ($matchState['whistle_blown']) {
            return false;
        }
        // 3. 防守方未完成人墙组织
        if ($matchState['wall_formed']) {
            return false;
        }
        // 4. 开球方未要求裁判排人墙
        if ($matchState['requested_wall']) {
            return false;
        }
        // 5. 位置合理性(远离球门才允许快发)
        if ($freeKickEvent['distance_to_goal'] < self::MIN_DISTANCE_TO_GOAL) {
            return false;
        }
        return true;
    }
    private function calcDelaySeconds(array $foul, array $fk): int
    {
        $foulSec = $foul['minute'] * 60 + $foul['second'];
        $fkSec   = $fk['minute'] * 60 + $fk['second'];
        return max(0, $fkSec - $foulSec);
    }
}

统计查询实现

单场比赛统计

<?php
class FreeKickStatistics
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计某场比赛的快发任意球尝试次数(按球队)
     */
    public function getMatchQuickFreeKickCount(int $matchId): array
    {
        $sql = "
            SELECT 
                team_id,
                COUNT(*) AS total_attempts,
                SUM(CASE WHEN outcome = 'goal' THEN 1 ELSE 0 END) AS goals,
                SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) AS success,
                AVG(delay_seconds) AS avg_delay
            FROM free_kick_attempts
            WHERE match_id = :match_id AND is_quick = 1
            GROUP BY team_id
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 统计某球员整赛季快发任意球次数
     */
    public function getPlayerSeasonQuickCount(int $playerId, int $seasonId): int
    {
        $sql = "
            SELECT COUNT(*) FROM free_kick_attempts fka
            JOIN matches m ON m.id = fka.match_id
            WHERE fka.player_id = :pid 
              AND m.season_id = :sid 
              AND fka.is_quick = 1
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':pid' => $playerId, ':sid' => $seasonId]);
        return (int) $stmt->fetchColumn();
    }
    /**
     * 快发任意球成功率排行(球队)
     */
    public function getTeamQuickRanking(int $seasonId, int $limit = 20): array
    {
        $sql = "
            SELECT 
                t.id, t.name,
                COUNT(fka.id) AS attempts,
                SUM(CASE WHEN fka.outcome IN ('success','goal') THEN 1 ELSE 0 END) AS succeeded,
                ROUND(
                    SUM(CASE WHEN fka.outcome IN ('success','goal') THEN 1 ELSE 0 END) 
                    / COUNT(fka.id) * 100, 2
                ) AS success_rate
            FROM free_kick_attempts fka
            JOIN matches m ON m.id = fka.match_id
            JOIN teams t ON t.id = fka.team_id
            WHERE m.season_id = :sid AND fka.is_quick = 1
            GROUP BY t.id, t.name
            HAVING attempts >= 3
            ORDER BY attempts DESC
            LIMIT :lim
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':sid', $seasonId, PDO::PARAM_INT);
        $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

核心 SQL(一次性统计)

-- 某球队某赛季快发任意球尝试次数
SELECT 
    COUNT(*) AS quick_fk_attempts,
    SUM(is_success) AS quick_fk_success,
    ROUND(SUM(is_success)/COUNT(*)*100, 2) AS conversion_rate
FROM free_kick_attempts
WHERE team_id = ? 
  AND is_quick = 1
  AND match_id IN (
      SELECT id FROM matches WHERE season_id = ?
  );

注意事项与优化建议

判定标准先行

与 Opta/StatsBomb 等数据源对齐"快发"定义
建议阈值: 犯规后 ≤ 10 秒内开球 + 裁判未鸣哨 + 防守方未排好人墙

事件补齐

单纯的 free_kick 事件无法判定"快发",必须关联前序犯规事件 foul_event_id
若数据源缺少"裁判鸣哨/指挥排人墙"事件,建议用时间间隔 + 位置双条件近似判定

性能优化

free_kick_attempts 表 预计算写入(采集时判定并落库),避免查询时复杂 JOIN

对 is_quick 加索引或使用部分索引(MySQL 8.0):

CREATE INDEX idx_quick ON free_kick_attempts(team_id, match_id) WHERE is_quick = 1;

赛季级统计用物化视图/汇总表 + 定时任务刷新

边界场景

越位/犯规取消的任意球要排除
补时阶段快发要按实际时间计算
死球状态下"示意快发但裁判吹停"应记录为尝试但不计入成功

如果你能提供具体的数据源格式(如 Opta XML、Wyscout JSON、自建表结构),我可以给出更贴合你项目的字段映射和判定 SQL。

本文地址： https://bmjup.com/post/69089.html