SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

2016年2月9日 2025年4月22日 asai

asai

本日も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句を使って分析関数のように使用することができる。
本日はここまで。

　SQL分析関数 ROW_NUMBER RANK DENSE_RANK

　SQL分析関数 FIRST_VALUE LAST_VALUE NTH_VALUE LAG LEAD

　SQL分析関数 PERCENT_RANK パーセンタイル

　 SQL分析関数 PERCENTILE_CONT PERCENTILE_DISC

　SQL分析関数 CUME_DISTとCOUNT OVER SQLポケリ

SQLポケリには、分析関数も載ってますよ。よかったらどうぞ。