7-70 クロスジョインして分析関数

SQLパズル

UserLogテーブル
UserID  SyouhinID
------  ---------
ID0001  AAA
ID0001  BBB
ID0001  CCC
ID0002  AAA
ID0003  AAA
ID0003  DDD
ID0004  EEE

SyouhinMasterテーブル
SyouhinID  Val
---------  ---
AAA        001
BBB        002
CCC        003
DDD        004
EEE        005

UserLogテーブルのUserIDごとに
SyouhinMasterテーブルの、
SyouhinIDのデータを持てば1、持たなければ0を
Flagとして以下の形式で出力する

出力結果
UserID  Val  Flag
------  ---  ----
ID0001  001     1
ID0001  002     1
ID0001  003     1
ID0001  004     0
ID0001  005     0
ID0002  001     1
ID0002  002     0
ID0002  003     0
ID0002  004     0
ID0002  005     0
ID0003  001     1
ID0003  002     0
ID0003  003     0
ID0003  004     1
ID0003  005     0
ID0004  001     0
ID0004  002     0
ID0004  003     0
ID0004  004     0
ID0004  005     1

データ作成スクリプト

create table UserLog as
select 'ID0001' as UserID,'AAA' as SyouhinID from dual
union select 'ID0001','BBB' from dual
union select 'ID0001','CCC' from dual
union select 'ID0002','AAA' from dual
union select 'ID0003','AAA' from dual
union select 'ID0003','DDD' from dual
union select 'ID0004','EEE' from dual;

create table SyouhinMaster as
select 'AAA' as SyouhinID,'001' as Val from dual
union select 'BBB','002' from dual
union select 'CCC','003' from dual
union select 'DDD','004' from dual
union select 'EEE','005' from dual;

SQL

--■■■分析関数を使う方法■■■
select distinct a.UserID,b.Val,
sum(decode(a.SyouhinID,b.SyouhinID,1,0)) over(partition by a.UserID,b.Val) as Flag
  from UserLog a,SyouhinMaster b
order by UserID,Val;

--■■■グループ化する方法■■■
select a.UserID,b.Val,
sum(decode(a.SyouhinID,b.SyouhinID,1,0)) as Flag
  from UserLog a,SyouhinMaster b
 group by a.UserID,b.Val
order by a.UserID,b.Val;

--■■■SQL99の結合構文を使う方法■■■
select c.UserID,b.Val,nvl2(a.SyouhinID,1,0) as Flag
  from SyouhinMaster b cross join (select distinct UserID from UserLog) c
       Left Join UserLog a
    on a.UserID = c.UserID
   and a.SyouhinID = b.SyouhinID
order by c.UserID,b.Val;

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.UserID,a.Val,nvl2(b.SyouhinID,1,0) as Flag
  from SyouhinMaster a
Left Join UserLog b
partition by (b.UserID)
    on (a.SyouhinID = b.SyouhinID)
order by b.UserID,a.Val;

解説

パフォーマンスの上では、
表に二回アクセスするのを避けるか
クロスジョインを避けるか
になりますね

10g以降なら、Partitioned Outer Joinを使ってもいいでしょう。
3-34 Partitioned Outer Join

---