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

wen PHP项目 2026-10-14 4

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

你这个问题有点模糊,我先按最常见的理解来回答：在一个 PHP 项目中，如何统计"慢跑（jogging）后恢复时间"这类数据，如果你指的是别的（比如服务器性能恢复、任务重试恢复），可以再补充说明。

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

数据模型设计

假设业务是记录用户每次慢跑后的心率恢复时间（运动后心率降到静息值所需时间）。

CREATE TABLE jog_recovery (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NOT NULL,
    run_date      DATE            NOT NULL,
    distance_km   DECIMAL(5,2)    NOT NULL,   -- 跑步距离
    duration_sec  INT             NOT NULL,   -- 跑步时长
    avg_hr        SMALLINT        NOT NULL,   -- 平均心率
    peak_hr       SMALLINT        NOT NULL,   -- 峰值心率
    rest_hr       SMALLINT        NOT NULL,   -- 静息心率
    recovery_sec  INT             NOT NULL,   -- 恢复时间(秒)
    created_at    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_date (user_id, run_date)
) ENGINE=InnoDB;

关键指标 recovery_sec = 跑步结束后心率回落到静息值所需秒数。

核心统计 SQL

用户平均恢复时间

SELECT 
    user_id,
    COUNT(*)                AS runs,
    AVG(recovery_sec)       AS avg_recovery,
    MIN(recovery_sec)       AS best_recovery,
    MAX(recovery_sec)       AS worst_recovery
FROM jog_recovery
WHERE user_id = ?
  AND run_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY user_id;

按周趋势

SELECT 
    YEARWEEK(run_date, 1) AS week,
    ROUND(AVG(recovery_sec), 1) AS avg_recovery,
    COUNT(*) AS runs
FROM jog_recovery
WHERE user_id = ?
GROUP BY week
ORDER BY week DESC
LIMIT 12;

恢复效率（恢复时间 / 跑步强度）

SELECT 
    run_date,
    distance_km,
    recovery_sec,
    ROUND(recovery_sec / (distance_km * 60), 2) AS recovery_per_km_min
FROM jog_recovery
WHERE user_id = ?
ORDER BY run_date DESC;

分位数（用窗口函数，MySQL 8+）

SELECT 
    user_id,
    AVG(recovery_sec) AS avg_r,
    MAX(CASE WHEN pct <= 0.5 THEN recovery_sec END) AS p50,
    MAX(CASE WHEN pct <= 0.9 THEN recovery_sec END) AS p90
FROM (
    SELECT user_id, recovery_sec,
           PERCENT_RANK() OVER (PARTITION BY user_id ORDER BY recovery_sec) AS pct
    FROM jog_recovery
    WHERE run_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
) t
GROUP BY user_id;

PHP 实现（PDO）

function getRecoveryStats(PDO $pdo, int $userId, int $days = 30): array
{
    $sql = "SELECT 
                COUNT(*) AS runs,
                ROUND(AVG(recovery_sec), 1) AS avg_recovery,
                MIN(recovery_sec) AS best,
                MAX(recovery_sec) AS worst
            FROM jog_recovery
            WHERE user_id = :uid
              AND run_date >= DATE_SUB(CURDATE(), INTERVAL :days DAY)";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':uid', $userId, PDO::PARAM_INT);
    $stmt->bindValue(':days', $days, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
}
// 使用
$stats = getRecoveryStats($pdo, 1001, 30);
echo "30天平均恢复时间: {$stats['avg_recovery']} 秒";

性能优化建议

场景	方案
单用户小数据量	直接 SQL 聚合
百万级数据	建汇总表 `jog_recovery_daily(user_id, date, avg_sec, runs)`，定时任务跑
实时看板	Redis 缓存近期统计，写入时更新
复杂分析	数据同步到 ClickHouse / Doris

汇总表定时任务（每天凌晨）：

$pdo->exec("
    INSERT INTO jog_recovery_daily (user_id, stat_date, avg_sec, runs)
    SELECT user_id, run_date, AVG(recovery_sec), COUNT(*)
    FROM jog_recovery
    WHERE run_date = CURDATE() - INTERVAL 1 DAY
    GROUP BY user_id, run_date
    ON DUPLICATE KEY UPDATE avg_sec = VALUES(avg_sec), runs = VALUES(runs)
");