Example analysis of the principle and solution of MySQL sliding order problem

This article uses examples to explain the principle and solution of the MySQL sliding order problem. Share with you for your reference, the details are as follows:

First, create the MonthlyOrders table and import some data according to the following code

CREATE TABLE MonthlyOrders(
ordermonth DATE,
ordernum INT UNSIGNED,
PRIMARY KEY (ordermonth)
);

INSERT INTO MonthlyOrders SELECT '2010-02-01',23;
INSERT INTO MonthlyOrders SELECT '2010-03-01',26;
INSERT INTO MonthlyOrders SELECT '2010-04-01',24;
INSERT INTO MonthlyOrders SELECT '2010-05-01',27;
INSERT INTO MonthlyOrders SELECT '2010-06-01',26;
INSERT INTO MonthlyOrders SELECT '2010-07-01',32;
INSERT INTO MonthlyOrders SELECT '2010-08-01',34;
INSERT INTO MonthlyOrders SELECT '2010-09-01',30;
INSERT INTO MonthlyOrders SELECT '2010-10-01',31;
INSERT INTO MonthlyOrders SELECT '2010-11-01',32;
INSERT INTO MonthlyOrders SELECT '2010-12-01',33;
INSERT INTO MonthlyOrders SELECT '2011-01-01',31;
INSERT INTO MonthlyOrders SELECT '2011-02-01',34;
INSERT INTO MonthlyOrders SELECT '2011-03-01',34;
INSERT INTO MonthlyOrders SELECT '2011-04-01',38;
INSERT INTO MonthlyOrders SELECT '2011-05-01',39;
INSERT INTO MonthlyOrders SELECT '2011-06-01',35;
INSERT INTO MonthlyOrders SELECT '2011-07-01',49;
INSERT INTO MonthlyOrders SELECT '2011-08-01',56;
INSERT INTO MonthlyOrders SELECT '2011-09-01',55;
INSERT INTO MonthlyOrders SELECT '2011-10-01',74;
INSERT INTO MonthlyOrders SELECT '2011-11-01',75;
INSERT INTO MonthlyOrders SELECT '2011-12-01',14;

The sliding order problem refers to returning the number of sliding orders in the previous year (quarter or month) for each month, that is, for each month N, returning the total number of orders from N-11 to month N. Here, it is assumed that there are no gaps in the sequence of months.

Execute the following SQL query to return the total number of sliding orders for the previous year each month

SELECT
  DATE_FORMAT(a.ordermonth, '%Y%m') AS frommonth,
  DATE_FORMAT(b.ordermonth, '%Y%m') AS tomonth,
  SUM(c.ordernum) AS orders
FROM monthlyorders a
INNER JOIN monthlyorders b
  ON DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth
INNER JOIN monthlyorders c
  ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonth
GROUP BY a.ordermonth,b.ordermonth;

The running results are as follows

The query first performs a self-join on the MonthlyOrders table. Table a is used as the lower boundary (frommonth) and table b is used as the upper boundary (tomonth). The conditions for connection are:

DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth

For example, February 2010 in table a will match January 2011.

After completing the self-connection, you need to count the orders. At this time, you need to perform another self-join to get the number of orders for each month within the range. So the condition for the connection is

c.ordermonth BETWEEN a.ordermonth AND b.ordermonth

Based on the above method, we can also count the order situation in each quarter and use it as a basis for comparison with year-on-year growth.

SELECT
  DATE_FORMAT(a.ordermonth, '%Y%m') AS frommonth,
  DATE_FORMAT(b.ordermonth, '%Y%m') AS tomonth,
  SUM(c.ordernum) AS orders
FROM monthlyorders a
INNER JOIN monthlyorders b
  ON DATE_ADD(a.ordermonth, INTERVAL 2 MONTH) = b.ordermonth
  AND MONTH(a.ordermonth) % 3 = 1
INNER JOIN monthlyorders c
  ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonth
GROUP BY a.ordermonth,b.ordermonth;

The running 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.

You may also be interested in:

Usage and performance optimization techniques of aggregate function count in MySQL
Detailed explanation of MySQL commonly used aggregate functions
How to add conditional expressions to aggregate functions in MySql
php+mysql open source XNA aggregation program released for download
Mysql cannot select non-aggregate columns
Analysis of MySQL query sorting and query aggregation function usage
Detailed explanation of MySQL single table query operation examples [syntax, constraints, grouping, aggregation, filtering, sorting, etc.]
Analysis of the principle and usage of MySQL continuous aggregation
Analysis of common usage examples of MySQL process functions
Analysis of MySQL cumulative aggregation principle and usage examples

<<: A complete tutorial on installing Ubuntu 20.04 using VMware virtual machine

>>: CentOS8 - bash: garbled characters and solutions