はじめに
今回の応用編では、分析関数の変わった使い方を紹介します。
対象読者
- SQLの可読性を向上させたい方
- SQLのパフォーマンスを向上させたい方
2問目で3値倫理に関連する問題を、4問目でcase式に関連する問題を取り上げますので、3値論理とNULLとCASE式のススメ（前編）を先に読んでおくと理解が進むと思います。
必要な環境
本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。ソースコードはDB2 V9.5でも動作確認しました。その他、次の環境でも応用が可能です。
- Oracle9i以降
- DB2
- SQL Server 2005以降
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 |
下記は、残念ながら文法エラーとなります。count関数で2つ以上の引数の指定は認められていないのです。
select ID,ColA,ColB, count(distinct ColA,ColB) over() as discnt from IDTable;
どうにかして引数を1つにまとめようということで、文字データとしてのカンマが存在しないならば、カンマを区切り文字に使って文字を連結させた下記のSQLでもいいでしょう。
select ID,ColA,ColB, count(distinct ColA || ',' || ColB) over() as discnt from IDTable order by ID;
しかし、上記のSQLではColAとColBが数値型や日付型だった場合に、文字型にキャストしないと暗黙キャストが発生します。
そこで、別解として下記の逆ソートを使うSQLがあります。count関数で2つ以上の引数の指定はできませんが、dense_rank関数であれば、2つ以上の引数を指定できるのです。
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 ColA asc,ColB ascでのascは省略可能ですが、descと対比させたほうが分かりやすいと考え、省略していません。
下記の数式をふまえてdense_rank関数を使っています。-1を加算しているのは、自分を2回数えているからです。
-1 + 重複を除いた自分以上の組み合わせの数 + 重複を除いた自分以下の組み合わせの数 = 重複を除いた組み合わせの数
下記SQLの結果を考えると理解しやすいでしょう。
select ID,ColA,ColB, dense_rank() over(order by ColA asc ,ColB asc ) as Rn, dense_rank() over(order by ColA desc,ColB desc) as RevRn, -1+dense_rank() over(order by ColA asc ,ColB asc ) +dense_rank() over(order by ColA desc,ColB desc) as "-1+Rn+RevRn" from IDTable order by ID;
| ID | ColA | ColB | Rn | RevRn | -1+Rn+RevRn |
| 1 | ab | c | 3 | 1 | 3 |
| 2 | a | bc | 2 | 2 | 3 |
| 3 | a | b | 1 | 3 | 3 |
| 4 | a | b | 1 | 3 | 3 |
| 5 | a | b | 1 | 3 | 3 |
