// シス担のミカタ
SQL・クエリ

ウィンドウ関数 うぃんどうかんすう

OVER句PARTITION BYRANKROW_NUMBER移動平均分析関数
ウィンドウ関数について教えて

簡単に言うとこんな感じ！

ウィンドウ関数は「グループ集計しながら元の行も残す」超便利なSQL機能だよ！普通のGROUP BYは集計するとグループ数だけに行が圧縮されるけど、ウィンドウ関数は「各行に順位・累計・移動平均を付けながら全行を保持」できるんだ。「売上ランキング付きの商品一覧」とか「月次累計」がSQLだけで書けるんだ！

ウィンドウ関数とは

ウィンドウ関数（Window Function）とは、SQL:2003で標準化された分析関数で、OVER句を使って「ウィンドウ（集計対象の行の範囲）」を定義し、各行に対して集計・順位付け・移動計算などを行う機能です。「分析関数」「OLAP関数」とも呼ばれます。

通常のGROUP BYによる集計は結果行数が減りますが、ウィンドウ関数は元のすべての行を保持しながら各行に集計値を付加します。これにより「全商品の一覧を表示しつつ、各行に平均価格との差分を表示する」ような処理が、サブクエリなしで1つのSQLで記述できます。

ウィンドウ関数はPARTITION BY（グループの定義）とORDER BY（並び順の定義）、そしてフレーム句（対象範囲の定義）の3要素で「ウィンドウ」を構成します。たとえばPARTITION BY category ORDER BY priceとすれば「カテゴリ別・価格順での処理」という窓を定義できます。

主なウィンドウ関数

関数種類説明使用例
ROW_NUMBER()順位付け重複なし連番各グループ内の連番
RANK()順位付け同順位あり・次番号スキップ売上ランキング
DENSE_RANK()順位付け同順位あり・連続番号密度ランキング
LAG(col, n)オフセットn行前の値を取得前月比・前日比
LEAD(col, n)オフセットn行後の値を取得翌月予測との比較
SUM() OVER集計累計・グループ合計月次累計売上
AVG() OVER集計移動平均7日間移動平均
FIRST_VALUE()値取得ウィンドウ先頭の値グループ内の最初の値
LAST_VALUE()値取得ウィンドウ末尾の値グループ内の最後の値
ウィンドウ関数 vs GROUP BY の動作の違い GROUP BY（行が圧縮される） SELECT category, SUM(price) FROM products GROUP BY category; category SUM(price) 家電 85000 衣類 9800 元の商品名・価格が消える！ グループの集計値しか残らない ウィンドウ関数（全行を保持） SELECT name, category, price, RANK() OVER ( PARTITION BY category ORDER BY price DESC ) AS rank_in_cat FROM products; name category price rank テレビ 家電 55000 1 掃除機 家電 30000 2 シャツ 衣類 5800 1 全行を保持しながら順位を付加！ PARTITION BYでカテゴリ別に順位を付ける

歴史と背景

  • 1970年代：分析関数の概念は統計学・OLAPの文脈で存在
  • 1999年：SQL:1999でウィンドウ関数が標準として議論開始
  • 2003年：SQL:2003で正式に標準化（OVER句・PARTITION BY・フレーム句）
  • 2005年頃：Oracle・PostgreSQL・SQL Serverが実装。普及が始まる
  • 2012年：PostgreSQL 8.4がウィンドウ関数に対応
  • 2018年：MySQL 8.0がウィンドウ関数に対応（長らく未対応だった）
  • 現在：BIツール・dbtでのSQL変換でウィンドウ関数が多用される定番機能に

OVER句の構文

関数名() OVER (
  PARTITION BY グループ列  -- グループ定義（省略可）
  ORDER BY 並び順列        -- 順序定義（省略可）
  ROWS/RANGE BETWEEN ...  -- フレーム定義（省略可）
)

関連する規格・RFC

規格・RFC番号内容
ISO/IEC 9075:2003ウィンドウ関数（OVER句）の標準化
ISO/IEC 9075:2011ウィンドウ関数のフレーム句拡張

関連用語