Window aggregates

You use window aggregates (also called window analytic functions) to compute an aggregate value that is based on a group of rows, which are defined by a window. The window determines the range of rows the system uses to conduct the calculations.

Window sizes can be based on a physical number of rows or a logical interval such as year-to-date or quarterly. You can use window aggregates to compute cumulative, moving, centered, and reporting aggregates. Unlike the grouped aggregates, window aggregates can preserve the row information.

For example, the following is a table of monthly sales information called 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)

With window aggregates, you can compute various aggregations over moving time frames. For example, the following query shows a three-month moving average of the sales total:

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)

The output has a result for each row and the AVG value is a moving average of the previous, current, and following month sales values. For the first row, the average is based only on the current and following month, as there is no previous month. Likewise, the last row is the average of only the previous and current month.

The following example shows a running total of the sales summary:

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)

The output has a result for each row and the SUM value is the total of the sales for each month in the table (all the previous rows plus the current row).