MySQL 生成简单的预测

备注:测试数据库版本为MySQL 8.0

测试数据:

drop table if exists orders;
create table orders(id int,order_date date,process_date date);
insert into orders values (1,'2020-09-25','2020-09-27');
insert into orders values (2,'2020-09-26','2020-09-28');
insert into orders values (3,'2020-09-27','2020-09-29');

一.需求

以当前数据为基础，返回另外的行和列，用于表示未来活动。
例如，查看下列结果集:
±-----±-----------±-------------+
| id | order_date | process_date |
±-----±-----------±-------------+
| 1 | 2020-09-25 | 2020-09-27 |
| 1 | 2020-09-26 | 2020-09-28 |
| 1 | 2020-09-27 | 2020-09-29 |
±-----±-----------±-------------+

要求对于结果集中的每一行，都要返回3行(对于一个订单，原来有一行再另外加两行)；
此外，还需要另外增加两列，用于存放对订单做进一步处理的日期。

从上面的结果集中可以看到，订单处理需要两天。对于这个例子，假定订单处理之后进行核对，最后一步是出货；
订单处理完1天后进行核对，核对完后过1天就出货。
显然希望能够将上面的结果集转换为如下结果集:

±-----±-----------±-------------±---------±---------+
| id | order_date | process_date | verified | shipped |
±-----±-----------±-------------±---------±---------+
| 1 | 2020-09-25 | 2020-09-27 | NULL | NULL |
| 1 | 2020-09-25 | 2020-09-27 | 20200928 | NULL |
| 1 | 2020-09-25 | 2020-09-27 | 20200928 | 20200929 |
| 2 | 2020-09-26 | 2020-09-28 | NULL | NULL |
| 2 | 2020-09-26 | 2020-09-28 | 20200929 | NULL |
| 2 | 2020-09-26 | 2020-09-28 | 20200929 | 20200930 |
| 3 | 2020-09-27 | 2020-09-29 | NULL | NULL |
| 3 | 2020-09-27 | 2020-09-29 | 20200930 | NULL |
| 3 | 2020-09-27 | 2020-09-29 | 20200930 | 20200931 |
±-----±-----------±-------------±---------±---------+

二.解决方案

这里的关键是用笛卡尔积为每个订单生产两个额外行，然后，只要使用case表达式创建所需要的列值就可以了。

with recursive c(n) as
(
select 1 as n
union all
select n + 1 from c where n  < 3
)
select order_date,
       process_date,
       case when c.n >= 2 then adddate(process_date,1) else null end as verified,
       case when c.n >= 3 then adddate(process_date,2) else null end as shipped
  from orders
  inner join c

测试记录:

mysql> with recursive c(n) as
    -> (
    -> select 1 as n
    -> union all
    -> select n + 1 from c where n  < 3
    -> )
    -> select order_date,
    ->        process_date,
    ->        case when c.n >= 2 then adddate(process_date,1) else null end as verified,
    ->        case when c.n >= 3 then adddate(process_date,2) else null end as shipped
    ->   from orders
    ->   inner join c;
+------------+--------------+------------+------------+
| order_date | process_date | verified   | shipped    |
+------------+--------------+------------+------------+
| 2020-09-25 | 2020-09-27   | NULL       | NULL       |
| 2020-09-25 | 2020-09-27   | 2020-09-28 | NULL       |
| 2020-09-25 | 2020-09-27   | 2020-09-28 | 2020-09-29 |
| 2020-09-26 | 2020-09-28   | NULL       | NULL       |
| 2020-09-26 | 2020-09-28   | 2020-09-29 | NULL       |
| 2020-09-26 | 2020-09-28   | 2020-09-29 | 2020-09-30 |
| 2020-09-27 | 2020-09-29   | NULL       | NULL       |
| 2020-09-27 | 2020-09-29   | 2020-09-30 | NULL       |
| 2020-09-27 | 2020-09-29   | 2020-09-30 | 2020-10-01 |
+------------+--------------+------------+------------+
9 rows in set (0.00 sec)