当前位置：极客教程 > SQL > SQL 实例 > SQL 生成简单的预测

SQL 生成简单的预测

SQL 生成简单的预测，基于当前数据，你想得到代表未来行为的、额外的行和列。例如，考虑下面的结果集。

SQL 生成简单的预测 问题描述

基于当前数据，你想得到代表未来行为的、额外的行和列。例如，考虑下面的结果集。

ID ORDER_DATE  PROCESS_DATE
-- ----------- ------------
 1 25-SEP-2005 27-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 3 27-SEP-2005 29-SEP-2005

对于上述结果集里的每一行，你希望返回三行数据（对每一份订单，在现有数据行的基础上额外加上两行数据）。此外，在这些额外的行里，你希望增加两列以展示预期的订单处理日期。
根据上面提供的结果集，我们可以看到一个订单的处理需要花费两天时间。假设订单处理完成后，下一步要做订单核查，最后一步则是发货。核查发生在订单处理后一天，发货则是在核查后一天。你希望得到一个能展示整个过程的结果集。最终是要把上面的结果集变换为如下所示的结果集。

ID ORDER_DATE  PROCESS_DATE VERIFIED    SHIPPED
-- ----------- ------------ ----------- -----------
 1 25-SEP-2005 27-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005 29-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005 30-SEP-2005
 3 27-SEP-2005 29-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005 01-OCT-2005

SQL 生成简单的预测 解决方案

关键在于借助笛卡儿积为每一个订单生成额外的两行数据，然后只需使用 CASE 表达式生成所需的列值即可。
DB2 和 SQL Server
使用 WITH 递归查询为笛卡儿积产生所需数目的行。DB2 和 SQL Server 的解决方案几乎完全一样，除了获取当前日期的函数不同。DB2 调用 CURRENT_DATE 函数，而 SQL Server 使用 GETDATE 函数。下面展示了 SQL Server 的解决方案。

 1 with nrows(n) as (
 2 select 1 from t1 union all
 3 select n+1 from nrows where n+1 <= 3
 4 )
 5 select id,
 6        order_date,
 7        process_date,
 8        case when nrows.n >= 2
 9             then process_date+1
10             else null
11        end as verified,
12        case when nrows.n = 3
13             then process_date+2
14             else null
15        end  as shipped
16   from (
17 select nrows.n id,
18        getdate()+nrows.n as order_date,
19        getdate()+nrows.n+2 as process_date
20   from nrows
21        ) orders, nrows
22  order by 1

Oracle
使用 CONNECT BY 子句生成笛卡儿积所需的 3 行数据。使用 WITH 子句，这样我们就可以重新使用由 CONNECT BY 返回的结果集，而不必再次调用它。

 1  with nrows as (
 2  select level n
 3    from dual
 4 connect by level <= 3
 5  )
 6  select id,
 7         order_date,
 8         process_date,
 9         case when nrows.n >= 2
10              then process_date+1
11              else null
12         end as verified,
13         case when nrows.n = 3
14              then process_date+2
15              else null
16         end  as shipped
17    from (
18  select nrows.n id,
19         sysdate+nrows.n   as order_date,
20         sysdate+nrows.n+2 as process_date
21    from nrows
22         ) orders, nrows

PostgreSQL
可以通过很多种不同的方式创建笛卡儿积，本解决方案用到了 PostgreSQL 的 GENERATE_SERIES 函数。

 1 select id,
 2        order_date,
 3        process_date,
 4        case when gs.n >= 2
 5             then process_date+1
 6             else null
 7        end as verified,
 8        case when gs.n = 3
 9             then process_date+2
10             else null
11         end  as shipped
12    from (
13  select gs.id,
14         current_date+gs.id   as order_date,
15         current_date+gs.id+2 as process_date
16    from generate_series(1,3) gs (id)
17         ) orders,
18           generate_series(1,3)gs(n)

MySQL
MySQL 不支持自动生成行数据的函数。

SQL 生成简单的预测 扩展知识

DB2 和 SQL Server
“问题”部分提供的结果集来自内嵌视图 ORDERS，如下所示。

with nrows(n) as (
select 1 from t1 union all
select n+1 from nrows where n+1 <= 3
)
select nrows.n id,
       getdate()+nrows.n   as order_date,
       getdate()+nrows.n+2 as process_date
  from nrows
　
　
ID ORDER_DATE  PROCESS_DATE
-- ----------- ------------
 1 25-SEP-2005 27-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 3 27-SEP-2005 29-SEP-2005

上面的查询使用 WITH 子句生成了 3 行数据，代表了我们要处理的订单。NROWS 返回了 3 个数字：1、2、3，这些数字和 GETDATE（DB2 则是 CURRENT_DATE）相加就得到了这些订单的 ORDER_DATE。因为前面在“问题”部分里说过订单处理需要花费 2 天时间，于是上面的查询也在 ORDER_DATE 的基础上增加了 2 天（将 NROWS 的返回值加入 GETDATE，然后再加上 2 天）。
现在我们已经得到了基础结果集，下一步是产生一个笛卡儿积，因为需要为每个订单返回 3 行数据。使用 NROWS 产生笛卡儿积，为每个订单返回 3 行数据。

with nrows(n) as (
select 1 from t1 union all
select n+1 from nrows where n+1 <= 3
)
select nrows.n,
       orders.*
  from (
select nrows.n id,
       getdate()+nrows.n as order_date,
       getdate()+nrows.n+2 as process_date
  from nrows
       ) orders, nrows
 order by 2,1
　
　
  N  ID ORDER_DATE  PROCESS_DATE
--- --- ----------- ------------
  1   1 25-SEP-2005 27-SEP-2005
  2   1 25-SEP-2005 27-SEP-2005
  3   1 25-SEP-2005 27-SEP-2005
  1   2 26-SEP-2005 28-SEP-2005
  2   2 26-SEP-2005 28-SEP-2005
  3   2 26-SEP-2005 28-SEP-2005
  1   3 27-SEP-2005 29-SEP-2005
  2   3 27-SEP-2005 29-SEP-2005
  3   3 27-SEP-2005 29-SEP-2005

现在每个订单都有了 3 行记录，然后只要用 CASE 表达式生成额外的列值即可，这些列代表了订单核查和发货的状态。
每个订单的第一行记录里 VERIFIED 和 SHIPPED 应该是 Null，第二行的 SHIPPED 应该为 Null。第三行的 VERIFIED 和 SHIPPED 都应该是非 Null 值。最终的结果集如下所示。

with nrows(n) as (
select 1 from t1 union all
select n+1 from nrows where n+1 <= 3
)
select id,
       order_date,
       process_date,
       case when nrows.n >= 2
            then process_date+1
            else null
       end as verified,
       case when nrows.n = 3
            then process_date+2
            else null
       end  as shipped
  from (
select nrows.n id,
       getdate()+nrows.n   as order_date,
       getdate()+nrows.n+2 as process_date
  from nrows
       ) orders, nrows
 order by 1
　
　
ID ORDER_DATE  PROCESS_DATE VERIFIED    SHIPPED
-- ----------- ------------ ----------- -----------
 1 25-SEP-2005 27-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005 29-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005 30-SEP-2005
 3 27-SEP-2005 29-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005 01-OCT-2005

最终的结果集展示了从收到订单直至发货为止的订单处理全过程。
Oracle
“问题”部分里的结果集是由内嵌视图 ORDERS 生成的，如下所示。

with nrows as (
select level n
  from dual
connect by level <= 3
)
select nrows.n id,
       sysdate+nrows.n   order_date,
       sysdate+nrows.n+2 process_date
  from nrows
　
　
ID ORDER_DATE  PROCESS_DATE
-- ----------- ------------
 1 25-SEP-2005 27-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 3 27-SEP-2005 29-SEP-2005

上述查询借助 CONNECT BY 生成我们要处理的 3 行订单数据。使用 WITH 子句参照 CONNECT BY 返回的行数据 NROWS.N。CONNECT BY 返回了数字 1、2 和 3，这些数字分别加上 SYSDATE 后得到的结果即是 ORDER_DATE。“问题”部分里提到，订单处理需要两天时间，因此上面的查询也为 ORDER_DATE 加上了 2 天（把 CONNECT BY 的返回值和 SYSDATE 相加后，再加上两天）。
现在得到了基础结果集，下一步是产生一个笛卡儿积，因为本问题要求为每个订单返回 3 行数据。使用 NROWS 产生笛卡儿积并为每个订单返回 3 行记录。

with nrows as (
select level n
  from dual
connect by level <= 3
)
select nrows.n,
       orders.*
  from (
select nrows.n id,
       sysdate+nrows.n order_date,
       sysdate+nrows.n+2 process_date
  from nrows
       ) orders, nrows
　
　
  N  ID ORDER_DATE  PROCESS_DATE
--- --- ----------- ------------
  1   1 25-SEP-2005 27-SEP-2005
  2   1 25-SEP-2005 27-SEP-2005
  3   1 25-SEP-2005 27-SEP-2005
  1   2 26-SEP-2005 28-SEP-2005
  2   2 26-SEP-2005 28-SEP-2005
  3   2 26-SEP-2005 28-SEP-2005
  1   3 27-SEP-2005 29-SEP-2005
  2   3 27-SEP-2005 29-SEP-2005
  3   3 27-SEP-2005 29-SEP-2005

现在每个订单都有了 3 行数据，接下来只要使用 CASE 表达式生成额外的列值即可，这些列代表了订单核查和发货的状态。
每个订单的第一行记录里 VERIFIED 和 SHIPPED 应该是 Null。第二行的 SHIPPED 应该为 Null。第三行的 VERIFIED 和 SHIPPED 都应该是非 Null 值。最终的结果集如下所示。

with nrows as (
select level n
  from dual
connect by level <= 3
)
select id,
       order_date,
       process_date,
       case when nrows.n >= 2
            then process_date+1
            else null
       end as verified,
       case when nrows.n = 3
            then process_date+2
            else null
       end  as shipped
  from (
select nrows.n id,
       sysdate+nrows.n order_date,
       sysdate+nrows.n+2 process_date
  from nrows
       ) orders, nrows
　
　
ID ORDER_DATE  PROCESS_DATE VERIFIED    SHIPPED
-- ----------- ------------ ----------- -----------
 1 25-SEP-2005 27-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005 29-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005 30-SEP-2005
 3 27-SEP-2005 29-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005 01-OCT-2005

最终的结果集展示了从收到订单直至发货为止的订单处理全过程。
PostgreSQL
“问题”部分里的结果集是由内嵌视图 ORDERS 生成的，如下所示。

select gs.id,
       current_date+gs.id   as order_date,
       current_date+gs.id+2 as process_date
  from generate_series(1,3) gs (id)
　
　
ID ORDER_DATE  PROCESS_DATE
-- ----------- ------------
 1 25-SEP-2005 27-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 3 27-SEP-2005 29-SEP-2005

上述查询使用 GENERATE_SERIES 函数生成了我们要处理的 3 行订单数据。GENERATE_SERIES 返回了数字 1、2 和 3，并把这些数字和 CURRENT_DATE 相加得到 ORDER_DATE。“问题”部分里提到，订单处理需要 2 天时间，因此上面的查询也为 ORDER_DATE 加上了 2 天（把 GENERATE_SERIES 的返回值和 CURRENT_DATE 相加，然后再加上两天）。
现在我们得到了基础结果集，下一步是产生一个笛卡儿积，因为本问题要求为每个订单返回 3 行数据。使用 GENERATE_SERIES 函数产生一个笛卡儿积并为每个订单返回 3 行记录。

select gs.n,
       orders.*
  from (
select gs.id,
       current_date+gs.id as order_date,
       current_date+gs.id+2 as process_date
  from generate_series(1,3) gs (id)
       ) orders,
         generate_series(1,3)gs(n)
　
　
  N  ID ORDER_DATE  PROCESS_DATE
--- --- ----------- ------------
  1   1 25-SEP-2005 27-SEP-2005
  2   1 25-SEP-2005 27-SEP-2005
  3   1 25-SEP-2005 27-SEP-2005
  1   2 26-SEP-2005 28-SEP-2005
  2   2 26-SEP-2005 28-SEP-2005
  3   2 26-SEP-2005 28-SEP-2005
  1   3 27-SEP-2005 29-SEP-2005
  2   3 27-SEP-2005 29-SEP-2005
  3   3 27-SEP-2005 29-SEP-2005

现在每个顺序都拥有了 3 行，然后简单地用一个 CASE 表达式创建需要增加的列值，这些列代表了核查和装运的状态。
每个订单的第一行记录里 VERIFIED 和 SHIPPED 应该是 Null。第二行的 SHIPPED 应该为 Null。第三行的 VERIFIED 和 SHIPPED 都应该是非空值。最终的结果集如下所示。

select id,
       order_date,
       process_date,
       case when gs.n >= 2
            then process_date+1
            else null
       end as verified,
       case when gs.n = 3
            then process_date+2
            else null
       end  as shipped
  from (
select gs.id,
       current_date+gs.id   as order_date,
       current_date+gs.id+2 as process_date
  from generate_series(1,3) gs(id)
       ) orders,
         generate_series(1,3)gs(n)
　
　
ID ORDER_DATE  PROCESS_DATE VERIFIED    SHIPPED
-- ----------- ------------ ----------- -----------
 1 25-SEP-2005 27-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005
 1 25-SEP-2005 27-SEP-2005  28-SEP-2005 29-SEP-2005
 2 26-SEP-2005 28-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005
 2 26-SEP-2005 28-SEP-2005  29-SEP-2005 30-SEP-2005
 3 27-SEP-2005 29-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005
 3 27-SEP-2005 29-SEP-2005  30-SEP-2005 01-OCT-2005

最终的结果集展示了从收到订单直至发货为止的订单处理全过程。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

SQL 实例