Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

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

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

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

目次

はじめに

 「分析関数の衝撃(後編)」に引き続き、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

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5