ANSI and Teradata Function Equivalents - Part 5 - Moving AVG(MAVG)

Function :Moving  Average

Teradata Syntax: Following is the Teradata way of  performing moving average. In the below example we have moving average window as 7 SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY, MAVG(SALARY,7,HIREDATE) FROM EMPLOYEE; Employeeid DepartmentNo Hiredate Salary MAvg(Salary,7,Hiredate) 8 400 1/1/1992 8000 8000 4 400 1/1/1996 4000 6000 7 300 6/1/1996 7000 6333.33 3 300 1/1/1997 3000 5500 6 200 3/1/1997 6000 5600 10 200 1/1/1998 10000 6333.33 2 200 1/1/1998 2000 5714.29 5 100 2/1/1998 5000 5285.71 1 100 1/1/1999 1000 4857.14 9 100 4/1/1999 9000 5142.86 11 300 5/1/1999 11000 6285.71 12 400 8/1/1999 12000 7142.86 144 400 4/20/2013 12345.11 7477.87 124 200 4/20/2013 12345.11 8955.75 In the above result we can see that the value of moving avg is equal to the avg of the salary in the current row and 6 rows above it. When there are less than 6 rows above the current rows, average is calculated based upon the existing rows. In the above 1st column in the brackets of the MAVG indicate the column to be summed. 2nd argument indicates the window for averaging rows. In above query the window width is 7 Column names following the window width are the columns that are used for ordering . Default sequence is ascending. In above HIREDATE is used for ordering.

ANSI syntax: Following is the ANSI syntax of getting similar result as above   SELECT EMPLOYEEID,DEPARTMENTNO,HIREDATE,SALARY, AVG(SALARY) OVER (ORDER BY HIREDATE  ROWS 6 PRECEDING) FROM EMPLOYEE; Employeeid DepartmentNo Hiredate Salary Moving Avg(Salary) 8 400 1/1/1992 8000 8000 4 400 1/1/1996 4000 6000 7 300 6/1/1996 7000 6333.33 3 300 1/1/1997 3000 5500 6 200 3/1/1997 6000 5600 10 200 1/1/1998 10000 6333.33 2 200 1/1/1998 2000 5714.29 5 100 2/1/1998 5000 5285.71 1 100 1/1/1999 1000 4857.14 9 100 4/1/1999 9000 5142.86 11 300 5/1/1999 11000 6285.71 12 400 8/1/1999 12000 7142.86 124 200 4/20/2013 12345.11 7477.87 144 400 4/20/2013 12345.11 8955.75 In the above syntax: The Column specified in the brackets following keyword AVG is the column used for Averaging. Column specified by the 'ORDER BY' indicates the column used for ordering. Default is ascending. COLUMN specified by the 'ROWS N PRECEDING' indicates the number of rows above the current rows to be used for averaging . Note that this number indicates the number of rows above the current row. Thus in the above example to have a window of 7 , we need to use 'ROWS 6 PRECEDING'. However when working with MAVG we need to write the 7 to achieve a window width of 7.