ウィンドウ関数の概要
ウィンドウ関数と集約関数
ウィンドウ関数は、WHERE GROUP BY および HAVING 節が適用された後、SELECT クエリで選択された行に対して作用します。
ウィンドウ関数は、行のグループのフィールドの値を結合し、結果セットに生成された列の各行の値を返します。
ウィンドウ関数は、複数の行からの結果を結合するという点では集約関数と似ていますが、行自体を結合しないという点では集約関数と異なります。ただし、集約関数 AVG()、MIN()、MAX()、SUM() は、ウィンドウ関数としても呼び出されます。このコンテキストで、各行は、対応するウィンドウ・フレーム内の行のグループに対する関数の呼び出し結果を受け取ります。
ウィンドウ関数の構文
ウィンドウ関数は、SELECT クエリ内の select-item として指定されます。ウィンドウ関数は、SELECT クエリの ORDER BY 節に指定することもできます。
ウィンドウ関数は、PARTITION BY 節、ORDER BY 節、および ROWS 節により指定された行ごとのウィンドウに関連してタスクを実行し、各行に対して値を返します。これら 3 つの節はオプションですが、指定する場合は、次の構文に示す順序で指定する必要があります。
window-function() OVER ( [ PARTITION BY partfield ] [ ORDER BY orderfield ] [ ROWS framestart ] | [ ROWS BETWEEN framestart AND frameend ] )
ここで、framestart と frameend は以下のようにできます。
UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW | UNBOUNDED FOLLOWING | offset FOLLOWING
キーワードとウィンドウ関数の名前では、大文字と小文字は区別されません。
簡単な例
CityTable には以下の値の行が含まれます。
Name | City |
Able | New York |
Betty | Boston |
Charlie | Paris |
Davis | Boston |
Eve | Paris |
Francis | Paris |
George | London |
Beatrix | Paris |
ROW_NUMBER() ウィンドウ関数は、指定されたウィンドウに基づいて、各行に一意の連続した整数を割り当てます。
SELECT Name,City,ROW_NUMBER() OVER (PARTITION BY City) FROM CityTable
この例では、City の値で行を分割し、以下を返します。
Name | City | Window_3 |
Able | New York | 1 |
Betty | Boston | 1 |
Charlie | Paris | 1 |
Davis | Boston | 2 |
Eve | Paris | 2 |
Francis | Paris | 3 |
George | London | 1 |
Beatrix | Paris | 4 |
SELECT Name,City,ROW_NUMBER() OVER (ORDER BY City) FROM CityTable
この例では、すべての行を単一のパーティションとして扱います。City の値で行を並べ替え、以下を返します。
Name | City | Window_3 |
Able | New York | 4 |
Betty | Boston | 1 |
Charlie | Paris | 5 |
Davis | Boston | 2 |
Eve | Paris | 6 |
Francis | Paris | 7 |
George | London | 3 |
Beatrix | Paris | 8 |
SELECT Name,City,ROW_NUMBER() OVER (Partition BY City ORDER BY Name) FROM CityTable
この例では、City の値で行を分割し、各 City のパーティションを Name の値で並べ替え、以下を返します。
Name | City | Window_3 |
Able | New York | 1 |
Betty | Boston | 1 |
Charlie | Paris | 2 |
Davis | Boston | 2 |
Eve | Paris | 3 |
Francis | Paris | 4 |
George | London | 1 |
Beatrix | Paris | 1 |
NULL
PARTITION BY 節は、NULL (値が割り当てられていない) のフィールドを持つ行を、分割された 1 つのグループとして処理します。例えば、ROW_NUMBER() OVER (Partition BY City) は、City 値が 'Paris' の行に連続した整数を割り当てるのと同様に、City 値のない行に連続した整数を割り当てます。
ORDER BY 節は、NULL のフィールドを持つ行を、割り当てられているどの値よりも前に順序付けられている (最も小さい照合値を持つ) ものとして処理します。例えば、ROW_NUMBER() OVER (ORDER BY City) は、最初に City の値がない行に連続した整数を割り当ててから、City 値がある行に照合順で連続した整数を割り当てます。
ROWS 節は、NULL (値が割り当てられていない) のフィールドを、ゼロの値を持つものとして処理します。例えば、SUM(Scores) OVER (ORDER BY Scores ROWS 1 PRECEDING)/2 は、Scores 値 ((0 + 0) / 2) を持たないすべての行に 0.00 を割り当て、これに 0 を加えて 2 で割ることで最初の Scores 値を処理します。
サポートされているウィンドウ関数
以下のウィンドウ関数がサポートされます。
-
AVG(field) — 指定したウィンドウ・フレーム内の行の field 列の値の平均を、そのウィンドウ・フレームのすべての行に割り当てます。例えば、AVG(Salary) OVER (PARTITION BY Department) FROM Company.Employee を使用して、各従業員の給与を、その従業員の部門内の従業員の給与の平均と比較できます。AVG() は ROWS 節をサポートします。使用方法の詳細は、AVG() 関数のリファレンス・ページを参照してください。
-
COUNT( * | field ) — 指定したウィンドウ・フレーム内の各行に 1 から始まる数を割り当てます。field が指定されている場合、カウントは field のコンテンツが NULL でないときにのみインクリメントされます。この引数が指定されていない場合、カウントはすべての行でインクリメントされます。
-
CUME_DIST() — 指定したウィンドウ・フレーム内のすべての行に累積分布値を割り当てます。累積分布は、現在の行の値以下の値を持つ行をカウントし、そのカウントをウィンドウ内の行の総数で除算することによって計算されます。累積分布が計算される列の名前は、ORDER BY 節で指定されます。
-
DENSE_RANK() — 同じウィンドウ・フレーム内の各行に、1 から始まるランキングを表す整数を割り当てます。このランキングを表す整数は、RANK() ウィンドウ関数の場合とは異なり、常に連続した値となります。ウィンドウ関数フィールドに同じ値を含む行が複数ある場合は、ランキングの整数に重複値を含めることができます。
-
FIRST_VALUE(field) — 指定したウィンドウ・フレーム内の最初の行 (ROW_NUMBER()=1) の field 列の値を、そのウィンドウ・フレームのすべての行に割り当てます。例えば、FIRST_VALUE(Country) OVER (PARTITION BY City) のようになります。FIRST_VALUE() は ROWS 節をサポートします。すべての値の前に NULL を照合し、最初の行の field の値が NULL の場合にウィンドウ内のすべての行が NULL になるようにすることに注意してください。
-
LAST_VALUE(field) — 指定したウィンドウ・フレーム内の最後の行の field 列の値を、そのウィンドウ・フレームのすべての行に割り当てます。LAST_VALUE() は ROWS 節をサポートします。
-
LAG(field[, offset[, default]]) — 指定したウィンドウ・フレーム内の指定の行の offset 行前にある field 列の値を割り当てます。offset が指定されていない場合、この関数は、既定で、指定された行の 1 行前の field 列の値を割り当てます。既定では、ウィンドウ・フレーム内で、指定された行の offset 行前に行がない場合、LAG() は値 NULL を割り当てます。ユーザは、値 default を含めることにより、これらの条件下で、代替値を割り当てることもできます。
-
LEAD(field[, offset[, default]]) — 指定したウィンドウ・フレーム内の指定の行の offset 行後にある field 列の値を割り当てます。offset が指定されていない場合、この関数は、既定で、指定された行の 1 行後の field 列の値を割り当てます。既定では、ウィンドウ・フレーム内で、指定された行の offset 行後に行がない場合、LEAD() は値 NULL を割り当てます。ユーザは、値 default を含めることにより、これらの条件下で、代替値を割り当てることもできます。
-
MAX(field) — 指定したウィンドウ・フレーム内の field 列の最大値を、そのウィンドウ・フレームのすべての行に割り当てます。例えば、MAX(Salary) OVER (PARTITION BY Department) FROM Company.Employee を使用して、各従業員の給与を、その従業員の部門内の従業員の最も高い給与と比較できます。MAX() は ROWS 節をサポートします。使用方法の詳細は、MAX() 関数のリファレンス・ページを参照してください。
-
MIN(field) — 指定したウィンドウ・フレーム内の field 列の最小値を、そのウィンドウ・フレームのすべての行に割り当てます。例えば、MIN(Salary) OVER (PARTITION BY Department) FROM Company.Employee を使用して、各従業員の給与を、その従業員の部門内の従業員の最も低い給与と比較できます。MIN() は ROWS 節をサポートします。使用方法の詳細は、MIN() 関数のリファレンス・ページを参照してください。
-
NTH_VALUE(field, n) — 指定したウィンドウ・フレーム内の行番号 n (1 からカウント) の field 列の値を、そのウィンドウ・フレームのすべての行に割り当てます。NTH_VALUE() は ROWS 節をサポートします。
-
NTILE(num-groups) — 指定したウィンドウ・フレーム内の行を、それぞれの要素の数がほぼ等しい、num-groups 個のグループに分割します。各グループは、1 から始まる番号で識別されます。
-
PERCENT_RANK() — 同じウィンドウ・フレーム内の各行に、0 以上 1 以下の小数としてランキングのパーセンテージを割り当てます。ウィンドウ関数フィールドに同じ値を含む行が複数ある場合は、ランキングのパーセンテージに重複値を含めることができます。
-
RANK() — 同じウィンドウ・フレーム内の各行に、1 から始まるランキングを表す整数を割り当てます。ウィンドウ関数フィールドに同じ値を含む行が複数ある場合は、ランキングの整数に重複値を含めることができます。
-
ROW_NUMBER() — 同じウィンドウ・フレーム内の各行に、一意の連続した整数を割り当てます。ウィンドウ関数フィールドの複数の行に同じ値が含まれている場合は、各行に一意の連続した整数が割り当てられます。
-
SUM(field) — 指定したウィンドウ・フレーム内の field 列の値の合計を、そのウィンドウ・フレームのすべての行に割り当てます。SUM() は ROWS 節をサポートします。使用方法の詳細は、SUM() 関数のリファレンス・ページを参照してください。
以下の例では、これらのウィンドウ関数内の ORDER BY 節により返された値を比較します。
SELECT Name,City,ROW_NUMBER() OVER (ORDER BY City) AS RowNum,
RANK() OVER (ORDER BY City) AS RankNum,
PERCENT_RANK() OVER (ORDER BY City) AS RankPct
FROM CityTable ORDER BY City
この例では、すべての行を単一のパーティションとして扱います。City の値で行を並べ替え、以下を返します。
Name | City | RowNum | RankNum | RankPct |
Harriet | 1 | 1 | 0 | |
Betty | Boston | 2 | 2 | .1111111111111111111 |
Davis | Boston | 3 | 2 | .1111111111111111111 |
George | London | 4 | 4 | .3333333333333333333 |
Able | New York | 5 | 5 | .4444444444444444444 |
Charlie | Paris | 6 | 6 | .5555555555555555555 |
Eve | Paris | 7 | 6 | .5555555555555555555 |
Francis | Paris | 8 | 6 | .5555555555555555555 |
Beatrix | Paris | 9 | 6 | .5555555555555555555 |
Jackson | Rome | 10 | 10 | 1 |
ROWS 節
ROW 節は、AVG()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()、MIN()、MAX()、および SUM() ウィンドウ関数で使用できます。他のウィンドウ関数に対して指定することはできますが、機能しません (結果は ROWS 節があってもなくても同じです)。
ROWS 節には次の 2 つの構文形式があります。
ROWS framestart ROWS BETWEEN framestart AND frameend
framestart および frameend は、次の 5 つの値を取ることが可能です。
UNBOUNDED PRECEDING /* start at beginning of the current partition */ offset PRECEDING /* start offset number of rows preceding the current row */ CURRENT ROW /* start at the current row */ offset FOLLOWING /* continue offset number of rows following the current row */ UNBOUNDED FOLLOWING /* continue to the end of the current partition */
ROWS 節の構文は、いずれかの方向の範囲を指定できます。例えば、ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING と ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED PRECEDING はまったく同じです。
ROWS framestart 構文は、指定されていない 2 番目の範囲の境界として、既定で CURRENT ROW が設定されます。したがって、以下は同等です。
ROWS UNBOUNDED PRECEDING | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
ROWS 1 PRECEDING | ROWS BETWEEN 1 PRECEDING AND CURRENT ROW |
ROWS CURRENT ROW | ROWS BETWEEN CURRENT ROW AND CURRENT ROW |
ROWS 1 FOLLOWING | ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING |
ROWS UNBOUNDED FOLLOWING | ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING |
ROWS 節が指定されていない場合の既定値は、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。
ROWS 節の例
次のクエリは、多くの "ノイズ" (ランダム変数) を含むスコアを返します。ROWS 節を使用して、現在のスコアと照合順の直前のスコアおよび直後のスコアとを合計し、3 で割ることでこれらの変動を "平滑化" し、ゆるやかな変動の平均スコアを取得します。
SELECT Item,Score,SUM(Score)
OVER (ORDER BY Score ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)/3
AS CohortScore FROM Sample.TestScores
演算は (PrecedingScore + CurrentScore + FollowingScore)/3 のようになります。CohortScore の一番下と一番上の値は、2 つの Score 値に 0 を加えて 3 で割る ((0 + CurrentScore + FollowingScore)/3 および (PrecedingScore + CurrentScore + 0)/3) ため、正確ではありません。
ウィンドウ関数の使用
ウィンドウ関数は、以下のような箇所で使用できます。
-
リストされた select-item としての SELECT リスト。
ウィンドウ関数は、select-item リスト内のサブクエリまたは集約関数に組み込むことはできません。
ウィンドウ関数は、ON、WHERE、GROUP BY、または HAVING 節の中では使用できません。これを実行しようとすると、SQLCODE -367 エラーが返されます。
列の名前とエイリアス
既定では、ウィンドウ関数の結果に割り当てられる列名は Window_n となります。ここで、n という数字接尾語は、SELECT リストで指定されている列順序番号です。したがって、以下の例では、列名 Window_3 および Window_6 が作成されます。
SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State),Age,AVG(Age),ROW_NUMBER() OVER (ORDER BY Age)
FROM Sample.Person
別の列名 (列のエイリアス) を指定するには、AS キーワードを使用します。
SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person
列エイリアスを使用して、ORDER BY 節でウィンドウ・フィールドを指定できます。
SELECT Name,State,ROW_NUMBER() OVER (PARTITION BY State) AS StateRow,Age
FROM Sample.Person
ORDER BY StateRow
ORDER BY 節で既定の列名 (Window_3 など) を使用することはできません。
列エイリアスの詳細は、SELECT 文を参照してください。
ORDER BY の使用
ORDER BY 節は、ウィンドウ関数が評価された後にクエリの結果セットに適用されるため、ORDER BY は select-item ウィンドウ関数によって割り当てられた値に影響を与えません。