MySQL 統計過去 12 個月數據（含無數據月份補零）

MySQL 統計過去 12 個月數據（含無數據月份補零）

業務常見需求：統計最近 12 個月每個月的指標（如某設備每分鐘上報數據中 body_infrared > 0 的筆數），若某月無數據需顯示 0 而非缺失。本文通過「月份基準表 + LEFT JOIN」優雅解決此問題。

場景與原始數據

表 device_data 按分鐘存儲，需按月統計某設備（device_id=13）符合條件的記錄數。直接 GROUP BY 只會返回有數據的月份，缺失月份不會出現在結果集中。

步驟 1：查詢有數據的月份統計

SELECT DATE_FORMAT(t.`time`, '%Y-%m') AS `month`, COUNT(t.id) AS `minute`
FROM device_data t
WHERE t.body_infrared > 0
  AND t.device_id = 13
  AND DATE_FORMAT(t.`time`, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m')
GROUP BY t.device_id, `month`;

步驟 2：構建過去 12 個月的基準視圖

用 UNION ALL 生成連續 12 個月份字串（格式 YYYY-MM），也可用存儲過程寫入臨時表：

CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW `past_12_month_view` AS
SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month` UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 2 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 3 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 4 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 5 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 7 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 8 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 9 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 10 MONTH, '%Y-%m') UNION ALL
SELECT DATE_FORMAT(CURDATE() - INTERVAL 11 MONTH, '%Y-%m');

步驟 3：LEFT JOIN 合併並補零

SELECT v.`month`, IFNULL(b.`minute`, 0) AS `minute`
FROM past_12_month_view v
LEFT JOIN (
  SELECT DATE_FORMAT(t.`time`, '%Y-%m') AS `month`, COUNT(t.id) AS `minute`
  FROM device_data t
  WHERE DATE_FORMAT(t.`time`, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 12 MONTH), '%Y-%m')
    AND t.device_id = (SELECT d.id FROM device d WHERE d.serial = '01150100004')
  GROUP BY t.device_id, `month`
) b ON v.`month` = b.`month`
GROUP BY v.`month`;

關鍵點總結

• 基準表（視圖/臨時表）確保 12 行完整月份；
• LEFT JOIN 保留基準表所有行；
• IFNULL 或 COALESCE 將 NULL 轉為 0。

MySQL 8.0 可用 WITH RECURSIVE 生成月份序列，無需建視圖，更輕量。