ウィンドウ集約

ウィンドウ集約 (ウィンドウ解析関数ともいう) を使用すると、ウィンドウによって定義される行グループに基づいて集約値を計算できます。ウィンドウは、システムが計算を実行するために使用する行の範囲を決定します。

ウィンドウのサイズは、物理的な行数、または論理的インターバル (過去 1 年間、四半期など) に基づいて決定できます。ウィンドウ集約を使い、累積、移動、集中、報告の集約を計算することが可能です。ウィンドウ集約では、グループ集約と違って、行の情報が保持されます。

例えば、以下のような月次の販売情報を記録した monthlysales という表があるとします。

 YEAR | MONTH | SALESK
------+-------+--------
 2007 |    10 |     20
 2007 |    11 |     22
 2007 |    12 |     25
 2008 |     1 |     30
 2008 |     2 |     35
 2008 |     3 |     50
 2008 |     4 |     70
(7 rows)

ウィンドウ集約を使用すると、さまざまな移動期間について、各種の集計を行うことができます。例えば、以下の照会では、総売上高の 3 カ月の移動平均を求めています。

MYDB.SCHEMA(USER)=> SELECT year, month, salesk, avg(salesk) OVER 
(PARTITION BY year ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 
FOLLOWING) FROM monthlysales; 
 YEAR | MONTH | SALESK |    AVG
------+-------+--------+-----------
 2007 |    10 |     20 | 21.000000
 2007 |    11 |     22 | 22.333333
 2007 |    12 |     25 | 23.500000
 2008 |     1 |     30 | 32.500000
 2008 |     2 |     35 | 38.333333
 2008 |     3 |     50 | 51.666667
 2008 |     4 |     70 | 60.000000
(7 rows)

出力には各行の結果が記載されます。AVG の値は、前月、当月、翌月の売上値の移動平均です。先頭行については、前月がないため、当月と翌月にのみ基づいて平均が計算されます。同様に、最後の行の平均は、前月と当月のみの平均です。

以下の例では、総売上高の累計を計算しています。

MYDB.SCHEMA(USER)=> SELECT *, sum(salesk) OVER (PARTITION BY year ORDER BY 
month ROWS UNBOUNDED PRECEDING) FROM monthlysales; 
 YEAR | MONTH | SALESK | SUM
------+-------+--------+-----
 2007 |    10 |     20 |  20
 2007 |    11 |     22 |  42
 2007 |    12 |     25 |  67
 2008 |     1 |     30 |  30
 2008 |     2 |     35 |  65
 2008 |     3 |     50 | 115
 2008 |     4 |     70 | 185
(7 rows)

出力には各行の結果が含まれており、SUM 値は、表内の各月の時点で総売上高を示しています (前月までの累計と当月の合計)。