|
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.
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;
|
Monday, 29 April 2013
Teradata Moving SUM using SUM window Function - ROWS N PRECEDING
Sunday, 28 April 2013
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;
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;
|
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;
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;
|
Subscribe to:
Posts (Atom)