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

Teradata Cumulative SUM using SUM WINDOW function

SUM window Cumulative Function:

SUM window function allows cumulative aggregate to be calculated. Teradata equivalent is CSUM function. To get cumulative SUM we use UNBOUNDED PRECEDING as follows: SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (  ORDER        BY DEPARTMENTNO ROWS  UNBOUNDED PRECEDING  ) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Cumulative Sum(Salary) 5 100 5000 5000 9 100 9000 14000 1 100 1000 15000 6 200 6000 21000 124 200 12345.11 33345.11 10 200 10000 43345.11 2 200 2000 45345.11 11 300 11000 56345.11 7 300 7000 63345.11 3 300 3000 66345.11 12 400 12000 78345.11 4 400 4000 82345.11 8 400 8000 90345.11 144 400 12345.11 102690.2 Cumulative Sum is nothing but sequential aggregation . Note that the Column title indicates that its 'Cumulative SUM' We can reset the cumulative SUM by making use of PARTITION BY clause as follows: SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER (  PARTITION BY DEPARTMENTNO ORDER        BY DEPARTMENTNO ROWS  UNBOUNDED PRECEDING  ) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Cumulative Sum(Salary) 1 100 1000 1000 5 100 5000 6000 9 100 9000 15000 124 200 12345.11 12345.11 10 200 10000 22345.11 6 200 6000 28345.11 2 200 2000 30345.11 7 300 7000 7000 3 300 3000 10000 11 300 11000 21000 4 400 4000 4000 12 400 12000 16000 8 400 8000 24000 144 400 12345.11 36345.11

Teradata SUM Window Function

Sum Window Group Function:

SUM window function:  This SUM window group function permits an aggregate to be computed across the defined group. Groups are defined using the PARTITION BY clause. If PARTITION BY is not used then all rows are considered as a one group.   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY,SUM(SALARY) OVER ( ORDER        BY DEPARTMENTNO) FROM        EMPLOYEE2;   Employeeid DepartmentNo Salary Group Sum(Salary) 1 100 1000 102690.2 5 100 5000 102690.2 9 100 9000 102690.2 6 200 6000 102690.2 10 200 10000 102690.2 2 200 2000 102690.2 124 200 12345.11 102690.2 11 300 11000 102690.2 7 300 7000 102690.2 3 300 3000 102690.2 12 400 12000 102690.2 8 400 8000 102690.2 4 400 4000 102690.2 144 400 12345.11 102690.2 As PARTITION is not specified all rows will be considered as a part of same group. Note that column title is GROUP SUM indicates the group function. We can also write the same query as follows: SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, SUM(SALARY) OVER ( ORDER        BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND        UNBOUNDED FOLLOWING) FROM        EMPLOYEE2; This means the words 'ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' are default for ordered analytical functions.

SUM WINDOW group Function with PARTITION:

We use PARTITION BY clause to perform grouping. Following query provides partitioning based on department.   SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY DEPARTMENTNO ) FROM EMPLOYEE2; Employeeid DepartmentNo Salary Group Sum(Salary) 1 100 1000 15000 5 100 5000 15000 9 100 9000 15000 6 200 6000 30345.11 124 200 12345.11 30345.11 2 200 2000 30345.11 10 200 10000 30345.11 7 300 7000 21000 3 300 3000 21000 11 300 11000 21000 4 400 4000 36345.11 8 400 8000 36345.11 144 400 12345.11 36345.11 12 400 12000 36345.11 We can get similar result using the following syntax:   SELECT EMPLOYEEID, DEPARTMENTNO, SALARY, SUM(SALARY) OVER (PARTITION BY DEPARTMENTNO ORDER BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM EMPLOYEE2;   The Group Sum reflects the total for each department. The key words ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING are unnecessary since they are the default.