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