Mysql for sliding order

1. demand

Find sliding order

2. example

The sliding order here refers to the characteristic number of an order calculated by every 12 months or every quarter [3 months]. Here is an example.
There are orders. The table data are as follows:

mysql> select * from orders;
+-------------+-----------+
| order_month | order_num |
+-------------+-----------+
| 2017-02-01  |        23 |
| 2017-03-01  |        26 |
| 2017-04-01  |        24 |
| 2017-05-01  |        27 |
| 2017-06-01  |        26 |
| 2017-07-01  |        32 |
| 2017-08-01  |        34 |
| 2017-09-01  |        30 |
| 2017-10-01  |        31 |
| 2017-11-01  |        32 |
| 2017-12-01  |        33 |
| 2018-01-01  |        31 |
| 2018-02-01  |        34 |
| 2018-03-01  |        34 |
| 2018-04-01  |        38 |
| 2018-05-01  |        39 |
| 2018-06-01  |        35 |
| 2018-07-01  |        49 |
| 2018-08-01  |        56 |
| 2018-09-01  |        55 |
+-------------+-----------+
20 rows in set (0.00 sec)

So the sliding order problem is to find the total order number of the starting month + December in the table.

+-------------+------------+
| start_month | end_month  |
+-------------+------------+
| 2017-02-01  | 2018-02-01 |
| 2017-03-01  | 2018-03-01 |
| 2017-04-01  | 2018-04-01 |
| 2017-05-01  | 2018-05-01 |
| 2017-06-01  | 2018-06-01 |
| 2017-07-01  | 2018-07-01 |
| 2017-08-01  | 2018-08-01 |
| 2017-09-01  | 2018-09-01 |
+-------------+------------+
8 rows in set (0.00 sec)

As shown in the figure above, the total orders of 2017-02-01 = > 2018-02-01 are calculated respectively 2017-09-01 = > total orders for 2018-09-01. The final results are as follows:

+-------------+------------+-------+
| start_month | end_month  | total |
+-------------+------------+-------+
| 2017-02-01  | 2018-02-01 |   383 |
| 2017-03-01  | 2018-03-01 |   394 |
| 2017-04-01  | 2018-04-01 |   406 |
| 2017-05-01  | 2018-05-01 |   421 |
| 2017-06-01  | 2018-06-01 |   429 |
| 2017-07-01  | 2018-07-01 |   452 |
| 2017-08-01  | 2018-08-01 |   476 |
| 2017-09-01  | 2018-09-01 |   497 |
+-------------+------------+-------+
8 rows in set (0.00 sec)

3. code 1

select
 t1.start_month
,t1.end_month
,sum(order_num) as total
from orders ord1
inner join 
(
	select 
	order_month as start_month
	,date_add(order_month,interval 12 month) as end_month
	from orders ord1
	where date_add(ord1.order_month,interval 12 month) <= (select max(order_month) as max_month from orders)
)as t1
on ord1.order_month between t1.start_month and t1.end_month
group by t1.start_month;

Here's one thing to note: in the statement of inner join...on, the condition in on is not necessarily the = sign. You can use >, <,! =, between on and so on

3. code two

select 
	date_format(a.order_month,'%Y%m') as start_month
	,date_format(b.order_month,'%Y%m') as end_month
	,sum(c.order_num) as total
from orders a
inner join orders b
	on date_add(a.order_month,interval 12 month ) = b.order_month
inner join orders c
	on c.order_month between a.order_month and b.order_month
group by a.order_month,b.order_month;

This code has the same effect as the above code.