SQLのウィンドウ関数とは？順位・累計を元の表と結果で解説

例で使う表

salesの1行は、同じ年の「担当者1人・1か月」の売上です。青木さんは1〜3月、北野さんは1〜2月のデータがあります。青木さんの2月と3月は同額なので、順位の違いも確認できます。各例の末尾のORDER BYは、結果表の表示順を決めるために付けています。

ウィンドウ関数の基本構文

SELECT id, salesperson, month_no, amount,
  SUM(amount) OVER () AS total_amount
FROM sales
ORDER BY id;

OVER ()で各行に全体の合計を付ける

売上は10 + 20 + 20 + 30 + 40で合計120万円です。SUM(amount) OVER ()は、この合計を5行すべてに付けます。SUM(amount)だけで集計すると1行になりますが、OVER ()を付けた結果では月別の5行が残ります。

SELECT id, salesperson, month_no, amount,
  SUM(amount) OVER () AS total_amount
FROM sales
ORDER BY id;

PARTITION BYで担当者ごとに計算する

OVERの中にPARTITION BY salespersonを足すと、合計を求める範囲が担当者ごとに分かれます。青木さんの行には50万円、北野さんの行には70万円が付きます。GROUP BYのように担当者を1行にまとめるのではなく、月別の5行を残します。

SELECT id, salesperson, month_no, amount,
  SUM(amount) OVER (PARTITION BY salesperson) AS person_total
FROM sales
ORDER BY id;

ROW_NUMBERで売上の高い順に連番を付ける

ROW_NUMBERは1から始まる連番を付けます。OVER内のORDER BY amount DESC, idで、売上の高い順、同額なら行IDの小さい順に番号を決めます。20万円の行にも3と4の別の番号が付きます。末尾のORDER BYも同じ順にして、番号と表示を見比べられるようにしています。

SELECT id, salesperson, month_no, amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS position
FROM sales
ORDER BY amount DESC, id;

RANKで同じ売上に同じ順位を付ける

同額を同順位にしたいときはRANKを使います。40万円が1位、30万円が2位、20万円の2行はどちらも3位です。その次の10万円は5位になります。同順位を判定するOVER内では売上だけを比較し、idは表示順にだけ使います。

SELECT id, salesperson, month_no, amount,
  RANK() OVER (ORDER BY amount DESC) AS position
FROM sales
ORDER BY amount DESC, id;

DENSE_RANKで次の順位を飛ばさずに付ける

RANKをDENSE_RANKに変えると、同じ売上に同順位を付けつつ、その次を飛ばしません。20万円の2行が3位なのは同じですが、10万円は4位になります。前のRANKの結果と、最後の行の番号を比べてください。

SELECT id, salesperson, month_no, amount,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS position
FROM sales
ORDER BY amount DESC, id;

LAGで前の月の売上を隣に表示する

LAG(amount)は指定順で1行前の売上を返します。担当者ごとに分け、month_no順で計算すると、青木さんの2月には1月の10万円が付きます。各担当者の先頭月は前の行がないのでNULLです。この表は月が連続していますが、欠けた月があると「前の行」は前月とは限りません。

SELECT id, salesperson, month_no, amount,
  LAG(amount) OVER (
    PARTITION BY salesperson ORDER BY month_no
  ) AS previous_amount
FROM sales
ORDER BY id;

SUMとROWSで月ごとの累計を求める

担当者ごとに月順で足し上げます。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは「先頭行から今の行まで」という計算範囲です。青木さんは10、30、50万円、北野さんは30、70万円となり、担当者が変わると累計も別になります。

SELECT id, salesperson, month_no, amount,
  SUM(amount) OVER (
    PARTITION BY salesperson ORDER BY month_no
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales
ORDER BY id;

AVGとROWSで直前の月と今月の平均を求める

前の例の範囲をROWS BETWEEN 1 PRECEDING AND CURRENT ROWに変え、AVGを使います。青木さんの2月は(10 + 20) ÷ 2で15万円、3月は(20 + 20) ÷ 2で20万円です。先頭月は1行しかないため、その月の値が平均になります。

SELECT id, salesperson, month_no, amount,
  AVG(amount) OVER (
    PARTITION BY salesperson ORDER BY month_no
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_average
FROM sales
ORDER BY id;

順位を条件にするには外側のWHEREで絞る

ウィンドウ関数の結果は、同じSELECTのWHEREでは条件にできません。まず内側で売上順のpositionを付け、その結果にrankedという名前を付けます。外側でposition <= 2とすると、40万円と30万円の2行が残ります。

SELECT id, salesperson, month_no, amount, position
FROM (
  SELECT id, salesperson, month_no, amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC, id) AS position
  FROM sales
) AS ranked
WHERE position <= 2
ORDER BY position;

実際に1問解いてみる

関連構文

ウィンドウ関数についてよくある質問

OVERの中と末尾のORDER BYはどう違いますか？

OVER内は順位や前後関係を計算する順番です。SQL末尾は最終的な表示順です。OVER内にORDER BYを書くだけでは表示順は保証されません。

担当者ごとの順位も付けられますか？

はい。ROW_NUMBERやRANKのOVER内にPARTITION BY salespersonを加えると、担当者ごとに順位が1から始まります。

累計のROWSは省略してよいですか？

省略すると、OVER内のORDER BYで同じ値を持つ行をまとめて範囲に含める既定の動作になります。1行ずつ足す意図ならROWSを明示し、同順位がある場合は行IDなども計算順に加えます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。

• PostgreSQL公式：ウィンドウ関数入門 — ウィンドウ関数入門の仕様と使用例
• PostgreSQL公式：ウィンドウ関数 — ウィンドウ関数の仕様と使用例
• PostgreSQL公式：ウィンドウ式と計算範囲 — ウィンドウ式と計算範囲の仕様と使用例