mysql滑动订单问题原理与解决方法实例分析

本文实例讲述了mysql滑动订单问题原理与解决方法。分享给大家供大家参考，具体如下：

先根据以下代码来创建表MonthlyOrders并导入一定的数据

?

1 2 3 4 5

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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;

滑动订单问题是指为每个月返回上一年度（季度或月度）的滑动订单数，即每个月份N，返回N-11到月份N的订单总数。这里，假设月份序列中不存在间断。

执行下面的SQL查询实现每个月返回上一年度的滑动订单总数

?

1 2 3 4 5 6 7 8 9 10

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;

运行结果如下图

该查询首先对MonthlyOrders表进行自连接。a表用做下边界(frommonth)，b表用做上边界(tomonth)。连接的条件为:

?

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

例如，a表中的2010年2月将匹配2011年1月。

完成自连接之后，需要对订单进行统计。这时需要再进行一次自连接，得到范围内每个月的订单数量。因此连接的条件为

?

1	c.ordermonth BETWEEN a.ordermonth AND b.ordermonth

基于上述方法，我们还可以统计每个季度订单的情况，以此作为和同比增长的比较依据。

?

1 2 3 4 5 6 7 8 9 10 11

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;

运行结果如下图

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/chenqionghe/p/4679800.html