MySQL sliding aggregation/year-to-date aggregation principle and usage example analysis

This article uses examples to illustrate the principles and usage of MySQL sliding aggregation/year-to-date aggregation. Share with you for your reference, the details are as follows:

Sliding aggregation is an operation that aggregates data within the sliding window range in sequence. Different from cumulative aggregation, sliding aggregation does not count the data from the starting position to the current position.

Here we take the example of counting the monthly orders of employees in the last three months to introduce sliding aggregation.

The main difference between the sliding aggregation and cumulative aggregation solutions lies in the different conditions for joining. The sliding aggregation condition is no longer b.ordermonth <= a.ordermonth, but b.ordermonth is greater than the months of the previous three months and less than the current month. Therefore, the SQL statement for the sliding aggregation solution is as follows

SELECT
 a.empid,
 DATE_FORMAT(a.ordermonth, '%Y-%m') AS ordermonth,
 a.qty AS this month,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
 ON a.empid=b.empid
 AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)
 AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,DATE_FORMAT(a.ordermonth, '%Y-%m'),a.qty
ORDER BY a.empid,a.ordermonth

The results are as follows

This solution returns a sliding aggregation with a three-month period, but each user includes the aggregation of the previous two months and less than 3 months. If you only want to return the aggregates that are more than 3 months old, but not the aggregates that are less than 3 months old, you can use the HAVING filter to filter. The filtering condition is MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL -2 MONTH), for example

SELECT
 a.empid,
 a.ordermonth AS ordermonth,
 a.qty AS this month,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
 ON a.empid=b.empid
 AND b.ordermonth > DATE_ADD(a.ordermonth, INTERVAL -3 MONTH)
 AND b.ordermonth <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015' AND a.empid=1
GROUP BY a.empid,DATE_FORMAT(a.ordermonth, '%Y-%m'),a.qty
HAVING MIN(b.ordermonth)=DATE_ADD(a.ordermonth, INTERVAL-2 MONTH)
ORDER BY a.empid,a.ordermonth

The results are as follows

The year-to-date aggregation is similar to the sliding aggregation, except that only the aggregation for the current year is counted. The only difference is where the lower limit starts. In the year-to-date problem, the lower bound is the first day of the year, while the lower bound for the sliding aggregation is the first day of N months. Therefore, the solution to the year-to-date problem is shown below, and the results obtained

SELECT
 a.empid,
 DATE_FORMAT(a.ordermonth, '%Y-%m') AS ordermonth,
 a.qty AS this month,
 SUM(b.qty) AS total,
 CAST(AVG(b.qty) AS DECIMAL(5,2)) AS avg
FROM emporders a
INNER JOIN emporders b
  ON a.empid=b.empid
  AND b.ordermonth >= DATE_FORMAT(a.ordermonth, '%Y-01-01')
  AND b.ordermonth <= a.ordermonth
  AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,a.ordermonth,a.qty
ORDER BY a.empid,a.ordermonth

The results are as follows

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.