ウィンドウ関数は現在の行に何らかとも関係するテーブル行の集合に渡って計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、非ウィンドウ集約呼び出しのように、ウィンドウ関数により行が単一出力行にグループ化されることはありません。 その代わり、行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。
これはその部署の平均給与とそれぞれの従業員の給与をどのように比較するかを示した例です。
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)
最初の3つの出力列は、テーブルempsalary
から直接もたらされ、テーブル内のそれぞれの行に対し1つの出力行が存在します。4番目の列は、現行の行と同じdepname
の値を持つ全てのテーブル行に渡って取得した平均値を表わしています。
(これは実際、非ウィンドウavg
集約関数と同じですが、OVER
句によりウィンドウ関数として扱われ、ウィンドウフレームに渡り計算されます。)
ウィンドウ関数呼び出しは常に、ウィンドウ関数名と引数の直後に続くOVER
句を含みます。
これが通常の関数、または非ウィンドウ集約関数と構文的に区別されるところです。
OVER
句は、ウィンドウ関数により処理のため問い合わせの行がどのように分解されるかを厳密に決定します。
OVER
内のPARTITION BY
句は、行をPARTITION BY
式の同じ値を共有するグループ、すなわちパーティションに分割します。
それぞれの行に対し、ウィンドウ関数は現在行と同じパーティションに分類される行に渡って計算されます。
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)
ここで示されたように、rank
関数は、それぞれの別々のORDER BY
の値に対する現在行のパーティション内における順位を、ORDER BY
句で定義された順序を使って生成します。
rank
は明示的なパラメータを必要としません。この動作はOVER
句により完全に決定されるためです。
ウィンドウ関数で考慮される行は、そのWHERE
、GROUP BY
、およびHAVING
句でフィルターをかけられた問い合わせのFROM
句によって生成された「仮想テーブル」の行です。
例えば、WHERE
条件に一致しないため削除された行はウィンドウ関数からは見えません。
異なったOVER
句を用いて、異なった方法によりデータを分割する複数のウィンドウ関数を問い合わせが含んでも構いません。
しかし、この仮想テーブルで定義された行の同一の集まり上で全てが作動します。
ORDER BY
は、行の順序付けが重要でない場合、省略可能であることを見てきました。
PARTITION BY
も同様に割愛することができます。
この場合、全ての行を含む単一のパーティションが存在します。
ウィンドウ関数に関連した別の重要な概念があります。
それぞれの行に対して、そのウィンドウフレームと呼ばれる、そのパーティション内の行の集合が存在します。
ウィンドウ関数の中には、パーティション全体ではなく、ウィンドウフレームの行のみに対して作用するものもあります。
デフォルトでは、ORDER BY
が指定されると、フレームは、パーティションの始めから現在の行までのすべての行、およびそれより後にあるがORDER BY
句に従うと現在の行とおなじ順序になるすべての行から構成されます。
ORDER BY
が省略された場合、デフォルトのフレームはそのパーティション内のすべての行を含みます。
[4]
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)
上では、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)
ここで、sumは最初の(最も低い)salaryから現在の行まで、現在のものと重複する全てを含んで、計算されます(重複するsalaryに対する結果に注意してください)。
ウィンドウ関数は問い合わせのSELECT
リストとORDER BY
句に限って許可されます。
GROUP BY
、HAVING
、およびWHERE
句などその他の場所では禁止されています。
その理由は、ウィンドウ関数は論理的に、ここに挙げたような句が処理された後に実行されるからです。
またウィンドウ関数は非ウィンドウ集約関数の後に実行されます。
これが意味する所は、ウィンドウ関数の引数に集約関数呼び出しを含めても有効ですが、その逆は成り立たないと言うことです。
ウィンドウ演算が行われた後、行にフィルタ処理を行ったりグループ化を行う必要が生じた場合、副問い合わせを使用します。 例をあげます。
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;
上記問い合わせは3より小さいrank
を持った内部問い合わせからの行のみを表示します。
問い合わせが複数のウィンドウ関数を含む場合、各ウィンドウ関数に異なるOVER
句を記述することができます。しかし複数の関数で同じウィンドウ処理動作が必要な場合は重複となり、またエラーを招きがちです。代わりにWINDOW
句でウィンドウ処理動作に名前を付け、これをOVER
内で参照することができます。以下に例を示します。
SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
ウィンドウ関数についてより詳細は、 4.2.8、 9.22、 7.2.5、および SELECT マニュアルページにあります。