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

Source: Internet
Author: User

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.