MySQL stats for the last 12 months (including this month)

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)