Skip to main content

This documentation is for an older version of this product. See the latest version of this content.Opens in a new tab

ウィンドウ関数の概要

集約およびランキングの計算のための行当たりの "ウィンドウ・フレーム" を指定する関数。

ウィンドウ関数と集約関数

ウィンドウ関数は、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 ]                       )

ここで、framestartframeend は以下のようにできます。

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() 関数のリファレンス・ページを参照してください。

  • 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 節をサポートします。

  • 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 FOLLOWINGROWS 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 リスト内のサブクエリまたは集約関数に組み込むことはできません

  • ORDER BY 節

ウィンドウ関数は、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 BYselect-item ウィンドウ関数によって割り当てられた値に影響を与えません。

関連項目


FeedbackOpens in a new tab