はじめに
2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズをPostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃2 （中編）』を、PostgreSQL8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
『自己結合の使い方』と 『相関サブクエリで行と行を比較する』に記載されているSQLをwindow関数を使って記述していきますので、『自己結合の使い方』と『相関サブクエリで行と行を比較する』を読まれてからのほうが理解しやすいと思います。
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 部分的に不一致なキーの検索
まずは、部分的に不一致なキーを検索するSQLについてです。 『自己結合の使い方』では、以下の自己非等値結合を使うSQLが提示されています。
SELECT DISTINCT A1.name, A1.address
FROM Addresses A1,
Addresses A2
WHERE A1.family_id = A2.family_id
AND A1.address <> A2.address ;
これをwindow関数で書き換えてみます。まずは、少しデータを追加した「Addresses」テーブルのデータと出力結果を考えます。
| name | family_id | address |
| 前田 義明 | 100 | 港区虎ノ門3-2-29 |
| 前田 由美 | 100 | 港区虎ノ門3-2-92 |
| 加藤 茶 | 200 | 新宿区西新宿2-8-1 |
| 加藤 勝 | 200 | 新宿区西新宿2-8-1 |
| ホームズ | 300 | ベーカー街221B |
| ワトソン | 400 | ベーカー街221B |
| 織田 信長 | 500 | 京都 |
| 織田 信忠 | 500 | 京都 |
| 織田 長益 | 500 | 京都 |
| 徳川 家康 | 600 | 関ヶ原 |
| 松平 忠吉 | 600 | 関ヶ原 |
| 徳川 秀忠 | 600 | 上田城 |
| name | family_id | address |
| 前田 義明 | 100 | 港区虎ノ門3-2-29 |
| 前田 由美 | 100 | 港区虎ノ門3-2-92 |
| 徳川 家康 | 600 | 関ヶ原 |
| 松平 忠吉 | 600 | 関ヶ原 |
| 徳川 秀忠 | 600 | 上田城 |
Oracleでは分析関数のcount関数で、distinctオプションが使用できますので、下記のSQLが使えましたが、PostgreSQL 8.4では文法エラーになります。
select name,address
from (select name,address,
count(distinct address) over(partition by address)
as distinctAddressCount
from Addresses) a
where distinctAddressCount > 1;
答えとして、下記の4つのSQLを紹介します。
『分析関数の衝撃6 （応用編）』の「1. 複数列のdistinctなcount」を応用し、逆ソートを使ってcount(distinct address) over(partition by family_id)を求める方法。
select name,address
from (select name,address,
-1+dense_rank() over(partition by family_id order by address asc)
+dense_rank() over(partition by family_id order by address desc) as cnt
from Addresses) a
where cnt > 1;
同じfamily_idでaddressが2通り以上あったら、すなわち、最小値と最大値が異なれば出力対象になると考える方法。
select name,address
from (select name,address,
max(address) over(partition by family_id) as MaxAddress,
min(address) over(partition by family_id) as MinAddress
from Addresses) a
where MaxAddress != MinAddress;
PostgreSQL 8.4ではarray_agg関数という集合関数が追加されました。array_agg関数はwindow関数としても使えますので、array_agg関数で配列型にまとめる方法も使えます。
select name,address
from (select name,address,
array_agg(address) over(partition by family_id) as aggAddress
from Addresses) a
where address != any(aggAddress);
select name,address
from (select name,address,
array_agg(address) over(partition by family_id) as aggAddress
from Addresses) a
where aggAddress[1] != any(aggAddress);
SQLのイメージは下記となります。partition by family_idなので、family_idを赤線で区切ってます。
