SQLのウィンドウ関数（WINDOW FUNCTION）を完全ガイド！初心者でもわかるデータ分析

新人と先輩の会話形式で理解しよう

新人

「SQLでランキングを作成したり、前後のデータを比較する方法ってありますか？」

先輩

「それならウィンドウ関数（WINDOW FUNCTION）を使うといいよ！通常の集計関数とは違って、グループごとのデータを個別に扱えるんだ。」

新人

「ウィンドウ関数って何ですか？」

先輩

「ウィンドウ関数を使うと、特定のグループごとにランキングをつけたり、合計を出したりできるんだよ。実際にテーブルを見ながら説明するね！」

1. ウィンドウ関数とは？

ウィンドウ関数（WINDOW FUNCTION）とは、通常の集約関数（SUM, AVG, COUNTなど）とは異なり、各行ごとにデータを計算できるSQLの機能です。

例えば、以下のようなランキングや合計を求めることができます。

ウィンドウ関数の主な用途

ランキング作成（RANK, DENSE_RANK, ROW_NUMBER）
移動平均の計算
累積合計の取得
前後のデータの取得（LEAD, LAG）

実際のデータを使って、ウィンドウ関数の使い方を説明します。

1. sales（売上テーブル）

sales_id	employee	department	sales_amount
1	田中太郎	営業	500000
2	佐藤花子	営業	700000
3	鈴木一郎	営業	600000
4	高橋直子	マーケティング	550000
5	山田健	マーケティング	650000

このデータを基に、ランキングを作成してみましょう。

2. RANK, DENSE_RANK, ROW_NUMBERの基本的な使い方

ウィンドウ関数を使うと、売上ランキングを作成できます。

1. RANK（順位をつける）

RANK関数は、同じ値があった場合に同じ順位をつけるが、次の順位はスキップされます。


SELECT employee, department, sales_amount,
       RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales;

RANKの実行結果

employee	department	sales_amount	rank
佐藤花子	営業	700000	1
山田健	マーケティング	650000	2
鈴木一郎	営業	600000	3
高橋直子	マーケティング	550000	4
田中太郎	営業	500000	5

2. DENSE_RANK（連番の順位）

DENSE_RANK関数は、同じ順位がついても、次の順位はスキップされない特徴があります。


SELECT employee, department, sales_amount,
       DENSE_RANK() OVER (ORDER BY sales_amount DESC) AS rank
FROM sales;

DENSE_RANKの実行結果

employee	department	sales_amount	rank
佐藤花子	営業	700000	1
山田健	マーケティング	650000	2
鈴木一郎	営業	600000	3
高橋直子	マーケティング	550000	4
田中太郎	営業	500000	5

3. ROW_NUMBER（ユニークな連番）

ROW_NUMBERは、すべての行にユニークな番号を振るため、同じ値でも順位は連番になります。


SELECT employee, department, sales_amount,
       ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS row_num
FROM sales;

次のセクションでは、PARTITION BYを使ってグループ別にランキングをつける方法を紹介します。

4. PARTITION BYを使ったグループ別集計

ウィンドウ関数では、PARTITION BYを使うことで、特定のグループごとにランキングや集計を行うことができます。

1. PARTITION BYの基本

例えば、salesテーブルのデータを部署ごとにランキングする場合、PARTITION BYを使います。


SELECT employee, department, sales_amount,
       RANK() OVER (PARTITION BY department ORDER BY sales_amount DESC) AS department_rank
FROM sales;

2. 実行結果（部署ごとのランキング）

employee	department	sales_amount	department_rank
佐藤花子	営業	700000	1
鈴木一郎	営業	600000	2
田中太郎	営業	500000	3
山田健	マーケティング	650000	1
高橋直子	マーケティング	550000	2

このように、PARTITION BYを使うと、部署ごとにランキングが作成されます。

5. LEAD, LAG関数を使った前後のデータ参照

LEADとLAG関数を使うと、前後のデータを参照できます。

1. LAG（前の行のデータを取得）

例えば、LAG関数を使って、前月の売上を取得できます。


SELECT employee, sales_amount,
       LAG(sales_amount, 1) OVER (ORDER BY sales_amount DESC) AS previous_sales
FROM sales;

2. 実行結果（前の行の売上を取得）

employee	sales_amount	previous_sales
佐藤花子	700000	NULL
山田健	650000	700000
鈴木一郎	600000	650000
高橋直子	550000	600000
田中太郎	500000	550000

3. LEAD（次の行のデータを取得）

逆に、LEADを使うと、次の行のデータを取得できます。


SELECT employee, sales_amount,
       LEAD(sales_amount, 1) OVER (ORDER BY sales_amount DESC) AS next_sales
FROM sales;

4. 実行結果（次の行の売上を取得）

employee	sales_amount	next_sales
佐藤花子	700000	650000
山田健	650000	600000
鈴木一郎	600000	550000
高橋直子	550000	500000
田中太郎	500000	NULL

このように、LAGとLEADを使うと、過去や未来のデータを取得できます。

6. SUM, AVG, COUNTなどの集計関数をウィンドウ関数で活用する

ウィンドウ関数では、SUM, AVG, COUNTなどの集計関数も利用できます。

1. 累積売上を計算（SUM）

SUMをウィンドウ関数として使うと、累積売上を計算できます。


SELECT employee, sales_amount,
       SUM(sales_amount) OVER (ORDER BY sales_amount DESC) AS cumulative_sales
FROM sales;

2. 実行結果（累積売上）

employee	sales_amount	cumulative_sales
佐藤花子	700000	700000
山田健	650000	1350000
鈴木一郎	600000	1950000
高橋直子	550000	2500000
田中太郎	500000	3000000

このように、ウィンドウ関数を使うことで、集計結果を各行ごとに取得できます。

次のセクションでは、ROWSやRANGEを活用したデータ分析について解説します。

7. フレーム指定（ROWS, RANGE）の活用方法

ウィンドウ関数では、ROWSやRANGEを使用して、特定の範囲内で計算を行うことができます。

1. ROWSを使った移動合計

ROWSを指定すると、特定の行数だけを対象にした計算が可能です。

例えば、直近3件の売上を合計するには、次のように記述します。


SELECT employee, sales_amount,
       SUM(sales_amount) OVER (ORDER BY sales_amount DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_sum
FROM sales;

2. 実行結果（直近3件の売上合計）

employee	sales_amount	moving_sum
佐藤花子	700000	700000
山田健	650000	1350000
鈴木一郎	600000	1950000
高橋直子	550000	1800000
田中太郎	500000	1650000

3. RANGEを使った累積合計

RANGEを使用すると、同じ値を持つすべての行を対象に計算できます。


SELECT employee, sales_amount,
       SUM(sales_amount) OVER (ORDER BY sales_amount DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM sales;

この方法を使うと、累積売上が計算できます。

8. ウィンドウ関数とJOIN, GROUP BYの組み合わせ

ウィンドウ関数は、JOINやGROUP BYと組み合わせることで、より高度な分析が可能になります。

1. JOINを活用した売上ランキング

以下のように、売上データをdepartmentsテーブルと結合し、部署ごとにランキングを作成できます。


SELECT e.employee, d.department_name, e.sales_amount,
       RANK() OVER (PARTITION BY d.department_name ORDER BY e.sales_amount DESC) AS department_rank
FROM sales e
JOIN departments d ON e.department_id = d.department_id;

この方法により、部署ごとに売上ランキングを作成できます。

2. GROUP BYとウィンドウ関数の違い

通常のGROUP BYを使うと、各グループごとに1行のデータしか取得できませんが、ウィンドウ関数を使うと、各行ごとに計算結果を保持できます。


SELECT employee, sales_amount, 
       SUM(sales_amount) OVER (PARTITION BY department) AS total_sales
FROM sales;

この方法では、各社員の行に対して、部署ごとの売上合計を追加できます。

9. 実践！ウィンドウ関数を活用したデータ分析テクニック

最後に、実際のデータ分析で役立つウィンドウ関数の活用例を紹介します。

1. 直近3ヶ月の売上の移動平均を計算

次のSQLでは、ROWS BETWEEN 2 PRECEDING AND CURRENT ROWを使って、直近3ヶ月の売上の移動平均を計算します。


SELECT employee, sales_amount,
       AVG(sales_amount) OVER (ORDER BY sales_amount DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

2. 前月比の売上増減を計算

LAG関数を使い、前月の売上と比較することで、売上の増減を計算できます。


SELECT employee, sales_amount,
       sales_amount - LAG(sales_amount, 1) OVER (ORDER BY sales_amount DESC) AS sales_diff
FROM sales;

この方法を使うと、前月比の売上変動を把握できます。

3. 50%以上の売上を占める社員を特定

累積売上を計算し、全体の売上の50%以上を占める社員を特定します。


SELECT employee, sales_amount,
       SUM(sales_amount) OVER (ORDER BY sales_amount DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales,
       SUM(sales_amount) OVER () AS total_sales
FROM sales
HAVING cumulative_sales / total_sales >= 0.5;

このクエリにより、売上の上位50%を占める社員を特定できます。

4. よくあるエラーとその対策

エラー1: ORDER BYなしでウィンドウ関数を実行しようとするとエラーになる
エラー2: GROUP BYとウィンドウ関数を誤って組み合わせると、意図しない結果になる
対策: 必ずPARTITION BYとORDER BYを適切に設定する

ウィンドウ関数は、データ分析やビジネスインテリジェンスで非常に役立ちます。適切に使いこなして、より高度なデータ処理を行いましょう！