A <firstterm>window function</firstterm> performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result. ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスできます。
Here is an example that shows how to compare each employee's salary with the average salary in his or her department: これはその部署の平均給与とそれぞれの従業員の給与をどのように比較するかを示した例です。
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)
The first three output columns come directly from the table
<structname>empsalary</structname>, and there is one output row for each row in the
table. The fourth column represents an average taken across all the table
rows that have the same <structfield>depname</structfield> value as the current row.
(This actually is the same function as the non-window <function>avg</function>
aggregate, but the <literal>OVER</literal> clause causes it to be
treated as a window function and computed across the window frame.)
最初の3つの出力列は、テーブルempsalary
から直接もたらされ、テーブル内の各行に対し1つの出力行が存在します。4番目の列は、現行の行と同じdepname
の値を持つ全てのテーブル行に渡って取得した平均値を表わしています。
(これは実際、非ウィンドウavg
集約関数と同じですが、OVER
句によりウィンドウ関数として扱われ、ウィンドウフレームに渡り計算されます。)
A window function call always contains an <literal>OVER</literal> clause
directly following the window function's name and argument(s). This is what
syntactically distinguishes it from a normal function or non-window
aggregate. The <literal>OVER</literal> clause determines exactly how the
rows of the query are split up for processing by the window function.
The <literal>PARTITION BY</literal> clause within <literal>OVER</literal>
divides the rows into groups, or partitions, that share the same
values of the <literal>PARTITION BY</literal> expression(s). For each row,
the window function is computed across the rows that fall into the
same partition as the current row.
ウィンドウ関数呼び出しは常に、ウィンドウ関数名と引数の直後に続くOVER
句を含みます。
これが通常の関数、または非ウィンドウ集約関数と構文的に区別されるところです。
OVER
句は、ウィンドウ関数により処理のため問い合わせの行がどのように分解されるかを厳密に決定します。
OVER
内のPARTITION BY
句は、行をPARTITION BY
式の同じ値を共有するグループ、すなわちパーティションに分割します。
それぞれの行に対し、ウィンドウ関数は現在行と同じパーティションに分類される行に渡って計算されます。
You can also control the order in which rows are processed by
window functions using <literal>ORDER BY</literal> within <literal>OVER</literal>.
(The window <literal>ORDER BY</literal> does not even have to match the
order in which the rows are output.) Here is an example:
OVER
内でORDER BY
を使用することによりウィンドウ関数で処理される行の順序を制御することもできます。
(ウィンドウのORDER BY
は行が出力される順序に一致する必要すらありません。)
ここに例をあげます。
SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;
depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 | 5000 | 1 sales | 4 | 4800 | 2 sales | 3 | 4800 | 2 (10 rows)
As shown here, the <function>rank</function> function produces a numerical rank
for each distinct <literal>ORDER BY</literal> value in the current row's
partition, using the order defined by the <literal>ORDER BY</literal> clause.
<function>rank</function> needs no explicit parameter, because its behavior
is entirely determined by the <literal>OVER</literal> clause.
ここで示されたように、rank
関数は、それぞれの別々のORDER BY
の値に対する現在行のパーティション内における順位を、ORDER BY
句で定義された順序を使って生成します。
rank
は明示的なパラメータを必要としません。この動作はOVER
句により完全に決定されるためです。
The rows considered by a window function are those of the <quote>virtual
table</quote> produced by the query's <literal>FROM</literal> clause as filtered by its
<literal>WHERE</literal>, <literal>GROUP BY</literal>, and <literal>HAVING</literal> clauses
if any. For example, a row removed because it does not meet the
<literal>WHERE</literal> condition is not seen by any window function.
A query can contain multiple window functions that slice up the data
in different ways using different <literal>OVER</literal> clauses, but
they all act on the same collection of rows defined by this virtual table.
ウィンドウ関数で考慮される行は、そのWHERE
、GROUP BY
、およびHAVING
句でフィルタをかけられた問い合わせのFROM
句によって生成された「仮想テーブル」の行です。
例えば、WHERE
条件に一致しないため削除された行はウィンドウ関数から見えません。
異なったOVER
句を用いて、異なった方法によりデータを分割する複数のウィンドウ関数を問い合わせが含んでも構いません。
しかし、この仮想テーブルで定義された行の同一の集まり上で全てが作動します。
We already saw that <literal>ORDER BY</literal> can be omitted if the ordering
of rows is not important. It is also possible to omit <literal>PARTITION
BY</literal>, in which case there is a single partition containing all rows.
ORDER BY
は、行の順序付けが重要でない場合、省略可能であることを見てきました。
PARTITION BY
も同様に割愛できます。
この場合、全ての行を含む単一のパーティションが存在します。
There is another important concept associated with window functions:
for each row, there is a set of rows within its partition called its
<firstterm>window frame</firstterm>. Some window functions act only
on the rows of the window frame, rather than of the whole partition.
By default, if <literal>ORDER BY</literal> is supplied then the frame consists of
all rows from the start of the partition up through the current row, plus
any following rows that are equal to the current row according to the
<literal>ORDER BY</literal> clause. When <literal>ORDER BY</literal> is omitted the
default frame consists of all rows in the partition.
ウィンドウ関数に関連した別の重要な概念があります。
それぞれの行に対して、そのウィンドウフレームと呼ばれる、そのパーティション内の行の集合が存在します。
ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。
デフォルトでは、ORDER BY
が指定されると、フレームは、パーティションの始めから現在の行までのすべての行、およびそれより後にあるがORDER BY
句に従うと現在の行とおなじ順序になるすべての行から構成されます。
ORDER BY
が省略された場合、デフォルトのフレームはそのパーティション内のすべての行を含みます。
[5]
Here is an example using <function>sum</function>:
sum
を使用した例を示します。
SELECT salary, sum(salary) OVER () FROM empsalary;
salary | sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows)
Above, since there is no <literal>ORDER BY</literal> in the <literal>OVER</literal>
clause, the window frame is the same as the partition, which for lack of
<literal>PARTITION BY</literal> is the whole table; in other words each sum is
taken over the whole table and so we get the same result for each output
row. But if we add an <literal>ORDER BY</literal> clause, we get very different
results:
上では、OVER
句内にORDER BY
が存在しませんので、ウィンドウフレームはパーティションと同一です。またパーティションはPARTITION BY
もありませんのでテーブル全体となります。言い換えると、総和はそれぞれ、テーブル全体に対して行われ、その結果、各出力行で同じ結果を得ることになります。
しかし以下のように、ORDER BY
句を加えると、非常に異なる結果を得ます。
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows)
Here the sum is taken from the first (lowest) salary up through the current one, including any duplicates of the current one (notice the results for the duplicated salaries). ここで、sumは最初の(最も低い)salaryから現在の行まで、現在のものと重複する全てを含んで、計算されます(重複するsalaryに対する結果に注意してください)。
Window functions are permitted only in the <literal>SELECT</literal> list
and the <literal>ORDER BY</literal> clause of the query. They are forbidden
elsewhere, such as in <literal>GROUP BY</literal>, <literal>HAVING</literal>
and <literal>WHERE</literal> clauses. This is because they logically
execute after the processing of those clauses. Also, window functions
execute after non-window aggregate functions. This means it is valid to
include an aggregate function call in the arguments of a window function,
but not vice versa.
ウィンドウ関数は問い合わせのSELECT
リストとORDER BY
句に限って許可されます。
GROUP BY
、HAVING
、およびWHERE
句などその他の場所では禁止されています。
その理由は、ウィンドウ関数は論理的に、ここに挙げたような句が処理された後に実行されるからです。
またウィンドウ関数は非ウィンドウ集約関数の後に実行されます。
これが意味する所は、ウィンドウ関数の引数に集約関数呼び出しを含めても有効ですが、その逆は成り立たないと言うことです。
If there is a need to filter or group rows after the window calculations are performed, you can use a sub-select. For example: ウィンドウ演算が行われた後、行にフィルタ処理を行ったりグループ化を行う必要が生じた場合、副SELECTを使用します。 例をあげます。
SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos < 3;
The above query only shows the rows from the inner query having
<literal>rank</literal> less than 3.
上記問い合わせは3より小さいrank
を持った内部問い合わせからの行のみを表示します。
When a query involves multiple window functions, it is possible to write
out each one with a separate <literal>OVER</literal> clause, but this is
duplicative and error-prone if the same windowing behavior is wanted
for several functions. Instead, each windowing behavior can be named
in a <literal>WINDOW</literal> clause and then referenced in <literal>OVER</literal>.
For example:
問い合わせが複数のウィンドウ関数を含む場合、各ウィンドウ関数に異なるOVER
句を記述できます。しかし複数の関数で同じウィンドウ処理動作が必要な場合は重複となり、またエラーを招きがちです。
代わりにWINDOW
句でウィンドウ処理動作に名前を付け、これをOVER
内で参照できます。
以下に例を示します。
SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
More details about window functions can be found in <xref linkend="syntax-window-functions"/>, <xref linkend="functions-window"/>, <xref linkend="queries-window"/>, and the <xref linkend="sql-select"/> reference page. ウィンドウ関数についてより詳細は、 4.2.8、 9.22、 7.2.5、および SELECT マニュアルページにあります。