OLAP - Part 4 - MSUM(Moving SUM)

Moving Sum (MSUM):

• Used to calculate Moving sum on a specified column.

• The number of rows used for aggregation operation is called as query width.

• If there are less than n preceding rows then all those are available are used for summing

• Syntax:

MSUM(colname, n, sortlist)

colname = the column on which the moving sum is computed

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

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

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

• Example:

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

Query width is 3.

Employeeid	Salary	Hiredate	MSum(Salary,3,Hiredate)
8	8000	1/1/1992	8000	same as 1st row
4	4000	1/1/1996	12000	sum of rows 1 and 2
7	7000	6/1/1996	19000	sum of rows 1,2 and 3
3	3000	1/1/1997	14000	Sum of rows 2,3 and 4
6	6000	3/1/1997	16000	sum of rows 3,4 and 5
2	2000	1/1/1998	11000	"
10	10000	1/1/1998	18000	"
5	5000	2/1/1998	17000	"
1	1000	1/1/1999	16000	"
9	9000	4/1/1999	15000	"
11	11000	5/1/1999	21000	"
12	12000	8/1/1999	32000	"

• Moving Sum (MSum) follows the same rules as Moving Averages (MAvg):
•          Uses current row and preceding n-1 rows.
•          Uses all preceding rows if less than n-1.
•          Sort ascending by sortlist column(s) is the default.