SQL Server - ROW NUMBER関数のソースを表示

== 概要 ==
ROW_NUMBER関数は、SQL Serverで結果セット内の各行に一意の連番を割り当てるウィンドウ関数である。<br>
この関数は、データの順序付け、ページング、重複行の特定等で使用される。<br>
<br>
 <syntaxhighlight lang="tsql">
 ROW_NUMBER() OVER (
    [PARTITION BY <カラム1>, <カラム2>, ...]
    ORDER BY <カラム1> [ASC|DESC], <カラム2> [ASC|DESC], ...
 )
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>ROW_NUMBER</code>関数は必ず<code>ORDER BY</code>句を必要とする。</u><br>
<u>これが無い場合、レコードの順序が不定となるため、期待した結果が得られない可能性がある。</u><br>
<u>また、同値の場合の順序も明確に指定することが推奨される。</u><br>
<br>
また、<code>ROW_NUMBER</code>関数は、他のウィンドウ関数 (RANK, DENSE_RANK) と組み合わせることにより、柔軟なデータ分析が可能になる。<br>
<u>ただし、大量データに対して使用する場合は、パフォーマンスに注意する。</u><br>
<br><br>

== PARTITIONキーワード ==
PARTITIONキーワードを使用する場合、指定した列でグループ分けを行い、各グループ内で個別に連番を振ることができる。<br>
<br>
例えば、部署ごとに従業員の給与順位を付ける。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ employeesテーブル
! style="background-color:#66CCFF;" | 列名 
! style="background-color:#66CCFF;" | データ型 
! style="background-color:#66CCFF;" | 説明
|-
| employee_id || INT || 従業員ID (主キー)
|-
| employee_name || VARCHAR(100) || 従業員名
|-
| department_name || VARCHAR(50) || 部署名
|-
| salary || DECIMAL(10,2) || 給与
|-
| hire_date || DATE || 入社日
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ employeesテーブル
! style="background-color:#66CCFF;" | employee_id 
! style="background-color:#66CCFF;" | employee_name 
! style="background-color:#66CCFF;" | department_name 
! style="background-color:#66CCFF;" | salary 
! style="background-color:#66CCFF;" | 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
|}
</center>
<br>
 <syntaxhighlight lang="tsql">
 SELECT 
    department_name,
    employee_name,
    salary,
    ROW_NUMBER() OVER (
       PARTITION BY department_name 
       ORDER BY salary DESC
    ) as salary_rank
 FROM employees;
 </syntaxhighlight>
<br>
上記のクエリでは、部署 (department_name) でグループ化して、各部署内で給与 (salary) の高い順に順位付けを行っている。<br>
そのため、営業部と技術部それぞれでsalary_rankが1から始まっている。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ 実効結果 (部署ごとの給与ランキング)
! style="background-color:#66CCFF;" | department_name 
! style="background-color:#66CCFF;" | employee_name 
! style="background-color:#66CCFF;" | salary 
! style="background-color:#66CCFF;" | salary_rank
|-
| 営業部 || 鈴木花子 || 380000.00 || 1
|-
| 営業部 || 山田太郎 || 350000.00 || 2
|-
| 技術部 || 佐藤次郎 || 420000.00 || 1
|-
| 技術部 || 田中美咲 || 400000.00 || 2
|}
</center>
<br><br>

== 重複レコードの除外 ==
ROW_NUMBER関数の主な用途として、重複行の除外がある。<br>
<br>
同一データが複数存在する場合、各グループの最初のレコードのみを取得する。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ ordersテーブル
! style="background-color:#66CCFF;" | 列名 
! style="background-color:#66CCFF;" | データ型 
! style="background-color:#66CCFF;" | 説明
|-
| order_id || INT || 注文ID (主キー)
|-
| customer_id || INT || 顧客ID
|-
| order_date || DATETIME || 注文日時
|-
| total_amount || DECIMAL(10,2) || 注文合計金額
|-
| status || VARCHAR(20) || 注文状態
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ ordersテーブル
! style="background-color:#66CCFF;" | order_id 
! style="background-color:#66CCFF;" | customer_id 
! style="background-color:#66CCFF;" | order_date 
! style="background-color:#66CCFF;" | 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 || 処理中
|}
</center>
<br>
 <syntaxhighlight lang="tsql">
 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;
 </syntaxhighlight>
<br>
上記のクエリでは、顧客 (customer_id) ごとにグループ化して、注文日時 (order_date) の新しい順に番号を付けて、その中から、row_num = 1 のレコード (各顧客の最新の注文) のみを抽出している。<br>
そのため、customer_id = 101 の人の3件の注文のうち、最も新しい注文 (order_id = 4) のみが表示されている。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ 実効結果 (顧客ごとの最新注文)
! style="background-color:#66CCFF;" | order_id 
! style="background-color:#66CCFF;" | customer_id 
! style="background-color:#66CCFF;" | order_date 
! style="background-color:#66CCFF;" | total_amount 
! style="background-color:#66CCFF;" | status 
! style="background-color:#66CCFF;" | row_num
|-
| 4 || 101 || 2024-01-17 09:10:00 || 22000.00 || 処理中 || 1
|-
| 3 || 102 || 2024-01-16 15:45:00 || 12000.00 || 処理中 || 1
|}
</center>
<br><br>

== ページング処理 ==
大量のデータをページング処理する場合にも有効である。<br>
例えば、100件ずつデータを取得する場合、ROW_NUMBER関数を使用して特定のページのレコードのみを効率的に抽出することができる。<br>
<br>
以下の例では、商品カタログを想定したテーブルである。<br>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ productsテーブルの構造
! style="background-color:#66CCFF;" | 列名 
! style="background-color:#66CCFF;" | データ型 
! style="background-color:#66CCFF;" | 説明
|-
| product_id || INT || 商品ID (主キー)
|-
| product_name || VARCHAR(100) || 商品名
|-
| category || VARCHAR(50) || カテゴリ
|-
| price || DECIMAL(10,2) || 価格
|-
| created_at || DATETIME || 商品登録日時
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ サンプルデータ
! style="background-color:#66CCFF;" | product_id 
! style="background-color:#66CCFF;" | product_name 
! style="background-color:#66CCFF;" | category 
! style="background-color:#66CCFF;" | price 
! style="background-color:#66CCFF;" | 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
|}
</center>
<br>
* 1ページあたり2件のレコードを表示する場合のページング処理
 <syntaxhighlight lang="tsql">
 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;
 </syntaxhighlight>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ @page_number = 1, @page_size = 2 の場合の実行結果
! style="background-color:#66CCFF;" | product_id 
! style="background-color:#66CCFF;" | product_name 
! style="background-color:#66CCFF;" | category 
! style="background-color:#66CCFF;" | price 
! style="background-color:#66CCFF;" | created_at
|-
| 6 || モニター F || 電化製品 || 34800.00 || 2024-01-13 10:30:00
|-
| 5 || ワイヤレスマウス E || アクセサリー || 4800.00 || 2024-01-12 16:45:00
|}
</center>
<br>
<center>
{| class="wikitable" | style="background-color:#fefefe;"
|+ @page_number = 2, @page_size = 2 の場合の実行結果
! style="background-color:#66CCFF;" | product_id 
! style="background-color:#66CCFF;" | product_name 
! style="background-color:#66CCFF;" | category 
! style="background-color:#66CCFF;" | price 
! style="background-color:#66CCFF;" | created_at
|-
| 4 || ヘッドフォン D || アクセサリー || 12800.00 || 2024-01-11 14:20:00
|-
| 3 || スマートフォン C || 電化製品 || 79800.00 || 2024-01-11 09:15:00
|}
</center>
<br>
総ページ数を取得する場合は、以下に示すようなクエリを追加で実行する。<br>
<br>
以下の例では、total_pages は 3  (6件 / 2件) となる。<br>
ROW_NUMBER関数とパラメータを組み合わせることにより、効率的なページング処理が実現できる。<br>
<br>
 <syntaxhighlight lang="tsql">
 SELECT CEILING(COUNT(*) * 1.0 / @page_size) as total_pages
 FROM products;
 </syntaxhighlight>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,Podman,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:SQL_Server]]