5-4 条件に応じた、他レコードの存在チェック

SQLパズル

会員リストテーブル
会員  開始日  終了日
----  ------  ------
山田  200201  200212  ← 過去会員
山田  200401  200406  ← 過去会員
山田  200501  211106  ← 現会員
山田  222203  244409  ← 予約会員
佐藤  200503  200509  ← 過去会員
佐藤  200511  200512  ← 過去会員
田中  222203  233309  ← 予約会員
岡本  200101  200505  ← 過去会員

テーブルが上記の構成の時､
現会員という条件で3番のレコードのみを、
3番のレコードを持たないときに予約会員という条件で4番のレコードのみを、
3番､4番がないときに過去会員1,2番のレコードを、
会員ごとに抽出する。

定義は
現会員    現在利用している人
予約会員  現在利用していないが将来利用する人
過去会員  現会員でなく予約会員でもないが過去に利用したことがある人

出力結果
会員種類  会員  開始日  終了日
--------  ----  ------  ------
過去会員  岡本  200101  200505
過去会員  佐藤  200503  200509
過去会員  佐藤  200511  200512
現会員    山田  200501  211106
予約会員  田中  222203  233309

データ作成スクリプト

create table 会員リスト(
会員 varchar2(4),
開始日 char(6),
終了日 char(6));

insert into 会員リスト values('山田','200201','200212');
insert into 会員リスト values('山田','200401','200406');
insert into 会員リスト values('山田','200501','211106');
insert into 会員リスト values('山田','222203','244409');
insert into 会員リスト values('山田','200501','200506');
insert into 会員リスト values('山田','200503','200509');
insert into 会員リスト values('佐藤','200503','200509');
insert into 会員リスト values('佐藤','200511','200512');
insert into 会員リスト values('田中','222203','233309');
insert into 会員リスト values('岡本','200101','200505');
commit;

SQL

--■■■unionで和集合を求める方法■■■
select '過去会員' as 会員種類,会員,開始日,終了日
from 会員リスト a
where 終了日 < to_char(sysdate,'YYYYMM')
  and not exists(select 1 from 会員リスト b
                  where b.会員=a.会員
                    and (to_char(sysdate,'YYYYMM') between b.開始日 and b.終了日
                      or b.開始日>to_char(sysdate,'YYYYMM')))
union all select '現会員',会員,開始日,終了日
from 会員リスト
where to_char(sysdate,'YYYYMM') between 開始日 and 終了日
union all select '予約会員',会員,開始日,終了日
from 会員リスト a
where 開始日>=to_char(sysdate,'YYYYMM')
　and not exists(select 1 from 会員リスト b
                  where b.会員=a.会員
                    and (to_char(sysdate,'YYYYMM') between b.開始日 and b.終了日))
order by 1,3,4;

--■■■分析関数とブール代数を組み合わせる方法■■■
select 会員種類,会員,開始日,終了日
from (select 会員,開始日,終了日,
      max(case when to_char(sysdate,'YYYYMM') between 開始日 and 終了日
               then 1 else 0 end) over(partition by 会員) as "has現会員",
      max(case when to_char(sysdate,'YYYYMM') < 終了日
               then 1 else 0 end) over(partition by 会員) as "has予約会員",
      case when 終了日 < to_char(sysdate,'YYYYMM')
           then '過去会員'
           when to_char(sysdate,'YYYYMM') between 開始日 and 終了日
           then '現会員'
           else '予約会員' end as 会員種類
      from 会員リスト)
where 会員種類 = '現会員'
   or (会員種類 = '予約会員' and "has現会員" = 0)
   or (会員種類 = '過去会員' and "has現会員" = 0 and "has予約会員" = 0)
order by 会員種類,開始日,終了日;

--■■■Rank関数で順位をつける方法■■■
select 会員種類,会員,開始日,終了日
from (select 会員種類,会員,開始日,終了日,
      rank() over(partition by 会員
                  order by
                  case 会員種類
                  when '現会員'   then 1
                  when '予約会員' then 2
                  else 3 end) as rn
      from (select 会員,開始日,終了日,
            case when 終了日 < to_char(sysdate,'YYYYMM')
                 then '過去会員'
                 when to_char(sysdate,'YYYYMM') between 開始日 and 終了日
                 then '現会員'
            else '予約会員' end as 会員種類
              from 会員リスト))
where rn=1
order by 会員種類,開始日,終了日;

解説

unionで和集合を求める方法では、会員の種類に応じて、
他レコードに対する存在チェックを行って、unionで和集合を求めてます。

現行会員の集合をA、予約会員の集合をB、過去会員の集合をCとすると、
A∪¬A∩B∪(¬A∪¬B)∩C
が求める集合となります。

---