6-2 期間別の集計

SQLパズル

サンプルデータ
番号   取引フラグ      取引日
----   ----------   -----------
   1           P    2004/01/06
   1           F    2004/03/05
   1           P    2004/04/07
   1           F    2004/06/08
   1           P    2004/08/01
   1           F    2004/10/11
   1           P    2004/12/06
   2           P    2004/01/12
   2           F    2004/05/06
   2           P    2004/08/06
   2           F    2004/12/06
   3           P    2004/02/21
   3           F    2004/09/21
   4           P    2004/03/16
   4           F    2004/10/14
   5           P    2004/02/21
   5           F    2004/05/11

期間別集計を行う。
取引フラグは、Pが取引開始、Fを取引終了日とし、
Pに対応するFがないものは、カウント対象外とする。

出力結果
3ヶ月以内     4〜6ヶ月以内   7〜9ヶ月以内    10〜12ヶ月以内
---------     ------------  ------------    --------------
       4                2             2                 0

データ作成スクリプト

create table サンプルデータ(
番号       number(1),
取引フラグ char(1),
取引日     date);

insert into サンプルデータ values(1,'P',to_date('20040106','YYYYMMDD'));
insert into サンプルデータ values(1,'F',to_date('20040305','YYYYMMDD'));
insert into サンプルデータ values(1,'P',to_date('20040407','YYYYMMDD'));
insert into サンプルデータ values(1,'F',to_date('20040608','YYYYMMDD'));
insert into サンプルデータ values(1,'P',to_date('20040801','YYYYMMDD'));
insert into サンプルデータ values(1,'F',to_date('20041011','YYYYMMDD'));
insert into サンプルデータ values(1,'P',to_date('20041206','YYYYMMDD'));
insert into サンプルデータ values(2,'P',to_date('20040112','YYYYMMDD'));
insert into サンプルデータ values(2,'F',to_date('20040506','YYYYMMDD'));
insert into サンプルデータ values(2,'P',to_date('20040806','YYYYMMDD'));
insert into サンプルデータ values(2,'F',to_date('20041206','YYYYMMDD'));
insert into サンプルデータ values(3,'P',to_date('20040221','YYYYMMDD'));
insert into サンプルデータ values(3,'F',to_date('20040921','YYYYMMDD'));
insert into サンプルデータ values(4,'P',to_date('20040316','YYYYMMDD'));
insert into サンプルデータ values(4,'F',to_date('20041014','YYYYMMDD'));
insert into サンプルデータ values(5,'P',to_date('20040221','YYYYMMDD'));
insert into サンプルデータ values(5,'F',to_date('20040511','YYYYMMDD'));
commit;

SQL

--■■■相関サブクエリとインラインビューを使う方法■■■
select
sum(case when ceil(期間) <= 3 then 1 else 0 end) as "3ヶ月以内",
sum(case when ceil(期間) between 4 and 6 then 1 else 0 end) as "4〜6ヶ月以内",
sum(case when ceil(期間) between 7 and 9 then 1 else 0 end) as "7〜9ヶ月以内",
sum(case when ceil(期間) between 10 and 12 then 1 else 0 end) as "10〜12ヶ月以内"
from
(select
months_between(a.取引日,(select b.取引日 from サンプルデータ b
                          where b.番号=a.番号
                            and b.取引フラグ='P'
                            and b.取引日 = (select max(c.取引日) from サンプルデータ c
                                             where c.番号=b.番号
                                               and c.取引日 < a.取引日))) as 期間
from サンプルデータ a
where 取引フラグ = 'F');

--■■■分析関数とインラインビューを使う方法■■■
select
sum(case when ceil(期間) <=3 then 1 else 0 end) as "3ヶ月以内",
sum(case when ceil(期間) between 4 and 6 then 1 else 0 end) as "4〜6ヶ月以内",
sum(case when ceil(期間) between 7 and 9 then 1 else 0 end) as "7〜9ヶ月以内",
sum(case when ceil(期間) between 10 and 12 then 1 else 0 end) as "10〜12ヶ月以内"
from
(select 期間
  from (select 取引フラグ,
        months_between(取引日,Lag(取引日) over(partition by 番号 order by 取引日)) as 期間
          from サンプルデータ)
where 取引フラグ='F');

解説

インラインビュー内で、期間の月数を求めて、
case式で、期間別に集計してます。

---