PostgreSQL: PostgreSQLのウィンドウ関数詳細ガイド

Q: 複数のウィンドウ関数で1つのOVER句を共有できますか？

はい。PostgreSQLはWINDOW句エイリアスをサポートしています。 WINDOW w AS (PARTITION BY ...) と記述し、複数の関数で OVER w を使用します。

最終更新：2026-08-26

1. 学習目標

ウィンドウ関数のOVER句と実行モデル
PARTITION BYによる分割とORDER BYによる順序付け
3つのフレーム指定: ROWS / RANGE / GROUPS
ランキング関数: ROW_NUMBER / RANK / DENSE_RANK / NTILE
オフセット関数: LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE
累積合計と移動平均
ウィンドウ関数と集計関数の根本的な違い

2. ストーリー

CharlieはSaaSプラットフォームのグロースアナリストです。プロダクトマネージャーからユーザー維持率レポートの作成を依頼されました。

日次新規ユーザー数
7日目維持率
30日目維持率
各ユーザーの登録ランク（同日に登録したN番目のユーザー）

従来のアプローチでは複数のSQL文と一時テーブルが必要です。ウィンドウ関数を学んだCharlieは、1つのSQL文で全統計を取得します。GROUP BYで行を折りたたむ必要はなく、各行が元のデータと共に計算結果を保持します。

3. 概念: ウィンドウ関数の基本

(1) ウィンドウ関数とは

ウィンドウ関数は関連する行のセット（「ウィンドウ」）に対して計算を行いますが、行を折りたたみません。各行が結果を返します。これが集計関数との最大の違いです。

特徴	集計関数	ウィンドウ関数
行数	複数行 → 1行	行数は変わらない
構文	`SUM(col)`	`SUM(col) OVER (...)`
GROUP BY	必須	不要
元のカラムを保持	いいえ	はい
典型的な用途	集計統計	ランキング、オフセット、累計

(2) OVER句の構造

SQL

function_name() OVER (
  [PARTITION BY expr]
  [ORDER BY expr [ASC|DESC] [NULLS FIRST|NULLS LAST]]
  [frame_clause]
)

flowchart TD
    A[OVER] --> B[PARTITION BY]
    B --> C[ORDER BY]
    C --> D[フレーム句]
    D --> E{ROWS / RANGE / GROUPS}
    E --> F[ROWS BETWEEN ... AND ...]
    E --> G[RANGE BETWEEN ... AND ...]
    E --> H[GROUPS BETWEEN ... AND ...]
    B -.->|オプション| C
    C -.->|オプション| D
    style A fill:#e1f5fe
    style B fill:#fff9c4
    style C fill:#fff9c4
    style D fill:#c8e6c9

▶ サンプル: 最も単純なウィンドウ関数

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER () AS total_all
FROM orders;

TEXT 📖 参照専用

 order_id | customer_id | amount | total_all
----------+-------------+--------+-----------
      101 |           1 |  15000 |   750000
      102 |           1 |   8000 |   750000
      201 |           2 |  25000 |   750000

すべての行がテーブル全体のSUMを返し、行数は変わりません。

4. 概念: PARTITION BYとORDER BY

(1) PARTITION BYによる分割

PARTITION BYはデータを独立した「パーティション」に分割し、ウィンドウ関数は各パーティション内で個別に計算します。

句	効果	類推
`PARTITION BY col`	カラムで分割	GROUP BYのようだが折りたたまない
PARTITION BYなし	テーブル全体が1パーティション	グループ化カラムなしのGROUP BYのよう

▶ サンプル: 顧客別の合計

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, order_id;

TEXT 📖 参照専用

 order_id | customer_id | amount | customer_total
----------+-------------+--------+----------------
      101 |           1 |  15000 |          23000
      102 |           1 |   8000 |          23000
      201 |           2 |  25000 |          25000
      301 |           3 |  12000 |          57000
      302 |           3 |  45000 |          57000

(2) ORDER BYによる順序付け

ORDER BYはパーティション内の行順序を決定し、ランキングやオフセット関数に不可欠です。

シナリオ	ORDER BYが必要？	理由
ROW_NUMBER / RANK	必須	ランキングは順序に依存
LAG / LEAD	必須	前後行は順序に依存
SUM() OVER (PARTITION BY)	オプション	順序なしでパーティション合計を計算
FIRST_VALUE / LAST_VALUE	必須	最初/最後の値は順序に依存

▶ サンプル: 金額ランキング

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn_desc
FROM orders;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) PARTITION BY + ORDER BYの組み合わせ

まず分割し、次に順序付けます。ランキ��グは各パーティション内で独立して計算されます。

▶ サンプル: 顧客ごとの注文金額ランキング

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS rank_in_customer
FROM orders;

TEXT 📖 参照専用

 order_id | customer_id | amount | rank_in_customer
----------+-------------+--------+------------------
      102 |           1 |   8000 |                2
      101 |           1 |  15000 |                1
      201 |           2 |  25000 |                1
      302 |           3 |  45000 |                1
      301 |           3 |  12000 |                2

5. 概念: フレーム句

(1) 3つのフレームタイプ

フレーム句はウィンドウ関数が計算のために「見る」行を決定します。

フレームタイプ	境界の基準	最適な用途
ROWS	物理的な行オフセット	精密な行制御。例: 「前の3行」
RANGE	論理的な値オフセット	同じ値の行をグループ化。例: 「同じ金額」
GROUPS	同じ値のグループオフセット	PostgreSQL固有。ORDER BYの値でグループ化

(2) フレーム境界キーワード

キーワード	意味
`UNBOUNDED PRECEDING`	パーティションの最初の行
`UNBOUNDED FOLLOWING`	パーティションの最後の行
`CURRENT ROW`	現在行
`N PRECEDING`	N行 / N値前
`N FOLLOWING`	N行 / N値後

(3) デフォルトフレームルール

ORDER BYあり？	デフォルトフレーム
はい	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
いいえ	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`

▶ サンプル: ROWSフレーム累積合計

SQL

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM daily_sales;

TEXT 📖 参照専用

 order_date  | amount | running_sum
-------------+--------+-------------
 2025-01-01  |   5000 |        5000
 2025-01-02  |   8000 |       13000
 2025-01-03  |   3000 |       16000
 2025-01-04  |  12000 |       28000

▶ サンプル: ROWSによる3行移動平均

SQL

SELECT
  order_date,
  amount,
  ROUND(AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg_3
FROM daily_sales;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

▶ サンプル: RANGEフレームで日付間隔

SQL

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS sum_last_7_days
FROM daily_sales;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

6. 概念: ランキング関数

(1) 4つのランキング関数の比較

関数	同値の処理	出力	連続？
ROW_NUMBER	厳密に増加	1,2,3,4	はい
RANK	同ランク、スキップ	1,1,3,4	いいえ
DENSE_RANK	同ランク、スキップなし	1,1,2,3	はい
NTILE(N)	Nグループに分割	1,1,2,2,3,3	—

▶ サンプル: ROW_NUMBER vs RANK vs DENSE_RANK

SQL

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM students;

TEXT 📖 参照専用

 name   | score | rn | rnk | drnk
--------+-------+----+-----+------
 Alice  |    95 |  1 |   1 |    1
 Bob    |    90 |  2 |   2 |    2
 Charlie|    90 |  3 |   2 |    2
 Dave   |    85 |  4 |   4 |    3

(2) NTILEグループ化

NTILE(N)は順序付き行をN個のほぼ等しいグループに分割します。四分位分析によく使われます。

▶ サンプル: 顧客を支出額で4グループに分割

SQL

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_summary;

TEXT 📖 参照専用

 customer_id | total_spent | quartile
-------------+-------------+----------
          12 |      500000 |        1
           5 |      350000 |        1
           8 |      280000 |        2
          19 |      150000 |        2
           3 |       90000 |        3
          22 |       60000 |        3
          41 |       25000 |        4
          15 |        5000 |        4

7. 概念: オフセット関数と値関数

(1) オフセット関数リファレンス

関数	効果	典型的な用途
LAG(col, N, default)	現在行のN行前の値	期間比較成長率
LEAD(col, N, default)	現在行のN行後の値	予測、比較
FIRST_VALUE(col)	ウィンドウの最初の値	最初の注文金額
LAST_VALUE(col)	ウィンドウの最後の値	最後の注文金額
NTH_VALUE(col, N)	ウィンドウのN番目の値	N番目の注文

▶ サンプル: LAGで前日比変化

SQL

SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
  daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS diff,
  ROUND(
    (daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date))
    * 100.0 / NULLIF(LAG(daily_revenue, 1) OVER (ORDER BY order_date), 0),
  2) AS pct_change
FROM daily_revenue
ORDER BY order_date;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: LEADで翌月を見る

SQL

SELECT
  month,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) FIRST_VALUE / LAST_VALUEの注意点

LAST_VALUEのデフォルトフレームはRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWであり、パーティション全体ではありません。パーティションの最後の行を取得するには、明示的にフレームを指定する必要があります。

関数	デフォルトフレーム	パーティションの最後の行を取得する方法
FIRST_VALUE	CURRENT ROWまで（たまたま正しい）	変更不要
LAST_VALUE	CURRENT ROWまで（最後の行ではない！）	`ROWS BETWEEN ... AND UNBOUNDED FOLLOWING`を追加

▶ サンプル: FIRST_VALUEとLAST_VALUE

SQL

SELECT
  order_id,
  customer_id,
  amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS first_order_amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_order_amount
FROM orders;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ サンプル: NTH_VALUEで2番目の注文

SQL

SELECT
  order_id,
  customer_id,
  amount,
  NTH_VALUE(amount, 2) OVER (
    PARTITION BY customer_id ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_order_amount
FROM orders;

TEXT 📖 参照専用

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. 概念: 累積合計と移動平均

(1) 累積合計

フレーム	意味	SQL
デフォルトフレーム	パーティション先頭から現在行まで	`SUM() OVER (ORDER BY col)`
明示的ROWS	同上	`ROWS UNBOUNDED PRECEDING`
明示的RANGE	同値行をグループ化	`RANGE UNBOUNDED PRECEDING`

▶ サンプル: 月次累積収益

SQL

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM monthly_revenue;

TEXT 📖 参照専用

  month   | revenue | running_revenue
----------+---------+----------------
 2025-01  |  500000 |         500000
 2025-02  |  620000 |        1120000
 2025-03  |  580000 |        1700000
 2025-04  |  710000 |        2410000

(2) 移動平均

▶ サンプル: 7日間移動平均

SQL

SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7day
FROM daily_revenue;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

(3) 分割累積合計

▶ サンプル: 顧客ごとの累積支出

SQL

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS cumulative_spent
FROM orders
ORDER BY customer_id, order_date;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

9. ウィンドウ関数と集計関数の比較

観点	集計関数	ウィンドウ関数
行数	1行に折りたたまれる	元の行数が保持される
構文	`SUM(col)`	`SUM(col) OVER(...)`
GROUP BY	必須	不要
行ごとの計算	1次元	複数の異なるウィンドウが可能
パフォーマンス	通常高速	ソート＋分割が必要でやや遅い
最適な用途	集計レポート	ランキング、オフセット、累計

▶ サンプル: 同等の書き方の比較

集計関数スタイル:

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

TEXT 📖 参照専用

  result  
----------
   42.50
(1 row)

ウィンドウ関数スタイル（元の行を保持）:

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

10. 総合サンプル

Charlieのユーザー維持分析——日次新規ユーザー数とその7日目/30日目維持率のカウント。

SQL

WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS first_date,
    ROW_NUMBER() OVER (PARTITION BY MIN(login_date) ORDER BY user_id) AS reg_rank
  FROM user_logins
  GROUP BY user_id
),
daily_new_users AS (
  SELECT
    first_date AS cohort_date,
    COUNT(*) AS new_users
  FROM first_login
  GROUP BY first_date
),
retention_base AS (
  SELECT
    fl.first_date AS cohort_date,
    fl.user_id,
    ul.login_date,
    ul.login_date - fl.first_date AS day_offset
  FROM first_login fl
  JOIN user_logins ul ON fl.user_id = ul.user_id
),
retention_count AS (
  SELECT
    cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS retained_users
  FROM retention_base
  WHERE day_offset IN (0, 7, 30)
  GROUP BY cohort_date, day_offset
)
SELECT
  r.cohort_date,
  n.new_users,
  MAX(CASE WHEN r.day_offset = 0  THEN r.retained_users END) AS d0,
  MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) AS d7,
  MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) AS d30,
  ROUND(
    MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day7_rate,
  ROUND(
    MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day30_rate
FROM retention_count r
JOIN daily_new_users n ON r.cohort_date = n.cohort_date
GROUP BY r.cohort_date, n.new_users
ORDER BY r.cohort_date;

11. 実行順序

SQL実行順序におけるウィンドウ関数の位置。

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["ウィンドウ関数<br/>OVER / PARTITION / ORDER / FRAME"]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT]

    style E fill:#c8e6c9
    style D fill:#fff9c4

ステップ	句	説明
1	FROM	データソースを決定
2	WHERE	行を絞り込み
3	GROUP BY	集計グループ化
4	HAVING	グループを絞り込み
5	ウィンドウ関数	絞り込まれた結果に対して計算
6	SELECT	出力カラムを選択
7	DISTINCT	重複除去
8	ORDER BY	最終ソート
9	LIMIT	行数制限

❓ よくある質問

Q ウィンドウ関数をWHERE句で使用できますか？

A いいえ。ウィンドウ関数はWHEREの後に実行されるため、WHEREはその結果を参照できません。サブクエリまたはCTEでラップしてフィルターしてください。

Q ROW_NUMBERとRANKの違いは？

A ROW_NUMBERは厳密に増加します（1,2,3,4）。RANKは同値に同じランクを与えてスキップします（1,1,3,4）。重複除去されたTop-1にはROW_NUMBERを使用してください。

Q LAST_VALUEがパーティションの最後の行を返さないのはなぜですか？

A デフォルトフレームがRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWだからです。ROWS BETWEEN ... AND UNBOUNDED FOLLOWINGを明示的に記述する必要があります。

Q 複数のウィンドウ関数で1つのOVER句を共有できますか？

A はい。PostgreSQLはWINDOW句エイリアスをサポートしています。WINDOW w AS (PARTITION BY ...)と記述し、複数の関数でOVER wを使用します。

Q ROWSフレームとRANGEフレームの違いは？

A ROWSは物理的な行数でオフセッ��します。RANGEは論理的な値でオフセットします。RANGEは同じORDER BY値を持つ行を1つの境界として扱います。ほとんどの累積合計ケースではROWSを使用します。

Q ウィンドウ関数のパフォーマンスを最適化するには？

A PARTITION BY + ORDER BYカラムにインデックスを確保する。パーティション数を減らす。大規模パーティションでの複雑なフレーム計算を避ける。CTEで先に絞り込み、その後ウィンドウ計算を行う。

📖 まとめ

ウィンドウ関数は行を折りたたまず、各行が結果を返す。OVER句がウィンドウを定義
PARTITION BYで分割、ORDER BYで順序付け、フレームで計算範囲を制御
ROW_NUMBER / RANK / DENSE_RANKは異なる動作。注意して選択
LAG / LEADで前後行にアクセス。FIRST_VALUE / LAST_VALUEのデフォルトフレームに注意
ROWSは物理行、RANGEは論理値、GROUPSはグループ値でオフセット
累積合計と移動平均は典型的なウィンドウ関数のユースケース
ウィンドウ関数はWHERE/GROUP BYの後に実行され、WHEREでは使用不可
WINDOW句でOVER定義を再利用し、重複を削減

📝 練習問題

⭐ ROW_NUMBERを使用して、各顧客の上位2件の最高金額注文をクエリしてください。
⭐ LAGを使用して、各顧客の隣接する注文間の金額差を計算してください。
⭐⭐ リージョンごとの日次収益の7日間移動平均を計算してください。
⭐⭐ DENSE_RANKとNTILE(4)を使用して、総支出額で顧客を4階層に分割してください。
⭐⭐⭐ 自己結合の代わりにウィンドウ関数を使用して、日次新規ユーザーの1日目/7日目/30日目維持率を1つのSQL文で計算してください。

Web-Tutorial 技術チーム

複数の開発者によって共同維持されているプログラミングチュートリアルプラットフォーム。各チュートリアルは専門分野の開発者が執筆・レビューしています。正確で信頼性の高いコンテンツを目指しています — 問題を見つけた場合はお知らせください。

私たちについて