[SQLの基本]ウィンドウ関数入門: データの分析と集計の強力なツール

はじめに

ウィンドウ関数は、SQLの強力な機能の一つであり、行ごとにデータを分析しながら集計を行うことができます。これにより、データベースのデータを効率的に分析し、複雑なクエリをシンプルに記述することができます。今回は、SQLのウィンドウ関数の基本とその応用について解説します。

ウィンドウ関数とは？

ウィンドウ関数は、SELECT文の中で使用される関数で、指定したウィンドウ（範囲）のデータを基に計算を行います。これにより、特定の条件に基づいて集計を行ったり、ランキングを付けたりすることができます。

SELECT 
  column1, 
  column2, 
  window_function() OVER (PARTITION BY column_list ORDER BY column_list) AS alias
FROM table_name;

主なウィンドウ関数

ROW_NUMBER

ROW_NUMBER関数は、結果セット内の各行に一意の連番を付けます。

例 従業員のリストに対して各行に連番を付けるクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  ROW_NUMBER() OVER (ORDER BY last_name) AS row_num
FROM employees;

このクエリは、従業員の名前をアルファベット順に並べ、それぞれの行に連番を付けます。

RANK

RANK関数は、値の順序に基づいて順位を付けます。同じ値には同じ順位が付けられ、その次の順位はスキップされます。

例 従業員の給与に基づいて順位を付けるクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

このクエリは、給与の高い順に従業員に順位を付け、同じ給与の場合は同じ順位が付けられます。

DENSE_RANK

DENSE_RANK関数は、値の順序に基づいて順位を付けます。同じ値には同じ順位が付けられますが、次の順位はスキップされません。

例 従業員の給与に基づいて順位を付けるクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_dense_rank
FROM employees;

このクエリは、給与の高い順に従業員に順位を付け、同じ給与の場合は同じ順位が付けられ、次の順位はスキップされません。

NTILE

NTILE関数は、結果セットを指定した数のグループに分割します。

例 従業員のリストを給与順に4つのグループに分けるクエリは次の通りです。

SELECT 
  first_name, 
  last_name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

このクエリは、従業員のリストを給与の高い順に並べ、4つのグループに分割します。

LAG

LAG関数は、指定した行の前の値を取得します。

例 前月の給与を取得するクエリは次の通りです。

SELECT 
  first_name, 
  last_name, salary,
  LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_month_salary
FROM employees;

このクエリは、従業員の給与と前月の給与を取得します。

LEAD

LEAD関数は、指定した行の次の値を取得します。

例 次月の給与を取得するクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_month_salary
FROM employees;

このクエリは、従業員の給与と次月の給与を取得します。

SUM

SUM関数は、指定したウィンドウ内の値の合計を計算します。

例 各従業員の累積給与を計算するクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  SUM(salary) OVER (ORDER BY hire_date) AS cumulative_salary
FROM employees;

このクエリは、従業員の累積給与を計算して返します。

AVG

AVG関数は、指定したウィンドウ内の値の平均を計算します。

例 各従業員の平均給与を計算するクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  AVG(salary) OVER (ORDER BY hire_date) AS average_salary
FROM employees;

このクエリは、従業員の平均給与を計算して返します。

ウィンドウ関数の応用

部門ごとの順位

部門ごとに従業員の給与順位を付けるクエリは次の通りです。

SELECT 
  department_id, 
  first_name, 
  last_name, 
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

このクエリは、各部門内で給与の高い順に従業員に順位を付けます。

移動平均

指定したウィンドウ内での移動平均を計算するクエリは次の通りです。

SELECT 
  first_name, 
  last_name, 
  salary,
  AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_salary
FROM employees;

このクエリは、各従業員の移動平均給与を計算して返します。