SQL ROW_NUMBER

概要: このチュートリアルでは、クエリ結果セットの各行にシーケンシャルな番号を割り当てるROW_NUMBER()関数の使用方法を学習します。

SQL ROW_NUMBER()関数の概要

ROW_NUMBER()は、クエリの結果セットの各行にシーケンシャルな整数を割り当てるウィンドウ関数です。

ROW_NUMBER()関数の構文を次に示します。

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

この構文において、

• まず、PARTITION BY句は、FROM句から返される結果セットをパーティションに分割します。PARTITION BY句は省略可能です。省略した場合、結果セット全体が単一のパーティションとして扱われます。
• 次に、ORDER BY句は各パーティション内の行をソートします。ROW_NUMBER()は順序に依存する関数であるため、ORDER BY句は必須です。
• 最後に、各パーティションの各行に、行番号と呼ばれるシーケンシャルな整数が割り当てられます。行番号は、パーティション境界を越えるたびにリセットされます。

SQL ROW_NUMBER()の例

サンプルデータベースのemployeesテーブルとdepartmentsテーブルをデモに使用します。

A) 簡単なSQL ROW_NUMBER()の例

次のステートメントは、すべての従業員の氏名と給与を見つけます。さらに、ROW_NUMBER()関数を用いて各行にシーケンシャルな整数を追加します。

SELECT 
    ROW_NUMBER() OVER (
            ORDER BY salary
    ) row_num, 
    first_name, 
    last_name, 
    salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

次の図は、部分的な結果セットを示しています。

B) ページングのためのSQL ROW_NUMBER()の使用

ROW_NUMBER()関数は、ページングに使用できます。たとえば、アプリケーションでテーブル内のすべての従業員をページで表示する場合、各ページに10件のレコードがあるとします。

• まず、ROW_NUMBER()関数を用いて、各行にシーケンシャルな整数を割り当てます。
• 次に、要求されたページによって行をフィルタリングします。たとえば、最初のページには1から9までの行があり、2ページ目には11から20までの行があり、以降同様です。

次のステートメントは、各ページに10件のレコードを持つ2ページ目のレコードを返します。

-- pagination get page #2

SELECT * FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY salary) row_num, 
        first_name, 
        last_name, 
        salary
    FROM
        employees
    ) t    
WHERE
    row_num > 10 AND row_num <=20;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

サブクエリではなく共通テーブル式（CTE）を使用する場合は、次のクエリを使用します。

WITH t AS(
    SELECT 
        ROW_NUMBER() OVER (
            ORDER BY salary
        ) row_num, 
        first_name, 
        last_name, 
        salary
    FROM
        employees
)
SELECT 
    * 
FROM 
    t
WHERE 
    row_num > 10 AND 
    row_num <=20;
Code language: SQL (Structured Query Language) (sql)

C) グループごとのn番目に高い値を見つけるためのSQL ROW_NUMBER()の使用

次の例は、部署で最も高い給与を受け取っている従業員を見つける方法を示しています。

-- find the highest salary per department
SELECT 
    department_name,
    first_name,
    last_name,
    salary
FROM 
    (
        SELECT 
            department_name,
            `ROW_NUMBER()` OVER (
                PARTITION BY department_name
                ORDER BY salary DESC) row_num, 
            first_name, 
            last_name, 
            salary
        FROM 
            employees e
            INNER JOIN departments d 
                ON d.department_id = e.department_id
    ) t
WHERE 
    row_num = 1;
Code language: SQL (Structured Query Language) (sql)

サブクエリにおいて、

• まず、PARTITION BY句は、部署別に従業員を分散します。
• 次に、ORDER BY句は、各部署の従業員を給与の降順でソートします。
• 第三に、ROW_NUMBER()は各行にシーケンシャルな整数を割り当てます。部署が変わると、番号がリセットされます。

サブクエリの結果セットを次に示します。

外部クエリでは、row_numの値が1である従業員行のみを選択しました。

クエリの全体の出力を次に示します。

WHERE句の述語を1から2、3などに変更すると、2番目に高い給与、3番目に高い給与などを受けている従業員を取得できます。

このチュートリアルでは、クエリの結果セットの各行にシーケンシャルな整数を割り当てるSQL ROW_NUMBER()関数の使用方法を学習しました。