MySQL 統計過去12個月的資料(包括本月)，mysql本月

MySQL 統計過去12個月的資料(包括本月)，mysql本月
1、問題

需要統計過去12個月的資料，如現在是2015年4月，那麼我們需要統計從2014年5月到2015年4月的資料情況。而這12個月中，如果這個月沒有資料的，則統計為0。

未經處理資料表如，資料是按分鐘儲存的。也就是說要統計每個月一共有多少條body_infared大於0的資料。

2、處理過程

2.1 擷取12個月到現在的資料

select * from device_data t where DATE_FORMAT(t.time,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m');

2.2 統計某台裝置在過去12個月中每個月body_infared大於0的資料之和。

select DATE_FORMAT(t.time,'%Y-%m') month,count(t.id) minute from device_data twhere t.body_infrared>0 and t.device_id=13 AND  DATE_FORMAT(time,'%Y-%m')>DATE_FORMAT(date_sub(curdate(), interval 12 month),'%Y-%m') group by device_id,month

擷取結果：

這時候我們發現，未經處理資料中只有三個月的資料，還有其它9個月按要求應該是顯示為0的。

2.3 用一個笨點的方法擷取過去12個月所有的月份。
建立一個尋找過去12個月的視圖，當然這裡你也可以使用預存程序遍曆地把12個月插入到一個暫存資料表中，代碼會更優雅一點。

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

查看12個月的顯示情況：

select t.month from past_12_month_view t group by t.month;

2.4 把2.3的12個月的顯示資料與 2.2查詢結果關聯查詢

select v.month,ifnull(b.minute,0)  minute from past_12_month_view v left join(select DATE_FORMAT(t.time,'%Y-%m') month,count(t.id) 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)bon v.month = b.month group by v.month

最後得到我們想要的結果：