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;
运行结果如下图
该查询首先对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;
运行结果如下图