← 一覧へ
データベース
#Window Function#OVER절#PARTITION BY#순위 함수#분석 함수
最終更新 · 2026-11-13

ウィンドウ関数(Window Function)

1. 概要

ウィンドウ関数(Window Function)とは、結果行を一つにまとめることなく、現在行と論理的に関連する行の集合(ウィンドウ、Window)を対象に集計・順位・オフセット計算を行い、各行ごとに一つの結果値を返すSQL分析関数である。OVER句でウィンドウの範囲を明示する点が、通常の集計関数と決定的に異なる。

従来の集計関数(SUM、AVG、COUNT)はGROUP BYと併用すると、複数行を一つの要約行に畳み込んでしまう。しかし実務の分析クエリは「個々の明細はそのまま見せつつ、その横に部門平均や累計・順位を併記したい」という要求がほとんどである。かつてはこのために同一テーブルをセルフ結合したり、相関サブクエリ(correlated subquery)を繰り返し記述したりする必要があり、クエリは長く遅く可読性が低かった。ウィンドウ関数はまさにこの隔たり — 明細データの保持と集計的洞察の並行 — を単一の構文で解決するために登場した。

ウィンドウ関数はSQL:2003標準で正式に導入されて以来、分析型クエリの事実上の標準ツールとして定着した。Oracleは早くから分析関数(Analytic Function)として、SQL Serverは順位関数とフレーム機能を、PostgreSQLは8.4版から、MySQLは比較的遅く8.0(2018)から対応し、今日ではほぼすべてのリレーショナルDBMSと分析エンジンがこれを提供している。データウェアハウス・BI・リアルタイムレポーティングの中核演算であり、技術士の観点では「集計の表現力を宣言的に高め、アプリケーションロジックをDBへ下ろす」という設計的含意が核心論点である。

ウィンドウ関数がなかった時代の分析クエリがどれほどの苦痛を伴ったかを思い起こせば、その必要性は明らかになる。「各部門で最も給与が高い者」を探すには部門別の最高給与を求めるサブクエリを作り、それを元表と再び結合する必要があり、「累計売上」を求めるには各行ごとに自身以下の全行を合算する相関サブクエリを書いて性能を犠牲にする必要があった。こうしたクエリは記述が難しいだけでなく、意図を読み取ることも困難であった。ウィンドウ関数はこの反復的で誤りの多いパターンを標準化された宣言的構文に置き換えることで、分析者がデータの問い自体に集中できるようにした点で、生産性面の寄与が大きい。

特徴を要約すると次のとおりである。第一に、行数を保持する(入力N行 → 出力N行)。第二に、OVER句でパーティション・整列・フレームを宣言的に指定する。第三に、SELECTとORDER BY句でのみ使え、WHERE・GROUP BY・HAVINGでは直接使えない(論理的実行順序上これらの後に評価されるため)。第四に、複数のウィンドウ関数を一つのクエリで異なるウィンドウとして混用でき、表現力が非常に高い。

これらの性質により、ウィンドウ関数は単なる構文上の利便を超えて分析クエリのパラダイムを変えた技術と評価される。かつて明細データと集計洞察を併せて得るには手続き的思考(カーソルを回して累積変数を更新する方式)に戻るか、複雑な入れ子クエリを書く必要があったが、ウィンドウ関数は「何を計算するか」だけを宣言すれば「どう計算するか」はオプティマイザが担う宣言型アプローチを完成させる。技術士の答案では、この点を「集合指向(set-oriented)SQLの表現力拡張」という文脈で提示すれば説得力が高い。

2. ウィンドウ関数の構成と動作原理

ウィンドウ関数はクエリの論理的処理パイプラインでFROM・WHERE・GROUP BY・HAVINGをすべて経た後、結果集合がほぼ確定した段階で評価される。この位置のため、WHERE句でウィンドウ関数の結果でフィルタするには必ずサブクエリかCTEで一度包む必要があるという実務的制約が生じる。下の構造図はウィンドウ関数が評価される時点を示す。

flowchart TB
    A["FROM / JOIN<br/>(ソース集合の構成)"] --> B["WHERE<br/>(行フィルタ)"]
    B --> C["GROUP BY / 集計<br/>(グループ要約)"]
    C --> D["HAVING<br/>(グループフィルタ)"]
    D --> E["ウィンドウ関数の評価<br/>(OVER: パーティション・整列・フレーム)"]
    E --> F["SELECT 射影"]
    F --> G["ORDER BY / LIMIT"]
    style E fill:#e1f5ff,stroke:#0288d1

構造図が示すように、ウィンドウ関数はSELECT射影の直前、しかしグループ集計の後に動作する。したがってGROUP BYで既に要約された結果の上に再びウィンドウ関数を重ね、「グループ別小計を計算した後にその小計の順位を付ける」二段分析も可能である。この実行順序を理解しないと、「ウィンドウ関数をWHEREで使ったらエラーが出た」というよくある誤りを繰り返すことになる。

この制約を回避する標準パターンが、まさにCTE(共通テーブル式)やインラインビューで一枚包むことである。内側のクエリでウィンドウ関数により順位・累積値を計算して列にした後、外側のクエリのWHEREでその列をフィルタする。例えば「各部門の1位のみ」のようなTop-Nクエリは、内側でROW_NUMBERを付け外側でWHERE rn = 1を適用する二段構造で書かれる。このパターンを体得すれば、ウィンドウ関数の実行位置の制約は限界ではなく、むしろ「分析段階を明示的に分離する」可読性の良い構造へと転じる。

A. OVER句の三つの構成要素

ウィンドウ関数の動作は、OVER句の三要素 — パーティション(PARTITION BY)、整列(ORDER BY)、フレーム(Frame) — によって完全に決まる。この三つを組み合わせて「現在行を中心にどの行を見るか」を精密に制御する。

PARTITION BY(パーティション分割) は、結果集合全体を指定した列の値に従って複数の独立した区画に分ける。各パーティションは互いに完全に隔離され、ウィンドウ関数は同じパーティション内の行のみを計算対象とする。例えばPARTITION BY deptは部門ごとに境界を引き、部門内部でのみ順位・累積を計算させる。PARTITION BYを省略すると結果集合全体が一つのパーティションになる。概念的にはGROUP BYに似るが、GROUP BYがグループを一つの行に畳むのに対し、PARTITION BYは行を保持したまま計算範囲のみを限定するという決定的な差がある。ここで注意すべきは、PARTITION BYがテーブルの物理的格納分割(パーティショニング)とは全く異なる概念だという点である — 前者はクエリ実行時点の論理的計算区画であり、後者はディスク上のデータ配置戦略で、名前が同じため混同しやすいが層位が完全に異なる。

ORDER BY(ウィンドウ整列) は、各パーティション内部で行の順序を定める。順位関数(ROW_NUMBER、RANKなど)とオフセット関数(LAG、LEAD)はこの順序があってこそ意味を持ち、累積集計(running total)も整列順序に沿って値を積み上げる。注意すべきは、OVER句内のORDER BYはクエリ最終結果の整列を保証しないことである — それはあくまでウィンドウ計算用の順序にすぎず、最終出力の整列は別途の最上位ORDER BYで指定しなければならない。また整列キーにNULLが混在する場合、NULLS FIRST/NULLS LASTの指定により順位とオフセットの結果が変わるため、仕様にNULL整列規則を明示することが欠陥を予防する道である。

フレーム(Frame、ウィンドウ枠) は、整列されたパーティション内で「現在行を基準に実際の計算に含める行の範囲」をより狭く指定する仕組みである。ROWS BETWEEN ... AND ...(物理的行数基準)、RANGE BETWEEN ...(整列キー値基準)、GROUPS BETWEEN ...(同順位グループ基準)の三モードがある。例えばROWS BETWEEN 2 PRECEDING AND CURRENT ROWは直前の2行と現在行、すなわち3行を窓として移動平均を計算する。フレームを明示しないと既定フレームが適用され、ORDER BYがあればRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(先頭から現在行まで)が、なければパーティション全体が既定値となる。

OVER要素 役割 省略時の動作
PARTITION BY 計算範囲を区画に分割 全体が単一パーティション
ORDER BY パーティション内の行順序を指定 順序なし(順位・オフセット無意味)
Frame(ROWS/RANGE/GROUPS) 現在行基準の包含範囲を限定 ORDER BYの有無で既定フレーム

この既定フレーム規則は、実務で最も悪名高い落とし穴の源泉である。LAST_VALUE(x) OVER (ORDER BY d)が「パーティションの最後の値」ではなく「現在行の値」を返す理由は、まさに既定フレームが現在行で切れるためである。正しく使うにはROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGでフレームを全体に開く必要がある。ウィンドウ関数を正しく使うとは、すなわちフレームの既定値と明示値を正確に理解するということである。

ROWSとRANGEの意味の差も設計時に必ず区別せねばならない。ROWSは整列された物理的行番号を基準に範囲を数えるため「直前の3行」のような個数基準の窓を作るが、RANGEは整列キーの値を基準にし、「現在行と同じ整列値を持つ同順位(peer)行」を一塊にまとめる。したがって同一日付が複数件あるテーブルでRANGEにより累積和を求めると、同じ日付のすべての行が同一の累積値を共有することになる。これが意図ならば正しいが、「行単位の逐次累積」を望んだならバグになる。整列キーに重複がある場合に両モードの結果が分かれる点を、テストで検証せねばならない。

3. ウィンドウ関数の種類

ウィンドウ関数は大きく順位関数、オフセット(分析)関数、集計ウィンドウ関数の三分類に分かれる。各分類はフレームに対する感度と典型的用途が異なる。下の詳細図は、整列されたパーティションの上でフレームが現在行とともに移動し集計が累積される過程を示す。

flowchart LR
    subgraph P["パーティション: 部門A (売上で整列)"]
      direction LR
      R1["行1<br/>100"] --> R2["行2<br/>150"] --> R3["行3<br/>200"] --> R4["行4<br/>250"]
    end
    R3 -. "現在行" .-> CUR["フレーム:<br/>UNBOUNDED PRECEDING ~ CURRENT ROW<br/>累積和=450"]
    style R3 fill:#ffe0b2,stroke:#e65100
    style CUR fill:#e1f5ff,stroke:#0288d1

A. 順位関数(Ranking Functions)

順位関数は整列されたパーティション内で各行に順位を付与する。ROW_NUMBER() は同順位と無関係に1, 2, 3…の一意の連番を付け、RANK() は同順位に同じ順位を与えつつ次の順位を飛ばし(1, 1, 3)、DENSE_RANK() は同順位に同じ順位を与えつつ飛ばさない(1, 1, 2)。NTILE(n) はパーティションをn個の均等なバケットに分けて分位数(四分位・十分位など)を計算し、PERCENT_RANK()・CUME_DIST() は百分位の位置と累積分布を返す。

この微妙な差は実務で決定的である。例えば「部門別売上上位3名のみ抽出」するTop-Nクエリでは、同順位処理方針によって結果の人数が変わる。ROW_NUMBERは同点者がいても正確に3名を、RANKやDENSE_RANKは3位の同点者をすべて含めて3名以上を返す。オンラインショップが「カテゴリ別ベストセラー10個」を選ぶ際、同順位の商品をすべて露出するか任意に10個で切るかが、まさにこの選択の問題である。Top-Nパターンはウィンドウ関数が相関サブクエリを置き換えた最も代表的な事例で、PARTITION BY category ORDER BY sales DESCで順位を付けた後、外部でWHERE rnk <= 10で絞る二段構造が定石である。

順位関数はまた重複排除(deduplication) の標準技法としても使われる。自然キーが重複したテーブルで「最も新しいレコードのみ残す」は、ROW_NUMBER() OVER (PARTITION BY natural_key ORDER BY updated_at DESC)で各キーグループに番号を付けた後、1番の行のみを取る方式で綺麗に解決される。データ取り込み(ingestion)パイプラインで遅れて到着した重複イベントを除外するロジックがまさにこの慣用句であり、DISTINCTやグループ集計では「どの行を代表として残すか」を制御できないのに対し、ウィンドウ関数は整列基準を通じて代表行の選択規則を明示できる点で優れている。NTILEは顧客を消費額の十分位に分けて等級を付与するセグメンテーションに、CUME_DISTは成績・給与の百分位の位置算出にそれぞれ活用される。

B. オフセット・分析関数(Offset / Value Functions)

オフセット関数は現在行を基準に前後の特定位置の値を直接参照する。LAG(x, n) はn行前の値を、LEAD(x, n) はn行後の値を取得し、セルフ結合なしに期間対比の増減分析を可能にする。例えば月次売上テーブルで売上 - LAG(売上,1) OVER (ORDER BY 月)は前月比の増減額を一行で計算する。FIRST_VALUE・LAST_VALUE・NTH_VALUE はフレーム内の特定位置の値を返し、各グループの基準値(例:部門最高給与者の名前)に対する相対比較に使われる。

実際の事例として、金融データ分析で株価の前日比収益率は(終値 - LAG(終値) OVER (PARTITION BY 銘柄 ORDER BY 日付)) / LAG(終値) OVER (...)と表現され、かつては日付を繋ぎ合わせる複雑なセルフ結合が必要だった計算が単一構文に圧縮される。ユーザー離脱分析でもLEAD(接続日) - 接続日で次の接続までの間隔を求め、休眠転換を検知するのに広く使われる。

オフセット関数は特にセッション化(sessionization) と時系列区間分割の中核ツールである。ウェブログ分析で同一ユーザーの連続イベント間の間隔をイベント時刻 - LAG(イベント時刻)で計算し、その間隔が30分を超えれば新しいセッションとして切るロジックは、ウィンドウ関数なしには書くのが至難である。こうして計算した「セッション境界フラグ」に再び累積和を適用するとセッション番号を付与でき、オフセット関数と集計ウィンドウ関数を段階的に組み合わせる分析エンジニアリングの典型的慣用句になる。数値で見ると、数千万件のログを手続き的コードでセッション化する際に数十分かかった作業が、ウィンドウ関数ベースのSQLでは分析エンジンで数十秒水準で処理される場合が報告されている。

C. 集計ウィンドウ関数(Aggregate Window Functions)

SUM、AVG、COUNT、MIN、MAXなど既存の集計関数にOVER句を付けると集計ウィンドウ関数になる。フレームをどう指定するかにより、累計(running total)、移動平均(moving average)、グループ内比率(各行の値 / パーティション合計) など多様な分析が可能である。累計売上はSUM(売上) OVER (ORDER BY 日付)、7日移動平均はAVG(売上) OVER (ORDER BY 日付 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)と表現される。

産業適用例として、小売業の日別販売ダッシュボードは当日売上・累計売上・直近7日移動平均を一画面に見せるが、この三指標はすべて同一テーブルに対する集計ウィンドウ関数一式で算出される。通信事業者のデータ使用量レポートで「今月の累計使用量が全料金プラン平均の何%か」を計算する際も、SUM(...) OVER (PARTITION BY 加入者 ...) / AVG(...) OVER ()の形で分子・分母を異なるウィンドウで指定し、単一クエリで解決する。

三分類のどれを使うかは「現在行を中心に何を知りたいか」で決まる。相対的位置(順位・分位)が知りたければ順位関数、前後の特定行の値が知りたければオフセット関数、関連行の合計・平均のような要約が知りたければ集計ウィンドウ関数を選ぶ。実際のダッシュボード一つには三分類が入り混じって使われる場合が多く、各関数のフレーム感度を理解し適切に組み合わせる設計力が分析クエリの品質を左右する。

集計ウィンドウ関数のもう一つの強みは、グループ内比率と全体比率を同時に計算できる点である。売上 / SUM(売上) OVER (PARTITION BY 部門)は部門内寄与度を、売上 / SUM(売上) OVER ()は全社寄与度を同じ行に並べて見せる。これはパレート分析(上位20%が全体の何%を占めるか)やABC在庫分類のように「個別値と累積比率を併せて見るべき」分析で特に有用である。移動平均の場合、フレーム幅を7日・28日と変えて指定すれば短期・中期の傾向線を一クエリで抽出し、ノイズを除去した傾向比較が可能で、異常検知(実測値が移動平均に対し閾値以上外れる区間の表示)の基礎指標としても活用される。

4. GROUP BY・サブクエリとの比較

ウィンドウ関数の価値を理解するには、それが置き換える従来技法との差が生じる理由を押さえねばならない。核心は「行を保持するか」と「スキャンを何回行うか」にある。

GROUP BYはグループを一つの行に畳むため、明細行と集計値を併せて見るには元表と集計結果を再び結合せねばならない。ウィンドウ関数はこの結合をなくし、一度の論理的スキャンで明細と集計を同時に返す。相関サブクエリ方式は外側クエリの各行ごとに内側集計を繰り返し実行するため、原理上O(N²)に近い非効率が発生するが、ウィンドウ関数は整列後の一度の逐次処理で終わり一般にはるかに効率的である。実測事例で、数百万行の累計を相関サブクエリで計算すると数十秒かかったものが、ウィンドウ関数では数秒以内に短縮される場合が多い。

区分 GROUP BY集計 相関サブクエリ ウィンドウ関数
行の保持 いいえ(畳む) はい はい
明細+集計の同時 追加結合が必要 可能だが繰り返し実行 単一構文
性能特性 良好 O(N²)傾向 整列後の単一パス
可読性 普通 低い(入れ子) 高い(宣言的)

ただしウィンドウ関数が常に優れているわけではない。単に「グループ別合計一行」だけ必要ならGROUP BYがより簡潔で、整列コスト(sort)の大きい大容量ではウィンドウ関数の整列段階がボトルネックになりうる。したがって「明細を保持すべきか、集計のみが必要か」という目的に応じて道具を選ぶのが実務的含意である。

もう一つ注目すべき差はNULLと空グループの処理である。GROUP BYは集計対象がなければ当該グループ自体が結果に現れないが、ウィンドウ関数はすべての明細行を保持するため、値のない区間も明示的に現れる。これは「データのない日は売上0で見せるべき」連続時系列レポーティングで重要で、ウィンドウ関数とカレンダーテーブル(calendar table)を結合すれば欠損区間まで含めた完全な傾向線を描ける。逆に相関サブクエリはこうした要求を満たそうとするほど入れ子が深くなり保守が難しくなる。結局、三技法の選択は単純な性能問題ではなく、分析意図(明細保持・連続性・代表行の選択)をSQLでどれだけ正確に表現するかの問題に帰着する。

5. 深化:性能最適化と最新動向

実務でウィンドウ関数の性能はほぼ全面的に整列コストに左右される。PARTITION BYとORDER BYの列に整列順序が一致するインデックスがあれば、DBは追加整列なしにインデックス順序をそのまま活用(インデックス戦略)しコストを大きく減らせる。逆に整列キーがインデックスと食い違うと大規模整列と一時領域(temp spill)が発生し性能が急落する。同じクエリ内で複数のウィンドウ関数が同一のOVER仕様を共有すれば、ほとんどのオプティマイザは整列を一度だけ行うため、ウィンドウ定義を統一するかWINDOW句(名前付きウィンドウ)で再利用するのが効果的である。RANGEフレームは同一整列値グループを毎回探索するためROWSフレームより高コストになりうるので、意味が同じならROWSを好む。

名前付きウィンドウ(WINDOW句)は性能だけでなく可読性にも寄与する。同一のPARTITION BY ... ORDER BY ...定義を複数の関数が繰り返し参照する際、クエリ末尾でWINDOW w AS (...)と一度だけ定義し各関数でOVER wと再利用すれば、クエリが短くなり定義変更時に一箇所だけ直せばよい。大規模な分析クエリほどこの慣用句が保守性を大きく高める。

最新動向の面で、分析特化のカラム型エンジン(ClickHouse、DuckDB、Apache Spark、Snowflake、BigQueryなど)はウィンドウ関数をベクトル化・並列実行で加速し、数十億行規模でもリアルタイム分析を提供する。SQL標準も進化しGROUPSフレームモードやフレーム除外(EXCLUDE)オプションなどが追加され、PostgreSQL 11以上がこれに対応する。またウィンドウ関数はデータウェアハウスのELTパターンで変換ロジックをSQLへ下ろす中核手段となり、dbtのような分析エンジニアリングツールのモデルで累積・順位・セッション化ロジックを表現する標準慣用句として定着した。ストリーム処理(例:Apache Flink)でも時間ウィンドウベースの分析関数がバッチSQLのウィンドウ概念を受け継ぎリアルタイム集計を担う。

ただしストリーミング環境のウィンドウはバッチSQLのウィンドウ関数と意味が微妙に異なる点に留意せねばならない。バッチウィンドウ関数は既に確定した有限の結果集合の上で整列・フレームを適用するが、ストリーミングの時間ウィンドウ(タンブリング・スライディング・セッションウィンドウ)は果てしなく流れる無限ストリームを時間軸で切って集計し、ウォーターマーク(watermark)で遅延到着データを扱う。二つの概念は同じ「ウィンドウ」という用語を共有するため混同しやすいが、前者が分析的クエリの表現力の問題なら、後者は状態管理と時間意味論(event-time vs processing-time)の問題という点でアプローチが異なる。技術士の答案でこの区別を押さえれば概念の深さを示せる。

6. 考慮事項および示唆点

技術士の観点で、ウィンドウ関数の導入と活用は次を総合的に考慮せねばならない。

  • 適用戦略(ロジックのDB内在化):順位・累積・セッション化のような分析ロジックをアプリケーションコードからDBのウィンドウ関数へ下ろせば、データ移動量が減り宣言的表現で保守性が向上する。ただし過度なビジネスロジックのDB集中はDBMS依存と拡張限界を生みうるため、集計・分析的性格のロジックに限定する境界設定が必要である。

  • 性能トレードオフ(整列コスト対表現力):ウィンドウ関数は表現力が高いが整列・フレーム評価のコストを伴う。パーティション・整列キーに整合したインデックス設計、同一ウィンドウの共有、ROWSフレームの選好、大容量時のカラム型・分析エンジンの活用などでコストを管理せねばならず、「単純集計ならGROUP BY、明細保持が必要ならウィンドウ関数」という選択基準を立てねばならない。

  • 正確性と落とし穴の管理:既定フレームによるLAST_VALUEの誤り、WHEREでウィンドウ結果を直接参照できない制約、同順位処理方針(RANK vs ROW_NUMBER)による結果の差などは、設計段階で明示的に検証すべき落とし穴である。テストケースに同順位・境界・NULL整列(NULLS FIRST/LAST)の状況を必ず含める。

  • 標準化と移植性:ウィンドウ関数はSQL標準に基づくが、対応範囲と構文(特にフレームオプション)がDBMSごとに異なる。マルチクラウド・異種DB環境では標準の共通部分集合を使うか、dbtなどの抽象化層で方言差を吸収する戦略が求められる。

  • ガバナンスと再現性:順位・セグメント・比率のような指標がウィンドウ関数で定義されるほど、同順位処理・フレーム・整列規則の微細な差が指標の数値を変える。したがって中核指標のウィンドウ定義をメトリック層(semantic layer)に一度だけ定義して全社で共有し、定義変更を構成管理で追跡するガバナンスが、数値の再現性と信頼を保証する鍵になる。

  • 連携技術の展望:ウィンドウ関数はデータレイクハウス・リアルタイム分析・分析エンジニアリング(ELT)の中核演算で、カラム型格納・ベクトル化実行・ストリーミングSQLと結合しその重要性が増している。今後の生成AIベースの自然言語-SQL変換でも順位・傾向クエリが最も頻繁にウィンドウ関数へ帰着するため、分析クエリ設計力の基礎として価値が持続する。

想定出題方向としては、①GROUP BYとウィンドウ関数の差と選択基準、②OVER句の三要素(パーティション・整列・フレーム)の役割と既定フレームの落とし穴、③順位関数3種(ROW_NUMBER/RANK/DENSE_RANK)の差とTop-N・重複排除の活用、④累計・移動平均など集計ウィンドウの実務クエリ作成、⑤整列コストとインデックスによる性能最適化がよく扱われる。答案構成時には「なぜ明細保持型集計が必要か」という背景から出発し、OVER句の動作原理と具体的クエリ・数値事例で深さを加えた後、従来技法との比較・性能・最新動向で締める流れが説得力がある。

参考資料


一言まとめ: ウィンドウ関数はOVER句のパーティション・整列・フレームで現在行と関連する行集合を指定し、明細データを保持したまま順位・累積・移動平均を計算するSQL分析の中核ツールで、セルフ結合・相関サブクエリを置き換え表現力と性能を同時に高める。