温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

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

发布时间:2020-08-24 11:42:28 来源:脚本之家 阅读:215 作者:雪山飞猪 栏目:MySQL数据库

本文实例讲述了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滑动订单问题原理与解决方法实例分析

更多关于MySQL相关内容感兴趣的读者可查看本站专题:《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

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

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI