10-23 連続した日付を作成

SQLパズル

在庫テーブル
日付        在庫数
----------  ------
2005/11/01      10
2005/11/04      20
2005/11/08       5

2005/11/02から2005/11/10までの在庫状況を出力する。

出力結果
日付        在庫数
----------  ------
2005/11/02      10
2005/11/03      10
2005/11/04      20
2005/11/05      20
2005/11/06      20
2005/11/07      20
2005/11/08       5
2005/11/09       5
2005/11/10       5

データ作成スクリプト

create table 在庫(
日付   date,
在庫数 number(2));

insert into 在庫 values(to_date('2005/11/01','YYYY/MM/DD'),10);
insert into 在庫 values(to_date('2005/11/04','YYYY/MM/DD'),20);
insert into 在庫 values(to_date('2005/11/08','YYYY/MM/DD'), 5);
commit;

SQL

--■■■相関サブクエリを使う方法■■■
select to_char(Day,'yyyy/mm/dd') as 日付,
(select b.在庫数 from 在庫 b
  where b.日付 = (select max(c.日付) from 在庫 c
                   where c.日付 <= a.Day)) as 在庫数
from (select to_date('2005/11/02','yyyy/mm/dd')+RowNum-1 as Day
        from all_catalog
       where to_date('2005/11/02','yyyy/mm/dd')+RowNum-1
          <= to_date('2005/11/10','yyyy/mm/dd')) a;

--■■■分析関数を使う方法■■■
select to_char(Day,'yyyy/mm/dd') as 日付,
(select distinct
        Last_Value(b.在庫数)
        over(order by b.日付 Rows between Unbounded Preceding and Unbounded Following)
   from 在庫 b
  where b.日付 <= a.Day) as 在庫数
from (select to_date('2005/11/02','yyyy/mm/dd')+RowNum-1 as Day
        from all_catalog
       where to_date('2005/11/02','yyyy/mm/dd')+RowNum-1
          <= to_date('2005/11/10','yyyy/mm/dd')) a;

--■■■dualテーブルに対する階層問い合わせを使う方法■■■
select to_char(Day,'yyyy/mm/dd') as 日付,
(select b.在庫数 from 在庫 b
  where b.日付 = (select max(c.日付) from 在庫 c
                   where c.日付 <= a.Day)) as 在庫数
from (select to_date('2005/11/02','yyyy/mm/dd') + Level-1 as Day from dual
  connect by to_date('2005/11/02','yyyy/mm/dd') + Level-1
          <= to_date('2005/11/10','yyyy/mm/dd')) a;

--■■■model句を使う方法(10g以降)■■■
select 日付,在庫数
  from 在庫
 model RETURN UPDATED ROWS
 dimension by (日付)
 measures(在庫数)
 rules(在庫数[for 日付 from date '2005-11-02' to date '2005-11-10' INCREMENT 1]
     = PresentV(在庫数[CV()],在庫数[CV()],在庫数[CV()-1]))
order by 日付;

解説

インラインビューで、
2005/11/02から2005/11/10までの連続した日付を作成してます。

Oracle9iだと、dualテーブルに対する階層問い合わせが、
うまくいかないようです。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
 Multi-Rows from DUAL (英語)

select RowNum
  from dual
connect by Level <= 10;
は、サポートしてないとかしてるとかいう話もあります。

Oracle Database SQLリファレンス 10g リリース2 階層問合せ演算子
>階層問合せでは、
>CONNECT BY condition内の1つの式をPRIOR演算子で修飾する必要があります。

Oracle Database SQL Reference 10g Release 2 (10.2) Hierarchical Query Operators
>In a hierarchical query,
>one expression in the CONNECT BY condition must be qualified by the PRIOR operator.

---