php项目统计定位球得分占比多少？

PHP项目统计定位球得分占比方案

下面给你一套完整的实现思路和代码示例,用于统计定位球得分占比（定位球进球数 / 总进球数）。

需求理解

定位球通常包括：

• 任意球（直接/间接）
• 角球
• 点球
• 界外球（算不算看业务定义）

得分占比公式：

定位球得分占比 = 定位球进球数 / 总进球数 × 100%

数据库设计

进球记录表 goals

CREATE TABLE goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL COMMENT '比赛ID',
    team_id INT NOT NULL COMMENT '球队ID',
    player_id INT NULL COMMENT '球员ID',
    goal_type VARCHAR(20) NOT NULL COMMENT '进球类型',
    -- 取值: open_play(运动战), free_kick(任意球),
    --       corner(角球), penalty(点球), throw_in(界外球), own_goal(乌龙球)
    is_set_piece TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否定位球',
    minute INT COMMENT '进球分钟',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_team (team_id),
    INDEX idx_type (goal_type),
    INDEX idx_set_piece (is_set_piece)
);

建议：既存 goal_type 又存 is_set_piece 冗余字段，查询更快，也便于业务方自定义哪些类型算定位球。

或者用字典表定义（更灵活）

CREATE TABLE goal_types (
    id INT PRIMARY KEY,
    code VARCHAR(20),
    name VARCHAR(30),
    is_set_piece TINYINT(1)  -- 是否计入定位球
);

核心统计 SQL

全联赛定位球得分占比

SELECT
    COUNT(*) AS total_goals,
    SUM(is_set_piece) AS set_piece_goals,
    ROUND(SUM(is_set_piece) / COUNT(*) * 100, 2) AS set_piece_ratio
FROM goals;

按球队统计

SELECT
    t.id,
    t.name,
    COUNT(g.id) AS total_goals,
    SUM(g.is_set_piece) AS set_piece_goals,
    ROUND(SUM(g.is_set_piece) / COUNT(g.id) * 100, 2) AS ratio_pct
FROM teams t
LEFT JOIN goals g ON g.team_id = t.id
GROUP BY t.id, t.name
ORDER BY ratio_pct DESC;

按进球类型细分

SELECT
    goal_type,
    COUNT(*) AS cnt,
    ROUND(COUNT(*) / (SELECT COUNT(*) FROM goals) * 100, 2) AS pct
FROM goals
GROUP BY goal_type
ORDER BY cnt DESC;

按时间段/赛事统计

SELECT
    m.competition_id,
    SUM(g.is_set_piece) AS sp_goals,
    COUNT(g.id) AS total,
    ROUND(SUM(g.is_set_piece) / COUNT(g.id) * 100, 2) AS ratio
FROM goals g
JOIN matches m ON m.id = g.match_id
GROUP BY m.competition_id;

PHP 实现示例（PDO）

<?php
class SetPieceStatService
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 联赛整体定位球得分占比
     */
    public function overallRatio(): array
    {
        $sql = "SELECT
                    COUNT(*) AS total_goals,
                    COALESCE(SUM(is_set_piece), 0) AS set_piece_goals
                FROM goals";
        $row = $this->pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
        $total = (int)$row['total_goals'];
        $sp    = (int)$row['set_piece_goals'];
        return [
            'total_goals'     => $total,
            'set_piece_goals' => $sp,
            'ratio'           => $total > 0 ? round($sp / $total * 100, 2) : 0.0,
            'ratio_text'      => $total > 0 ? round($sp / $total * 100, 2) . '%' : '0%',
        ];
    }
    /**
     * 按球队统计
     */
    public function byTeam(): array
    {
        $sql = "SELECT
                    t.id, t.name,
                    COUNT(g.id) AS total_goals,
                    COALESCE(SUM(g.is_set_piece), 0) AS sp_goals
                FROM teams t
                LEFT JOIN goals g ON g.team_id = t.id
                GROUP BY t.id, t.name
                HAVING total_goals > 0";
        $stmt = $this->pdo->query($sql);
        $result = [];
        foreach ($stmt as $row) {
            $total = (int)$row['total_goals'];
            $sp    = (int)$row['sp_goals'];
            $result[] = [
                'team_id'         => (int)$row['id'],
                'team_name'       => $row['name'],
                'total_goals'     => $total,
                'set_piece_goals' => $sp,
                'ratio'           => $total ? round($sp / $total * 100, 2) : 0.0,
            ];
        }
        return $result;
    }
    /**
     * 按进球类型分布
     */
    public function byType(): array
    {
        $sql = "SELECT goal_type, COUNT(*) AS cnt
                FROM goals GROUP BY goal_type";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
}

调用：

$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', '');
$service = new SetPieceStatService($pdo);
$overall = $service->overallRatio();
// ['total_goals'=>1000,'set_piece_goals'=>280,'ratio'=>28.0,'ratio_text'=>'28%']

几个业务注意点 ⚠️

1. 乌龙球一般不计入任何一方的定位球/运动战统计，需在 SQL 里排除：

   WHERE goal_type <> 'own_goal'

2. 点球是否算定位球？FIFA 官方统计中定位球通常含点球，但有些平台单列，建议数据表里 is_set_piece 由业务方配置，不硬编码。

3. 二次进攻：角球被顶出后二次进攻得分，算不算定位球？一般不算（那是 open_play），需要在录入时人工判定。

4. 占比分母建议排除乌龙球，否则会拉低比例失真。

5. 大数据量时可考虑物化汇总表 + 定时任务，而非每次实时 JOIN：

   CREATE TABLE team_goal_stats (
     team_id INT PRIMARY KEY,
     total_goals INT,
     set_piece_goals INT,
     updated_at TIMESTAMP
   );

输出示例

全联赛 总进球: 1000 | 定位球: 280 | 占比: 28.00%
任意球: 90 (9.0%)
角球:   130 (13.0%)
点球:   60 (6.0%)

如果你能告诉我具体的数据表结构（或现有业务的字段），我可以把 SQL 和 PHP 直接改成贴合你项目的版本。