SQL PERCENT_RANK

概要：このチュートリアルでは、SQLのPERCENT_RANK()関数を使用して、結果セット内の行のパーセンタイルランキングを計算する方法を学びます。

PERCENT_RANK()は、ウィンドウ関数であり、結果セット内の行のパーセンタイルランキングを計算します。

PERCENT_RANK()関数の構文は次のとおりです。

PERCENT_RANK()  
    OVER ( 
        PARTITION BY expr1, expr2,...
        ORDER BY expr1 [ASC|DESC], expr2 ...
    )
Code language: SQL (Structured Query Language) (sql)

PERCENT_RANK()関数は、0から1までの範囲のパーセンタイルランキング数を返します。

特定の行について、PERCENT_RANK()は次の式を使用してパーセンタイルランクを計算します。

(rank - 1) / (total_rows - 1)
Code language: SQL (Structured Query Language) (sql)

この式では、rankは行のランクです。total_rowsは評価対象の行数です。

この式に基づいて、PERCENT_RANK()関数は常に結果セットの最初の行に対してゼロを返します。

PARTITION BY句は行をパーティションに分割し、ORDER BY句は各パーティションの行の論理的な順序を指定します。PERCENT_RANK()関数は、各順序付けられたパーティションに対して独立して計算されます。

PARTITION BY句はオプションです。PARTITION BY句を省略すると、関数は結果セット全体を単一のパーティションとして扱います。

SQL PERCENT_RANK()関数の例

デモンストレーションのために、サンプルデータベースのemployeesテーブルとdepartmentsテーブルを使用します。

クエリ結果セットに対するSQL PERCENT_RANK()の使用例

次のクエリは、従業員の給与によるパーセンタイルランクを検索します。

SELECT
    first_name,
    last_name,
    salary,
    ROUND(
        PERCENT_RANK() OVER (
            ORDER BY salary
        ) 
    ,2) percentile_rank
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

この例では、PARTITION BY句を省略したため、関数は結果セット内のすべての従業員を単一のパーティションとして扱いました。パーセンタイルランキングを小数点以下2桁に丸めるために、ROUND()関数を使用したことに注意してください。

次の図は出力を示しています。

ORDER BY句は従業員の給与をソートし、PERCENT_RANK()関数は給与による従業員のパーセンタイルランキングを昇順に計算しました。

出力のいくつかの行を分析してみましょう。

• カレンの給与は最も低く、誰よりも高くないため、彼女のパーセンタイルランキングはゼロです。一方、スティーブンの給与は最も高く、誰よりも高いため、彼のパーセンタイルランキングは1または100％です。
• ナンシーとシェリーのパーセンタイルランキングは82％であり、これは彼らの給与が他のすべての従業員の82％よりも高いことを意味します。

パーティションに対するSQL PERCENT_RANK()の使用例

次のステートメントは、部門ごとの給与による従業員のパーセンタイルランキングを返します。

SELECT
    first_name,
    last_name,
    salary,
    department_name,
    ROUND(
        PERCENT_RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY salary
        ) 
    ,2) percentile_rank
FROM 
    employees e
    INNER JOIN departments d 
        ON d.department_id = e.department_id;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

この例では、従業員を部門名で分割しました。その後、PERCENT_RANK()が各パーティションに適用されました。

出力に明確に示されているように、部門が変更されるたびにパーセンタイルランキングがリセットされました。

このチュートリアルでは、SQLのPERCENT_RANK()関数を使用して、結果セット内の行のパーセンタイルランキングを計算する方法を学びました。