📊 Step 7: 集計ウィンドウ関数

累積合計・移動平均を計算しよう!

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

集計ウィンドウ関数とは（SUM、AVG、COUNT、MAX、MIN + OVER）
累積合計の計算（SUM() OVER）
移動平均の計算（AVG() OVER + ROWS BETWEEN）
累積最大・最小の計算（MAX/MIN() OVER）
シェア率の計算
実務での活用パターン

🎯 1. 集計ウィンドウ関数とは?

1-1. おなじみの集計関数をウィンドウ関数で使う

Step 6では順位付け関数（ROW_NUMBER、RANK、DENSE_RANK）を学びました。今回は、おなじみの集計関数（SUM、AVG、COUNT、MAX、MIN）をウィンドウ関数として使う方法を学びます。

💡 集計ウィンドウ関数でできること

累積合計：年初からの累積売上を計算
移動平均：過去3ヶ月の平均売上を計算
全体比率：各商品が全体の何%かを計算
グループ内比率：カテゴリ内のシェアを計算
累積最大・最小：ここまでの最高売上を追跡

1-2. GROUP BYとの違い（復習）

Step 6でも学びましたが、ウィンドウ関数とGROUP BYの違いをもう一度確認しましょう。

GROUP BYの場合：行がまとまって減る

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

— GROUP BY: 行がまとまって減る
SELECT 
    category,
    SUM(price) AS category_total
FROM products
GROUP BY category;

GROUP BYの結果:

category	category_total
果物	950
野菜	550
肉類	3,800

→ 行数が減る：元のデータが何十件あっても、カテゴリ数（3件）だけになる

ウィンドウ関数の場合：行数はそのまま

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

— ウィンドウ関数: 行数はそのまま
SELECT 
    product_name,
    category,
    price,
    SUM(price) OVER (PARTITION BY category) AS category_total
FROM products;

ウィンドウ関数の結果:

product_name	category	price	category_total
りんご	果物	150	950
バナナ	果物	120	950
ぶどう	果物	500	950
キャベツ	野菜	200	550

→ 行数はそのまま：各商品の価格 + カテゴリ合計を同時に見られる!

📌 ウィンドウ関数の特徴

行数は変わらない：元のデータがそのまま残る
元のデータと集計結果を同時に見られる：各行に集計結果が追加される
累積計算ができる：ORDER BYを指定すると「ここまでの合計」が計算できる

📈 2. SUM() OVER – 累積合計

2-1. 累積合計とは

累積合計とは、「ここまでの合計」を計算することです。

💡 累積合計のイメージ（貯金の例）

毎月の貯金額と、これまでの貯金の合計（累積）を考えてみましょう。

月	今月の貯金	累積貯金（ここまでの合計）
1月	10,000円	10,000円
2月	15,000円	25,000円（10,000 + 15,000）
3月	8,000円	33,000円（25,000 + 8,000）
4月	12,000円	45,000円（33,000 + 12,000）

このように、「今月まででいくら貯まったか」を計算するのが累積合計です。

2-2. 基本的な累積合計

やりたいこと：商品を価格の安い順に並べて、累積合計を計算する

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

SELECT 
    product_name,
    price,
    SUM(price) OVER (ORDER BY price) AS running_total
FROM products
ORDER BY price;

クエリの解説:

SUM(price) → 価格の合計を計算
OVER (ORDER BY price) → 価格の安い順に並べながら、「ここまでの合計」を計算
AS running_total → 結果に「running_total（累積合計）」という名前をつける

ORDER BYが重要:

ORDER BYを指定すると、その順番で「ここまでの合計」が計算されます。ORDER BYがないと、全体の合計が全行に表示されるだけです。

結果イメージ:

product_name	price	running_total
バナナ	120	120 ← 1件目
りんご	150	270 ← 120 + 150
みかん	180	450 ← 270 + 180
ぶどう	500	950 ← 450 + 500

各行で「ここまでの合計」が計算されています!

2-3. 累積合計の仕組み（1行ずつ詳しく）

累積合計がどのように計算されるか、1行ずつ見てみましょう。

📌 累積合計の計算の流れ

1行目（バナナ: 120円）
- ここまでの行: バナナだけ
- 合計: 120円
2行目（りんご: 150円）
- ここまでの行: バナナ + りんご
- 合計: 120 + 150 = 270円
3行目（みかん: 180円）
- ここまでの行: バナナ + りんご + みかん
- 合計: 120 + 150 + 180 = 450円
4行目（ぶどう: 500円）
- ここまでの行: バナナ + りんご + みかん + ぶどう
- 合計: 120 + 150 + 180 + 500 = 950円

2-4. カテゴリごとの累積合計（PARTITION BY）

やりたいこと：カテゴリごとに累積合計を計算する（カテゴリが変わるとリセット）

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

SELECT 
    product_name,
    category,
    price,
    SUM(price) OVER (
        PARTITION BY category 
        ORDER BY price
    ) AS category_running_total
FROM products
ORDER BY category, price;

クエリの解説:

PARTITION BY category → カテゴリごとにグループ分け
ORDER BY price → 各グループ内で価格の安い順に並べる
結果: カテゴリが変わると累積がリセットされる

結果イメージ:

product_name	category	price	category_running_total
バナナ	果物	120	120
りんご	果物	150	270
ぶどう	果物	500	770
にんじん	野菜	150	150 ← リセット!
キャベツ	野菜	200	350

カテゴリが「果物」から「野菜」に変わると、累積がリセットされて150から始まります!

2-5. 実務での活用：月次売上の累積

やりたいこと：月ごとの売上と、年初からの累積売上を計算する

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

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    SUM(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS running_total
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

strftime('%Y-%m', order_date) → 日付を「年-月」形式に変換（SQLiteの場合）
SUM(total_amount) → 月ごとの売上合計
SUM(SUM(total_amount)) OVER (...) → 月次売上の累積合計

なぜSUMが2回あるのか:

内側の SUM(total_amount) は GROUP BY による月次集計、外側の SUM(...) OVER (...) はその結果に対するウィンドウ関数です。

結果イメージ:

year_month	monthly_sales	running_total
2024-01	1,000,000	1,000,000
2024-02	1,200,000	2,200,000
2024-03	1,500,000	3,700,000
2024-04	1,800,000	5,500,000

「今年はここまでいくら売れたか」が一目でわかります!

活用シーン:

年間売上目標に対する進捗確認
顧客の累積購入額（LTV）の計算
在庫の累積入出庫の追跡
ポイントの累積獲得・使用履歴

📊 3. AVG() OVER – 移動平均

3-1. 移動平均とは

移動平均とは、「過去N期間の平均」を計算する方法です。データの急な変動を滑らかにして、トレンド（傾向）を見やすくします。

💡 移動平均のイメージ（体重管理の例）

毎日の体重は変動が大きいですが、「過去3日間の平均」を見ると、実際の傾向がわかりやすくなります。

日付	体重	3日移動平均
1日	70.0kg	70.0kg（1日分）
2日	70.5kg	70.25kg（2日分）
3日	69.5kg	70.0kg（70.0+70.5+69.5）÷3
4日	71.0kg	70.33kg（70.5+69.5+71.0）÷3
5日	69.0kg	69.83kg（69.5+71.0+69.0）÷3

3日移動平均を見ると、日々の変動が滑らかになり、「実際に増えているか減っているか」がわかりやすくなります。

3-2. ROWS BETWEENの基本

移動平均を計算するには、ROWS BETWEENを使って「どの範囲の行を対象にするか」を指定します。

📌 ROWS BETWEENの構文

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

ROWS BETWEEN 開始位置 AND 終了位置

N PRECEDING → N行前
CURRENT ROW → 現在の行
N FOLLOWING → N行後
UNBOUNDED PRECEDING → 最初の行から
UNBOUNDED FOLLOWING → 最後の行まで

3-3. ROWS BETWEENの視覚的な理解

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

📌 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW の意味

「2行前から現在の行まで」= 過去3行（現在を含む）

行	月	売上	対象範囲（3月の場合）
1	1月	100万	← 2行前
2	2月	120万	← 1行前
3	3月	150万	← 現在の行
4	4月	180万

3月の移動平均 = (100万 + 120万 + 150万) ÷ 3 = 123.3万

3-4. 過去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_3months
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

AVG(SUM(total_amount)) → 月次売上の平均を計算
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 2行前から現在まで（過去3ヶ月）
1月は1ヶ月分、2月は2ヶ月分、3月以降は3ヶ月分の平均になる

結果イメージ:

year_month	monthly_sales	moving_avg_3months
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ヶ月分）
2024-05	1,600,000	1,633,333（3ヶ月分）

3-5. 前後を含む移動平均

やりたいこと：前後1ヶ月を含む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_moving_avg
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

1 PRECEDING → 1行前
1 FOLLOWING → 1行後
結果: 前月 + 当月 + 翌月の3ヶ月平均

注意:

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

よく使うROWS BETWEENのパターン:

パターン	意味	用途
2 PRECEDING AND CURRENT ROW	2行前から現在まで（3行）	過去3ヶ月移動平均
1 PRECEDING AND 1 FOLLOWING	前後1行ずつ（3行）	中心移動平均
6 PRECEDING AND CURRENT ROW	6行前から現在まで（7行）	過去7日間の平均
UNBOUNDED PRECEDING AND CURRENT ROW	最初から現在まで	累積平均

移動平均の活用シーン:

売上のトレンド分析：季節変動を滑らかにして、全体的な傾向を把握
株価の分析：短期・中期・長期の移動平均線
異常値の検出：移動平均から大きく外れた値を見つける
予測の基礎：過去のトレンドから将来を予測

🔢 4. COUNT() OVER – 累積件数

4-1. 累積件数を数える

やりたいこと：注文を日付順に並べて、累積の注文件数を表示する

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

SELECT 
    order_date,
    order_id,
    total_amount,
    COUNT(*) OVER (ORDER BY order_date) AS running_count
FROM orders
ORDER BY order_date
LIMIT 10;

クエリの解説:

COUNT(*) → 行数をカウント
OVER (ORDER BY order_date) → 日付順に「ここまでの件数」を計算
結果: 1, 2, 3, 4, 5… と累積件数が増えていく

4-2. 顧客ごとの累積注文回数

やりたいこと：各顧客の注文履歴に、その顧客の累積注文回数を追加する

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

SELECT 
    c.customer_name,
    o.order_date,
    o.total_amount,
    COUNT(*) OVER (
        PARTITION BY c.customer_id 
        ORDER BY o.order_date
    ) AS customer_order_count
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 → 顧客ごとにグループ分け
ORDER BY o.order_date → 各顧客内で日付順
結果: 「この顧客の何回目の注文か」がわかる

結果イメージ:

customer_name	order_date	total_amount	customer_order_count
田中太郎	2024-01-05	5,000	1 ← 1回目
田中太郎	2024-02-10	8,000	2 ← 2回目
田中太郎	2024-03-15	3,000	3 ← 3回目
鈴木花子	2024-01-20	12,000	1 ← リセット!

4-3. 全体件数を各行に表示

やりたいこと：各商品の情報と、全商品数を同時に表示する

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

SELECT 
    product_name,
    category,
    price,
    COUNT(*) OVER () AS total_products
FROM products;

クエリの解説:

COUNT(*) OVER () → 全体の件数（ORDER BYなし）
全ての行に同じ値（全商品数）が表示される
各商品が「全体の何件中の1件か」がわかる

⬆️⬇️ 5. MAX() / MIN() OVER – 最大・最小値

5-1. グループ内の最大・最小を表示

やりたいこと：各商品に、そのカテゴリの最高価格と最低価格を表示する

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

SELECT 
    product_name,
    category,
    price,
    MAX(price) OVER (PARTITION BY category) AS category_max,
    MIN(price) OVER (PARTITION BY category) AS category_min
FROM products
ORDER BY category, price DESC;

クエリの解説:

MAX(price) OVER (PARTITION BY category) → カテゴリ内の最高価格
MIN(price) OVER (PARTITION BY category) → カテゴリ内の最低価格
各行に同じカテゴリの最高・最低価格が表示される

結果イメージ:

product_name	category	price	category_max	category_min
ぶどう	果物	500	500	120
りんご	果物	150	500	120
バナナ	果物	120	500	120

5-2. 最低価格との差額を計算

やりたいこと：各商品が、同じカテゴリの最低価格より何円高いかを計算する

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

SELECT 
    product_name,
    category,
    price,
    MIN(price) OVER (PARTITION BY category) AS category_min,
    price – MIN(price) OVER (PARTITION BY category) AS diff_from_min
FROM products
ORDER BY category, price;

クエリの解説:

price - MIN(price) OVER (...) → 自分の価格 – カテゴリ最低価格
最低価格の商品は差額が0になる
「このカテゴリで最も安い商品より何円高いか」がわかる

5-3. 累積最大（ここまでの最高値）

やりたいこと：月次売上と、ここまでの最高月次売上を表示する

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

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    MAX(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS max_so_far
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

MAX(...) OVER (ORDER BY ...) → ここまでの最大値
ORDER BYを指定すると「累積最大」になる
新記録が出た月で値が更新される

結果イメージ:

year_month	monthly_sales	max_so_far
2024-01	1,000,000	1,000,000
2024-02	1,200,000	1,200,000 ← 更新!
2024-03	1,100,000	1,200,000（変わらず）
2024-04	1,800,000	1,800,000 ← 更新!
2024-05	1,500,000	1,800,000（変わらず）

2月と4月で新記録が出ています!

累積最大/最小の活用シーン:

売上の新記録追跡：過去最高売上を更新したか確認
株価の最高値：過去最高値からの下落率を計算
パフォーマンス追跡：ベストタイム、最高得点の更新

📊 6. シェア率の計算

6-1. 全体に対するシェア率

やりたいこと：各商品の価格が、全商品の合計価格の何%かを計算する

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

SELECT 
    product_name,
    price,
    SUM(price) OVER () AS total_price,
    ROUND(price * 100.0 / SUM(price) OVER (), 2) AS share_pct
FROM products
ORDER BY price DESC;

クエリの解説:

SUM(price) OVER () → 全商品の価格合計
price * 100.0 / SUM(price) OVER () → 自分の価格 ÷ 全体合計 × 100
ROUND(..., 2) → 小数点以下2桁に丸める
100.0（小数点付き）を使うのは、整数除算を避けるため

結果イメージ:

product_name	price	total_price	share_pct
高級メロン	5,000	10,000	50.00%
ぶどう	3,000	10,000	30.00%
りんご	2,000	10,000	20.00%

6-2. カテゴリ内のシェア率

やりたいこと：各商品の価格が、同じカテゴリの合計価格の何%かを計算する

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

SELECT 
    product_name,
    category,
    price,
    SUM(price) OVER (PARTITION BY category) AS category_total,
    ROUND(price * 100.0 / SUM(price) OVER (PARTITION BY category), 2) AS category_share_pct
FROM products
ORDER BY category, price DESC;

クエリの解説:

SUM(price) OVER (PARTITION BY category) → カテゴリ内の価格合計
price / カテゴリ合計 × 100 → カテゴリ内でのシェア率
各カテゴリ内でのシェアを計算（カテゴリごとに合計が100%になる）

結果イメージ:

product_name	category	price	category_total	category_share_pct
ぶどう	果物	500	950	52.63%
りんご	果物	150	950	15.79%
バナナ	果物	120	950	12.63%

果物カテゴリ内では、ぶどうが52.63%のシェアを占めています。

📊 7. 複数の集計を同時に

7-1. 統計情報を一度に取得

やりたいこと：各商品に、カテゴリの統計情報（平均、最大、最小、合計、件数、シェア）を全て表示する

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

SELECT 
    product_name,
    category,
    price,
    — カテゴリ内統計
    AVG(price) OVER (PARTITION BY category) AS category_avg,
    MAX(price) OVER (PARTITION BY category) AS category_max,
    MIN(price) OVER (PARTITION BY category) AS category_min,
    SUM(price) OVER (PARTITION BY category) AS category_total,
    COUNT(*) OVER (PARTITION BY category) AS category_count,
    — シェア率
    ROUND(price * 100.0 / SUM(price) OVER (PARTITION BY category), 2) AS share_pct
FROM products
ORDER BY category, price DESC;

クエリの解説:

1つのクエリで複数のウィンドウ関数を同時に使用
全て同じ PARTITION BY category なので、カテゴリごとの統計
各行に全ての統計情報が追加される

メリット:

複数のサブクエリやJOINを使わずに、1回のクエリで全ての統計を取得できます。

7-2. 売上分析ダッシュボード

やりたいこと：月次売上の詳細分析（累積売上、移動平均、最高売上を同時に表示）

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

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    COUNT(order_id) AS order_count,
    SUM(total_amount) AS monthly_sales,
    AVG(total_amount) AS avg_order_amount,
    — 累積売上
    SUM(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS running_total,
    — 3ヶ月移動平均
    AVG(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m,
    — 累積最高売上
    MAX(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS max_so_far
FROM orders
WHERE order_date >= ‘2024-01-01’
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

クエリの解説:

COUNT(order_id) → 月の注文件数
SUM(total_amount) → 月次売上
AVG(total_amount) → 平均注文額
SUM(SUM(...)) OVER (...) → 累積売上
AVG(SUM(...)) OVER (... ROWS BETWEEN ...) → 3ヶ月移動平均
MAX(SUM(...)) OVER (...) → ここまでの最高売上

活用シーン:

経営ダッシュボード：売上の推移、目標達成率を一覧表示
商品分析レポート：カテゴリ内での位置づけを把握
顧客分析：顧客ごとの累積購入額、平均購入額を計算

💼 8. 実務での活用例

8-1. 顧客別累積購入額

やりたいこと：各顧客の注文履歴に、累積購入額と累積購入回数を追加する

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

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
    ) AS running_total,
    AVG(o.total_amount) OVER (
        PARTITION BY c.customer_id 
        ORDER BY o.order_date
    ) AS running_avg,
    COUNT(*) OVER (
        PARTITION BY c.customer_id 
        ORDER BY o.order_date
    ) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name, o.order_date;

活用シーン:

顧客のLTV（生涯価値）の推移を追跡
「この注文時点での累積購入額」を把握
顧客ランクの変化タイミングを特定

8-2. カテゴリ別売上シェア分析

やりたいこと：カテゴリ別の売上と全体に対するシェア率を計算する

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

SELECT 
    p.category,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(od.quantity * od.unit_price) AS category_sales,
    SUM(SUM(od.quantity * od.unit_price)) OVER () AS total_sales,
    ROUND(
        SUM(od.quantity * od.unit_price) * 100.0 / 
        SUM(SUM(od.quantity * od.unit_price)) OVER (),
        2
    ) AS share_pct,
    RANK() OVER (ORDER BY SUM(od.quantity * od.unit_price) DESC) AS sales_rank
FROM products p
INNER JOIN order_details od ON p.product_id = od.product_id
INNER JOIN orders o ON od.order_id = o.order_id
GROUP BY p.category
ORDER BY category_sales DESC;

クエリの解説:

GROUP BYでカテゴリごとに集計
ウィンドウ関数で全体合計とシェア率を計算
RANKで売上順位も追加

📝 練習問題

集計ウィンドウ関数をマスターしましょう!

問題 1基本

累積合計の計算

商品を価格の安い順に並べて、価格の累積合計を表示してください。

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

SELECT 
    product_name,
    price,
    SUM(price) OVER (ORDER BY price) AS running_total
FROM products
ORDER BY price;

解説:

SUM(price) → 価格の合計を計算
OVER (ORDER BY price) → 価格の安い順に「ここまでの合計」を計算
ORDER BYを指定すると累積計算になる

問題 2基本

カテゴリ平均と最高価格

各商品に、そのカテゴリの平均価格と最高価格を表示してください。

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

SELECT 
    product_name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg,
    MAX(price) OVER (PARTITION BY category) AS category_max
FROM products
ORDER BY category, price;

解説:

PARTITION BY category → カテゴリごとにグループ分け
行数は減らず、各行にカテゴリ統計が追加される

問題 3基本

全体商品数の表示

各商品の情報と、全商品数を同時に表示してください。

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

SELECT 
    product_name,
    price,
    COUNT(*) OVER () AS total_products
FROM products
LIMIT 10;

解説:

COUNT(*) OVER () → 全体の件数
ORDER BYがないので、全ての行に同じ件数が表示される

問題 4応用

カテゴリ別累積合計

カテゴリごとに価格の累積合計を計算してください。カテゴリが変わるとリセットされるようにしてください。

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

SELECT 
    product_name,
    category,
    price,
    SUM(price) OVER (
        PARTITION BY category 
        ORDER BY price
    ) AS category_running_total
FROM products
ORDER BY category, price;

解説:

PARTITION BY category → カテゴリごとにグループ分け
ORDER BY price → 各グループ内で価格順に累積
カテゴリが変わると累積がリセットされる

問題 5応用

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;

解説:

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 2行前から現在まで（計3行）
最初の1〜2行は対象行数が少ないため、1〜2行分の平均になる

問題 6応用

カテゴリ内シェア率

各商品の価格が、そのカテゴリの合計価格の何%かを計算してください。

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

SELECT 
    product_name,
    category,
    price,
    SUM(price) OVER (PARTITION BY category) AS category_total,
    ROUND(
        price * 100.0 / SUM(price) OVER (PARTITION BY category),
        2
    ) AS share_pct
FROM products
ORDER BY category, price DESC;

解説:

price * 100.0 / SUM(price) OVER (...) → 個別価格 ÷ カテゴリ合計 × 100
100.0（小数点付き）で整数除算を避ける
ROUNDで小数点以下2桁に丸める

問題 7応用

月次売上と年初累積

月ごとの売上と、年初からの累積売上を表示してください。

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

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    SUM(SUM(total_amount)) OVER (
        PARTITION BY strftime(‘%Y’, order_date)
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS ytd_total
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

解説:

PARTITION BY strftime('%Y', order_date) → 年ごとにリセット
年が変わると累積が1月からリセットされる（YTD: Year To Date）

問題 8チャレンジ

顧客別累積統計

各顧客の注文履歴に、その顧客の累積購入額、累積購入回数、平均購入額を表示してください。

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

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
    ) AS running_total,
    COUNT(*) OVER (
        PARTITION BY c.customer_id 
        ORDER BY o.order_date
    ) AS order_count,
    AVG(o.total_amount) OVER (
        PARTITION BY c.customer_id 
        ORDER BY o.order_date
    ) AS running_avg
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name, o.order_date
LIMIT 20;

解説:

3つの異なる集計ウィンドウ関数を同時に使用
全て同じPARTITION BY + ORDER BYで、顧客ごと・日付順の累積

問題 9チャレンジ

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;

解説:

AVG(SUM(...)) → GROUP BY結果に対するウィンドウ関数
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW → 過去3ヶ月

問題 10チャレンジ

累積最高売上

月次売上と、ここまでの最高月次売上を表示してください。

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

SELECT 
    strftime(‘%Y-%m’, order_date) AS year_month,
    SUM(total_amount) AS monthly_sales,
    MAX(SUM(total_amount)) OVER (
        ORDER BY strftime(‘%Y-%m’, order_date)
    ) AS max_so_far
FROM orders
GROUP BY strftime(‘%Y-%m’, order_date)
ORDER BY year_month;

解説:

MAX(...) OVER (ORDER BY ...) → 累積最大（ここまでの最高値）
新記録が出た月で値が更新される

問題 11チャレンジ

カテゴリ別詳細統計

各商品に、カテゴリ平均、カテゴリ合計、カテゴリ商品数、カテゴリ内シェア率を全て表示してください。

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

SELECT 
    product_name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg,
    SUM(price) OVER (PARTITION BY category) AS category_total,
    COUNT(*) OVER (PARTITION BY category) AS category_count,
    ROUND(
        price * 100.0 / SUM(price) OVER (PARTITION BY category),
        2
    ) AS share_pct
FROM products
ORDER BY category, price DESC;

解説:

1つのクエリで複数のウィンドウ関数を同時に使用
実務でよく使うパターン

問題 12チャレンジ

総合売上分析

月次売上、累積売上、3ヶ月移動平均、ここまでの最高売上を全て表示してください。

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

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

解説:

実務の売上ダッシュボードで使う総合的な分析
累積、移動平均、累積最大を同時に計算
これが書けるようになれば上級者です!

📝 Step 7 のまとめ

✅ 学んだこと

SUM() OVER：累積合計の計算
AVG() OVER：移動平均の計算
COUNT() OVER：累積件数の計算
MAX/MIN() OVER：累積最大・最小の計算
PARTITION BY：グループごとの集計
ROWS BETWEEN：範囲指定（移動平均など）
シェア率の計算：個別 / 全体 × 100
複数の集計：一度に複数の統計を計算

📌 よく使うパターン

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

— 累積合計
SUM(列名) OVER (ORDER BY 列名)

— カテゴリごとの累積
SUM(列名) OVER (PARTITION BY カテゴリ ORDER BY 列名)

— 移動平均（過去3行）
AVG(列名) OVER (
    ORDER BY 列名 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

— 累積最大
MAX(列名) OVER (ORDER BY 列名)

— シェア率
列名 * 100.0 / SUM(列名) OVER (PARTITION BY グループ)

💡 実務での使いどころ

売上分析：累積売上、移動平均、目標達成率
顧客分析：累積購入額、LTVの推移
商品分析：カテゴリ内シェア、全体シェア
トレンド分析：移動平均で変動を滑らかに
記録追跡：累積最大で新記録を把握

⚠️ 注意点

ORDER BY必須：累積計算や移動平均にはORDER BYが必要
ORDER BYなし：全体の合計/平均が全行に表示される
ROWS BETWEEN：行数で範囲指定（最初の数行は対象が少ない）

❓ よくある質問

Q1: ROWS BETWEENの範囲はどう指定しますか?

PRECEDING（前）とFOLLOWING（後）で指定します。

2 PRECEDING AND CURRENT ROW：2行前から現在まで（3行）
1 PRECEDING AND 1 FOLLOWING：前後1行ずつ（3行）
UNBOUNDED PRECEDING AND CURRENT ROW：最初から現在まで（デフォルト）

Q2: 累積合計と通常のSUMの違いは?

通常のSUM（GROUP BY）は行が減りますが、ウィンドウ関数のSUMは行数が変わりません。

ウィンドウ関数なら、元のデータと合計を同時に見られます。また、ORDER BYを指定すると「ここまでの合計（累積）」が計算できます。

Q3: 移動平均はなぜ使うのですか?

データの変動を滑らかにして、トレンドを見やすくするためです。

例えば、月次売上は季節や特別なイベントで大きく変動しますが、3ヶ月移動平均を使うと全体的な傾向が見えやすくなります。

Q4: PARTITION BYなしとありの違いは?

PARTITION BYなしは全体が対象、PARTITION BYありはグループごとに計算されます。

SUM(price) OVER ()：全体の合計
SUM(price) OVER (PARTITION BY category)：カテゴリごとの合計

Q5: ORDER BYは必須ですか?

累積計算や移動平均には必須です。ORDER BYがないと、どの順序で累積するかが決まりません。

ただし、単純な合計や平均（グループ全体）ならORDER BYなしでOKです。

Q6: 実務で最もよく使うのは?

累積合計と移動平均が最もよく使われます。売上分析、在庫管理、顧客分析など、あらゆる場面で活躍します。

次によく使うのはシェア率の計算です。

🎓 次のステップでは

Step 8: フレーム指定と範囲指定では、ROWS BETWEENとRANGE BETWEENの違い、より高度なフレーム指定方法を学びます。LAG/LEAD関数で前後の行を参照する方法も学びます!

📝

学習メモ

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

📋 過去のメモ一覧 ▼