はじめに
SQLでデータを集計・分析する際の機能を提供してくれるのがウィンドウ関数です。その中でもOVER句は、ウィンドウ関数の適用範囲や順序を指定するための重要な構文です。
この記事では、OVER句の基本から、PARTITION BYやORDER BYを組み合わせた高度な使い方までを詳しく解説します。実務で役立つサンプルコードも豊富に紹介しますので、ぜひ最後までご覧ください。
目次
- OVER句とは
- OVER句の基本的な使い方
- PARTITION BYの活用法
- ORDER BYによる並び替えと集計
- ウィンドウ関数の具体例
- まとめ
1. OVER句とは
OVER句は、ウィンドウ関数と組み合わせて使用され、データの集計や分析を行う際に、どの範囲（ウィンドウ）に対して関数を適用するかを指定するためのものです。
ウィンドウ関数の一般的な構文：
<ウィンドウ関数> OVER (
[PARTITION BY <列名>]
[ORDER BY <列名>]
[ROWS or RANGE <フレーム句>]
)
- ウィンドウ関数：SUM、AVG、COUNT、ROW_NUMBER、RANKなど
- PARTITION BY：データをグループ化する（小さなウィンドウに分割）
- ORDER BY：ウィンドウ内での行の順序を指定
- フレーム句：ウィンドウ内でさらに範囲を指定（省略可能）
2. OVER句の基本的な使い方
まずは、OVER句を使用しない場合と使用した場合の違いを見てみましょう。
集計関数を使用した例（OVER句なし）
SELECT
department,
COUNT(employee_id) AS employee_count
FROM
employees
GROUP BY
department;
結果：
| department | employee_count |
|---|---|
| Sales | 50 |
| HR | 20 |
| IT | 30 |
解説：
- 部署ごとに従業員数を集計。
- 結果は各部署ごとの集計のみ。
ウィンドウ関数を使用した例（OVER句あり）
SELECT
employee_id,
department,
COUNT(employee_id) OVER (PARTITION BY department) AS employee_count_in_department
FROM
employees;
結果：
| employee_id | department | employee_count_in_department |
|---|---|---|
| 1 | Sales | 50 |
| 2 | Sales | 50 |
| ... | ... | ... |
| 71 | HR | 20 |
| ... | ... | ... |
解説：
- 各従業員の行に、所属する部署の従業員数を表示。
- PARTITION BY department により、部署ごとにウィンドウを作成。
3. PARTITION BYの活用法
PARTITION BY は、データを指定した列でグループ化し、それぞれのグループごとにウィンドウ関数を適用します。
例：部署ごとの平均給与を各従業員の行に表示
SELECT
employee_id,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary_in_department
FROM
employees;
解説：
- 各従業員の行に、その部署の平均給与を表示。
- 部署ごとの給与水準を確認できる。
4. ORDER BYによる並び替えと集計
ORDER BY は、ウィンドウ内での行の順序を指定し、累積和や順位付けなどに利用します。
例1：売上データの累積和を計算
SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM
sales;
解説：
- 日付順に並べ、売上金額の累積和を計算。
- ビジネスの成長を視覚化するのに役立つ。
例2：順位を付ける
SELECT
employee_id,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM
employees;
解説：
- 給与の高い順に順位を付ける。
- 同じ給与額の場合、同順位となる（RANK関数の特徴）。
5. ウィンドウ関数の具体例
5.1 ROW_NUMBER()で連番を振る
SELECT
employee_id,
department,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY hire_date) AS employee_number_in_department
FROM
employees;
解説：
- 部署ごとに、入社日の順で連番を振る。
- 部署内での入社順を知ることができる。
5.2 LAGとLEADで前後の行の値を取得
SELECT
employee_id,
salary,
LAG(salary, 1) OVER (ORDER BY salary DESC) AS previous_salary,
LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM
employees;
解説：
- 給与が高い順に並べ、前後の従業員の給与を取得。
- 給与の差異を分析するのに便利。
5.3 窓枠（フレーム）を指定して移動平均を計算
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_average
FROM
sales;
解説：
- 過去2日間と当日の売上の平均を計算。
- 短期的な売上傾向を把握する。
6. まとめ
OVER句を活用することで、従来の集計関数では難しかった行ごとの集計や、累積計算、順位付けなどが容易に行えます。PARTITION BYやORDER BYを組み合わせることで、データ分析の幅が大きく広がります。
ポイント
- PARTITION BY：データをグループ化し、グループごとにウィンドウ関数を適用。
- ORDER BY：ウィンドウ内での行の順序を指定し、順位付けや累積計算を可能に。
- ウィンドウ関数：SUM、AVG、COUNT、RANK、ROW_NUMBER、LAG、LEADなど、多彩な関数が利用可能。
おわりに
ウィンドウ関数とOVER句について紹介しました。ぜひこの記事を参考に、実際のデータで試してみてください。
ご質問やコメントがあれば、お気軽にお寄せください！