🪟 Step 6: ウィンドウ関数入門

強力な分析ツール、ウィンドウ関数を使ってみよう!

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

ウィンドウ関数とは何か（GROUP BYとの違い）
OVER句の基本構文
PARTITION BY（グループごとの計算）
順位付け関数（ROW_NUMBER、RANK、DENSE_RANK）
グループごとのランキング
トップNの抽出方法

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

1-1. ウィンドウ関数でできること

ウィンドウ関数は、SQLの中でも特に強力で便利な機能です。これまで学んだサブクエリで行っていた「累積計算」「ランキング」「前後比較」などが、シンプルに書けるようになります。

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

順位付け：売上ランキング、成績順位など
累積計算：累積売上、累積ポイントなど
移動平均：過去3ヶ月の平均売上など
前後比較：前月との比較、前年同月比など
グループごとの統計：カテゴリ平均を各行に表示など

1-2. なぜ「ウィンドウ」と呼ぶのか

「ウィンドウ（窓）」という名前は、データの一部を「窓から覗くように見る」イメージから来ています。

例えば、「各商品を見ながら、同時にそのカテゴリ全体の平均も見る」というように、個々のデータと集計結果を同時に見られるのがウィンドウ関数の特徴です。

1-3. GROUP BYとの違い

これまで集計にはGROUP BYを使ってきました。ウィンドウ関数との最大の違いは「行数が減るかどうか」です。

GROUP BYの例：カテゴリごとの平均価格

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

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

GROUP BYの結果:

category	avg_price
果物	350
野菜	180
肉類	1,500

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

ウィンドウ関数の例：同じ計算を行数を減らさずに

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

— ウィンドウ関数: 行数は変わらない
SELECT 
    product_name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products;

ウィンドウ関数の結果:

product_name	category	price	category_avg
りんご	果物	150	350
バナナ	果物	120	350
ぶどう	果物	500	350
キャベツ	野菜	200	180
にんじん	野菜	150	180

→ 行数はそのまま：各商品の情報 + カテゴリ平均を同時に見られる!

📌 GROUP BY vs ウィンドウ関数

項目	GROUP BY	ウィンドウ関数
行数	減る（グループ数になる）	変わらない
元データ	見えなくなる	そのまま見える
用途	集計結果だけ欲しい時	元データ + 集計結果を同時に見たい時

📝 2. OVER句の基本

2-1. OVER句とは

ウィンドウ関数を使うときは、必ずOVER句を書きます。これがウィンドウ関数の目印です!

📌 ウィンドウ関数の基本構文

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

SELECT 
    列名,
    関数名() OVER (
        [PARTITION BY 列名]  — グループ分け（省略可能）
        [ORDER BY 列名]      — 並び順（省略可能）
    ) AS 別名
FROM テーブル名;

関数名() → SUM、AVG、COUNT、ROW_NUMBERなど
OVER () → ウィンドウ関数の目印
PARTITION BY → グループ分け（GROUP BYのようなもの）
ORDER BY → 計算する順番を指定

2-2. 例1：全体の平均を各行に追加

やりたいこと：各商品の価格と、全商品の平均価格を同時に表示する

これはStep 5でスカラーサブクエリを使って行いましたね。ウィンドウ関数を使うとさらにシンプルに書けます。

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

SELECT 
    product_name,
    price,
    AVG(price) OVER () AS overall_avg
FROM products
LIMIT 5;

クエリの解説:

AVG(price) → 価格の平均を計算
OVER () → 括弧の中が空 = 全体を対象にする
AS overall_avg → 結果に「overall_avg」という名前をつける

ポイント:

OVER () のように括弧の中が空の場合、テーブル全体が対象になります。

結果イメージ:

product_name	price	overall_avg
りんご	150	324.5
バナナ	120	324.5
みかん	180	324.5

全ての行に同じ平均価格（324.5円）が表示されます。

2-3. 例2：カテゴリごとの平均（PARTITION BY）

やりたいこと：各商品の価格と、同じカテゴリの平均価格を同時に表示する

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

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

クエリの解説:

PARTITION BY category → カテゴリごとにグループ分けして計算
果物は果物だけで平均、野菜は野菜だけで平均…
GROUP BYと違い、行数は減らない

結果イメージ:

product_name	category	price	category_avg
バナナ	果物	120	350
りんご	果物	150	350
ぶどう	果物	500	350
にんじん	野菜	150	180
キャベツ	野菜	200	180

果物は「果物の平均」、野菜は「野菜の平均」と、カテゴリごとに異なる平均が表示されます。

2-4. スカラーサブクエリとの比較

Step 5で学んだスカラーサブクエリと比較してみましょう。

スカラーサブクエリの場合（Step 5で学習）:

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

SELECT 
    p1.product_name,
    p1.category,
    p1.price,
    (
        SELECT AVG(p2.price) 
        FROM products p2 
        WHERE p2.category = p1.category
    ) AS category_avg
FROM products p1;

ウィンドウ関数の場合（今回学習）:

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

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

💡 ウィンドウ関数のメリット

シンプル：コードが短く読みやすい
効率的：サブクエリのように何度も実行されない
柔軟：複数のウィンドウ関数を同時に使える

2-5. 複数のウィンドウ関数を同時に使う

やりたいこと：各商品に、全体平均、カテゴリ平均、カテゴリ内最高価格を同時に表示する

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

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

クエリの解説:

AVG(price) OVER () → 全体の平均
AVG(price) OVER (PARTITION BY category) → カテゴリごとの平均
MAX(price) OVER (PARTITION BY category) → カテゴリごとの最高価格
1つのクエリで複数の統計を同時に計算できる!

結果イメージ:

product_name	category	price	overall_avg	category_avg	category_max
ぶどう	果物	500	324.5	350	500
りんご	果物	150	324.5	350	500

🏆 3. 順位付け関数

3-1. 順位付け関数とは

ウィンドウ関数の中でも特によく使われるのが順位付け関数です。データに順位（ランキング）を付けることができます。

💡 3つの順位付け関数

ROW_NUMBER()：連番をつける（必ず1, 2, 3, 4, 5…）
RANK()：同点を考慮した順位（同点があれば次は飛ぶ）
DENSE_RANK()：同点を考慮した順位（次は飛ばない）

3-2. ROW_NUMBER：連番をつける

ROW_NUMBER()は、各行に連続した番号を付けます。同じ値があっても、必ず異なる番号になります。

やりたいこと：商品を価格の高い順に並べて、順位をつける

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

SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products
LIMIT 10;

クエリの解説:

ROW_NUMBER() → 連番をつける関数
OVER (ORDER BY price DESC) → 価格の高い順に番号をつける
ORDER BY price DESC → DESC = 降順（高い順）

ポイント:

ROW_NUMBERは必ず ORDER BY が必要です。どの順番で番号をつけるか指定しないと意味がありません。

結果イメージ:

product_name	price	rank
高級メロン	5,000	1
まぐろ	3,000	2
和牛	2,500	3
うなぎ	2,000	4
かに	1,500	5

必ず連番（1, 2, 3, 4, 5…）になります。

3-3. RANK：同点を考慮した順位（次は飛ぶ）

RANK()は、同じ値には同じ順位を付けます。ただし、同点があった場合、次の順位は飛びます。

例えば、1位、2位、2位（同点）、4位…のようになります（3位は飛ばす）。

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

SELECT 
    product_name,
    price,
    RANK() OVER (ORDER BY price DESC) AS rank
FROM products
LIMIT 10;

結果イメージ（同点がある場合）:

product_name	price	rank
高級メロン	5,000	1
まぐろ	3,000	2
和牛	2,500	3
うなぎ	2,000	4
かに	1,500	5
いくら	1,500	5 ← 同点!
サーモン	1,200	7 ← 6は飛ばす

かにといくらが同点（1,500円）で5位。次のサーモンは6位ではなく7位になります。

3-4. DENSE_RANK：同点を考慮した順位（次は飛ばない）

DENSE_RANK()は、同じ値には同じ順位を付けますが、次の順位は飛ばしません。

例えば、1位、2位、2位（同点）、3位…のようになります（3位に続く）。

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

SELECT 
    product_name,
    price,
    DENSE_RANK() OVER (ORDER BY price DESC) AS rank
FROM products
LIMIT 10;

結果イメージ（同点がある場合）:

product_name	price	rank
高級メロン	5,000	1
まぐろ	3,000	2
和牛	2,500	3
うなぎ	2,000	4
かに	1,500	5
いくら	1,500	5 ← 同点!
サーモン	1,200	6 ← 飛ばさず続く

かにといくらが同点（1,500円）で5位。次のサーモンは6位になります（飛ばさない）。

3-5. 3つの関数の違いを比較

同じデータで3つの関数を同時に使って、違いを確認してみましょう。

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

SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products
ORDER BY price DESC
LIMIT 10;

3つの違いを比較:

product_name	price	row_num	rank	dense_rank
高級メロン	5,000	1	1	1
まぐろ	3,000	2	2	2
かに	1,500	3	3	3
いくら	1,500	4	3	3
サーモン	1,200	5	5	4

📌 3つの違いまとめ

関数	同点の扱い	次の順位	結果例
ROW_NUMBER	区別する（別の番号）	常に連番	1, 2, 3, 4, 5
RANK	同じ順位	飛ぶ	1, 2, 3, 3, 5
DENSE_RANK	同じ順位	飛ばない	1, 2, 3, 3, 4

3-6. どれを使うべきか?

使い分けの指針:

ROW_NUMBER：
- 「トップ3だけ取得したい」など、必ずN件欲しい時
- 同点があっても区別したい時
- 単純に番号を振りたい時
RANK：
- 「何人抜いたか」を知りたい時
- スポーツの順位表のように、飛び順位が自然な時
- 例：100人中5位 = 95人より上
DENSE_RANK：
- 「何種類の価格帯があるか」を知りたい時
- ランクの数を数えたい時
- 例：価格帯が10段階ある

💡 実務でのアドバイス

迷ったらROW_NUMBERを使うことが多いです。「トップ5」「上位10件」など、件数を指定して取得する場合に最も使いやすいからです。

🎨 4. PARTITION BYでグループごとの順位

4-1. グループごとに順位をつける

PARTITION BYを順位付け関数と組み合わせると、グループごとに独立した順位を付けられます。

例えば、「全体で1位〜100位」ではなく、「果物カテゴリで1位〜10位」「野菜カテゴリで1位〜10位」のように、カテゴリごとにランキングを作れます。

やりたいこと：各カテゴリ内で、価格の高い順にランキングをつける

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

SELECT 
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) AS rank_in_category
FROM products
ORDER BY category, rank_in_category;

クエリの解説:

PARTITION BY category → カテゴリごとにグループ分け
ORDER BY price DESC → 各グループ内で価格の高い順
結果：カテゴリごとに1から始まる順位がつく

実行の流れ:

データを category でグループ分け
各グループ内で price の高い順に並べる
グループごとに 1, 2, 3… と番号をつける

結果イメージ:

product_name	category	price	rank_in_category
高級メロン	果物	5,000	1 ← 果物で1位
ぶどう	果物	500	2
いちご	果物	800	3
和牛	肉類	2,500	1 ← 肉類で1位（リセット!）
豚肉	肉類	800	2
鶏肉	肉類	500	3
キャベツ	野菜	200	1 ← 野菜で1位（リセット!）
にんじん	野菜	150	2

カテゴリが変わると順位が1からリセットされます!

4-2. トップNを取得する

やりたいこと：各カテゴリで価格の高い商品トップ3だけを取得する

ウィンドウ関数の結果をWHERE句で直接フィルタリングすることはできません。なぜなら、ウィンドウ関数はWHERE句の後に実行されるからです。

❌ これはエラーになる（WHERE句で直接使えない）

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

— エラー: ウィンドウ関数はWHERE句で使えない
SELECT 
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank
FROM products
WHERE rank <= 3;  -- ★エラー!

代わりに、サブクエリを使って2段階で処理します。

✅ サブクエリを使った正しい方法

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

— Step 1: サブクエリで順位をつける
— Step 2: 外側のクエリで順位を使ってフィルタリング

SELECT *
FROM (
    — Step 1: 順位をつける
    SELECT 
        product_name,
        category,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY price DESC
        ) AS rank
    FROM products
) ranked
WHERE rank <= 3
ORDER BY category, rank;

クエリの解説:

サブクエリ（内側）：順位をつけた結果を作成
AS ranked → サブクエリに「ranked」という名前をつける（必須）
外側のクエリ：WHERE rank <= 3 でトップ3だけ抽出
ORDER BY category, rank → カテゴリ順、その中で順位順に並べる

結果イメージ:

product_name	category	price	rank
高級メロン	果物	5,000	1
いちご	果物	800	2
ぶどう	果物	500	3
和牛	肉類	2,500	1
豚肉	肉類	800	2
鶏肉	肉類	500	3

各カテゴリのトップ3だけが抽出されています!

活用シーン:

各カテゴリの人気商品トップ3を表示
各店舗の売上トップ5を抽出
各部門の優秀社員ベスト10を選出
各都道府県の優良顧客トップ5を特定

💼 5. 実務での活用例

5-1. 売上ランキングの作成

やりたいこと：商品ごとの総売上を計算し、ランキングを作成する

ウィンドウ関数はGROUP BYと組み合わせて使うこともできます。

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

SELECT 
    p.product_name,
    COUNT(od.detail_id) AS sales_count,
    SUM(od.quantity * od.unit_price) AS total_sales,
    RANK() OVER (ORDER BY SUM(od.quantity * od.unit_price) DESC) AS sales_rank
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY sales_rank
LIMIT 10;

クエリの解説:

LEFT JOIN → 売上がない商品も含める
GROUP BY → 商品ごとに集計
SUM(od.quantity * od.unit_price) → 総売上を計算
RANK() OVER (...) → 集計結果に対して順位をつける

ポイント:

GROUP BYで集計した後に、ウィンドウ関数で順位をつけています。ウィンドウ関数はGROUP BYの後に実行されます。

5-2. 顧客の購入ランキング

やりたいこと：顧客の購入回数と総購入額でランキングを作成する

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

SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    ROW_NUMBER() OVER (ORDER BY COUNT(o.order_id) DESC) AS count_rank,
    ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) AS amount_rank
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 10;

クエリの解説:

1つのクエリで2つの異なるランキングを作成
count_rank → 購入回数順のランキング
amount_rank → 購入額順のランキング
同じ顧客でも、回数と金額で順位が違うことがある

結果イメージ:

customer_name	order_count	total_spent	count_rank	amount_rank
田中太郎	25	350,000	1	1
鈴木花子	18	220,000	2	2
佐藤次郎	30	180,000	1	3

佐藤次郎さんは購入回数では1位だが、金額では3位。2つの視点でランキングできます。

5-3. 都道府県別の優良顧客

やりたいこと：各都道府県で購入額の多い顧客トップ5を抽出する

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

SELECT *
FROM (
    SELECT 
        c.customer_name,
        c.prefecture,
        SUM(o.total_amount) AS total_spent,
        RANK() OVER (
            PARTITION BY c.prefecture 
            ORDER BY SUM(o.total_amount) DESC
        ) AS rank_in_prefecture
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.prefecture
) ranked
WHERE rank_in_prefecture <= 5
ORDER BY prefecture, rank_in_prefecture;

クエリの解説:

PARTITION BY c.prefecture → 都道府県ごとにグループ分け
各都道府県内で独立した順位がつく
サブクエリで順位をつけてから、外側で上位5件を抽出

💡 ウィンドウ関数のメリット

シンプル：複雑なサブクエリが不要になる
読みやすい：何をしているか分かりやすい
効率的：データベースが最適化しやすい
柔軟：複数のランキングを同時に作成できる

📝 練習問題

ウィンドウ関数の基本をマスターしましょう!

問題 1基本

全体平均の表示

全商品の情報と、全商品の平均価格をウィンドウ関数で表示してください。

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

SELECT 
    product_name,
    price,
    AVG(price) OVER () AS overall_avg
FROM products
LIMIT 10;

解説:

AVG(price) → 価格の平均を計算
OVER () → 括弧の中が空 = 全体を対象にする
全ての行に同じ平均価格が表示される

問題 2基本

価格順の順位付け

商品を価格の高い順に並べ、ROW_NUMBER()で順位を付けてください。

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

SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products
LIMIT 10;

解説:

ROW_NUMBER() → 連番をつける関数
ORDER BY price DESC → 価格の高い順に番号をつける
必ず連番（1, 2, 3, 4, 5...）になる

問題 3基本

カテゴリ平均の表示

各商品に、そのカテゴリの平均価格をウィンドウ関数で追加してください。

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

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

解説:

PARTITION BY category → カテゴリごとにグループ分けして計算
果物は果物の平均、野菜は野菜の平均...と、カテゴリごとに異なる平均が表示される
GROUP BYと違い、行数は減らない

問題 4応用

3つの順位関数の比較

価格の高い順に、ROW_NUMBER、RANK、DENSE_RANKの3つを同時に表示して違いを確認してください。

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

SELECT 
    product_name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
    RANK() OVER (ORDER BY price DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rank
FROM products
ORDER BY price DESC
LIMIT 15;

解説:

ROW_NUMBER → 必ず連番（1, 2, 3, 4, 5...）
RANK → 同点は同じ順位、次は飛ぶ（1, 2, 3, 3, 5...）
DENSE_RANK → 同点は同じ順位、次は飛ばない（1, 2, 3, 3, 4...）
同じ価格の商品がある行で違いが分かる

問題 5応用

カテゴリごとの順位

各カテゴリ内で価格の高い順に順位を付けてください。

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

SELECT 
    product_name,
    category,
    price,
    ROW_NUMBER() OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) AS rank_in_category
FROM products
ORDER BY category, rank_in_category;

解説:

PARTITION BY category → カテゴリごとにグループ分け
ORDER BY price DESC → 各グループ内で価格の高い順
カテゴリが変わると順位が1からリセットされる

問題 6応用

カテゴリごとのトップ3

各カテゴリで価格の高い商品トップ3だけを表示してください。

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

SELECT *
FROM (
    SELECT 
        product_name,
        category,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY price DESC
        ) AS rank
    FROM products
) ranked
WHERE rank <= 3
ORDER BY category, rank;

解説:

サブクエリで順位をつける（内側のクエリ）
外側のクエリで WHERE rank <= 3 で絞り込む
ウィンドウ関数の結果はWHERE句で直接使えないので、サブクエリが必要

問題 7応用

複数の統計を同時表示

各商品に、全体平均、カテゴリ平均、カテゴリ内最高価格を同時に表示してください。

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

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

解説:

複数のウィンドウ関数を同時に使用
OVER () → 全体を対象
OVER (PARTITION BY category) → カテゴリごと
1つのクエリで複数の統計を計算できる

問題 8チャレンジ

売上ランキング

商品ごとの総売上額を計算し、売上の多い順にランキングを付けてください。

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

SELECT 
    p.product_name,
    SUM(od.quantity * od.unit_price) AS total_sales,
    RANK() OVER (ORDER BY SUM(od.quantity * od.unit_price) DESC) AS sales_rank
FROM products p
LEFT JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name
ORDER BY sales_rank
LIMIT 10;

解説:

GROUP BYで商品ごとに集計
ウィンドウ関数はGROUP BYの後に実行される
集計結果に対して順位をつけることができる

問題 9チャレンジ

都道府県別優良顧客トップ5

各都道府県で総購入額の多い顧客トップ5を表示してください。

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

SELECT *
FROM (
    SELECT 
        c.customer_name,
        c.prefecture,
        SUM(o.total_amount) AS total_spent,
        RANK() OVER (
            PARTITION BY c.prefecture 
            ORDER BY SUM(o.total_amount) DESC
        ) AS rank_in_pref
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.prefecture
) ranked
WHERE rank_in_pref <= 5
ORDER BY prefecture, rank_in_pref;

解説:

PARTITION BY c.prefecture → 都道府県ごとにグループ分け
各都道府県内で独立したランキングがつく
サブクエリでランクを計算してから、上位5件を抽出

問題 10チャレンジ

複数のランキングを同時作成

顧客の購入回数順のランキングと、購入額順のランキングを同時に表示してください。

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

SELECT 
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent,
    ROW_NUMBER() OVER (ORDER BY COUNT(o.order_id) DESC) AS count_rank,
    ROW_NUMBER() OVER (ORDER BY SUM(o.total_amount) DESC) AS amount_rank
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 10;

解説:

1つのクエリで2つの異なるランキングを作成
count_rank → 購入回数順のランキング
amount_rank → 購入額順のランキング
同じ顧客でも、回数と金額で順位が異なることがある

📝 Step 6 のまとめ

✅ 学んだこと

ウィンドウ関数は行数を減らさずに集計できる（GROUP BYとの違い）
OVER句がウィンドウ関数の目印
PARTITION BYでグループごとに計算
ORDER BYで順序を指定
ROW_NUMBER：連番（必ず1, 2, 3...）
RANK：同点考慮、次は飛ぶ（1, 2, 2, 4...）
DENSE_RANK：同点考慮、次は飛ばない（1, 2, 2, 3...）
ウィンドウ関数の結果で絞り込むにはサブクエリが必要

📌 ウィンドウ関数の基本形

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

-- 全体を対象
関数() OVER ()

-- グループごと
関数() OVER (PARTITION BY 列名)

-- 順序付き（順位付け関数に必須）
関数() OVER (ORDER BY 列名)

-- グループごと + 順序
関数() OVER (PARTITION BY 列名 ORDER BY 列名)

-- トップNの抽出
SELECT * FROM (
    SELECT ..., ROW_NUMBER() OVER (...) AS rank
    FROM ...
) ranked
WHERE rank <= N;

💡 実務での使いどころ

ランキング作成：売上順位、成績順位など
トップN抽出：各カテゴリのトップ3商品など
グループ内統計：カテゴリ平均を各行に表示
複数のランキング：回数順と金額順を同時に表示
サブクエリの代替：相関サブクエリより効率的

⚠️ 注意点

WHERE句で直接使えない：サブクエリが必要
ORDER BY必須：順位付け関数には必ずORDER BYを書く
SQLite 3.25.0以降、MySQL 8.0以降で対応

❓ よくある質問

Q1: ウィンドウ関数とGROUP BYの使い分けは?

行数を減らしたい場合はGROUP BY、元のデータと集計結果を同時に見たい場合はウィンドウ関数を使います。

例えば、「カテゴリごとの平均価格だけを見たい」ならGROUP BY、「各商品の価格とカテゴリ平均を同時に見たい」ならウィンドウ関数です。

Q2: ROW_NUMBER、RANK、DENSE_RANKはどう選ぶ?

同点をどう扱うかで選びます。

ROW_NUMBER：同点でも区別したい時、必ずN件欲しい時
RANK：「何人抜いたか」を知りたい時
DENSE_RANK：「何種類あるか」を知りたい時

迷ったらROW_NUMBERを使うことが多いです。

Q3: PARTITION BYは必須ですか?

いいえ。PARTITION BYがない場合は全体が対象になります。全商品の平均価格を求めるなら、OVER()だけでOKです。

Q4: WHERE句でウィンドウ関数の結果を使えないのはなぜ?

SQLの実行順序が理由です。WHERE句はウィンドウ関数より先に実行されます。

FROM（テーブルを指定）
WHERE（絞り込み）← ここではまだウィンドウ関数は計算されていない
GROUP BY
ウィンドウ関数 ← ここで計算される
SELECT
ORDER BY

そのため、サブクエリで先にウィンドウ関数を計算してから、外側で絞り込む必要があります。

Q5: ウィンドウ関数は遅いですか?

適切に使えば高速です。複雑なサブクエリやJOINよりも効率的なことが多いです。

ただし、大量データの場合はインデックスを適切に設定することが重要です。

Q6: SQLiteでもウィンドウ関数は使えますか?

はい。SQLite 3.25.0以降で使えます。MySQL 8.0、PostgreSQL、SQL Serverなど、主要なデータベースは全て対応しています。

🎓 次のステップでは

Step 7: 集計ウィンドウ関数では、SUM、AVG、COUNT などの集計関数をウィンドウ関数として使う方法を学びます。累積売上の計算、移動平均の計算など、より高度な分析ができるようになります!

📝

学習メモ

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

📋 過去のメモ一覧 ▼