3-17 case式とignore nullsその2

SQLパズル

収支テーブル
      日付  収入  支出
----------  ----  ----
2005/12/10     3     1
2005/12/11     5     2
2005/12/12     4     9
2005/12/13     5     2
2005/12/14     9     1
2005/12/15     6     3
2005/12/16     1     5
2005/12/17     0     6
2005/12/18     3     1

収支テーブルから、
日付、収入、支出、
収支(収入-支出)と、過去で直近の黒字の日の収支、過去で直近の黒字の日
を求める。

出力結果
      日付  収入   支出  収支  直近の黒字収支   直近の黒字日
----------  ----  ----  ----  --------------  ------------
2005/12/10     3     1     2               2   2005/12/10
2005/12/11     5     2     3               3   2005/12/11
2005/12/12     4     9    -5               3   2005/12/11
2005/12/13     5     2     3               3   2005/12/13
2005/12/14     9     1     8               8   2005/12/14
2005/12/15     6     3     3               3   2005/12/15
2005/12/16     1     5    -4               3   2005/12/15
2005/12/17     0     6    -6               3   2005/12/15
2005/12/18     3     1     2               2   2005/12/18

データ作成スクリプト

create table 収支(
日付 date,
収入 number(1),
支出 number(1));

insert into 収支 values(to_date('20051210','YYYYMMDD'),3,1);
insert into 収支 values(to_date('20051211','YYYYMMDD'),5,2);
insert into 収支 values(to_date('20051212','YYYYMMDD'),4,9);
insert into 収支 values(to_date('20051213','YYYYMMDD'),5,2);
insert into 収支 values(to_date('20051214','YYYYMMDD'),9,1);
insert into 収支 values(to_date('20051215','YYYYMMDD'),6,3);
insert into 収支 values(to_date('20051216','YYYYMMDD'),1,5);
insert into 収支 values(to_date('20051217','YYYYMMDD'),0,6);
insert into 収支 values(to_date('20051218','YYYYMMDD'),3,1);
commit;

SQL

col 収入 for 9999
col 支出 for 9999
col 収支 for 9999

--■■■case式とignore nullsを使う方法(10g以降)■■■
select to_char(日付,'YYYY/MM/DD') as 日付,収入,支出,
収入-支出 as 収支,
Last_Value(case when 収入-支出 >= 0
           then 収入-支出 end ignore nulls) over(order by 日付) as 直近の黒字収支,
to_char(Last_Value(case when 収入-支出 >= 0
                   then 日付 end ignore nulls) over(order by 日付),'YYYY/MM/DD') as 直近の黒字日
from 収支;

--■■■自己結合を使う方法■■■
select to_char(a.日付,'YYYY/MM/DD') as 日付,a.収入,a.支出,
a.収入-a.支出 as 収支,
b.収入-b.支出 as 直近の黒字収支,
to_char(b.日付,'YYYY/MM/DD') as 直近の黒字日
from 収支 a,収支 b
where b.日付 = (select max(c.日付) from 収支 c
                 where c.日付 <= a.日付
                   and c.収入-c.支出 >=0);

解説

Oracle10g以降なら、
case式とignore nullsを組み合わせる方法があります。

分析関数の衝撃6 (応用編)