SQL Server: ソートやフィルタリングに役立つROW_NUMBER関数: 応用例とサンプルコード

SQL ServerにおけるROW_NUMBER関数：詳細ガイド

ROW_NUMBER関数は、結果セット内の各行に固有の番号を割り当てるための強力なツールです。ソートやフィルタリング、分析など、様々な場面で威力を発揮します。本ガイドでは、ROW_NUMBER関数の詳細な使用方法と、実用的な例を交えて解説します。

構文

ROW_NUMBER() OVER (PARTITION BY 分割列 ORDER BY ソート列)

各要素の説明

• ROW_NUMBER(): 連番を生成する関数です。
• OVER: どの範囲で連番を振るかを指定します。
• PARTITION BY 分割列: グループごとに連番を振りたい場合は、分割列を指定します。
• ORDER BY ソート列: 連番の順序を決めるソート列を指定します。

基本的な使い方

例1：顧客ごとの注文履歴に連番を振る

SELECT
  CustomerID,
  OrderDate,
  ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderNumber
FROM Orders;

結果

例2：売上上位10件のみ表示

SELECT
  *
FROM Orders
ORDER BY TotalSales DESC
LIMIT 10;

例3：各月の売上合計と前月比を出力

WITH cte AS (
  SELECT
    Year,
    Month,
    SUM(SalesAmount) AS TotalSales,
    ROW_NUMBER() OVER (PARTITION BY Year, Month ORDER BY Month) AS MonthRank,
    LAG(SUM(SalesAmount), 1) OVER (PARTITION BY Year ORDER BY Month) AS PrevMonthSales
  FROM Sales
  GROUP BY Year, Month
)
SELECT
  *
FROM cte
WHERE MonthRank = 1 OR PrevMonthSales IS NOT NULL;

補足

• ROW_NUMBER関数は、様々な分析シナリオに活用できます。

ROW_NUMBER関数は、SQL Serverにおける強力なツールです。上記の例を参考に、様々な場面で活用してみてください。

サンプルコード集：ROW_NUMBER関数の応用例

この資料では、ROW_NUMBER関数の様々な応用例を、具体的なサンプルコードと共に紹介していきます。それぞれの例で使用される構文を詳細に説明し、理解を深めるためのヒントも提供します。

SELECT
  CustomerID,
  OrderDate,
  ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderNumber
FROM Orders;

説明:

この例では、Ordersテーブルから顧客IDと注文日、そして各顧客ごとの注文履歴に連番を割り当てた結果を出力します。

• PARTITION BY CustomerID: 顧客IDごとに連番を振ることを指定します。
• ORDER BY OrderDate: 注文日を昇順にソートします。

SELECT
  *
FROM Orders
ORDER BY TotalSales DESC
LIMIT 10;

この例では、Ordersテーブルからすべての列を取得し、売上金額（TotalSales）の降順にソートして上位10件のみを表示します。

• ORDER BY TotalSales DESC: 売上金額を降順にソートします。
• LIMIT 10: 上位10件のみ表示します。

WITH cte AS (
  SELECT
    Year,
    Month,
    SUM(SalesAmount) AS TotalSales,
    ROW_NUMBER() OVER (PARTITION BY Year, Month ORDER BY Month) AS MonthRank,
    LAG(SUM(SalesAmount), 1) OVER (PARTITION BY Year ORDER BY Month) AS PrevMonthSales
  FROM Sales
  GROUP BY Year, Month
)
SELECT
  *
FROM cte
WHERE MonthRank = 1 OR PrevMonthSales IS NOT NULL;

この例では、Salesテーブルから売上データを集計し、以下の情報を表示します。

ROW_NUMBER関数以外の代替方法

DENSE_RANK関数は、ROW_NUMBER関数と同様に結果セットに連番を割り当てますが、欠番が出ない点が特徴です。つまり、行が削除されても番号がずれることがありません。

SELECT
  CustomerID,
  OrderDate,
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderNumber
FROM Orders;

利点:

• 欠番が出ない
• 連番の並びが直感的

• 特定の行を参照する場合に不便

NTILE関数は、結果セットをN個の等しいグループに分割し、各グループに1からNまでの番号を割り当てます。

SELECT
  CustomerID,
  OrderDate,
  NTILE(4) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderGroup
FROM Orders;

• グループ化と連番の割り当てを同時にできる

• グループ数 (N) を固定する必要がある
• 連番の値が必ずしも連続とは限らない

RANK関数は、結果セット内の各行に順位を割り当てます。同等の値を持つ行には、同じ順位が割り当てられます。

SELECT
  CustomerID,
  OrderDate,
  RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS OrderRank
FROM Orders;

• 同等の値を持つ行に同じ順位を割り当てられる

• 欠番が出る場合がある

CURSORを使用して、ループ処理の中で手動で連番を割り当てる方法もあります。

DECLARE cur CURSOR FOR
  SELECT CustomerID, OrderDate
  FROM Orders
  ORDER BY CustomerID, OrderDate;

OPEN cur;

FETCH NEXT FROM cur INTO @CustomerID, @OrderDate;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @OrderNumber = @ROWCOUNT;

  -- 連番処理を実行

  FETCH NEXT FROM cur INTO @CustomerID, @OrderDate;
END;

CLOSE cur;
DEALLOCATE cur;

• 柔軟性が高い

• コーディングが複雑になる
• パフォーマンスが劣化する可能性がある

サブクエリを使用して、連番を算出する方法もあります。

SELECT
  CustomerID,
  OrderDate,
  (
    SELECT COUNT(*)
    FROM Orders AS o2
    WHERE o2.CustomerID = o1.CustomerID
      AND o2.OrderDate <= o1.OrderDate
  ) AS OrderNumber
FROM Orders AS o1;

• 比較的シンプルなコード

ROW_NUMBER関数以外にも、様々な方法で結果セットに連番を割り当てることができます。それぞれの方法の利点と欠点を理解し、状況に応じて最適な方法を選択することが重要です。