★PostgreSQLカンファレンス2021 11月12日開催/チケット販売中★
他のバージョンの文書 13 | 12 | 11 | 10 | 9.6 | 9.5 | 9.4 | 9.3 | 9.2 | 9.1 | 9.0 | 8.4 | 8.3 | 8.2 | 8.1 | 8.0 | 7.4 | 7.3 | 7.2

9.22. ウィンドウ関数

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

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

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

表9.60 汎用ウィンドウ関数

関数

説明

row_number () → bigint

1から数える現在行のパーティション内での行番号を返します。

rank () → bigint

ギャップを含んだ現在行の順位を返します。すなわちピアグループの先頭行のrow_numberと同じになります。

dense_rank () → bigint

ギャップを含まない現在行の順位。この関数は実質的にピアのグループ数を数えます。

percent_rank () → double precision

現在行の相対順位、すなわち (rank - 1) / (パーティションの総行数 - 1)を返します。 したがってこの値は境界を含み0から1となります。

cume_dist () → double precision

現在行の相対順位、すなわち (現在行より先行する行およびピアの行数) / (パーティションの総行数)を返します。 したがってこの値は1/Nから1となります。

ntile ( num_buckets integer ) → integer

できるだけ等価にパーティションを分割した、1から引数値までの整数を返します。

lag ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement

パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返します。 該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければなりません)を返します。 offsetdefaultは共に現在行について評価されます。 省略された場合、offsetはデフォルトで1となり、defaultNULLになります。

lead ( value anyelement [, offset integer [, default anyelement ]] ) → anyelement

パーティション内の現在行よりoffset行だけ後の行で評価されたvalueを返します。 該当する行がない場合、その代わりとしてdefault(valueと同じ型でなければなりません)を返します。 offsetdefaultは共に現在行について評価されます。 省略された場合、offsetはデフォルトで1となり、defaultNULLになります。

first_value ( value anyelement ) → anyelement

ウィンドウフレームの最初の行である行で評価されたvalueを返します。

last_value ( value anyelement ) → anyelement

ウィンドウフレームの最後の行である行で評価されたvalueを返します。

nth_value ( value anyelement, n integer ) → anyelement

ウィンドウフレームの(1から数えて)n番目の行である行で評価されたvalueを返します。行が存在しない場合はNULLを返します。


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

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

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

注記

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