MySQL statistics for the past 12 months (including this month), mysql this month

MySQL statistics for the past 12 months (including this month), mysql this month
1. Problems

We need to collect statistics for the past 12 months. For example, we need to collect statistics from. In the past 12 months, if there is no data for this month, the statistical value is 0.

Raw data tables, for example, are stored in minutes. That is to say, we need to count the total number of body_infared records greater than 0 each month.

2. handling process

2.1 obtain data from 12 months to the present

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

2.2 calculate the sum of the monthly body_infared data of a device greater than 0 in the past 12 months.

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

Result:

At this time, we found that the raw data is only three months, and the other nine months should be displayed as 0 as required.

2.3 use a stupid method to retrieve all the months in the past 12 months.
Create a view for the past 12 months. Of course, you can use the stored procedure to traverse and insert 12 months into a temporary table. The Code will be more elegant.

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`

View the display information for 12 months:

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

2.4 associate the displayed data of 2.3 for 12 months with the query result of 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

Finally, we can get the expected results: