MySQL 多表聚合寫入總表：LEFT JOIN 分組聚合與 INSERT ... ON DUPLICATE KEY UPDATE 完整方案

需求分析

• 源表：`realtime(send_time, range, oil)`、`gps(send_time, speed)`，每 10 秒一條，一一對應。
• 目標表：`sum(year, month, day, hour, range_diff, avg_oil, max_speed, avg_speed)`，組合主鍵 `(year,month,day,hour)`，按小時聚合。

核心 SQL：單語句聚合 + 寫入

INSERT INTO `sum` (`year`, `month`, `day`, `hour`, `range_diff`, `avg_oil`, `max_speed`, `avg_speed`)
SELECT 
    YEAR(r.`send_time`) AS `year`,
    MONTH(r.`send_time`) AS `month`,
    DAY(r.`send_time`) AS `day`,
    HOUR(r.`send_time`) AS `hour`,
    MAX(r.`range`) - MIN(r.`range`) AS `range_diff`,
    AVG(r.`oil`) AS `avg_oil`,
    MAX(g.`speed`) AS `max_speed`,
    AVG(g.`speed`) AS `avg_speed`
FROM `realtime` r
INNER JOIN `gps` g ON r.`send_time` = g.`send_time`  -- 一一對應，用內連接
GROUP BY `year`, `month`, `day`, `hour`
ON DUPLICATE KEY UPDATE
    `range_diff` = VALUES(`range_diff`),
    `avg_oil` = VALUES(`avg_oil`),
    `max_speed` = VALUES(`max_speed`),
    `avg_speed` = VALUES(`avg_speed`);

關鍵點解析

1. 時間維度提取：`YEAR()`/`MONTH()`/`DAY()`/`HOUR()` 函數直接從 `send_time` 衍生分組鍵，無需應用層計算。
2. 內連接而非左連接：題目說「每條數據一一對應」，`INNER JOIN` 確保雙方都存在，數據質量更高。若允許缺失再用 `LEFT JOIN`。
3. 聚合函數：`MAX-MIN` 計算極差，`AVG` 計算平均，`MAX` 計算峰值。
4. 組合主鍵衝突處理：`ON DUPLICATE KEY UPDATE` 實現 **Upsert (Insert or Update)**，避免主鍵衝突報錯，支持定時任務重跑。
5. VALUES(col)：引用 `INSERT` 部分計算出的值，避免重複寫聚合表達式。

定時任務調度建議

-- MySQL Event Scheduler (數據庫層定時，無需外部依賴)
SET GLOBAL event_scheduler = ON;

CREATE EVENT `etl_hourly_sum`
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE -- 錯開整點寫入高峰
DO
    INSERT INTO `sum` ... (上述 SQL) ... ;

大數據量優化策略

應用層實現 (Java/JDBC 參考)

String sql = "INSERT INTO `sum` ... SELECT ... FROM `realtime` r JOIN `gps` g ON r.send_time = g.send_time "
           + "WHERE r.send_time > ? AND r.send_time <= ? " // 增量時間窗
           + "GROUP BY YEAR(r.send_time), MONTH(r.send_time), DAY(r.send_time), HOUR(r.send_time) "
           + "ON DUPLICATE KEY UPDATE ...";

try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setTimestamp(1, lastProcessedTime);
    ps.setTimestamp(2, currentWindowEnd);
    ps.executeUpdate();
    // 更新 lastProcessedTime
}

總結

**「單條 SQL 完成 JOIN、分組聚合、Upsert 寫入」** 是 MySQL 處理此類定時彙總任務的最高效模式。利用 `ON DUPLICATE KEY UPDATE` 實現冪等寫入，配合事件調度器或應用層定時任務，構建穩健、高性能的數據彙總管道。