無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

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)

最初の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 が省略された場合、デフォルトのフレームはそのパーティション内のすべての行を含みます。 [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 マニュアルページにあります。

注意

[1]

ほかの方法でウィンドウフレームを定義するいくつかのオプションがありますが、このチュートリアルでは扱いません。詳細は、 項4.2.8 を参照してください。


powered by SEO.CUG.NET