php项目统计禁区内射门次数对比？

wen PHP项目 2026-10-16 2

PHP 项目：统计禁区内射门次数对比

下面给你一个完整的实现方案,包含数据库设计、核心 PHP 代码、统计逻辑，适用于足球数据统计系统。

php项目统计禁区内射门次数对比？

需求分析

统计两支球队在禁区内射门次数的对比，通常需要：

按比赛（match）统计
按球队（home / away）分组
只统计禁区内（is_in_box = 1）的射门
输出对比数据（次数、占比、可视化）

数据库设计

射门记录表 `shots`

CREATE TABLE `shots` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `match_id` INT UNSIGNED NOT NULL COMMENT '比赛ID',
  `team_id` INT UNSIGNED NOT NULL COMMENT '球队ID',
  `player_id` INT UNSIGNED DEFAULT NULL COMMENT '球员ID',
  `is_in_box` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否禁区内 1是 0否',
  `is_goal` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否进球',
  `minute` INT NOT NULL DEFAULT 0 COMMENT '比赛分钟',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_match_team` (`match_id`, `team_id`),
  KEY `idx_in_box` (`is_in_box`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

比赛表 `matches`（简化）

CREATE TABLE `matches` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `home_team_id` INT UNSIGNED NOT NULL,
  `away_team_id` INT UNSIGNED NOT NULL,
  `match_time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心 SQL：禁区内射门次数对比

一条 SQL 直接算出主客队禁区内 / 总射门次数：

SELECT
    m.id AS match_id,
    m.home_team_id,
    m.away_team_id,
    SUM(CASE WHEN s.team_id = m.home_team_id AND s.is_in_box = 1 THEN 1 ELSE 0 END) AS home_box_shots,
    SUM(CASE WHEN s.team_id = m.away_team_id AND s.is_in_box = 1 THEN 1 ELSE 0 END) AS away_box_shots,
    SUM(CASE WHEN s.team_id = m.home_team_id THEN 1 ELSE 0 END) AS home_total_shots,
    SUM(CASE WHEN s.team_id = m.away_team_id THEN 1 ELSE 0 END) AS away_total_shots
FROM matches m
LEFT JOIN shots s ON s.match_id = m.id
WHERE m.id = :match_id
GROUP BY m.id;

PHP 实现

PDO 连接

<?php
// db.php
function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            'mysql:host=127.0.0.1;dbname=football;charset=utf8mb4',
            'root',
            'password',
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]
        );
    }
    return $pdo;
}

服务类：统计禁区内射门对比

<?php
// ShotStatsService.php
class ShotStatsService
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 获取单场比赛禁区内射门对比
     */
    public function getBoxShotsComparison(int $matchId): array
    {
        $sql = "
            SELECT
                m.id AS match_id,
                m.home_team_id,
                m.away_team_id,
                SUM(CASE WHEN s.team_id = m.home_team_id AND s.is_in_box = 1 THEN 1 ELSE 0 END) AS home_box_shots,
                SUM(CASE WHEN s.team_id = m.away_team_id AND s.is_in_box = 1 THEN 1 ELSE 0 END) AS away_box_shots,
                SUM(CASE WHEN s.team_id = m.home_team_id THEN 1 ELSE 0 END) AS home_total_shots,
                SUM(CASE WHEN s.team_id = m.away_team_id THEN 1 ELSE 0 END) AS away_total_shots
            FROM matches m
            LEFT JOIN shots s ON s.match_id = m.id
            WHERE m.id = :match_id
            GROUP BY m.id
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        $row = $stmt->fetch();
        if (!$row) {
            return [];
        }
        // 计算占比
        $row['home_box_ratio'] = $row['home_total_shots'] > 0
            ? round($row['home_box_shots'] / $row['home_total_shots'] * 100, 2) : 0;
        $row['away_box_ratio'] = $row['away_total_shots'] > 0
            ? round($row['away_box_shots'] / $row['away_total_shots'] * 100, 2) : 0;
        return $row;
    }
    /**
     * 批量统计多场比赛（用于赛季数据）
     */
    public function getSeasonComparison(array $matchIds): array
    {
        if (empty($matchIds)) return [];
        $placeholders = implode(',', array_fill(0, count($matchIds), '?'));
        $sql = "
            SELECT
                team_id,
                COUNT(*) AS total_shots,
                SUM(is_in_box) AS box_shots,
                ROUND(SUM(is_in_box) / COUNT(*) * 100, 2) AS box_ratio
            FROM shots
            WHERE match_id IN ($placeholders)
            GROUP BY team_id
            ORDER BY box_shots DESC
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($matchIds);
        return $stmt->fetchAll();
    }
}

调用示例

<?php
// index.php
require 'db.php';
require 'ShotStatsService.php';
$service = new ShotStatsService(db());
// 单场对比
$comparison = $service->getBoxShotsComparison(1001);
header('Content-Type: application/json; charset=utf-8');
echo json_encode($comparison, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

输出示例：

{
    "match_id": 1001,
    "home_team_id": 5,
    "away_team_id": 8,
    "home_box_shots": 9,
    "away_box_shots": 4,
    "home_total_shots": 15,
    "away_total_shots": 10,
    "home_box_ratio": 60.00,
    "away_box_ratio": 40.00
}

可视化对比（前端）

<div class="box-shots-compare">
    <h3>禁区内射门次数对比</h3>
    <div class="bar-row">
        <span class="team">主队</span>
        <div class="bar" style="width: <?= $home_box_shots / max($home_box_shots + $away_box_shots, 1) * 100 ?>%">
            <?= $home_box_shots ?> 次（<?= $home_box_ratio ?>%）
        </div>
    </div>
    <div class="bar-row">
        <span class="team">客队</span>
        <div class="bar away" style="width: <?= $away_box_shots / max($home_box_shots + $away_box_shots, 1) * 100 ?>%">
            <?= $away_box_shots ?> 次（<?= $away_box_ratio ?>%）
        </div>
    </div>
</div>

.bar-row { display: flex; align-items: center; margin: 8px 0; }
.team { width: 60px; }
.bar { background: #3498db; color: #fff; padding: 6px 10px;
       border-radius: 4px; transition: width .3s; }
.bar.away { background: #e74c3c; }

进阶扩展建议

功能	实现方式
按球员统计	`GROUP BY player_id` 加一层维度
按半场统计	增加字段 `half` (1/2)，组合过滤
缓存	用 Redis 缓存单场比赛结果，key: `shot_compare:{match_id}`
实时更新	使用 WebSocket / 消息队列推送
图表可视化	前端用 ECharts / Chart.js 渲染柱状对比图
权限控制	后台仅教练/分析师可写入 shots 表