欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-02 09:27:56
本文实例讲述了mysql滑动订单问题原理与解决方法。分享给大家供大家参考,具体如下: 先根据以下代码来创建表monthlyorders并导入一定的数据 create tab...

本文实例讲述了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 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;

运行结果如下图

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

该查询首先对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 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滑动订单问题原理与解决方法实例分析