3.5. ウィンドウ関数

ウィンドウ関数は現在の行に何らかとも関係するテーブル行の一纏まり全般の計算を行います。 これは集約関数により行われる計算の形式と似たようなものです。 とは言っても、通常の集約関数とは異なり、ウィンドウ関数の使用は単一出力行に行をグループ化しません。 行はそれぞれ個別の身元を維持します。 裏側では、ウィンドウ関数は問い合わせ結果による現在行だけでなく、それ以上の行にアクセスすることができます。

これはその部署の平均給与とそれぞれの従業員の給与をどのように比較するかを示した例です。

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)

最初の３つの出力列は、テーブルempsalaryから直接もたらされ、テーブル内のそれぞれの行に対し１つの出力行が存在します。４番目の列は、現行の行と同じ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が省略された場合、デフォルトのフレームはそのパーティション内のすべての行を含みます。 [1] 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.21、 項7.2.4、および SELECT マニュアルページにあります。