MySQL Continuous aggregation

Continuous aggregation is an operation that aggregates ordered data in chronological order.

In the following save example, the EmpOrders table is used to hold the ordered quantity that occurs per employee each month. Run the following code to create the EmpOrders table and populate the sample data.

CREATE TABLE emporders (empid int not null,ordermonth DATE not null,qty INT not null,testprimary KEY (empid,ordermonth));

Query the Order table and OrderDetails table live Emporder table inserts orders for each month, the SQL statement is as follows (technique is grouped by month)

INSERT into EmpOrders SELECT a.employeeid,orderdate as Order date,sum (quantity) as Qty from orders Ainner JOIN OrderDetail S BON a.orderid=b.orderidgroup by Employid,date_format (OrderDate, '%y-m ');

The PHP file that generates the sample data is given below

<?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 an employee order form Emporder part of the data below discusses the issues of 3 consecutive aggregations according to the EmpOrders table: cumulative, sliding, year-to-date.

MySQL Continuous aggregation