php项目统计慢跑恢复时间数据如何？

PHP项目统计慢跑恢复时间数据方案

慢跑恢复时间"统计，需要先明确具体指标定义，以下是完整的实现思路。

明确统计指标

慢跑恢复时间通常涉及以下维度：

数据库设计

-- 跑步记录表
CREATE TABLE run_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    distance DECIMAL(6,2),        -- 公里
    avg_pace INT,                 -- 平均配速(秒/公里)
    avg_heart_rate INT,
    max_heart_rate INT,
    resting_heart_rate INT,       -- 静息心率
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, start_time)
);
-- 心率采样表（用于精确计算恢复时间）
CREATE TABLE heart_rate_samples (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    run_id INT NOT NULL,
    sample_time DATETIME NOT NULL,
    heart_rate INT NOT NULL,
    is_recovery TINYINT DEFAULT 0,  -- 是否属于恢复阶段
    INDEX idx_run_time (run_id, sample_time)
);
-- 恢复统计结果表
CREATE TABLE recovery_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    run_id INT NOT NULL UNIQUE,
    user_id INT NOT NULL,
    hrr_1min INT,        -- 1分钟心率恢复
    hrr_2min INT,        -- 2分钟心率恢复
    recovery_seconds INT,-- 完全恢复耗时(秒)
    recovery_quality ENUM('excellent','good','fair','poor'),
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id)
);

核心统计逻辑（PHP）

<?php
class RecoveryCalculator
{
    private PDO $db;
    // 恢复判定阈值：心率降到静息心率的 110% 以内
    private const RECOVERY_THRESHOLD = 1.1;
    public function __construct(PDO $db)
    {
        $this->db = $db;
    }
    /**
     * 计算单次跑步的恢复时间
     */
    public function calculate(int $runId): array
    {
        // 1. 获取跑步基本信息
        $stmt = $this->db->prepare(
            "SELECT * FROM run_records WHERE id = ?"
        );
        $stmt->execute([$runId]);
        $run = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$run || !$run['resting_heart_rate']) {
            throw new RuntimeException('数据不完整');
        }
        // 2. 获取恢复阶段心率采样（结束后的记录）
        $stmt = $this->db->prepare(
            "SELECT sample_time, heart_rate 
             FROM heart_rate_samples 
             WHERE run_id = ? AND is_recovery = 1 
             ORDER BY sample_time ASC"
        );
        $stmt->execute([$runId]);
        $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (empty($samples)) {
            throw new RuntimeException('无恢复期数据');
        }
        $restingHR = (int)$run['resting_heart_rate'];
        $threshold = $restingHR * self::RECOVERY_THRESHOLD;
        $endTime = strtotime($run['end_time']);
        $peakHR  = (int)$run['max_heart_rate'];
        $hrr1 = null;
        $hrr2 = null;
        $recoverySeconds = null;
        // 3. 遍历采样，计算各项指标
        foreach ($samples as $s) {
            $elapsed = strtotime($s['sample_time']) - $endTime;
            $hr = (int)$s['heart_rate'];
            // 1 分钟心率恢复
            if ($hrr1 === null && $elapsed >= 60) {
                $hrr1 = $peakHR - $hr;
            }
            // 2 分钟心率恢复
            if ($hrr2 === null && $elapsed >= 120) {
                $hrr2 = $peakHR - $hr;
            }
            // 完全恢复时间：首次降到阈值以下
            if ($recoverySeconds === null && $hr <= $threshold) {
                $recoverySeconds = $elapsed;
                break;
            }
        }
        $quality = $this->evaluateQuality($hrr1, $recoverySeconds);
        // 4. 写入统计表
        $stmt = $this->db->prepare(
            "INSERT INTO recovery_stats 
                (run_id, user_id, hrr_1min, hrr_2min, recovery_seconds, recovery_quality)
             VALUES (?, ?, ?, ?, ?, ?)
             ON DUPLICATE KEY UPDATE
                hrr_1min=VALUES(hrr_1min),
                hrr_2min=VALUES(hrr_2min),
                recovery_seconds=VALUES(recovery_seconds),
                recovery_quality=VALUES(recovery_quality)"
        );
        $stmt->execute([
            $runId, $run['user_id'], $hrr1, $hrr2, $recoverySeconds, $quality
        ]);
        return compact('hrr1', 'hrr2', 'recoverySeconds', 'quality');
    }
    /**
     * 评估恢复质量（参考运动医学常规标准）
     */
    private function evaluateQuality(?int $hrr1, ?int $recoverySec): string
    {
        if ($hrr1 === null) return 'fair';
        // HRR1 >= 25 一般视为良好，12-25 中等，<12 较差
        if ($hrr1 >= 30 && $recoverySec !== null && $recoverySec <= 180) {
            return 'excellent';
        }
        if ($hrr1 >= 25) return 'good';
        if ($hrr1 >= 12) return 'fair';
        return 'poor';
    }
}

聚合统计查询

/**
 * 按周/月统计用户恢复趋势
 */
public function getUserTrend(int $userId, string $period = 'week'): array
{
    // period 白名单，防止 SQL 注入
    $groupExpr = match ($period) {
        'week'  => "YEARWEEK(r.start_time, 1)",
        'month' => "DATE_FORMAT(r.start_time, '%Y-%m')",
        'day'   => "DATE(r.start_time)",
        default => throw new InvalidArgumentException('无效周期'),
    };
    $sql = "SELECT 
                {$groupExpr} AS period_key,
                COUNT(*) AS run_count,
                AVG(s.hrr_1min) AS avg_hrr1,
                AVG(s.recovery_seconds) AS avg_recovery_sec,
                MIN(s.recovery_seconds) AS best_recovery_sec,
                SUM(CASE WHEN s.recovery_quality IN ('excellent','good') THEN 1 ELSE 0 END) 
                    / COUNT(*) AS good_rate
            FROM run_records r
            JOIN recovery_stats s ON s.run_id = r.id
            WHERE r.user_id = :uid
              AND r.start_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
            GROUP BY period_key
            ORDER BY period_key DESC";
    $stmt = $this->db->prepare($sql);
    $stmt->execute([':uid' => $userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

性能优化建议

1. 采样数据分表/分区：心率采样数据量大，按 run_id 或时间做分区
2. 异步计算：恢复统计放入队列（Redis/RabbitMQ）异步处理，避免阻塞主请求
3. 缓存聚合结果：周/月统计用 Redis 缓存，设 TTL 5 分钟
4. 索引优化：heart_rate_samples(run_id, is_recovery, sample_time) 联合索引
5. 预聚合表：海量数据可用每日汇总表代替实时 GROUP BY

注意事项

• 数据来源：需明确心率数据来源（GPS手表 / 手机传感器），采样频率一般 1-5 秒
• 静息心率基准：建议用长期平均值而非单次值，更准确
• 医学免责：恢复指标仅供参考，需注明"不构成医学建议"
• 边界情况：跑步中途异常心率、数据缺失、采样间隔不均匀都要处理

如果你能补充具体场景（比如是跑团管理后台、运动App后端还是个人训练日志？数据来自API还是手动录入？），我可以给出更贴合的实现。