はじめに
2009年7月に正式リリースされたPostgreSQL8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズを、PostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃4（完結編）』をPostgreSQL8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
『SQLで集合演算』と 『帰ってきたHAVING句』に記載されているSQLをwindow関数を使って記述していきますので、『SQLで集合演算』と『帰ってきたHAVING句』を、読まれてからの方が理解しやすいと思います。
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 2つのテーブルが相等なら「等しい」、そうでなければ「異なる」を返すクエリ
まずは「2つのテーブルが相等か」を判定し、結果を返すクエリについて考えてみましょう。『SQLで集合演算』では、以下のSQLが提示されています。
SELECT CASE WHEN COUNT(*) = 0
THEN '等しい'
ELSE '異なる' END AS result
FROM ((SELECT * FROM tbl_A
UNION
SELECT * FROM tbl_B)
except all
(SELECT * FROM tbl_A
INTERSECT
SELECT * FROM tbl_B)) TMP;
これをwindow関数で書き換えてみます。まずは、数学の視点から集合が等しいという意味について考察してみます。数学の集合では集合の相等性を調べる公式として、次の式が成立することが知られています。
（A ⊆ B ） かつ （A ⊇ B） ⇔ （A = B）
一方、下記の場合も同様です。
（集合Aと集合Bの要素数が等しい） かつ （A ⊆ B） ⇔ （A = B）
集合Aと集合Bが両方とも空集合の場合に式が正しいことは明らかです。また、両方とも空集合でない場合は、A = Bの場合のみ左辺の条件が成立します。
要素数はwindow関数のcount関数を使えば求められますし、包含関係は差集合が空集合となるかを調べれば分かります。window関数で書き換えたSQLは下記となります。
select case when count(*) = 0
then '等しい'
else '異なる' end as result
from (select count(*) over(),* from tbl_A
except all
select count(*) over(),* from tbl_B) a;
ただし、上記のSQLだと、tbl_Aが空集合（レコードがない）の場合は、必ず等しいと判定されてしまいます。これを考慮したSQLは下記のようになります。
select case when count(*) = 0
then '等しい'
else '異なる' end as result
from ((select count(*) over(),* from tbl_A
except all
select count(*) over(),* from tbl_B)
union all
(select count(*) over(),* from tbl_B
except all
select count(*) over(),* from tbl_A)) a;
実際の業務においてはテーブルの中身や、2つのSQLの出力結果を比較する場合、空集合を考慮する必要がないことがほとんどです。また、SQLの結果表示を途中で中止することもできるので、通常は下記のSQLで十分でしょう。
select count(*) over(),* from tbl_A except all select count(*) over(),* from tbl_B;
