9.21. ウィンドウ関数

PostgreSQL 11.5文書
		第9章関数と演算子
前へ	上へ	9.21. ウィンドウ関数	次へ

ウィンドウ関数は現在の問い合わせ行に関連した行集合に渡っての計算処理機能を提供します。この機能の手引きは3.5を、文法の詳細は4.2.8を参照してください。

組み込みウィンドウ関数は表 9.57に一覧されています。これらの関数は必ずウィンドウ関数構文で呼び出されなければなりません。つまり、OVER句が必要です。

これらの関数に加え、どんな組み込み、またはユーザ定義の汎用集約関数または統計集約関数もウィンドウ関数として使用できます(ただし順序集合や仮想集合集約はそうではありません)。組み込み集約関数一覧は9.20を参照してください。集約関数は、呼び出しの後にOVER句が続いた場合のみウィンドウ関数として動作します。それ以外の場合は、非ウィンドウの集約関数として動作し、集合全体に対して1行だけを返します。

表9.57 汎用ウィンドウ関数

関数	戻り値	説明
`row_number()`	`bigint`	現在行のパーティション内での行番号（１から数える）
`rank()`	`bigint`	ギャップを含んだ現在行の順位。先頭ピアの`row_number`と同じになる。
`dense_rank()`	`bigint`	ギャップを含まない現在行の順位。この関数はピアのグループ数を数える。
`percent_rank()`	`double precision`	現在行の相対順位。 (`rank` - 1) / (パーティションの総行数 - 1)
`cume_dist()`	`double precision`	現在行の相対順位。 (現在行より先行する行およびピアの行数) / (パーティションの総行数)
`ntile(num_buckets integer)`	`integer`	できるだけ等価にパーティションを分割した、1から引数値までの整数
`lag(value anyelement [, offset integer [, default anyelement ]])`	`valueと同じ型`	パーティション内の現在行より`offset`行だけ前の行で評価された`value`を返す。該当する行がない場合、その代わりとして`default`(`value`と同じ型でなければならない)を返す。 `offset`と`default`は共に現在行について評価される。省略された場合、`offset`は1となり、`default`はNULLになる。
`lead(value anyelement [, offset integer [, default anyelement ]])`	`valueと同じ型`	パーティション内の現在行より`offset`行だけ後の行で評価された`value`を返す。該当する行がない場合、その代わりとして`default`(`value`と同じ型でなければならない)を返す。 `offset`と`default`は共に現在行について評価される。省略された場合、`offset`は1となり、`default`はNULLになる。
`first_value(value any)`	`valueと同じ型`	ウィンドウフレームの最初の行である行で評価された`value` を返す
`last_value(value any)`	`valueと同じ型`	ウィンドウフレームの最後の行である行で評価された`value` を返す
`nth_value(value any, nth integer)`	`valueと同じ型`	ウィンドウフレームの（１から数えて）`nth`番目の行である行で評価された`value`を返す。行が存在しない場合はNULLを返す

表 9.57に列挙された関数はすべて、対応するウィンドウ定義のORDER BY句で指定されるソート順に依存します。 ORDER BYの列だけを考慮した場合に重複する行はピアと呼ばれます。 4つの順位付け関数（cume_distを含む）は、すべてのピア行に対して同じ答えになるように定義されています。

first_value、last_value、nth_value関数は「ウィンドウフレーム」内の行のみを考慮することに注意してください。デフォルトで、ウィンドウフレームにはパーティションの先頭から現在の行の最終ピアまでの行が含まれます。これはlast_value、または時々nth_valueでは有用ではない結果を得ることになりがちです。 OVER句に適切なフレーム指定(RANGE、GROUP、もしくはROWS)を加えることで、フレームを再定義することができます。フレーム指定についての詳細は4.2.8を参照してください。

集約関数をウィンドウ関数として使用する場合、現在の行のウィンドウフレーム内の行に渡って集約処理を行います。 ORDER BYおよび、デフォルトのウィンドウフレーム定義を使用した集約では、「中間和」のような動作を行います。これが望まれる場合もあれば、望まれない場合もあります。パーティション全体に渡る集約処理を行うためには、ORDER BYを省略するかROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを使用してください。他のフレーム指定を使用することで様々な結果を得ることができます。

注記

SQL標準は、lead、lag、first_value、last_value、およびnth_valueに対しRESPECT NULLS、またはIGNORE NULLSオプションを定義します。これはPostgreSQLに実装されていません。動作は常に標準のデフォルトと同一です。つまり、RESPECT NULLSです。同様にして、標準のnth_valueに対するFROM FIRST、またはFROM LASTオプションは実装されていません。デフォルトのFROM FIRST動作のみに対応しています。（ORDER BY順序付けを逆に行うことで、FROM LASTの結果を得ることができます。）

cume_distが現在行およびそのピアより小さいか等しいパーティション行の割合を計算するのに対し、percent_rankは現在行がそのパーティション内にないものとして、現在行より小さいパーティション行の割合を計算します。

前へ	上へ	次へ
9.20. 集約関数	ホーム	9.22. 副問い合わせ式