SQLのウィンドウ関数（PERCENT_RANK、CUME_DIST、NTILE）を使ってみよう！

2024.07.12 株式会社システムインテグレータ

今回の初心者訓練所#16は、あまり聞き慣れていないかも知れませんが、知っていると便利な3つのウィンドウ関数を説明していきます。基本的な構文は今迄紹介してきたウィンドウ関数と同様なので、直ぐに覚えられると思います。今回もデータベースは、いつもの「SQLite」を使用していきますので、実践しながら確認していきましょう。

PERCENT_RANK()

PERCENT_RANK 関数は、抽出対象となったデータ内の行がORDER BY句で指定された項目値の順序で、どの位置にランク付けされるかをパーセンテージで示します。0から1までの値を返し、グループ内での相対的な位置を表します。また、PARTITION BY句が指定された場合は、指定された項目毎に区切ってパーセンテージで示します。
構文は下記になります。

PERCENT_RANK() OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

確認の準備として、テスト結果テーブルを作成して、データを挿入します。

【テスト結果テーブルのCREATE文とINSERT文】

CREATE TABLE tbl_test_result (
emp_code varchar not null
, dept_code varchar not null
, point integer default 0 not null,
PRIMARY KEY( emp_code ) );

INSERT INTO tbl_test_result ( emp_code,dept_code,point ) VALUES
('100','D100','50')
,('110','D200','60')
,('120','D100','60')
,('130','D100','75')
,('140','D200','100');

blog16-01
出力フォーマットを調整して、SELECT文で登録内容を確認してみましょう。

.headers on (ヘッダ情報を出力)
.mode column (カラム毎に左揃え出力)

SELECT * FROM tbl_test_result;

blog16-02

INSERT文で作成した、5件のデータを確認できます。

(1) それでは、ORDER BY句に点数(point)の昇順を指定して実行してみましょう。

SELECT
emp_code , dept_code , point ,
PERCENT_RANK() OVER (ORDER BY point ASC) AS PER
FROM tbl_test_result;

blog16-03

emp_code=100 の点数(50)は最小なので、パーセンテージは0.0となります。
emp_code =110 と 120 の点数は(60)で、同じランクでパーセンテージは0.25となります。
emp_code=130 の点数(75)は3番目となるので、パーセンテージは0.75です。
emp_code=140 の点数(100)は最大で、パーセンテージは1.0になります。

参考までに、PERCENT_RANK関数での計算式は以下のようになります。
パーセンテージ＝ ( RANK関数で計算した順位 - 1 ) / ( 全体のデータ数 - 1 )
RANK関数も含めたSELECT文で確認してみましょう。

SELECT
emp_code , dept_code , point ,
PERCENT_RANK() OVER (ORDER BY point ASC) AS PER,
RANK() OVER (ORDER BY point ASC) AS RANK
FROM tbl_test_result;

blog16-04

上から順に以下の式で、パーセンテージが算出されていることが確認できます。
( RANK-1 ) / ( 全体のデータ件数-1 )
emp_code = 100 ( 1-1 ) / ( 5-1 ) = 0.0
emp_code = 110 ( 2-1 ) / ( 5-1 ) = 0.25
emp_code = 120 ( 2-1 ) / ( 5-1 ) = 0.25
emp_code = 130 ( 4-1 ) / ( 5-1 ) = 0.75
emp_code = 140 ( 5-1 ) / ( 5-1 ) = 1.0

(2) 続いて、PARTITION BY句に部門コード(dept_code)を指定して実行してみましょう。　　

SELECT
emp_code , dept_code , point ,
PERCENT_RANK() OVER (PARTITION BY dept_code
ORDER BY point ASC) AS PER
FROM tbl_test_result;

blog16-05

部門コード毎にパーセンテージが求められていることが確認できます。

以上のように、データセット内での相対的な順位をパーセンテージで求め、ランキングを視覚化するのに最適な関数です。例えば、営業成績やスコアランキングなど、特定の基準で順位を付けたい場面で便利な関数です。

CUME_DIST()

CUME_DIST 関数は、累積分布（Cumulative Distribution）を計算するために使用されます。この関数は、指定した順序で並んだ行の各値がデータセット全体でどのくらいの位置にあるかを示すために用いられます。0から1までの値を返し、その行がデータセットの中で占める累積割合を示します。また、PARTITION BY句が指定された場合は、他の関数と同様に指定された項目毎に区切って位置を示します。

CUME_DIST関数の計算式は以下となり、PERCENT_RANK関数とは微妙に異なります。
全体からの位置＝集合の個数 / 全体の個数

構文も前述までの関数と同様で、下記になります。

CUME_DIST() OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

(1) それでは、ORDER BY句に点数(point)の昇順を指定して実行してみましょう。

SELECT
emp_code , dept_code , point ,
CUME_DIST() OVER (ORDER BY point ASC) AS CD
FROM tbl_test_result;

blog16-06

emp_code = 100 の点数(50)は５人中1番目なので、累積分布は0.2 ( 1 / 5 )になります。
emp_code = 110 と 120 の点数は(60)で、同じランクで順位は3番目なので、累積分布は0.6 ( 3 / 5 )になります。
emp_code = 130 の点数(75)は4番目なので、累積分布は0.8 ( 4 / 5 )になります。
emp_code = 140 の点数(100)は５番目なので、累積分布は1.0 ( 5 / 5 )になります。

(2) 次に、PARTITION BY句に部門コード(dept_code)を指定して実行してみましょう。

SELECT
emp_code , dept_code , point ,
CUME_DIST() OVER (PARTITION BY dept_code
ORDER BY point ASC) AS CD
FROM tbl_test_result;

blog16-07

部門コード毎に各データの累積分布の位置が算出されていることが確認できます。

以上のように、累積分布を計算するためのこの関数は、データの分布を理解しやすくし、特にデータセット全体での相対的な位置を示すことが求められる場合に重宝します。ビジネスインテリジェンスのレポートや統計解析で便利な関数です。

NTILE()

NTILE 関数は、パラメータに指定した数のグループにデータを均等に分割するために使用されます。各グループにはほぼ同じ数の行が含まれ、グループ番号が1から指定した数までの連続した値を返します。対象行数が指定した数で割り切れないときは、先頭のグループから順に1ずつ加算されます。たとえば、8行のデータを3つのグループに分ける場合は、3行、3行、2行となります。デシル分析等のデータをランク毎に分ける場合に非常に役立ちます。
PARTITION BY句が指定された場合は、前述の2つの関数と同様に指定された項目毎に区切ってデータを分割します。
構文は同様に、下記になります。

NTILE(N) OVER ( [ PARTITION BY 項目A, [項目B], ...]
ORDER BY 項目C, [項目D], ... [ASC|DESC])
※[ ]内は省略可能です。

(1) それでは、点数(point)を基準に、3つのグループに分割してみましょう。

SELECT
emp_code , dept_code , point ,
NTILE(3) OVER (ORDER BY point ASC) AS NT
FROM tbl_test_result;

blog16-08

点数順に、３つのグループに分割されていることが確認できます。データが５件しかないので、３つ目のグループが１件となっています。

(2) 次に、PARTITION BY句に部門コード(dept_code)を指定して実行してみましょう。

SELECT
emp_code , dept_code , point ,
NTILE(3) OVER (PARTITION BY dept_code
ORDER BY point ASC) AS NT
FROM tbl_test_result;

blog16-09

部門コード毎に３分割されていますが、dept_code ＝ D200は２件しかデータがないので、２分割となっています。

以上のように、データを均等にグループ分けすることができるこの関数は、顧客セグメンテーションやパフォーマンス評価など、多くのデータ解析シナリオに適しています。特に、データ集合を等間隔に分割し、各グループに属する対象を分析する際に便利な関数です。

知っていると便利な、3つのウィンドウ関数の説明は以上となります。

あとがき

　「初心者訓練所」の第16弾では、知っていると便利な3つのウィンドウ関数について説明してきました。これらのウィンドウ関数は、データ解析やレポート作成において非常に強力な関数です。各関数の特長と具体的な使用例を通じて、どのような場面で利用できるか、またどのようにデータの分布やランク付けを評価できるかが少しでも伝われば幸いです。今後もSQLのさらなるテクニックや応用例について取り上げていく予定ですので、次回の記事も、どうぞお楽しみに！

(株)システムインテグレータ　TOPSICチーム　元鬼軍曹　久保　司