SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ：AABlog：So-netブログ

	ブログをはじめるログイン

TX-8050壊れる代替機を選ぶ｜続 TX-8050壊れる　DENON C.. ブログトップ

SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ　[SQLポケリ] [編集]

本日もSQL分析関数である。

本日は、予定していた通り、CUME_DISTを紹介したい。
マニュアルを見ると、CUME_DISTは、累積分布を計算する分析関数、と書いてある。

はーん、わかったようなわからないような。

また、CUME_DISTは0〜1.0の範囲で値を戻す、とも書いてある。

なるほど、パーセンタイル値と同じか。
じゃあ、PERCENT_RANKと同じじゃない？とも思ってみたが、そうでもないみたい。
比較してみた。

-- PERCENT_RANK

SELECT val, PERCENT_RANK() OVER (ORDER BY val)
 FROM test_analyze3;

val  PRNK
---- ----
   1    0   -- {1}          0/4 0.0
   2 0.25   -- {1,2}        1/4 0.25
   3  0.5   -- {1,2,3}      2/4 0.5
   3  0.5   -- {1,2,3,3}    2/4 0.5
   4    1   -- {1,2,3,3,4}  4/4 1.0


-- CUME_DIST

SELECT val, CUME_DIST() OVER (ORDER BY val)
 FROM test_analyze3;

val  CDST
---- ----
   1  0.2   -- {1}          1/5 0.2
   2  0.4   -- {1,2}        2/5 0.4
   3  0.8   -- {1,2,3}      4/5 0.8
   3  0.8   -- {1,2,3,3}    4/5 0.8
   4    1   -- {1,2,3,3,4}  5/5 1.0

計算式の違いは、以下のようになる。
PERCENT_RANKは、RANK-1 / 全体の個数-1
CUME_DISTは、集合の個数 / 全体の個数

分子の方は微妙に異なる。分母の方は、全体の個数を-1するかどうかの違いである。

これだけでCUME_DISTの解説はおしまい。というのも寂しいので、解説していないPARTITION BYについても書いてみたいと思う。

PARTITION BY

分析関数は、OVER句で順序を指定する必要がある。これまでの例では、ORDER BYで順序のみを指定していたのだが、ここにPARTITION BYでグループ化の方法を指定できる。
グループ分けできるサンプルデータを作成して、PARTITION BYをやってみよう。

CREATE TABLE test_analyze5 (
 type INTEGER,
 val INTEGER
);

INSERT INTO test_analyze5 VALUES(1, 1);
INSERT INTO test_analyze5 VALUES(1, 2);
INSERT INTO test_analyze5 VALUES(1, 3);
INSERT INTO test_analyze5 VALUES(1, 4);
INSERT INTO test_analyze5 VALUES(2, 1);
INSERT INTO test_analyze5 VALUES(2, 1);
INSERT INTO test_analyze5 VALUES(2, 2);
INSERT INTO test_analyze5 VALUES(2, 3);
INSERT INTO test_analyze5 VALUES(2, 4);

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME
---- ---- ----
   1    1 0.25
   1    2  0.5
   1    3 0.75
   1    4    1
   2    1  0.4
   2    1  0.4
   2    2  0.6
   2    3  0.8
   2    4    1

PARTITION BYは、OVER句のカッコの中に記述する。ORDER BYは必須だが、PARTITION BYは任意。順番は、PARTITION BY、ORDER BYの順に書く必要がある。SELECT命令でのGROUP BYとORDER BYの関係と同じなので覚えやすいと思う。
PARTITION BYのグループ化指定方法も、GROUP BYと同じ。例では、type列の値別にグループ化してみた。

type val  CUME
---- ---- ----
   1    1 0.25  -+
   1    2  0.5   +- type=1のグループ
   1    3 0.75   |
   1    4    1  -+
   2    1  0.4  -+
   2    1  0.4   |
   2    2  0.6   +- type=2のグループ
   2    3  0.8   |
   2    4    1  -+

PARTITION BYを省略すると、全体が一つのグループになる。
例では、type列の値別にPARTITION BYしている。type=1とtype=2の二つのグループに分けられ、その中でvalの値が昇順で並び替えられていることがわかる。

COUNT OVER

さて、ここまでは分析関数に対してOVER句を指定して、どういった計算が行われるのかを紹介してきた。実は、従来からある、集計関数もOVER句を付けて分析関数のように使うこともできる。
その例として、COUNT関数をOVER句付きで使ってみよう。上記のCUME_DISTの例では、type=1のグループとtype=2のグループで行数が異なっていた。これをCOUNT関数で計算してみよう。

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME,
  COUNT(*) OVER (PARTITION BY type) CONT
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME CONT
---- ---- ---- ----
   1    1 0.25    4
   1    2  0.5    4
   1    3 0.75    4
   1    4    1    4
   2    1  0.4    5
   2    1  0.4    5
   2    2  0.6    5
   2    3  0.8    5
   2    4    1    5

COUNTは集計関数である。OVERを付けなければ、集計関数なので、SELECT命令にGROUP BYを付けて使用することが多いと思う。しかし、OVER句を付けて、分析関数として使えば、グループ化の方法は、OVER句のPARTITION BYの内容で決定できる。
COUNTで個数を集計するにあたり、データの並び順は重要ではなく、必要のない情報であるため、「ORDER BYを省略できる」。一方、順番に並べることで計算を行っていた分析関数では、ORDER BYを省略することはできない。
PARTITION BYはグループ化の必要がなければ、COUNTでも省略可能である。

では、COUNTのOVER句でORDER BYを指定しても意味のないものとなり、無視されるのであろうか？ちょっとやってみることにする。

SELECT type, val,
  CUME_DIST() OVER (PARTITION BY type ORDER BY val) CUME,
  COUNT(*) OVER (PARTITION BY type ORDER BY val) CNTO,
  COUNT(*) OVER (PARTITION BY type) CONT
 FROM test_analyze5
 ORDER BY type, val;

type val  CUME CNTO CONT
---- ---- ---- ---- ----
   1    1 0.25    1    4
   1    2  0.5    2    4
   1    3 0.75    3    4
   1    4    1    4    4
   2    1  0.4    2    5
   2    1  0.4    2    5
   2    2  0.6    3    5
   2    3  0.8    4    5
   2    4    1    5    5

おー、予想に反して「無視されることはない」ようである。CNTOの列が、ORDER BY付きである。ORDER BYを付けることで、分析関数に渡される集合が、処理しているカレント行までとなる。
CNTO/CONTでCUME_DISTが計算できている。あー、そういうことか。

ここでは、COUNTの例を紹介したが、集計関数のSUM、AVG、MIN、MAXでもOVER句を使って分析関数のように使用することができる。

本日はここまで。