Analysis of MySQL cumulative aggregation principle and usage examples

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

Cumulative aggregation aggregates data from the first element in the sequence to the current element, such as returning the cumulative number of orders and the average number of orders from the beginning of each month to the present for each employee.

There are two solutions to the row number problem, one is to use a subquery and the other is to use a join. The subquery method is usually more intuitive and readable. However, when aggregation is required, the subquery needs to scan the data once for each aggregation, while the connection method usually only needs to scan once to get the result. The following query uses a join to get the result

SELECT
 a.empid,
 a.ordermonth,a.qty AS thismonth,
 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 <= a.ordermonth
GROUP BY a.empid,a.ordermonth,a.qty
ORDER BY a.empid,a.ordermonth

If you only want to query the cumulative orders in 2015, you can add the where condition

WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'

The results are as follows

You may also want to filter the data, for example, to return only the monthly orders for each employee until a certain goal is reached. Here we assume that the total number of orders for each employee is counted before it reaches 1,000.

Here we can use the HAVING filter to complete the query

SELECT
 a.empid,
 a.ordermonth,a.qty AS thismonth,
 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 <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total < 1000
ORDER BY a.empid,a.ordermonth

The situation in that month when the number reaches 1000 is not counted here. If we want to do statistics, the situation will be a bit complicated. If total <= 1000 is specified, statistics will be taken only if the number of orders for that month is exactly 1000. Otherwise, statistics will not be taken for that month. Therefore, the filtering of this problem can be considered from another aspect. When the cumulative cumulative orders are less than 1000, the difference between the cumulative orders and the orders of the previous month is less than 1000. At the same time, the first month with an order quantity exceeding 1000 can also be counted. Therefore, the SQL statement for this solution is as follows

SELECT
 a.empid,
 a.ordermonth,a.qty AS thismonth,
 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 <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total-a.qty < 1000
ORDER BY a.empid,a.ordermonth

The results are as follows

If you only want to return the data for the month with a cumulative order count of 1000, and not the previous months, you can modify the above SQL statement.

Further filter and add the condition that the cumulative order quantity is greater than or equal to 1000. The SQL statement for this problem is as follows:

SELECT
 a.empid,
 a.ordermonth,a.qty AS thismonth,
 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 <= a.ordermonth
WHERE DATE_FORMAT(a.ordermonth,'%Y')='2015' AND DATE_FORMAT(b.ordermonth,'%Y')='2015'
GROUP BY a.empid,a.ordermonth,a.qty
HAVING total-a.qty < 1000 AND total >= 1000
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.