1. Questions
Statistics for the past 12 months are required, as it is now in April 2015, so we need to count data from May 2014 to April 2015. In the 12 months, if there is no data for this month, the statistic is 0.
Raw data tables, for example, data is stored in minutes. That is to count how many body_infared more than 0 data per month.
2. Processing process
2.1 Getting 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 Statistics The sum of data for a device that body_infared greater than 0 per month over the last 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 Span class= "Hljs-keyword" >by device_id,month
Get results:
At this point we found that the original data only three months of data, and the other 9 months as required should be displayed as 0.
2.3 Get all the months of the past 12 months with a stupid dot method.
Create a view that looks for the last 12 months, and of course you can use stored procedure traversal to insert 12 months into a temporary table, the code will be more elegant.
CREATEAlgorithm = UNDEFINED Definer =' Root '@`%` SQLSECURITY DefinerVIEW ' Past_12_month_view ' as SELECTDate_format (Curdate (),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 1 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 2 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 3 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 4 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 5 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 6 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 7 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 8 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL 9 MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL Ten MONTH),'%y-%m ') as ' Month ' UNION SELECTDate_format (Curdate ()-INTERVAL One MONTH),'%y-%m ') as ' Month '
See the 12-month display:
select t.month from past_12_month_view t group by t.month;
2.4 Correlate 2.3 of 12 months ' display data with 2.2 query results
SelectV.Month, Ifnull (b.minute,0)minute fromPast_12_month_view V Left Join(SelectDate_format (T. Time,'%y-%m ')Month,Count(t.id)minute fromDevice_data TwhereDate_format (T. Time,'%y-%m ') >date_format (Date_sub (Curdate (),interval A Month),'%y-%m ') andT.device_id= (SelectD.id fromDevice DwhereD.serial=' 01150100004 ')Group byT.DEVICE_ID,Month) b onV.Month= B.Month Group byV.Month
Finally get the result we want:
MySQL stats for the last 12 months (including this month)