Teradata Moving SUM using SUM window Function - ROWS N PRECEDING

SUM Window moving function:

Moving Window function allow aggregation over a defined window.   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (  ORDER        BY DEPARTMENTNO ROWS  2 PRECEDING  ) FROM        EMPLOYEE2; Note that for moving sum we don’t use the word unbounded, instead we specify the width explicitly as shown above. In the above example we specify 'ROWS 2 PRECEDING' to achieve a window of 3. Employeeid DepartmentNo Salary Moving Sum(Salary) 1 100 1000 1000 5 100 5000 6000 9 100 9000 15000 10 200 10000 24000 2 200 2000 21000 6 200 6000 18000 124 200 12345.11 20345.11 7 300 7000 25345.11 11 300 11000 30345.11 3 300 3000 21000 8 400 8000 22000 12 400 12000 23000 4 400 4000 24000 144 400 12345.11 28345.11 Note that column title is Moving Sum. We can produce the same result using the following query. This is same as the query shown above: SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (  ORDER        BY DEPARTMENTNO ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  ) FROM        EMPLOYEE2; Thus we know that 'ROWS 2 PRECEDING' actually means 'ROWS BETWEEN 2 PRECEDING and CURRENT ROW'. That’s the reason why to achieve a window of 3 we write 'ROWS 2 PRECEDING' as CURRENT ROW is included by default. We can also use PARTITION BY clause to perform grouping as shown below:   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (  PARTITION BY DEPARTMENTNO ORDER        BY DEPARTMENTNO ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  ) FROM        EMPLOYEE2;   Employeeid DepartmentNo Salary Moving Sum(Salary) 1 100 1000 1000 9 100 9000 10000 5 100 5000 15000 124 200 12345.11 12345.11 10 200 10000 22345.11 6 200 6000 28345.11 2 200 2000 18000 7 300 7000 7000 3 300 3000 10000 11 300 11000 21000 144 400 12345.11 12345.11 12 400 12000 24345.11 8 400 8000 32345.11 4 400 4000 24000

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.