SQLウィンドウ関数入門ROW_NUMBER・PARTITION BYで「顧客ごとの1件」を取り出す

ネットショップで新規顧客の獲得施策を評価するとき、「誰が、いつ、最初に、いくら買ったか」を知りたくなります。顧客ごとの注文件数や最初の日付だけでなく、その注文に紐づいた金額が必要です。

同じ課題は、利用者ごとの最初のログイン、製品ごとの最新価格、設備ごとの直近点検、担当者ごとの最新問い合わせにも現れます。「グループごとに1行選ぶ」ことは、業務アプリで繰り返し登場するSQLパターンです。

架空の注文履歴テーブルを考えます。注文回は顧客内で1から増える番号、注文日は注文を確定した日、金額はその注文の金額です。以下は説明用データです。

欲しいのは顧客101の1回目3,000円、102の1回目5,000円、103の1回目8,000円という3行です。顧客101の2回目は金額が大きくても選びません。

CREATE TABLE 注文履歴 (
  顧客ID INTEGER NOT NULL,
  注文回 INTEGER NOT NULL,
  注文日 DATE NOT NULL,
  金額 INTEGER NOT NULL,
  PRIMARY KEY (顧客ID, 注文回)
);

この例では複合主キーにより、同じ顧客に同じ注文回が重複しません。後半では、こうした一意性の前提が崩れた場合も扱います。

GROUP BYは同じ顧客IDの行を集約し、MINやMAXなどの集約値を求めます。

SELECT 顧客ID, MIN(注文回) AS 初回
FROM 注文履歴
GROUP BY 顧客ID;

この時点で「そのときの金額」は結果にありません。MIN(注文回)とMIN(金額)を同じSELECTに書いても、両者が同じ注文行から取られる保証はありません。たとえば顧客101の最小金額は3,000円ですが、別のデータなら最小金額が2回目であることもあります。

元の行の金額を取得するには、先ほどの「顧客IDと初回」の集約結果を、注文履歴に結合する方法があります。サブクエリの別名初回一覧は中間表の名前です。

SELECT o.顧客ID, o.注文回, o.注文日, o.金額
FROM 注文履歴 AS o
INNER JOIN (
  SELECT 顧客ID, MIN(注文回) AS 初回
  FROM 注文履歴
  GROUP BY 顧客ID
) AS 初回一覧
  ON o.顧客ID = 初回一覧.顧客ID
 AND o.注文回 = 初回一覧.初回;

①集約して初回番号を求める → ②顧客IDと注文回の両方で元表に結合する → ③該当する行の金額も取得する、という流れです。顧客IDだけでJOINすると、該当顧客のすべての注文が戻ってくるため、結合条件の2つ目が重要です。

「初回番号」が重複しないという主キーの前提があれば顧客ごとに1件になります。もし最小の日時が複数行で同じなら、その日時で結合した結果は複数件になる場合があります。

ウィンドウ関数（window function / 分析関数）は、元の行を消さずに、その行が属するグループや並び順に基づく値を計算します。ROW_NUMBER()なら、各行に連番を付けられます。

OVER (...)は関数が計算対象とする範囲・順序を指定する構文です。「ウィンドウ」は画面上の窓ではなく、計算の対象となる行集合を指します。

ROW_NUMBER() OVER (
  PARTITION BY 顧客ID
  ORDER BY 注文回
)

PARTITION BY 顧客IDは、顧客101・102・103ごとに別々の番号付けグループを作ります。グループが変わると番号は1からやり直しです。GROUP BYと似た「分ける」働きでも、各行を1行に縮めません。

注意したいのは、ここでのパーティションはSQLの分析上の論理的なグループです。DBのディスク領域を分割するテーブルパーティショニングとは異なります。

ORDER BY 注文回 ASCなら注文回が小さいものから、DESCなら大きいものから番号を付けます。ASCは昇順で、省略時も通常は昇順です。DESCは降順です。

ウィンドウ内のORDER BYと、最終的な表示順のORDER BYは別です。結果一覧の並び順も保証したい場合は外側のSELECTにもORDER BYを記述します。

各顧客について番号を振った後、番号1だけを取り出します。計算された別名「番号」は通常、同じSELECTのWHEREにはそのまま指定できないため、サブクエリに包みます。

SELECT 顧客ID, 注文回, 注文日, 金額
FROM (
  SELECT 顧客ID, 注文回, 注文日, 金額,
         ROW_NUMBER() OVER (
           PARTITION BY 顧客ID
           ORDER BY 注文回 ASC
         ) AS 番号
  FROM 注文履歴
) AS x
WHERE x.番号 = 1
ORDER BY 顧客ID;

同じ処理をWITH句（共通表式、CTE）で書くと、「番号を付ける処理」と「選ぶ処理」の境界が読みやすくなります。

WITH 番号付き注文 AS (
  SELECT 顧客ID, 注文回, 注文日, 金額,
         ROW_NUMBER() OVER (
           PARTITION BY 顧客ID
           ORDER BY 注文回
         ) AS 番号
  FROM 注文履歴
)
SELECT 顧客ID, 注文回, 注文日, 金額
FROM 番号付き注文
WHERE 番号 = 1;

CTEを使えば必ず速くなるわけではありません。DB製品やバージョン、最適化・実行計画により異なります。まず意味が明快なSQLを書き、必要に応じて性能を測るのが基本です。

注文回は顧客ごとに一意ですが、注文日時を基準に「最初の1件」を取ると、同時刻の注文が複数あるかもしれません。ROW_NUMBER()はそれでも異なる番号を振りますが、同順位同士のどちらが1になるかは追加の並び順がなければ安定しません。

-- 注文日時だけでは同時刻の順序が不定
ROW_NUMBER() OVER (
  PARTITION BY 顧客ID ORDER BY 注文日時
)

-- 一意な注文IDで同順位を解消
ROW_NUMBER() OVER (
  PARTITION BY 顧客ID ORDER BY 注文日時, 注文ID
)

前半のMINとJOIN方式では同じ最小日時を持つ行が2件とも返る一方、ROW_NUMBER方式はその中から1件だけ返します。したがって、「どちらも常に同じ結果」は成立しません。比較するには一意性や同順位時の業務ルールを明確にする必要があります。

同順位の扱いが違う3つの関数も知っておくと、応用範囲が広がります。得点が90、90、75、60の4行なら次のようになります。

ROW_NUMBERは連番、RANKは同順位の次に飛び番、DENSE_RANKは同順位の次も連続した順位です。上の例は得点降順を想定しています。90点の2行についてROW_NUMBERの順序を固定するには追加キーが必要です。

SELECT 顧客ID, 金額,
       ROW_NUMBER() OVER (PARTITION BY 顧客ID ORDER BY 金額 DESC) AS 連番,
       RANK() OVER (PARTITION BY 顧客ID ORDER BY 金額 DESC) AS 順位,
       DENSE_RANK() OVER (PARTITION BY 顧客ID ORDER BY 金額 DESC) AS 密順位
FROM 注文履歴;

最後の「同率1位すべて」では、RANKのORDER BYに注文IDを足すと同率として扱われなくなるので注意してください。同順位を残したい場合は、順位の定義に含める列を意識します。

NULLが含まれる列をORDER BYに使うと、NULLを先にするか後にするかがDB製品や指定により異なる場合があります。実運用ではNULLを除外するか、DBの対応構文で明示的に扱います。

-- 例: 注文日時が存在する注文だけから選ぶ
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY 顧客ID
    ORDER BY 注文日時, 注文ID
  ) AS rn
  FROM 注文履歴
  WHERE 注文日時 IS NOT NULL
)
SELECT * FROM ranked WHERE rn = 1;

内側のWHEREは「番号付けの対象」を絞り、外側のWHEREは「番号を付け終わった行」を絞ります。たとえば返品を除くかどうかを番号付け前後のどちらで判断するかで、答えが変わり得ます。

大量の注文履歴を顧客別に並べ替える処理では、ソートやスキャンのコストが生じます。(顧客ID, 注文日時, 注文ID)など、絞り込み・グループ分け・順序に合った複合インデックスは候補になりますが、必ずそのインデックスが使われるとは限りません。

-- 実際の列名とDB仕様に合わせて検討
CREATE INDEX idx_orders_customer_time
  ON 注文履歴 (顧客ID, 注文日時, 注文ID);

-- PostgreSQL等で実行計画を確認
EXPLAIN ANALYZE
SELECT ...;

対象行数、フィルタ条件、索引の選択性、DBの最適化機能によってGROUP BY + JOINとウィンドウ関数の優劣は変わります。SQLの短さだけで性能を決めつけないようにします。

複数企業が同じDBを利用するマルチテナントのSaaSでは、顧客IDだけでPARTITIONを切ると別テナントのデータが混在し得ます。アクセス制御を適切に実施したうえで、テナントIDと顧客IDの組でグループ化するなど、業務上の識別子の範囲を合わせます。

-- 例: テナント内の顧客ごとの最新注文
ROW_NUMBER() OVER (
  PARTITION BY テナントID, 顧客ID
  ORDER BY 注文日時 DESC, 注文ID DESC
)

この記述だけでアクセス制御が完成するわけではありません。認証・認可、テナント単位のWHERE条件、DB側の行レベルセキュリティなどをシステム要件に応じて組み合わせます。またユーザー入力をSQLに埋め込む場合はプレースホルダを使用し、SQLインジェクションを防止します。

実際のDBには接続しないブラウザ内シミュレーターです。元データ・順位列・抽出結果を並べ、顧客別の初回／最新／最高額と、ROW_NUMBER・RANK・DENSE_RANKを切り替えて確認できます。同値を含むデータも試してください。

WITH 順位付き AS (
  SELECT 顧客ID, 注文回, 金額,
         ROW_NUMBER() OVER (
           PARTITION BY 顧客ID
           ORDER BY 注文回 ASC
         ) AS 順位
  FROM 注文履歴
)
SELECT 顧客ID, 注文回, 金額, 順位
FROM 順位付き
WHERE 順位 = 1
ORDER BY 顧客ID, 順位;

GROUP BYはグループを集約し、ウィンドウ関数は元の行を保持して計算します。顧客ごとの初回注文をその金額とともに取得するとき、MINとJOINを使う方法に加え、ROW_NUMBERで順位を付けて1位だけ抽出する方法があります。

PARTITION BYは「誰ごとに」、ORDER BYは「何の順番で」、ROW_NUMBERは「何番を振るか」。この順で考えると、設備の直近点検や製品の最新価格など、多くの業務要件へ応用できます。最後に必ず確認したいのは、同値の扱いとデータの一意性です。