はじめに
2009年7月に正式リリースされたPostgreSQL 8.4で、分析関数（window関数）がサポートされました。本連載では、分析関数の衝撃シリーズをPostgreSQL用にアレンジした内容と、OracleやDB2の分析関数をPostgreSQL 8.4で代用する方法を扱います。
本稿では、『分析関数の衝撃6（応用編）』を、PostgreSQL 8.4用にリニューアルした内容を扱います。
対象読者
- PostgreSQLでwindow関数を使ってみたい方
- 分析関数の理解を深めたい方
必要な環境
本稿で扱うSQLは、PostgreSQL 8.4 beta 2で動作確認しました。その他、次の環境でも応用が可能です。
- Oracle
- DB2
- SQL Server
1. 複数列のdistinctなcount
最初は、複数列のdistinctなcountを求めるSQLです。まずは、テーブルのデータと、出力結果を考えます。
| ID | ColA | ColB |
| 1 | ab | c |
| 2 | a | bc |
| 3 | a | b |
| 4 | a | b |
| 5 | a | b |
ColA,ColBの組み合わせのdistinctなcountを求めます。(ColA,ColB) = (ab,c),(a,bc),(a,b)で3通りの組み合わせがあるので、これをdisCntとして下記の形で出力します。
| ID | ColA | ColB | disCnt |
| 1 | ab | c | 3 |
| 2 | a | bc | 3 |
| 3 | a | b | 3 |
| 4 | a | b | 3 |
| 5 | a | b | 3 |
集約関数で、複数列のdistinctなcountを取得する方法として、行コンストラクタを使った下記の方法があります。
with tmp(ID,Val1,Val2) as(
values('AA',1,1),
('AA',1,1),
('AA',2,2),
('BB',1,2),
('BB',2,1),
('BB',2,1),
('BB',3,1))
select ID,count(distinct Row(Val1,Val2)) as disCnt
from tmp
group by ID;
| ID | disCnt |
| AA | 2 |
| BB | 3 |
上記をふまえて、window関数のcount関数で同じようにdistinctオプションを使用すればいいように思えますが、 PostgreSQL 8.4では、window関数でのdistinctオプションが実装されていないので、文法エラーになります。
select ID,ColA,ColB, count(distinct Row(ColA,ColB)) over() as disCnt from IDTable; ERROR: DISTINCT is not implemented for window functions LINE 2: count(distinct Row(ColA,ColB)) over() as disCnt
答えは、下記の逆ソートを使う方法となります。
select ID,ColA,ColB, -1+dense_rank() over(order by ColA asc ,ColB asc ) +dense_rank() over(order by ColA desc,ColB desc) as disCnt from IDTable order by ID;
order by ColA asc,ColB ascでのascは省略可能なのですが、descと対比させたほうが分かりやすいと考え、省略せずに記述しています。
下記の数式をふまえてdense_rank関数を使っています。-1を加算しているのは、自分を2回数えているからです。
-1 + 重複を除いた自分以上の組み合わせの数 + 重複を除いた自分以下の組み合わせの数 = 重複を除いた組み合わせの数
