MySQL 固定時間窗口聚合：半小時平均值計算 SQL 寫法

MySQL 固定時間窗口聚合：半小時平均值計算

業務常需按固定間隔（如 5 分鐘、30 分鐘、1 小時）對時間序列資料做聚合。本文提供兩種通用寫法，適用於整數時間戳與 DATETIME 欄位。

場景一：整數 Unix Timestamp 欄位

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

每 5 分鐘（300 秒）平均值

SELECT 
  `timestamp` - `timestamp` % 300 AS window_start,
  AVG(`cpu`) AS avg_cpu
FROM `instance`
GROUP BY window_start
ORDER BY window_start;

原理：取模運算將時間戳對齊到最近的窗口起點（向下取整）。

場景二：DATETIME / TIMESTAMP 欄位

SELECT 
  queueName,
  HOUR(`dt`) AS h,
  FLOOR(MINUTE(`dt`) / 30) AS half_hour_bucket,
  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, half_hour_bucket
ORDER BY queueName, h, half_hour_bucket;

關鍵點：

• FLOOR(MINUTE(dt) / 30) 產生 0/1 兩個桶，代表 0~29 分、30~59 分
• 配合 HOUR(dt) 組成完整 30 分鐘窗口
• 若需跨天，建議改用 UNIX_TIMESTAMP(dt) DIV 1800 統一窗口 ID

通用模板：任意秒數窗口

-- 整數時間戳
SELECT 
  ts - ts % :window_sec AS window_start,
  AVG(val) AS avg_val
FROM metrics
GROUP BY window_start;

-- DATETIME 欄位
SELECT 
  DATE_FORMAT(dt, '%Y-%m-%d %H:00:00') + INTERVAL FLOOR(MINUTE(dt) / :interval_min) * :interval_min MINUTE AS window_start,
  AVG(val) AS avg_val
FROM metrics
GROUP BY window_start;

效能建議

1. 時間欄位建立索引：INDEX idx_dt (dt)
2. 大表考慮分區表（RANGE 按天/月分區）
3. 頻繁查詢可建立物化匯總表，定時任務刷新
4. MySQL 8.0+ 可使用 WINDOW FUNCTION 配合 RANGE BETWEEN 實現滑動窗口