OLAP - Part 5 - MDIFF (moving difference)

Moving Differences (MDIFF):

1.   The Moving Difference (MDIFF) function permits a calculation of a moving difference of a specified column, based on a defined query width (n).

2.   The query width determines how many rows back to count for the subtrahend (i.e. the number being subtracted).

3.   If there are less than n preceding rows, a null will be generated to represent the difference because there is no value to subtract.

Rule : If the width is 3 then there will be 3 rows with NULL values for the MDIFF column

4.   Syntax:

MDIFF(colname, n, sortlist)

colname = the column on which the moving sum is computed

n = the number of rows (< 4096)

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

5.   Example:

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

Employeeid	Salary	Hiredate	MDiff(Salary,3,Hiredate)
8	8000	1/1/1992	?	NULL as there is no value to subtract
4	4000	1/1/1996	?	NULL as there is no value to subtract
7	7000	6/1/1996	?	NULL as there is no value to subtract
3	3000	1/1/1997	-5000	Its 3000 from current row  minus 8000 from the 1st row.
6	6000	3/1/1997	2000	its 6000 from current row minus 4000 from the 2nd row
2	2000	1/1/1998	-5000	its 2000 from current row minus 7000 from the 3rd row
10	10000	1/1/1998	7000	"
5	5000	2/1/1998	-1000	"
1	1000	1/1/1999	-1000	"
9	9000	4/1/1999	-1000	"
11	11000	5/1/1999	6000	"
12	12000	8/1/1999	11000	"

6.   The usage of MDIFF is slightly different than MAvg and MSum in that it:

• Uses current row and preceding nth row

• Value is null if there is no preceding nth row

• Sort ascending by sortlist column(s) is default