SQLのrow_number関数の構文 パーティション・ソートとの組み合わせ

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;

関連）Oracle公式ドキュメント：ROW_NUMBER

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関数がないが、ユーザ定義変数で代用可能