Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

1対多の集計を行うSQL

  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加
2010/06/18 14:00

ダウンロード SourceCode (650.0 B)

 OracleのSQLのアンチパターンとして、メンテナンス性や可読性の悪いSQLと、修正したSQLを問題集形式で紹介します。今回は、1対多の集計を行うSQLについてみていきます。

目次

はじめに

 OracleのSQLのアンチパターンとして、メンテナンス性や可読性の悪いSQLと、修正したSQLを問題集形式で紹介します。

対象読者

  • SQLのアンチパターンの問題集を使って理解を深めたい方

必要な環境

 本稿で扱うSQLは、Oracle 11.2.0.1.0で動作確認しました。他のDBでも応用が可能です。

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

 よく見かける1対多の集計を行うSQLを考えます。サンプルデータと修正対象のSQLと出力結果は下記です。

ShopT
ShopID ShopName
1 ABCD
2 EFGH
3 IJKL
AmountT
ShopID Val
1 100
3 300
3 900

 ShopIDごとの、Valの合計を出力します。

修正対象のSQL
select a.ShopID,a.ShopName,sum(nvl(b.Val),0) as sumVal
  from ShopT a Left Join AmountT b
    on a.ShopID = b.ShopID
group by a.ShopID,a.ShopName
order by a.ShopID;
出力結果
ShopID ShopName sumVal
1 ABCD 100
2 EFGH 0
3 IJKL 1200

 上記のSQLでは、AmountTテーブルを使って求める列はsumValという1列のみであることに注目して、シンプルな記述で同じ結果を取得できるSQLに書き直して下さい。

ヒント

 Left Joinによる外部結合を行ってから、group byでグループ化しているため、結構複雑なSQLになってます。相関サブクエリを使って、少しシンプルにしてみます。

修正後のSQLと解説

修正後のSQL
select ShopID,ShopName,
(select nvl(sum(b.Val),0)
   from AmountT b
  where b.ShopID = a.ShopID) as sumVal
from ShopT a
order by ShopID;

 修正後のSQLでは、相関サブクエリを使うことによって、全体のfrom句にはShopTのみを記述すればよくなり、さらにgroup byによるグループ化も不要になりました。全体のfrom句にテーブル指定が1つしかないSQLは、たいていの場合において、可読性の高いSQLになります。

 Oracleのselect文の評価順序は、下記ですので、相関サブクエリを使って、(評価順序の1番目の)全体のfrom句に記述するテーブルの数を減らし、(評価順序の9番目の)select句に参照先のテーブルを記述するのは、select文の処理を脳内でイメージしやすくして、SQLの可読性を上げることになります。

select文の評価順序
 1. from句
 2. where句 (結合条件)
 3. start with句
 4. connect by句
 5. where句 (行のフィルタ条件)
 6. group by句
 7. having句
 8. model句
 9. select句
10. union、minus、intersectなどの集合演算
11. order by句

参考リンク


  • ブックマーク
  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:Oracle SQLアンチパターンの問題集
All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5