SQL練習室
← SQLガイド

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は、結果表の表示順を決めるために付けています。

exam_scores:試験の点数9件(満点100点)
idstudentscore
1青木95
2伊藤88
3上田88
4遠藤76
5大野70
6加藤70
7工藤55
8小林40
9斎藤30

NTILE・PERCENT_RANKの基本構文

sql
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点ですが、別のグループに入ります。

sql
SELECT student, score,
  NTILE(4) OVER (ORDER BY score DESC, id) AS quartile
FROM exam_scores
ORDER BY score DESC, id;
実行結果
studentscorequartile
青木951
伊藤881
上田881
遠藤762
大野702
加藤703
工藤553
小林404
斎藤304

割り切れないときの人数の配分を確かめる

行数がnで割り切れないとき、NTILEは差が1行以下になるよう、先頭側のグループへ余りを1行ずつ配ります。9人を5つに分けると、9÷5は商1・余り4なので、先頭の4グループが2人、最後のグループが1人です。人数のずれは必ず先頭側に出ます。グループごとの人数を数えて確認します。

sql
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;
実行結果
bucketmembers
12
22
32
42
51

PERCENT_RANKで0〜1の相対位置を出す

PERCENT_RANKは(順位-1)÷(行数-1)を返し、先頭は0、最後は1になります。点数の降順なら、1位の青木が0、同点2位の伊藤と上田が(2-1)÷(9-1)=0.125、最下位の斎藤が1です。同点の行は同じ順位なので、同じ値になります。1行しかないパーティションは0になります。

sql
SELECT student, score,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS pct_rank
FROM exam_scores
ORDER BY score DESC, id;
実行結果
studentscorepct_rank
青木950
伊藤880.125
上田880.125
遠藤760.375
大野700.5
加藤700.5
工藤550.75
小林400.875
斎藤301

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なので、同点の扱いも異なります。

sql
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;
実行結果
studentscorernkpct_rankcume_dist
青木95100.111
伊藤8820.1250.333
上田8820.1250.333
遠藤7640.3750.444
大野7050.50.667
加藤7050.50.667
工藤5570.750.778
小林4080.8750.889
斎藤30911

PERCENT_RANKで上位25%を抽出する

相対位置をWHEREで絞るには、サブクエリで列にしてから条件を書きます。降順のPERCENT_RANKが0.25以下の行は、青木(0)、伊藤と上田(0.125)の3人です。同点の伊藤と上田を同時に残せます。同じ条件をCUME_DISTで書くと、0.25以下は青木(1÷9=0.111)だけです。「何人目までか」「同点を含めるか」で、使う関数と不等号を選びます。

sql
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;
実行結果
studentscore
青木95
伊藤88
上田88

NTILE・PERCENT_RANKでよくある間違い

NTILEで上位N%を切ると同点が分かれる

上位半分をNTILE(2)の1番で取ると、グループの境目に同点があるとき、同じ点数の人が別のグループへ分かれます。ここでは5人目の大野(70点)が1番、6人目の加藤(70点)が2番になります。同点を同じ扱いにしたいときは、PERCENT_RANKやCUME_DISTで絞ります。

sql
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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。