SQL分析関数 ROW_NUMBER・RANK・DENSE_RANKついでにNTILE

本日も前回に引き続き、SQLのお話である。
前回は、FIRST_VALUEやLAGといった分析関数の基本を紹介した。
今回はというと、RANK、ROW_NUMBERといった順位計算系の分析関数を紹介したい。
ROW_NUMBER
ROW_NUMBERは、OVER句を必要とする分析関数となる。
OracleのROWNUMは、WHERE句を処理する際に割り当てられるため、ORDER BYでソートされた順にはならない、といったことは結構有名である。
なので、ソートした結果で連番としたい場合や、行制限をかけたい場合には、一度サブクエリを作ってやらないといけなかったりして不便であった。
というわけで、ROW_NUMBERはソートした結果で行制限を行う際によく使用されるのだが、実際は分析関数であるので、その他の用途でも、結構便利に使えるわけである。
ROW_NUMBERの機能は、引数で与えられた集合内の「要素数＋1を計算して戻す」だけである。集合を明示的に引数で指定する必要はないので、ROW_NUMBER関数の引数は存在しない。
早速やってみるのだが、その前に、サンプルのテーブルを作成しよう。

CREATE TABLE test_analyze2 (
val INTEGER
);
INSERT INTO test_analyze2 VALUES(1);
INSERT INTO test_analyze2 VALUES(2);
INSERT INTO test_analyze2 VALUES(3);
INSERT INTO test_analyze2 VALUES(3);
INSERT INTO test_analyze2 VALUES(4);
SELECT * FROM test_analyze2;
val
----
1
2
3
3
4

ROW_NUMBERで順番を計算してみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO
FROM test_analyze2
val  NO
---- ----
1    1    {1}
2    2    {1,2}
3    3    {1,2,3}
3    4    {1,2,3,3}
4    5    {1,2,3,3,4}

{}で囲まれている数値は、引数で渡される集合を"仮想的に"表現している。実際のクエリの結果には出現しないものなので、注意して欲しい。同時に、集合の要素数と一致することにも着目して欲しい。
番号は、ORDER BYで指定された順序に従って振られていく。ASCをDESCに変更すると違った結果になってくる。

SELECT val, ROW_NUMBER() OVER (ORDER BY val DESC) NO
FROM test_analyze2
val  NO
---- ----
4    1    {4}
3    2    {4,3}
3    3    {4,3,3}
2    4    {4,3,3,2}
1    5    {4,3,3,2,1}

集合の個数を計算することに変化はないが、要素の並びが降順となっていることに注目して欲しい。SELECT命令にORDER BYを付けていないので、全体のソート順もOracleの都合の良いように降順で出力されるようになった。
RANKとDENSE_RANK
RANKとDENSE_RANKも分析関数である。ROW_NUMBERは、順に連番を振っていくものだが、RANK、DENSE_RANKは順位を計算することができるというシロモノ。
連番と順位はどう違うかと申しますと、連番は、重なる番号が出現しないことに対して、順位は、同じ数値のものが、同じ順位になる、という点。
{1,2,3,3,4}とういう集合を例にして解説しているわけだが、この集合の中には数値3が二つ重複してある。数値が小さい方が優秀ということで話を進めると、1の順位が1位、2の順位が2。3の順位は、両方とも3になる。次の4はというと、ふた通りの考え方があるので、ちょっと後回し。
まずは、ROW_NUMBERとRANK関数を使って行番号と順位を比較してみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO,
RANK() OVER (ORDER BY val ASC) RANK
FROM test_analyze2
val  NO   RANK
---- ---- ----
1    1    1   {1}
2    2    2   {1,2}
3    3    3   {1,2,3}
3    4    3   {1,2,3,3}
4    5    5   {1,2,3,3,4}

ROW_NUMBERは、集合の要素数を計算する。一方、RANK関数は、カレント行の値より前の順位となる要素数を計算して、その値に+1した数値を戻す。
最初の3の行では、{1,2,3}が集合として関数に渡り、3より順位が高い1と2の二つがあることがわかる。2+1を計算して3が結果として戻される。
次の3の行では、{1,2,3,3}が集合として関数に渡り、3より順位が高い1と2の二つがあることがわかる。2+1を計算して3が結果として戻される。
これで、データ3の順位はどちらも3位となリ、ROW_NUMBERとの差が発生する。
DENSE_RANK
ここで、後回しにしていた、次の4まで考えてみよう。3については、同値の行が2行あるので「どちらも3位になる」ことがわかった。
次の4の行では、{1,2,3,3,4}が集合として関数に渡り、4より順位が高い1,2,3,3の4つがあることがわかる。4+1を計算して5が結果として戻される。
3位の次だから4位とはならず「同値であった行数分だけ順位が飛ぶ」のがRANK関数の考え方。順位が飛んでしまう現象を「ギャップ」と呼ぶこともある。
RANK関数ではなく、DENSE_RANK関数を使うと少し違った様子になる。やってみよう。

SELECT val, ROW_NUMBER() OVER (ORDER BY val ASC) NO,
RANK() OVER (ORDER BY val ASC) RANK,
DENSE_RANK() OVER (ORDER BY val ASC) DRNK
FROM test_analyze2
val  NO   RANK DRNK
---- ---- ---- ----
1    1    1    1  {1}
2    2    2    2  {1,2}
3    3    3    3  {1,2,3}
3    4    3    3  {1,2,3}
4    5    5    4  {1,2,3,4}

DENSE_RANKでも、より順位が高い要素の数＋1を計算する。異なるのは、集合から重複が取り除かれている点。つまり、DISTINCTされた状態で個数を計算するということ。なので、DENSE_RANKでは3位の次だから4位となり「同値である行があっても順位は飛ばない」ギャップが発生しない。
NTILE
さて、分析関数を使うことで、順位付けすることが簡単にできることがわかって頂けたかと思う。
分析関数が存在しなかった時代では、サブクエリを使って行番号や順位を計算していた。サブクエリとすると、SELECT命令が長くて複雑なものとなりやすい。分析関数を使えば、サブクエリよりも簡潔に分かりやすく記述できる。是非とも有益に使っていきたいものである。
サブクエリでなく、ユーザー定義関数を作ってしまう、という手もあり、自分でも結構関数を作ってきた。しかし、関数を作る際には、どうしても、スカラ値を返さなくてはならず、なんか面倒なことになりがち。分析関数で済むのなら、そっちの方が楽。
単純な順位付けなら、RANK関数で十分かも知れない。しかし、データ分析を実際にやろうとすると、データが大量にある場合などは、4つくらいのグループに分けて全体の傾向をつかみたい、ということもあるかと思う。
NTILEを使用すれば、そう言った順位付けを行うことが可能となる。
一つの例として、ヒストグラムを計算する際のSQLを例にしてみたい。ヒストグラムっていうのは、以下のようなデータの分布状態を視覚化したグラフ。デジイチユーザなら見たことがあるはず。

デジタルカメラの場合は、R,G,Bの階調データをそれぞれの色で3つのグラフで表示したりする。階調も256くらいあるので、例としてはデータが大き過ぎる。なので、白黒で8階調ということにしよう。
8階調なので、数値は0〜7までの値となる。画素数は4x4=16個にしようか。
0 1 0 4
0 1 7 0
6 2 7 1
0 3 7 2
こんな感じの画像データがあったとする。画像に直すと、どうなるかちょっとよく分からないが、まぁなんとなくわかるでしょう。
実際のテーブルには、以下のような感じで行を作った。

SELECT * FROM test_analyze3
val
----
01
04
01
7
06
以下略

集計を行うにあたっては、ピクセルの位置は関係ないので、ピクセルに出力している階調データのみを行のデータとして記録しているだけの単純な構造にした。
ここから、階調0を出力しているデータの個数、階調1を出力している個数、階調2と、順に7まで集計すれば、ヒストグラムを表示することができる。数の集計なので、COUNT使って、GROUP BYすればOKかも。まぁ、そうですね。分析関数使うまでもないか。

SELECT val X, COUNT(val) Y
FROM test_analyze3
GROUP BY val
ORDER BY val
X    Y
---- ----
0    5
1    3
2    2
3    1
4    1
6    1
7    3

これを、そのままグラフにすると、以下のようになる。

5 *
4 *
3 * *           *
2 * * *         *
1 * * * * *   * *
0 1 2 3 4 5 6 7

テキストで作成したのでショボイが、なんとなく、ヒストグラムっぽいでしょ？
しかし、分析関数使ってませんね。普通にGROUP BYとCOUNTで済んでしまった。まぁ、SQLの潜在能力を「思い知らされただけ」っていう話もあるが。そうは言っても、分析関数である。
ヒストグラムを表示すると言っても、実際は、8階調っていうことはないわけで、フルカラーを表現するには、24ビット整数の精度くらいは必要、実際はもっと大きい数値になる。さらに、画面の制約があるので幅は100ドットくらいでグラフを表示したい...
で、分析関数を使えば、そう言ったことに柔軟に対応できるようになるのである。
ここで、ビューを作成する。一度は集計処理はしないといけないので、そのためのビューを定義してしまう。

CREATE VIEW v_test_analyze3 AS
SELECT val X, COUNT(val) Y
FROM test_analyze3
GROUP BY val
SELECT * FROM v_test_analyze3 ORDER BY X
X    Y
---- ----
0    5
1    3
2    2
3    1
4    1
6    1
7    3

前のクエリをそのままビューにしただけなので、結果も変わらない。このビューの結果を元にさらに集計していく。
まず、画面に収めるため、Xが最大7であるところを半分の3にしてみたい。8階調であるところを、半分の4にしたいのである。そうすれば、小さい画面でもヒストグラムを表示できる。
いろいろなやり方はあると思うが、分析関数のNTILEを使ってやってみることにする(やっと出てきました分析関数)。
NTILEには、グループをいくつのグループに分割するのかを引数で指定できる。現段階での課題は、4つのグループに分割して、ヒストグラムを表示させたいので、NTILEの引数には4を指定する。
NTILEで分割したグループは「バケット」と呼ばれる。

SELECT X, Y, NTILE(4) OVER (ORDER BY X ASC) BUCT
FROM v_test_analyze3
ORDER BY X
X    Y    BUCT
---- ---- ----
0    5    1
1    3    1
2    2    2
3    1    2
4    1    3
6    1    3
7    3    4

BUCTが1となっている部分が、1のバケットに含まれているデータ行になる。同様に2なら2のバケットと分割数に指定した4まで続く。
この段階では、全体を4つのバケットに分割して連番を振っただけに過ぎない。BUCTの列がその連番になっていると理解して欲しい。
前回の8階調ヒストグラムでは、X毎にデータの個数を計算したのだが、今回はこれを、BUCT毎に計算するのである。Yの値は、バケット内のデータを合計する。BUCTは4分割したのだから、4階調に変換できるはず。

SELECT BUCT - 1 X, SUM(Y) Y
FROM (SELECT Y, NTILE(4) OVER (ORDER BY X ASC) BUCT
FROM v_test_analyze3) SUB
GROUP BY SUB.BUCT
ORDER BY X
X    Y
---- ----
0    8
1    3
2    2
3    3

グラフにしたら

8 *
7 *
6 *
5 *
4 *
3 *   *       *
2 *   *   *   *
1 *   *   *   *
0   1   2   3

こうなる。前の8階調のグラフと比較してみる。

5 *
4 *
3 * *           *
2 * * *         *
1 * * * * *   * *
0 1 2 3 4 5 6 7

なるほど、Xが0と1がバケット0なので、Yの合計がバケット0の数値8になっている。Xが2と3でバケット1だから、2＋1で3なのか。ちなみに、グラフの*の数はどちらも画素数の16個である。
なかなか良さそうではある、がしかし。
階調5のデータは0件なので、X={4,5}のバケットとX={6,7}のバケットが怪しい感じになっている。ちゃんとしたヒストグラムにしたい時は、5のところに0が入ってこないといけないことがわかった。まぁ、サンプルなので目をつむってもらうとしよう。
本日は、ここまで。

投稿者プロフィール

asai

最新の投稿

• コンピューター関係の記事2025年4月26日アイキャッチ画像にSQLアイコンとJSONアイコンが欲しい
• コンピューター関係の記事2025年4月23日AdSense審査に無事パス
• コンピューター関係の記事2025年4月22日SSブログ復活
• コンピューター関係の記事2025年4月22日SSブログが無くなった余波は大きい