mysql滑動訂單問題

標籤：

先根據以下代碼來建立表MonthlyOrders並匯入一定的資料

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;

滑動訂單問題是指為每個月返回上一年度（季度或月度）的滑動訂單數，即每個月份N，返回N-11到月份N的訂單總數。這裡，假設月份序列中不存在間斷。執行下面的SQL查詢實現每個月返回上一年度的滑動訂單總數

SELECT    DATE_FORMAT(a.ordermonth, ‘%Y%m‘) AS frommonth,    DATE_FORMAT(b.ordermonth, ‘%Y%m‘) AS tomonth,    SUM(c.ordernum) AS ordersFROM monthlyorders aINNER JOIN monthlyorders b     ON DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonthINNER JOIN monthlyorders c    ON c.ordermonth BETWEEN a.ordermonth AND b.ordermonthGROUP BY a.ordermonth,b.ordermonth;

運行結果如該查詢首先對MonthlyOrders表進行自串連。a表用做下邊界(frommonth)，b表用做上邊界(tomonth)。串連的條件為:DATE_ADD(a.ordermonth, INTERVAL 11 MONTH) = b.ordermonth。例如，a表中的2010年2月將匹配2011年1月。完成自串連之後，需要對訂單進行統計。這時需要再進行一次自串連，得到範圍內每個月的訂單數量。因此串連的條件為c.ordermonth BETWEEN a.ordermonth AND b.ordermonth。基於上述方法，我們還可以統計每個季度訂單的情況，以此作為和同比增長的比較依據。

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

運行結果如

mysql滑動訂單問題