10-113 12進数の減算で月数を求める

SQLパズル

実績テーブル
実績年月  実績数
--------  ------
  200511      12
  200512       1
  200512      12
  200601      10
  200601       1
  200602      20
  200604      40
  200605      50
  200606      60

200512から200605までの実績数の合計を集計する。

出力結果
実績年月  実績数
--------  ------
  200512      13
  200601      11
  200602      20
  200603       0
  200604      40
  200605      50

データ作成スクリプト

create table 実績 as
select '200511' as 実績年月,12 as 実績数 from dual
union select '200512', 1 from dual
union select '200512',12 from dual
union select '200601',10 from dual
union select '200601', 1 from dual
union select '200602',20 from dual
union select '200604',40 from dual
union select '200605',50 from dual
union select '200606',60 from dual;

SQL

--■■■12進数の減算で月数を求める方法■■■
select b.実績年月,
nvl(a.実績数,0) as 実績数
from (select 実績年月,sum(実績数) as 実績数
        from 実績
       where 実績年月 between '200512' and '200605'
      group by 実績年月) a,
     (select to_char(add_months(to_date('200512','yyyymm'),RowNum-1),'yyyymm') as 実績年月
        from all_catalog
       where RowNum <= (2006-2005)*12+(5-12)+1) b
 where a.実績年月(+) = b.実績年月
order by b.実績年月;

--■■■months_betweenで月数を求める方法■■■
select b.実績年月,
nvl(a.実績数,0) as 実績数
from (select 実績年月,sum(実績数) as 実績数
        from 実績
       where 実績年月 between '200512' and '200605'
      group by 実績年月) a,
     (select to_char(add_months(to_date('200512','yyyymm'),RowNum-1),'yyyymm') as 実績年月
        from all_catalog
       where RowNum <= months_between(to_date('200605','yyyymm'),to_date('200512','yyyymm'))+1) b
 where a.実績年月(+) = b.実績年月
order by b.実績年月;

--■■■model句を使う方法(10g以降)■■■
select 実績年月,実績数
  from 実績
group by 実績年月
 model RETURN UPDATED ROWS
 dimension by (実績年月)
 measures(sum(実績数) as 実績数)
 rules(実績数[200512] = 実績数[cv()],
       実績数[for 実績年月 from 200601 to 200605 INCREMENT 1]
           = PresentV(実績数[CV()],実績数[CV()],0))
order by 実績年月;

解説

yyyymm形式の年月から
12進数の減算で、月数を求めることができます。

8-19 12進数変換とrangeの組み合わせ

---