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 MAX/MIN using MAX/MIN window function

Cumulative Maximum/Minimum Using MAX/MIN OVER:

Just as we have cumulative SUM we can also have Cumulative MAX or MIN. Just as cumulative sum performs Summation sequentially, Cumulative MAX/MIN also provide the maximum/minimum sequentially. This means it provides the maximum or minimum value at each point of the result. Following example will help you understand better:   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, MAX(SALARY) OVER (  ORDER        BY DEPARTMENTNO ROWS  UNBOUNDED PRECEDING  ) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Cumulative Max(Salary) 1 100 1000 1000 5 100 5000 5000 9 100 9000 9000 10 200 10000 10000 124 200 12345.11 12345.11 6 200 6000 12345.11 2 200 2000 12345.11 7 300 7000 12345.11 3 300 3000 12345.11 11 300 11000 12345.11 12 400 12000 12345.11 8 400 8000 12345.11 4 400 4000 12345.11 144 400 12345.11 12345.11 In the above example at the first row there is nothing to compare against and hence maximum value is 1000. At the second row the maximum value at that point is 5000. Same thing happens on the 3rd and 4th row on 5th row. From the 5th row onwards the value never changes as it’s the maximum value in the lot. Also notice that the column title says CUMULATIVE SUM. We can also use partition by clause to perform grouping. SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, MAX(SALARY) OVER (  PARTITION BY DEPARTMENTNO ORDER        BY DEPARTMENTNO ROWS  UNBOUNDED PRECEDING  ) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Cumulative Max(Salary) 9 100 9000 9000 5 100 5000 9000 1 100 1000 9000 6 200 6000 6000 124 200 12345.11 12345.11 2 200 2000 12345.11 10 200 10000 12345.11 11 300 11000 11000 7 300 7000 11000 3 300 3000 11000 4 400 4000 4000 8 400 8000 8000 12 400 12000 12000 144 400 12345.11 12345.11

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