SQLウィンドウ関数

概要: このチュートリアルでは、複雑なクエリの課題を簡単な方法で解決するSQLウィンドウ関数について学習します。

SQLウィンドウ関数の概要

集計関数は、一連の行に対して計算を行い、単一の出力行を返します。

次のクエリでは、SUM() 集計関数を使用して、会社内のすべての従業員の給与の合計を計算します。

SELECT 
    SUM(salary) sum_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

出力は次のとおりです。

出力に明確に示されているように、employeesテーブルのすべての行は単一行にグループ化されます。

集計関数と同様に、ウィンドウ関数は一連の行に対して計算を行います。ただし、ウィンドウ関数は行を単一の出力行にグループ化しません。

次のクエリでは、SUM() をウィンドウ関数として使用します。これは、各従業員の給与とともに、すべての従業員の給与の合計を返します。

SELECT
    first_name,
    last_name,
    salary,
    SUM(salary) OVER() sum_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

部分的な出力は次のとおりです。

sql window functions - SUM window function example

この例では、OVER()句は、SUM()関数がウィンドウ関数として使用されていることを示します。

次の図は、集計関数とウィンドウ関数の主な違いを示しています。

SQLウィンドウ関数の構文

ウィンドウ関数の構文は次のとおりです。

window_function_name ( expression ) OVER (
    partition_clause
    order_clause
    frame_clause
)
Code language: SQL (Structured Query Language) (sql)

window_function_name

ROW_NUMBER()、RANK()、およびSUM()などの、サポートされているウィンドウ関数の名前。

expression

ウィンドウ関数が操作するターゲット式または列。

OVER句

OVER句は、ウィンドウパーティションを定義して行のグループを形成し、パーティション内の行の順序を指定します。 OVER句は、パーティション句、順序句、およびフレーム句の3つの句で構成されます。

パーティション句は、ウィンドウ関数が適用されるパーティションに行を分割します。構文は次のとおりです。

PARTITION BY expr1, expr2, ...
Code language: SQL (Structured Query Language) (sql)

PARTITION BY句が指定されていない場合、結果セット全体が単一のパーティションとして扱われます。

順序句は、ウィンドウ関数が操作するパーティション内の行の順序を指定します。

ORDER BY 
    expression [ASC | DESC]  [NULL {FIRST| LAST}]
    ,...
Code language: SQL (Structured Query Language) (sql)

フレームは、現在のパーティションのサブセットです。フレームを定義するには、次のいずれかの構文を使用します。

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end  
Code language: SQL (Structured Query Language) (sql)

ここで、frame_startは次のオプションのいずれかです。

N PRECEDING
UNBOUNDED PRECEDING
CURRENT ROW
Code language: SQL (Structured Query Language) (sql)

frame_endは次のオプションのいずれかです。

CURRENT ROW
UNBOUNDED FOLLOWING
N FOLLOWING
Code language: SQL (Structured Query Language) (sql)

次の図は、フレームとそのオプションを示しています。

UNBOUNDED PRECEDING: フレームはパーティションの最初の行で開始します。
N PRECEDING: フレームは現在の行のN行前で開始します。
CURRENT ROW: 評価中の現在の行を意味します。
UNBOUNDED FOLLOWING: フレームはパーティションの最後の行で終了します。
N FOLLOWING: フレームは現在の行の後のN行で終了します。

ROWSまたはRANGEは、現在の行とフレーム行の関係のタイプを指定します。

ROWS: 現在の行とフレーム行のオフセットは、行番号です。
RANGE: 現在の行とフレーム行のオフセットは、行の値です。

SQLウィンドウ関数の種類

ウィンドウ関数は、値ウィンドウ関数、集計ウィンドウ関数、ランキングウィンドウ関数の3つのタイプに分類されます。

値ウィンドウ関数

ランキングウィンドウ関数

集計ウィンドウ関数

AVG()
COUNT()
MAX()
MIN()
SUM()