OLAP - Part 3 - MAVG (Moving average)

Moving Average (MAVG):

1. Used to calculate Moving average on a column.

2. The result is based on a predefined number of rows . The number of rows used for aggregation operation is called as query width.

3. Syntax:

MAVG(colname, n, sortlist)

colname = the column on which the moving average is computed

n = the number of rows (< 4096) which will be used in the calculation including the current row.

 ('n' is also referred to as the 'width' of the average)

sortlist = the column(s) which determine the sequencing of the rows. Default is ascending.

Think of this as - the data is first sorted using the sort list and then the moving average is calculated.

4. If the number of rows preceding the current row is less than the width, the average is computed based on the existing preceding rows.

5. Example:

SELECT employeeid,salary,hiredate,MAVG(salary,3,hiredate) from employee;

Here the query width is 3.

Employeeid	Salary	Hiredate	MAvg(Salary,3,Hiredate)
8	8000	1/1/1992	8000	Average of row 1
4	4000	1/1/1996	6000	Average of rows 1 and 2
7	7000	6/1/1996	6333.33	Average of rows 1,2 and 3
3	3000	1/1/1997	4666.67	Average of rows 2,3 and 4
6	6000	3/1/1997	5333.33	Average of rows 3,4 and 5
2	2000	1/1/1998	3666.67	"
10	10000	1/1/1998	6000	"
5	5000	2/1/1998	5666.67	"
1	1000	1/1/1999	5333.33	"
9	9000	4/1/1999	5000	"
11	11000	5/1/1999	7000	"
12	12000	8/1/1999	10666.67	"

For computation purpose current row and the preceding n-1 rows are used.

If the number of rows is less than n-1, it uses all preceding rows.

It sorts in ascending order by sortlist column(s) as the default.