🎯 Step 8: フレーム指定と範囲指定

ウィンドウ関数を自在に操ろう!

📋 このステップで学ぶこと

フレーム（Frame）とは何か
ROWS BETWEENの詳細な使い方
RANGE BETWEENとの違い
移動平均の計算（3ヶ月、6ヶ月、12ヶ月）
前後の行を含む平均（中心移動平均）
実務での活用パターン

🎯 1. フレームとは?

1-1. ウィンドウ関数が見る「範囲」

Step 7で学んだように、ウィンドウ関数は「ここまでの合計」や「過去3ヶ月の平均」を計算できます。この「どの範囲のデータを見るか」を指定する仕組みをフレーム（Frame）と呼びます。

💡 フレームのイメージ（窓から覗くように）

データの列を想像してください。各行を処理するとき、「窓」のサイズと位置を指定して、その中のデータだけを見ることができます。

行	月	売上	窓の範囲（3行）
1	1月	100万
2	2月	120万
3	3月	150万	← ここを処理中
4	4月	180万
5	5月	160万

3月を処理中の場合、「2行前から現在まで」のフレームなら、1月・2月・3月の3行が対象になります。

1-2. なぜフレームが必要なのか

フレームを指定することで、様々な計算ができるようになります。

フレーム指定でできること:

累積合計：最初から現在までの合計
移動平均：過去N期間の平均
前後平均：前後を含む平均（データを滑らかに）
残り合計：現在から最後までの合計
期間内の最大・最小：過去30日の最大売上など

1-3. フレーム指定の2つの方法

フレームを指定する方法には、ROWSとRANGEの2種類があります。

📌 ROWSとRANGEの違い

方法	指定方法	特徴
ROWS	物理的な行数で指定	シンプルで分かりやすい（★おすすめ）
RANGE	値の範囲で指定	同じ値がある場合の挙動が複雑

迷ったらROWSを使いましょう! 実務でもROWSを使うことがほとんどです。

📏 2. ROWS BETWEENの基本

2-1. ROWS BETWEENの構文

📌 ROWS BETWEENの基本構文

※横にスクロールできます

関数() OVER (
    ORDER BY 列名
    ROWS BETWEEN 開始位置 AND 終了位置
)

2-2. 位置の指定方法

フレームの開始位置と終了位置は、以下のキーワードで指定します。

位置を指定するキーワード:

キーワード	意味	例
`UNBOUNDED PRECEDING`	最初の行から	累積合計の開始
`N PRECEDING`	N行前から	2 PRECEDING = 2行前
`CURRENT ROW`	現在の行	今処理中の行
`N FOLLOWING`	N行後まで	1 FOLLOWING = 1行後
`UNBOUNDED FOLLOWING`	最後の行まで	残り全部

2-3. フレームの視覚的な理解

「2 PRECEDING AND CURRENT ROW」の場合を視覚的に見てみましょう。

📌 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW の意味

現在の行が「4月」の場合:

行	月	売上	フレーム内?
1	1月	100万
2	2月	120万	← 2行前（PRECEDING）
3	3月	150万	← 1行前
4	4月	180万	← 現在の行（CURRENT ROW）
5	5月	160万

4月を処理中の場合、2月・3月・4月の3行が対象になります。
→ 3ヶ月移動平均 = (120 + 150 + 180) ÷ 3 = 150万

2-4. 例1：3行移動平均（過去3行）

やりたいこと：商品を価格順に並べて、現在を含む過去3行の平均価格を計算する

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM products
ORDER BY price
LIMIT 10;

クエリの解説:

AVG(price) → 価格の平均を計算
ORDER BY price → 価格の安い順に並べる
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 2行前から現在まで（計3行）

なぜ「2 PRECEDING」なのか:

3行移動平均なので、「2行前 + 1行前 + 現在」= 3行です。「欲しい行数 – 1」がPRECEDINGの数になります。

結果イメージ:

product_name	price	moving_avg_3
もやし	50	50（1行分）
にんじん	80	65（2行分）
キャベツ	100	76.67（3行分）
りんご	150	110（3行分）
バナナ	200	150（3行分）

最初の数行は対象行数が少ないため、1行分、2行分の平均になります。

2-5. 例2：前後1行を含む平均（中心移動平均）

やりたいこと：前後1行ずつを含む3行の平均を計算する

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS centered_avg
FROM products
ORDER BY price;

クエリの解説:

1 PRECEDING → 1行前から
1 FOLLOWING → 1行後まで
結果: 前1行 + 現在 + 後1行 = 計3行

違いに注目:

「2 PRECEDING AND CURRENT ROW」は過去3行、「1 PRECEDING AND 1 FOLLOWING」は前後1行ずつ + 現在です。同じ3行でも、対象が異なります。

2-6. 例3：累積合計（最初から現在まで）

やりたいこと：最初から現在までの累積合計を計算する

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM products
ORDER BY price;

クエリの解説:

UNBOUNDED PRECEDING → 最初の行から（制限なし）
CURRENT ROW → 現在の行まで
結果: 最初から現在までの全ての行が対象

補足:

実は、ORDER BYだけ指定してROWS BETWEENを省略した場合も、デフォルトで累積計算になります。ただし、明示的に書く方がコードが分かりやすくなります。

よく使うフレーム指定パターン:

パターン	意味	用途
2 PRECEDING AND CURRENT ROW	2行前から現在（3行）	3行移動平均
1 PRECEDING AND 1 FOLLOWING	前後1行ずつ（3行）	中心移動平均
UNBOUNDED PRECEDING AND CURRENT ROW	最初から現在	累積合計
CURRENT ROW AND UNBOUNDED FOLLOWING	現在から最後	残り合計

📊 3. RANGE BETWEEN – 値の範囲で指定

3-1. ROWSとRANGEの違い

ROWSは物理的な行数で指定しますが、RANGEは値の範囲で指定します。この違いが最も顕著になるのは、同じ値がある場合です。

テストデータ（同じ価格の商品がある場合）:

product_name	price
商品A	100
商品B	100（← 同じ価格）
商品C	100（← 同じ価格）
商品D	200

3-2. ROWSの場合（物理的な行数）

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS rows_total
FROM products;

ROWSの結果:

product_name	price	rows_total
商品A	100	100（1行目まで）
商品B	100	200（2行目まで）
商品C	100	300（3行目まで）
商品D	200	500（4行目まで）

ROWSは物理的な行数なので、同じ価格でも1行ずつ累積されます。

3-3. RANGEの場合（値の範囲）

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS range_total
FROM products;

RANGEの結果:

product_name	price	range_total
商品A	100	300（価格100の全て）
商品B	100	300（価格100の全て）
商品C	100	300（価格100の全て）
商品D	200	500

RANGEは値の範囲なので、同じ価格の行はまとめて処理されます。商品A、B、Cは全て同じ結果（300）になります。

📌 ROWSとRANGEの違いまとめ

項目	ROWS	RANGE
指定方法	物理的な行数	値の範囲
同じ値の扱い	1行ずつ別々に処理	まとめて処理
動作の予測	分かりやすい	複雑
おすすめ	★ こちらを使う	特殊なケースのみ

⚠️ RANGEの注意点

RANGEは同じ値がある場合の挙動が複雑で、データベースによっても動作が異なることがあります。特別な理由がない限り、ROWSを使いましょう。

📅 4. 実践: 日付範囲での集計

4-1. 月次売上の3ヶ月移動平均

やりたいこと：月ごとの売上と、過去3ヶ月の移動平均を計算する

Step 7で学んだ移動平均を、ROWS BETWEENを明示的に使って書いてみましょう。

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

strftime('%Y-%m', order_date) → 日付を「年-月」形式に変換（SQLiteの場合）
SUM(total_amount) → 月ごとの売上合計（GROUP BY）
AVG(SUM(...)) → GROUP BY結果に対するウィンドウ関数
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 過去3ヶ月

結果イメージ:

year_month	monthly_sales	moving_avg_3m
2024-01	1,000,000	1,000,000（1ヶ月分）
2024-02	1,200,000	1,100,000（2ヶ月分）
2024-03	1,500,000	1,233,333（3ヶ月分）
2024-04	1,800,000	1,500,000（3ヶ月分）

4-2. 前後を含む3ヶ月平均（中心移動平均）

やりたいこと：前月と翌月を含む3ヶ月平均を計算する（データを滑らかにする）

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS centered_avg_3m
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

1 PRECEDING → 1行前（前月）
1 FOLLOWING → 1行後（翌月）
結果: 前月 + 当月 + 翌月 = 3ヶ月

過去3ヶ月との違い:

「2 PRECEDING AND CURRENT ROW」は過去3ヶ月（1月→1月だけ、2月→1-2月、3月→1-3月…）。
「1 PRECEDING AND 1 FOLLOWING」は前後1ヶ月を含む（2月→1-3月、3月→2-4月…）。
用途によって使い分けます。

結果イメージ:

year_month	monthly_sales	centered_avg_3m
2024-01	1,000,000	1,100,000（1月+2月の平均）
2024-02	1,200,000	1,233,333（1月+2月+3月）
2024-03	1,500,000	1,500,000（2月+3月+4月）
2024-04	1,800,000	1,633,333（3月+4月+5月）

最初の行は「前月」がないので2ヶ月分、最後の行は「翌月」がないので2ヶ月分の平均になります。

4-3. 日数の計算ルール

日次データで「直近N日」を計算したい場合、PRECEDINGの数は「N – 1」になります。

📌 日数とPRECEDINGの関係

欲しい日数	PRECEDING	考え方
直近7日	6 PRECEDING	6日前 + … + 今日 = 7日
直近30日	29 PRECEDING	29日前 + … + 今日 = 30日
直近90日	89 PRECEDING	89日前 + … + 今日 = 90日

公式: N日分 → (N – 1) PRECEDING AND CURRENT ROW

4-4. 週次売上の4週移動平均

やりたいこと：週ごとの売上と、過去4週の移動平均を計算する

※横にスクロールできます

SELECT 
    strftime(‘%Y-W%W’, order_date) AS week,
    SUM(total_amount) AS weekly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-W%W’, order_date)
        ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
    ) AS moving_avg_4w
FROM orders
GROUP BY strftime(‘%Y-W%W’, order_date)
ORDER BY week DESC
LIMIT 12;

クエリの解説:

strftime('%Y-W%W', order_date) → 日付を「年-W週番号」形式に変換
3 PRECEDING → 3週前から（4週分）
ORDER BY week DESC → 最新の週から表示

活用シーン:

売上トレンド分析：週次・月次の移動平均で傾向を把握
季節変動の除去：12ヶ月移動平均で季節要因を取り除く
異常検知：移動平均から大きく外れた週を検出
予算策定：過去のトレンドから来期の予算を策定

📈 5. 高度なフレーム指定

5-1. 逆累積合計（現在から最後まで）

やりたいこと：現在の行から最後の行までの合計を計算する（残り合計）

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS remaining_total
FROM products
ORDER BY price;

クエリの解説:

CURRENT ROW → 現在の行から
UNBOUNDED FOLLOWING → 最後の行まで
結果: 現在を含む残りの全ての行が対象

結果イメージ:

product_name	price	remaining_total
もやし	50	500（全商品）
にんじん	80	450（にんじん以降）
キャベツ	100	370（キャベツ以降）
りんご	150	270（りんご以降）

行が進むにつれて残り合計が減っていきます。

活用シーン:

予算残高：年度末までに残っている予算
在庫予測：今後必要な在庫量の計算
タスク管理：残りの作業量の把握

5-2. 前後非対称の範囲

やりたいこと：前3行と後1行を含む5行の平均を計算する

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
    ) AS asymmetric_avg
FROM products
ORDER BY price;

クエリの解説:

3 PRECEDING → 3行前から
1 FOLLOWING → 1行後まで
結果: 3行前 + 2行前 + 1行前 + 現在 + 1行後 = 最大5行

なぜ非対称?:

過去のデータを重視したい場合に使います。例えば、「過去のトレンドを重視しつつ、少し未来も考慮する」ような分析に使えます。

5-3. カテゴリごとの移動平均（PARTITION BY + ROWS BETWEEN）

やりたいこと：カテゴリごとに価格順で3行移動平均を計算する

※横にスクロールできます

SELECT 
    product_name,
    category,
    price,
    AVG(price) OVER (
        PARTITION BY category
        ORDER BY price
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS category_moving_avg
FROM products
ORDER BY category, price;

クエリの解説:

PARTITION BY category → カテゴリごとにグループ分け
ORDER BY price → 各カテゴリ内で価格順
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 各カテゴリ内で3行移動平均

ポイント:

カテゴリが変わるとフレームがリセットされます。果物カテゴリの最初の商品は、野菜カテゴリのデータを参照しません。

結果イメージ:

product_name	category	price	category_moving_avg
バナナ	果物	120	120（1行分）
りんご	果物	150	135（2行分）
ぶどう	果物	500	256.67（3行分）
にんじん	野菜	80	80（リセット!）
キャベツ	野菜	150	115（2行分）

5-4. 短期・中期・長期の移動平均を同時に計算

やりたいこと：月次売上に対して、3ヶ月・6ヶ月・12ヶ月の移動平均を同時に計算する

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3m,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS ma_6m,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS ma_12m
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

2 PRECEDING → 3ヶ月移動平均（短期）
5 PRECEDING → 6ヶ月移動平均（中期）
11 PRECEDING → 12ヶ月移動平均（長期）

💡 複数の移動平均を使う理由

短期（3ヶ月）：最近のトレンドを素早く把握
中期（6ヶ月）：季節変動をある程度考慮
長期（12ヶ月）：季節要因を取り除いた長期的な傾向

株価分析の「ゴールデンクロス」（短期線が長期線を上抜く）のような分析に使えます。

💼 6. 実務での活用例

6-1. 在庫推移の分析

やりたいこと：商品ごとの在庫変動を、7日移動平均と30日間の最大・最小で分析する

※横にスクロールできます

SELECT 
    product_id,
    check_date,
    stock,
    — 7日移動平均
    AVG(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d,
    — 30日間の最大在庫
    MAX(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS max_30d,
    — 30日間の最小在庫
    MIN(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS min_30d
FROM stock_history
ORDER BY product_id, check_date DESC;

クエリの解説:

PARTITION BY product_id → 商品ごとに計算
6 PRECEDING → 7日分（6日前から今日まで）
29 PRECEDING → 30日分（29日前から今日まで）
MAX/MINで期間内の最大・最小を取得

活用シーン:

在庫アラート：30日最小が基準を下回った商品を検出
需要予測：移動平均のトレンドから発注量を決定
異常検知：急激な在庫変動を検出

6-2. 顧客の購買行動分析

やりたいこと：顧客ごとの注文履歴に、累積購入額と直近3回の平均購入額を表示する

※横にスクロールできます

SELECT 
    c.customer_name,
    o.order_date,
    o.total_amount,
    — 累積購入額
    SUM(o.total_amount) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    — 直近3回の平均購入額
    AVG(o.total_amount) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_last_3
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name, o.order_date;

クエリの解説:

PARTITION BY c.customer_id → 顧客ごとに計算
UNBOUNDED PRECEDING → 最初の注文から累積
2 PRECEDING → 直近3回の注文

活用シーン:

顧客ランク判定：累積購入額でランクを決定
購買傾向の変化：直近3回の平均が下がっている顧客を抽出
優良顧客の発見：累積購入額の上昇が著しい顧客

6-3. アクセスログの分析

やりたいこと：時間帯別のアクセス数と、24時間移動平均・週間最大アクセスを計算する

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m-%d %H:00’, access_time) AS hour,
    COUNT(*) AS access_count,
    — 24時間移動平均
    AVG(COUNT(*)) OVER (
        ORDER BY strftime(‘%Y-%m-%d %H:00’, access_time)
        ROWS BETWEEN 23 PRECEDING AND CURRENT ROW
    ) AS ma_24h,
    — 週間（168時間）最大アクセス
    MAX(COUNT(*)) OVER (
        ORDER BY strftime(‘%Y-%m-%d %H:00’, access_time)
        ROWS BETWEEN 167 PRECEDING AND CURRENT ROW
    ) AS max_7d
FROM access_logs
GROUP BY strftime(‘%Y-%m-%d %H:00’, access_time)
ORDER BY hour DESC
LIMIT 48;

クエリの解説:

strftime('%Y-%m-%d %H:00', access_time) → 時間単位で集計
23 PRECEDING → 24時間分（23時間前から現在まで）
167 PRECEDING → 168時間 = 1週間分

活用シーン:

サーバー負荷予測：過去のパターンから負荷を予測
ピーク時間の特定：週間最大の時間帯を把握
異常検知：移動平均から大きく外れたアクセス急増を検出

📝 練習問題

フレーム指定をマスターしましょう!

問題 1基本

3行移動平均

商品を価格順に並べて、現在を含む過去3行の価格の平均を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM products
ORDER BY price;

解説:

2 PRECEDING → 2行前から（2行前 + 1行前 + 現在 = 3行）
3行移動平均なので「3 – 1 = 2」がPRECEDINGの数

問題 2基本

前後含む平均

商品を価格順に並べて、前後1行ずつを含む3行の平均価格を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS centered_avg
FROM products
ORDER BY price;

解説:

1 PRECEDING → 1行前から
1 FOLLOWING → 1行後まで
前1行 + 現在 + 後1行 = 3行の中心移動平均

問題 3基本

累積合計（明示的）

商品を価格順に並べて、最初から現在までの累積合計を計算してください。ROWS BETWEENを明示的に使ってください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM products
ORDER BY price;

解説:

UNBOUNDED PRECEDING → 最初の行から（制限なし）
CURRENT ROW → 現在の行まで
これがデフォルトの累積計算と同じ動作

問題 4応用

5行移動平均

商品を価格順に並べて、現在を含む過去5行の移動平均を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) AS moving_avg_5
FROM products
ORDER BY price;

解説:

5行移動平均なので「5 – 1 = 4」がPRECEDINGの数
4行前 + 3行前 + 2行前 + 1行前 + 現在 = 5行

問題 5応用

逆累積合計

商品を価格順に並べて、現在から最後までの合計（残り合計）を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    SUM(price) OVER (
        ORDER BY price
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS remaining_total
FROM products
ORDER BY price;

解説:

CURRENT ROW → 現在の行から
UNBOUNDED FOLLOWING → 最後の行まで
行が進むにつれて残り合計が減っていく

問題 6応用

前後2行の最大値

商品を価格順に並べて、前後2行ずつを含む5行の中での最大価格を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    MAX(price) OVER (
        ORDER BY price
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS max_in_5
FROM products
ORDER BY price;

解説:

2 PRECEDING → 2行前から
2 FOLLOWING → 2行後まで
2行前 + 1行前 + 現在 + 1行後 + 2行後 = 5行

問題 7応用

カテゴリごとの移動平均

カテゴリごとに、価格順で3行移動平均を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    category,
    price,
    AVG(price) OVER (
        PARTITION BY category
        ORDER BY price
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS category_moving_avg
FROM products
ORDER BY category, price;

解説:

PARTITION BY category でカテゴリごとにグループ分け
カテゴリが変わるとフレームがリセットされる

問題 8チャレンジ

月次売上の3ヶ月移動平均

月ごとの売上と、過去3ヶ月の移動平均を計算してください。

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

解説:

GROUP BYで月次集計してから、ROWS BETWEENで移動平均を計算
AVG(SUM(...)) → GROUP BY結果に対するウィンドウ関数

問題 9チャレンジ

短期・長期移動平均

月次売上に対して、3ヶ月移動平均と12ヶ月移動平均を同時に計算してください。

※横にスクロールできます

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS ma_3m,
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
    ) AS ma_12m
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

解説:

複数のウィンドウ関数を同時に使用
3ヶ月 → 2 PRECEDING、12ヶ月 → 11 PRECEDING

問題 10チャレンジ

顧客別累積と移動平均

顧客ごとの注文履歴に、累積購入額と直近3回の平均購入額を表示してください。

※横にスクロールできます

SELECT 
    c.customer_name,
    o.order_date,
    o.total_amount,
    SUM(o.total_amount) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(o.total_amount) OVER (
        PARTITION BY c.customer_id
        ORDER BY o.order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS avg_last_3
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name, o.order_date
LIMIT 20;

解説:

PARTITION BYで顧客ごとに分けて、異なるフレームを指定
累積はUNBOUNDED PRECEDING、直近3回は2 PRECEDING

問題 11チャレンジ

前後非対称の範囲

商品を価格順に並べて、前3行と後1行を含む5行の平均を計算してください。

※横にスクロールできます

SELECT 
    product_name,
    price,
    AVG(price) OVER (
        ORDER BY price
        ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
    ) AS asymmetric_avg
FROM products
ORDER BY price;

解説:

前と後で異なる行数を指定できる
3行前 + 2行前 + 1行前 + 現在 + 1行後 = 最大5行

問題 12チャレンジ

在庫変動の総合分析

商品ごとの在庫履歴に対して、7日移動平均、30日最大在庫、30日最小在庫を計算してください。

※横にスクロールできます

SELECT 
    product_id,
    check_date,
    stock,
    AVG(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d,
    MAX(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS max_30d,
    MIN(stock) OVER (
        PARTITION BY product_id
        ORDER BY check_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS min_30d
FROM stock_history
ORDER BY product_id, check_date DESC;

解説:

複数の異なるフレーム指定を1つのクエリで使用
7日 → 6 PRECEDING、30日 → 29 PRECEDING
実務でよく使う在庫分析パターン

📝 Step 8 のまとめ

✅ 学んだこと

フレーム：ウィンドウ関数が見る「範囲」
ROWS BETWEEN：物理的な行数で範囲指定（★おすすめ）
RANGE BETWEEN：値の範囲で指定（特殊なケース）
PRECEDING：前の行を指定
FOLLOWING：後の行を指定
UNBOUNDED：最初/最後まで（制限なし）
移動平均：トレンド分析に活用
累積計算：様々なパターン

📌 フレーム指定のパターン

※横にスクロールできます

— 累積（最初から現在）
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

— N行移動平均（過去N行）
ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW
— 例: 3行移動平均 → 2 PRECEDING AND CURRENT ROW

— 前後対称（前後N行）
ROWS BETWEEN N PRECEDING AND N FOLLOWING
— 例: 前後1行 → 1 PRECEDING AND 1 FOLLOWING

— 逆累積（現在から最後）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

— 全範囲
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

💡 実務での使いどころ

売上分析：3ヶ月・6ヶ月・12ヶ月移動平均でトレンド把握
在庫管理：7日移動平均、30日最大・最小で在庫推移を分析
顧客分析：累積購入額、直近N回の平均で購買傾向を把握
アクセス解析：24時間移動平均、週間ピーク検出

⚠️ 注意点

ROWSを使う：RANGEは複雑なので避ける
N行 → (N-1) PRECEDING：現在の行も含まれるため
最初・最後の行：対象行数が少なくなる可能性がある

❓ よくある質問

Q1: ROWSとRANGEはどちらを使うべきですか?

ほとんどの場合、ROWSを使います。ROWSは物理的な行数で指定するのでわかりやすく、意図した通りに動作します。RANGEは値の範囲で指定するため、同じ値がある場合の挙動が複雑です。

Q2: PRECEDINGの数はどう決めますか?

欲しい行数から1を引きます。

3行移動平均 → 2 PRECEDING（2行前 + 1行前 + 現在 = 3行）
7日移動平均 → 6 PRECEDING（6日前から今日まで = 7日）
12ヶ月移動平均 → 11 PRECEDING

Q3: 前後対称の範囲はいつ使いますか?

データを滑らかにしたい場合に使います。例えば、月次売上の急な変動を滑らかにするために、前後1ヶ月を含む3ヶ月平均を使います。これを「中心移動平均」とも呼びます。

Q4: UNBOUNDEDとは何ですか?

「制限なし」という意味です。

UNBOUNDED PRECEDING：最初の行から
UNBOUNDED FOLLOWING：最後の行まで

累積合計を計算するときによく使います。

Q5: 移動平均はなぜ重要ですか?

トレンドを見やすくするためです。生データは日々変動しますが、移動平均を使うと全体的な傾向がはっきりします。売上分析、在庫管理、アクセス解析など、あらゆる場面で活用されます。

Q6: フレーム指定なしでも動きますか?

はい。ORDER BYだけ指定した場合、デフォルトで「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」（最初から現在まで）になります。ただし、明示的にROWS BETWEENを書く方がコードが分かりやすくなります。

🎓 次のステップでは

Step 9: LAG、LEAD、FIRST_VALUE、LAST_VALUEでは、前の行や次の行の値を取得する方法を学びます。「前月比」「前年同月比」などの計算ができるようになります!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 8

📋 過去のメモ一覧 ▼