SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

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

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

1対多の集計を行うSQL


  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード SourceCode (650.0 B)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

はじめに

 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句

参考リンク

会員登録無料すると、続きをお読みいただけます

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

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

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
Oracle SQLアンチパターンの問題集連載記事一覧
この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/4847 2010/06/24 15:25

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング