MySQL 按固定時間間隔分組統計：每半小時平均值 SQL 實現與優化

業務常需按固定時間窗口（如每 5 分鐘、每半小時、每小時）聚合統計。MySQL 利用時間戳數學運算構造分組鍵，配合索引可高效完成。本文提供兩種核心實現方式、索引建議、大數據量優化方案。

一、表結構

CREATE TABLE `instance` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `timestamp` INT UNSIGNED DEFAULT NULL,  -- Unix 時間戳
    `cpu` DECIMAL(8,3) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB;

二、核心實現：時間戳取模分組

方法 1：Unix 時間戳取模（最高效）

-- 每 5 分鐘 (300 秒) 平均值
SELECT `timestamp` - `timestamp` % 300 AS time_bucket, AVG(`cpu`) AS avg_cpu
FROM `instance`
GROUP BY time_bucket
ORDER BY time_bucket;

原理： 同一時間窗口內的時間戳對窗口秒數取模結果相同，作為分組鍵。

方法 2：日期時間函數構造（可讀性強）

-- 每半小時統計
SELECT
    queueName,
    HOUR(`dt`) AS h,
    FLOOR(MINUTE(`dt`) / 30) AS v,
    AVG(memoryUsed) AS avg_mem,
    SUM(memoryUsed) AS sum_mem,
    COUNT(*) AS cnt
FROM queue_resource
WHERE `dt` >= '2014-04-14 00:00:00'
GROUP BY queueName, h, v
ORDER BY queueName, h, v;

適用： DATETIME/TIMESTAMP 型別欄位，業務邏輯清晰。

三、索引優化策略

1. 單欄索引： KEY idx_timestamp (timestamp) — 適用方法 1，掃描索引即可完成分組。
2. 聯合索引（覆蓋）： KEY idx_dt_queue (dt, queueName, memoryUsed) — 適用方法 2，實現索引覆蓋，避免回表。
3. 分區表： 按 timestamp RANGE 分區（如按月），配合分區裁剪大幅減少掃描範圍。

四、大數據量優化方案

五、避坑指南

• 時區一致性： 確認 timestamp 存儲為 UTC，應用層統一處理時區轉換。
• 邊界包含： WHERE dt >= '2024-01-01' AND dt < '2024-01-02' 避免跨日重複/遺漏。
• NULL 值處理： 聚合函數自動忽略 NULL，但 COUNT(*) 計入 NULL 行，COUNT(col) 不計入。
• 精度陷阱： FLOAT/DOUBLE 聚合有精度誤差，金額/關鍵指標用 DECIMAL。

六、總結

時間窗口分組統計核心是「構造確定性分組鍵」。Unix 時間戳取模效能最優，日期函數可讀性最佳。配合覆蓋索引、分區表、摘要表分層架構，可支撐從萬級到億級的時序聚合查詢需求。