SHOEISHA iD

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

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

分析関数の衝撃

分析関数の衝撃(完結編)

CodeZineに掲載されたSQLを分析関数で記述する 4

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

ダウンロード ソースコード (4.6 KB)

SQLを使う業務では分析関数を使いこなすと、生産性とSQLの可読性とパフォーマンスを、大きく向上させることができます。分析関数を使う際の考え方と、処理のイメージを解説します。

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

はじめに

 「分析関数の衝撃(後編)」に引き続き、CodeZineのミック氏の記事で記載されたSQLを、分析関数を使って記述していきます。完結編では、数学を扱ったSQLを主に解説します。

対象読者

  • SQLの可読性を向上させたい方
  • SQLのパフォーマンスを向上させたい方

 この記事では、「SQLで集合演算」と「帰ってきたHAVING句」に記載されているSQLを、分析関数を使って記述します。先にそれらを読んでおくと理解が進むと思います。

必要な環境

 本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。ソースコードはDB2 V9.5でも動作確認しました。その他、次の環境でも応用が可能です。

  • Oracle9i以降
  • DB2
  • SQL Server 2005以降

「2つのテーブルが相等か」を判定するクエリ

 まずは「2つのテーブルが相等か」を判定し、結果を返すクエリについて考えてみましょう。「SQLで集合演算」では、以下のSQLが提示されています(ここでは、EXCEPTminusにしています)。

2つのテーブルが相等なら「等しい」、そうでなければ「異なる」を返すクエリ
SELECT CASE WHEN COUNT(*) = 0
            THEN '等しい'
            ELSE '異なる' END AS result
  FROM ((SELECT * FROM  tbl_A
         UNION
         SELECT * FROM  tbl_B)
         minus
        (SELECT * FROM  tbl_A
         INTERSECT
         SELECT * FROM  tbl_B)) TMP;

 これを分析関数で書き換えてみます。まずは、数学の視点から集合が等しいという意味について考察してみます。数学の集合では集合の相等性を調べる公式として、次の式が成立することが知られています。

(A ⊆ B ) かつ (A ⊇ B) ⇔ (A = B)

 一方、次の場合も同様です。

(集合Aと集合Bの要素数が等しい) かつ (A ⊆ B) ⇔ (A = B)

 集合Aと集合Bが両方とも空集合の場合に式が正しいことは明らかです。また、両方とも空集合でない場合は、A = Bの場合のみ左辺の条件が成立します。

 要素数は分析関数のcount関数を使えば求められますし、包含関係は差集合が空集合となるかを調べれば分かります。分析関数で書き換えたSQLは次のようになります。

分析関数で書き換えたSQL
select case when count(*) = 0
            then '等しい'
            else '異なる' end as result
  from (select count(*) over(),a.* from tbl_A a
         minus
        select count(*) over(),b.* from tbl_B b);

 ただし、上記のSQLだと、tbl_Aが空集合(レコードがない)の場合は、必ず等しいと判定されてしまいます。これを考慮したSQLは次のようになります。

分析関数で書き換えたSQL(空集合を考慮)
select case when count(*) = 0
            then '等しい'
            else '異なる' end as result
  from ((select count(*) over(),a.* from tbl_A a
          minus
         select count(*) over(),b.* from tbl_B b)
        union all
        (select count(*) over(),b.* from tbl_B b
          minus
         select count(*) over(),a.* from tbl_A a));

 ただし、実際の業務においてはテーブルの中身や、2つのSQLの出力結果を比較する場合、空集合を考慮する必要がないことがほとんどです。また、SQLの結果表示を途中で中止することもできるので、通常は次のSQLで十分でしょう。

結果が0件なら「等しい」、1件以上なら「異なる」
select count(*) over(),a.* from tbl_A a
 minus
select count(*) over(),b.* from tbl_B b

次のページ
関係除算を表現する

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

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

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

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

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

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

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/1593 2008/07/22 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

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

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

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

メールバックナンバー

アクセスランキング

アクセスランキング