window関数は、データベースのテーブル内の行に関連する情報を取得するための特別な種類のSQL関数です。
名前に「window」と付いているのは、この関数が「視点（ウインドウ）」を移動させながら、その「ウインドウ」内のデータに基づいて計算を行うからです。
具体的には、ある行を中心に、その前後の行を「window」として捉え、その範囲内のデータを使って計算を行うイメージ。例えば、4日間の平均売上を求めたいとき、1日目、2日目、3日目、4日目の売上をウインドウ（＝計算範囲）として捉え、その平均を計算する、というようなことがwindow関数で可能です。
このページではwindow関数の基本を１からわかりやすく初心者向けに解説します。
プログラマーやシステムエンジニアを目指す方であれば知らないと恥ずかしい基本知識です。是非最後までご覧ください。
window関数とは？
window関数は、SQLの強力な機能の1つで、各行に対して、ある「window」と呼ばれる範囲内の行を基に計算を行う関数です。これにより、各行の相対的な情報、たとえば「この行の前の行の値は何か？」や「このグループ内でのこの行のランクは？」などを求めることができます。
window関数は通常の集計関数と異なり、結果セットの行数を変えずに各行に対して情報を提供します。
window関数の構文ルール
window関数を使用する際の基本的な構文は以下のようになります。
<関数名>(<式>) OVER (
PARTITION BY <分割列>
ORDER BY <並び替え列>
<フレーム範囲指定>
)
<関数名>(<式>):- 使用するwindow関数の名前と、その関数に渡す引数または式。
- OVER はwindow関数を利用しますよ！という宣言・印だと理解すればOK。
PARTITION BY <分割列>:PARTITION BYオプションを使って、データを特定の列の値に基づいてグループに分けることができる。→これにより、各グループ内でwindow関数が適用される。- GROUP BY と同じようなイメージでOK。
ORDER BY <並び替え列>:- ウインドウ内のデータの順序を定義します。この順序は、ランキング関数や前後の行との比較を行う関数など、関数の動作に影響を与えます。
<フレーム範囲指定>:- window関数がどの範囲のデータを考慮して計算を行うかを指定します。例えば「現在の行から2行前まで」や「現在の行から次の行まで」といった具体的な範囲を定義できます。指定しない場合は、PARTITION全体が考慮されるのがデフォルトです。
と、文字だけではイメージしづらいかと思いますので、具体例を用いながらご説明します。
サンプル studentsテーブルの例
| id | name | score | |----|---------|-------| | 1 | Alice | 85 | | 2 | Bob | 90 | | 3 | Charlie | 85 | | 4 | David | 92 | | 5 | Eve | 88 | |----|---------|-------|
↑のテーブルを例に説明します。このテーブルは5人の生徒のテストのスコアが記録されています。
ここでは代表的なwindow関数の１つ、ROW_NUMBER()を使用してスコアの高い順にランキングをつけたいと考えてみます。その例が↓。
SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM students; /* 結果 |---------|-------|------| | name | score | rank | |---------|-------|------| | David | 92 | 1 | | Bob | 90 | 2 | | Eve | 88 | 3 | | Alice | 85 | 4 | | Charlie | 85 | 5 | |---------|-------|------| */
参考 SELECT文の基本
ROW_NUMBER()関数は、指定されたORDER BYの順序に従って、各行にユニークな連番を割り当てる。OVER (ORDER BY score DESC)部分は、スコアを降順に並べ替えて、その順番に従って連番を付けることを指示。- 結果、最もスコアが高いDavidが1位、次に高いBobが2位というようにランキングが付けられる。AliceとCharlieは同じスコアですが、
ROW_NUMBER()はユニークな値を割り当てるため、異なるランクが付けられています。
この基本的な動作を理解することで、他のウインドウ関数もより理解しやすくなるでしょう。
PARTITION BY を使うことで、window関数を特定のカテゴリやグループごとに適用することができます。先ほどの、students テーブルに class 列を追加し、その上で ROW_NUMBER() を使用してクラスごとに順番をつけてみましょう。
↓のテーブルを利用して、クラスごとにスコアの高い順にランキングをつけてみます。
/* 利用するテーブル |----|---------|-------|-------| | id | name | score | class | |----|---------|-------|-------| | 1 | Alice | 85 | A | | 2 | Bob | 90 | B | | 3 | Charlie | 85 | A | | 4 | David | 92 | B | | 5 | Eve | 88 | A | |----|---------|-------|-------| */ -- クエリ SELECT name, score, class, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rank_in_class FROM students ORDER BY class, rank_in_class; /* 結果 | name | score | class | rank_in_class | |---------|-------|-------|---------------| | Eve | 88 | A | 1 | | Alice | 85 | A | 2 | | Charlie | 85 | A | 3 | | David | 92 | B | 1 | | Bob | 90 | B | 2 | |---------|-------|-------|---------------| */
解説
PARTITION BY classによって、データはクラスごとに分けられる→結果、各クラス内で独立したランキングが生成される。ORDER BY score DESCにより、各クラス内でのランキングはスコアの降順にソートされる。
→クラスAではEveが1位、クラスBではDavidが1位に。
このように、PARTITION BY を使うことで、ウインドウ関数を特定のカテゴリやグループごとに適用することができます。
フレーム範囲指定は、window関数を適用する際の対象範囲をより細かく制御するためのものです。通常、window関数は特定の行に基づいて、その行を中心に前後の範囲で計算を行いますが、フレーム範囲指定を使うことでその計算範囲を具体的に定義することができます。
<関数名>(<式>) OVER (
PARTITION BY <分割列>
ORDER BY <並び替え列>
ROWS <範囲指定>
)
ROWS の代わりに RANGE も利用できますが、その動作は異なります。ここでは、まず ROWS を用いたフレーム範囲指定について詳しく説明します。
以下は、フレーム範囲指定の一般的な使用方法の例です。
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING:
- 現在の行を中心に、1行前と1行後を含む範囲で関数を適用します。
- ROWS UNBOUNDED PRECEDING:
- 現在の行から、最初の行までの範囲で関数を適用します。
- ROWS 2 FOLLOWING:
- 現在の行から、2行後までの範囲で関数を適用します。
サンプル 3日移動平均の計算
/* 利用するsalesテーブル
|------------|--------------|
| date | sales_amount |
|------------|--------------|
| 2023-01-01 | 100 |
| 2023-01-02 | 110 |
| 2023-01-03 | 90 |
| 2023-01-04 | 95 |
| 2023-01-05 | 105 |
|------------|--------------|
*/
-- 各日の売上の3日移動平均を計算するためのSQLクエリ
SELECT
date,
sales_amount,
AVG(sales_amount) OVER (
ORDER BY date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg_3days
FROM
sales;
/* 結果
|------------|--------------|------------------|
| date | sales_amount | moving_avg_3days |
|------------|--------------|------------------|
| 2023-01-01 | 100 | 105 |
| 2023-01-02 | 110 | 100 |
| 2023-01-03 | 90 | 98.33 |
| 2023-01-04 | 95 | 96.67 |
| 2023-01-05 | 105 | 100 |
|------------|--------------|------------------|
*/
解説
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWINGによって、各日の売上の1日前、当日、1日後の3日間のデータを対象として平均を計算。
フレーム範囲指定を利用することで、累積計算や移動平均、前後のデータとの差分計算など、さまざまな集計処理を行うことができます。
よく使われるwindow関数の紹介
window関数は非常に多岐にわたるため、ここでは特によく使われるwindow関数をいくつか使い方を簡潔に紹介します。
1. ROW_NUMBER()
- 説明: 各行に一意の連続する番号を割り当てます。主にランキングや行番号付けに使用されます。
- 例: 各商品の売上順にランキングをつける場合
ROW_NUMBER() OVER (ORDER BY sales_amount DESC)
さらに詳しく→ ROW_NUMBER関数
2. RANK()
- 説明: 各行にランキングを割り当てますが、同じ値を持つ行には同じランクを割り当て、次のランクはスキップされます。
- 例: 各学生のテストスコアのランキングを付ける場合
RANK() OVER (ORDER BY score DESC)
3. DENSE_RANK()
- 説明:
RANK()と同様にランキングを割り当てますが、同じ値を持つ行には同じランクを割り当て、次のランクはスキップされません。 - 例: スポーツのポイントランキングで、同点者がいても次の順位をスキップせずに付ける場合
DENSE_RANK() OVER (ORDER BY points DESC)
4. NTILE(n)
- 説明: データを指定した
nの数だけの等しいサイズのバケットに分割します。 - 例: 学生を4つのグループに分ける場合
NTILE(4) OVER (ORDER BY score)
5. LAG()
- 説明: 指定したカラムの前の行の値を取得します。
- 例: 各月の売上と前月の売上の差分を計算する場合
sales_amount - LAG(sales_amount) OVER (ORDER BY month)
6. LEAD()
- 説明: 指定したカラムの次の行の値を取得します。
- 例: 各月の売上と次月の売上の差分を計算する場合
sales_amount - LEAD(sales_amount) OVER (ORDER BY month)
7. FIRST_VALUE(), LAST_VALUE()
- 説明: ウィンドウの最初の値や最後の値を取得します。
- 例: 各月の売上とその四半期の最初の月の売上を比較する場合
sales_amount - FIRST_VALUE(sales_amount) OVER (ORDER BY month)
これらの関数は、ビジネスやデータ分析の現場で頻繁に使われます。適切な関数を選ぶことで、複雑な集計や解析を効率的に行うことができます。
まとめ：window関数とは？
現在のスキルに今一つ満足できていないシステムエンジニア/プログラマーの方へ。
SQLやデータベースの仕組みを１から学習したい方（学び直したい方）向けに、現役エンジニア達のスキルを結集して 完全無料 のSQL教材を作成しました。是非この機会にブックマークを！
読者料典 【完全無料】SQL：初心者向け学習ロードマップ ←こちらから！