SQL OVER 句とは？集計関数をレベルアップさせる魔法のツール

2024-04-17

SQL OVER() 句: いつ、なぜ役立つのか？

SQL OVER() 句は、集計関数やウィンドウ関数と呼ばれる特殊な関数を、特定の行または行のグループに対して適用できるようにする機能です。従来の集計関数は、テーブル全体またはサブクエリ全体に対してのみ適用できましたが、OVER() 句を使用すると、より柔軟なデータ分析が可能になります。

いつ役立つか？

OVER() 句は、以下の状況で役立ちます。

行または行のグループに関連する集計値を計算したい場合
行または行のグループをランキングまたは順序付けしたい場合
条件に基づいて集計値をフィルタリングしたい場合

例

行ベースの集計

従業員の給与テーブルがあるとします。各従業員の平均給与を求めるには、次のように OVER() 句を使用できます。

SELECT employee_id, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

このクエリは、各部門の平均給与を含む新しい列 avg_salary を作成します。

ウィンドウベースのランキング

販売データテーブルがあるとします。各製品の販売個数に基づいて、各製品をランク付けするには、次のように OVER() 句を使用できます。

SELECT product_id, sales_count, ROW_NUMBER() OVER (ORDER BY sales_count DESC) AS rank
FROM sales;

このクエリは、各製品の販売個数に基づいてランクを示す新しい列 rank を作成します。

条件付き集計

SELECT customer_id, order_amount, SUM(order_amount) OVER (PARTITION BY customer_id WHERE order_status = 'shipped') AS total_shipped_amount
FROM orders;

このクエリは、各顧客の発送済み注文の合計金額を含む新しい列 total_shipped_amount を作成します。

SQL Server での OVER() 句の使用

SQL Server 2005 以降では、OVER() 句がサポートされています。OVER() 句を使用するには、まずウィンドウフレームを定義する必要があります。ウィンドウフレームは、集計関数やウィンドウ関数が適用される行の範囲を指定します。

ウィンドウフレームには、以下の種類があります。

ROWS: 指定された行数
RANGE: 指定された行範囲
GROUPS: 指定されたグループ

ウィンドウフレームを定義するには、OVER() 句の PARTITION BY 句または ORDER BY 句を使用します。

SQL OVER() 句は、集計関数やウィンドウ関数をより柔軟に使用できるようにする強力な機能です。この機能を使用すると、複雑なデータ分析をより簡単に実行できます。

SQL OVER 句を使用したサンプルコード

従業員テーブル employees が以下の構造を持つと仮定します。

employee_id | department | salary
------------+------------+--------
1           | Sales      | 50000
2           | Engineering| 60000
3           | Sales      | 40000
4           | Marketing | 55000
5           | Engineering| 52000

このテーブルの各部門における平均給与を計算するには、次のクエリを実行します。

SELECT employee_id, department, salary, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

このクエリは以下の結果を返します。

employee_id | department | salary | avg_salary
------------+------------+--------+-----------
1           | Sales      | 50000 | 45000.00
2           | Engineering| 60000 | 56000.00
3           | Sales      | 40000 | 45000.00
4           | Marketing | 55000 | 55000.00
5           | Engineering| 52000 | 56000.00

例2：販売データテーブルの製品別販売個数ランキング

product_id | sales_count
------------+------------
1           | 100
2           | 50
3           | 120
4           | 75
5           | 25

このテーブルの製品別販売個数に基づいて製品をランク付けするには、次のクエリを実行します。

SELECT product_id, sales_count, ROW_NUMBER() OVER (ORDER BY sales_count DESC) AS rank
FROM sales;

product_id | sales_count | rank
------------+------------+-----
3           | 120        | 1
1           | 100        | 2
4           | 75         | 3
2           | 50         | 4
5           | 25         | 5

例3：顧客注文テーブルの顧客別発送済み注文金額合計

customer_id | order_id   | order_amount | order_status
------------+------------+------------+-------------
1           | 1001       | 100.00      | Shipped
1           | 1002       | 50.00       | Pending
2           | 2001       | 200.00      | Shipped
2           | 2002       | 150.00      | Canceled
3           | 3001       | 300.00      | Shipped

SELECT customer_id, order_id, order_amount, SUM(order_amount) OVER (PARTITION BY customer_id WHERE order_status = 'shipped') AS total_shipped_amount
FROM orders;

customer_id | order_id   | order_amount | total_shipped_amount
------------+------------+------------+----------------------
1           | 1001       | 100.00      | 100.00
1           | 1002       | 50.00       | 100.00
2           | 2001       | 200.00      | 200.00
2           | 2002       | 150.00      | 200.00
3           | 3001       | 300.00      | 300.00

これらの例は、SQL OVER 句を使用してさまざまな種類のデータ分析を実行できることを示しています。

特定の期間内の売上合計を計算する
特定の顧客の注文履歴を表示する
在庫状況を追跡する

SQL OVER 句の代替方法

サブクエリを使用して、関連する行の集計値を計算できます。ただし、サブクエリはクエリを複雑にし、パフォーマンスを低下させる可能性があります。

例：従業員テーブルの部門別平均給与

従業員テーブル employees の各部門における平均給与をサブクエリを使用して計算するには、次のクエリを実行します。

SELECT employee_id, department, salary, (
  SELECT AVG(salary)
  FROM employees AS e2
  WHERE e2.department = e.department
) AS avg_salary
FROM employees AS e;

カーソルを使用して、行を反復処理し、集計値を計算できます。ただし、カーソルは非効率的で、コードが冗長になる可能性があります。

DECLARE @rank INT;
DECLARE @sales_count INT;

SELECT product_id, sales_count
FROM sales
ORDER BY sales_count DESC;

OPEN cursor cur_sales;

FETCH NEXT FROM cur_sales INTO @product_id, @sales_count;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @rank = @rank + 1;

  UPDATE sales
  SET rank = @rank
  WHERE product_id = @product_id;

  FETCH NEXT FROM cur_sales INTO @product_id, @sales_count;
END;

CLOSE cur_sales;
DEALLOCATE cur_sales;

COMMON TABLE EXPRESSIONS (CTE)

CTEを使用して、複雑なクエリをより小さな、より管理しやすい部分に分割できます。ただし、CTEは新しいSQL Serverバージョンでのみサポートされていることに注意してください。

WITH customer_shipped_orders AS (
  SELECT customer_id, SUM(order_amount) AS total_shipped_amount
  FROM orders
  WHERE order_status = 'shipped'
  GROUP BY customer_id
)
SELECT o.customer_id, o.order_id, o.order_amount, cs.total_shipped_amount
FROM orders AS o
JOIN customer_shipped_orders AS cs ON o.customer_id = cs.customer_id;