はじめに
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を赤線で区切ってます。