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

Source: Internet
Author: User

MySQL statistics for the past 12 months (including 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. <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> VcD4KCgoKPHByZSBjbGFzcz0 = "brush: SQL;"> 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:

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.