30 JAMES

30 MARTIN

30 TURNER

30 BLAKE

30 WARD

30 ALLEN

950 03-DEC-1981 950

1250 28-SEP-1981 950

1500 08-SEP-1981 950

2850 01-MAY-1981 950

1250 22-FEB-1981 950

1600 20-FEB-1981 950

Формирование простых прогнозов

Задача

Исходя из текущих данных требуется получить дополнительные строки и столбцы, представляющие будущие действия. Например, рассмотрим следующее результирующее множество:

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

Для каждой строки результирующего множества требуется возвратить три строки (строка плюс две дополнительные строки для каждого заказа). Кроме дополнительных строк, должны быть добавлены столбцы с предполагаемыми датами обработки заказов.

Из представленного выше результирующего множества видно, что обработка заказа занимает два дня. Для примера предположим, что следующий этап после обработки заказа - контроль, и последний этап -поставка. Контроль выполняется на следующий день после обработки, и поставка осуществляется на следующий день после контроля. Необходимо получить результирующее множество, отражающее всю процедуру. В итоге представленное выше результирующее множество должно быть преобразовано в следующее:

Решение

Ход решения - с помощью декартова произведения получить по две дополнительные строки на каждый заказ и затем просто использовать выражения CASE для создания необходимых значений столбцов.

DB2 и SQL Server

Чтобы сформировать строки, необходимые для декартова произведения, используйте рекурсивный оператор WITH. Решения для DB2 и SQL Server практически идентичны и отличаются лишь функциями, применяемыми для получения текущей даты. DB2 использует функцию CURRENTDATE, а SQL Server - функцию GETDATE. Ниже показано решение для SQL Server:

1 with nrows(n) as (

Чтобы получить строки, необходимые для декартова произведения, используйте иерархический оператор CONNECT BY. Оператор WITH обеспечит возможность использовать результаты CONNECT BY без его повторного вызова:

I with nrows as (

2 select level n

3 from dual

4 connect by level <= 3

6 select id,

7 order date,

8 process date,

9 case when nrows.n >= 2 10 then process date+1

II else null

12 end as verified,

13 case when nrows.n = 3

14 then process date+2

MySQL не поддерживает функцию для автоматического формирования строк.

Обсуждение

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

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 SE-

RIES: