Analysis of the principle and usage of MySQL continuous aggregation

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

Continuous aggregation is the operation of aggregating ordered data in time order.

In the following examples, the EmpOrders table is used to store the order quantity of each employee each month.

Run the following code to create the EmpOrders table and populate it with sample data.

CREATE TABLE EmpOrders (
empid INT NOT NULL,
ordermonth DATE NOT NULL,
qty INT NOT NULL,test
PRIMARY KEY (empid,ordermonth)
);

Query the order table and orderdetails table and insert the orders for each month into the EmpOrder table. The SQL statement is as follows (the trick is to group by month)

INSERT INTO EmpOrders
SELECT a.employeeid,orderdate AS Order date,SUM(quantity) AS qty
FROM orders a
INNER JOIN orderdetails b
ON a.orderid=b.orderid
GROUP BY employid,DATE_FORMAT(orderdate,'%Y-m');

The following is the PHP file that generates sample data

<?php
$sql = "INSERT INTO emporders SELECT %s,'%s-%02d-01',%s;".'<br />';
$insert_sql = '';
for($empid=1;$empid<=8;$empid++)
{
  for($year=2009;$year<=2015;$year++)
  {
    for($month=1;$month<=12;$month++)
    {
      $num = rand(20,800);
      $insert_sql .= sprintf($sql,$empid,$year,$month,$num);
    }
    $insert_sql .= '<br />';
  }
}
echo $insert_sql;

The following is part of the data of the employee order table EmpOrder

The following discusses three continuous aggregation issues based on the EmpOrders table: cumulative, sliding, and year-to-date.

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.