ANSI and Teradata Function Equivalents - Part 4 - Moving SUM(MSUM) and MSUM with Grouping

Function : MOVING SUM

Teradata Syntax: Following is the Teradata  syntax for getting Moving Sum. In the following syntax the we get moving sum with a window of 3 SELECT EMPLOYEEID,HIREDATE,SALARY, MSUM(SALARY,3,HIREDATE) FROM EMPLOYEE; Employeeid Hiredate Salary MSum(Salary,3,Hiredate) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 14000 6 3/1/1997 6000 16000 10 1/1/1998 10000 19000 2 1/1/1998 2000 18000 5 2/1/1998 5000 17000 1 1/1/1999 1000 8000 9 4/1/1999 9000 15000 11 5/1/1999 11000 21000 12 8/1/1999 12000 32000 144 4/20/2013 12345.11 35345.11 124 4/20/2013 12345.11 36690.22 In the above result we can see that the value of moving sum is equal to the sum of the salary in the current row and 2 rows above it. In the above 1st column in the brackets of the MSUM indicate the column to be summed. 2nd argument indicates the window for summing rows. In above query the window width is 3 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. We can perform resets by making use of 'GROUP BY' as shown below SELECT EMPLOYEEID,HIREDATE,SALARY, MSUM(SALARY,5,HIREDATE) FROM EMPLOYEE GROUP BY DEPARTMENTNO; The column used in the 'GROUP BY' also acts as the major sort sequence and the moving sum is reset when the value of department changes.

ANSI syntax: Following is the way we get the same result as above using the ANSI syntax   SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (ORDER BY HIREDATE ROWS 2 PRECEDING) FROM EMPLOYEE; Employeeid Hiredate Salary Moving Sum(Salary) 8 1/1/1992 8000 8000 4 1/1/1996 4000 12000 7 6/1/1996 7000 19000 3 1/1/1997 3000 14000 6 3/1/1997 6000 16000 2 1/1/1998 2000 11000 10 1/1/1998 10000 18000 5 2/1/1998 5000 17000 1 1/1/1999 1000 16000 9 4/1/1999 9000 15000 11 5/1/1999 11000 21000 12 8/1/1999 12000 32000 144 4/20/2013 12345.11 35345.11 124 4/20/2013 12345.11 36690.22 In the above syntax: The Column specified in the brackets following keyword SUM is the column used for summing. 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 summing . Note that this number indicates the number of rows above the current row. Thus in the above example to have a window of 3 , we need to use 'ROWS 2 PRECEDING'. However when working with MSUM we need to write the 3 to achieve a window width of 3. We can perform resets by making use of 'PARTITION BY' as shown below SELECT EMPLOYEEID,HIREDATE,SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY HIREDATE ROWS 2 PRECEDING) FROM EMPLOYEE; The column used in the 'PARTITION BY ' also acts as the major sort sequence and the moving sum is reset when the value of department changes.