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

wen PHP项目 2026-11-10 1

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

下面给你一个完整的实现方案，从数据表设计 → 统计逻辑 → 对比展示，适合用在足球数据类 PHP 项目中。

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

数据表设计

-- 射门记录表
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 '是否进球',
  `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;
-- 球队表(简化)
CREATE TABLE `teams` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心统计 SQL

单场比赛禁区内外射门对比（按球队）

SELECT
    t.name AS team_name,
    SUM(CASE WHEN s.is_in_box = 1 THEN 1 ELSE 0 END) AS in_box_shots,
    SUM(CASE WHEN s.is_in_box = 0 THEN 1 ELSE 0 END) AS out_box_shots,
    COUNT(*) AS total_shots,
    ROUND(
        SUM(CASE WHEN s.is_in_box = 1 THEN 1 ELSE 0 END) / COUNT(*) * 100, 2
    ) AS in_box_rate,
    SUM(CASE WHEN s.is_in_box = 1 AND s.is_goal = 1 THEN 1 ELSE 0 END) AS in_box_goals,
    SUM(CASE WHEN s.is_in_box = 0 AND s.is_goal = 1 THEN 1 ELSE 0 END) AS out_box_goals
FROM shots s
JOIN teams t ON t.id = s.team_id
WHERE s.match_id = :match_id
GROUP BY s.team_id, t.name
ORDER BY in_box_shots DESC;

跨多场比赛 / 赛季累计对比

SELECT
    t.name AS team_name,
    COUNT(*) AS total_shots,
    SUM(s.is_in_box = 1) AS in_box_shots,
    SUM(s.is_in_box = 0) AS out_box_shots,
    ROUND(SUM(s.is_in_box = 1) / COUNT(*) * 100, 2) AS in_box_rate,
    ROUND(AVG(CASE WHEN s.is_in_box = 1 THEN 1 ELSE 0 END) * 100, 2) AS avg_in_box_rate
FROM shots s
JOIN teams t ON t.id = s.team_id
WHERE s.match_id IN (:match_ids)   -- 或按赛季/日期过滤
GROUP BY s.team_id, t.name
ORDER BY in_box_rate DESC;

PHP 实现（PDO）

封装函数

<?php
class ShotStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 单场比赛：各队禁区内/外射门对比
     */
    public function getMatchInBoxComparison(int $matchId): array
    {
        $sql = "
            SELECT
                t.name AS team_name,
                SUM(s.is_in_box = 1) AS in_box_shots,
                SUM(s.is_in_box = 0) AS out_box_shots,
                COUNT(*)             AS total_shots,
                ROUND(SUM(s.is_in_box = 1) / COUNT(*) * 100, 2) AS in_box_rate,
                SUM(s.is_in_box = 1 AND s.is_goal = 1) AS in_box_goals,
                SUM(s.is_in_box = 0 AND s.is_goal = 1) AS out_box_goals
            FROM shots s
            JOIN teams t ON t.id = s.team_id
            WHERE s.match_id = :match_id
            GROUP BY s.team_id, t.name
            ORDER BY in_box_shots DESC
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 多场比赛累计对比
     */
    public function getSeasonInBoxComparison(array $matchIds): array
    {
        if (empty($matchIds)) {
            return [];
        }
        // 用占位符防止 SQL 注入
        $placeholders = implode(',', array_fill(0, count($matchIds), '?'));
        $sql = "
            SELECT
                t.name AS team_name,
                COUNT(*)             AS total_shots,
                SUM(s.is_in_box = 1) AS in_box_shots,
                SUM(s.is_in_box = 0) AS out_box_shots,
                ROUND(SUM(s.is_in_box = 1) / COUNT(*) * 100, 2) AS in_box_rate,
                ROUND(AVG(s.is_in_box = 1) * 100, 2) AS avg_in_box_rate
            FROM shots s
            JOIN teams t ON t.id = s.team_id
            WHERE s.match_id IN ($placeholders)
            GROUP BY s.team_id, t.name
            ORDER BY in_box_rate DESC
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($matchIds);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 两队对比（用于聚焦某场比赛的两个对手）
     */
    public function compareTwoTeams(int $matchId): array
    {
        $rows = $this->getMatchInBoxComparison($matchId);
        $comparison = [
            'total_in_box'  => 0,
            'total_out_box' => 0,
            'teams'         => $rows,
        ];
        foreach ($rows as $r) {
            $comparison['total_in_box']  += (int)$r['in_box_shots'];
            $comparison['total_out_box'] += (int)$r['out_box_shots'];
        }
        return $comparison;
    }
}

使用示例

$pdo = new PDO('mysql:host=127.0.0.1;dbname=football;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stats = new ShotStats($pdo);
$matchId = 1001;
$res = $stats->getMatchInBoxComparison($matchId);
foreach ($res as $team) {
    printf(
        "【%s】禁区内射门: %d | 禁区外: %d | 总射门: %d | 禁区占比: %.2f%% | 禁区内进球: %d\n",
        $team['team_name'],
        $team['in_box_shots'],
        $team['out_box_shots'],
        $team['total_shots'],
        $team['in_box_rate'],
        $team['in_box_goals']
    );
}

输出示例：

【A队】禁区内射门: 12 | 禁区外: 5 | 总射门: 17 | 禁区占比: 70.59% | 禁区内进球: 3
【B队】禁区内射门: 6  | 禁区外: 9 | 总射门: 15 | 禁区占比: 40.00% | 禁区内进球: 1

可视化对比（简单 HTML 表 + 条形图）

function renderComparison(array $rows): string
{
    $max = 1;
    foreach ($rows as $r) {
        $max = max($max, (int)$r['in_box_shots'], (int)$r['out_box_shots']);
    }
    $html = '<table class="table"><thead><tr>
        <th>球队</th><th>禁区内</th><th>禁区外</th><th>禁区占比</th>
        </tr></thead><tbody>';
    foreach ($rows as $r) {
        $inW  = round($r['in_box_shots']  / $max * 100);
        $outW = round($r['out_box_shots'] / $max * 100);
        $html .= sprintf(
            '<tr>
                <td>%s</td>
                <td>
                    <div style="background:#e74c3c;height:16px;width:%d%%;display:inline-block"></div>
                    %d
                </td>
                <td>
                    <div style="background:#3498db;height:16px;width:%d%%;display:inline-block"></div>
                    %d
                </td>
                <td>%s%%</td>
            </tr>',
            htmlspecialchars($r['team_name']),
            $inW, $r['in_box_shots'],
            $outW, $r['out_box_shots'],
            $r['in_box_rate']
        );
    }
    return $html . '</tbody></table>';
}

性能与扩展建议

关注点	做法
索引	`(match_id, team_id)`、`(team_id, is_in_box)` 联合索引
全表扫描	赛季统计用 `WHERE match_id IN (...)`，可提前缓存聚合结果
性能加固	若表很大，建汇总表 `shot_summary(match_id, team_id, in_box, out_box, in_box_goals, out_box_goals)`，在插入射门时用触发器/事务更新
数据一致性	`is_in_box` 与 `is_goal` 用 TINYINT，避免 NULL 参与运算
空数据	`COUNT(*)=0` 时避免除零误区（`GROUP BY` 天然不会出现空组，但要判 `total_shots>0`）
多维度	可加 `period`（上半场/下半场）、`shot_type`（头球/脚射/点球）等字段

进阶：禁区内射门效率对比

如果你还想比较"禁区内射门转化率"（更实用）：

SELECT
    t.name,
    SUM(s.is_in_box = 1) AS in_box_shots,
    SUM(s.is_in_box = 1 AND s.is_goal = 1) AS in_box_goals,
    ROUND(
        SUM(s.is_in_box = 1 AND s.is_goal = 1)
        / NULLIF(SUM(s.is_in_box = 1), 0) * 100, 2
    ) AS in_box_conversion
FROM shots s
JOIN teams t ON t.id = s.team_id
WHERE s.match_id = :match_id
GROUP BY s.team_id, t.name;

用 NULLIF 防止禁区内 0 射门时除零错误。

需要我进一步补充：

按球员维度的禁区内射门排名？
前锋/中场/后卫位置对比？
接入 Redis 缓存 的版本？

告诉我你的场景,我可以直接给出对应模块。

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