WriteAhead Sprint 4

SCRUM-67 核心數據抽取實作分析

閱讀時間約 10 分鐘

SCRUM-67 核心數據抽取實作分析

JIRA: SCRUM-67 Parent: SCRUM-14 (後台－數值管理頁) 難度: ⭐⭐⭐ (中等) 預估工作量: 2-3 天 依賴: 無（為 SCRUM-43、SCRUM-68 的前置項）

功能需求摘要

SCRUM-67 負責後台統計系統的核心數據抽取邏輯，為 SCRUM-43（前端數據監控頁）和 SCRUM-68（圖表數據）提供數據基礎。

10+ 核心指標

#指標計算方式現有狀態
1活躍人數COUNT(DISTINCT user_email) from logs_user_logins✅ 已實作
2做題總數COUNT(*) from answer WHERE status=1✅ 已實作
3平均做題數做題總數 ÷ 活躍人數✅ 已實作
4平均得分AVG(score) WHERE is_review=1✅ 已實作
5平均解題時間AVG(times)✅ 已實作
6題目平均使用數已批閱答題數 ÷ 題庫總數✅ 已實作
7題目最大使用數GROUP BY question_bank_id ORDER BY COUNT DESC✅ 已實作
8題目最小使用數GROUP BY question_bank_id ORDER BY COUNT ASC✅ 已實作
9新增未批改總數COUNT(*) WHERE is_review=0✅ 已實作
10平均未批改數未批改數 ÷ 活躍人數✅ 已實作

時間篩選

選項範圍現有狀態
全部不篩選✅ 已實作
1日今天 00:00:00 ~ 現在✅ 已實作
1月本月 1 日 ~ 現在✅ 已實作
1年今年 1/1 ~ 現在✅ 已實作
自定義start_date ~ end_date✅ 已實作

現有程式碼分析

API 端點

POST /admin/statistics/getStatistics
權限: permission:statistics,view

關鍵檔案

檔案路徑狀態
Controller/home/matt/Github/writeahead-main-api/app/Controllers/Admin/StatisticsController.php✅ 已實作
Model/home/matt/Github/writeahead-main-api/app/Models/Admin/StatisticsModel.php⚠️ 需優化
Routes/home/matt/Github/writeahead-main-api/app/Config/Routes.php✅ 已設定

涉及資料表

資料表用途關鍵欄位
answer學生作答記錄id, question_bank_id, user_email, score, times, ai_score, status, is_review, created_at
question題庫id, name, type_id, issue_id, status
logs_user_logins使用者登入記錄user_email, created_at

深度分析：現有程式碼問題

問題 1：重複建立 DB 連線

每個方法獨立呼叫 db_connect()，一次 getStatistics() 會建立 10+ 次連線：

// 每個方法都有這行
$db = db_connect();

影響: 效能浪費，尤其在高併發場景 建議: 在 constructor 或 getStatistics() 開頭建立一次連線，作為參數傳入各方法

問題 2：getMinQuestionUsage() 遺漏零使用題目

目前只查 answer 表中有記錄的題目，使用次數為 0 的題目完全被忽略：

// 現有做法：只能找到有被使用過的最小值
$builder = $db->table('answer');
$builder->groupBy('answer.question_bank_id');
$builder->orderBy('usage_count', 'ASC');

建議: 改用 question LEFT JOIN answer 找出真正最低使用（包含 0 次）的題目

問題 3：getAvgAnswersPerUser() SQL 層除零風險

$builder->select('COUNT(*) / COUNT(DISTINCT user_email) as avg_count');

當 answer 表在篩選範圍內無資料時，COUNT(DISTINCT user_email) = 0，SQL 會除以零。雖然 MySQL 會回傳 NULL 而不會 crash，但語義不夠嚴謹。

建議: 使用 NULLIF 或在 PHP 層計算

問題 4：缺少資料庫索引

目前沒有發現任何自訂索引。以下查詢模式頻繁出現但無索引覆蓋：

查詢模式頻率影響
answer.status + is_review + created_at每次統計全表掃描
answer.user_email + status人均計算全表掃描
answer.question_bank_id + status題目使用統計全表掃描
logs_user_logins.created_at + user_email活躍用戶全表掃描

問題 5：無快取機制

每次請求都即時計算全部 10 個指標，當資料量增長後會有顯著效能問題。

問題 6：getAvgQuestionUsage() 語義不清

// 命名為「平均題目使用數」，但實際計算的是「批閱覆蓋率」
$numerator = 已批閱答題數;
$denominator = 有效題目數;

現狀: 回傳的是一個比率（如 15.5），不是直覺的「平均每題被做幾次」 需確認: 這是 PM 要的計算方式，還是應該改為 COUNT(*) / COUNT(DISTINCT question_bank_id)

實作 Checklist

第一階段：程式碼優化（必做）

  • 統一資料庫連線

    • 檔案: app/Models/Admin/StatisticsModel.php
    • 在 getStatistics() 開頭建立 $db = db_connect()
    • 將 $db 作為參數傳入所有 private 方法
    • 避免 10+ 次重複 db_connect()

  • 修復 getMinQuestionUsage() 遺漏零使用題目

    • 檔案: app/Models/Admin/StatisticsModel.php
    • 改用 question LEFT JOIN answer 查詢
    • 使 usage_count=0 的題目也能被偵測到
    • SQL 範例: 
      SELECT q.id, q.name, COUNT(a.id) as usage_count
FROM question q
LEFT JOIN answer a ON q.id = a.question_bank_id
  AND a.status = 1
  AND (a.created_at >= ? OR ? IS NULL)
  AND (a.created_at <= ? OR ? IS NULL)
WHERE q.status != 0
GROUP BY q.id
ORDER BY usage_count ASC
LIMIT 1

  • 修復 getAvgAnswersPerUser() 除零防護

    • 檔案: app/Models/Admin/StatisticsModel.php
    • 改為 PHP 層分開查詢再計算，或使用 NULLIF
    • 確保空資料時回傳 0 而非 NULL

第二階段：效能優化（強烈建議）

  • 新增資料庫索引

    • 建立 migration 檔案
    • 索引清單: 
      -- 統計查詢最常用的複合索引
CREATE INDEX idx_answer_status_review_created
  ON answer(status, is_review, created_at);
 
-- 使用者維度查詢
CREATE INDEX idx_answer_user_status_created
  ON answer(user_email, status, created_at);
 
-- 題目使用統計
CREATE INDEX idx_answer_question_status
  ON answer(question_bank_id, status);
 
-- 登入記錄查詢
CREATE INDEX idx_logins_created_user
  ON logs_user_logins(created_at, user_email);

  • 加入 Redis 快取機制

    • 確認專案是否已安裝 Redis 擴展
    • 設計快取 key: stats:{time_range}:{start}:{end}
    • 設定 TTL: 5-15 分鐘（視資料即時性需求）
    • 在 getStatistics() 加入快取讀取/寫入邏輯
    • 可先實作簡單的檔案快取作為替代方案

  • 合併 SQL 查詢減少 round-trip

    • 將多個 answer 表的統計合併為 1-2 條 SQL: 
      SELECT
  COUNT(*) as total_answers,
  COUNT(DISTINCT user_email) as unique_users,
  AVG(CASE WHEN is_review = 1 THEN score END) as avg_score,
  AVG(times) as avg_time,
  SUM(CASE WHEN is_review = 0 THEN 1 ELSE 0 END) as total_unreviewed,
  SUM(CASE WHEN is_review = 1 THEN 1 ELSE 0 END) as total_reviewed
FROM answer
WHERE status = 1
  AND (created_at >= :start OR :start IS NULL)
  AND (created_at <= :end OR :end IS NULL)
    • 可從 10+ 條 SQL 減少至 3-4 條

第三階段：擴展功能（視需求）

  • 確認 getAvgQuestionUsage() 語義

    • 與 PM 確認：是要「批閱覆蓋率」還是「平均每題使用次數」
    • 若為後者，改為 COUNT(*) / COUNT(DISTINCT question_bank_id)

  • 新增 getChartData() 支援 (SCRUM-68 前置)

    • 預留時間序列數據方法的介面
    • 方便 SCRUM-68 直接擴展

  • 新增單元測試

    • 測試各指標計算邏輯
    • 測試空資料邊界情況
    • 測試時間範圍篩選正確性

  • 更新 API 文件

    • 記錄各指標的計算公式
    • 記錄回傳格式範例
    • 記錄可能的邊界值

第四階段：驗證

  • 各指標在「全部」範圍下回傳正確數值
  • 各時間範圍（日/月/年/自定義）篩選正確
  • 空資料時不出現 NaN / NULL / 錯誤
  • getMinQuestionUsage 能正確回傳 0 次使用的題目
  • 索引加入後查詢效能提升（可用 EXPLAIN 驗證）
  • 快取命中率正常，TTL 過期後能正確更新

API 回傳格式

現有格式

{
  "code": 200,
  "data": {
    "active_users": 150,
    "total_answers": 3200,
    "avg_answers_per_user": 21.33,
    "avg_score": 14.75,
    "avg_time": 1823.50,
    "avg_question_usage": 15.24,
    "max_question_usage": {
      "question_id": 42,
      "question_name": "Environment Essay",
      "usage_count": 89
    },
    "min_question_usage": {
      "question_id": 17,
      "question_name": "Technology Discussion",
      "usage_count": 2
    },
    "total_unreviewed": 45,
    "avg_unreviewed_per_user": 0.30
  }
}

請求格式

{
  "time_range": "month",
  "start_date": "2026-01-01",
  "end_date": "2026-01-31"
}

start_date / end_date 僅在 time_range = "custom" 時使用

風險與待確認事項

#項目優先級狀態
1getAvgQuestionUsage() 語義確認高❓ 待確認
2資料庫是否有既有索引（需 DBA 確認）高❓ 待確認
3Redis 是否已部署可用中❓ 待確認
4資料量級（目前 answer 表有多少筆）中❓ 待確認
5零使用題目是否需要顯示在 min 統計中中❓ 待確認

開發優先級建議

Day 1: 第一階段（程式碼優化 + bug 修復）
        → 統一連線、修復 min 遺漏、除零防護
Day 2: 第二階段（效能優化）
        → 加索引、合併 SQL、評估快取需求
Day 3: 第三階段 + 第四階段（擴展 + 驗證）
        → 確認語義、寫測試、更新文件

相關筆記

更新紀錄

日期更新內容
2026-02-06初始深度分析，建立 checklist，識別 6 個程式碼問題

關係圖譜

反向連結