SQLにおけるウィンドウ関数とは

SQLにおけるウィンドウ関数は、現在の行に関連するある種の関数であり、テーブルの行のセット全体に対して計算を行います。ウィンドウ関数を使用することで、各入力行に固有の結果を得ることができ、これを利用して複雑な問題を比較的簡単に解決することができます。ウィンドウ関数は、データ分析の領域で広く利用されています。

ウィンドウ関数の目的

ウィンドウ関数の主な目的は、特定の値でパーティション分割され、他の値で順序付けられたグループの上で何かを計算する必要がある問題をデータアナリストに提供する強力なツールを提供することです。例えば、移動平均、累積合計、特定のフィールドのランニングトータルなどを計算することができます。

ウィンドウ関数の主な利点の一つは、現在の行に関連する行を跨いで計算を行う能力です。これに対して、他のタイプのSQL操作では、行は独立して処理されます。

パーティションとフレームの概念

パーティション

SQLにおけるパーティションは、総合的な行のセットをより小さなセットに分割するものです。ウィンドウ関数において、この操作はPARTITION BY句によって行われます。

例として、会社の売上データを含むテーブルsalesを考えてみます。

このテーブルをRegion列でパーティション分割する場合は以下のようになります。

SELECT Region, Sales,
SUM(Sales) OVER (PARTITION BY Region) AS total_sales
FROM sales;

結果セットはEastとWestの2つのパーティションに分割され、売上の合計が各パーティションに対して計算されます。

フレーム

フレームは、現在のパーティションのサブセットであり、「スライディングウィンドウ」と考えることができます。フレームはパーティション上を移動し、ウィンドウ関数の計算における現在の行セットを定義します。フレームはウィンドウ関数のROWS句によって定義されます。

フレームの理解を深めるために、salesテーブルを使います。各地域内の売上の累積を計算したい場合を考えます。

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

フレーム、または「ウィンドウ」は、各パーティションの最初の行（UNBOUNDED PRECEDING）から現在の行（CURRENT ROW）で開始し、実質的にランニングトータルを作成します。

ウィンドウ関数の構文

OVER句

OVER句は、ウィンドウ関数が適用されるウィンドウまたは行セットを定義します。これはウィンドウ関数と共に使用されます。OVER句を使用しない場合、ウィンドウ関数は機能しません。ウィンドウは、ORDER BY句とPARTITION BY句によって定義されます。

例えば、会社の各部門の総給与を求めたい場合、次のようなSQLクエリを使用できます。

SELECT department, salary,
SUM(salary) OVER (PARTITION BY department) AS total_salary
FROM employees;

これにより、各行に部門、個々の給与、およびその部門の総給与が含まれる結果が返されます。

PARTITION BY句

PARTITION BY句は、結果セットをパーティション（またはグループ）に分割します。ウィンドウ関数は、各パーティションに個別に適用され、パーティションごとに計算が再開されます。

例えば、各部門で最高の給与を計算したい場合、次のように書きます。

SELECT department, salary,
MAX(salary) OVER (PARTITION BY department) AS highest_salary
FROM employees;

これにより、各行に部門、従業員の給与、およびその部門での最高給与が含まれるテーブルが返されます。

ORDER BY句

OVER句内のORDER BY句は、ウィンドウ関数内の各パーティション内の行の順序を定義します。これにより、ウィンドウ関数の結果が変わることがあります。

例えば、各部門での累積給与を求めるために次のクエリを使用できます。

SELECT department, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS cumulative_salary
FROM employees;

これにより、各行に部門、従業員の給与、およびその部門での給与の累積が給与順に並べられたテーブルが返されます。

ROWS句

SQLのROWS句は、現在の入力行の位置からの物理的なオフセットを使用して、パーティション内のウィンドウフレームを定義します。異なるキーワードを使用して、このウィンドウフレームを定義します。

UNBOUNDED PRECEDING

このキーワードは、ウィンドウフレームがパーティションの最初の行で始まることを指定します。

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

この場合、各行に対するrunning_totalは、パーティションの開始から現在の行までの全ての売上を含みます。

n PRECEDING

ウィンドウフレームがパーティション内の現在の行のn行前から始まることを指定します。例えば、nが2の場合は以下のようになります。

SELECT Salesperson, Region, Sales,
AVG(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS average_sales
FROM sales;

ここでは、各行のaverage_salesは、同じパーティション内の現在の行と直前の2つの行の平均です。

CURRENT ROW

パーティション内の現在の行を表します。ウィンドウフレームは現在の行で終了または開始することができます。

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total
FROM sales;

ここでは、各行のrunning_totalは、現在の行からパーティションの最後までの売上の合計です。

n FOLLOWING

ウィンドウフレームがパーティション内の現在の行のn行後で終了することを指定します。

SELECT Salesperson, Region, Sales,
AVG(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS average_sales
FROM sales;

ここでは、各行のaverage_salesは、同じパーティション内の現在の行と次の行の平均です。

UNBOUNDED FOLLOWING

ウィンドウフレームがパーティションの最後の行で終了することを指定します。

SELECT Salesperson, Region, Sales,
SUM(Sales) OVER (PARTITION BY Region ORDER BY Salesperson ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS running_total
FROM sales;

この場合、各行のrunning_totalには、現在の行からパーティションの最後までの売上が含まれます。

RANGE BETWEEN句

ウィンドウ関数のRANGE BETWEEN句は、現在の行と同じORDER BY値を持つ行のフレーム（セット）を定義し、パーティション内のn PRECEDINGおよびn FOLLOWING行を対象とします。これは、データの論理的な表現に対して操作を行います。

RANGE BETWEEN句を使用するには、ウィンドウ関数内にORDER BY句が必要です。例えば、各セールスパーソンの総売上と、ちょうどその下とその上の2人のセールスパーソンの売上を計算したい場合を考えます。

SELECT Salesperson, Sales,
SUM(Sales) OVER (ORDER BY Sales RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sales_range_total
FROM sales;

これにより、各行にセールスパーソン、その売上、および彼らの売上とちょうどその下とその上のセールスパーソンの売上を含むsales_range_totalが含まれる結果が返されます。

ウィンドウ関数とGROUP BYの比較

SQLのウィンドウ関数とGROUP BY句は、データの集計に使用されるツールですが、目的や振る舞いが異なります。

GROUP BY

GROUP BY句は、SUM、AVG、MAX、MIN、COUNTなどの集計関数とともに使用され、結果セットを1つ以上の列でグループ化します。GROUP BYを使用すると、データベースシステムはSELECTステートメントから返された行をグループに分割します。各グループに対して、集計関数を適用することができます。

例えば、salesテーブルをSalesperson列でグループ化し、各セールスパーソンの売上合計を計算することができます。

SELECT Salesperson, SUM(Sales) as total_sales
FROM sales
GROUP BY Salesperson;

これにより、各行にセールスパーソンとその合計売上が含まれる結果が返されます。

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

GROUP BYとウィンドウ関数の基本的な違いは、データセットの行をどのように扱うかです。

• GROUP BYを使用すると、行は出力行の小さなセットにグループ化され、個別の識別情報を失います。集計関数は、グループごとに1つの出力行を返します。
• 対照的に、ウィンドウ関数では、各行は個別の識別情報を保持し、固有の結果を持つ可能性があります。ウィンドウ関数は、現在の行に関連する行のセットを対象に計算を行います。

例えば、各セールスパーソンの総売上と平均売上を計算したいとします。GROUP BYを使用する場合、次のように書くことができます。

SELECT Salesperson, SUM(Sales) as total_sales, AVG(Sales) as average_sales
FROM sales
GROUP BY Salesperson;

これにより、各行にセールスパーソン、総売上、平均売上が含まれる結果が返されます。

しかし、これらの総売上と平均売上を元のテーブルの各行に含めたい場合はどうでしょうか。ここでウィンドウ関数が登場します。

SELECT Salesperson, Sales,
SUM(Sales) OVER (PARTITION BY Salesperson) as total_sales,
AVG(Sales) OVER (PARTITION BY Salesperson) as average_sales
FROM sales;

これにより、各行にセールスパーソン、売上、総売上、平均売上が含まれる結果が返されます。