Oracle SQLアンチパターンの問題集

OracleのSQLのアンチパターンの問題集1

1対多の集計を行うSQL

山岸賢治[著]

更新日: 2010/06/24
公開日: 2010/06/18

ポスト

ダウンロード SourceCode (650.0 B)

2. 1対多の集計を行うSQLのアンチパターン2

　前問の類題として、1対多の集計を行うSQLを再度考えます。サンプルデータ（前問と同じ）と修正対象のSQLと出力結果は下記です。

ShopT

ShopID	ShopName
1	ABCD
2	EFGH
3	IJKL

AmountT

ShopID	Val
1	100
3	300
3	900

　ShopIDごとの、Valの合計とValの最大値とValの最小値を出力します。

修正対象のSQL

select ShopID,ShopName,
(select nvl(sum(b.Val),0)
   from AmountT b
  where b.ShopID = a.ShopID) as sumVal,
(select max(b.Val)
   from AmountT b
  where b.ShopID = a.ShopID) as maxVal,
(select min(b.Val)
   from AmountT b
  where b.ShopID = a.ShopID) as minVal
from ShopT a
order by ShopID;

出力結果

ShopID	ShopName	sumVal	maxVal	minVal
1	ABCD	100	100	100
2	EFGH	0	null	null
3	IJKL	1200	900	300

　上記のSQLでは、相関サブクエリを3回も使っていて、from AmountT b where b.ShopID = a.ShopIDという記述が3回も出現しているので、縦に長いSQLになってしまってます。シンプルな記述で同じ結果を取得できるSQLに書き直して下さい。

ヒント

　相関サブクエリは、便利なのですが、外部結合と使い分けることも大事です。

修正後のSQLと解説

修正後のSQL1

select a.ShopID,a.ShopName,sum(nvl(b.Val,0)) as sumVal,
max(b.Val) as maxVal,min(b.Val) as minVal
  from ShopT a Left Join AmountT b
    on a.ShopID = b.ShopID
group by a.ShopID,a.ShopName
order by a.ShopID;

　上記のSQLでは、外部結合してからgroup by a.ShopID,a.ShopNameでグループ化して、ShopIDごとの、Valの合計とValの最大値とValの最小値を求めてます。外部結合を使うことによって、3つの相関サブクエリを消去できましたね。

　上記のSQLのgroup by a.ShopID,a.ShopNameという記述で、a.ShopNameがグループ化のキーにあるのが冗長な感じがするなら、下記のSQLのように、事前にインラインビューで集約しておいてから外部結合させる方法もあります。

修正後のSQL2

select a.ShopID,a.ShopName,nvl(b.sumVal,0) as sumVal,
b.maxVal,b.minVal
  from ShopT a
  Left Join (select ShopID,sum(Val) as sumVal,
             max(Val) as maxVal,min(Val) as minVal
               from AmountT
             group by ShopID) b
    on a.ShopID = b.ShopID
order by a.ShopID;