Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

PostgreSQLの分析関数の衝撃4
(集合の一致と全称肯定命題)

array_agg関数とbool_and関数の使用例

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

ダウンロード SourceCode (2.4 KB)

 2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数(window関数)がサポートされました。本稿では、『分析関数の衝撃4(完結編)』を、PostgreSQL8.4用にリニューアルした内容を扱います。

目次

はじめに

 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が提示されています。

2つのテーブルが相等なら「等しい」、そうでなければ「異なる」を返すクエリ
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は下記となります。

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は下記のようになります。

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)
        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;

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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