9.22. ウィンドウ関数

PostgreSQL 16.0文書
		第9章関数と演算子	誤訳等の報告
前へ	上へ	9.22. ウィンドウ関数	次へ

9.22. ウィンドウ関数 #

<title>Window Functions</title>

<firstterm>Window functions</firstterm> provide the ability to perform calculations across sets of rows that are related to the current query row. See <xref linkend="tutorial-window"/> for an introduction to this feature, and <xref linkend="syntax-window-functions"/> for syntax details. ウィンドウ関数は現在の問い合わせ行に関連した行集合に渡っての計算処理機能を提供します。この機能の手引きは3.5を、文法の詳細は4.2.8を参照してください。

The built-in window functions are listed in <xref linkend="functions-window-table"/>. Note that these functions <emphasis>must</emphasis> be invoked using window function syntax, i.e., an <literal>OVER</literal> clause is required. 組み込みウィンドウ関数は表 9.64に一覧されています。これらの関数は必ずウィンドウ関数構文で呼び出されなければなりません。つまり、OVER句が必要です。

In addition to these functions, any built-in or user-defined ordinary aggregate (i.e., not ordered-set or hypothetical-set aggregates) can be used as a window function; see <xref linkend="functions-aggregate"/> for a list of the built-in aggregates. Aggregate functions act as window functions only when an <literal>OVER</literal> clause follows the call; otherwise they act as plain aggregates and return a single row for the entire set. これらの関数に加え、すべての組み込み、またはユーザ定義の汎用集約関数または統計集約関数もウィンドウ関数として使用できます(ただし順序集合や仮想集合集約はそうではありません)。組み込み集約関数一覧は9.21を参照してください。集約関数は、呼び出しの後にOVER句が続いた場合のみウィンドウ関数として動作します。それ以外の場合は、非ウィンドウの集約関数として動作し、集合全体に対して1行だけを返します。

表9.64 汎用ウィンドウ関数

<title>General-Purpose Window Functions</title>

Function 関数 Description 説明
`row_number` () → `bigint` Returns the number of the current row within its partition, counting from 1. １から数える現在行のパーティション内での行番号を返します。
`rank` () → `bigint` Returns the rank of the current row, with gaps; that is, the <function>row_number</function> of the first row in its peer group. ギャップを含んだ現在行の順位を返します。すなわちピアグループの先頭行の`row_number`と同じになります。
`dense_rank` () → `bigint` Returns the rank of the current row, without gaps; this function effectively counts peer groups. ギャップを含まない現在行の順位。この関数は実質的にピアのグループ数を数えます。
`percent_rank` () → `double precision` Returns the relative rank of the current row, that is (<function>rank</function> - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive. 現在行の相対順位、すなわち (`rank` - 1) / (パーティションの総行数 - 1)を返します。したがってこの値は境界を含み0から1となります。
`cume_dist` () → `double precision` Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/<parameter>N</parameter> to 1. 現在行の相対順位、すなわち (現在行より先行する行およびピアの行数) / (パーティションの総行数)を返します。したがってこの値は1/`N`から1となります。
`ntile` ( `num_buckets` `integer` ) → `integer` Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible. できるだけ等価にパーティションを分割した、1から引数値までの整数を返します。
`lag` ( `value` `anycompatible` [, `offset` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Returns <parameter>value</parameter> evaluated at the row that is <parameter>offset</parameter> rows before the current row within the partition; if there is no such row, instead returns <parameter>default</parameter> (which must be of a type compatible with <parameter>value</parameter>). Both <parameter>offset</parameter> and <parameter>default</parameter> are evaluated with respect to the current row. If omitted, <parameter>offset</parameter> defaults to 1 and <parameter>default</parameter> to <literal>NULL</literal>. パーティション内の現在行より`offset`行だけ前の行で評価された`value`を返します。該当する行がない場合、その代わりとして`default`(`value`と互換性のある型でなければなりません)を返します。 `offset`と`default`は共に現在行について評価されます。省略された場合、`offset`はデフォルトで1となり、`default`は`NULL`になります。
`lead` ( `value` `anycompatible` [, `offset` `integer` [, `default` `anycompatible` ]] ) → `anycompatible` Returns <parameter>value</parameter> evaluated at the row that is <parameter>offset</parameter> rows after the current row within the partition; if there is no such row, instead returns <parameter>default</parameter> (which must be of a type compatible with <parameter>value</parameter>). Both <parameter>offset</parameter> and <parameter>default</parameter> are evaluated with respect to the current row. If omitted, <parameter>offset</parameter> defaults to 1 and <parameter>default</parameter> to <literal>NULL</literal>. パーティション内の現在行より`offset`行だけ後の行で評価された`value`を返します。該当する行がない場合、その代わりとして`default`(`value`と互換性のある型でなければなりません)を返します。 `offset`と`default`は共に現在行について評価されます。省略された場合、`offset`はデフォルトで1となり、`default`は`NULL`になります。
`first_value` ( `value` `anyelement` ) → `anyelement` Returns <parameter>value</parameter> evaluated at the row that is the first row of the window frame. ウィンドウフレームの最初の行である行で評価された`value`を返します。
`last_value` ( `value` `anyelement` ) → `anyelement` Returns <parameter>value</parameter> evaluated at the row that is the last row of the window frame. ウィンドウフレームの最後の行である行で評価された`value`を返します。
`nth_value` ( `value` `anyelement`, `n` `integer` ) → `anyelement` Returns <parameter>value</parameter> evaluated at the row that is the <parameter>n</parameter>'th row of the window frame (counting from 1); returns <literal>NULL</literal> if there is no such row. ウィンドウフレームの（１から数えて）`n`番目の行である行で評価された`value`を返します。行が存在しない場合は`NULL`を返します。

Function 関数

Description 説明

row_number () → bigint

Returns the number of the current row within its partition, counting from 1. １から数える現在行のパーティション内での行番号を返します。

rank () → bigint

Returns the rank of the current row, with gaps; that is, the <function>row_number</function> of the first row in its peer group. ギャップを含んだ現在行の順位を返します。すなわちピアグループの先頭行のrow_numberと同じになります。

dense_rank () → bigint

Returns the rank of the current row, without gaps; this function effectively counts peer groups. ギャップを含まない現在行の順位。この関数は実質的にピアのグループ数を数えます。

percent_rank () → double precision

Returns the relative rank of the current row, that is (<function>rank</function> - 1) / (total partition rows - 1). The value thus ranges from 0 to 1 inclusive. 現在行の相対順位、すなわち (rank - 1) / (パーティションの総行数 - 1)を返します。したがってこの値は境界を含み0から1となります。

cume_dist () → double precision

Returns the cumulative distribution, that is (number of partition rows preceding or peers with current row) / (total partition rows). The value thus ranges from 1/<parameter>N</parameter> to 1. 現在行の相対順位、すなわち (現在行より先行する行およびピアの行数) / (パーティションの総行数)を返します。したがってこの値は1/Nから1となります。

ntile ( num_buckets integer ) → integer

Returns an integer ranging from 1 to the argument value, dividing the partition as equally as possible. できるだけ等価にパーティションを分割した、1から引数値までの整数を返します。

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

Returns <parameter>value</parameter> evaluated at the row that is <parameter>offset</parameter> rows before the current row within the partition; if there is no such row, instead returns <parameter>default</parameter> (which must be of a type compatible with <parameter>value</parameter>). Both <parameter>offset</parameter> and <parameter>default</parameter> are evaluated with respect to the current row. If omitted, <parameter>offset</parameter> defaults to 1 and <parameter>default</parameter> to <literal>NULL</literal>. パーティション内の現在行よりoffset行だけ前の行で評価されたvalueを返します。該当する行がない場合、その代わりとしてdefault(valueと互換性のある型でなければなりません)を返します。 offsetとdefaultは共に現在行について評価されます。省略された場合、offsetはデフォルトで1となり、defaultはNULLになります。

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

Returns <parameter>value</parameter> evaluated at the row that is <parameter>offset</parameter> rows after the current row within the partition; if there is no such row, instead returns <parameter>default</parameter> (which must be of a type compatible with <parameter>value</parameter>). Both <parameter>offset</parameter> and <parameter>default</parameter> are evaluated with respect to the current row. If omitted, <parameter>offset</parameter> defaults to 1 and <parameter>default</parameter> to <literal>NULL</literal>. パーティション内の現在行よりoffset行だけ後の行で評価されたvalueを返します。該当する行がない場合、その代わりとしてdefault(valueと互換性のある型でなければなりません)を返します。 offsetとdefaultは共に現在行について評価されます。省略された場合、offsetはデフォルトで1となり、defaultはNULLになります。

first_value ( value anyelement ) → anyelement

Returns <parameter>value</parameter> evaluated at the row that is the first row of the window frame. ウィンドウフレームの最初の行である行で評価されたvalueを返します。

last_value ( value anyelement ) → anyelement

Returns <parameter>value</parameter> evaluated at the row that is the last row of the window frame. ウィンドウフレームの最後の行である行で評価されたvalueを返します。

nth_value ( value anyelement, n integer ) → anyelement

Returns <parameter>value</parameter> evaluated at the row that is the <parameter>n</parameter>'th row of the window frame (counting from 1); returns <literal>NULL</literal> if there is no such row. ウィンドウフレームの（１から数えて）n番目の行である行で評価されたvalueを返します。行が存在しない場合はNULLを返します。

All of the functions listed in <xref linkend="functions-window-table"/> depend on the sort ordering specified by the <literal>ORDER BY</literal> clause of the associated window definition. Rows that are not distinct when considering only the <literal>ORDER BY</literal> columns are said to be <firstterm>peers</firstterm>. The four ranking functions (including <function>cume_dist</function>) are defined so that they give the same answer for all rows of a peer group. 表 9.64に列挙された関数はすべて、対応するウィンドウ定義のORDER BY句で指定されるソート順に依存します。 ORDER BYの列だけを考慮した場合に重複する行はピアと呼ばれます。 4つの順位付け関数（cume_distを含む）は、すべてのピア行に対して同じ答えになるように定義されています。

Note that <function>first_value</function>, <function>last_value</function>, and <function>nth_value</function> consider only the rows within the <quote>window frame</quote>, which by default contains the rows from the start of the partition through the last peer of the current row. This is likely to give unhelpful results for <function>last_value</function> and sometimes also <function>nth_value</function>. You can redefine the frame by adding a suitable frame specification (<literal>RANGE</literal>, <literal>ROWS</literal> or <literal>GROUPS</literal>) to the <literal>OVER</literal> clause. See <xref linkend="syntax-window-functions"/> for more information about frame specifications. first_value、last_value、nth_value関数は「ウィンドウフレーム」内の行のみを考慮することに注意してください。デフォルトで、ウィンドウフレームにはパーティションの先頭から現在の行の最終ピアまでの行が含まれます。これはlast_value、または時々nth_valueでは有用ではない結果を得ることになりがちです。 OVER句に適切なフレーム指定(RANGE、GROUP、もしくはROWS)を加えることで、フレームを再定義することができます。フレーム指定についての詳細は4.2.8を参照してください。

When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with <literal>ORDER BY</literal> and the default window frame definition produces a <quote>running sum</quote> type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit <literal>ORDER BY</literal> or use <literal>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</literal>. Other frame specifications can be used to obtain other effects. 集約関数をウィンドウ関数として使用する場合、現在の行のウィンドウフレーム内の行に渡って集約処理を行います。 ORDER BYおよび、デフォルトのウィンドウフレーム定義を使用した集約では、「中間和」のような動作を行います。これが望まれる場合もあれば、望まれない場合もあります。パーティション全体に渡る集約処理を行うためには、ORDER BYを省略するかROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGを使用してください。他のフレーム指定を使用することで様々な結果を得ることができます。

注記

The SQL standard defines a <literal>RESPECT NULLS</literal> or <literal>IGNORE NULLS</literal> option for <function>lead</function>, <function>lag</function>, <function>first_value</function>, <function>last_value</function>, and <function>nth_value</function>. This is not implemented in <productname>PostgreSQL</productname>: the behavior is always the same as the standard's default, namely <literal>RESPECT NULLS</literal>. Likewise, the standard's <literal>FROM FIRST</literal> or <literal>FROM LAST</literal> option for <function>nth_value</function> is not implemented: only the default <literal>FROM FIRST</literal> behavior is supported. (You can achieve the result of <literal>FROM LAST</literal> by reversing the <literal>ORDER BY</literal> ordering.) 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の結果を得ることができます。）

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