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 MAX window Function

Maximum Window Function:

Just as minimum window function we also have maximum window function. Maximum window function is used to get maximum value for the defined group.   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, MAX(SALARY) OVER ( ORDER        BY DEPARTMENTNO) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Group Max(Salary) 9 100 9000 12345.11 5 100 5000 12345.11 1 100 1000 12345.11 124 200 12345.11 12345.11 10 200 10000 12345.11 6 200 6000 12345.11 2 200 2000 12345.11 11 300 11000 12345.11 3 300 3000 12345.11 7 300 7000 12345.11 12 400 12000 12345.11 4 400 4000 12345.11 8 400 8000 12345.11 144 400 12345.11 12345.11 Using PARTITION BY clause we can find maximum per group.   SELECT        EMPLOYEEID,DEPARTMENTNO,SALARY, MAX(SALARY) OVER ( PARTITION BY DEPARTMENTNO ORDER        BY DEPARTMENTNO ROWS BETWEEN UNBOUNDED PRECEDING AND        UNBOUNDED FOLLOWING ) FROM        EMPLOYEE2; Employeeid DepartmentNo Salary Group Max(Salary) 1 100 1000 9000 9 100 9000 9000 5 100 5000 9000 6 200 6000 12345.11 2 200 2000 12345.11 124 200 12345.11 12345.11 10 200 10000 12345.11 3 300 3000 11000 7 300 7000 11000 11 300 11000 11000 4 400 4000 12345.11 8 400 8000 12345.11 12 400 12000 12345.11 144 400 12345.11 12345.11