如何基于问题发布与首答时间计算单日平均首次响应时间

问题原因 #

现有逻辑的核心问题是SQL查询会返回每个问题对应的所有回答记录，同一问题的多条回答会被重复计入统计，导致统计分母偏大、最终平均响应时间偏低。

最优修复方案（SQL层过滤，效率更高） #

直接在查询阶段就过滤出每个问题的最早回答记录，减少无效数据传输，也避免PHP侧额外判断：

修正后的SQL逻辑

先对回答表按QuestionId分组，取每组最小的Created_at即为该问题的首次回答时间，再关联问题表计算时间差。

完整修正后代码

// 初始化变量避免未定义警告
$qdiff_total = 0;
$count = 0;
$avgResponseTime = 0;

// 注意时区适配：如果数据库存的是东八区时间，用date即可，gmdate为格林威治时间会差8小时
$today_start = date('Y-m-d 00:00:00');

// 预编译SQL避免SQL注入风险
$stmt = $mysqli->prepare("
    SELECT 
        a.Created_at AS qtime,
        MIN(b.Created_at) AS first_atime
    FROM tb_addquestion AS a 
    INNER JOIN tb_answer b ON a.QuestionId = b.QuestionId 
    -- 若需要统计当天有回答的所有问题（不管问题创建时间），把下面条件改为 b.Created_at >= ? 即可
    WHERE a.Created_at >= ?
    GROUP BY a.QuestionId, a.Created_at
");
$stmt->bind_param('s', $today_start);
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $qtime = strtotime($row['qtime']);
        $first_atime = strtotime($row['first_atime']);
        // 过滤异常数据：回答时间早于提问时间的脏数据
        if ($first_atime > $qtime) {
            $qdiff_total += $first_atime - $qtime;
            $count++;
        }
    }
    if ($count > 0) {
        $avg_seconds = $qdiff_total / $count;
        $avgResponseTime = gmdate('H:i:s', $avg_seconds);
    }
}

$stmt->close();

备用方案（PHP层过滤，不推荐） #

如果不方便修改SQL，可以在PHP侧用数组标记已经统计过的问题，跳过同一问题的后续回答：

// 在原有循环外新增标记数组
$counted_qids = [];
while ($row_avgresTime = mysqli_fetch_assoc($select_avgresponsetime)) {
    $qid = $row_avgresTime['QuestionId'];
    // 已统计过的问题直接跳过
    if (isset($counted_qids[$qid])) {
        continue;
    }
    $counted_qids[$qid] = true;

    // 原有计算逻辑保留即可，注意补充$qdiff_total初始化、删除多余的$b = +$a代码
}

原有代码的其他优化点 #

1. 原代码未初始化$qdiff_total变量，首次运行会抛出未定义变量警告
2. 原代码中的$b = +$a属于无效代码，直接用计数变量$a即可
3. 原WHERE条件用OR会把前一天创建、当天回答的问题也纳入统计，可根据业务需求调整判断逻辑

内容的提问来源于stack exchange，提问作者Sandhya Srishti