10-210 複数列指定のPartitioned Outer Join

SQLパズル

purchaseテーブル
Customer  Item   Color  Num
--------  -----  -----  ---
john      shirt  blue     2
john      shoes  black    1
mary      jeans  blue     1
mary      jeans  black    1
mary      shirt  blue     3
mary      shoes  blue     2

Customer,Itemごとで
Colorの行を補完する。(補完した行のNumは0とする)

出力結果
Customer  Item   Color  Num
--------  -----  -----  ---
john      shirt  blue     2
john      shirt  black    0
john      shoes  blue     0
john      shoes  black    1
mary      jeans  blue     1
mary      jeans  black    1
mary      shirt  blue     3
mary      shirt  black    0
mary      shoes  blue     2
mary      shoes  black    0

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table purchase(Customer,Item,Color,Num) as
select 'john','shirt','blue' ,2 from dual union
select 'john','shoes','black',1 from dual union
select 'mary','jeans','black',1 from dual union
select 'mary','jeans','blue' ,1 from dual union
select 'mary','shirt','blue' ,3 from dual union
select 'mary','shoes','blue' ,2 from dual;

SQL

--■■■Partitioned Outer Joinを使う方法(10g以降)■■■
select b.Customer,b.Item,a.Color,nvl(b.Num,0) as Num
  from (select distinct Color from purchase) a
  Left join purchase b
 partition by (b.Customer,b.Item)
    on (a.Color = b.Color)
order by b.Customer,b.Item,a.Color desc;

--■■■SQL99構文で結合させる方法■■■
select a.Customer,a.Item,b.Color,nvl(c.Num,0) as Num
  from     (select distinct Customer,Item from purchase) a
cross Join (select distinct Color from purchase) b
 Left Join purchase c
   on (a.Customer = c.Customer
   and a.Item     = c.Item
   and b.Color    = c.Color)
order by a.Customer,a.Item,b.Color desc;

--■■■グループ化する方法■■■
select b.Customer,b.Item,a.Color,
sum(case when  b.Color=a.Color
         then b.Num else 0 end) as Num
  from (select distinct Color from purchase) a,purchase b
group by b.Customer,b.Item,a.Color
order by b.Customer,b.Item,a.Color desc;

--■■■grouping setsを使う方法■■■
select customer,item,
case when g_blue  = 1 then 'blue'
     when g_black = 1 then 'black' end as Color,num
from (select customer,item,
      grouping('blue') as g_blue,
      grouping('black') as g_black,
      case when grouping('blue')  = 1
           then sum(decode(Color,'blue',num,0))
           when grouping('black') = 1
           then sum(decode(Color,'black',num,0)) end as num
        from purchase
      group by grouping sets((customer,item,'blue'),(customer,item,'black')))
order by customer,item,Color desc;

解説

Left Joinを使う、Partitioned Outer Joinでは、
マスタデータの表別名がa
補完するデータの表別名がb
となります。

Left Joinの仕組みと、
補完するデータが0件だった場合を考えると
分かりやすいかもしれません。

3-34 Partitioned Outer Join

---