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

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

慢跑恢复时间（通常指运动后心率恢复到静息水平的时间）是一个常见的运动健康统计需求,下面给你一套完整的实现思路。

数据模型设计

核心表结构（MySQL）

-- 运动记录表
CREATE TABLE running_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    start_time DATETIME NOT NULL,           -- 开始时间
    end_time DATETIME NOT NULL,             -- 结束时间
    distance DECIMAL(10,2) DEFAULT 0,       -- 距离(km)
    avg_pace INT DEFAULT 0,                 -- 平均配速(秒/km)
    avg_heart_rate TINYINT UNSIGNED,        -- 平均心率
    max_heart_rate TINYINT UNSIGNED,        -- 最大心率
    end_heart_rate TINYINT UNSIGNED,        -- 结束即时心率
    recovery_seconds INT DEFAULT NULL,      -- 恢复时间(秒)★核心
    recovery_heart_rate TINYINT UNSIGNED,   -- 恢复后心率
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, start_time),
    INDEX idx_recovery (user_id, recovery_seconds)
);
-- 心率采样表（用于精确计算恢复时间）
CREATE TABLE heart_rate_samples (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    record_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    bpm TINYINT UNSIGNED NOT NULL,
    sampled_at DATETIME NOT NULL,
    INDEX idx_record_time (record_id, sampled_at)
);

恢复时间计算逻辑

class RecoveryCalculator
{
    /** 静息心率（可用用户近7天晨起平均值） */
    private int $restingHr;
    /** 恢复阈值：心率降至静息+20或低于最大心率65% */
    private int $threshold;
    public function __construct(int $restingHr, int $maxHr)
    {
        $this->restingHr = $restingHr;
        $this->threshold = max($restingHr + 20, (int)($maxHr * 0.65));
    }
    /**
     * 根据心率采样序列计算恢复时间（秒）
     * @param array $samples [['bpm'=>120,'ts'=>1699999999], ...] 按时间升序
     */
    public function calculate(array $samples): ?int
    {
        if (count($samples) < 2) return null;
        $startTs = $samples[0]['ts'];
        foreach ($samples as $s) {
            if ($s['bpm'] <= $this->threshold) {
                return $s['ts'] - $startTs;
            }
        }
        return null; // 未恢复到阈值
    }
}

统计查询场景

场景1：用户恢复时间趋势（按月）

public function monthlyTrend(int $userId, string $month): array
{
    $sql = "SELECT 
                DATE(start_time) AS day,
                AVG(recovery_seconds) AS avg_recovery,
                MIN(recovery_seconds) AS best_recovery,
                COUNT(*) AS run_count
            FROM running_records
            WHERE user_id = :uid
              AND recovery_seconds IS NOT NULL
              AND start_time >= :start
              AND start_time < :end
            GROUP BY DATE(start_time)
            ORDER BY day";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([
        ':uid'   => $userId,
        ':start' => $month . '-01 00:00:00',
        ':end'   => date('Y-m-d', strtotime("$month-01 +1 month")) . ' 00:00:00',
    ]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

场景2：恢复能力评估（对比前后期）

public function comparePeriods(int $userId, string $p1Start, string $p1End, string $p2Start, string $p2End): array
{
    $sql = "SELECT 
                CASE 
                    WHEN start_time BETWEEN :p1s AND :p1e THEN 'before'
                    ELSE 'after'
                END AS period,
                AVG(recovery_seconds) AS avg_recovery,
                AVG(avg_heart_rate)   AS avg_hr
            FROM running_records
            WHERE user_id = :uid
              AND recovery_seconds IS NOT NULL
              AND (start_time BETWEEN :p1s AND :p1e 
                OR start_time BETWEEN :p2s AND :p2e)
            GROUP BY period";
    // ... 参数绑定
}

场景3：恢复速度分级统计

SELECT 
    CASE 
        WHEN recovery_seconds < 60  THEN '优秀(<1min)'
        WHEN recovery_seconds < 180 THEN '良好(1-3min)'
        WHEN recovery_seconds < 300 THEN '一般(3-5min)'
        ELSE '偏慢(>5min)'
    END AS level,
    COUNT(*) AS cnt,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM running_records
WHERE user_id = ? AND recovery_seconds IS NOT NULL
GROUP BY level;

性能优化建议

汇总表设计

CREATE TABLE recovery_daily_stats (
    user_id BIGINT UNSIGNED,
    stat_date DATE,
    avg_recovery INT,
    min_recovery INT,
    run_count INT,
    updated_at TIMESTAMP,
    PRIMARY KEY(user_id, stat_date)
);

通过定时任务（Crontab + PHP 脚本）或消息队列异步更新。

输出示例（API 返回）

{
  "user_id": 1001,
  "period": "2024-11",
  "summary": {
    "avg_recovery_seconds": 156,
    "best_recovery_seconds": 68,
    "worst_recovery_seconds": 420,
    "total_runs": 18
  },
  "trend": [
    {"day": "2024-11-01", "avg_recovery": 180, "run_count": 1},
    {"day": "2024-11-03", "avg_recovery": 145, "run_count": 2}
  ],
  "level_distribution": {
    "优秀": 4,
    "良好": 9,
    "一般": 4,
    "偏慢": 1
  }
}

关键注意点

1. 恢复时间定义要统一：是降到静息心率，还是降到"静息+20"？建议用后者,否则数据永远不达标。
2. 静息心率动态化：用近7天晨起心率均值，比固定值 60 bpm 更科学。
3. 异常值过滤：recovery_seconds < 10 或 > 3600 视为无效（传感器误差）。
4. 缺失处理：recovery_seconds IS NULL 的记录不应参与 AVG,否则会拉低统计。
5. 时区问题：start_time 存 UTC，展示时转换,避免按天分组错乱。

需要我针对具体某块展开吗？

• 心率采样的实时计算（PHP + Redis 流式处理）
• 对接运动手表 API（Garmin/华为/Apple Health 数据导入）
• 可视化图表（ECharts 前端联动）
• 分表/分区实施细节