IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

综合php项目，高速跑动距离对比？

wen PHP项目 1

PHP 项目中"高速跑动距离对比"的实现方案

这是体育/运动数据分析类的常见需求，下面给你一个综合 PHP 项目的完整实现思路 + 代码。

综合php项目，高速跑动距离对比？

需求理解

"高速跑动距离对比"通常指:

概念 说明
高速阈值 ≥ 5.5 m/s 或 ≥ 19.8 km/h
冲刺阈值 ≥ 7.0 m/s 或 ≥ 25.2 km/h
对比维度 球员 vs 球员、球队 vs 球队、场次 vs 场次、时间片（15分钟分段）
数据源 GPS 背心、雷达、视频追踪（每秒 10-25 帧）

数据库设计

-- 球员表
CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_id INT,
    name VARCHAR(50),
    number INT
);
-- 原始跑动数据（每秒一条 或 每 0.1 秒一条）
CREATE TABLE tracking_data (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    player_id INT,
    match_id INT,
    ts DATETIME(3),
    x DECIMAL(8,3),      -- 球场坐标 x (米)
    y DECIMAL(8,3),      -- 球场坐标 y (米)
    speed DECIMAL(5,2),  -- 瞬时速度 m/s
    INDEX idx_player_match_ts (player_id, match_id, ts)
);
-- 汇总表（加速查询）
CREATE TABLE player_match_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    player_id INT,
    match_id INT,
    total_distance DECIMAL(8,2),        -- 总距离
    high_speed_distance DECIMAL(8,2),   -- 高速跑动距离
    sprint_distance DECIMAL(8,2),       -- 冲刺距离
    high_speed_count INT,               -- 高速跑动次数
    max_speed DECIMAL(5,2),
    UNIQUE KEY uk_player_match (player_id, match_id)
);

核心计算服务

1 用 SQL 聚合高速跑动距离

-- 简单版：按瞬时速度直接累加
SELECT 
    p.name,
    SUM(CASE WHEN t.speed >= 5.5 THEN t.speed * 1 ELSE 0 END) AS high_speed_dist,
    SUM(CASE WHEN t.speed >= 7.0 THEN t.speed * 1 ELSE 0 END) AS sprint_dist,
    SUM(t.speed * 1) AS total_dist
FROM tracking_data t
JOIN players p ON p.id = t.player_id
WHERE t.match_id = :match_id
GROUP BY t.player_id;

前提：采样间隔 = 1 秒；0.1 秒采样，系数改成 0.1。

2 PHP 计算服务（推荐）

<?php
class RunDistanceService
{
    const HIGH_SPEED_THRESHOLD = 5.5; // m/s
    const SPRINT_THRESHOLD     = 7.0;
    public function __construct(private PDO $pdo) {}
    /**
     * 计算单个球员在一场比赛中的跑动指标
     * 使用 Haversine 距离作为兜底（防止 GPS 速度抖动）
     */
    public function calcPlayerMatch(int $playerId, int $matchId): array
    {
        $sql = "SELECT ts, x, y, speed 
                FROM tracking_data 
                WHERE player_id = :pid AND match_id = :mid 
                ORDER BY ts ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':pid' => $playerId, ':mid' => $matchId]);
        $total = $high = $sprint = 0.0;
        $highCnt = 0;
        $maxSpeed = 0.0;
        $prev = null;
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $speed = (float)$row['speed'];
            $maxSpeed = max($maxSpeed, $speed);
            if ($prev) {
                // 时间差（秒）
                $dt = (strtotime($row['ts']) - strtotime($prev['ts']))
                    + ((int)substr($row['ts'], -4) - (int)substr($prev['ts'], -4)) / 1000;
                // 位移距离（Haversine）
                $dist = $this->haversine(
                    (float)$prev['x'], (float)$prev['y'],
                    (float)$row['x'],  (float)$row['y']
                );
                // 距离用平均速度更稳（防止 GPS 跳点）
                $avgSpeed = $dt > 0 ? $dist / $dt : 0;
                $useSpeed = min($avgSpeed, $speed * 1.2); // 简单去噪
                $total += $dist;
                if ($useSpeed >= self::HIGH_SPEED_THRESHOLD) {
                    $high += $dist;
                    $highCnt++;
                }
                if ($useSpeed >= self::SPRINT_THRESHOLD) {
                    $sprint += $dist;
                }
            }
            $prev = $row;
        }
        return [
            'player_id'           => $playerId,
            'match_id'            => $matchId,
            'total_distance'      => round($total, 2),
            'high_speed_distance' => round($high, 2),
            'sprint_distance'     => round($sprint, 2),
            'high_speed_count'    => $highCnt,
            'max_speed'           => round($maxSpeed, 2),
        ];
    }
    private function haversine(float $lat1, float $lon1, float $lat2, float $lon2): float
    {
        // 若为平面坐标 (米) 直接欧氏距离
        $dx = $lat2 - $lat1;
        $dy = $lon2 - $lon1;
        return sqrt($dx * $dx + $dy * $dy);
    }
}

对比查询（多维度）

1 球员对比

SELECT 
    p.name,
    s.total_distance,
    s.high_speed_distance,
    ROUND(s.high_speed_distance / s.total_distance * 100, 2) AS hs_ratio,
    s.sprint_distance,
    s.max_speed
FROM player_match_stats s
JOIN players p ON p.id = s.player_id
WHERE s.match_id = :mid
  AND s.player_id IN (1, 2, 3)
ORDER BY s.high_speed_distance DESC;

2 球队对比

SELECT 
    t.name AS team_name,
    SUM(s.high_speed_distance) AS team_high_speed,
    AVG(s.high_speed_distance) AS avg_per_player,
    MAX(s.max_speed) AS team_max_speed
FROM player_match_stats s
JOIN players p ON p.id = s.player_id
JOIN teams t ON t.id = p.team_id
WHERE s.match_id = :mid
GROUP BY t.id;

3 时间段对比（15 分钟一档）

SELECT 
    player_id,
    FLOOR(TIMESTAMPDIFF(SECOND, :kickoff, ts) / 900) AS period,
    SUM(CASE WHEN speed >= 5.5 THEN 1 ELSE 0 END) AS hs_frames
FROM tracking_data
WHERE match_id = :mid
GROUP BY player_id, period
ORDER BY player_id, period;

Laravel 实现（如果是框架项目）

// app/Models/TrackingData.php
class TrackingData extends Model {}
// app/Services/RunDistanceService.php
namespace App\Services;
use App\Models\PlayerMatchStat;
use Illuminate\Support\Facades\DB;
class RunDistanceService
{
    public function compare(array $playerIds, int $matchId)
    {
        return PlayerMatchStat::query()
            ->join('players', 'players.id', '=', 'player_match_stats.player_id')
            ->where('match_id', $matchId)
            ->whereIn('player_id', $playerIds)
            ->select([
                'players.name',
                'player_match_stats.total_distance',
                'player_match_stats.high_speed_distance',
                'player_match_stats.sprint_distance',
                'player_match_stats.max_speed',
                DB::raw('ROUND(high_speed_distance / total_distance * 100, 2) as hs_ratio'),
            ])
            ->orderByDesc('high_speed_distance')
            ->get();
    }
}

控制器：

public function compare(Request $request, RunDistanceService $svc)
{
    $data = $svc->compare($request->input('player_ids'), $request->input('match_id'));
    return response()->json(['code' => 0, 'data' => $data]);
}

性能优化建议

问题 方案
tracking_data 上亿行 按 match_id 分表 / 分区
频繁聚合 用汇总表 player_match_stats，实时增量更新
实时对比 Redis 缓存 hs:{match_id}:{player_id}
单场高并发 使用 ClickHouse / TimescaleDB 存时序数据
导入大批量 LOAD DATA 或 INSERT ... VALUES 批量 5000/批

可对比的指标扩展

  • 高速跑动次数（次数 vs 距离）
  • 高速跑动占比（hs / total）
  • 有效比赛时间内的 HS 距离
  • 按位置（中卫/边锋）分组均值
  • 对手强度修正后的 HS

如果你告诉我具体环境（原生 PHP / Laravel / ThinkPHP，数据源是 GPS 背心 / 视频追踪 / 手动录入，采样频率多少），我可以给出更贴合的实现代码或者帮你写一个完整的对比接口 + 前端图表（ECharts）。

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

下一篇当前分类已是最新一篇

相关文章

抱歉，评论功能暂时关闭!