MySQL Slide aggregation

Sliding aggregations are operations that aggregate data in a sliding window range sequentially. Under cumulative aggregations, sliding aggregation is not the data that counts to the current position of the location where the calculation begins.

Here to statistics of monthly orders of employees in the last three monthsFor example, slide aggregation is described. The main difference between a sliding aggregation and a cumulative aggregation solution is that the conditions for the connection are different. The sliding aggregation condition is no longer b.ordermonth <= A.ordermonth, but should be b.ordermonth greater than the month of the previous three months and less than the current month. So the SQL statement for the solution that slides the aggregation is as follows

SELECT  a.empid,  date_format (a.ordermonth, '%y-%m ') as OrderMonth,  A.qty as Thismonth,  SUM (B.qty) as Total,  CAST (AVG (B.qty) as DECIMAL (5,2)) as Avgfrom 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.ordermonthwhere date_format ( A.ordermonth, '%Y ') = ' + ' and date_format (B.ordermonth, '%Y ') = ' + ' GROUP by A.empid,date_format (A.ordermonth, '%y-% M '), A.qtyorder by A.empid,a.ordermonth

The result of the operation is as follows the solution returns a three-month slide aggregation for a period, but each user contains aggregations for the first two months and not over 3 months. If you only want to return aggregations that are full 3 months, and do not return aggregations that are less than 3 months old, you can filter by having a filter with a condition of min (b.ordermonth) =date_add (A.ordermonth, INTERVAL-2 MONTH), for example

SELECT  A.empid,  a.ordermonth as OrderMonth,  A.qty as Thismonth,  SUM (b.qty) as Total,  CAST (AVG ( B.qty) as DECIMAL (5,2)) as Avgfrom emporders a INNER JOIN emporders B on    a.empid=b.empid and    b.ordermonth > DAT E_add (A.ordermonth, INTERVAL-3 MONTH) and    b.ordermonth <= a.ordermonthwhere date_format (a.ordermonth, '%Y ') = ' "And Date_format (B.ordermonth, '%Y ') = '" and A.empid=1group by A.empid,date_format (A.ordermonth, '%y-%m '), A.qtyhaving MIN (b.ordermonth) =date_add (A.ordermonth, INTERVAL-2 MONTH) ORDER by A.empid,a.ordermonth

The operation results are as follows

MySQL Slide aggregation