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

No comments:

Post a Comment