7.24.1　ウィンドウ関数の指定形式

(a)　LAG関数およびLEAD関数

LAG関数：：＝LAG(対象データ〔,オフセット〔,既定値〕〕) 〔ナル値動作〕
LEAD関数：：＝LEAD(対象データ〔,オフセット〔,既定値〕〕) 〔ナル値動作〕

指定規則を次に示します。

• 対象データおよび既定値には，次のどれかのデータ型の値式を指定してください。

  • 数データ

  • 文字データ

  • 日時データ

  • バイナリデータ

• 対象データには，？パラメタを単独で指定できません。

• オフセットを省略した場合，オフセットには1が仮定されます。

• 既定値を省略した場合，既定値にはナル値が仮定されます。

• 既定値に？パラメタを単独で指定した場合，？パラメタのデータ型には対象データのデータ型が仮定されます。

• 対象データと既定値のデータ型は，比較できるデータ型にしてください。比較できるデータ型については，「6.2.2　変換，代入，比較できるデータ型」の「(1)　比較できるデータ型」を参照してください。

  ただし，対象データおよび既定値が，文字データまたは日時データの場合，指定できるデータ型の組み合わせは，次の表に示す組み合わせになります。

  表7‒34　対象データおよび既定値が文字データまたは日時データの場合に指定できるデータ型の組み合わせ

  対象データ		既定値
  		文字データ				日時データ
  		日付データの既定の入力表現である文字列定数	時刻データの既定の入力表現である文字列定数	時刻印データの既定の入力表現である文字列定数	左記以外	日付データ	時刻データ	時刻印データ
  文字データ	日付データの既定の入力表現である文字列定数	〇	〇	〇	〇	×	×	×
  	時刻データの既定の入力表現である文字列定数	〇	〇	〇	〇	×	×	×
  	時刻印データの既定の入力表現である文字列定数	〇	〇	〇	〇	×	×	×
  	上記以外	〇	〇	〇	〇	×	×	×
  日時データ	日付データ	〇	×	〇	×	〇	×	〇
  	時刻データ	×	〇	×	×	×	〇	×
  	時刻印データ	〇	×	〇	×	〇	×	〇

  （凡例）

  〇：指定できます。

  ×：指定できません。

• ナル値動作にRESPECT NULLSを指定した場合，順序付けされた行の集合内で，対象データの結果がナル値となる行は取り除かれずに評価されます。ナル値動作にIGNORE NULLSを指定した場合，順序付けされた行の集合内で，対象データの結果がナル値となる行は取り除かれて評価されます。

• ナル値動作を省略した場合，RESPECT NULLSが仮定されます。

(b)　ウィンドウ分割句

ウィンドウ分割句：：＝PARTITION BY 値式〔,値式〕…

値式の結果を用いて，表式の結果を区画分けします。ウィンドウ分割句を省略した場合，表式のすべての結果が1つのウィンドウ（区画）になります。

ウィンドウ分割句の機能概要を次の図に示します。

図7‒4　ウィンドウ分割句の機能概要

指定規則を次に示します。

• ウィンドウ分割句には，列指定を含む値式を指定してください。

• ウィンドウ分割句には，最大16個の値式を指定できます。

• ウィンドウ分割句に単独の列指定を指定する場合，同じ列は指定できません。

• ウィンドウ分割句の値式には，バイナリデータおよび配列データを指定できません。

(c)　ウィンドウ順序句

ウィンドウ順序句：：＝ORDER BY ソート指定リスト

ウィンドウ（区画）内のデータを順序付け（ソート）する場合に指定します。ソート指定リストの指定形式，および指定規則については，「7.25　ソート指定リスト」を参照してください。

ウィンドウ順序句の機能概要を次の図に示します。

図7‒5　ウィンドウ順序句の機能概要

指定規則を次に示します。

• ソート指定リストのソートキーに指定できるデータ型を次の表に示します。

  表7‒35　ウィンドウ順序句のソート指定リストのソートキーに指定できるデータ型

  ウィンドウ枠句の指定		ウィンドウ枠値の指定	ソートキーのデータ型
  			数データ	文字データ	日時データ	バイナリデータ	配列データ
  指定あり	ROWS	−	○	○	○	×	×
  	RANGE	指定あり	○	×	○	×	×
  		指定なし	○	○	○	×	×
  指定なし		−	○	○	○	×	×

  （凡例）

  ○：指定できます。

  ×：指定できません。

  −：該当しません。

• ソート指定リストのソートキーに？パラメタを単独で指定した場合，？パラメタのデータ型にはINTEGER型が仮定されます。

• DISTINCT集合関数，または逆分布関数をウィンドウ関数として使用する場合，ウィンドウ順序句は指定できません。

• RANK，DENSE_RANK，CUME_DIST，LAG，またはLEADを指定した場合は，ウィンドウ指定中にウィンドウ順序句を指定してください。

• ウィンドウ枠句にRANGEを指定し，ウィンドウ枠境界にウィンドウ枠値指定を指定する場合，ウィンドウ順序句のソート指定リストには，ソート指定を複数指定できません。

(d)　ウィンドウ枠句

ウィンドウ枠句：：＝｛ROWS｜RANGE｝ ｛開始指定ウィンドウ枠｜範囲指定ウィンドウ枠｝

ウィンドウ関数の集計範囲をウィンドウ枠として指定します。

ウィンドウ枠句の機能概要を次の図に示します。

図7‒6　ウィンドウ枠句の機能概要

ウィンドウ枠句にROWSを指定した場合は，物理的な行単位のウィンドウ枠になります。RANGEを指定した場合は，論理的な値のオフセット（日時などの論理間隔）としてのウィンドウ枠になります。

ウィンドウ枠句の指定を省略した場合のウィンドウ枠の範囲を次に示します。

• ウィンドウ順序句の指定がある場合

  ウィンドウ枠の範囲は，次の範囲指定ウィンドウ枠を指定したときと等価になります。

  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

  ウィンドウ（区画）の先頭行から現在行までがウィンドウ関数の集計範囲になります。ただし，RANGEが仮定されているため，現在行のソートキーの値と同じソートキーの値を持つ後方の行も集計対象になります。

• ウィンドウ順序句の指定がない場合

  現在行が含まれるウィンドウ（区画）がウィンドウ関数の集計範囲になります。

指定規則を次に示します。

• ウィンドウ枠句を指定する場合，ウィンドウ関数として使用できる集合関数は，COUNT(*)または一般集合関数（DISTINCT集合関数を除く）となります。

• 次のウィンドウ（区画）全体を表すウィンドウ枠句以外のウィンドウ枠句を指定する場合は，ウィンドウ順序句の指定が必要です。

  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

• RANK，DENSE_RANK，CUME_DIST，ROW_NUMBER，LAG，またはLEADを指定した場合は，ウィンドウ指定中にウィンドウ枠句を指定できません。

• ウィンドウ枠句に開始指定ウィンドウ枠を指定する場合は，次の範囲指定ウィンドウ枠と等価になります。

  BETWEEN 開始指定ウィンドウ枠 AND CURRENT ROW

• ウィンドウ枠句に範囲指定ウィンドウ枠を指定した場合，開始指定ウィンドウ枠境界をウィンドウ枠の前方の境界，終了指定ウィンドウ枠境界をウィンドウ枠の後方の境界に設定します。

• ウィンドウ枠境界は次のようになります。

  • UNBOUNDED PRECEDINGを指定した場合

    ウィンドウ（区画）内の先頭行からウィンドウ枠が開始します。UNBOUNDED PRECEDINGは，開始指定ウィンドウ枠境界に指定できます。

  • UNBOUNDED FOLLOWINGを指定した場合

    ウィンドウ（区画）内の最終行でウィンドウ枠が終了します。UNBOUNDED FOLLOWINGは，終了指定ウィンドウ枠境界に指定できます。

  • CURRENT ROWを指定した場合

    ・ROWSを指定した場合：

    開始指定ウィンドウ枠境界に指定した場合は，現在行からウィンドウ枠が開始します。終了指定ウィンドウ枠境界に指定した場合は，現在行でウィンドウ枠が終了します。

    ・RANGEを指定した場合：

    開始指定ウィンドウ枠境界に指定した場合は，現在行のソートキーの値と同じソートキーの値を持つ最初の行からウィンドウ枠が開始します。終了指定ウィンドウ枠境界に指定した場合は，現在行のソートキーの値と同じソートキーの値を持つ最後の行でウィンドウ枠が終了します。

  • 「ウィンドウ枠値指定 PRECEDING」または「ウィンドウ枠値指定 FOLLOWING」を指定した場合

    ・ROWSを指定した場合：

    ウィンドウ枠値指定は，現在行からの物理的な行単位のオフセットになります。符号なし値指定のデータ型は，INTEGER型が指定できます。ラベル付き間隔は指定できません。

    ・RANGEを指定した場合：

    ウィンドウ枠値指定は，現在行のソートキーの値からの論理的なオフセットになります。ウィンドウ順序句に指定したソートキーのデータ型と，指定できる符号なし値指定またはラベル付き間隔を次の表に示します。

    表7‒36　ウィンドウ順序句に指定したソートキーのデータ型と，指定できる符号なし値指定またはラベル付き間隔（RANGEを指定した場合）

    ウィンドウ順序句に指定したソートキーのデータ型	指定できる符号なし値指定またはラベル付き間隔
    数データ	数データの符号なし値指定
    DATE	ラベル付き間隔（YEARS，MONTHS，DAYS）
    TIME	ラベル付き間隔（HOURS，MINUTES，SECONDS，MILLISECONDS，MICROSECONDS，NANOSECONDS，PICOSECONDS）
    TIMESTAMP	ラベル付き間隔（YEARS，MONTHS，DAYS，HOURS，MINUTES，SECONDS，MILLISECONDS，MICROSECONDS，NANOSECONDS，PICOSECONDS）

• 範囲指定ウィンドウ枠に指定するウィンドウ枠境界には，指定できない組み合わせがあります。

  • 開始指定ウィンドウ枠境界にUNBOUNDED FOLLOWINGを指定できません。

  • 終了指定ウィンドウ枠境界にUNBOUNDED PRECEDINGを指定できません。

  組み合わせの指定可否を次の表に示します。

  表7‒37　組み合わせの指定可否

  開始指定ウィンドウ枠境界の指定	終了指定ウィンドウ枠境界の指定
  	UNBOUNDED FOLLOWING	CURRENT ROW	ウィンドウ枠値指定PRECEDING	ウィンドウ枠値指定FOLLOWING
  UNBOUNDED PRECEDING	○	○	○	○
  CURRENT ROW	○	○	×	○
  ウィンドウ枠値指定PRECEDING	○	○	○	○
  ウィンドウ枠値指定FOLLOWING	○	×	×	○

  （凡例）

  ○：指定できます。

  ×：指定できません。

• 範囲指定ウィンドウ枠に指定するウィンドウ枠境界に，ウィンドウ枠値指定としてラベル付き間隔を指定する場合は，開始指定ウィンドウ枠境界と終了指定ウィンドウ枠境界のラベル付き間隔修飾子は同じにしてください。ラベル付き間隔修飾子については，「7.29.1　ラベル付き間隔の指定形式および規則」を参照してください。

  （例）

  BETWEEN 2 DAYS PRECEDING AND 1 DAYS PRECEDING

• ウィンドウ枠値指定としてラベル付き間隔を指定する場合は，ラベル付き間隔の値式一次子には値指定だけが指定できます。

• ウィンドウ枠値指定としてラベル付き間隔を指定する場合は，次の範囲の値が指定できます。

  YEARS：0〜9,998

  MONTHS：0〜119,987

  DAYS：0〜3,652,058

  HOURS：0〜87,649,415

  MINUTES：0〜5,258,964,959

  SECONDS：0〜315,537,897,599

  MILLISECONDS：0〜315,537,897,599,999

  MICROSECONDS：0〜315,537,897,599,999,999

  NANOSECONDS：0〜9,223,372,036,854,775,807

  PICOSECONDS：0〜9,223,372,036,854,775,807

• ウィンドウ枠値指定が負の値，またはナル値の場合はエラーになります。

• ウィンドウ枠値指定に？パラメタを指定した場合に仮定されるデータ型を次の表に示します。

  表7‒38　ウィンドウ枠値指定に？パラメタを指定した場合に仮定されるデータ型

  ウィンドウ枠の指定	ウィンドウ順序句のソートキーのデータ型	ウィンドウ枠値指定に仮定されるデータ型
  RANGE	SMALLINT	SMALLINT
  	INTEGER，BIGINT	INTEGER
  	DECIMAL，NUMERIC	DECIMAL
  	DOUBLE PRECISION，FLOAT	DOUBLE PRECISION
  	DATE	−（ラベル付き間隔だけが指定できます）
  	TIME
  	TIMESTAMP
  ROWS	−	INTEGER

  （凡例）

  −：該当しません。