MySQL 滑動訂單問題

神諭丶發表於2014-08-10
MySql
滑動訂單問題是指為每個月返回上一年度（季度或月度等）的滑動訂單數。
即：某個月份N，返回從（N-11）月到N月的訂單總數。（架設月份序列中不存在間斷）。



首先建立一個測試表

  1. create table monthly_orders(
  2. ordermonth date,
  3. ordernum int unsigned,
  4. primary key(ordermonth));


再插入一些資料

  1. insert into monthly_orders select '2010-02-01',23;
  2. insert into monthly_orders select '2010-03-01',26;
  3. insert into monthly_orders select '2010-04-01',24;
  4. insert into monthly_orders select '2010-05-01',27;
  5. insert into monthly_orders select '2010-06-01',26;
  6. insert into monthly_orders select '2010-07-01',32;
  7. insert into monthly_orders select '2010-08-01',34;
  8. insert into monthly_orders select '2010-09-01',30;
  9. insert into monthly_orders select '2010-10-01',31;
  10. insert into monthly_orders select '2010-11-01',32;    
  11. insert into monthly_orders select '2010-12-01',33;
  12. insert into monthly_orders select '2011-01-01',31;
  13. insert into monthly_orders select '2011-02-01',34;
  14. insert into monthly_orders select '2011-03-01',34;
  15. insert into monthly_orders select '2011-04-01',38;
  16. insert into monthly_orders select '2011-05-01',39;
  17. insert into monthly_orders select '2011-06-01',35;
  18. insert into monthly_orders select '2011-07-01',49;
  19. insert into monthly_orders select '2011-08-01',56;
  20. insert into monthly_orders select '2011-09-01',55;
  21. insert into monthly_orders select '2011-10-01',74;
  22. insert into monthly_orders select '2011-11-01',75;
  23. insert into monthly_orders select '2011-12-01',14;

最後返回的結果集應該為：



解決方案
  1. select
  2.     date_format(a.ordermonth, '%Y-%m') as from_month,
  3.     date_format(b.ordermonth, '%Y-%m') as to_month,
  4.     sum(c.ordernum) as orders
  5. from monthly_orders a
  6.     join monthly_orders b
  7.     on date_add(a.ordermonth, interval 11 month) = b.ordermonth
  8.     join monthly_orders c
  9.     on c.ordermonth between a.ordermonth and b.ordermonth
  10. group by a.ordermonth,b.ordermonth;

該查詢對monthly_orders表進行自聯接，a表用做下邊界(from_month)，b表用做上邊界(to_month)。
聯接條件為：date_add(a.ordermonth, interval 11 month) = b.ordermonth。
例如2010年2月將匹配2011年1月
完成此次自聯接操作之後，需要對訂單進行統計，這時需要在進行一次自聯接，得到範圍內每個月的訂單數量，因此聯接條件為c.ordermonth between a.ordermonth and b.ordermonth 。



按照上述方法，我們還可以統計每個季度訂單的詳情，作為環比和同比增長的比較依據。

即



解決方案：


  1. select
  2.     date_format(a.ordermonth,\'%Y-%m\') as from_month,
  3.     date_format(b.ordermonth,\'%Y-%m\') as to_month,
  4.     sum(c.ordernum) as orders
  5. from monthly_orders a
  6.     join monthly_orders b
  7.     on date_add(a.ordermonth, interval 2 month) = b.ordermonth
  8.     and month(a.ordermonth) % 3 = 1
  9.     join monthly_orders c
  10.     on c.ordermonth between a.ordermonth and b.ordermonth
  11. group by a.ordermonth,b.ordermonth;


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1248568/，如需轉載，請註明出處，否則將追究法律責任。

相關文章