Blog archive

Monday, 29 April 2013

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

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;

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