はじめに
「分析関数の衝撃（後編）」に引き続き、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が提示されています（ここでは、EXCEPTをminusにしています）。
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は次のようになります。
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は次のようになります。
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で十分でしょう。
select count(*) over(),a.* from tbl_A a minus select count(*) over(),b.* from tbl_B b
