SQL Server - ROW NUMBER関数

概要

ROW_NUMBER関数は、SQL Serverで結果セット内の各行に一意の連番を割り当てるウィンドウ関数である。
この関数は、データの順序付け、ページング、重複行の特定等で使用される。

 ROW_NUMBER() OVER (
    [PARTITION BY <カラム1>, <カラム2>, ...]
    ORDER BY <カラム1> [ASC|DESC], <カラム2> [ASC|DESC], ...
 )

※注意
ROW_NUMBER関数は必ずORDER BY句を必要とする。
これが無い場合、レコードの順序が不定となるため、期待した結果が得られない可能性がある。
また、同値の場合の順序も明確に指定することが推奨される。

また、ROW_NUMBER関数は、他のウィンドウ関数 (RANK, DENSE_RANK) と組み合わせることにより、柔軟なデータ分析が可能になる。
ただし、大量データに対して使用する場合は、パフォーマンスに注意する。

また、SQL Serverには、連番を振る関数として他にもRANK関数が存在する。
RANK関数は、同じ順位に同じ番号を振るが、同じ順位でも異なる番号を振るものがROW_NUMBER関数である。

ROW_NUMBER関数が使用できるデータベースを以下に示す。

• SQL Server
• Oracle Database
• PostgreSQL

PARTITIONキーワード

PARTITIONキーワードを使用する場合、指定した列でグループ分けを行い、各グループ内で個別に連番を振ることができる。

例えば、部署ごとに従業員の給与順位を付ける。

employeesテーブル

列名	データ型	説明
employee_id	INT	従業員ID (主キー)
employee_name	VARCHAR(100)	従業員名
department_name	VARCHAR(50)	部署名
salary	DECIMAL(10,2)	給与
hire_date	DATE	入社日

employeesテーブル

employee_id	employee_name	department_name	salary	hire_date
1	山田太郎	営業部	350000.00	2022-04-01
2	鈴木花子	営業部	380000.00	2021-08-15
3	佐藤次郎	技術部	420000.00	2020-11-01
4	田中美咲	技術部	400000.00	2021-03-20

 SELECT 
    department_name,
    employee_name,
    salary,
    ROW_NUMBER() OVER (
       PARTITION BY department_name 
       ORDER BY salary DESC
    ) as salary_rank
 FROM employees;

上記のクエリでは、部署 (department_name) でグループ化して、各部署内で給与 (salary) の高い順に順位付けを行っている。
そのため、営業部と技術部それぞれでsalary_rankが1から始まっている。

実効結果 (部署ごとの給与ランキング)

department_name	employee_name	salary	salary_rank
営業部	鈴木花子	380000.00	1
営業部	山田太郎	350000.00	2
技術部	佐藤次郎	420000.00	1
技術部	田中美咲	400000.00	2

重複レコードの除外

ROW_NUMBER関数の主な用途として、重複行の除外がある。

同一データが複数存在する場合、各グループの最初のレコードのみを取得する。

ordersテーブル

列名	データ型	説明
order_id	INT	注文ID (主キー)
customer_id	INT	顧客ID
order_date	DATETIME	注文日時
total_amount	DECIMAL(10,2)	注文合計金額
status	VARCHAR(20)	注文状態

ordersテーブル

order_id	customer_id	order_date	total_amount	status
1	101	2024-01-15 10:30:00	15000.00	完了
2	101	2024-01-16 14:20:00	8500.00	完了
3	102	2024-01-16 15:45:00	12000.00	処理中
4	101	2024-01-17 09:10:00	22000.00	処理中

 WITH numbered_rows AS (
    SELECT *,
       ROW_NUMBER() OVER (
          PARTITION BY customer_id 
          ORDER BY order_date DESC
       ) as row_num
    FROM orders
 )
 SELECT * FROM numbered_rows 
 WHERE row_num = 1;

上記のクエリでは、顧客 (customer_id) ごとにグループ化して、注文日時 (order_date) の新しい順に番号を付けて、その中から、row_num = 1 のレコード (各顧客の最新の注文) のみを抽出している。
そのため、customer_id = 101 の人の3件の注文のうち、最も新しい注文 (order_id = 4) のみが表示されている。

実効結果 (顧客ごとの最新注文)

order_id	customer_id	order_date	total_amount	status	row_num
4	101	2024-01-17 09:10:00	22000.00	処理中	1
3	102	2024-01-16 15:45:00	12000.00	処理中	1

ページング処理

大量のデータをページング処理する場合にも有効である。
例えば、100件ずつデータを取得する場合、ROW_NUMBER関数を使用して特定のページのレコードのみを効率的に抽出することができる。

以下の例では、商品カタログを想定したテーブルである。

productsテーブルの構造

列名	データ型	説明
product_id	INT	商品ID (主キー)
product_name	VARCHAR(100)	商品名
category	VARCHAR(50)	カテゴリ
price	DECIMAL(10,2)	価格
created_at	DATETIME	商品登録日時

サンプルデータ

product_id	product_name	category	price	created_at
1	ノートパソコン A	電化製品	89800.00	2024-01-10 10:00:00
2	タブレット B	電化製品	45800.00	2024-01-10 11:30:00
3	スマートフォン C	電化製品	79800.00	2024-01-11 09:15:00
4	ヘッドフォン D	アクセサリー	12800.00	2024-01-11 14:20:00
5	ワイヤレスマウス E	アクセサリー	4800.00	2024-01-12 16:45:00
6	モニター F	電化製品	34800.00	2024-01-13 10:30:00

• 1ページあたり2件のレコードを表示する場合のページング処理

 WITH numbered_products AS (
    SELECT 
       *,
       ROW_NUMBER() OVER (
          ORDER BY created_at DESC
       ) as row_num
    FROM products
 )
 SELECT 
    product_id,
    product_name,
    category,
    price,
    created_at
 FROM numbered_products
 WHERE row_num BETWEEN (@page_number - 1) * @page_size + 1 AND @page_number * @page_size;

@page_number = 1, @page_size = 2 の場合の実行結果

product_id	product_name	category	price	created_at
6	モニター F	電化製品	34800.00	2024-01-13 10:30:00
5	ワイヤレスマウス E	アクセサリー	4800.00	2024-01-12 16:45:00

@page_number = 2, @page_size = 2 の場合の実行結果

product_id	product_name	category	price	created_at
4	ヘッドフォン D	アクセサリー	12800.00	2024-01-11 14:20:00
3	スマートフォン C	電化製品	79800.00	2024-01-11 09:15:00

総ページ数を取得する場合は、以下に示すようなクエリを追加で実行する。

以下の例では、total_pages は 3 (6件 / 2件) となる。
ROW_NUMBER関数とパラメータを組み合わせることにより、効率的なページング処理が実現できる。

 SELECT CEILING(COUNT(*) * 1.0 / @page_size) as total_pages
 FROM products;