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

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

Recommend

Analysis of the cutting of the background image of the nine-square grid with adaptive width and height

<br />Based on the original width-and-height...

Example of Vue's implementation of the underlying code for simulating responsive principles

Table of contents 1.Vue.js features: 2.Observer.j...

Detailed explanation of the problem of matching even when there is a space at the end of the string in the Mysql query condition

1. Table structure TABLE person id name 1 you 2 Y...

Several ways to store images in MySQL database

Usually the pictures uploaded by users need to be...

Summary of block-level elements, inline elements, and variable elements

Block element p - paragraph pre - format text tabl...

Problems encountered by MySQL nested transactions

MySQL supports nested transactions, but not many ...

Detailed explanation of MySQL 5.7.9 shutdown syntax example

mysql-5.7.9 finally provides shutdown syntax: Pre...

Detailed tutorial on installing Nginx 1.16.0 under Linux

Because I have been tinkering with Linux recently...

SSH port forwarding, local port forwarding, remote port forwarding, dynamic port forwarding details

Part 1 Overview of SSH Port Forwarding When you a...

What are the benefits of using // instead of http:// (adaptive https)

//Default protocol /The use of the default protoc...

Vue implements upload component

Table of contents 1. Introduction 2. Ideas Two wa...

How to deploy a simple c/c++ program using docker

1. First, create a hello-world.cpp file The progr...

Vue Basics Listener Detailed Explanation

Table of contents What is a listener in vue Usage...

Page Speed ​​Optimization at a Glance

I believe that the Internet has become an increas...

Introduction to the difference between OBJECT and EMBED tags used to display flash content

1. Introduction: If we want to display flash conte...