PostgreSQLのウィンドウ関数の使い方（ROW_NUMBER・LAG・移動平均）

PostgreSQL のウィンドウ関数は、GROUP BY のように行をまとめてしまわずに、各行を残したまま「グループ内の順位」「前の行との差」「累計や移動平均」を計算できる機能です。書き方の中心は 関数名() OVER (PARTITION BY … ORDER BY …) の一つだけで、ここを理解すれば多くの集計を一本の SELECT で書けます。この記事では、基本の構文、順位・前後比較・累計の定番パターン、つまずきやすいフレームの既定値、性能面の注意までを順に説明します。

なお、WordPress 本体は MySQL / MariaDB を前提としており、PostgreSQL は公式にはサポートされていません（2026年10月現在）。この記事は、WordPress と並べて運用している分析用のデータベースや、別のアプリケーションで PostgreSQL を使う場面を想定しています。MySQL 8.0 以降にもウィンドウ関数はありますが、細かな対応範囲は異なります。

例で使うテーブル

以下の例は、注文を記録する次のテーブルを前提にします。手元で試す場合は、本番ではなく検証用のデータベースで作成してください。

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  customer_id integer       NOT NULL,
  ordered_at  timestamptz   NOT NULL,
  amount      numeric(10,2) NOT NULL
);

OVER句の基本：行を残したまま集計する

まず GROUP BY との違いを確認します。GROUP BY customer_id で合計を出すと、顧客ごとに1行へまとめられ、個々の注文は見えなくなります。ウィンドウ関数では、各注文の行を残したまま、その横に「その顧客の合計」を並べられます。

SELECT id,
       customer_id,
       amount,
       sum(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

OVER の中身が「どの行の集まり（ウィンドウ）を見て計算するか」を決めます。

• PARTITION BY：計算を区切る単位。省略すると結果全体が一つのウィンドウになります。
• ORDER BY：ウィンドウ内の並び順。順位や累計のように順序が意味を持つ計算で指定します。
• フレーム指定（ROWS BETWEEN … など）：並び順の中で、現在の行から見てどこまでを計算に含めるか。

sum や avg、count といった普通の集計関数も、OVER を付けるとウィンドウ関数として動きます。各注文が顧客合計の何割かを出したいときは amount / sum(amount) OVER (PARTITION BY customer_id) のように、通常の列と組み合わせた式も書けます。

順位をつける：row_number・rank・dense_rank

グループ内の順位づけは、ウィンドウ関数がもっとも役立つ場面の一つです。三つの関数は同じ値（同順位）の扱いが違います。

• row_number()：同じ値でも 1, 2, 3 と連番を振ります。同順位の並びは不定なので、安定させたい場合は ORDER BY に主キーなどを追加します。
• rank()：同じ値は同じ順位にし、次は飛ばします（1, 1, 3）。
• dense_rank()：同じ値は同じ順位にし、次を飛ばしません（1, 1, 2）。

よくある要件が「顧客ごとに最新の注文を1件だけ取りたい」というものです。

SELECT *
FROM (
  SELECT o.*,
         row_number() OVER (
           PARTITION BY customer_id
           ORDER BY ordered_at DESC, id DESC
         ) AS rn
  FROM orders o
) t
WHERE rn = 1;

ここで内側のサブクエリが必要なのは、ウィンドウ関数が WHERE や GROUP BY、HAVING の処理が終わった後に計算されるためです。WHERE row_number() OVER (…) = 1 と直接書くとエラーになります。サブクエリか CTE（WITH 句）で一段包み、外側で絞り込むのが定石です。上位3件なら rn <= 3 に変えるだけです。PostgreSQL 15 以降では、このように外側で row_number() などを絞り込む形について、内部で早めに計算を打ち切る最適化が入っています。

なお「顧客ごとに1件」だけであれば、PostgreSQL 独自の SELECT DISTINCT ON (customer_id) … ORDER BY customer_id, ordered_at DESC でも書けます。上位N件や同順位の扱いを柔軟に変えたいときはウィンドウ関数の方が扱いやすい、と使い分けるとよいでしょう。

前の行と比べる：lag・lead

前回の注文からの間隔や、前月比を出すには lag()（前の行の値）と lead()（次の行の値）を使います。引数は「値」「何行前（後）か」「行が無いときの既定値」の順です。

SELECT customer_id,
       ordered_at,
       amount,
       ordered_at - lag(ordered_at) OVER w AS since_prev_order,
       amount - lag(amount, 1, 0) OVER w   AS diff_from_prev
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY ordered_at);

同じ OVER の中身を何度も書く場合は、この例のように WINDOW 句で名前を付けると読みやすくなり、書き間違いも防げます。各顧客の最初の注文には前の行が無いため、lag(ordered_at) は NULL になります。差分を 0 として扱いたいのか、NULL のまま「比較対象なし」と示したいのかは、集計の目的に合わせて決めてください。

累計と移動平均：フレームの指定

累計は ORDER BY を付けた sum() で書けます。

SELECT ordered_at::date AS day,
       amount,
       sum(amount) OVER (ORDER BY ordered_at, id) AS running_total
FROM orders;

日ごとの売上から「直近7日の移動平均」を出すには、まず日別に集計し、その結果に対してフレームを指定します。

WITH daily AS (
  SELECT ordered_at::date AS day, sum(amount) AS total
  FROM orders
  GROUP BY 1
)
SELECT day,
       total,
       avg(total) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_7rows
FROM daily
ORDER BY day;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW は「現在の行と、その前の6行」です。ここでつまずきやすいのが、売上が無い日は行そのものが存在しない点です。行数で数える ROWS では、欠けた日があると「直近7行」が7日より長い期間になります。日付の幅で区切りたい場合は RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW のように RANGE で値の幅を指定するか、generate_series で日付の行を補ってから集計します（RANGE で間隔を指定する書き方は PostgreSQL 11 以降）。

既定のフレームとlast_valueの落とし穴

ORDER BY を指定したときの既定のフレームは「先頭から現在の行（と同じ値を持つ行）まで」です。累計が ORDER BY を付けるだけで書けるのはこのためですが、last_value() では意図とずれます。

-- 期待と違う例: 現在の行までしか見ないため、ほぼ自分自身の値が返る
SELECT id, last_value(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at)
FROM orders;

-- グループの最後の値がほしい場合はフレームを広げる
SELECT id, last_value(amount) OVER (
         PARTITION BY customer_id ORDER BY ordered_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM orders;

また、既定のフレームは RANGE 扱いなので、並び順の値が同じ行（同時刻の注文など）は同じ累計値になります。1行ずつ積み上げたいなら、上の累計の例のように ORDER BY に id を加えて順序を一意にするか、ROWS を明示します。

性能面の注意と確認方法

ウィンドウ関数は便利ですが、計算の前に PARTITION BY と ORDER BY の順で行を並べ替えるため、大きな表では並べ替えが重くなります。確認は EXPLAIN (ANALYZE, BUFFERS) で行い、実行計画に WindowAgg とその下の Sort が出ているか、Sort Method がメモリ内で済んでいるか（ディスクに書き出す external merge になっていないか）を見ます。

• 対象を先に絞る：WHERE で期間などを絞ってからウィンドウ関数を計算すると、並べ替える行数が減ります。
• インデックスを検討する：PARTITION BY customer_id ORDER BY ordered_at なら (customer_id, ordered_at) の複合インデックスがあると、並べ替えを省ける場合があります。効くかどうかは実行計画で確かめてください。
• work_mem の扱い：並べ替えがディスクに溢れる場合、セッション単位で SET work_mem を上げて試す方法があります。サーバー全体の設定を大きくすると同時接続数ぶんメモリを使う恐れがあるため、変更は慎重に行います。
• OVER の種類を揃える：同じ並び順のウィンドウをまとめると、並べ替えの回数が減ることがあります。

本番のデータベースで重い集計を試すと、ほかの処理を遅くすることがあります。まず検証環境や複製（レプリカ）で計画と所要時間を確認し、必要ならバックアップを取ったうえで本番に適用してください。

まとめ

• ウィンドウ関数は 関数() OVER (PARTITION BY … ORDER BY …) で、行を残したままグループ内の計算ができます。
• 順位は row_number・rank・dense_rank を同順位の扱いで使い分け、絞り込みはサブクエリや CTE の外側で行います。
• 前後比較は lag・lead、累計や移動平均はフレーム指定で書きます。欠けた日付と ROWS / RANGE の違いに注意します。
• ORDER BY 付きの既定フレームは「現在の行まで」なので、last_value では範囲を明示します。
• 重いときは EXPLAIN (ANALYZE, BUFFERS) で WindowAgg と Sort を確認し、対象の絞り込みやインデックスで対処します。

• PostgreSQLのCTE（WITH句）の使い方
• PostgreSQLのインデックス最適化