メインコンテンツまでスキップ

DB SQL Window Functions

Overview​

ウィンドウ関数（Window Functions） は、SQLにおいて 行の集合（ウィンドウ）を対象に計算を行いながら、各行の結果を保持する分析用の関数。
通常の集計関数（SUM や AVG など）は GROUP BY を使用すると 行がまとめられてしまいますが、ウィンドウ関数を使用すると 元の行を保持したまま集計結果を取得できる。

ウィンドウ関数は主に以下のような用途で使用される

  • ランキングの計算
  • 累積合計（Running Total）
  • グループ内順位
  • 前後行の比較
  • 分析クエリ

基本構文​

function_name(...) OVER (
    PARTITION BY columng
    ORDER BY column
)

構成要素​

要素説明
function_name使用するウィンドウ関数
OVERウィンドウ関数を使用することを示すキーワード
PARTITION BYグループ単位を定義（省略可能）
ORDER BY計算順序を定義

シンプルな例（累積売上）​

SELECT
  order_id,
  amount,
  SUM(amount) OVER (ORDER BY order_id) AS running_total
FROM orders;

処理内容​

  1. orders テーブルを取得
  2. order_id 順に並べる
  3. SUM(amount) を累積計算

結果

order_idamountrunning_total
1100100
2200300
3150450

PARTITION BY の使用例​

PARTITION BY を使用すると グループ単位でウィンドウ計算ができます。

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

このクエリは 顧客ごとの累積売上を計算します。

代表的なウィンドウ関数​

ROW_NUMBER​

行番号を付与します。

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

RANK​

ランキングを付与します（同順位あり）。

SELECT
  customer_id,
  amount,
  RANK() OVER (ORDER BY amount DESC) AS ranking
FROM orders;

DENSE_RANK​

順位を詰めたランキングを付与します。

SELECT
  customer_id,
  amount,
  DENSE_RANK() OVER (ORDER BY amount DESC) AS ranking
FROM orders;

LAG​

前の行の値を取得します。

SELECT
  order_id,
  amount,
  LAG(amount) OVER (ORDER BY order_id) AS prev_amount
FROM orders;

LEAD​

次の行の値を取得します。

SELECT
  order_id,
  amount,
  LEAD(amount) OVER (ORDER BY order_id) AS next_amount
FROM orders;

ウィンドウ関数とGROUP BYの違い​

項目GROUP BYWindow Function
行数減る変わらない
集計結果グループ単位各行に付与
用途集計分析

例

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

この場合 顧客ごとに1行だけになります。

一方

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

この場合 元の行を保持したまま集計値を取得できます。

使用される主な場面​

ウィンドウ関数は以下のような分析クエリでよく使用されます。

  • 売上ランキング
  • 顧客ごとの累積売上
  • 時系列データ分析
  • 前回データとの差分
  • 上位N件抽出

特に データ分析・BIクエリ・レポート生成で頻繁に利用されます。

対応している主なデータベース​

Database対応
PostgreSQL✔
MySQL8.0+
SQL Server✔
Oracle✔
SQLite✔

まとめ​

ウィンドウ関数（Window Functions）は、SQLにおいて 分析クエリを記述するための非常に重要な機能です。

特に以下のような処理で活用されます。

  • ランキング
  • 累積計算
  • 行間比較
  • グループ内分析

GROUP BY では表現できない分析処理を実現できるため、モダンなSQLでは広く利用されています。

OVER句（Window Clause）​

ウィンドウ関数は OVER() 句 と組み合わせて使用します。

通常の集計関数（SUM や COUNT など）は OVER() が無い場合、GROUP BY と同様に 複数の行を1行にまとめる集計になります。

しかし OVER() を指定すると、

  • 行をまとめず
  • 指定した範囲（ウィンドウ）の中で計算し
  • 結果を各行に付与

する動作になります。

例

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

このクエリでは 全体の合計値を計算しながら、各行にその結果を表示します。

OVER() の構成要素​

OVER() の中では、ウィンドウ（計算対象の範囲）を定義します。

主に以下の3つの要素で構成されます。

要素役割
PARTITION BYグループ単位を定義
ORDER BYウィンドウ内の並び順を定義
ROWS / RANGE計算対象の行範囲を定義

PARTITION BY​

PARTITION BY は ウィンドウのグループ分割を行います。

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

このクエリでは 顧客ごとの合計売上が計算されます。

ORDER BY​

ORDER BY は ウィンドウ内の計算順序を決定します。

ランキングや累積計算では必須になります。

SELECT
  order_id,
  amount,
  SUM(amount) OVER (
      ORDER BY order_id
  ) AS running_total
FROM orders;

この場合、order_id の順に並べた上で 累積合計（Running Total） が計算されます。

ROWS / RANGE（Window Frame）​

ROWS または RANGE を使用すると ウィンドウの計算範囲（Window Frame） をさらに細かく指定できます。

SELECT
  order_id,
  amount,
  AVG(amount) OVER (
      ORDER BY order_id
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM orders;

このクエリでは 現在行を含めた直近3行の平均値を計算します。

指定意味
UNBOUNDED PRECEDINGパーティションの先頭行
CURRENT ROW現在の行
N PRECEDINGN行前
N FOLLOWINGN行後

GROUP BY と Window Functions の使い分け​

項目GROUP BYWindow Function
行数減る変わらない
集計結果グループ単位各行に付与
主用途集計処理分析処理

GROUP BY は「データをまとめる処理」、 ウィンドウ関数は「データを分析する処理」に適しています。

実務でよく使われるパターン​

グループ内ランキング​

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

上位N件の取得​

SELECT *
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY amount DESC
    ) AS rn
  FROM orders
) t
WHERE rn <= 3;

これは 顧客ごとの売上上位3件を取得する典型的なSQLパターンです。

まとめ​

ウィンドウ関数は、SQLにおいて 分析クエリを記述するための重要な機能です。

特に以下のような処理で活用されます。

  • ランキング
  • 累積計算
  • 移動平均
  • 行間比較
  • グループ内分析

GROUP BY では表現できない 高度な分析処理を実現できるため、データ分析やレポート作成において広く利用されています。