SQLのNTILEとPERCENT_RANKとは?四分位・デシル・CUME_DISTの違いと上位25%の抽出
NTILE(n)は行を点数順にn個のグループへ等分して番号を付け、PERCENT_RANKとCUME_DISTは全体の中の相対位置を0〜1の値で返します。四分位やデシルの区分、「上位25%」の抽出に使います。9人の試験の点数で、同点の扱いまで確認しましょう。
例で使う表
exam_scoresの1行は1人の試験の点数です。伊藤と上田は88点、大野と加藤は70点の同点で、同点の扱いによる結果の違いを確かめます。9人なので、4つに分けると割り切れません。各例のORDER BYは、結果表の表示順を決めるために付けています。
| id | student | score |
|---|---|---|
| 1 | 青木 | 95 |
| 2 | 伊藤 | 88 |
| 3 | 上田 | 88 |
| 4 | 遠藤 | 76 |
| 5 | 大野 | 70 |
| 6 | 加藤 | 70 |
| 7 | 工藤 | 55 |
| 8 | 小林 | 40 |
| 9 | 斎藤 | 30 |
NTILE・PERCENT_RANKの基本構文
SELECT student, score,
NTILE(4) OVER (ORDER BY score DESC, id) AS quartile
FROM exam_scores;NTILE(4)で四分位のグループに分ける
NTILE(n) OVER (ORDER BY ...)は、並べた行を先頭から順にn個のグループへ分け、1〜nの番号を返します。点数の高い順に分けるとき、1が上位グループです。同点の行を区別するため、ORDER BYにidを加えて順序を一意にします。9人を4つに分けると、先頭側のグループが多くなり、青木・伊藤・上田が1、遠藤・大野が2、加藤・工藤が3、小林・斎藤が4です。大野と加藤は同じ70点ですが、別のグループに入ります。
SELECT student, score,
NTILE(4) OVER (ORDER BY score DESC, id) AS quartile
FROM exam_scores
ORDER BY score DESC, id;| student | score | quartile |
|---|---|---|
| 青木 | 95 | 1 |
| 伊藤 | 88 | 1 |
| 上田 | 88 | 1 |
| 遠藤 | 76 | 2 |
| 大野 | 70 | 2 |
| 加藤 | 70 | 3 |
| 工藤 | 55 | 3 |
| 小林 | 40 | 4 |
| 斎藤 | 30 | 4 |
割り切れないときの人数の配分を確かめる
行数がnで割り切れないとき、NTILEは差が1行以下になるよう、先頭側のグループへ余りを1行ずつ配ります。9人を5つに分けると、9÷5は商1・余り4なので、先頭の4グループが2人、最後のグループが1人です。人数のずれは必ず先頭側に出ます。グループごとの人数を数えて確認します。
SELECT bucket, COUNT(*) AS members
FROM (
SELECT NTILE(5) OVER (ORDER BY score DESC, id) AS bucket
FROM exam_scores
) t
GROUP BY bucket
ORDER BY bucket;| bucket | members |
|---|---|
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 1 |
PERCENT_RANKで0〜1の相対位置を出す
PERCENT_RANKは(順位-1)÷(行数-1)を返し、先頭は0、最後は1になります。点数の降順なら、1位の青木が0、同点2位の伊藤と上田が(2-1)÷(9-1)=0.125、最下位の斎藤が1です。同点の行は同じ順位なので、同じ値になります。1行しかないパーティションは0になります。
SELECT student, score,
PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank
FROM exam_scores
ORDER BY score DESC, id;| student | score | pct_rank |
|---|---|---|
| 青木 | 95 | 0 |
| 伊藤 | 88 | 0.125 |
| 上田 | 88 | 0.125 |
| 遠藤 | 76 | 0.375 |
| 大野 | 70 | 0.5 |
| 加藤 | 70 | 0.5 |
| 工藤 | 55 | 0.75 |
| 小林 | 40 | 0.875 |
| 斎藤 | 30 | 1 |
CUME_DISTとPERCENT_RANKの違いと同値の扱い
CUME_DISTは「現在行以前の行と同値の行の数÷全体の行数」で、最小値は1÷行数、最大値は1です。PERCENT_RANKが0から始まるのに対し、CUME_DISTは0になりません。同点は最後のピアまで数えるため、伊藤と上田はどちらも3÷9で0.333、大野と加藤は6÷9で0.667になります。PERCENT_RANKでは同じ2位が0.125なので、同点の扱いも異なります。
SELECT student, score,
RANK() OVER (ORDER BY score DESC) AS rnk,
PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY score DESC)::numeric, 3) AS cume_dist
FROM exam_scores
ORDER BY score DESC, id;| student | score | rnk | pct_rank | cume_dist |
|---|---|---|---|---|
| 青木 | 95 | 1 | 0 | 0.111 |
| 伊藤 | 88 | 2 | 0.125 | 0.333 |
| 上田 | 88 | 2 | 0.125 | 0.333 |
| 遠藤 | 76 | 4 | 0.375 | 0.444 |
| 大野 | 70 | 5 | 0.5 | 0.667 |
| 加藤 | 70 | 5 | 0.5 | 0.667 |
| 工藤 | 55 | 7 | 0.75 | 0.778 |
| 小林 | 40 | 8 | 0.875 | 0.889 |
| 斎藤 | 30 | 9 | 1 | 1 |
PERCENT_RANKで上位25%を抽出する
相対位置をWHEREで絞るには、サブクエリで列にしてから条件を書きます。降順のPERCENT_RANKが0.25以下の行は、青木(0)、伊藤と上田(0.125)の3人です。同点の伊藤と上田を同時に残せます。同じ条件をCUME_DISTで書くと、0.25以下は青木(1÷9=0.111)だけです。「何人目までか」「同点を含めるか」で、使う関数と不等号を選びます。
SELECT student, score
FROM (
SELECT id, student, score,
PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank
FROM exam_scores
) t
WHERE pct_rank <= 0.25
ORDER BY score DESC, id;| student | score |
|---|---|
| 青木 | 95 |
| 伊藤 | 88 |
| 上田 | 88 |
NTILE・PERCENT_RANKでよくある間違い
NTILEで上位N%を切ると同点が分かれる
上位半分をNTILE(2)の1番で取ると、グループの境目に同点があるとき、同じ点数の人が別のグループへ分かれます。ここでは5人目の大野(70点)が1番、6人目の加藤(70点)が2番になります。同点を同じ扱いにしたいときは、PERCENT_RANKやCUME_DISTで絞ります。
SELECT student, score,
NTILE(2) OVER (ORDER BY score DESC, id) AS half
FROM exam_scores
ORDER BY score DESC, id;実際に1問解いてみる
NTILE
点数の高い順、同点ならid順で四分位に分け、student、score、グループ番号を取得してください。1を最上位とし、番号の列にはAS別名を付けずNTILEの既定名を使います。行はその順序で返します。
関連構文
NTILE・PERCENT_RANKについてよくある質問
- NTILEで四分位やデシルを作るにはどう書きますか?
- 四分位はNTILE(4)、デシル(10分位)はNTILE(10)です。OVERにORDER BYを書き、同点でも順序を一意にするため、idのような列を加えます。点数の高い順に分けるときはDESCにすると、1が上位です。
- PERCENT_RANKとCUME_DISTはどちらを使えばよいですか?
- 先頭を0にして「順位の位置」を見たいときはPERCENT_RANK、「その値以下が全体の何割か」を見たいときはCUME_DISTです。CUME_DISTは必ず0より大きく、最後は1になります。同点は、どちらも同じ値にまとまります。
- NTILEで同点の行を同じグループにできますか?
- NTILEは行数だけで分けるため、同点でもグループの境目で分かれることがあります。同点を同じ扱いにしたいときは、PERCENT_RANKやCUME_DISTで絞り込みます。
参考文献
内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:ウィンドウ関数(ntile・percent_rank・cume_dist) — ntile・percent_rank・cume_distの定義を確認
- PostgreSQL公式:ウィンドウ関数チュートリアル — OVER・ORDER BYの基本