SQLのrow_number関数についてまとめています。
SQLのrow_number関数の構文
row_number関数は、機能としては「連番を振る」関数です。
SELECT ROW_NUMBER() OVER(ORDER BY score ASC) num , user_name , score FROM scores
【関連記事】
▶SQLで行番号を取得する ROW_NUMBER・ROWNUM・ユーザ定義変数の使い方
しかし、具体的な使い方は、サブクエリと組み合わせることで、グルーピングしたデータの上位n番、下位n番といった条件指定が可能になるんですね。
以下の例は、Cityごとにグルーピングし、scoreを昇順にソートして連番を振っています。
SELECT ROW_NUMBER() OVER(PARTITION BY City ORDER BY Age ASC) num name , city , score FROM scores num user_name city score 1 山田 太郎 Tokyo 10 2 鈴木 一郎 Tokyo 20 1 佐藤 太郎 Nagoya 30 2 田中 次郎 Nagoya 40 1 伊藤 太郎 Osaka 50
【関連記事】
▶【SQL】連番を振るROW_NUMBER関数を解説！一番よく使う順位付け関数をマスターしよう
Oracleのrow_number関数の構文
以下は、Oracleのrow_number関数の使用例です。hr.employees表で各部門の支給額の高いトップ3人の従業員のレコードを抽出します。従業員が3人未満の部門は、3行未満の行が戻されます。
SELECT department_id, first_name, last_name, salary
FROM
(
SELECT
department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary desc) rn
FROM employees
)
WHERE rn <= 3
ORDER BY department_id, salary DESC, last_name;
SQL Serverのrow_number関数の構文
SQL Serverのrow_numberの構文は以下の通りです。
ROW_NUMBER ( )
OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
関連）ROW_NUMBER (Transact-SQL) – SQL Server | Microsoft Docs
以下のSQLは、sys.databasesテーブルのdatabase_idが5以下のデータベースに、nameを昇順にソートして連番を振っている例です。
SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5; Row# name recovery_model_desc 1 master SIMPLE 2 model FULL 3 msdb SIMPLE 4 tempdb SIMPLE
以下は、PARTITION BY指定をおこなって、SalesTerritoryKeyごとにグルーピングした上でグループごとに連番を振っています。
-- Uses AdventureWorks
SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey
ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
LastName, SalesTerritoryKey AS Territory,
CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName, SalesTerritoryKey;
RowNumber LastName Territory SalesQuota
--------- ------------------ --------- -------------
1 Campbell 1 4,025,000.00
2 Ansman-Wolfe 1 3,551,000.00
3 Mensa-Annan 1 2,275,000.00
1 Blythe 2 11,162,000.00
1 Carson 3 12,198,000.00
1 Mitchell 4 11,786,000.00
2 Ito 4 7,804,000.00
：
：
MySQLでrow_number関数を使わず行番号をつける
MySQLには、row_number関数がありません。ユーザ変数を使って、行番号を振ることが可能です。
SET @rownum=0; SELECT @rownum:=@rownum+1 as num, first_name, last_name, hire_date FROM employees ORDER BY hire_date; +------+-------------+--------------+------------+ | num | first_name | last_name | hire_date | +------+-------------+--------------+------------+ | 1 | Tonny | Butterworth | 1985-01-01 | | 2 | Ebru | Alpin | 1985-01-01 | | 3 | Przemyslawa | Kaelbling | 1985-01-01 | | 4 | DeForest | Hagimont | 1985-01-01 | | 5 | Peternela | Onuegbe | 1985-01-01 | | 6 | Krassimir | Wegerle | 1985-01-01 | | 7 | Arie | Staelin | 1985-01-01 | | 8 | Margareta | Markovitch | 1985-01-01 | | 9 | Shirish | Ossenbruggen | 1985-01-01 | | 10 | Isamu | Legleitner | 1985-01-14 | : :
【関連記事】
▶SQLで行番号を取得する ROW_NUMBER・ROWNUM・ユーザ定義変数の使い方 |
PostgreSQLのrow_number関数の構文
以下は、PostgreSQLでrow_number関数を使用する例です。
SELECT
product_id,
product_name,
group_id,
ROW_NUMBER () OVER (
ORDER BY product_name
)
FROM
products;
関連）PostgreSQL ROW_NUMBER() Explained with Practical Examples
まとめ
- row_number関数は、連番を生成する関数
- partition byやorder byでグルーピング範囲とソート指定が可能
- MySQLにはrow_number関数がないが、ユーザ定義変数で代用可能
